Table of Contents for

Learning SQL, 2nd Edition

Retrieve the employee ID, first name, and last name for all bank employees. Sort by last name and then by first name.

mysql> SELECT emp_id, fname, lname
    -> FROM employee
    -> ORDER BY lname, fname;
+--------+----------+-----------+
| emp_id | fname    | lname     |
+--------+----------+-----------+
|      2 | Susan    | Barker    |
|     13 | John     | Blake     |
|      6 | Helen    | Fleming   |
|     17 | Beth     | Fowler    |
|      5 | John     | Gooding   |
|      9 | Jane     | Grossman  |
|      4 | Susan    | Hawthorne |
|     12 | Samantha | Jameson   |
|     16 | Theresa  | Markham   |
|     14 | Cindy    | Mason     |
|      8 | Sarah    | Parker    |
|     15 | Frank    | Portman   |
|     10 | Paula    | Roberts   |
|      1 | Michael  | Smith     |
|      7 | Chris    | Tucker    |
|     18 | Rick     | Tulman    |
|      3 | Robert   | Tyler     |
|     11 | Thomas   | Ziegler   |
+--------+----------+-----------+
18 rows in set (0.01 sec)

Retrieve the account ID, customer ID, and available balance for all accounts whose status equals 'ACTIVE' and whose available balance is greater than $2,500.

mysql> SELECT account_id, cust_id, avail_balance
    -> FROM account
    -> WHERE status = 'ACTIVE'
    ->   AND avail_balance > 2500;
+------------+---------+---------------+
| account_id | cust_id | avail_balance |
+------------+---------+---------------+
|          3 |       1 |       3000.00 |
|         10 |       4 |       5487.09 |
|         13 |       6 |      10000.00 |
|         14 |       7 |       5000.00 |
|         15 |       8 |       3487.19 |
|         18 |       9 |       9345.55 |
|         20 |      10 |      23575.12 |
|         22 |      11 |       9345.55 |
|         23 |      12 |      38552.05 |
|         24 |      13 |      50000.00 |
+------------+---------+---------------+
10 rows in set (0.00 sec)

Write a query against the account table that returns the IDs of the employees who opened the accounts (use the account.open_emp_id column). Include a single row for each distinct employee.

mysql> SELECT DISTINCT open_emp_id
    -> FROM account;
+-------------+
| open_emp_id |
+-------------+
|           1 |
|          10 |
|          13 |
|          16 |
+-------------+
4 rows in set (0.00 sec)

