Ниже представлены решения заданий № 45-66 из онлайн тренажера на сайте SQL ACADEMY (sql-academy.org).
Ответы на задания 1-22 (часть 1) здесь.
Ответы на задания 23-44 (часть 2) тут.
Задание 45. Какой(ие) кабинет(ы) пользуются самым большим спросом?
SELECT classroom
FROM Schedule
GROUP BY classroom
HAVING COUNT(classroom) =
(SELECT COUNT(classroom)
FROM Schedule
GROUP BY classroom
ORDER BY COUNT(classroom) DESC
LIMIT 1)Задание 46. В каких классах введет занятия преподаватель “Krauze” ?
SELECT DISTINCT name
FROM Class
JOIN Schedule
ON Class.id=Schedule.class
JOIN Teacher
ON Schedule.teacher=Teacher.id
WHERE Teacher.last_name='Krauze';Задание 47. Сколько занятий провел Krauze 30 августа 2019 г.?
SELECT COUNT(teacher) AS count
FROM Schedule
WHERE date='2019-08-30'and teacher=(
SELECT id
FROM Teacher
WHERE last_name='Krauze');Задание 48. Выведите заполненность классов в порядке убывания
SELECT c.name, COUNT(sc.student) AS count
FROM Class AS c
JOIN Student_in_class AS sc
ON c.id=sc.class
GROUP BY c.id
ORDER BY count DESC;Задание 49. Какой процент обучающихся учится в 10 A классе ?
SELECT COUNT(student) * 100 / (SELECT COUNT(student) FROM Student_in_class) AS percent
FROM Student_in_class
JOIN Class
ON Student_in_class.class=Class.id
WHERE name='10 A';Задание 50. Какой процент обучающихся родился в 2000 году? Результат округлить до целого в меньшую сторону.
SELECT FLOOR(COUNT(id)*100/(SELECT COUNT(id) FROM Student)) AS percent
FROM Student
WHERE YEAR(birthday)=2000;Задание 51. Добавьте товар с именем “Cheese” и типом “food” в список товаров (Goods). В качестве первичного ключа (good_id) укажите количество записей в таблице + 1.
INSERT INTO Goods
SET good_id=(SELECT COUNT(*)+1 FROM Goods AS a),
good_name='Cheese',
type=(SELECT good_type_id FROM GoodTypes WHERE good_type_name='food');Задание 52. Добавьте в список типов товаров (GoodTypes) новый тип “auto”. В качестве первичного ключа (good_type_id) укажите количество записей в таблице + 1
INSERT INTO GoodTypes
SET good_type_id=(SELECT COUNT(*)+1 FROM GoodTypes AS a),
good_type_name='auto';Задание 53. Измените имя “Andie Quincey” на новое “Andie Anthony”.
UPDATE FamilyMembers
SET member_name='Andie Anthony'
WHERE member_name='Andie Quincey';Задание 54. Удалить всех членов семьи с фамилией “Quincey”.
DELETE FROM FamilyMembers
WHERE member_name LIKE '%Quincey';Задание 55. Удалить компании, совершившие наименьшее количество рейсов.
DELETE FROM Company
WHERE Company.id IN (
SELECT company FROM Trip
GROUP BY company
HAVING COUNT(id) = (SELECT MIN(count) FROM (SELECT COUNT(id) AS count FROM Trip GROUP BY company) AS min_count)
);Задание 56. Удалить все перелеты, совершенные из Москвы (Moscow).
DELETE FROM Trip
WHERE town_from='Moscow';Задание 57. Перенести расписание всех занятий на 30 мин. вперед.
UPDATE Timepair
SET start_pair=start_pair + INTERVAL 30 MINUTE,
end_pair=end_pair + INTERVAL 30 MINUTE;Задание 58. Добавить отзыв с рейтингом 5 на жилье, находящиеся по адресу “11218, Friel Place, New York”, от имени “George Clooney”.В качестве первичного ключа (id) укажите количество записей в таблице + 1.
INSERT INTO Reviews
SET id=(SELECT COUNT(*)+1 FROM Reviews AS a),
rating=5,
reservation_id= (SELECT r.id FROM Reservations AS r
JOIN Rooms ON r.room_id=Rooms.id
JOIN Users ON r.user_id=Users.id
WHERE address='11218, Friel Place, New York'
AND name='George Clooney');Задание 59. Вывести пользователей, указавших Белорусский номер телефона ? Телефонный код Белоруссии +375.
SELECT *
FROM Users
WHERE phone_number LIKE '+375%';Задание 60. Выведите идентификаторы преподавателей, которые хотя бы один раз за всё время преподавали в каждом из одиннадцатых классов.
SELECT teacher
FROM Schedule
JOIN Class
ON Schedule.class=Class.id
WHERE name LIKE '11%'
GROUP BY teacher
HAVING COUNT(DISTINCT name) = 2;Задание 61. Выведите список комнат, которые были зарезервированы в течение 12 недели 2020 года.
SELECT DISTINCT Rooms.*
FROM Rooms
JOIN Reservations
ON Rooms.id=Reservations.room_id
WHERE WEEK(start_date, 1) = 12 AND YEAR(start_date)=2020;Задание 62. Вывести в порядке убывания популярности доменные имена 2-го уровня, используемые пользователями для электронной почты.
Полученный результат необходимо дополнительно отсортировать по возрастанию названий доменных имён.
SELECT SUBSTRING_INDEX(email,'@',-1) AS domain,
COUNT(SUBSTRING_INDEX(email,'@',-1)) AS count
FROM Users
GROUP BY domain
ORDER BY count DESC, domain;Задание 63. Выведите отсортированный список (по возрастанию) имен студентов в виде Фамилия.И.О.
SELECT CONCAT(last_name, '.', LEFT(first_name, 1), '.', LEFT(middle_name, 1), '.') AS name
FROM Student
ORDER BY last_name, first_name;Задание 64. Выведите имена всех пар пассажиров, летевших вместе на одном рейсе два или более раз, и количество таких совместных рейсов.
В passengerName1 разместите имя пассажира с наименьшим идентификатором.
SELECT passengerName1, passengerName2, COUNT(tr1) AS COUNT
FROM (SELECT Passenger.id AS p1, name AS passengerName1, trip AS tr1
FROM Passenger
INNER JOIN Pass_in_trip
ON Passenger.id = Pass_in_trip.passenger
GROUP BY Passenger.id, name, trip) AS UP1
INNER JOIN (SELECT Passenger.id AS p2, name AS passengerName2, trip AS tr2
FROM Passenger
INNER JOIN Pass_in_trip
ON Passenger.id = Pass_in_trip.passenger
GROUP BY Passenger.id, name, trip) AS UP2
ON UP1.tr1 = UP2.tr2
WHERE (p1<p2) GROUP BY passengerName1, passengerName2
HAVING (COUNT(tr1)>1);Задание 65. Необходимо вывести рейтинг для комнат, которые хоть раз арендовали, как среднее значение рейтинга отзывов округленное до целого вниз.
SELECT room_id, FLOOR(AVG(rating)) AS rating
FROM Reservations
JOIN Reviews
ON Reservations.id=Reviews.reservation_id
GROUP BY room_id
ORDER BY rating DESC; -- Сортировка не обязательна по условию задачиЗадание 66. Вывести список комнат со всеми удобствами (наличие ТВ, интернета, кухни и кондиционера), а также общее количество дней и сумму за все дни аренды каждой из таких комнат.
Если комната не сдавалась, то количество дней и сумму вывести как 0.
SELECT home_type, address, IFNULL(SUM(TIMESTAMPDIFF(DAY,start_date,end_date)), 0) AS days, IFNULL(SUM(total), 0) AS total_fee
FROM Rooms
LEFT JOIN Reservations
ON Rooms.id=Reservations.room_id
WHERE (has_tv, has_internet, has_kitchen, has_air_con) = (1,1,1,1)
GROUP BY Rooms.id;$begingroup$
I have solved the task of
Delete the companies that made the least number of flights.
the visual table diagram is here. The exercise is here
This is my source code -it’s «arrow antipattern» , what is more elegant solution?
the inner most counts num of flights and groups by company, this is used to compare and find the actual minimum, then just select company names and delete.
DELETE FROM company
WHERE name IN (SELECT name
FROM (SELECT name
FROM trip
INNER JOIN company
ON trip.company = company.id
GROUP BY name
HAVING Count(trip.id) = (SELECT Min(kolv) AS mini
FROM (SELECT
Count(trip.id) AS kolv
FROM trip
INNER JOIN company
ON trip.company
=
company.id
GROUP BY name
ORDER BY kolv)k)) o)
Setris
1,4131 gold badge7 silver badges16 bronze badges
asked Feb 16, 2021 at 20:34
$endgroup$
5
$begingroup$
- If you look at the visual table diagram on the site you linked, for the
Companytable, theidis the primary key and therefore is guaranteed to be unique for each row in the table.name, on the other hand, is not guaranteed to be unique. So in your query, you should be grouping and deleting companies by theirid, not by theirname. - Your query seems to be doing the work of counting the number of trips per company twice (once at the part
HAVING Count(trip.id)and again at the partCount(trip.id) AS kolv) which is not necessary. - As @Flavian and @RickJames have mentioned, refactoring your query to use Common Table Expressions will save it from the arrow anti-pattern, and make it much more readable.
An example refactor:
WITH TripsPerCompany AS (
SELECT company, COUNT(id) AS num_trips FROM Trip GROUP BY company
)
,CompaniesWithTheFewestTrips AS (
SELECT company
FROM TripsPerCompany
WHERE num_trips = (SELECT MIN(num_trips) FROM TripsPerCompany)
)
DELETE Company
FROM Company
JOIN CompaniesWithTheFewestTrips
ON Company.id = CompaniesWithTheFewestTrips.company;
answered Mar 29, 2021 at 8:25
SetrisSetris
1,4131 gold badge7 silver badges16 bronze badges
$endgroup$
$begingroup$
If you can with MySQL 8.0, use the WITH Common Table Expression to simplify this query.
The idea is to extract out the nested queries into ones in the WITH statement before the main query and then use them just like tables in the main query.
Toby Speight
67.6k14 gold badges84 silver badges235 bronze badges
answered Feb 16, 2021 at 22:43
$endgroup$
$begingroup$
- Use the multi-table flavor of
DELETE. Why? To avoid the «arrow». And possibly the performance will be better. - Avoid
IN ( SELECT ... ); instead, try to useJOIN(or maybeLEFT JOIN). The Optimizer has been notoriously poor at optimizingIN (SELECT...). I don’t know whether it will do a good job here — please addEXPLAIN SELECT .... - Alternatively, do the task in steps — Create a temp table with, say, the innermost pair of queries, then use that temp in the rest. When doing this, be sure to explain what that temp table represents. I make this suggestion because my head is still spinning from the «arrow».
- Use CTE, if available, instead of a temp table. (There is a lot of overlap in the benefits/drawbacks between temp tables and CTEs. CTEs are the «modern» answer, where applicable.)
answered Mar 29, 2021 at 3:34
Rick JamesRick James
2741 silver badge8 bronze badges
$endgroup$
3
Помогаю со студенческими работами здесь
Удалить из списка наименьшее количество чисел чтобы получилась возрастающая последовательность
В списке L записано 100 целых чисел.Удалитt из списка наименьшее количество чисел, чтобы получилась…
Найти вероятность того, что из 400 рейсов, запланированных на ноябрь, будет отложено 50 рейсов
По данным метеослужбы аэропорта в ноябре из-за плохих метеоусловий откладывается 10% рей-сов. Найти…
По данным автопарка определить общий процент порожних рейсов и месяц с максимальным количеством таких рейсов
В автопарке 20 шоферов, о каждом из которых известно:
ежемесячное общее количество рейсов и…
Удалить наименьшее одинаковое количество синих и красных точек так, чтобы выполнялось условие
программа выдает s как пустую стироку вот условие
Дана цепочка, состоящая из синих (B) и красных…
вывести Наименьшее количество элементов, которое нужно удалить, чтоб осталась возрастающая последовательность
Нужно в массиве вывести Наименьшее количество элементов, которое нужно удалить, чтоб осталась…
В заданной строке вывести все слова, имеющие наименьшее количество букв и удалить начинающиеся с маленькой буквы
С клавиатуры вводиться строка, написать программу 1) которая выводит на экран слова, которые имеют…
Искать еще темы с ответами
Или воспользуйтесь поиском по форуму:
1
SQL-Academy.org
Задание 1: Вывести имена всех когда-либо обслуживаемых пассажиров авиакомпаний
SELECT name from Passenger
Задание 2: Вывести названия всеx авиакомпаний
SELECT name FROM Company;
Задание 3: Вывести все рейсы, совершенные из Москвы
SELECT * FROM Trip
WHERE town_from = ‘Moscow’;
Задание 4: Вывести имена людей, которые заканчиваются на «man»
SELECT name FROM Passenger
WHERE name LIKE ‘%man’;
Задание 5: Вывести количество рейсов, совершенных на TU-134
SELECT DISTINCT COUNT(‘plane’) AS count FROM Trip
WHERE plane LIKE ‘TU-134’;
Задание 6: Какие компании совершали перелеты на Boeing
SELECT Company.name FROM Trip
LEFT JOIN Company
ON Company.id = Trip.company
WHERE plane = ‘Boeing’
GROUP BY company;
Задание 7: Вывести все названия самолётов, на которых можно улететь в Москву (Moscow)
SELECT plane FROM Trip
WHERE town_to = ‘Moscow’
GROUP BY plane;
Задание 8: В какие города можно улететь из Парижа (Paris) и сколько времени это займёт?
SELECT town_to, TIMEDIFF(time_in, time_out) AS flight_time FROM Trip WHERE town_from = ‘Paris’;
Задание 9:
SELECT name FROM Company AS c
LEFT JOIN Trip AS t
ON c.id = t.company
WHERE t.town_from = ‘Vladivostok’;
Задание 10: Вывести вылеты, совершенные с 10 ч. по 14 ч. 1 января 1900 г.
SELECT * FROM Trip
WHERE time_out BETWEEN ‘1900-01-01T10:00:00.000Z’ AND ‘1900-01-01T14:00:00.000Z’;
Задание 11: Вывести пассажиров с самым длинным именем
SELECT name FROM Passenger
ORDER BY LENGTH(name) DESC LIMIT 1;
Задание 12: Вывести id и количество пассажиров для всех прошедших полётов
SELECT trip, COUNT(passenger) AS count FROM Pass_in_trip
GROUP BY trip;
Задание 13: Вывести имена людей, у которых есть полный тёзка среди пассажиров
SELECT name FROM Passenger GROUP BY name HAVING COUNT(*) > 1;
Задание 14: В какие города летал Bruce Willis
SELECT t.town_to FROM Trip AS t
JOIN Pass_in_trip AS pit
ON t.id = trip
JOIN Passenger AS p
ON p.id = passenger
WHERE name = ‘Bruce Willis’;
Задание 15: Во сколько Стив Мартин (Steve Martin) прилетел в Лондон (London)
SELECT t.time_in FROM Trip AS t
JOIN Pass_in_trip AS pit
ON t.id = trip
JOIN Passenger AS p
ON p.id = passenger
WHERE name = ‘Steve Martin’ AND town_to = ‘London’;
Задание 16: Вывести отсортированный по количеству перелетов (по убыванию) и имени (по возрастанию) список пассажиров, совершивших хотя бы 1 полет.
SELECT p.name, COUNT(passenger) AS count FROM Trip AS t
JOIN Pass_in_trip AS pit
ON t.id = trip
JOIN Passenger AS p
ON p.id = passenger
GROUP BY p.name
HAVING count >= 1
ORDER BY count DESC, p.name ASC;
Задание 17: Определить, сколько потратил в 2005 году каждый из членов семьи
SELECT member_name, status, SUM(unit_price * amount) as costs FROM Payments AS p
JOIN FamilyMembers AS fm
ON p.family_member = fm.member_id
WHERE date LIKE ‘2005%’
GROUP BY family_member;
Задание 18: Узнать, кто старше всех в семьe
SELECT member_name FROM FamilyMembers
WHERE birthday = (SELECT MIN(birthday) FROM FamilyMembers);
Задание 19: Определить, кто из членов семьи покупал картошку (potato)
SELECT status FROM FamilyMembers AS fm
JOIN Payments AS p
ON fm.member_id = p.family_member
JOIN Goods AS g
ON p.good = g.good_id
WHERE good_name LIKE ‘potato’ GROUP BY status;
Задание 20: Сколько и кто из семьи потратил на развлечения (entertainment). Вывести статус в семье, имя, сумму
SELECT status, member_name, SUM(unit_price*amount) AS costs FROM FamilyMembers AS fm
JOIN Payments AS p
ON fm.member_id = p.family_member
JOIN Goods AS g
ON p.good = g.good_id
JOIN GoodTypes as gp
ON g.type = gp.good_type_id
WHERE good_type_name = ‘entertainment’
GROUP BY family_member;
Задание 21: Определить товары, которые покупали более 1 раза
SELECT good_name FROM Payments AS p
JOIN Goods as g
ON p.good = g.good_id
GROUP BY good
HAVING COUNT(good_name) > 1;
Задание 22: Найти имена всех матерей (mother)
SELECT member_name FROM FamilyMembers
WHERE status = ‘mother’;
Задание 23: SELECT good_name, unit_price FROM Payments AS p
JOIN Goods AS g
ON p.good = g.good_id
JOIN GoodTypes as gp
ON g.type = gp.good_type_id
WHERE good_type_name = ‘delicacies’
LIMIT 1;
Задание 24: Определить кто и сколько потратил в июне 2005
SELECT member_name, SUM(unit_price*amount) as costs FROM Payments as p
JOIN FamilyMembers as fm
ON p.family_member = fm.member_id
WHERE date LIKE ‘2005-06%’
GROUP BY member_name;
Задание 25: Определить, какие товары имеются в таблице Goods, но не покупались в течение 2005 года
SELECT good_name FROM Goods
LEFT JOIN Payments ON
Goods.good_id = Payments.good
AND YEAR(Payments.date) = 2005
WHERE Payments.good IS NULL
GROUP BY good_id;
SELECT good_name, good_id, good, date FROM Goods as g
LEFT OUTER JOIN Payments as p
ON g.good_id = p.good
WHERE date IS NULL OR date NOT LIKE ‘2005%’
ORDER BY good;
Задание 26: Определить группы товаров, которые не приобретались в 2005 году
ГРУППЫ, ТОВАРЫ, КОГДА ПРИОБРЕТАЛИСЬ:
SELECT good_type_name, good_name, good_id, good, payment_id, date FROM Goods JOIN Payments ON Goods.good_id = Payments.good
JOIN GoodTypes ON GoodTypes.good_type_id = Goods.type;
РЕШЕНИЕ:
SELECT good_type_name FROM GoodTypes
WHERE good_type_id NOT IN (SELECT good_type_id FROM Goods
JOIN Payments ON Goods.good_id = Payments.good AND YEAR(date) = 2005
JOIN GoodTypes ON GoodTypes.good_type_id = Goods.type);
Задание 27: Узнать, сколько потрачено на каждую из групп товаров в 2005 году. Вывести название группы и сумму
SELECT good_type_name, SUM(amount*unit_price) AS costs FROM GoodTypes
JOIN Goods ON good_type_id = type
JOIN Payments ON good = good_id AND YEAR(date) = 2005
GROUP BY good_type_name;
Задание 28: Сколько рейсов совершили авиакомпании с Ростова (Rostov) в Москву (Moscow) ?
SELECT COUNT(id) AS count FROM Trip
WHERE town_from = ‘Rostov’ AND town_to = ‘Moscow’;
Задание 29: Выведите имена пассажиров улетевших в Москву (Moscow) на самолете TU-134
SELECT DISTINCT name FROM Passenger
JOIN Pass_in_trip ON Passenger.id = Pass_in_trip.passenger
JOIN Trip ON Pass_in_trip.trip = Trip.id
WHERE plane = ‘TU-134’ AND town_to = ‘Moscow’;
Задание 30: Выведите нагруженность (число пассажиров) каждого рейса (trip). Результат вывести в отсортированном виде по убыванию нагруженности.
SELECT trip, COUNT(passenger) AS count FROM Passenger
JOIN Pass_in_trip ON Passenger.id = Pass_in_trip.passenger
JOIN Trip ON Pass_in_trip.trip = Trip.id
GROUP BY trip ORDER BY count DESC;
Задание 31: Вывести всех членов семьи с фамилией Quincey.
SELECT * FROM FamilyMembers
WHERE member_name LIKE ‘%Quincey’;
Задание 32: Вывести средний возраст людей (в годах), хранящихся в базе данных. Результат округлите до целого в меньшую сторону.
SELECT FLOOR(AVG(FLOOR(DATEDIFF(NOW(), birthday)/365))) AS age FROM FamilyMembers;
Задание 33: Найдите среднюю стоимость икры. В базе данных хранятся данные о покупках красной (red caviar) и черной икры (black caviar).
SELECT AVG(unit_price) AS cost FROM Payments
JOIN Goods ON good=good_id
WHERE good_name = ‘red caviar’ OR good_name = ‘black caviar’;
Задание 34: Сколько всего 10-ых классов?
SELECT COUNT(name) AS count FROM Class WHERE name LIKE ‘10%’;
Задание 35: Сколько различных кабинетов школы использовались 2.09.2019 в образовательных целях ?
SELECT DISTINCT COUNT(classroom) AS count FROM Schedule
WHERE date LIKE ‘2019-09-02%’;
Задание 36: Выведите информацию об обучающихся живущих на улице Пушкина (ul. Pushkina)?
SELECT * FROM Student WHERE address LIKE ‘%Pushkina%’;
Задание 37: Сколько лет самому молодому обучающемуся ?
SELECT ROUND(MIN(DATEDIFF(NOW(), birthday)/365)) AS year FROM Student;
SELECT FLOOR(MIN(DATEDIFF(NOW(), birthday)/365)) AS year FROM Student;
Задание 38:
SELECT COUNT(1) As count FROM Student WHERE first_name LIKE ‘Anna’;
Задание 39:
-
SELECT COUNT(class) AS count FROM Student_in_class
JOIN Class ON Class.id=class WHERE name LIKE ’10 B’; -
SELECT COUNT(class) AS count FROM Student_in_class
JOIN Class ON Class.id=class AND name = ’10 B’;
Задание 40: Выведите название предметов, которые преподает Ромашкин П.П. (Romashkin P.P.) ?
SELECT DISTINCT(Subject.name) AS subjects FROM Subject
JOIN Schedule ON Subject.id=Schedule.subject
JOIN Teacher ON Teacher.id=Schedule.teacher AND last_name=’Romashkin’;
Задание 41: Во сколько начинается 4-ый учебный предмет по расписанию ?
SELECT start_pair FROM Timepair WHERE id = 4;
SELECT start_pair FROM Timepair LIMIT 3, 1;
SELECT start_pair FROM Timepair LIMIT 1 OFFSET 3;
Задание 42: Сколько времени обучающийся будет находиться в школе, учась со 2-го по 4-ый уч. предмет ?
SELECT DISTINCT TIMEDIFF((SELECT end_pair FROM Timepair WHERE id = 4), (SELECT start_pair FROM Timepair WHERE id = 2)) as time FROM Timepair;
Задание 43: Выведите фамилии преподавателей, которые ведут физическую культуру (Physical Culture). Остортируйте преподавателей по фамилии.
SELECT last_name FROM Teacher
JOIN Schedule ON Teacher.id=Schedule.teacher
JOIN Subject ON Subject.id=Schedule.subject
WHERE Subject.name=’Physical Culture’ ORDER BY last_name ASC;
Задание 44: Найдите максимальный возраст (колич. лет) среди обучающихся 10 классов ?
SELECT FLOOR(MAX((DATEDIFF(NOW(), birthday)/365))) AS max_year FROM Student
JOIN Student_in_class ON Student.id=Student_in_class.student
JOIN Class ON Class.id=Student_in_class.class WHERE Class.name LIKE ‘10%’;
Задание 45: Какой(ие) кабинет(ы) пользуются самым большим спросом?
SELECT classroom, COUNT(classroom) as count FROM Schedule
GROUP BY classroom
HAVING COUNT() > 4
ORDER BY COUNT() DESC; — какие кабинеты в топе?
Задание 46: В каких классах введет занятия преподаватель «Krauze» ?
SELECT DISTINCT name FROM Class
JOIN Schedule ON Class.id=Schedule.class
JOIN Teacher ON Teacher.id=Schedule.teacher
WHERE last_name = ‘Krauze’;
Задание 47: Сколько занятий провел Krauze 30 августа 2019 г.?
SELECT COUNT(teacher) AS count FROM Schedule
JOIN Teacher ON Teacher.id=Schedule.teacher AND last_name = ‘Krauze’
WHERE date LIKE ‘2019-08-30%’;
Задание 48: Выведите заполненность классов в порядке убывания
SELECT name, COUNT(class) as count FROM Class
JOIN Student_in_class ON Class.id=Student_in_class.class
GROUP BY name ORDER BY COUNT(*) DESC;
Задание 49: Какой процент обучающихся учится в 10 A классе ?
SELECT (COUNT(*)*100/(SELECT COUNT(Student.id) as count FROM Student
JOIN Student_in_class ON Student.id=Student_in_class.student)) AS percent
FROM Student_in_class
JOIN Class ON Class.id=Student_in_class.class AND name = ’10 A’;
Задание 50: Какой процент обучающихся родился в 2000 году? Результат округлить до целого в меньшую сторону.
SELECT FLOOR((COUNT(*)*100/(SELECT COUNT(Student.id) as count FROM Student
JOIN Student_in_class ON Student.id=Student_in_class.student))) AS percent FROM Student
WHERE YEAR(birthday) = 2000;
Задание 51: Добавьте товар с именем «Cheese» и типом «food» в список товаров (Goods).
INSERT INTO Goods(good_id, good_name, type) VALUES (17, ‘Cheese’, 2);
Задание 52: Добавьте в список типов товаров (GoodTypes) новый тип «auto».
INSERT INTO GoodTypes(good_type_id, good_type_name) VALUES (9, ‘auto’);
Задание 53: Измените имя «Andie Quincey» на новое «Andie Anthony».
UPDATE FamilyMembers SET member_name=’Andie Anthony’ WHERE member_id=3;
Задание 54: Удалить всех членов семьи с фамилией «Quincey».
DELETE FROM FamilyMembers WHERE member_name LIKE ‘%Quincey’;
Задание 55: Удалить компании, совершившие наименьшее количество рейсов.
SELECT name, COUNT(company) as company FROM Trip
JOIN Company ON Company.id=Trip.company GROUP BY name;
DELETE FROM Company WHERE id = 4;
DELETE FROM Company WHERE id = 3;
DELETE FROM Company WHERE id = 2;
Задание 56: Удалить все перелеты, совершенные из Москвы (Moscow).
DELETE FROM Trip WHERE town_from LIKE ‘%Moscow’;
Задание 57: Перенести расписание всех занятий на 30 мин. вперед.
UPDATE Timepair SET start_pair = DATE_ADD(start_pair, INTERVAL 30 MINUTE);
UPDATE Timepair SET end_pair = DATE_ADD(end_pair, INTERVAL 30 MINUTE);
Задание 58: Добавить отзыв с рейтингом 5 на жилье, находящиеся по адресу «11218, Friel Place, New York», от имени «George Clooney»
SELECT Users.name, Reservations.* FROM Reservations
JOIN Rooms ON Rooms.id=Reservations.room_id
JOIN Users ON Users.id=Reservations.user_id
WHERE address = ‘11218, Friel Place, New York’
INSERT INTO Reviews (id, reservation_id, rating) VALUES (23, 2, 5);
Задание 59: Вывести пользователей,указавших Белорусский номер телефона ? Телефонный код Белоруссии +375.
SELECT * FROM Users WHERE phone_number LIKE ‘+375%’;
Задание 60: Выведите идентификаторы преподавателей, которые хотя бы один раз за всё время преподавали в каждом из одиннадцатых классов.
SELECT teacher FROM Schedule
JOIN Teacher ON Teacher.id=Schedule.teacher
JOIN Subject ON Subject.id=Schedule.subject
JOIN Class ON Class.id=Schedule.class
WHERE Class.name IN (’11 A’, ’11 B’)
GROUP BY teacher HAVING COUNT(teacher)>=1
ORDER BY teacher;
Задание 61: Выведите список комнат, которые были зарезервированы в течение 12 недели 2020 года.
SELECT Rooms.* FROM Rooms
JOIN Reservations ON Rooms.id=Reservations.room_id AND YEAR(start_date)=2020 AND YEAR(end_date)=2020
WHERE WEEK(start_date, 1)=12 OR WEEK(end_date, 1)=12;
Задание 62: Вывести в порядке убывания популярности доменные имена 2-го уровня, используемые пользователями для электронной почты. Полученный результат необходимо дополнительно отсортировать по возрастанию названий доменных имён.
SELECT SUBSTRING_INDEX(email, ‘@’, -1) as domain, count(*) AS count FROM Users
GROUP BY domain
ORDER BY count DESC, domain ASC;
Задание 63: Выведите отсортированный список (по возрастанию) имен студентов в виде Фамилия.И.О.
SELECT CONCAT(last_name, ‘.’, LEFT(first_name, 1), ‘.’, LEFT(middle_name, 1), ‘.’) AS name FROM Student ORDER BY first_name ASC;
Задание 64: Выведите имена всех пар пассажиров, летевших вместе на одном рейсе два или более раз, и количество таких совместных рейсов. В passengerName1 разместите имя пассажира с наименьшим идентификатором.
Задание 65: Необходимо вывести рейтинг для комнат, которые хоть раз арендовали, как среднее значение рейтинга отзывов округленное до целого вниз.
SELECT room_id, FLOOR(AVG(rating)) AS rating FROM Reservations
JOIN Reviews ON Reviews.reservation_id=Reservations.id
GROUP BY room_id;
Задание 66: Вывести список комнат со всеми удобствами (наличие ТВ, интернета, кухни и кондиционера), а также общее количество дней и сумму за все дни аренды каждой из таких комнат.
SELECT home_type, address, COALESCE(SUM(DATEDIFF(end_date, start_date)), 0) as days, COALESCE(SUM(Reservations.total), 0) AS total_fee FROM Reservations
RIGHT JOIN Rooms ON Rooms.id=Reservations.room_id
WHERE has_tv !=0 AND has_internet !=0 AND has_kitchen !=0 AND has_air_con !=0
GROUP BY address, home_type;
Задача по sql: вывести самые популярные события в самый популярный год
Есть табличка с полями id — PK AI, event — название события, year — год, в котором событие произошло:
1) Нужно вывести самые популярные события в самый популярный год. Т.е. в данном случае самый популярный год 2000, т.к. в нём произошло сразу 3 события. А самое популярное событие в 2000 году — X.
Результат должен быть в виде: событие, кол-во, год.
2) Если самых популярных событий несколько, то нужно вывести все самые популярные события в самый популярный год. Например для:
sql-запрос должен вернуть 2 ряда:
3) Написать sql-запрос, который бы выводил самые популярные события в самые популярные годы. Т.е. может быть несколько самых популярных лет. Например:
Результат должен быть:
1) Мой вариант — первый запрос можно написать как (работает верно не на всех данных):
IvanPadoltsev/SQL-Academy
Задание 15: Во сколько Стив Мартин (Steve Martin) прилетел в Лондон (London) SELECT t.time_in FROM Trip AS t JOIN Pass_in_trip AS pit ON t.id = trip JOIN Passenger AS p ON p.id = passenger WHERE name = ‘Steve Martin’ AND town_to = ‘London’;
Задание 16: Вывести отсортированный по количеству перелетов (по убыванию) и имени (по возрастанию) список пассажиров, совершивших хотя бы 1 полет. SELECT p.name, COUNT(passenger) AS count FROM Trip AS t JOIN Pass_in_trip AS pit ON t.id = trip JOIN Passenger AS p ON p.id = passenger GROUP BY p.name HAVING count >= 1 ORDER BY count DESC, p.name ASC;
Задание 17: Определить, сколько потратил в 2005 году каждый из членов семьи SELECT member_name, status, SUM(unit_price * amount) as costs FROM Payments AS p JOIN FamilyMembers AS fm ON p.family_member = fm.member_id WHERE date LIKE ‘2005%’ GROUP BY family_member;
Задание 18: Узнать, кто старше всех в семьe SELECT member_name FROM FamilyMembers WHERE birthday = (SELECT MIN(birthday) FROM FamilyMembers);
Задание 19: Определить, кто из членов семьи покупал картошку (potato) SELECT status FROM FamilyMembers AS fm JOIN Payments AS p ON fm.member_id = p.family_member JOIN Goods AS g ON p.good = g.good_id WHERE good_name LIKE ‘potato’ GROUP BY status;
Задание 20: Сколько и кто из семьи потратил на развлечения (entertainment). Вывести статус в семье, имя, сумму SELECT status, member_name, SUM(unit_price*amount) AS costs FROM FamilyMembers AS fm JOIN Payments AS p ON fm.member_id = p.family_member JOIN Goods AS g ON p.good = g.good_id JOIN GoodTypes as gp ON g.type = gp.good_type_id WHERE good_type_name = ‘entertainment’ GROUP BY family_member;
Задание 21: Определить товары, которые покупали более 1 раза SELECT good_name FROM Payments AS p JOIN Goods as g ON p.good = g.good_id GROUP BY good HAVING COUNT(good_name) > 1;
Задание 22: Найти имена всех матерей (mother) SELECT member_name FROM FamilyMembers WHERE status = ‘mother’;
Задание 23: SELECT good_name, unit_price FROM Payments AS p JOIN Goods AS g ON p.good = g.good_id JOIN GoodTypes as gp ON g.type = gp.good_type_id WHERE good_type_name = ‘delicacies’ LIMIT 1;
Задание 24: Определить кто и сколько потратил в июне 2005 SELECT member_name, SUM(unit_price*amount) as costs FROM Payments as p JOIN FamilyMembers as fm ON p.family_member = fm.member_id WHERE date LIKE ‘2005-06%’ GROUP BY member_name;
Задание 25: Определить, какие товары имеются в таблице Goods, но не покупались в течение 2005 года SELECT good_name FROM Goods
LEFT JOIN Payments ON Goods.good_id = Payments.good AND YEAR(Payments.date) = 2005 WHERE Payments.good IS NULL GROUP BY good_id;
SELECT good_name, good_id, good, date FROM Goods as g LEFT OUTER JOIN Payments as p ON g.good_id = p.good WHERE date IS NULL OR date NOT LIKE ‘2005%’ ORDER BY good;
Задание 26: Определить группы товаров, которые не приобретались в 2005 году ГРУППЫ, ТОВАРЫ, КОГДА ПРИОБРЕТАЛИСЬ: SELECT good_type_name, good_name, good_id, good, payment_id, date FROM Goods JOIN Payments ON Goods.good_id = Payments.good JOIN GoodTypes ON GoodTypes.good_type_id = Goods.type;
РЕШЕНИЕ: SELECT good_type_name FROM GoodTypes WHERE good_type_id NOT IN (SELECT good_type_id FROM Goods JOIN Payments ON Goods.good_id = Payments.good AND YEAR(date) = 2005 JOIN GoodTypes ON GoodTypes.good_type_id = Goods.type);
Задание 27: Узнать, сколько потрачено на каждую из групп товаров в 2005 году. Вывести название группы и сумму SELECT good_type_name, SUM(amount*unit_price) AS costs FROM GoodTypes JOIN Goods ON good_type_id = type JOIN Payments ON good = good_id AND YEAR(date) = 2005 GROUP BY good_type_name;
Задание 28: Сколько рейсов совершили авиакомпании с Ростова (Rostov) в Москву (Moscow) ? SELECT COUNT(id) AS count FROM Trip WHERE town_from = ‘Rostov’ AND town_to = ‘Moscow’;
Задание 29: Выведите имена пассажиров улетевших в Москву (Moscow) на самолете TU-134 SELECT DISTINCT name FROM Passenger JOIN Pass_in_trip ON Passenger.id = Pass_in_trip.passenger JOIN Trip ON Pass_in_trip.trip = Trip.id WHERE plane = ‘TU-134’ AND town_to = ‘Moscow’;
Задание 30: Выведите нагруженность (число пассажиров) каждого рейса (trip). Результат вывести в отсортированном виде по убыванию нагруженности. SELECT trip, COUNT(passenger) AS count FROM Passenger JOIN Pass_in_trip ON Passenger.id = Pass_in_trip.passenger JOIN Trip ON Pass_in_trip.trip = Trip.id GROUP BY trip ORDER BY count DESC;
Задание 31: Вывести всех членов семьи с фамилией Quincey. SELECT * FROM FamilyMembers WHERE member_name LIKE ‘%Quincey’;
Задание 32: Вывести средний возраст людей (в годах), хранящихся в базе данных. Результат округлите до целого в меньшую сторону. SELECT FLOOR(AVG(FLOOR(DATEDIFF(NOW(), birthday)/365))) AS age FROM FamilyMembers;
Задание 33: Найдите среднюю стоимость икры. В базе данных хранятся данные о покупках красной (red caviar) и черной икры (black caviar). SELECT AVG(unit_price) AS cost FROM Payments JOIN Goods ON good=good_id WHERE good_name = ‘red caviar’ OR good_name = ‘black caviar’;
Задание 34: Сколько всего 10-ых классов? SELECT COUNT(name) AS count FROM Class WHERE name LIKE ‘10%’;
Задание 35: Сколько различных кабинетов школы использовались 2.09.2019 в образовательных целях ? SELECT DISTINCT COUNT(classroom) AS count FROM Schedule WHERE date LIKE ‘2019-09-02%’;
Задание 36: Выведите информацию об обучающихся живущих на улице Пушкина (ul. Pushkina)? SELECT * FROM Student WHERE address LIKE ‘%Pushkina%’;
Задание 37: Сколько лет самому молодому обучающемуся ? SELECT ROUND(MIN(DATEDIFF(NOW(), birthday)/365)) AS year FROM Student; SELECT FLOOR(MIN(DATEDIFF(NOW(), birthday)/365)) AS year FROM Student;
Задание 38: SELECT COUNT(1) As count FROM Student WHERE first_name LIKE ‘Anna’;
SELECT COUNT(class) AS count FROM Student_in_class JOIN Class ON Class.id=class WHERE name LIKE ’10 B’;
SELECT COUNT(class) AS count FROM Student_in_class JOIN Class ON Class.id=class AND name = ’10 B’;
Задание 40: Выведите название предметов, которые преподает Ромашкин П.П. (Romashkin P.P.) ? SELECT DISTINCT(Subject.name) AS subjects FROM Subject JOIN Schedule ON Subject.id=Schedule.subject JOIN Teacher ON Teacher.id=Schedule.teacher AND last_name=’Romashkin’;
Задание 41: Во сколько начинается 4-ый учебный предмет по расписанию ? SELECT start_pair FROM Timepair WHERE start_pair FROM Timepair LIMIT 3, 1; SELECT start_pair FROM Timepair LIMIT 1 OFFSET 3;
Задание 42: Сколько времени обучающийся будет находиться в школе, учась со 2-го по 4-ый уч. предмет ? SELECT DISTINCT TIMEDIFF((SELECT end_pair FROM Timepair WHERE (SELECT start_pair FROM Timepair WHERE as time FROM Timepair;
Задание 43: Выведите фамилии преподавателей, которые ведут физическую культуру (Physical Culture). Остортируйте преподавателей по фамилии. SELECT last_name FROM Teacher JOIN Schedule ON Teacher.id=Schedule.teacher JOIN Subject ON Subject.id=Schedule.subject WHERE Subject.name=’Physical Culture’ ORDER BY last_name ASC;
Задание 44: Найдите максимальный возраст (колич. лет) среди обучающихся 10 классов ? SELECT FLOOR(MAX((DATEDIFF(NOW(), birthday)/365))) AS max_year FROM Student JOIN Student_in_class ON Student.id=Student_in_class.student JOIN Class ON Class.id=Student_in_class.class WHERE Class.name LIKE ‘10%’;
SELECT classroom, COUNT(classroom) as count FROM Schedule GROUP BY classroom HAVING COUNT() > 4 ORDER BY COUNT() DESC; — какие кабинеты в топе?
Задание 46: В каких классах введет занятия преподаватель «Krauze» ? SELECT DISTINCT name FROM Class JOIN Schedule ON Class.id=Schedule.class JOIN Teacher ON Teacher.id=Schedule.teacher WHERE last_name = ‘Krauze’;
Задание 47: Сколько занятий провел Krauze 30 августа 2019 г.? SELECT COUNT(teacher) AS count FROM Schedule JOIN Teacher ON Teacher.id=Schedule.teacher AND last_name = ‘Krauze’ WHERE date LIKE ‘2019-08-30%’;
Задание 48: Выведите заполненность классов в порядке убывания SELECT name, COUNT(class) as count FROM Class JOIN Student_in_class ON Class.id=Student_in_class.class GROUP BY name ORDER BY COUNT(*) DESC;
Задание 49: Какой процент обучающихся учится в 10 A классе ? SELECT (COUNT(*)*100/(SELECT COUNT(Student.id) as count FROM Student JOIN Student_in_class ON Student.id=Student_in_class.student)) AS percent FROM Student_in_class JOIN Class ON Class.id=Student_in_class.class AND name = ’10 A’;
Задание 50: Какой процент обучающихся родился в 2000 году? Результат округлить до целого в меньшую сторону. SELECT FLOOR((COUNT(*)*100/(SELECT COUNT(Student.id) as count FROM Student JOIN Student_in_class ON Student.id=Student_in_class.student))) AS percent FROM Student WHERE YEAR(birthday) = 2000;
Задание 51: Добавьте товар с именем «Cheese» и типом «food» в список товаров (Goods). INSERT INTO Goods(good_id, good_name, type) VALUES (17, ‘Cheese’, 2);
Задание 52: Добавьте в список типов товаров (GoodTypes) новый тип «auto». INSERT INTO GoodTypes(good_type_id, good_type_name) VALUES (9, ‘auto’);
Задание 53: Измените имя «Andie Quincey» на новое «Andie Anthony». UPDATE FamilyMembers SET member_name=’Andie Anthony’ WHERE member_id=3;
Задание 54: Удалить всех членов семьи с фамилией «Quincey». DELETE FROM FamilyMembers WHERE member_name LIKE ‘%Quincey’;
Задание 55: Удалить компании, совершившие наименьшее количество рейсов. SELECT name, COUNT(company) as company FROM Trip JOIN Company ON Company.id=Trip.company GROUP BY name; DELETE FROM Company WHERE FROM Company WHERE FROM Company WHERE >
Задание 56: Удалить все перелеты, совершенные из Москвы (Moscow). DELETE FROM Trip WHERE town_from LIKE ‘%Moscow’;
Задание 57: Перенести расписание всех занятий на 30 мин. вперед. UPDATE Timepair SET start_pair = DATE_ADD(start_pair, INTERVAL 30 MINUTE); UPDATE Timepair SET end_pair = DATE_ADD(end_pair, INTERVAL 30 MINUTE);
Задание 58: Добавить отзыв с рейтингом 5 на жилье, находящиеся по адресу «11218, Friel Place, New York», от имени «George Clooney» SELECT Users.name, Reservations.* FROM Reservations JOIN Rooms ON Rooms.id=Reservations.room_id JOIN Users ON Users.id=Reservations.user_id WHERE address = ‘11218, Friel Place, New York’
INSERT INTO Reviews (id, reservation_id, rating) VALUES (23, 2, 5);
Задание 59: Вывести пользователей,указавших Белорусский номер телефона ? Телефонный код Белоруссии +375. SELECT * FROM Users WHERE phone_number LIKE ‘+375%’;
SELECT teacher FROM Schedule JOIN Teacher ON Teacher.id=Schedule.teacher JOIN Subject ON Subject.id=Schedule.subject JOIN Class ON Class.id=Schedule.class WHERE Class.name IN (’11 A’, ’11 B’) GROUP BY teacher HAVING COUNT(teacher)>=1 ORDER BY teacher;
Задание 61: Выведите список комнат, которые были зарезервированы в течение 12 недели 2020 года. SELECT Rooms.* FROM Rooms JOIN Reservations ON Rooms.id=Reservations.room_id AND YEAR(start_date)=2020 AND YEAR(end_date)=2020 WHERE WEEK(start_date, 1)=12 OR WEEK(end_date, 1)=12;
Задание 62: Вывести в порядке убывания популярности доменные имена 2-го уровня, используемые пользователями для электронной почты. Полученный результат необходимо дополнительно отсортировать по возрастанию названий доменных имён. SELECT SUBSTRING_INDEX(email, ‘@’, -1) as domain, count(*) AS count FROM Users GROUP BY domain ORDER BY count DESC, domain ASC;
Задание 63: Выведите отсортированный список (по возрастанию) имен студентов в виде Фамилия.И.О. SELECT CONCAT(last_name, ‘.’, LEFT(first_name, 1), ‘.’, LEFT(middle_name, 1), ‘.’) AS name FROM Student ORDER BY first_name ASC;
27 распространённых вопросов по SQL с собеседований и ответы на них
Вопросы по SQL на собеседованиях — обычное дело, и чтобы не завалиться, нужно хорошо понимать, как работать с базами данных. В этом списке представлены основные вопросы и задачи по SQL, которые часто встречаются на собеседованиях, а также ответы на них.
Теория
Что такое СУБД?
Допустим, есть большая база данных, которой пользуются многие сотрудники: кто-то ищет информацию, а кто-то изменяет или даже удаляет её. Чтобы правильно обрабатывать все эти запросы, нужно специальное программное обеспечение, и именно такое ПО получило название системы управления базами данных (СУБД).
Какие типы СУБД в соответствии с моделями данных вы знаете?
Этот вопрос по SQL предполагает не просто назвать, но и дать краткое описание каждому типу.
- Реляционные, которые поддерживают установку связей между таблицами с помощью первичных и внешних ключей. Пример — MySQL.
- Flat File — базы данных с двумерными файлами, в которых содержатся записи одного типа и отсутствует связь с другими файлами, как в реляционных. Пример — Excel.
- Иерархические подразумевают наличие записей, связанных друг с другом по принципу отношений один-к-одному или один-ко-многим. А вот для отношений многие-ко-многим следует использовать реляционную модель. Пример — Adabas.
- Сетевые похожи на иерархические, но в этом случае «ребёнок» может иметь несколько «родителей» и наоборот. Примеры — IDS и IDMS.
- Объектно-ориентированные СУБД работают с базами данных, которые состоят из объектов, используемых в ООП. Объекты группируются в классы и называются экземплярами, а классы в свою очередь взаимодействуют через методы. Пример — Versant.
- Объектно-реляционные обладают преимуществами реляционной и объектно-ориентированной моделей. Пример — IBM Db2.
- Многомерная модель является разновидностью реляционной и использует многомерные структуры. Часто представляется в виде кубов данных. Пример — Oracle Essbase.
- Гибридные состоят из двух и более типов баз данных. Используются в том случае, если одного типа недостаточно для обработки всех запросов. Пример — Altibase HDВ.
Что такое первичный ключ?
Первичный ключ или PRIMARY KEY предназначен для однозначной идентификации каждой записи в таблице и является строго уникальным ( UNIQUE ): две записи таблицы не могут иметь одинаковые значения первичного ключа. Нулевые значения ( NULL ) в PRIMARY KEY не допускаются. Если в качестве PRIMARY KEY используется несколько полей, их называют составным ключом.
Здесь в качестве первичного ключа используется поле id.
Когда используется PRIMARY KEY?
PRIMARY KEY — это первичный ключ, который используется в качестве основного ключа и может быть использован для связи с дочерней таблицей, содержащей внешний ключ.
А что такое внешний ключ?
Внешний ключ или FOREIGN KEY также является атрибутом ограничения и обеспечивает связь двух таблиц. По сути, это поле или несколько полей, которые ссылаются на PRIMARY KEY в родительской таблице.
В данном случае внешний ключ, привязанный к полю user_id в таблице order , ссылается на первичный ключ id в таблице users , и именно по этим полям происходит связывание двух таблиц.
Какие ещё ограничения вы знаете, как они работают и указываются?
SQL-ограничения (constraints) указываются при создании или изменении таблицы. Это правила для ограничения типа данных, которые могут храниться в таблице. Действие с данными не будет выполнено, если нарушаются установленные ограничения.
- UNIQUE — гарантирует уникальность значений в столбце;
- NOT NULL — значение не может быть NULL ;
- INDEX — создаёт индексы в таблице для быстрого поиска/запросов;
- CHECK — значения столбца должны соответствовать заданным условиям;
- DEFAULT — предоставляет столбцу значения по умолчанию.
Для чего используется ключевое слово ORDER BY?
Для сортировки данных в порядке возрастания ( ASC ) или убывания ( DESC ).
Выбираются пользователи, которые будут отсортированы по имени в порядке убывания. Дополните ответ на этот вопрос по SQL тем, что без указания DESC данные были бы отсортированы по умолчанию — в порядке возрастания:
Назовите четыре основных типа соединения в SQL
Чтобы объединить две таблицы в одну, следует использовать оператор JOIN . Соединение таблиц может быть внутренним ( INNER ) или внешним ( OUTER ), причём внешнее соединение может быть левым ( LEFT ), правым ( RIGHT ) или полным ( FULL ).
- INNER JOIN — получение записей с одинаковыми значениями в обеих таблицах, т.е. получение пересечения таблиц.
- FULL OUTER JOIN — объединяет записи из обеих таблиц (если условие объединения равно true) и дополняет их всеми записями из обеих таблиц, которые не имеют совпадений. Для записей, которые не имеют совпадений из другой таблицы, недостающее поле будет иметь значение NULL .
- LEFT JOIN — возвращает все записи, удовлетворяющие условию объединения, плюс все оставшиеся записи из внешней (левой) таблицы, которые не удовлетворяют условию объединения.
- RIGHT JOIN — работает точно так же, как и левое объединение, только в качестве внешней таблицы будет использоваться правая.
Рассмотрим пример соединения SQL таблиц с использованием INNER JOIN . Следующий запрос выбирает все заказы с информацией о клиенте:
А что такое Self JOIN?
Такой вопрос тоже может прозвучать на собеседовании по SQL. Это выражение используется для того, чтобы таблица объединилась сама с собой, словно это две разные таблицы. Чтобы такое реализовать, одна из таких «таблиц» временно переименовывается.
Например, следующий SQL-запрос объединяет клиентов из одного города:
Для чего нужен оператор UNION?
Он используется для объединения полученных данных из двух или более запросов, которые должны иметь одинаковое количество столбцов с одинаковыми типами данных и расположенных в том же порядке.
Как работают подстановочные знаки?
Это специальные символы, которые нужны для замены каких-либо знаков в запросе. Они используются вместе с оператором LIKE , с помощью которого можно отфильтровать запрашиваемые данные.
Какими бывают подстановочные знаки?
- % — заменить ноль или более символов;
- _ — заменить один символ.
Данный запрос позволяет найти данные всех пользователей, имена которых содержат в себе «test».
А в этом случае имена искомых пользователей начинаются на «t», после содержат какой-либо символ и «est» в конце.
Что делают псевдонимы Aliases?
SQL-псевдонимы нужны для того, чтобы дать временное имя таблице или столбцу. Это нужно, когда в запросе есть таблицы или столбцы с неоднозначными именами. В этом случае для удобства в составлении запроса используются псевдонимы. SQL-псевдоним существует только на время запроса.
Для чего нужен оператор INSERT INTO SELECT?
Данный оператор копирует данные из одной таблицы и вставляет их в другую, при этом типы данных в обеих таблицах должны соответствовать.
Что такое нормализация и денормализация?
Нормализация отношений в SQL призвана организовать информацию в базе данных таким образом, чтобы она не занимала много места и с ней было удобно работать. Это удаление избыточных данных, устранение дублей, идентификация наборов связанных данных через PRIMARY KEY , etc.
Соответственно, денормализация является обратным процессом, который вносит в нормализованную таблицу избыточные данные.
Подробнее о пяти нормальных формах и форме Бойса-Кодда можно узнать из данного видеокурса:
Объясните разницу между командами DELETE и TRUNCATE
Команда DELETE — это DML-операция, которая удаляет записи из таблицы, соответствующие заданному условию:
При этом создаются логи удаления, то есть операцию можно отменить.
А вот команда TRUNCATE — это DDL-операция, которая полностью пересоздаёт таблицу, и отменить такое удаление невозможно:
Чем VARCHAR отличается от NVARCHAR?
Главное отличие в том, что VARCHAR хранит значения в формате ASCII, где символ занимает один байт, а NVARCHAR хранит значения в формате Unicode, где символ «весит» 2 байта. Тип VARCHAR следует использовать, если вы уверены, что в значениях не будет Unicode-символов. Например, VARCHAR можно применить к адресам электронной почты, состоящих из ASCII-символов.
Практика
Как выбрать записи с нечётными Id?
Один из самых распространённых вопросов на собеседовании. SQL запрос для выбора записей с нечётными id должен выглядеть следующим образом:
Если остаток от деления id на 2 равен нулю, перед нами чётное значение, и наоборот.
Как найти дубли в поле email?
Функция COUNT() возвращает количество строк из поля email . Оператор HAVING работает почти так же, как и WHERE , вот только применяется не для всех столбцов, а для набора, созданного оператором GROUP BY .
При выборке из таблицы прибавьте к дате 1 день
Функция DATE_ADD() прибавляет к дате заданный промежуток времени. Синтаксис выглядит следующим образом:
Выберите только уникальные имена
SELECT DISTINCT возвращает разные значения, даже если в выбранном столбце есть дубли.
Найдите в таблице среднюю зарплату работников
Функция AVG() применяется только к числовым типам данных и возвращает среднее значение по столбцу.
А теперь получите список сотрудников с зарплатой выше средней
Даны таблицы workers и departments. Найдите все департаменты без единого сотрудника
Замените в таблице зарплату работника на 1000, если она равна 900, и на 1500 в остальных случаях
Замена значений — одна из наиболее часто встречаемых задач по SQL на собеседованиях. Решить её несложно:
Оператор UPDATE используется для изменения существующих записей. Но ответы на подобные вопросы с собеседований по SQL должны быть более развёрнутыми. Уточните,что после UPDATE следует указать, какие записи должны быть обновлены. В противном случае обновятся все записи в таблице.
В нашем примере условие задаётся через оператор CASE: если текущая зарплата равна 900, изменяем её на 1000, в остальных случаях — на 1500.
При выборке из таблицы пользователей создайте поле, которое будет включать в себя и имена, и зарплату
Функция CONCAT() используется для конкатенации (объединения) строк, неявно преобразуя при этом любые типы данных в строки.
Переименуйте таблицу
С помощью оператора ALTER TABLE можно добавлять, удалять, изменять столбцы, а также изменять название таблицы.
Перед решением задач изучите теорию к данному уроку.
Примеры решения задач
Задача
Задача. Выберите из таблицы workers записи с id равным 3, 5, 6, 10.
Решение:
SELECT * FROM workers WHERE id IN(3,5,6,10)Задача
Задача. Выберите из таблицы workers записи с id равным 3, 5, 6, 10
и логином, равным ‘eee’, ‘zzz’ или ‘ggg’.
Решение:
SELECT * FROM workers WHERE id IN(3,5,6,10) AND login IN("eee", "zzz", "ggg")Задача
Задача. Выберите из таблицы workers записи c зарплатой от 500 до 1500.
Решение:
SELECT * FROM workers WHERE price BETWEEN 500 AND 1500Задача
Задача. Выберите из таблицы workers все записи так, чтобы вместо id было workersId,
вместо login – workersLogin, вместо salary — workersSalary.
Решение:
SELECT id AS userId, login AS userLogin, salary AS userSalary FROM workersЗадача
Задача. Найдите в таблице workers минимальный возраст.
Решение:
SELECT MIN(age) FROM workersЗадача
Задача. Найдите в таблице workers суммарный возраст.
Решение:
SELECT SUM(age) FROM workersЗадача
Задача. Вставьте в таблицу workers запись с полем date с
текущим моментом времени в формате ‘год-месяц-день часы:минуты:секунды’.
Решение:
INSERT INTO workers ('name', 'date') VALUES ('Вася', NOW())Задача
Задача. Вставьте в таблицу workers запись с полем date
с текущей датой в формате ‘год-месяц-день’.
Решение:
INSERT INTO workers ('name', 'date') VALUES ( 'Маша', CURDATE())Задача
Задача. При выборке из таблицы workers запишите день, месяц и год в отдельные поля.
Решение:
SELECT EXTRACT(DAY FROM date) AS day,
EXTRACT(MONTH FROM date) AS month,
EXTRACT(YEAR FROM date) AS year
FROM workersЗадача
Задача. Выберите из таблицы workers
записи, в которых минуты больше секунд.
Решение:
SELECT * FROM workers WHERE HOUR(date) > SECOND(date)Задача
Задача. При выборке из таблицы workers прибавьте к дате 1 год.
Решение:
SELECT DATE_ADD(date, INTERVAL 1 YEAR) as date FROM workersИли:
SELECT date + INTERVAL 1 YEAR as date FROM workersЗадача
Задача. При выборке из таблицы workers отнимите от даты 1 год.
Решение:
SELECT DATE_ADD(date, INTERVAL - 1 YEAR) as date FROM workersИли:
SELECT date - INTERVAL 1 YEAR as date FROM workersЗадача
Задача. При выборке из таблицы workers прибавьте к дате 3 года, 4 месяца.
Решение:
SELECT DATE_ADD(date, INTERVAL "3:4" YEAR_MONTH) as date FROM workersИли:
SELECT date + INTERVAL 3 YEAR + INTERVAL 4 MONTH as date FROM workersЗадача
Задача. При выборке из таблицы workers прибавьте к дате 4 дня, 3 часа, 2 минуты, 1 секунду.
Решение:
SELECT DATE_SUB(date, INTERVAL "4 3:2:1" DAY_SECOND) FROM workersЗадача
Задача. При выборке из таблицы workers прибавьте к дате 3 дня и отнимите 2 часа.
Решение:
SELECT date + INTERVAL 3 DAY - INTERVAL 2 HOUR FROM workersЗадачи для решения
Во всех задачах ниже
считайте, что таблица workers имеет поля id, login, salary, age, date, description
(и другие при необходимости).
На IN
Для решения задач данного блока вам понадобятся следующие SQL команды и функции:
IN.
Выберите из таблицы workers записи с id равным 1, 2, 3, 5, 14.
Решение:
SELECT * FROM workers WHERE id IN(1,2,3,5,14)
Выберите из таблицы workers записи с login равным ‘eee’, ‘bbb’, ‘zzz’.
Решение:
SELECT * FROM workers WHERE login IN("eee", "bbb", "zzz")
Выберите из таблицы workers записи с id равным 1, 2, 3, 7, 9,
и логином, равным ‘user’, ‘admin’, ‘ivan’ и зарплатой больше 300.
Решение:
SELECT * FROM workers WHERE id IN(1,2,3,7,9) AND login IN("user", "admin", "ivan") AND price>300На BETWEEN
Для решения задач данного блока вам понадобятся следующие SQL команды и функции:
BETWEEN.
Выберите из таблицы workers записи c зарплатой от 100 до 1000.
Решение:
SELECT * FROM workers WHERE salary BETWEEN 100 AND 1500
Выберите из таблицы workers записи c id от 3 до 10 и зарплатой от 300 до 500.
Решение:
SELECT * FROM workers WHERE id BETWEEN 3 AND 10 AND salary BETWEEN 100 AND 500На AS
Для решения задач данного блока вам понадобятся следующие SQL команды и функции:
AS.
Выберите из таблицы workers все записи так, чтобы вместо id было userId,
вместо login – userLogin, вместо salary — userSalary.
Решение:
SELECT id AS userId, login AS userLogin, salary AS userSalary FROM workersНа DISTINCT
Для решения задач данного блока вам понадобятся следующие SQL команды и функции:
DISTINCT.
Выберите из таблицы workers все записи так,
чтобы туда попали только записи с разной зарплатой (без дублей).
Решение:
SELECT DISTINCT salary FROM workers
Получите SQL запросом все возрасты без дублирования.
Решение:
SELECT DISTINCT age FROM workersНа MIN и MAX
Для решения задач данного блока вам понадобятся следующие SQL команды и функции:
MIN,
MAX.
Найдите в таблице workers минимальную зарплату.
Решение:
SELECT MIN(salary) FROM workers
Найдите в таблице workers максимальную зарплату.
Решение:
SELECT MAX(salary) FROM workersНа SUM
Для решения задач данного блока вам понадобятся следующие SQL команды и функции:
SUM.
Найдите в таблице workers суммарную зарплату.
Решение:
SELECT SUM(salary) FROM workers
Найдите в таблице workers суммарную зарплату для людей в возрасте от 21 до 25.
Решение:
SELECT SUM(salary) FROM workers WHERE age BETWEEN 21 AND 25
Найдите в таблице workers суммарную зарплату для id, равного 1, 2, 3 и 5.
Решение:
SELECT SUM(salary) FROM workers WHERE id IN(1,2,3,5)На AVG
Для решения задач данного блока вам понадобятся следующие SQL команды и функции:
AVG.
Найдите в таблице workers среднюю зарплату.
Решение:
SELECT AVG(salary) FROM workers
Найдите в таблице workers средний возраст.
Решение:
SELECT AVG(age) FROM workers На NOW, CURRENT_DATE, CURRENT_TIME
Для решения задач данного блока вам понадобятся следующие SQL команды и функции:
NOW,
CURRENT_DATE,
CURRENT_TIME.
Выберите из таблицы workers все записи, у которых дата больше текущей.
Решение:
SELECT * FROM workers WHERE date>CURRENT_DATE()
Вставьте в таблицу workers запись с полем date с
текущим моментом времени в формате ‘год-месяц-день часы:минуты:секунды’.
Решение:
INSERT INTO workers ('name', 'date') VALUES ('Вася', NOW())
Вставьте в таблицу workers запись с полем date
с текущей датой в формате ‘год-месяц-день’.
Решение:
INSERT INTO workers ('name', 'date') VALUES ('Петя', CURDATE())
Вставьте в таблицу workers запись с полем
time с текущим моментом времени в формате ‘часы:минуты:секунды’.
Решение:
INSERT INTO workers ('name', 'time') VALUES ('Коля', CURTIME())На работу с частью даты
Выберите из таблицы workers все записи за 2016 год.
Решение:
SELECT * FROM workers WHERE YEAR(date) = 2016
Выберите из таблицы workers все записи за март любого года.
Решение:
SELECT * FROM workers WHERE MONTH(date) = 3
Выберите из таблицы workers все записи за третий день месяца.
Решение:
SELECT * FROM workers WHERE DAY(date) = 3
Выберите из таблицы workers все записи за пятый день апреля любого года.
Решение:
SELECT * FROM workers WHERE MONTH(date) = 4 AND DAY(date) = 5
Выберите из таблицы workers все записи за
следующие дни любого месяца: 1, 7, 11, 12, 15, 19, 21, 29.
Решение:
SELECT * FROM workers WHERE DAY(date) IN(1,7,11,12,15,19,21,29)
Выберите из таблицы workers все записи за вторник.
Решение:
SELECT * FROM workers WHERE DAYOFWEEK(date)= 3
Выберите из таблицы workers все записи
за первую декаду любого месяца 2016 года.
Решение:
SELECT * FROM workers WHERE DAY(date) <= 10 AND YEAR(date)= 2016
Выберите из таблицы workers все записи, в которых день меньше месяца.
Решение:
SELECT * FROM workers WHERE DAY(date) < MONTH(date)
При выборке из таблицы workers запишите день, месяц и год в отдельные поля.
Решение:
SELECT DAY(date) AS day, MONTH(date) AS month, YEAR(date) AS year FROM workers
При выборке из таблицы workers создайте новое поле today,
в котором будет номер текущего дня недели.
Решение:
SELECT WEEKDAY(NOW()) as today FROM workers;На EXTRACT, DATE
Для решения задач данного блока вам понадобятся следующие SQL команды и функции:
EXTRACT,
DATE.
При выборке из таблицы workers запишите год, месяц и день в
отдельные поля с помощью EXTRACT.
Решение:
SELECT EXTRACT(YEAR FROM date) AS year,
EXTRACT(MONTH FROM date) AS month,
EXTRACT(DAY FROM date) AS day
FROM workers
При выборке из таблицы workers запишите день, месяц и год в
отдельное поле с помощью DATE в формате ‘год-месяц-день’.
Решение:
SELECT DATE(date) FROM workersНа DATE_FORMAT
Для решения задач данного блока вам понадобятся следующие SQL команды и функции:
DATE_FORMAT.
При выборке из таблицы workers выведите дату в формате ‘31.12.2025’.
Решение:
SELECT DATE_FORMAT(date, '%d.%m.%Y') FROM workers
При выборке из таблицы workers выведите дату в формате ‘2025% 31.12’.
Решение:
SELECT DATE_FORMAT(date, '%Y%% %m.%d') FROM workersНа INTERVAL, DATE_ADD, DATE_SUB
Для решения задач данного блока вам понадобятся следующие SQL команды и функции:
INTERVAL,
DATE_ADD,
DATE_SUB.
При выборке из таблицы workers прибавьте к дате 1 день.
Решение:
SELECT DATE_ADD(date, INTERVAL 1 DAY) FROM workers
При выборке из таблицы workers отнимите от даты 1 день.
Решение:
SELECT DATE_ADD(date, INTERVAL -1 DAY) FROM workers
При выборке из таблицы workers прибавьте к дате 1 день, 2 часа.
Решение:
SELECT DATE_ADD(date, INTERVAL "1:2" DAY_HOUR) FROM workers
При выборке из таблицы workers прибавьте к дате 1 год, 2 месяца.
Решение:
SELECT DATE_ADD(date, INTERVAL "1:2" YEAR_MONTH) FROM workers
При выборке из таблицы workers прибавьте к дате 1 день, 2 часа, 3 минуты.
Решение:
SELECT DATE_SUB(date, INTERVAL "1:2:3" DAY_SECOND) FROM workers
При выборке из таблицы workers прибавьте к дате 1 день, 2 часа, 3 минуты, 5 секунд.
Решение:
SELECT DATE_SUB(date, INTERVAL "1 2:3:5" DAY_SECOND) FROM workers
При выборке из таблицы workers прибавьте к дате 2 часа, 3 минуты, 5 секунд.
Решение:
SELECT DATE_SUB(date, INTERVAL "2:3:5" HOUR_SECOND) FROM workers WHERE
При выборке из таблицы workers прибавьте к дате 1 день и отнимите 2 часа.
Решение:
SELECT DATE_ADD(date, INTERVAL 1:-2 DAY_HOUR) FROM workers
При выборке из таблицы workers прибавьте к дате 1 день и отнимите 2 часа, 3 минуты.
Решение:
SELECT DATE_ADD(date, INTERVAL 1 -2 -3 DAY_MINUTE) FROM workers На математические операции
При выборке из таблицы workers создайте новое поле res, в котором будет число 3.
Решение:
SELECT 3 AS res FROM workers
При выборке из таблицы workers создайте новое поле res, в котором будет строка ‘eee’.
Решение:
SELECT "eee" AS res FROM workers
При выборке из таблицы workers создайте новое поле 3, в котором будет число 3.
При выборке из таблицы workers создайте новое поле res, в котором будет лежать сумма зарплаты и возраста.
Решение:
SELECT SUM(age) AND SUM(salary) AS res FROM workers
При выборке из таблицы workers создайте новое поле res, в котором будет лежать разность зарплаты и возраста.
Решение:
SELECT (salary - age) AS res FROM workers
При выборке из таблицы workers создайте новое поле res, в котором будет лежать произведение зарплаты и возраста.
Решение:
SELECT (salary * age) AS res FROM workers
При выборке из таблицы workers создайте новое поле res, в котором будет лежать среднее арифметическое зарплаты и возраста.
Решение:
SELECT ((salary * age)/2) AS res FROM workers
Выберите из таблицы workers все записи, в которых сумма дня и месяца меньше 10-ти.
Решение:
SELECT (MONTH(date) + DAY(date)) FROM workers На LEFT, RIGHT, SUBSTRING
Для решения задач данного блока вам понадобятся следующие SQL команды и функции:
LEFT,
RIGHT,
SUBSTRING.
При выборке из таблицы workers получите первые 5 символов поля description.
Решение:
SELECT LEFT(description, 5) FROM workers
При выборке из таблицы workers получите последние 5 символов поля description.
Решение:
SELECT RIGHT(description, 5) FROM workers
При выборке из таблицы workers получите из поля description символы со второго по десятый.
Решение:
SELECT SUBSTRING(description, 2,10) FROM workersНа UNION
Для решения задач данного блока вам понадобятся следующие SQL команды и функции:
UNION.
Даны две таблицы: таблица category и таблица sub_category с полями id и name. Достаньте одним запросом названия категорий и подкатегорий.
Решение:
SELECT id, name FROM workers UNION SELECT id, name FROM sub_category На CONCAT, CONCAT_WS
Для решения задач данного блока вам понадобятся следующие SQL команды и функции:
CONCAT,
CONCAT_WS.
При выборке из таблицы workers создайте новое поле res, в котором будут лежать одновременно зарплата и возраст (слитно).
Решение:
SELECT CONCAT(salary, age) AS res FROM workers
При выборке из таблицы workers создайте новое поле res, в котором будут лежать одновременно зарплата и возраст (слитно), а после возраста будут идти три знака ‘!’.
Решение:
SELECT CONCAT(salary, age, '!!!') AS res FROM workers
При выборке из таблицы workers создайте новое поле res, в котором будут лежать одновременно зарплата и возраст через дефис.
Решение:
SELECT CONCAT_WS('-', salary, age) AS res FROM workers
При выборке из таблицы workers получите первые 5 символов логина и добавьте троеточие.
Решение:
SELECT CONCAT (LEFT(login, 5), '...') AS log FROM workers;На GROUP BY
Для решения задач данного блока вам понадобятся следующие SQL команды и функции:
GROUP BY.
Найдите самые маленькие зарплаты по группам возрастов (для каждого возраста свою минимальную зарплату).
Решение:
SELECT MIN(salary), MAX(salary) FROM workers GROUP BY age
Найдите самый большой возраст по группам зарплат (для каждой зарплаты свой максимальный возраст).
Решение:
SELECT MAX(age) FROM workers GROUP BY salary ;На GROUP_CONCAT
Для решения задач данного блока вам понадобятся следующие SQL команды и функции:
GROUP_CONCAT.
Выберите из таблицы workers уникальные возраста так, чтобы для каждого возраста было поле res, в котором будут лежать через дефис id записей с таким возрастом.
Решение:
SELECT DISTINCT age AS res (SELECT GROUP_CONCAT( id SEPARATOR '-') ) FROM workersНа подзапросы
Выберите из таблицы workers все записи, зарплата в которых больше средней зарплаты.
Решение:
SELECT * FROM workers WHERE salary >(SELECT AVG(salary) FROM workers)
Выберите из таблицы workers все записи, возраст в которых меньше среднего возраста, деленного на 2 и умноженного на 3.
Решение:
SELECT * FROM workers WHERE age < (SELECT AVG(age)/2*3 FROM workers)
Выберите из таблицы workers записи с минимальной зарплатой.
Решение:
SELECT * FROM workers WHERE salary = (SELECT MIN(salary) FROM workers)
Выберите из таблицы workers записи с максимальной зарплатой.
Решение:
SELECT * FROM workers WHERE salary = (SELECT MAX(salary) FROM workers)
При выборке из таблицы workers создайте новое поле max, в котором будет лежать максимальное значение зарплаты для возраста 25 лет.
Решение:
SELECT MAX(salary) AS max FROM workers WHERE age = 25 !!!!!!
При выборке из таблицы workers создайте новое поле avg, в котором будет лежать деленная на 2 разница между
максимальным значением возраста и минимальным значением возраста в во всей таблице.
Решение:
SELECT (SELECT (MAX(age) - MIN(age))/2 FROM workers) AS avg
При выборке из таблицы workers создайте новое поле avg, в котором будет лежать деленная
на 2 разница между максимальным значением зарплаты и минимальным значением зарплаты для возраста 25 лет.
Решение:
SELECT (SELECT (MAX(age) - MIN(age))/2 FROM workers ) AS avg FROM workers WHERE age = 25На JOIN
Для решения задач данного блока вам понадобятся следующие SQL команды и функции:
JOIN.
Даны две таблицы: таблица category с полями id и name и таблица page с полями id, name и category_id. Достаньте одним запросом все страницы вместе с их категориями.
Решение:
SELECT * FROM page LEFT JOIN category ON page.catogory_id = category.id
Даны 3 таблицы: таблица category с полями id и name, таблица sub_category с полями id и name и таблица page с полями id, name и sub_category_id. Достаньте одним запросом все страницы вместе с их подкатегориями и категориями.
Решение:
SELECT * FROM page LEFT JOIN category ON page.catogory_id = category.id
LEFT JOIN sub_category ON category.id = sub_category.category_id На работу с полями
Задачи данного блока следует решать SQL запросами,
а не через PhpMyAdmin.
Создайте базы данных test1 и test2.
Решение:
CREATE DATABASE test1
CREATE DATABASE test2
Удалите базу данных test2.
Создайте в базе данных test1 таблицы table1 и table2 с полями id, login, salary, age, date.
Решение:
CREATE TABLE table1(
id INT(1),
login VARCHAR(255),
salary INT(6),
age INT(6),
date DATE NOT NULL
primary key (Id)
);
CREATE TABLE table2(
id INT(1),
login VARCHAR(255),
salary INT(6),
age INT(6),
date DATE NOT NULL
primary key (Id)
);
Переименуйте таблицу table2 в table3.
Решение:
RENAME TABLE table2 TO table3
Добавьте в таблицу table1 поле status.
Решение:
ALTER TABLE table1 ALTER COLUMN status
Удалите из таблицы table1 поле age.
Решение:
ALTER TABLE table1 DROP COLUMN age
Переименуйте поле login на user_login.
Решение:
RENAME COLUMN login TO user_login
Смените типа поля salary с int на varchar(255).
Решение:
ALTER TABLE table1 CHANGE salary salary VARCHAR(255)
Очистите все таблицы базы данных test1.