SQL Basics

$ mysql -u username -p
Enter Password: ******** 

CREATE DATABASE [IF NOT EXISTS] db_name;
DROP DATABASE [IF EXISTS] db_name;

SHOW DATABASES;

USE db_name;

CREATE TABLE [IF NOT EXISTS] table_name(
   column_1_definition,
   column_2_definition,
   ...,
   table_constraints
) ENGINE=storage_engine;

CREATE TABLE employee(
emp_id INT PRIMARY KEY,
emp_name VARCHAR(50),
doj DATE,
);

CREATE TABLE t_name(
serial_no INT NOT NULL AUTO_INCREMENT, -- PRIMARY KEY (alternatively)
emp_id INT NOT NULL,
emp_name VARCHAR(25) NOT NULL,
doj DATE,
PRIMARY KEY (emp_id)    --single column only, multiple => CONSTRAINT combined_pk_alias PRIMARY KEY (ID,LastName)
);

CREATE TABLE TestTable AS
SELECT customername, contactname
FROM customers;

CREATE TABLE emp1 AS
SELECT * FROM emp 
WHERE 1=2;  --false condition; no data from where clause

ALTER TABLE t_name
ADD c_name varchar(10)
[ FIRST | AFTER column_name],
ADD c2_name varchar(100)
[ FIRST | AFTER column_name];

ALTER TABLE t_name
MODIFY c_name varchar(20),
MODIFY c2_name varchar(90);

ALTER TABLE t_name
CHANGE COLUMN old_name new_name
[ FIRST | AFTER column_name];

ALTER TABLE t_name
DROP COLUMN c_name;

ALTER TABLE t_name
RENAME TO new_t_name;

INSERT INTO t_name(attrib1, attrib2, ... attribN)
VALUES
(v11,v12,...), 
(v21,v22,...), 
... 
(vnn,vn2,...);

-- No need to specify attribute list if filling all attributes:
INSERT INTO t_name VALUES (value1, value2, ... valueN);

SELECT [DISTINCT] Attribute_List FROM T1,T2…TM
[WHERE condition1 [AND/OR... condition2]]
[GROUP BY (Attributes)[HAVING condition]]
[ORDER BY(Attributes)[ASC/DESC]];

SELECT first_name FROM person_tbl WHERE first_name REGEXP '^a.*';

SELECT field1, field2,...fieldN 
FROM table_name1, table_name2...
[WHERE Clause]
[LIMIT N] [OFFSET M ]

SELECT field1, field2,...fieldN 
FROM table_name1, table_name2...
[WHERE Clause]
LIMIT [offset_value,] limit_value

select long_w from station 
where lat_n > 38 order by lat_n asc limit 1;

-- selects long_w for a (single) smallest lat_n greater than 38

ORDER BY foo,   -- required
OFFSET m {ROW | ROWS}
FETCH [FIRST | NEXT] n {ROW | ROWS} ONLY;

UPDATE table_name 
SET field1 = value1, 
field2 = value2
[WHERE Clause]

DELETE FROM table_name 
[WHERE Clause]

LEN()    LCASE()    UCASE()    MID()    CONCAT()    RAND()    ROUND()    NOW()    FORMAT()

SELECT name, price * 0.25 from items;
SELECT name, ROUND(price - (price * 0.25), 2) from items;   -- scale = 2, ROUND(expr, scale)

SELECT name, ROUND(price - (price * 0.25), 2) AS discounted_price from items;   -- aliasing column

-- COALESCE: returns first non-null value from the left among the arguments
SELECT COALESCE(null, null, 1, 10) from customers;      -- 1
SELECT COALESCE(is_smoker, 'No') from customers;

-- NULLIF: returns NULL if both the arguments are equal otherwise left arg
NULLIF(9, 9)
NULLIF (1, 0); -- returns 1
NULLIF ('A', 'B'); -- returns A

-- CAST
CAST('2022-06-23' AS DATE)
CAST('78' AS INTEGER)
CAST('8.99' AS DOUBLE)

# Column Aliases
SELECT price AS cost FROM table;

