Сколько обучающихся в 10 b классе

Вывести средний возраст людей (в годах), хранящихся в базе данных. Результат округлите до целого в меньшую сторону

Round – нельзя использовать, т.к. округление возрастов не работает с ROUND.

SELECT ROUND(
AVG(
DATE_FORMAT(FROM_DAYS(DATEDIFF(NOW(), birthday)), ‘%Y’) + 0
),
0
) as age
FROM FamilyMembers;

Задание 32
Найдите среднюю стоимость икры. В базе данных хранятся данные о покупках красной (red caviar) и черной икры (black caviar).

SELECT AVG(unit_price) as cost

FROM Payments a
JOIN Goods b ON a.good = b.good_id
WHERE good_name LIKE ‘% caviar’

Быстрее сработает этот запрос, потому что из подзапроса меньше выбор. В первом варианте он будет брать каждую строку и сравнивать.

SELECT AVG(UNIT_PRICE) AS COST
FROM PAYMENTS
WHERE GOOD IN (
SELECT GOOD_id
FROM GOODS
WHERE GOOD_NAME LIKE ‘%_CAVIAR’
);

Сколько различных кабинетов школы использовались 2.09.2019 в образовательных целях ?
SELECT COUNT(DISTINCT(classroom)) AS count
FROM Schedule
WHERE date = ‘2019-09-02T00:00:00.000Z’;
SELECT DISTINCT COUNT(CLASSROOM) AS COUNT
FROM Schedule
WHERE DATE = ‘2019-09-02’;

Выведите информацию об обучающихся живущих на улице Пушкина (ul. Pushkina)?
SELECT *
FROM student
WHERE address LIKE ‘ul. Pushkina%’;

Сколько лет самому молодому обучающемуся ?
SELECT MIN(
DATE_FORMAT(FROM_DAYS(DATEDIFF(NOW(), birthday)), ‘%Y’) + 0
) AS year
FROM Student;

SELECT TIMESTAMPDIFF(YEAR, BIRTHDAY, CURRENT_dATE) AS YEAR

FROM student
ORDER BY YEAR
LIMIT 1;

Задание 38
Сколько Анн (Anna) учится в школе ?

SELECT COUNT(first_name) as count

FROM Student
WHERE first_name = ‘Anna’;

Задание 40
Выведите название предметов, которые преподает Ромашкин П.П. (Romashkin P.P.) ?
SELECT name as subjects
FROM Subject a
JOIN Schedule b ON a.id = b.subject
JOIN Teacher c ON b.teacher = c.id
WHERE last_name = ‘Romashkin’
AND first_name LIKE ‘P%’
AND middle_name LIKE ‘P%’;

Задание 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). Отcортируйте преподавателей по фамилии.
SELECT last_name
FROM Teacher a
JOIN Schedule b ON a.id = b.teacher
JOIN Subject c ON b.subject = c.id
WHERE c.name = ‘Physical Culture’
ORDER BY last_name;

Задание 44
Найдите максимальный возраст (колич. лет) среди обучающихся 10 классов ?
Используйте конструкцию «as max_year» для указания максимального возраста в годах. Это необходимо для корректной проверки.

SELECT MAX(TIMESTAMPDIFF (YEAR, birthday, CURRENT_DATE)) as max_year
FROM Student
JOIN Student_in_class ON Student.id = Student_in_class.student
JOIN Class ON Student_in_class.class = Class.id
WHERE Class.name LIKE «10%»;

Какой(ие) кабинет(ы) пользуются самым большим спросом?
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
)

Задание 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’);
Задание 57
Перенести расписание всех занятий на 30 мин. вперед.

UPDATE Timepair
SET DATEADD(MINUTE, 30, MINUTE(start_pair))

Задание 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»);

SELECT MAX(good_id) + 1 INTO @id_add_1

SELECT good_type_id INTO @good_type_id_food

FROM GoodTypes
WHERE good_type_name = «food»;

#SELECT @good_type_id_food
INSERT INTO Goods(good_id, good_name, type)
VALUES (
@id_add_1,
— good_id
‘Cheese’,
— good_name
@good_type_id_food — type
);
SELECT *
FROM Goods;

