Basic SQL Queries

Simple Queries

-- Query all data from the employee table:
SELECT * FROM employee; -- * represents all columns

-- Display specific columns:
SELECT employee_id, name, salary FROM employee;

-- Filter rows using WHERE clause:
SELECT * FROM employee WHERE salary > 2000;

-- Combine column selection and row filtering:
SELECT employee_id, name, job_title, manager_id FROM employee WHERE salary > 2000;

Column Aliases

-- Assign aliases to columns (AS keyword optional):
SELECT employee_id AS staff_number, name AS full_name, salary AS wage FROM employee;

-- Use quotes for aliases with special characters:
SELECT employee_id AS 'staff number', name AS "full name", salary AS wage FROM employee;

-- Error example (missing quotes for alias with space):
-- SELECT employee_id AS staff number, name AS "full name", salary AS wage FROM employee;
-- Error: Syntax error near 'number'

Arithmetic Operations

-- Perform calculations in SELECT:
SELECT employee_id, name, salary, salary + 1000 AS 'increased_salary', department_id FROM employee WHERE salary < 2500;

-- Handle NULL values in calculations:
SELECT employee_id, name, salary, commission, salary + commission FROM employee; -- Note: NULL handling discussed later

Distinct Values

-- Show all job titles:
SELECT job_title FROM employee;

-- Remove duplicate job titles:
SELECT DISTINCT job_title FROM employee;

-- Distinct combinations of multiple columns:
SELECT DISTINCT job_title, department_id FROM employee; -- Removes duplicate combinations, not individual columns

Sorting Results

-- Default ascending order:
SELECT * FROM employee ORDER BY salary;

-- Explicit ascending order:
SELECT * FROM employee ORDER BY salary ASC;

-- Descending order:
SELECT * FROM employee ORDER BY salary DESC;

-- Multiple sort criteria:
SELECT * FROM employee ORDER BY salary ASC, department_id DESC; -- Sort by salary ascending, then department descending

WHERE Clause Filtering

The WHERE clause specifies conditions to filter records in queries.

Comparison Operators

-- Equality check:
SELECT * FROM employee WHERE department_id = 10;

-- Numeric comparisons:
SELECT * FROM employee WHERE department_id > 10;
SELECT * FROM employee WHERE department_id >= 10;
SELECT * FROM employee WHERE department_id < 10;
SELECT * FROM employee WHERE department_id <= 10;
SELECT * FROM employee WHERE department_id <> 10; -- Not equal
SELECT * FROM employee WHERE department_id != 10; -- Alternative not equal

-- String comparisons (case-insensitive by default):
SELECT * FROM employee WHERE job_title = 'CLERK';
SELECT * FROM employee WHERE job_title = 'clerk'; -- Case-insensitive match
SELECT * FROM employee WHERE BINARY job_title = 'clerk'; -- Case-sensitive match

-- Date comparisons:
SELECT * FROM employee WHERE hire_date < '1981-12-25';

Logical Operators

-- AND operator (both conditions must be true):
SELECT * FROM employee WHERE salary > 1500 AND salary < 3000;
SELECT * FROM employee WHERE salary > 1500 && salary < 3000; -- Alternative syntax
SELECT * FROM employee WHERE salary BETWEEN 1500 AND 3000; -- Inclusive range

-- OR operator (either condition true):
SELECT * FROM employee WHERE department_id = 10 OR department_id = 20;
SELECT * FROM employee WHERE department_id = 10 || department_id = 20; -- Alternative syntax
SELECT * FROM employee WHERE department_id IN (10, 20); -- Using IN operator
SELECT * FROM employee WHERE job_title IN ('MANAGER', 'CLERK', 'ANALYST');

Pattern Matching

-- Wildcard search (% matches any sequence of characters):
SELECT * FROM employee WHERE name LIKE '%A%'; -- Names containing 'A'

-- Underscore matches single character:
SELECT * FROM employee WHERE name LIKE '__A%'; -- Third character is 'A'

NULL Value Handling

-- Check for NULL values:
SELECT * FROM employee WHERE commission IS NULL;
SELECT * FROM employee WHERE commission IS NOT NULL;

Operator Precedence

-- Parentheses clarify evaluation order:
SELECT * FROM employee WHERE job_title = 'SALESMAN' OR job_title = 'CLERK' AND salary >= 1500; -- AND evaluated before OR
SELECT * FROM employee WHERE job_title = 'SALESMAN' OR (job_title = 'CLERK' AND salary >= 1500);
SELECT * FROM employee WHERE (job_title = 'SALESMAN' OR job_title = 'CLERK') AND salary >= 1500;

SQL Functions

MySQL provides numerous functions to simplify databasee operations, including string manipulation, date calculations, and numeric operations. Functions enhance SELECT statement capabilities and facilitate data transformation without altering stored data.

Functions are categorized in to:

• Single-row functions: Proces each input record individually, returning one result per row.
• Multi-row functions: Operate on groups of records, returning a single aggregated result.

Function Examples

-- Single-row string functions:
SELECT employee_id, name, LOWER(name), UPPER(name), salary FROM employee;

-- Multi-row aggregate functions:
SELECT MAX(salary), MIN(salary), COUNT(salary), SUM(salary), AVG(salary) FROM employee;

Note: Functions process data for presentation without modifying the underlying database values.

Function Classification

• Single-row functions: Transform each row independently (e.g., LOWER(), UPPER()).
• Multi-row functions: Aggregate multiple rows into a single result (e.g., MAX(), MIN(), COUNT(), SUM(), AVG()).

All functions except the listed multi-row functions are considered single-row functions.