Posted on Aug 31, 2021 • Edited on Sep 7, 2022

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;