Structured Query Language/Snippets

__TOC__ This page offers SQL examples concerning different topics. You can copy/past the examples according to your needs. Create Table Data Types More Details -- -- 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 More Details -- -- 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 More Details -- -- 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 More Details 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 More Details -- -- 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 Basic Syntax More Details -- -- 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 More Details -- -- 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 More Details -- 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 More Details -- -- 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 More Details -- -- 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 More Details -- -- 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 More Details -- 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 More Details -- 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 More Details -- 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 More Details -- -- 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 More Details -- -- 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 More Details -- -- 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 More Details -- -- 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 More Details -- -- 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;