Structured Query Language/Snippets

From Wikibooks, open books for an open world
Jump to: navigation, search

← Language Elements | Create a simple Table →

Create Table[edit]

Data Types[edit]

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[edit]

More Details
--
-- Denominate all contraints 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]

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[edit]

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 definitons. 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 extention to the SQL standard some implementations offer an ENABLE / DISABLE command for constraints.

Drop Table[edit]

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[edit]

Basic Syntax[edit]

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[edit]

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[edit]

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[edit]

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[edit]

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[edit]

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;

Insert[edit]

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[edit]

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[edit]

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[edit]

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[edit]

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;