Structured Query Language/Snippets
This page offers SQL examples concerning different topics. You can copy/past the examples according to your needs.
Create Table
[edit | edit source]Data Types
[edit | edit source]--
-- Frequently used data types and simple constraints
CREATE TABLE t_standard (
-- column name data type default nullable/constraint
id DECIMAL PRIMARY KEY, -- some prefer the name: 'sid'
col_1 VARCHAR(50) DEFAULT 'n/a' NOT NULL, -- string with variable length. Oracle: 'VARCHAR2'
col_2 CHAR(10), -- string with fixed length
col_3 DECIMAL(10,2) DEFAULT 0.0, -- 8 digits before and 2 after the decimal. Signed.
col_4 NUMERIC(10,2) DEFAULT 0.0, -- same as col_3
col_5 INTEGER,
col_6 BIGINT -- Oracle: use 'NUMBER(n)', n up to 38
);
-- Data types with temporal aspects
CREATE TABLE t_temporal (
-- column name data type default nullable/constraint
id DECIMAL PRIMARY KEY,
col_1 DATE, -- Oracle: contains day and time, seconds without decimal
col_2 TIME, -- Oracle: use 'DATE' and pick time-part
col_3 TIMESTAMP, -- Including decimal for seconds
col_4 TIMESTAMP WITH TIME ZONE, -- MySql: no time zone
col_5 INTERVAL YEAR TO MONTH,
col_6 INTERVAL DAY TO SECOND
);
CREATE TABLE t_misc (
-- column name data type default nullable/constraint
id DECIMAL PRIMARY KEY,
col_1 CLOB, -- very long string (MySql: LONGTEXT)
col_2 BLOB, -- binary, eg: Word document or mp3-stream
col_3 FLOAT(6), -- example: two-thirds (2/3).
col_4 REAL,
col_5 DOUBLE PRECISION,
col_6 BOOLEAN, -- Oracle: Not supported
col_7 XML -- Oracle: 'XMLType'
);
Constraints
[edit | edit source]--
-- Denominate all constraints with an expressive name, eg.: abbreviations for
-- table name (unique across all tables in your schema), column name, constraint type, running number.
--
CREATE TABLE myExampleTable (
id DECIMAL,
col_1 DECIMAL(1), -- only 1 (signed) digit
col_2 VARCHAR(50),
col_3 VARCHAR(90),
CONSTRAINT example_pk PRIMARY KEY (id),
CONSTRAINT example_uniq UNIQUE (col_2),
CONSTRAINT example_fk FOREIGN KEY (col_1) REFERENCES person(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_col_2_nn CHECK (col_2 IS NOT NULL),
CONSTRAINT example_check_1 CHECK (LENGTH(col_2) > 3),
CONSTRAINT example_check_2 CHECK (LENGTH(col_2) < LENGTH(col_3))
);
Foreign Key
[edit | edit source]--
-- Reference to a different (or the same) table. This creates 1:m or n:m relationships.
CREATE TABLE t_hierarchie (
id DECIMAL,
part_name VARCHAR(50),
super_part_id DECIMAL, -- ID of the part which contains this part
CONSTRAINT hier_pk PRIMARY KEY (id),
-- In this special case the foreign key refers to the same table
CONSTRAINT hier_fk FOREIGN KEY (super_part_id) REFERENCES t_hierarchie(id)
);
-- -----------------------------------------------
-- n:m relationships
-- -----------------------------------------------
CREATE TABLE t1 (
id DECIMAL,
name VARCHAR(50),
-- ...
CONSTRAINT t1_pk PRIMARY KEY (id)
);
CREATE TABLE t2 (
id DECIMAL,
name VARCHAR(50),
-- ...
CONSTRAINT t2_pk PRIMARY KEY (id)
);
CREATE TABLE t1_t2 (
id DECIMAL,
t1_id DECIMAL,
t2_id DECIMAL,
CONSTRAINT t1_t2_pk PRIMARY KEY (id), -- also this table should have its own Primary Key
CONSTRAINT t1_t2_unique UNIQUE (t1_id, t2_id), -- every link should occur only once
CONSTRAINT t1_t2_fk_1 FOREIGN KEY (t1_id) REFERENCES t1(id),
CONSTRAINT t1_t2_fk_2 FOREIGN KEY (t2_id) REFERENCES t2(id)
);
-- -----------------------------------------------------------------------------------
-- ON DELETE / ON UPDATE / DEFFERABLE
-- -----------------------------------------------------------------------------------
-- DELETE and UPDATE behaviour for child tables (see first example)
-- Oracle: Only DELETE [CASCADE | SET NULL] is possible. Default is NO ACTION, but this cannot be
-- specified explicit - just omit the phrase.
CONSTRAINT hier_fk FOREIGN KEY (super_part_id) REFERENCES t_hierarchie(id)
ON DELETE CASCADE -- or: NO ACTION (the default), RESTRICT, SET NULL, SET DEFAULT
ON UPDATE CASCADE -- or: NO ACTION (the default), RESTRICT, SET NULL, SET DEFAULT
-- Initial stage: immediate vs. deferred, [not] deferrable
-- MySQL: DEFERABLE is not supported
CONSTRAINT t1_t2_fk_1 FOREIGN KEY (t1_id) REFERENCES t1(id)
INITIALLY IMMEDIATE DEFERRABLE
-- Change constraint characteristics at a later stage
SET CONSTRAINT hier_fk DEFERRED; -- or: IMMEDIATE
Alter Table
[edit | edit source]Concerning columns.
-- Add a column (plus some column constraints). Oracle: The key word 'COLUMN' is not allowed.
ALTER TABLE t1 ADD COLUMN col_1 VARCHAR(100) CHECK (LENGTH(col_1) > 5);
-- Change a columns characteristic. (Some implementations use different key words like 'MODIFY'.)
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. Oracle: The key word 'COLUMN' is mandatory.
ALTER TABLE t1 DROP COLUMN col_2;
Concerning complete table.
--
ALTER TABLE t1 ADD CONSTRAINT t1_col_1_uniq UNIQUE (col_1);
ALTER TABLE t1 ADD CONSTRAINT t1_col_2_fk FOREIGN KEY (col_2) REFERENCES person (id);
-- Change definitions. Some implementations use different key words like 'MODIFY'.
ALTER TABLE t1 ALTER CONSTRAINT t1_col_1_unique UNIQUE (col_1);
-- Drop a constraint. You need to know its name. Not supported by MySQL, there is only a 'DROP FOREIGN KEY'.
ALTER TABLE t1 DROP CONSTRAINT t1_col_1_unique;
-- As an extension to the SQL standard, some implementations offer an ENABLE / DISABLE command for constraints.
Drop Table
[edit | edit source]--
-- All data and complete table structure inclusive indices are thrown away.
-- No column name. No WHERE clause. No trigger is fired. Considers Foreign Keys. Very fast.
DROP TABLE t1;
Select
[edit | edit source]Basic Syntax
[edit | edit source]--
-- Overall structure: SELECT / FROM / WHERE / GROUP BY / HAVING / ORDER BY
-- constants, column values, operators, functions
SELECT 'ID: ', id, col_1 + col_2, sqrt(col_2)
FROM t1
-- precedence within WHERE: functions, comparisions, 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; -- sort ascending (the default) or descending
-- 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;
-- In the next example col_1 many have duplicates. Only the combination of col_1 plus col_2 is unique.
SELECT DISTINCT col_1, col_2 FROM t1;
Case
[edit | edit source]--
-- CASE expression with conditions on exactly ONE column
SELECT id,
CASE contact_type -- ONE column name
WHEN 'fixed line' THEN 'Phone'
WHEN 'mobile' THEN 'Phone'
ELSE 'Not a telephone number'
END,
contact_value
FROM contact;
-- CASE expression with conditions on ANY column
SELECT id,
CASE -- NO column name
WHEN contact_type IN ('fixed line', 'mobile') THEN 'Phone'
WHEN id = 4 THEN 'ICQ'
ELSE 'Something else'
END,
contact_value
FROM contact;
Grouping
[edit | edit source]--
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;
-- Attention: in the next example, col_2 is not part of the GROUP BY criterion. Therefore it cannot be displayed.
SELECT col_1, col_2
FROM t1
GROUP BY col_1;
-- We must accumulate all col_2-values of each group to ONE value, eg:
SELECT col_1, sum(col_2), min(col_2)
FROM t1
GROUP BY col_1;
Join
[edit | edit source]--
-- Inner join: Only persons together with their contacts.
-- Ignores all persons without contacts and all contacts without persons
SELECT *
FROM person p
JOIN contact c ON p.id = c.person_id;
-- Left outer join: ALL persons. Ignores contacts without persons
SELECT *
FROM person p
LEFT JOIN contact c ON p.id = c.person_id;
-- Right outer join: ALL contacts. Ignores persons without contacts
SELECT *
FROM person p
RIGHT JOIN contact c ON p.id = c.person_id;
-- Full outer join: ALL persons. ALL contacts.
SELECT *
FROM person p
FULL JOIN contact c ON p.id = c.person_id;
-- Carthesian product (missing ON keyword): be carefull!
SELECT COUNT(*)
FROM person p
JOIN contact c;
Subquery
[edit | edit source]--
-- Subquery within SELECT clause
SELECT id,
lastname,
weight,
(SELECT avg(weight) FROM person) -- the subquery
FROM person;
-- Subquery within WHERE clause
SELECT id,
lastname,
weight
FROM person
WHERE weight < (SELECT avg(weight) FROM person) -- the subquery
;
-- CORRELATED subquery within SELECT clause
SELECT id,
(SELECT status_name FROM status st WHERE st.id = sa.state)
FROM sales sa;
-- CORRELATED subquery retrieving the highest version within each booking_number
SELECT *
FROM booking b
WHERE version =
(SELECT MAX(version) FROM booking sq WHERE sq.booking_number = b.booking_number)
;
Set operations
[edit | edit source]--
-- UNION
SELECT firstname -- first SELECT command
FROM person
UNION -- push both intermediate results together to one result
SELECT lastname -- second SELECT command
FROM person;
-- Default behaviour is: 'UNION DISTINCT'. 'UNION ALL' must be explicitly specified, if duplicate values shall be removed.
-- INTERSECT: resulting values must be in BOTH intermediate results
SELECT firstname FROM person
INTERSECT
SELECT lastname FROM person;
-- EXCEPT: resulting values must be in the first but not in the second intermediate result
SELECT firstname FROM person
EXCEPT -- Oracle uses 'MINUS'. MySQL does not support EXCEPT.
SELECT lastname FROM person;
Rollup/Cube
[edit | edit source]-- Additional sum per group and sub-group
SELECT SUM(col_x), ...
FROM ...
GROUP BY ROLLUP (producer, model); -- the MySQL syntax is: GROUP BY producer, model WITH ROLLUP
-- Additional sum per EVERY combination of the grouping columns
SELECT SUM(col_x), ...
FROM ...
GROUP BY CUBE (producer, model); -- not supported by MySQL
Window functions
[edit | edit source]-- The frames boundaries
SELECT id,
emp_name,
dep_name,
FIRST_VALUE(id) OVER (PARTITION BY dep_name ORDER BY id) AS frame_first_row,
LAST_VALUE(id) OVER (PARTITION BY dep_name ORDER BY id) AS frame_last_row,
COUNT(*) OVER (PARTITION BY dep_name ORDER BY id) AS frame_count,
LAG(id) OVER (PARTITION BY dep_name ORDER BY id) AS prev_row,
LEAD(id) OVER (PARTITION BY dep_name ORDER BY id) AS next_row
FROM employee;
-- The moving average
SELECT id, dep_name, salary,
AVG(salary) OVER (PARTITION BY dep_name ORDER BY salary
ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS sum_over_1or2or3_rows
FROM employee;
Recursions
[edit | edit source]-- The 'with clause' consists of three parts:
-- First: arbitrary name of an intermediate table and its columns
WITH intermediate_table (id, firstname, lastname) AS
(
-- Second: starting row (or rows)
SELECT id, firstname, lastname
FROM family_tree
WHERE firstname = 'Karl'
AND lastname = 'Miller'
UNION ALL
-- Third: Definition of the rule for querying the next level. In most cases this is done with a join operation.
SELECT f.id, f.firstname, f.lastname
FROM intermediate_table i
JOIN family_tree f ON f.father_id = i.id
)
-- After the 'with clause': depth first / breadth first
-- SEARCH BREADTH FIRST BY firstname SET sequence_number (default behaviour)
-- SEARCH DEPTH FIRST BY firstname SET sequence_number
-- The final SELECT
SELECT * FROM intermediate_table;
-- Hints: Oracle supports the syntax of the SQL standard since version 11.2. .
-- MySQL does not support recursions at all and recommend procedural workarounds.
Insert
[edit | edit source]--
-- fix list of values/rows
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');
COMMIT;
-- subselect: leads to 0, 1 or more new rows
INSERT INTO t1 (id, col_1, col_2)
SELECT id, col_x, col_y
FROM t2
WHERE col_y > 100;
COMMIT;
-- dynamic values
INSERT INTO t1 (id, col_1, col_2) VALUES (16, CURRENT_DATE, 'abc');
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;
COMMIT;
Update
[edit | edit source]--
-- basic syntax
UPDATE t1
SET col_1 = 'Jimmy Walker',
col_2 = 4711
WHERE id = 5;
-- raise value of col_2 by factor 2; no WHERE ==> all rows!
UPDATE t1 SET col_2 = col_2 * 2;
-- non-correlated subquery leads to one single evaluation of the subquery
UPDATE t1 SET col_2 = (SELECT max(id) FROM t1);
-- correlated subquery leads to one evaluation of subquery for EVERY affected row of outer query
UPDATE t1 SET col_2 = (SELECT col_2 FROM t2 where t1.id = t2.id);
-- Subquery in WHERE clause
UPDATE article
SET col_1 = 'topseller'
WHERE id IN
(SELECT article_id
FROM sales
GROUP BY article_id
HAVING COUNT(*) > 1000
);
Merge
[edit | edit source]--
-- 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'))
;
-- Independent from the number of affected rows there is only ONE round trip between client and DBMS
Delete
[edit | edit source]--
-- Basic syntax
DELETE FROM t1 WHERE id = 5; -- no column name behind 'DELETE' key word because the complete row will be deleted
-- no hit is OK
DELETE FROM t1 WHERE id != id;
-- subquery
DELETE FROM person_hobby
WHERE person_id IN
(SELECT id
FROM person
WHERE lastname = 'Goldstein'
);
Truncate
[edit | edit source]--
-- TRUNCATE deletes ALL rows (WHERE clause is not possible). The table structure remains.
-- No trigger actions will be fired. Foreign Keys are considered. Much faster than DELETE.
TRUNCATE TABLE t1;