SQL Queries

What is SQL?

SQL (Structured Query Language) is the standard language for communicating with databases. It allows you to retrieve, insert, update, and delete data.

Basic SELECT Statement

The SELECT statement retrieves data from a database:

SELECT column1, column2, ...
FROM table_name;

Examples

-- Select specific columns
SELECT FirstName, LastName
FROM Students;

-- Select all columns
SELECT *
FROM Students;

-- Select with alias (rename column in output)
SELECT FirstName AS "First Name", LastName AS "Surname"
FROM Students;

WHERE Clause

Filter results using conditions:

SELECT column1, column2
FROM table_name
WHERE condition;

Comparison Operators

Operator	Description	Example
`=`	Equal to	`WHERE Age = 15`
`<>` or `!=`	Not equal to	`WHERE Status <> 'Inactive'`
`>`	Greater than	`WHERE Price > 100`
`<`	Less than	`WHERE Quantity < 10`
`>=`	Greater than or equal	`WHERE Score >= 50`
`<=`	Less than or equal	`WHERE Rating <= 3`

Logical Operators

-- AND - both conditions must be true
SELECT * FROM Products
WHERE Price > 50 AND Category = 'Electronics';

-- OR - either condition can be true
SELECT * FROM Students
WHERE Grade = 10 OR Grade = 11;

-- NOT - reverses the condition
SELECT * FROM Orders
WHERE NOT Status = 'Cancelled';

-- Combining operators
SELECT * FROM Products
WHERE (Category = 'Books' OR Category = 'Music')
AND Price < 30;

Special Operators

-- BETWEEN - range of values (inclusive)
SELECT * FROM Products
WHERE Price BETWEEN 10 AND 50;

-- IN - matches any value in a list
SELECT * FROM Students
WHERE YearLevel IN (10, 11, 12);

-- LIKE - pattern matching
SELECT * FROM Customers
WHERE LastName LIKE 'Sm%';    -- Starts with 'Sm'
WHERE Email LIKE '%@gmail.com'; -- Ends with '@gmail.com'
WHERE Name LIKE '_an';         -- Any char + 'an' (Dan, Jan, etc.)

-- IS NULL - checks for empty values
SELECT * FROM Contacts
WHERE Phone IS NULL;

ORDER BY Clause

Sort your results:

-- Ascending order (default)
SELECT * FROM Students
ORDER BY LastName ASC;

-- Descending order
SELECT * FROM Products
ORDER BY Price DESC;

-- Multiple columns
SELECT * FROM Students
ORDER BY YearLevel DESC, LastName ASC;

LIMIT Clause

Restrict the number of results:

-- Get first 10 results
SELECT * FROM Products
ORDER BY Price DESC
LIMIT 10;

-- Skip first 5, then get 10
SELECT * FROM Products
ORDER BY ProductName
LIMIT 10 OFFSET 5;

Aggregate Functions

Perform calculations on groups of rows:

Function	Purpose	Example
`COUNT()`	Counts rows	`SELECT COUNT(*) FROM Students`
`SUM()`	Adds values	`SELECT SUM(Price) FROM Orders`
`AVG()`	Calculates average	`SELECT AVG(Score) FROM Tests`
`MAX()`	Finds highest value	`SELECT MAX(Price) FROM Products`
`MIN()`	Finds lowest value	`SELECT MIN(Age) FROM Users`

GROUP BY Clause

Group rows that have the same values:

-- Count students per year level
SELECT YearLevel, COUNT(*) AS StudentCount
FROM Students
GROUP BY YearLevel;

-- Average price per category
SELECT Category, AVG(Price) AS AvgPrice
FROM Products
GROUP BY Category;

-- HAVING filters groups (like WHERE for groups)
SELECT Category, COUNT(*) AS ProductCount
FROM Products
GROUP BY Category
HAVING COUNT(*) > 5;

JOIN Operations

Combine data from multiple tables:

INNER JOIN

Returns only matching rows from both tables:

SELECT Orders.OrderID, Customers.CustomerName, Orders.OrderDate
FROM Orders
INNER JOIN Customers ON Orders.CustomerID = Customers.CustomerID;

LEFT JOIN

Returns all rows from the left table, matching rows from the right:

SELECT Customers.CustomerName, Orders.OrderID
FROM Customers
LEFT JOIN Orders ON Customers.CustomerID = Orders.CustomerID;
-- Shows all customers, even those with no orders

Tip: Use table aliases to make JOIN queries easier to read:

SELECT o.OrderID, c.CustomerName
FROM Orders o
INNER JOIN Customers c ON o.CustomerID = c.CustomerID;

Query Order of Execution

SQL clauses are executed in this order:

FROM and JOIN - Get the tables
WHERE - Filter rows
GROUP BY - Group rows
HAVING - Filter groups
SELECT - Choose columns
ORDER BY - Sort results
LIMIT - Restrict output

Complete Query Template

SELECT columns
FROM table
JOIN other_table ON condition
WHERE row_conditions
GROUP BY column
HAVING group_conditions
ORDER BY column ASC/DESC
LIMIT number;

Key Terminology

SQL - Structured Query Language
SELECT - Retrieves data from tables
WHERE - Filters rows based on conditions
ORDER BY - Sorts results
JOIN - Combines data from multiple tables
GROUP BY - Groups rows with same values
Aggregate function - Calculates values across rows (SUM, AVG, COUNT)
Alias - Temporary name for a column or table

Learning Objectives