# 9.1 What is a subquery?
SELECT account_id, product_cd, cust_id, avail_balance
FROM account
WHERE account_id = (SELECT MAX(account_id)
FROM account);
# 9.4 Correlated Subqueries
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);
----------------------------------------
# (original) Deleting data
DELETE FROM account
WHERE account_id = 2;
# (original) Data modification using equality conditions
DELETE FROM account
WHERE account_id >= 10 AND account_id <= 20;
# 9.4.2 Data manipulation using correlated subqueries
DELETE FROM department d
WHERE NOT EXISTS (SELECT 1
FROM employee e
WHERE e.dept_id = d.dept_id);
----------------------------------------
# (original) Updating data
UPDATE employee
SET lname = 'Bush',
dept_id = 3
WHERE emp_id = 10;
# 9.4.2 Data Manipulation Using Correlated Subqueries
UPDATE account
SET last_activity_date =
(SELECT MAX(t.txn_date)
FROM transaction0 t
WHERE t.account_id = account.account_id)
WHERE EXISTS (SELECT 1
FROM transaction0 t
WHERE t.account_id = account.account_id);
----------------------------------------
# (from script) The insert statement
INSERT INTO branch (branch_id, name, address, city, state, zip)
VALUES (null, 'Headquarters', '3882 Main St.', 'Waltham', 'MA', '02451');
# (from script) The insert statement
INSERT INTO employee (emp_id, fname, lname, start_date,
dept_id, title, assigned_branch_id)
VALUES (null, 'Michael', 'Smith', '2001-06-22',
(SELECT dept_id FROM department WHERE name = 'Administration'),
'President',
(SELECT branch_id FROM branch WHERE name = 'Headquarters'));