alan_beaulieu-learning_sql-ru (865932), страница 49

Просмтор этого файла доступен только зарегистрированным пользователям. Но у нас супер быстрая регистрация: достаточно только электронной почты!

Текст из файла (страница 49)

NH Branch ||17 | Beth| Fowler| So. NH Branch ||18 | Rick| Tulman| So. NH Branch |+++++18 rows in set (0.03 sec)Верные значения для <1> и <2>:1. branch2. branch_id5.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 | 111111111 | checking account||2 | 111111111 | savings account||3 | 111111111 | certificate of deposit ||4 | 222222222 | checking account||5 | 222222222 | savings account||6 | 333333333 | checking account||7 | 333333333 | money market account ||8 | 444444444 | checking account||9 | 444444444 | savings account||10 | 444444444 | money market account ||11 | 555555555 | checking account||12 | 666666666 | checking account||13 | 666666666 | certificate of deposit ||14 | 777777777 | certificate of deposit |Решения к упражнениям277|15 | 888888888 | checking account||16 | 888888888 | savings account||17 | 999999999 | checking account||18 | 999999999 | money market account ||19 | 999999999 | 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)Глава 66.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|+++278Приложение C| 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)6.3Отсортируйте результаты упражнения 6.2 по столбцу lname.mysql> SELECT fname, lname> FROM individual> UNION ALL> SELECT fname, name> FROM employee> ORDER BY lname;+++| fname| lname|+++| Susan| Barker|| Louis| Blake|| John| Blake|| Richard | Farley|| Helen| Fleming || Beth| Fowler|| Charles | Frasier || John| Gooding |Решения к упражнениям279| 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)Глава 77.1Написать запрос, возвращающий 17–25 символы строки «Please findthe 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)7.2Напишите запрос, возвращающий абсолютную величину и знак (1, 0или 1) числа –25,768 23.

Также возвратите число, округленное до сотых.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)280Приложение C7.3Напишите запрос, возвращающий только значение месяца текущейдаты.mysql> SELECT EXTRACT(MONTH FROM CURRENT_DATE( ));++| EXTRACT(MONTH FROM CURRENT_DATE) |++|5 |++1 row in set (0.02 sec)(Если это упражнение выполняется не в мае, полученный результатбудет отличаться от приведенного.)Глава 88.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,, поэтому я был вынужден обозначить столбец сортировки его порядковым номером.Глава 99.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, чтобы показать уровень квалификации каждого сотрудника:Решения к упражнениям283SELECT 'trainee' name, '20040101' start_dt, '20051231' end_dtUNION ALLSELECT 'worker' name, '20020101' start_dt, '20031231' end_dtUNION ALLSELECT 'mentor' name, '20000101' start_dt, '20011231' end_dtДайте подзапросу псевдоним levels (уровни) и включите ID сотрудника, имя, фамилию и квалификацию (levels.name).

(Совет: в условии соединения определяйте диапазон, в который попадает столбец employee.start_date, с помощью условия неравенства.)mysql> SELECT e.emp_id, e.fname, e.lname, levels.name> FROM employee e INNER JOIN> (SELECT 'trainee' name, '20040101' start_dt, '20051231' end_dt> UNION ALL> SELECT 'worker' name, '20020101' start_dt, '20031231' end_dt> UNION ALL> SELECT 'mentor' name, '20000101' start_dt, '20011231' 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)9.4Создайте запрос к таблице employee для получения ID, имени и фамилии сотрудника вместе с названиями отдела и отделения, к которымон приписан.

Не используйте соединение таблиц.mysql> SELECT e.emp_id, e.fname, e.lname,> (SELECT d.name FROM department d> WHERE d.dept_id = e.dept_id) dept_name,284Приложение C> (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)Глава 1010.1Напишите запрос, возвращающий все типы счетов и открытые счетаэтих типов (для соединения с таблицей product используйте столбецproduct_cd таблицы account). Должны быть включены все типы счетов,даже если не был открыт ни один счет определенного типа.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 |Решения к упражнениям285| 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)10.2Переформулируйте запрос из упражнения 10.1 и примените другойтип внешнего соединения (т.

е. если в упражнении 10.1 использовалось левостороннее внешнее соединение, используйте правостороннее), так чтобы результаты были, как в упражнении 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 |286Приложение C| 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)10.3Проведите внешнее соединение таблицы account с таблицами individual и business (посредством столбца account.cust_id) таким образом,чтобы результирующий набор содержал по одной строке для каждогосчета.

Характеристики

Список файлов ответов (шпаргалок)