test_sql

Номер группы: 9373 ФИО: Заболотников Максим Евгеньевич

Задание	1	2	3	4	5	6	7	8	9	10	11	Сумма
баллы	10	20	20	20	50	50	45	45	70	85	85	500
получено

1. (10 баллов) Какое из следующих выражений выведет итоговую сумму столбца ”price” в таблице ”sales”?

⃝ SELECT TOTALSUM(price) FROM sales

⃝ SELECT SUM(price) WHERE sales

⃝ SELECT SUM(price) FROM sales

⃝ SELECT TOTAL(price) FROM sales

2. (20 баллов) Что будет результатом SQL запроса: SELECT column1 FROM table3 GROUP BY column2 WHERE column2<>NULL

⃝ Результатом будут непустые значения столбца column2

⃝ SQL запрос вызовет ошибку

⃝ Результатом всегда будет пустое множество

3. (20 баллов) Могут ли в запросах SQL одновременно использоваться ”HAVING” и ”WHERE”?

⃝ да

⃝ нет

4. (20 баллов) Какой порядок ключевых слов в команде select правильный?

⃝ SELECT … FROM … GROUP BY … WHERE …

⃝ SELECT … FROM … ORDER BY … WHERE …

⃝ SELECT … FROM … ORDER BY … GROUP BY …

⃝ SELECT … FROM … WHERE … GROUP BY …

Дана таблица T2 с полями a, b, c:

a	b	c
3	6	1
5	2	1

5. (50 баллов) Какое значение вернёт SELECT SUM(a*b*c)-MAX(b)+COUNT(*) FROM T2.

24

6. (50 баллов) Какое значение вернёт SELECT SUM(a)*SUM(b)*SUM(c)-MIN(a*b) FROM T2.

118

В базе данных имеется таблица book со следующими столбцами:

id — уникальный числовой идентификатор издания, первичный ключ;

title — название книги;

author — автор книги (принимаем допущение, что у каждой книги только один автор);

publisher — издательство, в котором вышла книга;

pub_year — год издания книги.

7. (45 баллов) Необходимо выбрать авторов, чьи книги переиздавались в том же или в другом издательстве. Выберите правильный вариант запроса.

⃝ SELECT author FROM book GROUP BY author HAVING count(id)>1;

⃝ SELECT author FROM book GROUP BY author, title HAVING count(id)>1;

⃝ SELECT author FROM book GROUP BY publisher HAVING сount(author)>1;

⃝ SELECT author FROM book WHERE count(id)>1;

8. (45 баллов) Напишите запрос, возвращающий авторов, у которых с 2000 по 2012 год вышло более 5 книг.

SELECT author FROM book WHERE pub_year between 2000 and 2012 GROUP BY author HAVING count(distinct title) > 5

9. (70 баллов) Напишите запрос, возвращающий издательство, которое выпустило в 2012 году наибольшее количество книг

SELECT publisher FROM(SELECT publisher, pub_year, COUNT(id) AS sum FROM book GROUP BY publisher, pub_year HAVING pub_year = 2012) AS a WHERE sum = (SELECT max(sum) FROM (SELECT publisher, pub_year, COUNT(id) AS sum FROM book GROUP BY publisher, pub_year HAVING pub_year = 2012) AS b);

В базе данных имеется таблица client со следующими столбцами:

id_client — уникальный числовой идентификатор абонента, первичный ключ; fullname — ФИО абонента; rate — тариф (руб. за мин.);

id_client	fullname	rate
1	Петров А.А.	1.1
2	Волков П.А.	1.05
3	Зайцев В.В.	1.3
4	Краснова Н.В.	1.1
5	Шульц Ш.	1.4

и таблица client_call со следующими столбцами:

id_client — ссылка на уникальный идентификатор абонента, внешний ключ; call_time — дата и время звонка; duration — длительность звонка (мин.);

id_client	call_time	duration
1	2012.12.05 10:30:52	3
2	2012.12.05 12:10:02	5
2	2012.12.05 12:16:12	1
4	2012.12.05 13:19:22	2
5	2012.12.05 16:10:42	4

10. (85 баллов) Напишите запрос, возвращающий для каждого абонента стоимость всех его разговоров.

SELECT fullname, SUM(rate * duration) FROM client LEFT JOIN client_call ON client.id_client = client_call.id_client GROUP fullname;

11. (85 баллов) Напишите запрос, возвращающий список ФИО абонентов, совершивших только один звонок.

SELECT fullname FROM (SELECT fullname, COUNT(*) AS number FROM client

LEFT JOIN client_call ON client.id_client = client_call.id_client

GROUP BY fullname) AS smth WHERE number = 1;

Задания по Stack Overflow:

1. select count(distinct Location) from Users

Результат: 222074

2. select count(DisplayName) from Users where DisplayName like '%Ivan%' or DisplayName like '%John%'

Результат: 89125

3. select sum(case when summa < 10 then 1 else 0 end) less_than_10, sum(case when summa between 10 and 20 then 1 else 0 end) from_10_to_20, sum(case when summa > 20 then 1 else 0 end) more_than_20 from (select OwnerUserId, count(*) summa from Posts where PostTypeId = 1 group by OwnerUserId) as smth

Результат:

4. –

5. select count(DisplayName) from(select DisplayName from Users except select DisplayName from Users left join Posts on Users.Id = Posts.OwnerUserId where PostTypeId = 1 group by DisplayName) as smth

Результат:

6. select sum(price * quantity) from pizza left join order_content as a on pizza.id = a.id_pizza left join orders as b on a.id_order = b.id_order where b.dat_order < ‘2015-01-01’ and b.dat_order >= ’2014-01-01’

7. select name from client as c left join orders as o on c.id = o.id_client left join order_content as oc on o.id_order = oc.id_order where c.id = o.id_client and (oc.quantity >= 10 and oc.id_pizza = 4) group by name except select name from client as c left join orders as o on c.id = o.id_client left join order_content as oc on o.id_order = oc.id_order where c.id = o.id_client and (oc.id_pizza = 1) group by name

8. –

9. select avg(tot) as avr from (select oc.id_order, o.dat_order, sum(price * oc.quantity) as tot from order_content as oc left join orders as o on o.id_order oc.id_order left join pizza as p on p.id = oc.id_pizza group by oc.id_order, o.dat_oder) as smth where to_char(dat_order, ‘Day’) like ‘%Sunday%’ or to_char(dat_order, ‘Day’) like ‘Saturday’