Database Systems - SQL Syntax

Data Types

• INT,

• VARCHAR(1) [specify length],

• DATE [YYYY-MM-DD],

• TIMESTAMP [YYYY-MM-DD HH:MM:SS]

Constraints

• NOT NULL [Ensures that a column cannot have a NULL value]

• UNIQUE [Ensures that all values in a column are different]

• PRIMARY KEY [Uniquely identifies each row in a table]

• FOREIGN KEY [Prevents actions that would destroy links between tables]

• DEFAULT [Sets a default value for a column if no value is specified]

• CREATE INDEX [Used to create and retrieve data from the database very quickly]

Create new table

CREATE TABLE table_name (
    column1 datatype,
    column2 datatype,
    column3 datatype
);

Add new column

ALTER TABLE table_name
ADD column_name datatype;

Insert into / Add new row …

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

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

Update, change, modify…

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

Delete table

DROP TABLE table_name;

Delete all rows from table

DELETE FROM table_name;

Delete specific row from table

DELETE FROM table_name

WHERE condition;

Delete column from table

ALTER TABLE tablename
DROP COLUMN columnname;

Display… / List …

SELECT column1, column2, ...
FROM table_name;

Display the different …

SELECT DISTINCT column1, column2, ...
FROM table_name;

Display __ who / that / from …

SELECT column1, column2, ...
FROM table_name
WHERE condition;

Sort in ascending / descending order

SELECT column1, column2, ...
FROM table_name
ORDER BY column1, column2, ... ASC|DESC;

[alphabetically-ASC /

reverse alphabetically-DESC]

AND Operator

SELECT column1, column2, ...
FROM table_name
WHERE condition1 AND condition2 AND condition3 ...;

Combined AND and OR

SELECT * FROM table_name
WHERE condition1 AND ( condition2 OR condition3 );

OR Operator

SELECT column1, column2, ...
FROM table_name
WHERE condition1 OR condition2 OR condition3 ...;

NOT Operator

SELECT column1, column2, ...
FROM table_name
WHERE NOT condition;

SELECT *
FROM table_name
WHERE column1 NOT IN / BETWEEN | NOT …;

NULL | Missing data… / not missing data…

SELECT column_names
FROM table_name
WHERE column_name IS NULL;

SELECT column_names
FROM table_name
WHERE column_name IS NOT NULL;

Select top …

SELECT TOP number|percent column_name(s)
FROM table_name
WHERE condition;

…

ORDER BY column1, column2, … ASC/DESC

Display minimum, lowest, smallest, earliest…

SELECT MIN(column_name)
FROM table_name
WHERE condition;

Display maximum, largest, latest…

SELECT MAX(column_name)
FROM table_name
WHERE condition;

Number of… | Number of distinct…

SELECT COUNT/COUNT(DISTINCT column_name)
FROM table_name
WHERE condition;

Display the Sum… , Total amount…

SELECT SUM(column_name)
FROM table_name
WHERE condition;

Display the Average

SELECT AVG(column_name)
FROM table_name
WHERE condition;

Starts with…

SELECT column1, column2, ...
FROM table_name
WHERE columnN LIKE ‘A%’;

Ends with…

SELECT column1, column2, ...
FROM table_name
WHERE columnN LIKE ‘%A’;

Contains ‘xyz’ …

SELECT column1, column2, ...
FROM table_name
WHERE columnN LIKE ‘%A%’;

Wildcard characters

SELECT column1, column2, ...
FROM table_name
WHERE columnN LIKE ‘A_bc’;

Multiple OR conditions

SELECT column_name(s)
FROM table_name
WHERE column_name IN (value1, value2, ...);

SELECT column_name(s)
FROM table_name
WHERE column_name NOT IN (value1, value2, ...);

Display between range/ outside range…

SELECT column_name(s)
FROM table_name
WHERE column_name BETWEEN value1 AND value2;

SELECT column_name(s)
FROM table_name
WHERE column_name NOT BETWEEN value1 AND value2;

Aliases

SELECT column_name AS alias_name
FROM table_name;

[If the alias contains spaces]:

SELECT column_name AS [alias_name]
FROM table_name;

SELECT column1 + ‘ ‘ + column2 AS alias_name
FROM table_name AS alias_name;

Inner JOIN : Display _ and their …

SELECT column_name(s)
FROM table1
JOIN table2
ON table1.column_name = table2.column_name;

[Matching values on both tables]

Left / Right JOIN : Display _ even if they have no …

SELECT column_name(s)
FROM table1
LEFT JOIN table2
ON table1.column_name = table2.column_name;

SELECT column_name(s)
FROM table1
RIGHT JOIN table2
ON table1.column_name = table2.column_name;

Display _ who have never / have no …

SELECT column_name(s)
FROM table1
LEFT JOIN table2
ON table1.column_name = table2.column_name

WHERE table2.othercolumn_name IS NULL;

[Displays only unmatched rows from join]

Outer JOIN : Combine every record from tables…

SELECT column_name(s)
FROM table1
FULL OUTER JOIN table2
ON table1.column_name = table2.column_name
WHERE condition;

Self JOIN : Display _ that are from the same …

SELECT column_name(s)
FROM table1 T1, table1 T2
WHERE condition;

UNION : Display _ from both tables

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

[Union selects only distinct values]

Display per … , grouped based on …, by each…

SELECT column_name(s)
FROM table_name
WHERE condition
GROUP BY column_name(s)
ORDER BY column_name(s);