Single line comment: -- comment
Multiline comment: /* comments */
Data query Language (DQL)
1
2
3
4
5
6
7
| 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;
|
1
2
| -- number of rows, number of not-null-values
SELECT COUNT(*), COUNT(col_1) FROM t1;
|
1
2
| -- predefined functions
SELECT COUNT(col_1), MAX(col_1), MIN(col_1), AVG(col_1), SUM(col_1) FROM t1;
|
1
2
| -- UNIQUE values only
SELECT DISTINCT col_1 FROM t1;
|
1
2
| -- Only the combination of col_1 plus col_2 is unique
SELECT DISTINCT col_1, col_2 FROM t1;
|
Case Expression
1
2
3
4
5
6
7
8
| 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
1
2
3
4
5
6
| 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
clauseHAVING
clause filters after the grouping is done and can have aggregate functionsWHERE
clause can’t have aggregate functions
Joins
Inner Join
1
2
3
| SELECT *
FROM person p
JOIN contact c ON p.id = c.person_id;
|
Left Outer Join
1
2
3
| SELECT *
FROM person p
LEFT JOIN contact c ON p.id = c.person_id;
|
Right Outer Join
1
2
3
| SELECT *
FROM person p
RIGHT JOIN contact c ON p.id = c.person_id;
|
Full Outer Join
1
2
3
| SELECT *
FROM person p
FULL JOIN contact c ON p.id = c.person_id;
|
Cartesian Product
1
2
3
| SELECT COUNT(*)
FROM person p
JOIN contact c;
|
Subqueries
1
2
3
4
| 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
1
2
3
4
5
6
| 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
1
2
3
4
5
| SELECT firstname -- first SELECT command
FROM person
UNION -- push both intermediate results together to one result
SELECT lastname -- second SELECT command
FROM person;
|
Intersect
1
2
3
4
| -- INTERSECT: resulting values must be in BOTH intermediate results
SELECT firstname FROM person
INTERSECT
SELECT lastname FROM person;
|
Except
1
2
3
4
| -- 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
1
| INSERT INTO t1 (id, col_1, col_2) VALUES (6, 46, 'abc');
|
1
2
3
| INSERT INTO t1 (id, col_1, col_2) VALUES (7, 47, 'abc7'),
(8, 48, 'abc8'),
(9, 49, 'abc9');
|
1
2
3
4
5
| INSERT INTO t1 (id, col_1, col_2)
SELECT id, col_x, col_y
FROM t2
WHERE col_y > 100;
COMMIT;
|
1
2
3
4
5
6
7
8
9
| 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
1
2
3
| UPDATE t1
SET col_1 = 'Jimmy Walker', col_2 = 4711
WHERE id = 5;
|
1
| UPDATE t1 SET col_2 = col_2 * 2 WHERE id = 5;
|
1
| UPDATE t1 SET col_2 = (SELECT max(id) FROM t1) WHERE id = 5;
|
1
| UPDATE t1 SET col_2 = (SELECT col_2 FROM t2 where t1.id = t2.id) WHERE id = 5;
|
1
2
3
4
5
6
7
8
| 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.
1
2
3
4
5
6
7
8
9
10
11
| -- 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
1
| DELETE FROM t1 WHERE id = 5;
|
1
2
3
4
5
6
| DELETE FROM person_hobby
WHERE person_id IN
(SELECT id
FROM person
WHERE lastname = 'Goldstein'
);
|
Truncate
- 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
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
| 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
1
| ALTER TABLE t1 ADD COLUMN col_1 VARCHAR(100) CHECK (LENGTH(col_1) > 5);
|
Change column characteristic
1
2
3
4
| 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
1
| ALTER TABLE t1 DROP COLUMN col_2;
|
Add unique column constraint
1
| ALTER TABLE t1 ADD CONSTRAINT t1_col_1_uniq UNIQUE (col_1);
|
Add foreign key constraint
1
| ALTER TABLE t1 ADD CONSTRAINT t1_col_2_fk FOREIGN KEY (col_2) REFERENCES person (id);
|
Change a constraint
1
| ALTER TABLE t1 ALTER CONSTRAINT t1_col_1_unique UNIQUE (col_1);
|
Drop a constraint
1
| ALTER TABLE t1 DROP CONSTRAINT t1_col_1_unique;
|
Drop Table
Data Control Language (DCL)
1
2
3
| GRANT SELECT, UPDATE
ON example
TO some_user, another_user;
|
1
2
3
| REVOKE SELECT, UPDATE
ON example
FROM some_user, another_user;
|
References