Show Menu
Cheatography

SQL basics - only snippets no explaining Cheat Sheet (DRAFT) by

For those who know SQL but sometimes forget the right syntax

This is a draft cheat sheet. It is a work in progress and is not finished yet.

WITH

WITH query_tab_name AS ( 
 SELECT ....
) 
SELECT ...
FROM  query_tab_name  ....

Full Syntax Order

SELECT [ DISTINCT ]
expressions
FROM tables
[JOIN other_table ON conditions]
[WHERE conditions]
[GROUP BY expressions]
[HAVING conditions]
[ORDER BY expression [ ASC | DESC ]]
[LIMIT number_rows OFFSET offset_value];

CASE WHEN

CASE 
   WHEN contionion THEN x
   WHEN condition2 THEN y
   ELSE z
END

JOINS

SQL_Server & Oracle

[INNER] JOIN
LEFT [OUTER] JOIN 
RIGHT [OUTER] JOIN
FULL [OUTER] JOIN 
CROSS JOIN -- cartesian_join

example:
SELECT * FROM tabA INNER JOIN tabB ON tabA .id = tabB .id;

Conditions

column BETWEEN x AND y
IS NULL / IS NOT NULL
column LIKE pattern -- eg. ‘%value%’
 

UPDATE

UPDATE table
SET column1 = expression1,
    column2 = expression2,
    ...
[WHERE conditions];
UPDATE table1
SET column1 = (SELECT expression1
               FROM table2
               WHERE conditions)
[WHERE conditions];

UPDATE table1, table2, ...
SET column1 = expression1,
    column2 = expression2,
    ...
WHERE table1.column = table2.column
[AND conditions];

Functions

STRCMP­­("s­t­r­1",­­"­­st­r­2­")
Compare strings
LOWER(­­"­s­tr­­")
UPPER(­­"­s­tr­­")
LTRIM(­­"­s­tr­­")
Left trim
SUBSTR­­IN­G­(­"­st­­r","i­­nx­1­"­­,"in­­x2­")
Substring of a string
CONCAT­­("s­t­r­1",­­"­­st­r­2­")
Concat­­enate
ROUND (colum­n,10)
round number

Aggreg­ations

COUNT
SUM
AVG
MIN
MAX
 

INSERT

INSERT INTO table
(column1, column2, ... column_n )
VALUES
(expression1, expression2, ... expression_n );


INSERT INTO table
(column1, column2, ... )
SELECT expression1, expression2, ...
FROM source_table
[WHERE conditions];

EXCEPT

SELECT expression1, expression2, ... expression_n
FROM tables
[WHERE conditions]
EXCEPT
SELECT expression1, expression2, ... expression_n
FROM tables
[WHERE conditions];

CREATE

CREATE TABLE table_name (
       id INT AUTO_I­NCR­EMENT,
       name VARCHA­R(2),
       surname VARCHA­R(32),
       PRIMARY KEY (id)
);

ALTER TABLE

ALTER TABLE table ADD COLUMN column;
ALTER TABLE table DROP COLUMN column;
ALTER TABLE table ADD PRIMARY KEY (column);

DELETE

DELETE FROM table
[WHERE conditions];