Table of Contents
Introduction to SQL
SQL (Structured Query Language) is the standard language for interacting with relational databases. It allows you to retrieve, insert, update, and delete data.
Basic SELECT Syntax
The SELECT statement is used to query data from a database.
SELECT column1, column2, ...
FROM table_name;Selecting All Columns
-- Select all columns from customers table
SELECT * FROM customers;Selecting Specific Columns
-- Select only name and email columns
SELECT first_name, last_name, email
FROM customers;Using Column Aliases
Aliases give columns temporary names in the result set.
SELECT
first_name AS "First Name",
last_name AS "Last Name",
email AS "Email Address"
FROM customers;WHERE Clause - Filtering Data
The WHERE clause filters records based on specified conditions.
-- Find customers with specific last name
SELECT * FROM customers
WHERE last_name = 'Smith';
-- Find products with price greater than 50
SELECT product_name, price
FROM products
WHERE price > 50;Comparison Operators
=Equal to!=or<>Not equal to>Greater than<Less than>=Greater than or equal to<=Less than or equal to
-- Examples of comparison operators
SELECT * FROM products WHERE price >= 25 AND price <= 100;
SELECT * FROM customers WHERE customer_id != 5;Logical Operators
AND Operator
SELECT * FROM products
WHERE price > 20 AND category_id = 1;OR Operator
SELECT * FROM customers
WHERE city = 'New York' OR city = 'Los Angeles';NOT Operator
SELECT * FROM products
WHERE NOT category_id = 3;Pattern Matching with LIKE
Use wildcards to search for patterns in text:
%- Matches any sequence of characters_- Matches any single character
-- Find customers whose first name starts with 'J'
SELECT * FROM customers
WHERE first_name LIKE 'J%';
-- Find products containing 'phone' in the name
SELECT * FROM products
WHERE product_name LIKE '%phone%';
-- Find customers with exactly 5-character first names
SELECT * FROM customers
WHERE first_name LIKE '_____';IN Operator
Check if a value matches any value in a list.
SELECT * FROM customers
WHERE city IN ('New York', 'Chicago', 'Miami');
-- Equivalent to:
SELECT * FROM customers
WHERE city = 'New York' OR city = 'Chicago' OR city = 'Miami';BETWEEN Operator
Check if a value falls within a range.
SELECT * FROM products
WHERE price BETWEEN 25 AND 100;
-- Works with dates too
SELECT * FROM orders
WHERE order_date BETWEEN '2024-01-01' AND '2024-12-31';NULL Values
Handle missing data with IS NULL and IS NOT NULL.
-- Find customers without phone numbers
SELECT * FROM customers
WHERE phone IS NULL;
-- Find customers with phone numbers
SELECT * FROM customers
WHERE phone IS NOT NULL;ORDER BY - Sorting Results
-- Sort customers by last name (ascending)
SELECT * FROM customers
ORDER BY last_name;
-- Sort by last name descending
SELECT * FROM customers
ORDER BY last_name DESC;
-- Sort by multiple columns
SELECT * FROM customers
ORDER BY last_name, first_name;LIMIT - Restricting Results
-- Get first 10 customers
SELECT * FROM customers
LIMIT 10;
-- Get customers 11-20 (pagination)
SELECT * FROM customers
LIMIT 10 OFFSET 10;DISTINCT - Removing Duplicates
-- Get unique cities from customers
SELECT DISTINCT city FROM customers;
-- Count unique cities
SELECT COUNT(DISTINCT city) AS unique_cities
FROM customers;Practical Examples
-- Find expensive products in electronics category
SELECT product_name, price
FROM products
WHERE category_id = 1 AND price > 500
ORDER BY price DESC;
-- Find recent orders from specific customer
SELECT order_id, order_date, total_amount
FROM orders
WHERE customer_id = 123
AND order_date >= '2024-01-01'
ORDER BY order_date DESC
LIMIT 5;Practice Exercises
- Write a query to find all customers whose email ends with '.com'
- Find products with prices between $10 and $50, sorted by price
- Get the first 5 orders placed in 2024
- Find customers from either 'California' or 'Texas' states
Next Steps
In the next lesson, we'll learn about aggregate functions (COUNT, SUM, AVG) and GROUP BY clauses to analyze and summarize data.