SQL (Structured Query Language) is used for retrieving and managing data in relational databases. This page will give you an overview of the general syntax.

Some useful data types are:

Advertisement

INT[EGER], SHORTINT, FLOAT [REAL], DECIMAL(a, b), VARCHAR(n), CHAR(n), BIT(n), BOOLEAN, NULL, DATE, TIME, TIMESTAMP

which should be self-explanatory.

Basic SQL Query

SELECT <Attribute(s)> -- What?
FROM <Relation(s)> -- Where from?
WHERE <Condition(s)> -- With what condition?

Equivalent meaning in relational algebra:

  • Projection: SELECT
  • Restriction/Selection: WHERE

If no projection is intended, use the asterisk operator: SELECT * FROM somewhere;

Projections as defined above can result duplicate attributes. This means that the regular outcome of a SELECT statement can be mathematically generalized as a multiset (or bag). Nevertheless, we also have the ability to generate tuples with set-property (collection without duplicates) by simply using the DISTINCT keyword.

SELECT DISTINCT name FROM Employee;

Types of SQL Statements

Data Definition Language (DDL)

Statements used to define the database structure/schema.
These include: CREATE, ALTER, DROP, TRUNCATE, COMMENT, RENAME.

Data Manipulation Language (DML)

Statements used for managing data within tables.
These include: SELECT, INSERT, UPDATE, DELETE

Data Control Language (DCL)

GRANT and REVOKE user’s access privileges.

Order Of Operations

1. FROM
2. WHERE
3. GROUP BY
4. HAVING
5. SELECT
6. ORDER BY

This is important in regards to determining on how results turn out to be!

Creating Tables

The general syntax looks like this:

CREATE TABLE table_name (
    columnName1 <type> <constraint>
    columnName2 <type> <constraint>
    columnName3 <type> <constraint>
);

The following table lists available constraint keywords.

Syntax Meaning
NOT NULL Must not store NULL value
UNIQUE Unique value for each row
PRIMARY KEY Combination of NOT NULL and UNIQUE
FOREIGN KEY Reference to data in another table (see referential integrity)
CHECK Value has to meet specific requirement
DEFAULT Specifies default value

Examples (excerpts):

columnName INTEGER NOT NULL
columnName INTEGER UNIQUE NOT NULL
columnName INTEGER UNIQUE
columnName INTEGER PRIMARY KEY
columnName INTEGER DEFAULT 5
columnName INTEGER CHECK(columnName < 10)

Constraints can be applied to multiple columns (also works for just one) through the following syntax:

CONSTRAINT constraint_name <constraint> (column1, column2...)

To sum it up, a short example showcasing most things from above:

CREATE TABLE Employee (
    FirstName VARCHAR(255),
    LastName VARCHAR(255),
    SupID INT NOT NULL,
    City VARCHAR(255),
    Smile INT,

    CONSTRAINT pk_employee PRIMARY KEY(FirstName, LastName),
    CONSTRAINT fk_boss FOREIGN KEY SupID REFERENCES Superiors(BossID),
    CONSTRAINT chk_good CHECK (City = 'GoodCity' AND Smile > 10)
);

Set Comparison

An example with constants:

SELECT * FROM somewhere
WHERE id IN (1, 2, 3);

Nested query as comparison:

SELECT attribute FROM somewhere
WHERE id IN (SELECT id
             FROM somewhereElse
             WHERE otherAttribute = 'example');

Exists

Select all departments associated with at least one employee.

SELECT * FROM Department 
WHERE EXISTS (SELECT * FROM Employees WHERE depId = id);

Quantors

These should be self-explanatory (ANY and SOME are synonyms).

SELECT * FROM Department
WHERE place = ANY(SELECT name FROM Places);

SELECT * FROM Department 
WHERE place = SOME(SELECT name FROM Places);

SELECT * FROM Employees
WHERE salary >= ALL(SELECT salary FROM Employees);

Set Operations

Note that both tables have to have the same amount of columns, which furthermore have similar domains to each other.
Results contain distinct attribute columns.

UNION [ALL]

SELECT * FROM LeftTable UNION (SELECT * FROM RightTable);
SELECT * FROM LeftTable UNION ALL SELECT * FROM RightTable;

The union operator removes duplicate rows by default.

INTERSECT [ALL]

The intersect operator removes duplicate rows by default.

