SQL Code Example
CRUD statement
INSERT INTO person (person_id, fname, lname, gender, birth_date)
VALUES (null, 'William','Turner', 'M', '1972-05-27');
UPDATE person SET street = '1225 Tremont St.',
city = 'Boston', state = 'MA', country = 'USA', postal_code = '02138'
WHERE person_id = 1;
DELETE FROM person WHERE person_id = 2;
DROP TABLE favorite_food;
The group by and having Clauses[key point]
Count all the employees in each department and returns the names of those departments having more than two employees:
SELECT d.name, count(e.emp_id) num_employees
FROM department d INNER JOIN employee e
ON d.dept_id = e.dept_id
GROUP BY d.name
HAVING count(e.emp_id) > 2;
The order by Clause
Ascending Versus Descending Sort Order[Asc, Desc]
Filtering
Using Parentheses
Using the not Operator
Condition Types: =, <>, <, >
DELETE FROM account WHERE status = 'CLOSED' AND YEAR(close_date) = 2002;
The between operator(Number, String, Date)
Membership Conditions(in, not in)
Using wildcards(LIKE)
_ Exactly one character
% Any number of characters (including 0)
Using regular expressions
SELECT emp_id, fname, lname FROM employee WHERE lname REGEXP '^[FG]';
Null(IS [not] NULL)
Querying Multiple Tables[Join]
Self-Joins
List every employee’s name along with the name of his or her manager:
SELECT e.fname, e.lname, e_mgr.fname mgr_fname, e_mgr.lname mgr_lname
FROM employee e INNER JOIN employee e_mgr ON e.superior_emp_id = e_mgr.emp_id;
Using the Same Table Twice
SELECT a.account_id, e.emp_id, b_a.name open_branch, b_e.name emp_branch
FROM account a INNER JOIN branch b_a ON a.open_branch_id = b_a.branch_id
INNER JOIN employee e ON a.open_emp_id = e.emp_id
INNER JOIN branch b_e ON e.assigned_branch_id = b_e.branch_id
WHERE a.product_cd = 'CHK';
Using Subqueries As Tables
Non-Equi-Joins
List all possible chess matches:
SELECT e1.fname, e1.lname, 'VS' vs, e2.fname, e2.lname
FROM employee e1 INNER JOIN employee e2 ON e1.emp_id < e2.emp_id
WHERE e1.title = 'Teller' AND e2.title = 'Teller';
Grouping and Aggregates[key point]
SELECT open_emp_id, COUNT(*) how_many FROM account
GROUP BY open_emp_id HAVING COUNT(*) > 4;
Aggregate Functions
Max(), Min(),Avg(), Sum(), Count()
SELECT product_cd, MAX(avail_balance) max_balance,
MIN(avail_balance) min_balance, AVG(avail_balance) avg_balance,
SUM(avail_balance) tot_balance, COUNT(*) num_accts
FROM account GROUP BY product_cd;
Counting Distinct Values
SELECT COUNT(DISTINCT open_emp_id) FROM account;
SELECT product_cd, open_branch_id, SUM(avail_balance) tot_balance
FROM account GROUP BY product_cd, open_branch_id;
Grouping via Expressions
SELECT EXTRACT(YEAR FROM start_date) year, COUNT(*) how_many
FROM employee GROUP BY EXTRACT(YEAR FROM start_date);
Group Filter Conditions[having]
Subqueries
SELECT account_id, product_cd, cust_id, avail_balance
FROM account WHERE account_id = (SELECT MAX(account_id) FROM account);
Subquery Types
Noncorrelated Subqueries
Single-Row, Single-Column Subqueries
This type of subquery is known as a scalar subquery and can appear on either side of a condition using the usual operators (=, <>, <,>, <=, >=).
Multiple-Row, Single-Column Subqueries[in and not in, all, any]
List which employees supervise other employees:
SELECT emp_id, fname, lname, title FROM employee
WHERE emp_id IN (SELECT superior_emp_id FROM employee);
SELECT emp_id, fname, lname, title FROM employee
WHERE emp_id NOT IN (SELECT superior_emp_id
FROM employee WHERE superior_emp_id IS NOT NULL);
Find all employees whose employee IDs are not equal to any of the supervisor employee IDs:
SELECT emp_id, fname, lname, title FROM employee
WHERE emp_id <> ALL (SELECT superior_emp_id
FROM employee WHERE superior_emp_id IS NOT NULL);
Multicolumn Subqueries
Find all accounts whose balances don’t match the sum of the transaction amounts for that account.
Correlated Subqueries
List customers having exactly two accounts
SELECT c.cust_id, c.cust_type_cd, c.city FROM customer c
WHERE 2 = (SELECT COUNT(*) FROM account a WHERE a.cust_id = c.cust_id);
The exists Operator
Find all the accounts for which a transaction was posted on a particular day
SELECT a.account_id, a.product_cd, a.cust_id, a.avail_balance FROM account a
WHERE EXISTS (SELECT 1 FROM transaction t WHERE t.account_id = a.account_id AND t.txn_date = '2008-09-22');
UPDATE account a SET a.last_activity_date =
(SELECT MAX(t.txn_date) FROM transaction t WHERE t.account_id = a.account_id);
WHERE EXISTS (SELECT 1 FROM transaction t WHERE t.account_id = a.account_id);
Remove data from the department table that has no child rows in the employee table:
DELETE FROM department WHERE NOT EXISTS
(SELECT 1 FROM employee WHERE employee.dept_id = department.dept_id);
Subqueries in Filter Conditions
Find the employee responsible for opening the most accounts:
SELECT open_emp_id, COUNT(*) how_many FROM account GROUP BY open_emp_id
HAVING COUNT(*) = (SELECT MAX(emp_cnt.how_many) FROM (SELECT COUNT(*) how_many
FROM account GROUP BY open_emp_id) emp_cnt);
Working with Sets
Set Operators[union, union all, intersect, except, ]
union all operator doesn’t remove duplicates.
SELECT 'IND' type_cd, cust_id, lname name FROM individual
UNION ALL
SELECT 'BUS' type_cd, cust_id, name FROM business;
Conditional Logic
CASE
WHEN C1 THEN E1
WHEN CN THEN EN
[ELSE ED]
END
CASE V0
WHEN V1 THEN E1
WHEN VN THEN EN
[ELSE ED]
END
SELECT
SUM(CASE WHEN EXTRACT(YEAR FROM open_date) = 2010 THEN 1 ELSE 0 END) year_2010,
SUM(CASE WHEN EXTRACT(YEAR FROM open_date) = 2011 THEN 1 ELSE 0 END) year_2011,
FROM account WHERE open_date > '1999-12-31' AND open_date < '2006-01-01';
Find accounts whose account balances don’t agree with the raw data in the transaction table.
SELECT CONCAT('ALERT! : Account #', a.account_id,
' Has Incorrect Balance!')
FROM account a
WHERE (a.avail_balance, a.pending_balance) <>
(SELECT
SUM(CASE
WHEN t.funds_avail_date > CURRENT_TIMESTAMP() THEN 0
WHEN t.txn_type_cd = 'DBT' THEN t.amount * −1
ELSE t.amount
END),
SUM(CASE WHEN t.txn_type_cd = 'DBT' THEN t.amount * −1 ELSE t.amount END)
FROM transaction t
WHERE t.account_id = a.account_id);
Show whether the customer has any checking accounts and whether the customer has any savings accounts:
SELECT c.cust_id, c.fed_id, c.cust_type_cd,
CASE
WHEN EXISTS (SELECT 1 FROM account a
WHERE a.cust_id = c.cust_id
AND a.product_cd = 'CHK') THEN 'Y'
ELSE 'N'
END has_checking,
CASE
WHEN EXISTS (SELECT 1 FROM account a
WHERE a.cust_id = c.cust_id
AND a.product_cd = 'SAV') THEN 'Y'
ELSE 'N'
END has_savings
FROM customer c;
Count the number of accounts for each customer, and then returns either 'None', '1', '2', or '3+':
SELECT c.cust_id, c.fed_id, c.cust_type_cd,
CASE (SELECT COUNT(*) FROM account a
WHERE a.cust_id = c.cust_id)
WHEN 0 THEN 'None'
WHEN 1 THEN '1'
WHEN 2 THEN '2'
ELSE '3+'
END num_accounts
FROM customer c;
Division-by-Zero Errors
SELECT a.cust_id, a.product_cd, a.avail_balance /
CASE
WHEN prod_tots.tot_balance = 0 THEN 1
ELSE prod_tots.tot_balance
END percent_of_total
FROM account a INNER JOIN
(SELECT a.product_cd, SUM(a.avail_balance) tot_balance
FROM account a
GROUP BY a.product_cd) prod_tots
ON a.product_cd = prod_tots.product_cd;
Transactions
Lock Granularities:Table locks, Page locks, Row locks
SHOW TABLE STATUS LIKE 'transaction' \G
START TRANSACTION;
UPDATE product
SET date_retired = CURRENT_TIMESTAMP() WHERE product_cd = 'XYZ';
SAVEPOINT before_close_accounts;
UPDATE account
SET status = 'CLOSED', close_date = CURRENT_TIMESTAMP(),
last_activity_date = CURRENT_TIMESTAMP()
WHERE product_cd = 'XYZ';
ROLLBACK TO SAVEPOINT before_close_accounts;
COMMIT;
Resource