Add to the list of product types (GoodTypes) a new type of «auto».

INSERT INTO GoodTypes(good_type_id, good_type_name)

SELECT MAX(good_type_id) + 1,
‘auto’
FROM GoodTypes;

Remove all family members whose last name is «Quincey».

DELETE FROM FamilyMembers

WHERE member_name LIKE ‘%Quincey’
Задание 55
Delete the companies that made the least number of flights.

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)
— find minimum count
SELECT MIN(count) INTO @min
FROM (
SELECT COUNT(*) count
FROM Trip
GROUP BY company
) a;
— show records with minimum count
SELECT DISTINCT(a.company) AS c
FROM Trip a
JOIN (
SELECT company
FROM Trip
GROUP BY company
HAVING COUNT(*) = @min
) b ON a.company = b.company;
— delete all companies from companies
DELETE FROM Company
WHERE id in (2, 3, 4)

SQL ACADEMY ответы и решения заданий (часть 3, задания 45-66)

SQL Academy (ответы и решения заданий 45-66)

ВКонтакте WhatsApp Pinterest Facebook Email

Задание 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 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 (p11);

Задание 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;

SQL ACADEMY ответы и решения заданий (часть 2, задания 23-44)

SQL Academy (ответы и решения заданий 23-44)

ВКонтакте WhatsApp Pinterest Facebook Email

Задание 23. Найдите самый дорогой деликатес (delicacies) и выведите его стоимость.

SELECT g.good_name, p.unit_price FROM Goods AS g JOIN Payments AS p ON (g.good_id=p.good) JOIN GoodTypes AS gt ON (g.type=gt.good_type_id) WHERE p.unit_price = (SELECT MAX(p.unit_price) FROM Payments AS p JOIN Goods AS g ON (g.good_id=p.good) JOIN GoodTypes AS gt ON (g.type=gt.good_type_id) WHERE gt.good_type_name='delicacies');

Задание 24. Определить кто и сколько потратил в июне 2005.

SELECT member_name, SUM(amount*unit_price) AS costs FROM FamilyMembers AS fm JOIN Payments AS p ON fm.member_id=p.family_member WHERE MONTH(date) = 06 AND YEAR(date) = 2005 GROUP BY member_name;

Задание 25. Определить, какие товары имеются в таблице Goods, но не покупались в течение 2005 года

SELECT good_name FROM Goods WHERE good_id NOT IN (SELECT good FROM Payments WHERE YEAR(date) = 2005);

Задание 26. Определить группы товаров, которые не приобретались в 2005 году

SELECT good_type_name FROM GoodTypes WHERE good_type_id NOT IN ( SELECT good_type_id FROM GoodTypes JOIN Goods ON good_type_id=type JOIN Payments ON good_id=good WHERE YEAR(date)=2005);

Задание 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_id=good WHERE YEAR(date)=2005 GROUP BY good_type_name;

Задание 28. Сколько рейсов совершили авиакомпании с Ростова (Rostov) в Москву (Moscow)?

SELECT COUNT(*) 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 Pass_in_trip.passenger=Passenger.id JOIN Trip ON Trip.id=Pass_in_trip.trip WHERE town_to='Moscow' AND plane='TU-134';

Задание 30. Выведите нагруженность (число пассажиров) каждого рейса (trip). Результат вывести в отсортированном виде по убыванию нагруженности.

SELECT trip, COUNT(Passenger) as count FROM Pass_in_trip GROUP BY trip ORDER BY count DESC;

Задание 31. Вывести всех членов семьи с фамилией Quincey.

SELECT * FROM FamilyMembers WHERE member_name LIKE '%Quincey';

Задание 32. Вывести средний возраст людей (в годах), хранящихся в базе данных. Результат округлите до целого в меньшую сторону.

SELECT FLOOR(AVG(YEAR(CURRENT_DATE) - YEAR(birthday))) AS age FROM FamilyMembers;