EXCEPT [ALL]

The except operator removes duplicate rows by default.

Joins

Syntax Explanation
INNER JOIN or JOIN Result set matching both tables
LEFT OUTER JOIN or LEFT JOIN Returns all rows from the left table, unmatched attributes from the right table are respectively filled with NULL
RIGHT OUTER JOIN or RIGHT JOIN Returns all rows from the left table, unmatched attributes from the right table are respectively filled with NULL
FULL OUTER JOIN or FULL JOIN Returns matching rows from the left and right table, an unmatched row gets respectively filled with NULL values
CROSS JOIN Returns the cartesian product of both tables
NATURAL JOIN Join via columns with equal names (result consists of distinct attribute columns)

Note to cross join: *each row in the first table gets concatenated with every row in the second table (new size: size of first table multiplied with the size of our second table)*

We are also able to join a table with itself (Self Join), by defining aliases:

SELECT p1.firstname AS "Employee", p2.firstname AS "Superior"
FROM Person AS p1, Person AS p2
WHERE p1.superior = p2.id;

Equi-join via using() (distinct attribute columns as in natural-join):

SELECT * FROM Person JOIN Department USING(firstname, surname);

Aggregation Functions

Built-in functions, which are applicable on single columns.

Syntax Meaning
MIN(<attribute>) Minimum attribute value
MAX(<attribute>) Maximum attribute value
COUNT(<attribute>) Amount of rows (for which its attribute value is not null)
COUNT(*) Amount of rows
COUNT(DISTINCT <attribute>) Amount of different attribute values
AVG(<attribute>) Average of numbers
SUM(<attribute>) Sum of numbers
SUM(DISTINCT <attribute>) SUM(DISTINCT <attribute>) No duplicates (see above)

Examples:

SELECT AVG(salary) FROM Employees WHERE depId = 5; -- Returns average salary
SELECT COUNT(DISTINCT lastname) FROM Employees; -- Returns amount of different lastnames (no duplicates)
SELECT COUNT(*) AS Amount
FROM Employees e, Employees b
WHERE e.bossId = b.id AND e.salary > b.salary; -- Returns amount of employees, who earn more than their defined superior

Having

  1. Restriction before grouping: WHERE
  2. Restriction after grouping: HAVING

Sorting

Task: Give me all employees sorted by their underlying surnames, which have salary greater than 2000.

SELECT firstname, surname
FROM Employee
WHERE Salary > 2000
ORDER BY surname;

The above query sorts our desired records in ascending order, considering we did not specify the optional keyword DESC to ORDER BY. Therefore it isn’t necessary to use the ASC keyword, as sorting defaults to that.

ORDER BY surname DESC -- Descending order
ORDER BY surname ASC -- Ascending order (default)

It is also possible to sort multiple columns, one after another.

ORDER BY surname DESC, firstname ASC 

This would sort surnames in a descending order first, and then firstnames by ascending (optional) order.

Grant/Revoke Privilege

General syntax:

GRANT <Privileges> ON <TableName> TO <UserName>;
GRANT SELECT, INSERT ON Employee TO SubAdmin 

Available privileges:

Privilege Description
SELECT Ability to perform SELECT statement
INSERT Ability to perform INSERT statement
UPDATE Ability to perform UPDATE statement
DELETE Ability to perform DELETE statement
REFERENCES Ability to create a reference constraint
ALTER Ability to change the table definition
ALL Grants SELECT, INSERT, UPDATE; DELETE and REFERENCES

Views

General syntax:

CREATE VIEW ViewName AS
<statement>;

An example:

CREATE VIEW GetTopEmployees AS
SELECT firstname, surname, salary
FROM Employee
WHERE rank <= 50
ORDER BY rank, salary ASC;

Trigger

General syntax:

CREATE TRIGGER TriggerName
{BEFORE | AFTER | INSTEAD OF}
{INSERT | UPDATE | DELETE} [OF <column(s)>] ON TableName
REFERENCING NEW ROW AS newrow
FOR EACH ROW WHEN (<condition>)
<statement>;

Assertions

CREATE ASSERTION allows us to specify specific constraint rules (same as the previous CHECK usage in our table creation).

CREATE ASSERTION AssertionName
CHECK (
NOT EXISTS (SELECT * FROM Employees WHERE salary < 1000)
);

Make sure that there is no employee with a salary less than 1000.