Fill in the blanks (denoted by <#>) for this multi-data-set query to achieve the results shown here:

mysql> SELECT p.product_cd, a.cust_id, a.avail_balance
    -> FROM product p INNER JOIN account <1>
    ->   ON p.product_cd = <2>
    -> WHERE p.<3> = 'ACCOUNT';
+------------+---------+---------------+
| product_cd | cust_id | avail_balance |
+------------+---------+---------------+
| CD         |       1 |       3000.00 |
| CD         |       6 |      10000.00 |
| CD         |       7 |       5000.00 |
| CD         |       9 |       1500.00 |
| CHK        |       1 |       1057.75 |
| CHK        |       2 |       2258.02 |
| CHK        |       3 |       1057.75 |
| CHK        |       4 |        534.12 |
| CHK        |       5 |       2237.97 |
| CHK        |       6 |        122.37 |
| CHK        |       8 |       3487.19 |
| CHK        |       9 |        125.67 |
| CHK        |      10 |      23575.12 |
| CHK        |      12 |      38552.05 |
| MM         |       3 |       2212.50 |
| MM         |       4 |       5487.09 |
| MM         |       9 |       9345.55 |
| SAV        |       1 |        500.00 |
| SAV        |       2 |        200.00 |
| SAV        |       4 |        767.77 |
| SAV        |       8 |        387.99 |
+------------+---------+---------------+
21 rows in set (0.02 sec)

The correct values for <1>, <2>, and <3> are:

Which of the transaction IDs would be returned by the following filter conditions?

txn_date < '2005-02-26' AND (txn_type_cd = 'DBT' OR amount > 100)

Transaction IDs 1, 2, 3, 5, 6, and 7.

Which of the transaction IDs would be returned by the following filter conditions?

account_id IN (101,103) AND NOT (txn_type_cd = 'DBT' OR amount > 100)

Transaction IDs 4 and 9.

Construct a query that retrieves all accounts opened in 2002.

mysql> SELECT account_id, open_date
    -> FROM account
    -> WHERE open_date BETWEEN '2002-01-01' AND '2002-12-31';
+------------+------------+
| account_id | open_date  |
+------------+------------+
|          6 | 2002-11-23 |
|          7 | 2002-12-15 |
|         12 | 2002-08-24 |
|         20 | 2002-09-30 |
|         21 | 2002-10-01 |
+------------+------------+
5 rows in set (0.01 sec)

Construct a query that finds all nonbusiness customers whose last name contains an a in the second position and an e anywhere after the a.

mysql> SELECT cust_id, lname, fname
    -> FROM individual
    -> WHERE lname LIKE '_a%e%';
+---------+--------+---------+
| cust_id | lname  | fname   |
+---------+--------+---------+
|       1 | Hadley | James   |
|       9 | Farley | Richard |
+---------+--------+---------+
2 rows in set (0.02 sec)

Fill in the blanks (denoted by <#>) for the following query to obtain the results that follow:

mysql> SELECT e.emp_id, e.fname, e.lname, b.name
    -> FROM employee e INNER JOIN <1> b
    ->   ON e.assigned_branch_id = b.<2>;
+--------+----------+-----------+---------------+
| emp_id | fname    | lname     | name          |
+--------+----------+-----------+---------------+
|      1 | Michael  | Smith     | Headquarters  |
|      2 | Susan    | Barker    | Headquarters  |
|      3 | Robert   | Tyler     | Headquarters  |
|      4 | Susan    | Hawthorne | Headquarters  |
|      5 | John     | Gooding   | Headquarters  |
|      6 | Helen    | Fleming   | Headquarters  |
|      7 | Chris    | Tucker    | Headquarters  |
|      8 | Sarah    | Parker    | Headquarters  |
|      9 | Jane     | Grossman  | Headquarters  |
|     10 | Paula    | Roberts   | Woburn Branch |
|     11 | Thomas   | Ziegler   | Woburn Branch |
|     12 | Samantha | Jameson   | Woburn Branch |
|     13 | John     | Blake     | Quincy Branch |
|     14 | Cindy    | Mason     | Quincy Branch |
|     15 | Frank    | Portman   | Quincy Branch |
|     16 | Theresa  | Markham   | So. NH Branch |
|     17 | Beth     | Fowler    | So. NH Branch |
|     18 | Rick     | Tulman    | So. NH Branch |
+--------+----------+-----------+---------------+
18 rows in set (0.03 sec)

The correct values for <1> and <2> are:

Write a query that returns the account ID for each nonbusiness customer (customer.cust_type_cd = 'I') along with the customer’s federal ID (customer.fed_id) and the name of the product on which the account is based (product.name).

mysql> SELECT a.account_id, c.fed_id, p.name
    -> FROM account a INNER JOIN customer c
    ->   ON a.cust_id = c.cust_id
    ->   INNER JOIN product p
    ->   ON a.product_cd = p.product_cd
    -> WHERE c.cust_type_cd = 'I';
+------------+-------------+------------------------+
| account_id | fed_id      | name                   |
+------------+-------------+------------------------+
|          1 | 111-11-1111 | checking account       |
|          2 | 111-11-1111 | savings account        |
|          3 | 111-11-1111 | certificate of deposit |
|          4 | 222-22-2222 | checking account       |
|          5 | 222-22-2222 | savings account        |
|          6 | 333-33-3333 | checking account       |
|          7 | 333-33-3333 | money market account   |
|          8 | 444-44-4444 | checking account       |
|          9 | 444-44-4444 | savings account        |
|         10 | 444-44-4444 | money market account   |
|         11 | 555-55-5555 | checking account       |
|         12 | 666-66-6666 | checking account       |
|         13 | 666-66-6666 | certificate of deposit |
|         14 | 777-77-7777 | certificate of deposit |
|         15 | 888-88-8888 | checking account       |
|         16 | 888-88-8888 | savings account        |
|         17 | 999-99-9999 | checking account       |
|         18 | 999-99-9999 | money market account   |
|         19 | 999-99-9999 | certificate of deposit |
+------------+-------------+------------------------+
19 rows in set (0.00 sec)

Construct a query that finds all employees whose supervisor is assigned to a different department. Retrieve the employees’ ID, first name, and last name.

mysql> SELECT e.emp_id, e.fname, e.lname
    -> FROM employee e INNER JOIN employee mgr
    ->   ON e.superior_emp_id = mgr.emp_id
    -> WHERE e.dept_id != mgr.dept_id;
+--------+-------+-----------+
| emp_id | fname | lname     |
+--------+-------+-----------+
|      4 | Susan | Hawthorne |
|      5 | John  | Gooding   |
+--------+-------+-----------+
2 rows in set (0.00 sec)

If set A = {L M N O P} and set B = {P Q R S T}, what sets are generated by the following operations?

Write a compound query that finds the first and last names of all individual customers along with the first and last names of all employees.

mysql> SELECT fname, lname
    -> FROM individual
    -> UNION
    -> SELECT fname, lname
    -> FROM employee;
+----------+-----------+
| fname    | lname     |
+----------+-----------+
| James    | Hadley    |
| Susan    | Tingley   |
| Frank    | Tucker    |
| John     | Hayward   |
| Charles  | Frasier   |
| John     | Spencer   |
| Margaret | Young     |
| Louis    | Blake     |
| Richard  | Farley    |
| Michael  | Smith     |
| Susan    | Barker    |
| Robert   | Tyler     |
| Susan    | Hawthorne |
| John     | Gooding   |
| Helen    | Fleming   |
| Chris    | Tucker    |
| Sarah    | Parker    |
| Jane     | Grossman  |
| Paula    | Roberts   |
| Thomas   | Ziegler   |
| Samantha | Jameson   |
| John     | Blake     |
| Cindy    | Mason     |
| Frank    | Portman   |
| Theresa  | Markham   |
| Beth     | Fowler    |
| Rick     | Tulman    |
+----------+-----------+
27 rows in set (0.01 sec)

Sort the results from Exercise 6-2 by the lname column.

mysql> SELECT fname, lname
    -> FROM individual
    -> UNION ALL
    -> SELECT fname, lname
    -> FROM employee
    -> ORDER BY lname;
+----------+-----------+
| fname    | lname     |
+----------+-----------+
| Susan    | Barker    |
| Louis    | Blake     |
| John     | Blake     |
| Richard  | Farley    |
| Helen    | Fleming   |
| Beth     | Fowler    |
| Charles  | Frasier   |
| John     | Gooding   |
| Jane     | Grossman  |
| James    | Hadley    |
| Susan    | Hawthorne |
| John     | Hayward   |
| Samantha | Jameson   |
| Theresa  | Markham   |
| Cindy    | Mason     |
| Sarah    | Parker    |
| Frank    | Portman   |
| Paula    | Roberts   |
| Michael  | Smith     |
| John     | Spencer   |
| Susan    | Tingley   |
| Chris    | Tucker    |
| Frank    | Tucker    |
| Rick     | Tulman    |
| Robert   | Tyler     |
| Margaret | Young     |
| Thomas   | Ziegler   |
+----------+-----------+
27 rows in set (0.01 sec)

Write a query that returns the 17^th through 25^th characters of the string 'Please find the substring in this string'.

mysql> SELECT SUBSTRING('Please find the substring in this string',17,9);
+------------------------------------------------------------+
| SUBSTRING('Please find the substring in this string',17,9) |
+------------------------------------------------------------+
| substring                                                  |
+------------------------------------------------------------+
1 row in set (0.00 sec)

Write a query that returns the absolute value and sign (−1, 0, or 1) of the number −25.76823. Also return the number rounded to the nearest hundredth.

mysql> SELECT ABS(-25.76823), SIGN(-25.76823), ROUND(-25.76823, 2);
+----------------+-----------------+---------------------+
| ABS(-25.76823) | SIGN(-25.76823) | ROUND(-25.76823, 2) |
+----------------+-----------------+---------------------+
|       25.76823 |              −1 |              −25.77 |
+----------------+-----------------+---------------------+
1 row in set (0.00 sec)

Write a query to return just the month portion of the current date.

mysql> SELECT EXTRACT(MONTH FROM CURRENT_DATE());
+----------------------------------+
| EXTRACT(MONTH FROM CURRENT_DATE) |
+----------------------------------+
|                                5 |
+----------------------------------+
1 row in set (0.02 sec)

(Your result will most likely be different, unless it happens to be May when you try this exercise.)

Construct a query that counts the number of rows in the account table.

mysql> SELECT COUNT(*)
    -> FROM account;
+----------+
| count(*) |
+----------+
|       24 |
+----------+
1 row in set (0.32 sec)

Modify your query from Exercise 8-1 to count the number of accounts held by each customer. Show the customer ID and the number of accounts for each customer.

mysql> SELECT cust_id, COUNT(*)
    -> FROM account
    -> GROUP BY cust_id;
+---------+----------+
| cust_id | count(*) |
+---------+----------+
|       1 |        3 |
|       2 |        2 |
|       3 |        2 |
|       4 |        3 |
|       5 |        1 |
|       6 |        2 |
|       7 |        1 |
|       8 |        2 |
|       9 |        3 |
|      10 |        2 |
|      11 |        1 |
|      12 |        1 |
|      13 |        1 |
+---------+----------+
13 rows in set (0.00 sec)

Modify your query from Exercise 8-2 to include only those customers having at least two accounts.

mysql> SELECT cust_id, COUNT(*)
    -> FROM account
    -> GROUP BY cust_id
    -> HAVING COUNT(*) >= 2;
+---------+----------+
| cust_id | COUNT(*) |
+---------+----------+
|       1 |        3 |
|       2 |        2 |
|       3 |        2 |
|       4 |        3 |
|       6 |        2 |
|       8 |        2 |
|       9 |        3 |
|      10 |        2 |
+---------+----------+
8 rows in set (0.04 sec)

Find the total available balance by product and branch where there is more than one account per product and branch. Order the results by total balance (highest to lowest).

mysql> SELECT product_cd, open_branch_id, SUM(avail_balance)
    -> FROM account
    -> GROUP BY product_cd, open_branch_id
    -> HAVING COUNT(*) > 1
    -> ORDER BY 3 DESC;
+------------+----------------+--------------------+
| product_cd | open_branch_id | SUM(avail_balance) |
+------------+----------------+--------------------+
| CHK        |              4 |           67852.33 |
| MM         |              1 |           14832.64 |
| CD         |              1 |           11500.00 |
| CD         |              2 |            8000.00 |
| CHK        |              2 |            3315.77 |
| CHK        |              1 |             782.16 |
| SAV        |              2 |             700.00 |
+------------+----------------+--------------------+
7 rows in set (0.01 sec)

Note that MySQL would not accept ORDER BY SUM(avail_balance) DESC,, so I was forced to indicate the sort column by position.

Construct a query against the account table that uses a filter condition with a noncorrelated subquery against the product table to find all loan accounts (product.product_type_cd = 'LOAN'). Retrieve the account ID, product code, customer ID, and available balance.

mysql> SELECT account_id, product_cd, cust_id, avail_balance
    -> FROM account
    -> WHERE product_cd IN (SELECT product_cd
    ->   FROM product
    ->   WHERE product_type_cd = 'LOAN');
+------------+------------+---------+---------------+
| account_id | product_cd | cust_id | avail_balance |
+------------+------------+---------+---------------+
|         21 | BUS        |      10 |          0.00 |
|         22 | BUS        |      11 |       9345.55 |
|         24 | SBL        |      13 |      50000.00 |
+------------+------------+---------+---------------+
3 rows in set (0.07 sec)

Rework the query from Exercise 9-1 using a correlated subquery against the product table to achieve the same results.

mysql> SELECT a.account_id, a.product_cd, a.cust_id, a.avail_balance
    -> FROM account a
    -> WHERE EXISTS (SELECT 1
    ->   FROM product p
    ->   WHERE p.product_cd = a.product_cd
    ->     AND p.product_type_cd = 'LOAN');
+------------+------------+---------+---------------+
| account_id | product_cd | cust_id | avail_balance |
+------------+------------+---------+---------------+
|         21 | BUS        |      10 |          0.00 |
|         22 | BUS        |      11 |       9345.55 |
|         24 | SBL        |      13 |      50000.00 |
+------------+------------+---------+---------------+
3 rows in set (0.01 sec)

Join the following query to the employee table to show the experience level of each employee:

SELECT 'trainee' name, '2004-01-01' start_dt, '2005-12-31' end_dt
UNION ALL
SELECT 'worker' name, '2002-01-01' start_dt, '2003-12-31' end_dt
UNION ALL
SELECT 'mentor' name, '2000-01-01' start_dt, '2001-12-31' end_dt

Give the subquery the alias levels, and include the employee’s ID, first name, last name, and experience level (levels.name). (Hint: build a join condition using an inequality condition to determine into which level the employee.start_date column falls.)

mysql> SELECT e.emp_id, e.fname, e.lname, levels.name
    -> FROM employee e INNER JOIN
    ->  (SELECT 'trainee' name, '2004-01-01' start_dt, '2005-12-31' end_dt
    ->   UNION ALL
    ->   SELECT 'worker' name, '2002-01-01' start_dt, '2003-12-31' end_dt
    ->   UNION ALL
    ->   SELECT 'mentor' name, '2000-01-01' start_dt, '2001-12-31' end_dt) levels
    ->   ON e.start_date BETWEEN levels.start_dt AND levels.end_dt;
+--------+----------+-----------+---------+
| emp_id | fname    | lname     | name    |
+--------+----------+-----------+---------+
|      6 | Helen    | Fleming   | trainee |
|      7 | Chris    | Tucker    | trainee |
|      2 | Susan    | Barker    | worker  |
|      4 | Susan    | Hawthorne | worker  |
|      5 | John     | Gooding   | worker  |
|      8 | Sarah    | Parker    | worker  |
|      9 | Jane     | Grossman  | worker  |
|     10 | Paula    | Roberts   | worker  |
|     12 | Samantha | Jameson   | worker  |
|     14 | Cindy    | Mason     | worker  |
|     15 | Frank    | Portman   | worker  |
|     17 | Beth     | Fowler    | worker  |
|     18 | Rick     | Tulman    | worker  |
|      1 | Michael  | Smith     | mentor  |
|      3 | Robert   | Tyler     | mentor  |
|     11 | Thomas   | Ziegler   | mentor  |
|     13 | John     | Blake     | mentor  |
|     16 | Theresa  | Markham   | mentor  |
+--------+----------+-----------+---------+
18 rows in set (0.00 sec)

Construct a query against the employee table that retrieves the employee ID, first name, and last name, along with the names of the department and branch to which the employee is assigned. Do not join any tables.

mysql> SELECT e.emp_id, e.fname, e.lname,
    ->  (SELECT d.name FROM department d
    ->   WHERE d.dept_id = e.dept_id) dept_name,
    ->  (SELECT b.name FROM branch b
    ->   WHERE b.branch_id = e.assigned_branch_id) branch_name
    -> FROM employee e;
+--------+----------+-----------+----------------+---------------+
| emp_id | fname    | lname     | dept_name      | branch_name   |
+--------+----------+-----------+----------------+---------------+
|      1 | Michael  | Smith     | Administration | Headquarters  |
|      2 | Susan    | Barker    | Administration | Headquarters  |
|      3 | Robert   | Tyler     | Administration | Headquarters  |
|      4 | Susan    | Hawthorne | Operations     | Headquarters  |
|      5 | John     | Gooding   | Loans          | Headquarters  |
|      6 | Helen    | Fleming   | Operations     | Headquarters  |
|      7 | Chris    | Tucker    | Operations     | Headquarters  |
|      8 | Sarah    | Parker    | Operations     | Headquarters  |
|      9 | Jane     | Grossman  | Operations     | Headquarters  |
|     10 | Paula    | Roberts   | Operations     | Woburn Branch |
|     11 | Thomas   | Ziegler   | Operations     | Woburn Branch |
|     12 | Samantha | Jameson   | Operations     | Woburn Branch |
|     13 | John     | Blake     | Operations     | Quincy Branch |
|     14 | Cindy    | Mason     | Operations     | Quincy Branch |
|     15 | Frank    | Portman   | Operations     | Quincy Branch |
|     16 | Theresa  | Markham   | Operations     | So. NH Branch |
|     17 | Beth     | Fowler    | Operations     | So. NH Branch |
|     18 | Rick     | Tulman    | Operations     | So. NH Branch |
+--------+----------+-----------+----------------+---------------+
18 rows in set (0.12 sec)

Write a query that returns all product names along with the accounts based on that product (use the product_cd column in the account table to link to the product table). Include all products, even if no accounts have been opened for that product.

mysql> SELECT p.product_cd, a.account_id, a.cust_id, a.avail_balance
    -> FROM product p LEFT OUTER JOIN account a
    ->   ON p.product_cd = a.product_cd;
+------------+------------+---------+---------------+
| product_cd | account_id | cust_id | avail_balance |
+------------+------------+---------+---------------+
| AUT        |       NULL |    NULL |          NULL |
| BUS        |         21 |      10 |          0.00 |
| BUS        |         22 |      11 |       9345.55 |
| CD         |          3 |       1 |       3000.00 |
| CD         |         13 |       6 |      10000.00 |
| CD         |         14 |       7 |       5000.00 |
| CD         |         19 |       9 |       1500.00 |
| CHK        |          1 |       1 |       1057.75 |
| CHK        |          4 |       2 |       2258.02 |
| CHK        |          6 |       3 |       1057.75 |
| CHK        |          8 |       4 |        534.12 |
| CHK        |         11 |       5 |       2237.97 |
| CHK        |         12 |       6 |        122.37 |
| CHK        |         15 |       8 |       3487.19 |
| CHK        |         17 |       9 |        125.67 |
| CHK        |         20 |      10 |      23575.12 |
| CHK        |         23 |      12 |      38552.05 |
| MM         |          7 |       3 |       2212.50 |
| MM         |         10 |       4 |       5487.09 |
| MM         |         18 |       9 |       9345.55 |
| MRT        |       NULL |    NULL |          NULL |
| SAV        |          2 |       1 |        500.00 |
| SAV        |          5 |       2 |        200.00 |
| SAV        |          9 |       4 |        767.77 |
| SAV        |         16 |       8 |        387.99 |
| SBL        |         24 |      13 |      50000.00 |
+------------+------------+---------+---------------+
26 rows in set (0.01 sec)

Reformulate your query from Exercise 10-1 to use the other outer join type (e.g., if you used a left outer join in Exercise 10-1, use a right outer join this time) such that the results are identical to Exercise 10-1.

mysql> SELECT p.product_cd, a.account_id, a.cust_id, a.avail_balance
    -> FROM account a RIGHT OUTER JOIN product p
    ->   ON p.product_cd = a.product_cd;
+------------+------------+---------+---------------+
| product_cd | account_id | cust_id | avail_balance |
+------------+------------+---------+---------------+
| AUT        |       NULL |    NULL |          NULL |
| BUS        |         21 |      10 |          0.00 |
| BUS        |         22 |      11 |       9345.55 |
| CD         |          3 |       1 |       3000.00 |
| CD         |         13 |       6 |      10000.00 |
| CD         |         14 |       7 |       5000.00 |
| CD         |         19 |       9 |       1500.00 |
| CHK        |          1 |       1 |       1057.75 |
| CHK        |          4 |       2 |       2258.02 |
| CHK        |          6 |       3 |       1057.75 |
| CHK        |          8 |       4 |        534.12 |
| CHK        |         11 |       5 |       2237.97 |
| CHK        |         12 |       6 |        122.37 |
| CHK        |         15 |       8 |       3487.19 |
| CHK        |         17 |       9 |        125.67 |
| CHK        |         20 |      10 |      23575.12 |
| CHK        |         23 |      12 |      38552.05 |
| MM         |          7 |       3 |       2212.50 |
| MM         |         10 |       4 |       5487.09 |
| MM         |         18 |       9 |       9345.55 |
| MRT        |       NULL |    NULL |          NULL |
| SAV        |          2 |       1 |        500.00 |
| SAV        |          5 |       2 |        200.00 |
| SAV        |          9 |       4 |        767.77 |
| SAV        |         16 |       8 |        387.99 |
| SBL        |         24 |      13 |      50000.00 |
+------------+------------+---------+---------------+
26 rows in set (0.02 sec)

Outer-join the account table to both the individual and business tables (via the account.cust_id column) such that the result set contains one row per account. Columns to include are account.account_id, account.product_cd, individual.fname, individual.lname, and business.name.

mysql> SELECT a.account_id, a.product_cd,
    ->   i.fname, i.lname, b.name
    -> FROM account a LEFT OUTER JOIN business b
    ->   ON a.cust_id = b.cust_id
    ->   LEFT OUTER JOIN individual i
    ->   ON a.cust_id = i.cust_id;
+------------+------------+----------+---------+------------------------+
| account_id | product_cd | fname    | lname   | name                   |
+------------+------------+----------+---------+------------------------+
|          1 | CHK        | James    | Hadley  | NULL                   |
|          2 | SAV        | James    | Hadley  | NULL                   |
|          3 | CD         | James    | Hadley  | NULL                   |
|          4 | CHK        | Susan    | Tingley | NULL                   |
|          5 | SAV        | Susan    | Tingley | NULL                   |
|          6 | CHK        | Frank    | Tucker  | NULL                   |
|          7 | MM         | Frank    | Tucker  | NULL                   |
|          8 | CHK        | John     | Hayward | NULL                   |
|          9 | SAV        | John     | Hayward | NULL                   |
|         10 | MM         | John     | Hayward | NULL                   |
|         11 | CHK        | Charles  | Frasier | NULL                   |
|         12 | CHK        | John     | Spencer | NULL                   |
|         13 | CD         | John     | Spencer | NULL                   |
|         14 | CD         | Margaret | Young   | NULL                   |
|         15 | CHK        | Louis    | Blake   | NULL                   |
|         16 | SAV        | Louis    | Blake   | NULL                   |
|         17 | CHK        | Richard  | Farley  | NULL                   |
|         18 | MM         | Richard  | Farley  | NULL                   |
|         19 | CD         | Richard  | Farley  | NULL                   |
|         20 | CHK        | NULL     | NULL    | Chilton Engineering    |
|         21 | BUS        | NULL     | NULL    | Chilton Engineering    |
|         22 | BUS        | NULL     | NULL    | Northeast Cooling Inc. |
|         23 | CHK        | NULL     | NULL    | Superior Auto Body     |
|         24 | SBL        | NULL     | NULL    | AAA Insurance Inc.     |
+------------+------------+----------+---------+------------------------+
24 rows in set (0.05 sec)

Devise a query that will generate the set {1, 2, 3,..., 99, 100}. (Hint: use a cross join with at least two from clause subqueries.)

SELECT ones.x + tens.x + 1
FROM
 (SELECT 0 x UNION ALL
  SELECT 1 x UNION ALL
  SELECT 2 x UNION ALL
  SELECT 3 x UNION ALL
  SELECT 4 x UNION ALL
  SELECT 5 x UNION ALL
  SELECT 6 x UNION ALL
  SELECT 7 x UNION ALL
  SELECT 8 x UNION ALL
  SELECT 9 x) ones
CROSS JOIN
(SELECT 0 x UNION ALL
  SELECT 10 x UNION ALL
  SELECT 20 x UNION ALL
  SELECT 30 x UNION ALL
  SELECT 40 x UNION ALL
  SELECT 50 x UNION ALL
  SELECT 60 x UNION ALL
  SELECT 70 x UNION ALL
  SELECT 80 x UNION ALL
  SELECT 90 x) tens;

Rewrite the following query, which uses a simple case expression, so that the same results are achieved using a searched case expression. Try to use as few when clauses as possible.

SELECT emp_id,
  CASE title
    WHEN 'President' THEN 'Management'
    WHEN 'Vice President' THEN 'Management'
    WHEN 'Treasurer' THEN 'Management'
    WHEN 'Loan Manager' THEN 'Management'
    WHEN 'Operations Manager' THEN 'Operations'
    WHEN 'Head Teller' THEN 'Operations'
    WHEN 'Teller' THEN 'Operations'
    ELSE 'Unknown'
  END
FROM employee;

 SELECT emp_id,
  CASE
    WHEN title LIKE '%President' OR title = 'Loan Manager'
      OR title = 'Treasurer'
      THEN 'Management'
    WHEN title LIKE '%Teller' OR title = 'Operations Manager'
      THEN 'Operations'
    ELSE 'Unknown'
  END
FROM employee;

Rewrite the following query so that the result set contains a single row with four columns (one for each branch). Name the four columns branch_1 through branch_4.

mysql> SELECT open_branch_id, COUNT(*)
    -> FROM account
    -> GROUP BY open_branch_id;
+----------------+----------+
| open_branch_id | COUNT(*) |
+----------------+----------+
|              1 |        8 |
|              2 |        7 |
|              3 |        3 |
|              4 |        6 |
+----------------+----------+
4 rows in set (0.00 sec)

mysql> SELECT
    ->   SUM(CASE WHEN open_branch_id = 1 THEN 1 ELSE 0 END) branch_1,
    ->   SUM(CASE WHEN open_branch_id = 2 THEN 1 ELSE 0 END) branch_2,
    ->   SUM(CASE WHEN open_branch_id = 3 THEN 1 ELSE 0 END) branch_3,
    ->   SUM(CASE WHEN open_branch_id = 4 THEN 1 ELSE 0 END) branch_4
    -> FROM account;
+----------+----------+----------+----------+
| branch_1 | branch_2 | branch_3 | branch_4 |
+----------+----------+----------+----------+
|        8 |        7 |        3 |        6 |
+----------+----------+----------+----------+
1 row in set (0.02 sec)

Generate a transaction to transfer $50 from Frank Tucker’s money market account to his checking account. You will need to insert two rows into the transaction table and update two rows in the account table.

START TRANSACTION;

SELECT i.cust_id,
 (SELECT a.account_id FROM account a
  WHERE a.cust_id = i.cust_id
    AND a.product_cd = 'MM') mm_id,
 (SELECT a.account_id FROM account a
  WHERE a.cust_id = i.cust_id
    AND a.product_cd = 'chk') chk_id
INTO @cst_id, @mm_id, @chk_id
FROM individual i
WHERE i.fname = 'Frank' AND i.lname = 'Tucker';

INSERT INTO transaction (txn_id, txn_date, account_id,
  txn_type_cd, amount)
VALUES (NULL, now(), @mm_id, 'CDT', 50);

INSERT INTO transaction (txn_id, txn_date, account_id,
  txn_type_cd, amount)
VALUES (NULL, now(), @chk_id, 'DBT', 50);

UPDATE account
SET last_activity_date = now(),
  avail_balance = avail_balance - 50
WHERE account_id = @mm_id;

UPDATE account
SET last_activity_date = now(),
  avail_balance = avail_balance + 50
WHERE account_id = @chk_id;

COMMIT;

Modify the account table so that customers may not have more than one account for each product.

ALTER TABLE account
ADD CONSTRAINT account_unq1 UNIQUE (cust_id, product_cd);

Generate a multicolumn index on the transaction table that could be used by both of the following queries:

SELECT txn_date, account_id, txn_type_cd, amount
FROM transaction
WHERE txn_date > cast('2008-12-31 23:59:59' as datetime);

SELECT txn_date, account_id, txn_type_cd, amount
FROM transaction
WHERE txn_date > cast('2008-12-31 23:59:59' as datetime)
  AND amount < 1000;

CREATE INDEX txn_idx01
ON transaction (txn_date, amount);

Create a view that queries the employee table and generates the following output when queried with no where clause:

+-----------------+------------------+
| supervisor_name | employee_name    |
+-----------------+------------------+
| NULL            | Michael Smith    |
| Michael Smith   | Susan Barker     |
| Michael Smith   | Robert Tyler     |
| Robert Tyler    | Susan Hawthorne  |
| Susan Hawthorne | John Gooding     |
| Susan Hawthorne | Helen Fleming    |
| Helen Fleming   | Chris Tucker     |
| Helen Fleming   | Sarah Parker     |
| Helen Fleming   | Jane Grossman    |
| Susan Hawthorne | Paula Roberts    |
| Paula Roberts   | Thomas Ziegler   |
| Paula Roberts   | Samantha Jameson |
| Susan Hawthorne | John Blake       |
| John Blake      | Cindy Mason      |
| John Blake      | Frank Portman    |
| Susan Hawthorne | Theresa Markham  |
| Theresa Markham | Beth Fowler      |
| Theresa Markham | Rick Tulman      |
+-----------------+------------------+
18 rows in set (1.47 sec)

mysql> CREATE VIEW supervisor_vw
    ->  (supervisor_name,
    ->   employee_name
    ->  )
    -> AS
    -> SELECT concat(spr.fname, ' ', spr.lname),
    ->   concat(emp.fname, ' ', emp.lname)
    -> FROM employee emp LEFT OUTER JOIN employee spr
    ->   ON emp.superior_emp_id = spr.emp_id;
Query OK, 0 rows affected (0.12 sec)

mysql> SELECT * FROM supervisor_vw;
+-----------------+------------------+
| supervisor_name | employee_name    |
+-----------------+------------------+
| NULL            | Michael Smith    |
| Michael Smith   | Susan Barker     |
| Michael Smith   | Robert Tyler     |
| Robert Tyler    | Susan Hawthorne  |
| Susan Hawthorne | John Gooding     |
| Susan Hawthorne | Helen Fleming    |
| Helen Fleming   | Chris Tucker     |
| Helen Fleming   | Sarah Parker     |
| Helen Fleming   | Jane Grossman    |
| Susan Hawthorne | Paula Roberts    |
| Paula Roberts   | Thomas Ziegler   |
| Paula Roberts   | Samantha Jameson |
| Susan Hawthorne | John Blake       |
| John Blake      | Cindy Mason      |
| John Blake      | Frank Portman    |
| Susan Hawthorne | Theresa Markham  |
| Theresa Markham | Beth Fowler      |
| Theresa Markham | Rick Tulman      |
+-----------------+------------------+
18 rows in set (0.17 sec)

The bank president would like to have a report showing the name and city of each branch, along with the total balances of all accounts opened at the branch. Create a view to generate the data.

mysql> CREATE VIEW branch_summary_vw
    ->  (branch_name,
    ->   branch_city,
    ->   total_balance
    ->  )
    -> AS
    -> SELECT b.name, b.city, sum(a.avail_balance)
    -> FROM branch b INNER JOIN account a
    ->   ON b.branch_id = a.open_branch_id
    -> GROUP BY b.name, b.city;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT * FROM branch_summary_vw;
+---------------+-------------+---------------+
| branch_name   | branch_city | total_balance |
+---------------+-------------+---------------+
| Headquarters  | Waltham     |      27882.57 |
| Quincy Branch | Quincy      |      53270.25 |
| So. NH Branch | Salem       |      68240.32 |
| Woburn Branch | Woburn      |      21361.32 |
+---------------+-------------+---------------+
4 rows in set (0.01 sec)

Write a query that lists all the indexes in the bank schema. Include the table names.

mysql> SELECT DISTINCT table_name, index_name
    -> FROM information_schema.statistics
    -> WHERE table_schema = 'bank';
+--------------+--------------------+
| table_name   | index_name         |
+--------------+--------------------+
| account      | PRIMARY            |
| account      | account_unq1       |
| account      | fk_product_cd      |
| account      | fk_a_branch_id     |
| account      | fk_a_emp_id        |
| account      | acc_bal_idx        |
| branch       | PRIMARY            |
| business     | PRIMARY            |
| customer     | PRIMARY            |
| department   | PRIMARY            |
| department   | dept_name_idx      |
| employee     | PRIMARY            |
| employee     | fk_dept_id         |
| employee     | fk_e_branch_id     |
| employee     | fk_e_emp_id        |
| individual   | PRIMARY            |
| officer      | PRIMARY            |
| officer      | fk_o_cust_id       |
| product      | PRIMARY            |
| product      | fk_product_type_cd |
| product_type | PRIMARY            |
| transaction  | PRIMARY            |
| transaction  | fk_t_account_id    |
| transaction  | fk_teller_emp_id   |
| transaction  | fk_exec_branch_id  |
| transaction  | txn_idx01          |
+--------------+--------------------+
26 rows in set (0.00 sec)

Write a query that generates output that can be used to create all of the indexes on the bank.employee table. Output should be of the form:

"ALTER TABLE <table_name> ADD INDEX <index_name> (<column_list>)"

mysql> SELECT concat(
    ->   CASE
    ->     WHEN st.seq_in_index = 1 THEN
    ->       concat('ALTER TABLE ', st.table_name, ' ADD',
    ->         CASE
    ->           WHEN st.non_unique = 0 THEN ' UNIQUE '
    ->           ELSE ' '
    ->         END,
    ->         'INDEX ',
    ->         st.index_name, ' (', st.column_name)
    ->     ELSE concat('  ', st.column_name)
    ->   END,
    ->   CASE
    ->     WHEN st.seq_in_index =
    ->      (SELECT max(st2.seq_in_index)
    ->       FROM information_schema.statistics st2
    ->       WHERE st2.table_schema = st.table_schema
    ->         AND st2.table_name = st.table_name
    ->         AND st2.index_name = st.index_name)
    ->       THEN ');'
    ->     ELSE ''
    ->   END
    ->  ) index_creation_statement
    -> FROM information_schema.statistics st
    -> WHERE st.table_schema = 'bank'
    ->   AND st.table_name = 'employee'
    -> ORDER BY st.index_name, st.seq_in_index;
+---------------------------------------------------------------------+
| index_creation_statement                                            |
+---------------------------------------------------------------------+
| ALTER TABLE employee ADD INDEX fk_dept_id (dept_id);                |
| ALTER TABLE employee ADD INDEX fk_e_branch_id (assigned_branch_id); |
| ALTER TABLE employee ADD INDEX fk_e_emp_id (superior_emp_id);       |
| ALTER TABLE employee ADD UNIQUE INDEX PRIMARY (emp_id);             |
+---------------------------------------------------------------------+
4 rows in set (0.20 sec)