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

Functions

The implementation of SQL functions varies significantly across different DBMS, making them unportable.

Common SQL function categories:

Type Purpose
Text Functions Handle text strings (remove, pad, change case, etc.)
Numeric Functions Perform arithmetic operations on numeric data (absolute value, algebraic operations)
Date and Time Functions Handle date and time values (calculate date differences, check date validity, etc.)
System Functions Return special information used by the DBMS (e.g., user login information)

Text Processing Functions

Function Description
LEFT() Returns the leftmost characters of a string
LENGTH() Returns the length of a string
LOWER() Converts to lowercase
LTRIM() Removes leading spaces
RIGHT() Returns the rightmost characters of a string
RTRIM() Removes trailing spaces
SOUNDEX() Returns the SOUNDEX value of a string
UPPER() Converts to uppercase

SOUNDEX is a function that converts a string to a code representing its sound. For example, Peterson will be converted to P362.

SELECT cust_name, cust_contact
FROM Customers
WHERE SOUNDEX(cust_contact) = SOUNDEX('Michael Green')

Date and Time Processing Functions

Function Description
AddDate() Adds a date (day, week, etc.)
AddTime() Adds time (hours, minutes, etc.)
Now() Returns the current date and time
CurDate() Returns the current date
CurTime() Returns the current time
Date() Returns the date part of a datetime
DateDiff() Calculates the difference between two dates
Date_Add() Highly flexible date operation function
Date_Format() Returns a formatted date or time string
DayOfWeek() For a date, returns the corresponding day of the week
Time() Returns the time part of a datetime
Year() Returns the year part of a date
Month() Returns the month part of a date
Day() Returns the day part of a date
Hour() Returns the hour part of a time
Minute() Returns the minute part of a time
Second() Returns the second part of a time

Numeric Processing Functions

Function Description
ABS() Absolute value
COS() Cosine
EXP() Exponential value
PI() Value of Pi
SIN() Sine
SQRT() Square root
TAN() Tangent

Summarizing Data

  • Aggregate function
Function Description
AVG() Average value
COUNT() Number of rows in a column
MAX() Maximum value
MIN() Minimum value
SUM() Sum 
SELECT AVG(prod_price) AS avg_price
FROM Products;
  • COUNT(*) includes null values.

Grouping Data

Creating Groups

GROUP BY clause must be placed after WHERE and before ORDER BY.

SELECT vend_id, COUNT(*) AS num_prods
FROM Products
GROUP BY vend_id;

Filtering Groups

The HAVING clause is used to filter groups.

SELECT vend_id, COUNT(*) AS num_prods
FROM Products
GROUP BY vend_id
HAVING COUNT(*) > 2;

Subqueries

  • Query (query)
  • Subquery (subquery)
SELECT cust_id
FROM Orders
WHERE order_num IN (SELECT order_num 
                    FROM OrderItems
                    WHERE prod_id = 'D1')
  • Subqueries are always processed from the inside out.

Joining Tables

  • Join (join)
  • Scale (scale)
  • Cartesian Product (cartesian product)
  • Cross Join (cross join)
  • Equijoin (equijoin)
  • Inner Join (inner join)
  • Self-Join (self-join)
  • Natural Join (natural join)
  • Outer Join (outer join)

Creating Joins

SELECT vend_name, prod_name, prod_price
FROM Vendors, Products
WHERE Vendors.vend_id = Products.vend_id;

Inner Join

INNER JOIN xxx ON xxxx

SELECT vend_name, prod_name, prod_price
FROM Vendors INNER JOIN Products
ON Vendors.vend_id = Products.vend_id;

Table Aliases

SELECT cust_name, cust_contact
FROM Customers AS C, Orders AS O, OrderItems AS OI
WHERE C.cust_id = O.cust_id
    AND OI.order_num = O.order_num
    AND prod_id = 'D1'
  • Self-joins are faster than subqueries, so joins should be used whenever possible.

Combining Queries

UNION keyword

SELECT cust_name, cust_contact, cust_email
FROM Customers
WHERE cust_state IN ('IL', 'IN', 'MI')
UNION
SELECT cust_name, cust_contact, cust_email
FROM Customers
WHERE cust_name = 'a'

Equivalent to:

SELECT cust_name, cust_contact, cust_email
FROM Customers
WHERE cust_state IN ('IL', 'IN', 'MI')
    OR cust_name = 'a'
  • Each query must contain the same columns.
  • Data types do not need to be identical, but they must be compatible (convertible).
  • UNION removes duplicates by default; use UNION ALL if you want to keep them.
  • Only one ORDER BY clause is allowed, and it must be placed at the end.