Задание 33. Найдите среднюю стоимость икры. В базе данных хранятся данные о покупках красной (red caviar) и черной икры (black caviar).

SELECT AVG(unit_price) AS cost FROM Payments WHERE good IN (SELECT good_id FROM Goods WHERE good_name LIKE '%caviar');

Задание 34. Сколько всего 10-ых классов

SELECT COUNT(name) AS count FROM Class WHERE name LIKE '10%';

Задание 35. Сколько различных кабинетов школы использовались 2.09.2019 в образовательных целях?

SELECT COUNT(classroom) AS count FROM Schedule WHERE date='2019-09-02';

Задание 36. Выведите информацию об обучающихся живущих на улице Пушкина (ul. Pushkina)?

SELECT * FROM Student WHERE address LIKE 'ul. Pushkina%';

Задание 37. Сколько лет самому молодому обучающемуся ?

SELECT MIN(TIMESTAMPDIFF(YEAR,birthday,CURRENT_DATE)) AS year FROM Student;

Задание 38. Сколько Анн (Anna) учится в школе?

SELECT COUNT(first_name) AS count FROM Student WHERE first_name='Anna';

Задание 39. Сколько обучающихся в 10 B классе ?

SELECT COUNT(student) AS count FROM Student_in_class JOIN Class ON Student_in_class.class=Class.id WHERE Class.name = '10 B';

Задание 40. Выведите название предметов, которые преподает Ромашкин П.П. (Romashkin P.P.) ?

SELECT name AS subjects FROM Subject JOIN Schedule ON Subject.id=Schedule.subject JOIN Teacher ON Schedule.teacher=Teacher.id WHERE Teacher.last_name='Romashkin' AND Teacher.first_name LIKE 'P%' AND Teacher.middle_name LIKE 'P%';

Задание 41. Во сколько начинается 4-ый учебный предмет по расписанию ?

SELECT start_pair FROM Timepair WHERE >Задание 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 Schedule.subject=Subject.id WHERE Subject.name = 'Physical Culture' ORDER BY Teacher.last_name

Задание 44. Найдите максимальный возраст (колич. лет) среди обучающихся 10 классов ?

SELECT MAX(TIMESTAMPDIFF(YEAR,birthday,CURRENT_DATE)) as max_year FROM Student JOIN Student_in_class ON Student.id=Student_in_class.student JOIN Class ON Student_in_class.class=Class.id WHERE Class.name LIKE '10%';

Задачи¶

Тут я складываю решение задач по SQL, может кому-то понадобится.

Именно для PostgreSQL есть отдельный раздел тут

Cсылки на задачи¶

• SQL. Занимательные задачи — отличный список задач с схемой HR отдела. Затрагивает очень много кейсов — от фильтрация, группировка, агрегация. Мастхев для прорешивания в первую очередь.
• SQL-EX — гигабазовый онлаин тренажер с >500 задач.
• SQL Academy — ещё один онлаин тренажер.
• Leetcode — Задачи по БД на литкоде.
• Интерактивный тренажер по SQL, Stepik — подойдет прям для новичков
• SQLBolt

Решения¶

Задачи с sql-academy.org¶

