Notes on ‘SQL: The Complete Reference’ (1)

SQL, or Structured Query Language, has syntax and features that vary depending on the specific DBMS (Database Management System) implementation. The following examples include only general SQL and MySQL examples.

Database

• Database Management System (DBMS)
• Database (database)
• Table (table)
  • Schema (schema): Describes the information of the table.
  • Table names must be unique within the same database.
• Column (column)
  • Data type (datatype)
• Row (row)
  • Also known as a record
• Primary Key (primary key)
  • Uniquely identifies each row in the table, cannot be changed, and cannot be reused after deletion.
  • Can be a combination of multiple columns as a composite key.

Retrieval

SELECT

• If no sort order is specified, the output order may vary.
• Ends with a semicolon ;, logically split into multiple lines.
• SQL keywords are written in uppercase for readability.
• Clause (clause)

SELECT prod_name
FROM Products;

Retrieving Multiple Columns

Separate columns with commas ,, no comma after the last column.

SELECT prod_id, prod_name, prod_price
FROM Products;

Retrieving All Columns

The * wildcard is used to retrieve all columns.

SELECT *
FROM Products;

Retrieving Unique Values

The DISTINCT keyword only returns unique values and applies to all specified columns.

SELECT DISTINCT vend_id
FROM Products;

Limiting Results

• The first row is indexed as 0.

Use the LIMIT keyword to return a maximum of n rows.

SELECT prod_name
FROM Products
LIMIT 5;

Use the OFFSET keyword to start counting from the nth row.

SELECT prod_name
FROM Products
LIMIT 3 OFFSET 4;

A shorthand version, where the first value is OFFSET and the second is LIMIT.

SELECT prod_name
FROM Products
LIMIT 4, 3;

Comments

Use --, #, or /* */ for comments.

SELECT prod_name -- This is a comment
FROM Products;

# This is a comment
SELECT prod_name 
FROM Products;

/* This is
a multi-line comment */
SELECT prod_name 
FROM Products;

Sorting Data

Ordering Data

The ORDER BY keyword can only be placed at the end of the query.

SELECT prod_name 
FROM Products
ORDER BY prod_name;

Sorting by multiple fields.

SELECT prod_name 
FROM Products
ORDER BY prod_price, prod_name;

Sorting by column position; the following example sorts by prod_price, prod_name.

SELECT prod_name, prod_price, prod_name
FROM Products
ORDER BY 2, 3;

Sorting Direction

ASC is the default ascending order (A to Z), and DESC is descending order (Z to A). Sorting is case-insensitive by default; “A” and “a” are treated the same unless settings are changed.

SELECT prod_name, prod_price, prod_name
FROM Products
ORDER BY prod_price DESC;

Filtering Data

WHERE Clause

• Search criteria
• Filter criteria

SELECT prod_name, prod_price, prod_name
FROM Products
WHERE prod_price = 3.49;

WHERE Clause Operators

Checking Single Values

SELECT prod_name, prod_price
FROM Products
WHERE prod_price < 10;

When comparing strings, use quotation marks.

SELECT prod_name, prod_price
FROM Products
WHERE prod_name != 'abc';

BETWEEN matches a range, including both endpoints.

SELECT prod_name, prod_price
FROM Products
WHERE prod_price BETWEEN 0 AND 10;

Null value check, matches only NULL, not 0.

SELECT prod_name, prod_price
FROM Products
WHERE prod_price IS NULL;

Combining WHERE Clauses

Operator

AND operator.

SELECT prod_name, prod_price
FROM Products
WHERE vend_id = 'D1' AND prod_price <= 4;

OR operator.

SELECT prod_name, prod_price
FROM Products
WHERE vend_id = 'D1' OR vend_id = 'D2';

IN operator.

SELECT prod_name, prod_price
FROM Products
WHERE vend_id IN ( 'D1', 'D2' );

NOT operator.

SELECT prod_name, prod_price
FROM Products
WHERE NOT vend_id = 'D1';

Order of Evaluation

AND has a higher precedence than OR. Multiple operators should be separated by parentheses ().

Filtering with Wildcards

• Wildcard (wildcard)
• Search pattern (search pattern)
• Predicate (predicate)

The percent symbol % is a wildcard that matches zero or more characters but does not match NULL.

SELECT prod_name, prod_price
FROM Products
WHERE prod_name LIKE '%Fish%';

SELECT prod_name, prod_price
FROM Products
WHERE prod_name LIKE '[^JM]%';

• Searches are case-insensitive.
• Avoid overusing wildcards; use other operators first.
• If necessary, place the wildcard at the end of the search pattern.

Calculated Fields

• Field (field)
• Concatenate (concatenate)

Concatenating Fields

|| operator.

SELECT vend_name + ' (' + vend_country + ')'
FROM Vendors
ORDER BY vend_name;

MySQL uses the Concat function.

SELECT Concat(vend_name, ' (', vend_country, ')')
FROM Vendors
ORDER BY vend_name;

Aliases

• Alias (alias)
• Derived column (derived column)

The AS operator can be used to rename fields, especially when the field name is illegal or confusing.

SELECT vend_name AS vend_title
FROM Vendors
ORDER BY vend_name;

Arithmetic Calculations

Add, subtract, multiply, divide +-*/.

SELECT prod_id, quantity, item_price, quantity*item_price AS expanded_price
FROM OrderItems
WHERE order_num = 20008;