SQL Cheatsheet

2024-02-05 1210 words 6 minutes

Single line comment: -- comment

Multiline comment: /* comments */

Data query Language (DQL)

SELECT 'ID: ', id, col_1 + col_2, sqrt(col_2)
FROM   t1
-- precedence within WHERE: functions, comparisons, NOT, AND, OR
WHERE  col_1 > 100
AND    NOT MOD(col_2, 10) = 0
OR     col_3 < col_1
ORDER  BY col_4 DESC, col_5;

-- number of rows, number of not-null-values
SELECT COUNT(*), COUNT(col_1) FROM t1;

-- predefined functions
SELECT COUNT(col_1), MAX(col_1), MIN(col_1), AVG(col_1), SUM(col_1) FROM t1;

-- UNIQUE values only
SELECT DISTINCT col_1 FROM t1;

-- Only the combination of col_1 plus col_2 is unique
SELECT DISTINCT col_1, col_2 FROM t1;

Case Expression

SELECT id,
       CASE contact_type
         WHEN 'fixed line' THEN 'Phone'
         WHEN 'mobile'     THEN 'Phone'
         ELSE                   'Not a telephone number'
       END AS 'contact_type',
       contact_value
FROM   contact;

There are two short forms for special CASE expressions: COALESCE and NULLIF.

COALESCE(x1,x2) is equivalent to CASE WHEN x1 IS NOT NULL THEN x1 ELSE x2 END

NULLIF(x1, x2) is equivalent to CASE WHEN x1 = x2 THEN NULL ELSE x1 END

Grouping

SELECT product_group, count(*) AS cnt
FROM   sales
WHERE  region = 'west'  -- additional restrictions are possible but not necessary
GROUP  BY product_group -- 'product_group' is the criterion which creates groups
HAVING COUNT(*) > 1000  -- restriction to groups with more than 1000 sales per group
ORDER  BY cnt;

SELECT clause must have columns that are specified in the GROUP BY clause
HAVING clause filters after the grouping is done and can have aggregate functions
WHERE clause can’t have aggregate functions

Joins

Inner Join

SELECT *
FROM   person p
JOIN   contact c ON p.id = c.person_id;

Left Outer Join

SELECT *
FROM   person p
LEFT   JOIN contact c ON p.id = c.person_id;

Right Outer Join

SELECT *
FROM   person p
RIGHT  JOIN contact c ON p.id = c.person_id;

Full Outer Join

SELECT *
FROM   person p
FULL   JOIN contact c ON p.id = c.person_id;

Cartesian Product

SELECT COUNT(*)
FROM   person p
JOIN   contact c;

Subqueries

SELECT isbn, title, price
FROM  Book
WHERE price < (SELECT AVG(price) FROM Book)
ORDER BY title;

Slower than joins
Adds hierarchy execution that can can be useful (example: AVG above)
Subquery can use values from outer query (also called correlated subquery)

Derived table functionality allows the user to reference the subquery as a table

SELECT b.isbn, b.title, b.price, sales.items_sold, sales.company_nm
FROM Book b
  JOIN (SELECT SUM(Items_Sold) Items_Sold, Company_Nm, ISBN
        FROM Book_Sales
        GROUP BY Company_Nm, ISBN) sales -- derived table
  ON sales.isbn = b.isbn

Set Operations

Union

SELECT firstname  -- first SELECT command
FROM   person
  UNION           -- push both intermediate results together to one result
SELECT lastname   -- second SELECT command
FROM   person;

Intersect

-- INTERSECT: resulting values must be in BOTH intermediate results
SELECT firstname FROM person
  INTERSECT
SELECT lastname FROM person;

Except

-- EXCEPT: resulting values must be in the first but not in the second intermediate result
SELECT firstname FROM person
  EXCEPT
SELECT lastname FROM person;

Order of execution

Clause	Order of execution
SELECT <columns>	5.
FROM <table>	1.
WHERE <predicate on rows>	2.
GROUP BY <columns>	3.
HAVING <predicate on groups>	4.
ORDER BY <columns>	6.
OFFSET	7.
FETCH FIRST	8.

Operators

Operator	Description	Example
=	Equal to	Author = ‘Alcott’
<>	Not equal to (many dialects also accept !=)	Dept <> ‘Sales’
>	Greater than	Hire_Date > ‘2012-01-31’
<	Less than	Bonus < 50000.00
>=	Greater than or equal	Dependents >= 2
<=	Less than or equal	Rate <= 0.05
[NOT] BETWEEN	Between an inclusive range.	Cost BETWEEN 100.00 AND 500.00
[NOT] LIKE	Begins with a character pattern, Contains a character pattern	Full_Name LIKE ‘Will%’, Full_Name LIKE ‘%Will%’
[NOT] IN	Equal to one of multiple possible values	DeptCode IN (101, 103, 209)
IS [NOT] NULL	Compare to null (missing data)	Address IS NOT NULL
IS [NOT] TRUE, IS [NOT] FALSE	Boolean truth value test	PaidVacation IS TRUE
IS NOT DISTINCT FROM	Is equal to value or both are nulls (missing data)	Debt IS NOT DISTINCT FROM - Receivables
AS	Used to change a column name when viewing results	SELECT employee AS department1

