SQL: Exemple de snippet

--
-- 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'
);

--
-- 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))
);

--
-- 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

-- 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;

--
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.

--
-- 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;

--
-- 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 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;

--
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;

--
-- 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 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)
;

--
-- 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;

-- 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

-- 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;

-- 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.

--
-- 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;

--
-- 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
  );

--
-- 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

--
-- 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 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;