Create, Insert, Retrieve Data in SQL

Create table

We often use table in relational database to make models and predictions, create dashboards, visualize data with other tools, extract data from other sources and store them in an organized way.

Table Creation Syntax:

CREATE TABLE table_name (
    Col_name_1 DATA_TYPE,
    Col_name_2 DATA_TYPE,
    …….
    Col_name_n DATA_TYPE
);

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)
);

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 the Table

INSERT INTO shoes
VALUES (1, ‘Nike’, ‘Slipper’, ‘Black’, ‘9.2’, NULL);

OR add data using explicitly declared the column name:

INSERT INTO shoes (id, brand, shoe_type, color, price, desc)
VALUES (1, ‘Nike’, ‘Slipper’, ‘Black’, ‘9.2’, NULL);

CREATE TEMPORARY TABLE

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’
);

Example:

CREATE TEMPORARY TABLE Sandals AS (
    SELECT *
    FROM shoes
    WHERE shoe_type=’sandal’
);

ADD COMMENTS in SQL STATEMENTS

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;

Multiline Comments / Section Comments

SELECT *
/*  Shoe_id,
    Shoe_name,
Shoe_type,
Shoe_color
*/
FROM shoes;

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;

Credit

• Cover Photo by Michael Dziedzic on Unsplash