Data Manipulation Language (DML)

Transaction Control

START TRANSACTION
SAVE TRANSACTION
COMMIT
ROLLBACK

Insert

INSERT INTO t1 (id, col_1, col_2) VALUES (6, 46, 'abc');

INSERT INTO t1 (id, col_1, col_2) VALUES (7, 47, 'abc7'),
                                         (8, 48, 'abc8'),
                                         (9, 49, 'abc9');

INSERT INTO t1 (id, col_1, col_2)
  SELECT id, col_x, col_y 
  FROM   t2
  WHERE  col_y > 100;
COMMIT;

INSERT INTO t1 (id, col_1, col_2)
  SELECT id,
         CASE
           WHEN col_x < 40 THEN col_x + 10
           ELSE                 col_x +  5
         END,
         col_y 
  FROM   t2
  WHERE  col_y > 100;

Update

UPDATE t1
SET col_1 = 'Jimmy Walker', col_2 = 4711
WHERE id = 5;

UPDATE t1 SET col_2 = col_2 * 2 WHERE id = 5;

UPDATE t1 SET col_2 = (SELECT max(id) FROM t1) WHERE id = 5;

UPDATE t1 SET col_2 = (SELECT col_2 FROM t2 where t1.id = t2.id) WHERE id = 5;

UPDATE article
SET col_1 = 'topseller'
WHERE id IN
  (SELECT article_id
   FROM   sales
   GROUP BY article_id
   HAVING COUNT(*) > 1000
  );

Merge

If the rows exist, they must use the UPDATE command, if not, the INSERT command. Also known as upsert.

-- INSERT / UPDATE depending on any criterion, in this case: the two columns 'id'
MERGE INTO hobby_shadow                     t   -- the target table
      USING (SELECT id, hobbyname, remark
             FROM   hobby
             WHERE  id < 8)                 s   -- the source
      ON    (t.id = s.id)                       -- the 'match criterion'
  WHEN MATCHED THEN
    UPDATE SET remark = concat(s.remark, ' Merge / Update')
  WHEN NOT MATCHED THEN
    INSERT (id, hobbyname, remark) VALUES (s.id, s.hobbyname, concat(s.remark, ' Merge / Insert'))
;

Delete

DELETE FROM t1 WHERE id = 5;

DELETE FROM person_hobby
WHERE person_id IN
  (SELECT id
   FROM   person
   WHERE  lastname = 'Goldstein'
  );

Truncate

TRUNCATE TABLE t1;

Deletes ALL rows (WHERE clause is not possible). The table structure remains.
No trigger action will be launched
Faster than DELETE

Data Definition Language (DDL)

Create a table

CREATE TABLE table_name (
  -- column name   data type     default        nullable/constraint
  id               DECIMAL                      PRIMARY KEY,
  col_1            VARCHAR(50)   DEFAULT 'n/a'  NOT NULL,
  col_2            CHAR(10),
  col_3            DECIMAL(10,2) DEFAULT 0.0,                 -- 8 digits before and 2 after the decimal. Signed.
  col_4            TIMESTAMP,
  col_5            INTEGER,
  col_6            BIGINT,
  CONSTRAINT example_pk           PRIMARY KEY (id),
  CONSTRAINT example_uniq         UNIQUE (col_2),
  CONSTRAINT example_fk           FOREIGN KEY (col_1) REFERENCES table2_name(id),
  CONSTRAINT example_col_1_nn     CHECK (col_1 IS NOT NULL),
  CONSTRAINT example_col_1_check  CHECK (col_1 >=0 AND col_1 < 6),
  CONSTRAINT example_check_1      CHECK (LENGTH(col_2) > 3),
);

Alter Table

Add a column with a column constraint

ALTER TABLE t1 ADD COLUMN col_1 VARCHAR(100) CHECK (LENGTH(col_1) > 5);

Change column characteristic

ALTER TABLE t1 ALTER COLUMN col_1 SET DATA TYPE NUMERIC;
ALTER TABLE t1 ALTER COLUMN col_1 SET SET DEFAULT -1;
ALTER TABLE t1 ALTER COLUMN col_1 SET NOT NULL;
ALTER TABLE t1 ALTER COLUMN col_1 DROP NOT NULL;

Drop a column
```
ALTER TABLE t1 DROP COLUMN col_2;
```

Add unique column constraint

ALTER TABLE t1 ADD CONSTRAINT t1_col_1_uniq UNIQUE (col_1);

Add foreign key constraint

ALTER TABLE t1 ADD CONSTRAINT t1_col_2_fk FOREIGN KEY (col_2) REFERENCES person (id);

Change a constraint

ALTER TABLE t1 ALTER CONSTRAINT t1_col_1_unique UNIQUE (col_1);

Drop a constraint

ALTER TABLE t1 DROP CONSTRAINT t1_col_1_unique;

Drop Table

DROP TABLE t1;

Data Control Language (DCL)

GRANT SELECT, UPDATE
 ON example
 TO some_user, another_user;

REVOKE SELECT, UPDATE
 ON example
 FROM some_user, another_user;

Contents