SQL Essentials

What Is SQL?

Write SQL SELECT queries to retrieve, filter, and sort data from database tables using WHERE, AND, OR, LIKE, and ORDER BY.

What Is SQL?

SQL (Structured Query Language) is the standard language for querying and managing relational databases. While MS Access uses a visual Query Design Grid, it generates SQL behind the scenes. Understanding SQL directly is essential for professional database work.

Basic SELECT Syntax

SELECT column1, column2, ...
FROM   table_name
WHERE  condition
ORDER BY column [ASC | DESC];

-- Select all columns:
SELECT * FROM Movie;

-- Select specific columns:
SELECT title, ratingCode FROM Movie;

-- Select with alias:
SELECT title AS MovieTitle, relyear AS Year FROM Movie;

WHERE Clause and Comparisons

-- Exact match:
SELECT title, ratingCode FROM Movie WHERE relyear = 2015;

Comparison operators

Item	Meaning
=	equal
<>	not equal
>	greater than
<	less than
>=	greater than or equal
<=	less than or equal

-- Text match (case-insensitive in Access):
SELECT title FROM Movie WHERE ratingCode = 'PG';

AND and OR Operators

-- AND: both conditions must be true
SELECT title FROM Movie
WHERE relyear = 2013 AND ratingCode = 'M';

-- OR: at least one condition must be true
SELECT title FROM Movie
WHERE relyear = 2013 OR relyear = 2016;

-- Watch out: AND on the same field is often a mistake
SELECT title FROM Movie
WHERE relyear = 2013 AND relyear = 2016;  -- returns 0 rows!

LIKE and Wildcards

Access wildcards

Item	Meaning
*	any string of any length (SQL standard uses %)
?	exactly one character (SQL standard uses _)

-- Titles starting with THE:
SELECT title FROM Movie WHERE title LIKE 'the *';

-- Titles containing 'day':
SELECT title FROM Movie WHERE title LIKE '*day*';

-- In standard SQL:
SELECT title FROM Movie WHERE title LIKE 'The %';
SELECT title FROM Movie WHERE title LIKE '%day%';

ORDER BY and BETWEEN

-- Sort alphabetically by title:
SELECT title, relyear FROM Movie ORDER BY title ASC;

-- Sort by year descending:
SELECT title, relyear FROM Movie ORDER BY relyear DESC;

-- Range filter using BETWEEN:
SELECT title FROM Movie
WHERE relyear BETWEEN 2011 AND 2015;
-- Equivalent to: relyear >= 2011 AND relyear <= 2015

Aggregate Functions

Item	Meaning
COUNT(*)	number of rows
SUM()	total of a numeric column
AVG()	average value
MIN()	minimum value
MAX()	maximum value

-- Count movies per rating:
SELECT ratingCode, COUNT(*) AS Total
FROM   Movie
GROUP BY ratingCode;

-- Average runtime per year (rounded to 1 decimal):
SELECT relyear, ROUND(AVG(runtime), 1) AS AvgRuntime
FROM   Movie
GROUP BY relyear
HAVING AVG(runtime) >= 100;