SQL-Cheat-Sheet-websitesetup.pdf

Managing Tables

Create a new table with three columns

CREATE TABLE t (
     id INT PRIMARY KEY,
     name VARCHAR NOT NULL,
     price INT DEFAULT 0
);

Delete the table from the database

DROP TABLE t;

Add a new column to the table

ALTER TABLE t
ADD email_address varchar(255);

Drop column c from the table

ALTER TABLE t DROP COLUMN name;

Add a constraint

ALTER TABLE t
ADD CONSTRAINT 
cstr_name PRIMARY KEY (name, price);

Drop a constraint

ALTER TABLE t DROP cstr_name;

Rename a table from t1 to t2

ALTER TABLE t1 RENAME TO t2;

Rename column c1 to c2

ALTER TABLE t1 RENAME c1 TO c2 ;

Remove all data in a table

TRUNCATE TABLE t;

SQL Constraints

Set c1 and c2 as a primary key

CREATE TABLE t(
    c1 INT, c2 INT, c3 VARCHAR,
    PRIMARY KEY (c1,c2)
);

Set c2 column as a foreign key

CREATE TABLE t1(
    c1 INT PRIMARY KEY,  
    c2 INT,
    FOREIGN KEY (c2) REFERENCES t2(c2)
);

Make the values in c1 and c2 unique

CREATE TABLE t(
    c1 INT, c1 INT,
    UNIQUE(c2,c3)
);

Ensure c1 > 0 and values in c1 >= c2

CREATE TABLE t(
  c1 INT, c2 INT,
  CHECK(c1> 0 AND c1 >= c2)
);

Set values in c2 column not NULL

CREATE TABLE t(
     c1 INT PRIMARY KEY,
     c2 VARCHAR NOT NULL
);

Querying data from a table

Query data in columns c1, c2 from a table

SELECT c1, c2 FROM t;

Query all columns from a table

SELECT * FROM t;

Query data and filter rows with a condition

SELECT c1, c2 FROM t
WHERE condition;

Query distinct rows from a table

SELECT DISTINCT c1 FROM t
WHERE condition;

Sort the result set in ascending or descending order

SELECT c1, c2 FROM t
ORDER BY c1 ASC [DESC];

Skip offset of rows and return the next n rows

SELECT c1, c2 FROM t
ORDER BY c1 
LIMIT n OFFSET offset;

Group rows using an aggregate function

• AVG, COUNT, SUM, MAX, MIN

SELECT c1, aggregate(c2)
FROM t
GROUP BY c1;

Filter groups using HAVING clause

SELECT c1, aggregate(c2)
FROM t
GROUP BY c1
HAVING condition;

Querying from multiple tables

Inner join t1 and t2

SELECT c1, c2 
FROM t1
INNER JOIN t2 ON condition;

Left join t1 and t1

SELECT c1, c2 
FROM t1
LEFT JOIN t2 ON condition;

Right join t1 and t2

SELECT c1, c2 
FROM t1
RIGHT JOIN t2 ON condition;

Perform full outer join

SELECT c1, c2 
FROM t1
FULL OUTER JOIN t2 ON condition;

Produce a Cartesian product of rows in tables

SELECT c1, c2 
FROM t1
CROSS JOIN t2;
// Another way
SELECT c1, c2 
FROM t1, t2;

Join t1 to itself using INNER JOIN clause

SELECT c1, c2
FROM t1 A
INNER JOIN t1 B ON condition;

Inner join with 3 tables

// 'MIKE'의 주문 정보 + 주문자 이름 + 상품명
SELECT O.*, C.CUST_NAME, P.PRODUCT_NAME
FROM TB_ORDER O
	LEFT OUTER JOIN TB_CUST C ON O.CUST_NO = C.CUST_NO
	LEFT OUTER JOIN TB_PRODUCT P ON O.PRODUCT_NO = P.PRODUCT_NO
WHERE
	C.CUST_NAME = 'MIKE'

Using SQL Operators

Combine rows from two queries

SELECT c1, c2 FROM t1
UNION [ALL] -- All allows duplicate
SELECT c1, c2 FROM t2;

Return the intersection of two queries

SELECT c1, c2 FROM t1
INTERSECT
SELECT c1, c2 FROM t2;

Subtract a result set from another result set

SELECT c1, c2 FROM t1
MINUS
SELECT c1, c2 FROM t2;

Query rows using pattern matching %, _

• % - zero or more characters
• - - single character
• [a-z] - single character in 'a' to 'z'

SELECT c1, c2 FROM t1
WHERE c1 [NOT] LIKE '%app_e';
---
SELECT * FROM users
WHERE first_name LIKE '%[!n-s]';

Query rows in a list

SELECT c1, c2 FROM t
WHERE c1 [NOT] IN value_list;

Query rows between two values

SELECT c1, c2 FROM t
WHERE  c1 BETWEEN low AND high;

Check if values in a table is NULL or not

SELECT c1, c2 FROM t
WHERE  c1 IS [NOT] NULL;

All

SELECT first_name, surname, tasks_no
FROM users
WHERE tasks_no > ALL (SELECT tasks 
								FROM user WHERE
								department_id = 2);

Any or Some

SELECT name
FROM products
WHERE productId = ANY (SELECT productId
									FROM orders WHERE
									quantity > 5);

Data Types