SQL Notes

sql-notes.md

Statements

Defining How Your Data Is Stored

CREATE DATABASE ...

... creates a database with the given name...

CREATE [OR REPLACE] DATABASE [IF NOT EXISTS] database_name; 

DROP DATABASE ...

... drop an existing database...

DROP DATABASE [IF EXISTS] database_name; 

USE ...

Use the database_name database as the default (current) database for subsequent statements.

USE database_name

CREATE TABLE ...

... create a new table...

CREATE TABLE [OR REPLACE] table_name [IF NOT EXISTS] (
    column1 datatype constraint,
    column2 datatype constraint,
    column3 datatype constraint,
   ....
); 

CREATE TABLE new_table_name AS
    SELECT column1, column2,...
    FROM existing_table_name
    WHERE ....; 

ALTER TABLE ...

... alter an existing table's definition...

ALTER TABLE table_name
ADD column_name datatype; 

ALTER TABLE table_name
ALTER COLUMN column_name datatype; 

ALTER TABLE table_name
DROP COLUMN column_name; 

DROP TABLE ...

... drop an existing table...

DROP TABLE table_name [IF EXISTS]; 

DESCRIBE ...

Provides information about the columns in a table...

DESCRIBE table_name; 

Manipulating Your Data

INSERT INTO ... VALUES ...

... add (or insert) new data...

INSERT INTO table_name (column1, column2, column3, ...)
VALUES (value1, value2, value3, ...); 

INSERT INTO table_name
VALUES (value1, value2, value3, ...); 

SELECT ... FROM ...

... read (or select) your data...

SELECT column1, column2, ...
FROM table_name;

SELECT * FROM table_name; 

UPDATE ... SET ... WHERE ...

... change (or update) existing data...

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

DELETE FROM ... WHERE ...

... remove (or delete) existing data...

DELETE FROM table_name WHERE condition;

REPLACE INTO ... VALUES ...

... add or change (or replace) new or existing data...

REPLACE INTO table_name

TRUNCATE ...

... empty (or delete) all data from the template...

TRUNCATE TABLE table_name; 

CASE

CASE
    WHEN condition1 THEN result1
    WHEN condition2 THEN result2
    WHEN conditionN THEN resultN
    ELSE result
END; 

CLAUSES

Constraints

• NOT NULL: Ensures that a column cannot have a NULL value
• UNIQUE: Ensures that all values in a column are different
• PRIMARY KEY: A combination of a NOT NULL and UNIQUE. Uniquely identifies each row in a table
• FOREIGN KEY: Uniquely identifies a row/record in another table
• CHECK: Ensures that all values in a column satisfies a specific condition
• DEFAULT: Sets a default value for a column when no value is specified
• INDEX: Used to create and retrieve data from the database very quickly

Operators

• LIKE:
• IN: eg. ... WHERE column_name IN (value1, value2, ...); or ... WHERE column_name IN (SELECT STATEMENT);
• BETWEEN: inclusive eg. ... WHERE column_name BETWEEN value1 AND value2;

Functions

• MIN
• MAX
• COUNT
• AVG
• SUM

Internal Storage

Normal Forms

NF1

NF2

NF3