SELECT price cost FROM table;        -- ommitting AS

# Table Aliases
SELECT item, price FROM table AS t;

SELECT item, price FROM table t;     -- ommitting AS

SELECT COUNT(CustomerID), Country
FROM Customers
GROUP BY Country    -- count() is grouped; otherwise it would not have been

SELECT COUNT(CustomerID), Country
FROM Customers
GROUP BY Country
HAVING Country = 'USA';

select long_w from station 
where lat_n < 137 order by lat_n desc limit 1;

-- gets the (single) maximum lat_n less than 137 and displays corresponding long_w

-- subquery in projection
SELECT atribute1, attribute2, (subquery) FROM demo;

-- select from subquery; aka Derived Table
SELECT atribute1, ..., attributeN FROM (subquery) AS subquery_alias;
-- alias is mandatory as because everything in the FROM clause must have a name

-- simple subquery: inner query is independent of outer query
-- employees whose salary is greater than average of all employees in the company
SELECT 
    employee_id, first_name, last_name, salary
FROM
    employees
WHERE
    salary > (SELECT AVG(salary) FROM employees)    -- executed only once for all rows of outer query

-- correlated subquery: inner query uses outer table's column
-- employees whose salary is greater than average of all employees in thier respective departments
SELECT 
    employee_id, 
    first_name,
    last_name
FROM
    employees e
WHERE 
    salary > (SELECT AVG(salary) 
                FROM employees 
                WHERE department_id = e.department_id);     -- correlation; executed once for each row for all rows of outer query

SELECT SupplierName
FROM Suppliers
WHERE EXISTS (SELECT ProductName FROM Products WHERE Products.SupplierID = Suppliers.supplierID AND Price < 20);

SELECT ProductName 
FROM Products
WHERE ProductID = ANY (SELECT ProductID FROM OrderDetails WHERE Quantity = 10);

-- lists productIDs in Products table which are equal to any one ProductID in OrderDetails table

SELECT ProductName 
FROM Products
WHERE ProductID < ALL (SELECT ProductID FROM OrderDetails WHERE Quantity = 10);

-- lists productIDs in Products table which are less than all ProductIDs in OrderDetails table

SELECT column1, column2, column3, ...
INTO newtable [IN externaldb]
FROM oldtable
WHERE condition;

SELECT * 
INTO newtable
FROM oldtable
WHERE 1=2;

INSERT INTO table2 (column1, column2, column3, ...)
SELECT column1, column2, column3, ...
FROM table1
WHERE condition;

CASE
    WHEN condition1 THEN result1    -- notice no comma separator
    WHEN condition2 THEN result2
    WHEN conditionN THEN resultN
    ELSE default_result
END

-- another way
CASE(expr)
    WHEN value_of_expr1 THEN result1
    WHEN value_of_expr2 THEN result2
    WHEN value_of_exprN THEN resultN
    ELSE default_result
END

SELECT OrderID, Quantity,
CASE WHEN Quantity > 30 THEN 'The quantity is greater than 30'
WHEN Quantity = 30 THEN 'The quantity is 30'
ELSE 'The quantity is under 30'
END AS QuantityText
FROM OrderDetails;

CASE(animal_name)
    WHEN 'Dolphin' THEN 'Mammal'
    WHEN 'Shark' THEN 'Fish'
    WHEN 'Snake' THEN 'Reptile'
    ELSE 'Unspecified'
END

SELECT column_name(s) FROM table1
UNION / UNION ALL
SELECT column_name(s) FROM table2;

SELECT City, Country FROM Customers
WHERE Country = 'Germany'
UNION
SELECT City, Country FROM Suppliers
WHERE Country = 'Germany'
ORDER BY City;	--applies to whole union-ed table

SELECT a
FROM A
INNER JOIN B ON b = a;

-- three tables
SELECT A.n
FROM A
INNER JOIN B ON B.n = A.n
INNER JOIN C ON C.n = A.n;

SELECT A.n
FROM A
LEFT JOIN B ON B.n = A.n;

