Structured Query Language (SQL)

EXISTS

Checks if subquery returns any rows.

IS NULL

Checks whether attribute value is null.

BETWEEN

Checks whether attribute value is within a range.

IN

Checks whether attribute value matches any value within a value list.

LIKE

Checks whether attribute value matches given string pattern.

UNION ALL

Clause that combines the result sets of two or more SELECT statements. It does not remove duplicate rows between the various SELECT statements (all rows are returned).

UNION

Clause that combines the results of two SQL queries into a single table of all matching rows.

INTERSECT

Clause that combines two SELECT statements, but returns rows only from the first select statement that are identical to a row in the second select statement.

MINUS

Clause that is used to return all rows in the first SELECT statement that are not returned by the second select statement.

JOIN with Alias

JOIN that uses an alias to identify source table. (Eg. "WHERE P.V_CODE = V.V_CODE").

WHERE CLAUSE

Suited to SELECT records individually.

Comparison Operators

= Equal to, < Less Than, <= Less than or equal to, > Greater than, >= Greater than or equal to, <> or != Not equal to.

VIEW

Virtual table, created with SELECT, provides different ways of "seeing" the same data. Enforces security and can be read-only or updatable.

Arithmetic Operators

+ Add, - Subtract, * Multiply, / Divide, ^ Raise to the power of.

Subquery (Nested Query)

A query inside a query, normally inside parentheses.

D.I.E

Add, modify, or delete table rows.

SQL Index

Can be created on a basis of any selected attribute, composite form is based on two or more attributes, often used to prevent data duplication.

SELECT COUNT

Counts instances from query result.

DROP TABLE

Delete a table from the database.

DELETE

Deletes a table row.

Integrity Constraints

Ex. NOT NULL, UNIQUE, CHECK, DEFAULT

SELECT SUM

Lists sum of query result.

SELECT DISTINCT

Lists unique values.

Recursive Join

Joining a table to itself.

SELECT AVG

Lists average from query result.

MIN & MAX

Lists extreme values from query result.

UPDATE

Modify data in a table.

Binary Opertation

Operation with more than one operand.

Unary Operation

Operation with only one operand.

INC

Order ascending.

DESC

Order descending.

ORDER BY

Orders a listing.

JOIN

Performed when data are retrieved from more than one table at a time. Generally composed of an equality comparison between foreign key and primary key of related tables.

COMMIT

Permanently saves any changes made to any table in the database. (Only works with data manipulation commands that are used to add, modify, or delete table rows).

RIGHT JOIN

Preserves the unmatched rows from the second table, joining them with a NULL in the shape of the first table.

TRUNCATE TABLE

Removes all records from "table-name"; shorthand for DELETE.

LEFT JOIN

Returns all rows from the left table, with the matching rows in the right table.

DESCRIBE TABLE

Shows the details of the table structure.

Outer Query

The first query.

Inner Query

The inside query (Executed first).

ALTER TABLE

Used to alter table definition. Followed by the keyword that produces specific change.

INSERT

Used to enter data into table. Can be used to paste data from existing table.

SELECT

Used to list contents of table.

ROLLBACK

Used to restore database to its previous condition. (Only works with data manipulation commands that are used to add, modify, or delete table rows).

GROUP BY

Used when you have attribute columns combined with aggregation functions in the select statement (e.g. COUNT, MAX, MIN, SUM, AVG).

HAVING CLAUSE

Used with GROUP BY clause, good for evaluating conditions that applies to a group of records. (E.g. ... GROUP BY V_CODE HAVING AVG(P_PRICE) < 10;