1. В какие города можно улететь из Парижа (Paris) и сколько времени это займёт?
   Задача на знание встроенных функций, решается при помощи TIMEDIFF :

 SELECT town_to, TIMEDIFF(time_in, time_out) as flight_time FROM trip WHERE town_from = 'Paris' 

 SELECT * FROM trip WHERE time_out BETWEEN "1900-01-01T10:00:00" AND "1900-01-01T14:00:00" 

 SELECT name FROM passenger WHERE LENGTH(name) = (SELECT MAX(LENGTH(name)) FROM passenger); 

 SELECT member_name FROM FamilyMembers ORDER BY birthday LIMIT 1 

 SELECT good_name, unit_price FROM Payments JOIN Goods ON Goods.good_id = Payments.good JOIN GoodTypes ON GoodTypes.good_type_id = Goods.type WHERE GoodTypes.good_type_name = "delicacies" ORDER BY unit_price DESC LIMIT 1 

 SELECT fm.status, fm.member_name, SUM(p.amount * p.unit_price) as costs FROM FamilyMembers as fm JOIN Payments AS p ON fm.member_id = p.family_member JOIN Goods as g ON g.good_id = p.good JOIN GoodTypes AS gt ON gt.good_type_id = g.type WHERE gt.good_type_name = "entertainment" GROUP BY fm.status, fm.member_name 

 SELECT g.good_name FROM Payments as p JOIN Goods as g ON p.good = g.good_id GROUP BY g.good_name HAVING COUNT(good_name) > 1 

 SELECT name FROM Passenger GROUP BY name HAVING COUNT(name) > 1 

 SELECT name, count(pt.id) as count FROM Pass_in_trip AS pt JOIN Passenger AS p ON pt.passenger = p.id GROUP BY pt.passenger ORDER BY count desc, name asc 

 SELECT member_name, status, SUM(p.amount * p.unit_price) as costs FROM Payments AS p JOIN FamilyMembers AS fm ON fm.member_id = p.family_member WHERE p.date BETWEEN "2005-01-01" AND "2005-12-31" GROUP BY p.family_member 

 SELECT fm.member_name, sum(p.amount * p.unit_price) AS costs FROM Payments AS p JOIN FamilyMembers AS fm ON fm.member_id = p.family_member WHERE p.date BETWEEN "2005-06-01" AND "2005-06-30" GROUP BY p.family_member 

 SELECT good_name FROM Goods AS g WHERE g.good_id NOT IN ( SELECT good FROM Payments AS p WHERE YEAR(p.date) = 2005 ) 

 SELECT good_type_name FROM GoodTypes WHERE good_type_name NOT IN ( SELECT DISTINCT gt.good_type_name FROM Payments as p JOIN Goods AS g ON p.good = g.good_id JOIN GoodTypes AS gt ON g.type = gt.good_type_id WHERE YEAR(p.date) = 2005 ) 

 SELECT gt.good_type_name, SUM(p.amount * p.unit_price) as costs FROM Payments AS p JOIN Goods AS g ON p.good = g.good_id JOIN GoodTypes AS gt ON g.type = gt.good_type_id WHERE YEAR(p.date) = 2005 GROUP BY gt.good_type_id 

 SELECT DISTINCT p.name FROM Trip AS t JOIN Pass_in_trip AS pt ON t.id = pt.trip JOIN Passenger AS p ON pt.passenger = p.id WHERE t.town_to = "Moscow" AND t.plane = "TU-134" 

 SELECT pt.trip, count(pt.trip) as count from Pass_in_trip AS pt GROUP BY pt.trip ORDER BY count(pt.trip) DESC 

 SELECT * FROM FamilyMembers WHERE member_name LIKE "% Quincey" 

 SELECT AVG(p.unit_price) as cost FROM Payments AS p JOIN Goods AS g ON p.good = g.good_id WHERE g.good_name IN ("red caviar", "black caviar") 

 SELECT COUNT(classroom) as count FROM Schedule WHERE date = "2019-09-02" 

 SELECT MIN(TIMESTAMPDIFF(YEAR, birthday, CURRENT_DATE)) AS year FROM Student 

 SELECT FLOOR(AVG(TIMESTAMPDIFF(YEAR, birthday, CURRENT_DATE))) AS age FROM FamilyMembers 

 SELECT s.name as subjects FROM Subject AS s JOIN Schedule AS sc ON sc.subject = s.id JOIN teacher AS t On sc.teacher = t.id WHERE t.last_name = "Romashkin" 

 SELECT DISTINCT TIMEDIFF( ( SELECT end_pair FROM Timepair WHERE id = 4 ), ( SELECT start_pair FROM Timepair WHERE id = 2 ) ) as time from Timepair 

 SELECT last_name from Teacher AS t JOIN Schedule AS sc ON sc.teacher = t.id JOIN Subject as s ON sc.subject = s.id WHERE s.name = "Physical Culture" ORDER BY t.last_name 

 SELECT MAX( TIMESTAMPDIFF(YEAR, s.birthday, CURRENT_DATE) ) as max_year FROM Student_in_class AS sc JOIN Class AS c ON sc.class = c.id JOIN Student AS s ON sc.student = s.id WHERE c.name LIKE "10%" 

 SELECT classroom FROM Schedule GROUP BY classroom HAVING COUNT(classroom) = ( SELECT COUNT(classroom) FROM Schedule GROUP BY classroom ORDER BY classroom DESC LIMIT 1 ) 

 SELECT DISTINCT name FROM Class AS c JOIN Schedule AS sc ON sc.class = c.id JOIN Teacher AS t ON sc.teacher = t.id WHERE t.last_name = "Krauze" 

 SELECT count(id) as count FROM Schedule WHERE date = "2019-08-30" AND teacher = ( SELECT id FROM Teacher WHERE last_name = "Krauze" ) 

 SELECT c.name as name, count(c.id) as count FROM Student_in_class AS sc JOIN Class AS c ON sc.class = c.id JOIN Student AS s ON sc.student = s.id GROUP BY c.name ORDER BY count(c.id) DESC 

 SELECT COUNT(student) * 100 / ( SELECT COUNT(student) FROM Student_in_class ) as percent FROM Student_in_class AS si JOIN Class AS c ON si.class = c.id WHERE c.name = "10 A" 

 SELECT FLOOR(COUNT(id) * 100 / ( SELECT COUNT(id) FROM Student )) as percent FROM Student WHERE YEAR(birthday) = 2000 

 SELECT sc.teacher FROM Schedule AS sc JOIN Class AS c ON c.id = sc.class WHERE c.name LIKE "11%" GROUP BY sc.teacher HAVING COUNT(DISTINCT c.name) = 2 

 DELETE FROM Company WHERE Company.id IN ( SELECT company FROM Trip GROUP BY company HAVING COUNT(id) = (SELECT MIN(count) FROM (SELECT count(*) as count FROM Trip GROUP BY Trip.company) as min ) ) 

 INSERT INTO Reviews SET id = (SELECT COUNT(*)+1 FROM Reviews as a), reservation_id = ( SELECT Reservations.id FROM Reservations JOIN Users ON Users.id = Reservations.user_id JOIN Rooms ON Rooms.id = Reservations.room_id WHERE Users.name = "George Clooney" AND Rooms.address = "11218, Friel Place, New York" ), rating = 5 

 UPDATE Timepair SET start_pair = start_pair + INTERVAL 30 MINUTE, end_pair = end_pair + INTERVAL 30 MINUTE 

 SELECT Rooms.* FROM Rooms JOIN Reservations ON Reservations.room_id = Rooms.id WHERE WEEK(Reservations.start_date, 1) = 12 AND YEAR(start_date) = 2020 

 SELECT REGEXP_SUBSTR(email, '(?<=@)[^.]*.[^.]*(?=\.).') as domain, COUNT(REGEXP_SUBSTR(email, '(?<=@)[^.]*.[^.]*(?=\.).')) as count FROM Users GROUP BY domain ORDER BY count DESC, domain 

 SELECT Rooms.home_type, Rooms.address, IFNULL(SUM(DATEDIFF(Reservations.end_date, Reservations.start_date)), 0) as days, IFNULL(SUM(total), 0) as total_fee FROM Rooms LEFT JOIN Reservations ON Rooms.id = Reservations.room_id WHERE Rooms.has_tv = true AND has_internet = true AND has_kitchen = true AND has_air_con = true GROUP BY Rooms.id 

 SELECT owner_id, IFNULL(SUM(Reservations.total), 0) as total_earn FROM Rooms LEFT JOIN Reservations ON Reservations.room_id = Rooms.id GROUP BY owner_id 

 SELECT room_id, COUNT(room_id) as count FROM Reservations GROUP BY room_id HAVING MOD(count, 2) 

 SELECT YEAR(start_date) as year, MONTH(start_date) as month, COUNT(*) as amount FROM Reservations GROUP BY YEAR(start_date), MONTH(start_date) ORDER BY YEAR(start_date) ASC