DEV Community

Cover image for Create, Insert, Retrieve Data in SQL
Md. Jamal Uddin
Md. Jamal Uddin

Posted on • Updated on

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

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

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);
Enter fullscreen mode Exit fullscreen mode

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

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

Top comments (0)