SQL Comprehensive Notes

SQL Queries

SELECT and FROM Clauses

When asked to display certain attributes, the attributes should follow the SELECT keyword, separated by commas.

SELECT attribute1, attribute2 FROM table_name;

• Attributes must exist in the table.
• Use the exact attribute name as defined in the table structure.
• The FROM clause specifies the table to retrieve data from.
• SELECT * FROM table_name; retrieves all rows from the table.

WHERE Clause

The WHERE clause filters rows based on a condition.

• Conditions follow the WHERE keyword.

SELECT attributes FROM table_name WHERE condition;

• For string comparisons, use the LIKE keyword with regular expressions instead of the equals (=) symbol for exact matches.

Special Conditions

Special keywords for condition checking include BETWEEN, IS NULL, and IS NOT NULL.

Logical Operators

To check multiple conditions, concatenate them using logical operators. The WHERE keyword should only be used once.

AND, OR, and NOT

The logical operators are AND, OR, and NOT.

WHERE condition1 AND condition2;
WHERE condition1 OR condition2;
WHERE NOT condition;

NOT Operator

The NOT operator negates a condition.

• IS NOT NULL checks for non-null values.
• NOT LIKE checks for values not matching a regular expression.

Example

SELECT * FROM table_name WHERE manage_ID IS NOT NULL;
SELECT * FROM table_name WHERE attribute NOT LIKE 'pattern';

AND Operator

The AND operator requires all conditions to be true.

Example

To list the employee name and job description for all male salesmen:

SELECT employee_name, job_description
FROM employees
WHERE job_description LIKE 'salesman' AND gender LIKE 'M';

Truth Table for AND

Multiple AND Conditions

WHERE condition1 AND condition2 AND condition3;

All conditions must be true to return a true result.

OR Operator

The OR operator requires at least one condition to be true.

Example

To list employee name and job description for employees who work either in clerical or sales-related jobs:

SELECT employee_name, job_description
FROM employees
WHERE job_description LIKE 'clerk' OR job_description LIKE 'sales%';

Truth Table for OR

Multiple OR Conditions

WHERE condition1 OR condition2 OR condition3;

At least one condition must be true to return a true result.

Conditions on the Same Column

When checking multiple conditions on the same column using OR, each condition must be complete:

WHERE job_description LIKE 'clerk' OR job_description LIKE 'sales%';

Combining AND and OR

WHERE (job_description LIKE 'clerk' OR job_description LIKE 'sales%') OR gender LIKE 'M';

Functions in SQL

Functions are precompiled code that generate an output. SQL functions are categorized into single-row functions and multi-row functions.

Single-Row Functions

Single-row functions operate on each row of a column.

• Example: LOWER(employee_name) converts all employee names to lowercase.

Multi-Row Functions

Multi-row functions operate on a column with multiple values and return a single value.

• Example: SUM(salary) returns the total salary.

Examples of Functions

LENGTH

SELECT LENGTH('University of Auckland'); -- Returns 22
SELECT employee_ID, LENGTH(empName) FROM employee;

COUNT

SELECT COUNT(*) FROM employee; -- Counts all rows
SELECT COUNT(employee_ID) FROM employee; -- Counts non-null employee IDs
SELECT COUNT(commission) FROM employee; -- Counts non-null commission values

• COUNT(*) counts all rows.
• COUNT(column_name) counts non-null values in the specified column.

MAX and MIN

SELECT MAX(salary) FROM employees; -- Returns the highest salary
SELECT MIN(salary) FROM employees;  -- Returns the lowest salary
SELECT MIN(project_start_date) FROM projects; -- Returns the earliest start date
SELECT MAX(project_start_date) FROM projects;  -- Returns the most recent start date

DATETIME

SELECT DATETIME('now'); -- Returns the current universal date and time
SELECT DATETIME('now', 'localtime'); -- Returns the current local date and time

• Modifiers like now and localtime adjust the output.

Exercises

1. List the employee name of all employees hired before 01/01/2010 and earning more than 900.

SELECT employee_name
FROM employee
WHERE hire_date < '2010-01-01' AND salary > 900;

2. List the department 30 projects costing 50,000 or more and ending before the year 2010.

SELECT *
FROM project
WHERE department_ID = 30
  AND cost >= 50000
  AND project_end_date < '2010-01-01';

3. Display all details of projects with the name starting with c or d.

SELECT *
FROM project
WHERE project_name LIKE 'C%' OR project_name LIKE 'D%';

4. List the employee name of the employees who earn a salary more than $12.50 or a commission more than 500.

SELECT employee_name
FROM employee
WHERE salary > 1500 OR commission > 500;

5. Display the employee details for the least paid employee.

This requires sorting and limiting since aggregate functions can't be directly used in WHERE clauses.

ORDER BY Clause

The ORDER BY clause sorts the result set based on a column.

• ORDER BY column_name ASC sorts in ascending order (default).
• ORDER BY column_name DESC sorts in descending order.

SELECT * FROM employee ORDER BY salary DESC;

LIMIT Clause

The LIMIT clause limits the number of rows returned.

SELECT * FROM employee ORDER BY salary ASC LIMIT 1;

This gives the least paid employee.

OFFSET Modifier

The OFFSET modifier skips a specified number of rows before starting to return rows.

SELECT * FROM employee ORDER BY salary ASC LIMIT 5 OFFSET 2;

This skips the first two rows and returns the next five lowest-paid employees.

ORDER BY with Multiple Columns

SELECT employee_name, salary
FROM employee
WHERE salary > 1000
ORDER BY employee_name ASC, salary DESC;

This sorts by employee name in ascending order and then by salary in descending order within each name group.

Naturally Occurring Groups

Naturally occurring groups arise from one-to-many relationships in data. The GROUP BY clause allows aggregate functions to be used in the context of these groups.

Example

Project assignment data shows multiple employees assigned to each project.

To see the number of employees assigned to each project:

SELECT project_number, COUNT(employee_ID)
FROM project_assignment
GROUP BY project_number;

Another Example

To select the number of projects which started on the same date in each department:

SELECT project_start_date, department_ID, COUNT(*)
FROM project
GROUP BY project_start_date, department_ID;

Example: Average Salary for Job Categories in Each Department

SELECT department_ID, job_description, AVG(salary)
FROM employee
GROUP BY department_ID, job_description;