SQL Syntax and Queries

Introduction to SQL

SQL (Structured Query Language) is the standard language for interacting with relational databases. Developed in the 1970s at IBM, SQL has evolved into a powerful, standardized language that serves as the foundation for most relational database systems including MySQL, PostgreSQL, SQL Server, Oracle, and SQLite.

Why SQL Matters

• Universal Language: Despite variations between database systems, core SQL knowledge is transferable
• Powerful Querying: Express complex data requirements with relatively simple syntax
• Data Integrity: Enforce rules that maintain the accuracy and reliability of your data
• Concurrency: Handle multiple users accessing data simultaneously
• Enduring Relevance: Despite new technologies, SQL has remained relevant for over 40 years

SQL Language Components

SQL is divided into several key components, each serving specific functions:

This lecture focuses primarily on DQL (SELECT statements) and some basic DML operations, as these form the foundation of SQL querying.

Basic SQL Syntax Elements

SQL Statement Structure

SQL statements typically follow a logical structure:

• Begin with a command (SELECT, INSERT, UPDATE, etc.)
• Specify the data or objects being affected
• Include conditions or modifications as needed
• End with a semicolon (;) in most database systems

-- A basic SQL query structure
SELECT column1, column2
FROM table_name
WHERE condition
ORDER BY column_name;
          

Key SQL Syntax Rules

• SQL is not case-sensitive for keywords (SELECT is the same as select), but database objects may be case-sensitive depending on the system
• Whitespace is generally ignored, allowing for formatting to improve readability
• Comments use -- for single line or /* */ for multi-line
• String literals are enclosed in single quotes ('text')
• Identifiers (table and column names) may be enclosed in database-specific delimiters (e.g., backticks in MySQL, double quotes in PostgreSQL) when they contain special characters or match reserved words

-- This is a single-line comment
SELECT first_name, last_name  -- This selects two columns
FROM employees;

/* This is a 
   multi-line comment
   demonstrating SQL's flexibility */
SELECT 
    employee_id,
    first_name,
    last_name,
    hire_date
FROM 
    employees
WHERE 
    department_id = 50
ORDER BY 
    hire_date DESC;
          

Basic SELECT Queries

The SELECT statement is the most commonly used command in SQL, allowing you to retrieve data from one or more tables.

Simple SELECT Examples

-- Retrieve all columns from the customers table
SELECT * FROM customers;
          

-- Retrieve only name and email columns
SELECT customer_name, email FROM customers;
          

-- Retrieve customers from a specific country
SELECT customer_name, email, phone 
FROM customers
WHERE country = 'USA';
          

Column Aliases

Aliases allow you to rename columns in your result set, making output more readable or accommodating specific application requirements.

-- Rename columns in the result set
SELECT 
    first_name AS "First Name",
    last_name AS "Last Name",
    email AS "Contact Email"
FROM employees;

-- Shorthand without AS keyword
SELECT 
    product_name "Product",
    unit_price "Price",
    units_in_stock "Inventory"
FROM products;
          

Filtering Data with WHERE

The WHERE clause allows you to filter results based on specific conditions, returning only rows that match your criteria.

Comparison Operators

Logical Operators

Combine multiple conditions for more complex filtering:

-- Using AND to match multiple conditions
SELECT product_name, unit_price, category
FROM products
WHERE unit_price > 50 AND category = 'Electronics';

-- Using OR to match any of multiple conditions
SELECT first_name, last_name, hire_date
FROM employees
WHERE department = 'Sales' OR department = 'Marketing';

-- Using NOT to negate a condition
SELECT order_id, order_date, status
FROM orders
WHERE NOT status = 'Delivered';
          

-- Combining AND, OR with parentheses for clarity
SELECT product_id, product_name, unit_price, units_in_stock
FROM products
WHERE 
    (category = 'Beverages' OR category = 'Condiments')
    AND unit_price > 10
    AND units_in_stock > 0;
          

Special Operators and Conditions

BETWEEN Operator

The BETWEEN operator selects values within a given range (inclusive).

-- Retrieve products within a price range
SELECT product_name, unit_price
FROM products
WHERE unit_price BETWEEN 10 AND 20;

-- Equivalent to:
-- WHERE unit_price >= 10 AND unit_price <= 20

-- BETWEEN with dates
SELECT order_id, customer_id, order_date
FROM orders
WHERE order_date BETWEEN '2023-01-01' AND '2023-03-31';
          

IN Operator

The IN operator allows you to specify multiple values in a WHERE clause, acting as a shorthand for multiple OR conditions.

-- Find customers in specific countries
SELECT customer_name, country
FROM customers
WHERE country IN ('USA', 'Canada', 'Mexico');

-- Equivalent to:
-- WHERE country = 'USA' OR country = 'Canada' OR country = 'Mexico'

