Snippets


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;



  This article uses material from the Wikipedia page available here. It is released under the Creative Commons Attribution-Share-Alike License 3.0.

Structured_Query_Language/Snippets
 



 

Connect with defaultLogic
What We've Done
Led Digital Marketing Efforts of Top 500 e-Retailers.
Worked with Top Brands at Leading Agencies.
Successfully Managed Over $50 million in Digital Ad Spend.
Developed Strategies and Processes that Enabled Brands to Grow During an Economic Downturn.
Taught Advanced Internet Marketing Strategies at the graduate level.


Manage research, learning and skills at defaultLogic. Create an account using LinkedIn or facebook to manage and organize your Digital Marketing and Technology knowledge. defaultLogic works like a shopping cart for information -- helping you to save, discuss and share.

Visit defaultLogic's partner sites below:
PopFlock.com : Music Genres | Musicians | Musical Instruments | Music Industry
NCR Works : Retail Banking | Restaurant Industry | Retail Industry | Hospitality Industry

  Contact Us