SELECT A.n
FROM A
RIGHT JOIN B ON B.n = A.n;

SELECT A.n
FROM A
FULL [OUTER] JOIN B ON B.n = A.n;

-- all of the below are equivalent

SELECT select_list
FROM T1
CROSS JOIN T2;

SELECT select_list
FROM T1, T2;

SELECT *
FROM T1
INNER JOIN T2 ON true;

SELECT select_list FROM T1
NATURAL [INNER, LEFT, RIGHT] JOIN T2;

SELECT * FROM city
NATURAL JOIN country;

SELECT * FROM products
INNER JOIN categories USING (category_id);

BEGIN;      -- begin a transaction

COMMIT;     -- cannot rollback to a point after a commit

SAVEPOINT savepoint_name;

ROLLBACK TO savepoint_name;
ROLLBACK;   -- rolls back to latest savepoint; or latest commit

CREATE TABLE demo(
id int NOT NULL PRIMARY KEY,	--column level since single PK
name varchar(20)
);

CREATE TABLE demo(
id int,
name varchar(20),
PRIMARY KEY(id)
);

CREATE TABLE demo(
name varchar(20),
id int NOT NULL,
[CONSTRAINT key_alias] PRIMARY KEY(name, id) 		--table level since multiple PK, single also allowed
);

PersonID int FOREIGN KEY REFERENCES Persons(PersonID)       -- column level

FOREIGN KEY (PersonID) REFERENCES Persons(PersonID)         -- table level; same effect as above

CONSTRAINT FK_PersonOrder FOREIGN KEY (PersonID) REFERENCES Persons(PersonID)   -- same but uses CONSTRAINT keyword; table level 

Age int CHECK(Age >= 18)       -- column level; single column check

CONSTRAINT CHK_Person CHECK (Age >= 18 AND City = 'Seattle')   -- table level; multiple column check

CREATE TABLE t_name (
    col1 int NOT NULL UNIQUE,
    col2 varchar(255) DEFAULT 3,
    col3 varchar(255),
    col4 int,
    
);

ALTER  TABLE demo 
ADD PRIMARY KEY(id);

ALTER TABLE Persons
ADD CHECK (Age >= 18);

ALTER TABLE demo
DROP PRIMARY KEY;

id int AUTO_INCREMENT   -- MySQL; column level

id int SERIAL   -- Postgres; column level

-- we can also create a sequence and SERIAL uses sequence internally with step = 1, init val = 0

-- Identity colummn is also provided in Postgres which also uses sequences internally

-- GROUP BY with UNION ALL
SELECT count(*) FROM employees GROUP BY first_name;
UNION ALL
SELECT count(*) FROM employees GROUP BY last_name;
UNION ALL
SELECT count(*) FROM employees;

-- equivalent query using GROUPING SETS; we can specify sets to group by inside parentheses
SELECT count(*) FROM employees
GROUP BY GROUPING SETS ( 
    first_name, 
    last_name, 
    () 
);
-- () means no gouping (it will return count(*) for the whole table)

-- another example with composite grouping sets; equivalent to ROLLUP
SELECT count(*) FROM employees
GROUP BY GROUPING SETS ( 
    (first_name, last_name),
    (first_name),
    (last_name),
    ()
);

-- ROLLUP
SELECT count(*) FROM employees
GROUP BY ROLLUP ( first_name, last_name );
-- shows result of group by none, group by all 3, group by 2, group by just 1 

-- CUBE
SELECT count(*) FROM employees
GROUP BY CUBE ( first_name, last_name );
-- shows result of group by for all combinations (2^n power set) 

-- syntax
WITH cte_name [(column_list)] AS (
    CTE_query 
)
query;

-- example 
WITH cte_film AS (
    SELECT film_id, title    
    FROM film
    WHERE release_year <= 2000
)
SELECT film_id, title
FROM  cte_film        -- notice
WHERE genre = 'Sci-Fi';

CREATE VIEW view_name AS query;

-- query can be any JOIN query that outputs a projection having columns from multiple tables
-- when we query a View, we are effectively querying this complex JOIN query's output