DEV Community

Cover image for SQL Basic: Part-4
Md. Jamal Uddin
Md. Jamal Uddin

Posted on

SQL Basic: Part-4

Creating tables

Why tables are useful as Data Scientist
Use tables to make models and predictions, create dashboards, Visualize data with other tools, Extract data from other sources and store them in an organized way.

Syntax

CREATE TABLE table_name (
    Col_name_1 DATA_TYPE,
    Col_name_2 DATA_TYPE,
    …….
    Col_name_n DATA_TYPE
);
Enter fullscreen mode Exit fullscreen mode

Example:

CREATE TABLE shoes (
    id                 NUMBER           PRIMARY KEY,
    brand          VARCHAR(20)   NOT NULL,
    shoe_type   VARCHAR(20),
    color            VARCHAR(10),
    price            DECIMAL(2, 8)  NOT NULL,
    desc            VARCHAR(100)
);
Enter fullscreen mode Exit fullscreen mode

NULL and Primary Key

Every column can be NULL or NOT NULL.
An error will be returned if one tries to submit a column with no value when it is defined as a NOT NULL
Don’t confuse NULL values with empty strings that are different kind of data
Primary keys can be NOT NULL and MUST be have a unique value in a table

ADD / INSERT Data to your Table

INSERT INTO shoes
VALUES (1, Nike, Slipper, Black, 9.2, NULL);
Enter fullscreen mode Exit fullscreen mode

OR

INSERT INTO shoes (id, brand, shoe_type, color, price, desc)
VALUES (1, Nike, Slipper, Black, 9.2, NULL);
Enter fullscreen mode Exit fullscreen mode

CREATE TEMPORARY TABLE

WHY CREATE TEMPORARY TABLES?
Temporary tables will be deleted when current session terminated
Faster than creating a real table
Useful for complex queries using subsets and joins

Syntax:

CREATE TEMPORARY TABLE temp_table_name AS (
    SELECT *
    FROM main_table_name
    WHERE param=value
);
Enter fullscreen mode Exit fullscreen mode

Example:

CREATE TEMPORARY TABLE Sandals AS (
    SELECT *
    FROM shoes
    WHERE shoe_type=sandal
);
Enter fullscreen mode Exit fullscreen mode

ADD COMMENTS in SQL STATEMENTS

WHY ADD COMMENTS

Comments help you remember what you were doing and why
Mute the expression of code (commenting out code)
Troubleshoot query issues systematically

Single Line Comments

SELECT shoe_id,
       - -  brand_id,
shoe_name,
shoe_type
FROM shoes;
Enter fullscreen mode Exit fullscreen mode

Multiline Comments / Section Comments

SELECT *
/*  Shoe_id,
    Shoe_name,
Shoe_type,
Shoe_color
*/
FROM shoes;
Enter fullscreen mode Exit fullscreen mode

COMMENTS GONE WRONG

SELECT
-- Getting the avg units for each materials
Material,
Avg_units,
FROM shoes
-- group it by each type
GROUP BY shoe_type
-- Order it by brand
ORDER BY brand;
Enter fullscreen mode Exit fullscreen mode

Credit: Cover Photo by Michael Dziedzic on Unsplash

Discussion (0)