изучаем SQL. Она позволяет решать многошаговые задачи одним выражением

Название	Она позволяет решать многошаговые задачи одним выражением
Дата	09.02.2018
Размер	1.6 Mb.
Формат файла
Имя файла	изучаем SQL.pdf
Тип	Документы #36127
страница	28 из 31

1 ... 23 24 25 26 27 28 29 30 31

Решения к упражнениям
273
3.2
Извлеките ID счета, ID клиента и доступный остаток всех счетов,
имеющих статус 'ACTIVE' (активный) и доступный остаток больше
2500 долларов.
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)
3.3
Напишите запрос к таблице account, возвращающий ID сотрудников, от крывших счета (используйте столбец account.open_emp_id). Результирую щий набор должен включать по одной строке для каждого сотрудника.
mysql> SELECT DISTINCT open_emp_id
> FROM account;
+
+
| open_emp_id |
+
+
| 1 |
| 10 |
| 13 |
| 16 |
+
+
4 rows in set (0.00 sec)
3.4
В этом запросе к нескольким наборам данных заполните пробелы (обо значенные как <число>) так, чтобы получить результат, приведенный ниже:
mysql> SELECT p.product_cd, a.cust_id, a.avail_balance
> FROM product p INNER JOIN account <1>

274
Приложение C
> 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)
Верные значения для <1>, <2> и <3>:
1. a
2. a.product_cd
3. product_type_cd
Глава 4
4.1
Какие ID транзакций возвращают следующие условия фильтрации?
txn_date < '2005 02 26' AND (txn_type_cd = 'DBT' OR amount > 100)
ID транзакций 1, 2, 3, 5, 6 и 7.
4.2
Какие ID транзакций возвращают следующие условия фильтрации?
account_id IN (101,103) AND NOT (txn_type_cd = 'DBT' OR amount > 100)
ID транзакций 4 и 9.

Решения к упражнениям
275
4.3
Создайте запрос, выбирающий все счета, открытые в 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)
4.4
Создайте запрос, выбирающий всех клиентов физических лиц, второй буквой фамилии которых является буква 'a' и есть 'e' в любой пози ции после '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)
Глава 5
5.1
Заполните в следующем запросе пробелы (обозначенные как <число>),
чтобы получить такие результаты:
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 |

276
Приложение C
| 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)
Верные значения для <1> и <2>:
1. branch
2. branch_id
5.2
Напишите запрос, по которому для каждого клиента физического лица
(customer.cust_type_cd = 'I') возвращаются ID счета, федеральный ID
(customer.fed_id) и тип созданного счета (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 |

Решения к упражнениям
277
| 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)
5.3
Создайте запрос для выбора всех сотрудников, начальник которых при писан к другому отделу. Извлечь ID, имя и фамилию сотрудника.
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)
Глава 6
6.1
Имеются множество A = {L M N O P} и множество B = {P Q R S T}. Какие мно жества будут получены в результате следующих операций:
•
A
union B = {L M N O P Q R S T}
•
A
union all B = {L M N O P P Q R S T}
•
A
intersect B = {P}
•
A
except B = {L M N O}
6.2
Напишите составной запрос для выбора имен и фамилий всех клиен тов физических лиц, а также имен и фамилий всех сотрудников.
mysql> SELECT fname, lname
> FROM individual
> UNION
> SELECT fname, lname
> FROM employee;
+
+
+
| fname | lname |
+
+
+

280
Приложение C
7.3
Напишите запрос, возвращающий только значение месяца текущей даты.
mysql> SELECT EXTRACT(MONTH FROM CURRENT_DATE( ));
+
+
| EXTRACT(MONTH FROM CURRENT_DATE) |
+
+
| 5 |
+
+
1 row in set (0.02 sec)
(Если это упражнение выполняется не в мае, полученный результат будет отличаться от приведенного.)
Глава 8
8.1
Создайте запрос для подсчета числа строк в таблице account.
mysql> SELECT COUNT(*)
> FROM account;
+
+
| count(*) |
+
+
| 24 |
+
+
1 row in set (0.32 sec)
8.2
Измените свой запрос из упражнения 8.1 для подсчета числа счетов,
имеющихся у каждого клиента. Для каждого клиента выведите ID
клиента и количество счетов.
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 |

Решения к упражнениям
281
| 11 | 1 |
| 12 | 1 |
| 13 | 1 |
+
+
+
13 rows in set (0.00 sec)
8.3
Измените запрос из упражнения 8.2 так, чтобы в результирующий на бор были включены только клиенты, имеющие не меньше двух счетов.
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)
8.4 (дополнительно)
Найдите общий доступный остаток по типу счета и отделению, где на каждый тип и отделение приходится более одного счета. Результаты должны быть упорядочены по общему остатку (от наибольшего к наи меньшему).
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)

282
Приложение C
Примечание:
MySQL не принимает ORDER BY SUM(avail_balance) DESC,, по этому я был вынужден обозначить столбец сортировки его порядко вым номером.
Глава 9
9.1
Создайте запрос к таблице account, использующий условие фильтра ции с несвязанным подзапросом к таблице product для поиска всех кре дитных счетов (product.product_type_cd = 'LOAN'). Должны быть выбра ны ID счета, код счета, ID клиента и доступный остаток.
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)
9.2
Переработайте запрос из упражнения 9.1, используя связанный подза прос к таблице product для получения того же результата.
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)
9.3
Соедините следующий запрос с таблицей employee, чтобы показать уро вень квалификации каждого сотрудника:

1 ... 23 24 25 26 27 28 29 30 31