-- Using IN with subqueries (more on this later)
SELECT product_name, unit_price
FROM products
WHERE category_id IN (SELECT category_id FROM categories WHERE category_name = 'Beverages');
          

LIKE Operator for Pattern Matching

The LIKE operator is used for string pattern matching, using wildcards:

• % - Matches any sequence of characters (including zero characters)
• _ - Matches exactly one character

-- Find customers whose names start with 'A'
SELECT customer_name
FROM customers
WHERE customer_name LIKE 'A%';

-- Find products with 'coffee' anywhere in the name
SELECT product_name
FROM products
WHERE product_name LIKE '%coffee%';

-- Find employees with exactly 5-character last names
SELECT first_name, last_name
FROM employees
WHERE last_name LIKE '_____';

-- Find products with price values ending in .99
SELECT product_name, unit_price
FROM products
WHERE CAST(unit_price AS VARCHAR) LIKE '%.99';
          

NULL Values

NULL represents a missing or unknown value. You can't use regular comparison operators with NULL; instead, use IS NULL or IS NOT NULL.

-- Find employees without a manager
SELECT employee_id, first_name, last_name
FROM employees
WHERE manager_id IS NULL;

-- Find customers with phone numbers
SELECT customer_name, phone
FROM customers
WHERE phone IS NOT NULL;
          

Sorting Results with ORDER BY

The ORDER BY clause sorts your query results based on one or more columns, in ascending (ASC) or descending (DESC) order.

-- Sort customers alphabetically by name (ascending is default)
SELECT customer_id, customer_name, city
FROM customers
ORDER BY customer_name;

-- Sort products by price in descending order (highest first)
SELECT product_name, unit_price
FROM products
ORDER BY unit_price DESC;
          

-- Sort employees by department, then by salary (highest within each department)
SELECT employee_id, first_name, last_name, department, salary
FROM employees
ORDER BY department ASC, salary DESC;
          

-- Sort by the second column in the SELECT list
SELECT product_name, unit_price, units_in_stock
FROM products
ORDER BY 2 DESC;  -- Sorts by unit_price descending
          

Limiting Results

Different database systems offer ways to limit the number of rows returned:

-- Return only the first 10 products
SELECT product_id, product_name, unit_price
FROM products
ORDER BY unit_price DESC
LIMIT 10;
          

-- Return only the first 10 products
SELECT TOP 10 product_id, product_name, unit_price
FROM products
ORDER BY unit_price DESC;
          

-- Return only the first 10 products
SELECT product_id, product_name, unit_price
FROM products
ORDER BY unit_price DESC
FETCH FIRST 10 ROWS ONLY;
          

-- Get the second page of 10 products (rows 11-20)
SELECT product_id, product_name, unit_price
FROM products
ORDER BY product_name
LIMIT 10 OFFSET 10;
          

Using Distinct Values

The DISTINCT keyword eliminates duplicate rows from your result set.

-- Get a list of all unique countries where customers are located
SELECT DISTINCT country
FROM customers
ORDER BY country;

-- Get unique combination of city and country
SELECT DISTINCT city, country
FROM customers
ORDER BY country, city;
          

Practical Activities

Activity 1: Basic SQL Queries

Using a sample database (like Northwind or Sakila), write SQL queries to:

1. Retrieve all columns from the products table
2. List the names and email addresses of all customers
3. Find all products with a price greater than $50
4. List all employees hired before 2020
5. Find all orders placed in the last quarter of 2022

Activity 2: Filtering and Sorting

Write queries that:

1. Find all products in the 'Electronics' category with more than 10 units in stock, sorted by price (highest first)
2. List customers from Germany or France whose company name starts with the letter 'A'
3. Find employees without a manager
4. List orders with shipping date between March 1 and March 31, 2023
5. Find products with the word "chair" in their name, regardless of case

Activity 3: Real-World Challenge

Imagine you're working for an online bookstore. Write SQL queries to:

1. List the 10 bestselling books of 2023
2. Find all science fiction books published in the last 5 years
3. List authors who have published books in multiple genres
4. Find books that are running low on inventory (less than 5 copies)
5. List customers who have placed orders totaling more than $500

Key Takeaways

• SQL is a powerful, standardized language for interacting with relational databases
• The SELECT statement forms the foundation of data retrieval in SQL
• The WHERE clause enables filtering data based on various conditions
• Special operators like BETWEEN, IN, and LIKE provide powerful filtering capabilities
• ORDER BY allows for precise control over the sorting of result sets
• DISTINCT helps eliminate duplicate rows from results
• Result limiting techniques (LIMIT, TOP) help manage large result sets

Next Steps

Our next lecture will cover CRUD operations in SQL, focusing on INSERT, UPDATE, and DELETE statements for managing data in your tables.