Skip to content

SQL.md

Latest commit

 

History

History
284 lines (265 loc) · 6.72 KB

SQL.md

File metadata and controls

284 lines (265 loc) · 6.72 KB
Raw

SQL Cheatsheet

Domain types

CHAR(n)       -- fixed-length character string, with user-specified length.
VARCHAR(n)    -- variable-length character string, with user-specified maximum length.
INT           -- (also: INTEGER) an integer (length is machine-dependent).
DATE          -- a calendar date, containing four digit year, month, and day of the month.
TIMESTAMP     -- the time of the day in hours, minutes, and seconds.
FLOAT(n)      -- floating-point, with user-specified precision of at least n digits.
SMALLINT      -- a small integer (length is machine-dependent).
NUMERIC(p, d) -- a fixed-point number with user-specified precision, consists of p digits (plus a sign) and d of p digits are to the right of the decimal point. E.g., numeric(3, 1) allows 44.5 to be stored exactly but not 444.5.
REAL          -- (also: DOUBLE PRECISION) floating-point or double-precision floating-point numbers, with machine-dependent precision.

Operator order

SELECT
FROM
WHERE
GROUP BY
HAVING
ORDER BY

Basic create

CREATE TABLE book(
  title VARCHAR(256),
  authors VARCHAR(256),
  format CHAR(9) DEFAULT 'paperback',
  pages INT,
  year DATE,
  ISBN13 CHAR(14)
);

Insert

Insert all values in the order defined during table creation:

INSERT INTO student VALUES(...), (...), ...;

Insert only values indicated by the ones list and in that order:

INSERT INTO student (email, name, faculty, department)
VALUES (...), (...), ...;

Delete table

DROP TABLE loan;

Query

Display all columns from table:

SELECT * FROM table_name;

Display indicated columns from table:

SELECT column_name, column_name
FROM table_name;

Display indicated columns from table with condition:

SELECT name, email
FROM student
WHERE department='CS';

Select from multiple tables:

SELECT student.name, book.title
FROM student, copy, book
WHERE student.email=copy.owner
AND copy.book=book.ISBN13;

Renaming colums for output (purely cosmetic):

SELECT s.name AS owner
FROM loan l, student s
WHERE s.email=l.owner
  AND l.returned > '2010-03-04'
  AND l.borrower = 'abc@mail.com';

Removing duplicates in a single row:

SELECT DISTINCT nationality
FROM student;

Selecting only unique tuples:

SELECT DISTINCT nationality, last_name
FROM student;

Ordering (default asc):

SELECT name
FROM student
ORDER BY matric_num DESC;

Multiple ordering (default asc):

SELECT name
FROM student
ORDER BY nationality, name;     -- order first by nationality then by name within same nationality

Arithmetic:

SELECT book, price * 1.17
AS priceGST
FROM catalog;

Union

Note: The UNION operator selects only distinct values by default. To allow duplicate values, use UNION ALL

SELECT column_name(s) FROM table1
UNION
SELECT column_name(s) FROM table2;

Minus

TODO

Group

Splitting up table into buckets.
Note: whatever columns that appear in SELECT must also appear in GROUP BY

SELECT book        -- selects the book from each book bucket (works exactly like UNIQUE)
FROM loan
GROUP BY book;
SELECT borrower, borrowed_date, COUNT(book)
FROM loan
GROUP BY borrower, borrowed_date;  -- unlike ORDER BY, order doesn't matter here
SELECT l.borrower
FROM loan l
GROUP BY l.borrowed, l.borrower
HAVING COUNT(l.book) > 1;          -- aggregate conditionals on GROUP BY (cannot use WHERE). It applies to each group bucket

Aggregate queries

E.g. MAX(), MIN(), AVG(), STD(), SUM() etc
NOTE: aggregate functions cannot be used in the WHERE clause

SELECT COUNT(*) FROM book;                          -- counts all entries in book
SELECT COUNT(title) from book;                      -- counts non NULL entries in title
SELECT COUNT(DISTINCT column_name) FROM table_name; -- counts number of distinct values from table
SELECT NATIONALITY, AVG(SALARY)                     -- prints the average salary of entries for each nationality bucket
FROM EMPLOYEE
GROUP BY NATIONALITY;

Basic update

UPDATE table_name
SET column1=value1, column2=value2, ...
WHERE some_column=some_value;

Delete entries

DELETE FROM table_name;  -- delete all of table entires but keep table 
DELETE FROM table_name   -- delete entries that falls within condition
WHERE some_column=some_value; -- note: quotes have to be placed also on boolean values. e.g. some_bool='TRUE'

Modify entries

ALTER TABLE table_name        -- add new column
ADD column_name domain_type;
ALTER TABLE table_name        -- modify exisitng column
MODIFY column_name datatype
ALTER TABLE table_name        -- delete column
DROP COLUMN column_name

Integrity constraints

Primary key

Automatically enforces NOT NULL and UNIQUE

ISBN13 CHAR(14) PRIMARY key     -- singular
PRIMARY KEY (owner, book, copy) -- composite

Foreign key

Singular:

column_name VARCHAR(256) REFERENCES other_table(other_table_PK)     -- singular

Composite

FOREIGN KEY (owner, book, copy) REFERENCES copy(owner, book, copy)  -- composite

Update/delete rules:

-- ON UPDATE/DELETE
  CASCADE
  NO ACTION
  SET DEFAULT 
  SET NULL

NOT NULL

ISBN10 CHAR(10) NOT NULL

Unique

NOTE: Does not enforce NOT NULL. i.e. permits multiple null values)
Singular:

ISBN10 CHAR(10) UNIQUE

Composite:

UNIQUE (first_name, last_name)

Check

Column check:

age INT CHECK(age>0)

Table constraint without naming:

check (death_date > birth_date OR death_date is NULL)

Table constraint with naming to help identify violations:

CONSTRAINT positive_val CHECK(age>0)

CHECK constraints that are declared outside tables:

CREATE ASSERTION no_student_staff
  CHECK(NOT EXISTS (SELECT * FROM staff, student WHERE staff.id = student.id))

Tuple variables (alias)

SELECT s.name, b.title
FROM student s, copy c, book b
WHERE s.email=c.owner
AND c.book=b.ISBN13;

Conditionals

email like('%@%.com') -- where % is the wildcard character
format = 'paperback' OR format = 'hardcover'

Date format

specifies the default date format to use with the TO_CHAR and TO_DATE functions

alter session set NLS_DATE_FORMAT = 'YYYY-MM-DD'

Triggers

A trigger is a piece of SQL to execute either before or after an update, insert, or delete in a database

CREATE TRIGGER trigger_name
AFTER UPDATE
  INSERT INTO CustomerLog (column1, ...)
  SELECT column1, ... FROM deleted

Misc

Conitionals can be TRUE/FALSE or UNKNOWN for cases dealing with NULL values

set define off  -- disable special character such as '&'
O''reilly       -- single quotes are escaped by doubling