------------------- объединение строк
SELECT CONCAT(DATE_FORMAT(time_out, "%H:%i, %e."),MONTH(time_out), " - ",
DATE_FORMAT(time_in, "%H:%i, %e."),MONTH(time_in)
) as flight_time FROM trip
SELECT CONCAT( DATE_FORMAT(time_out, "%H:%i, %e.%c"), " - ", DATE_FORMAT(time_in, "%H:%i, %e.%c") ) AS flight_time
FROM Trip;
------------------- если NULL в ноль (0) + объединение вложенной таблицы
SELECT home_type,
address,
COALESCE(r.days, 0) as days,
COALESCE(r.total_fee, 0) as total_fee
FROM Rooms
LEFT JOIN (
SELECT SUM(TIMESTAMPDIFF(day, start_date, end_date)) AS days,
SUM(Reservations.total) AS total_fee,
room_id
FROM Reservations,
Rooms
WHERE Reservations.room_id = Rooms.id
and has_tv
and has_internet
and has_kitchen
and has_air_con
GROUP BY room_id
) AS r ON r.room_id = Rooms.id
WHERE has_tv
and has_internet
and has_kitchen
and has_air_con
SELECT home_type,
address,
COALESCE(SUM(total / Reservations.price), 0) AS days,
COALESCE(SUM(total), 0) AS total_fee
FROM Rooms
LEFT JOIN Reservations ON Rooms.id = Reservations.room_id
WHERE has_tv
AND has_internet
AND has_kitchen
AND has_air_con
GROUP BY 1,
2
------------------- подсчет и сортировка по двум полям
select right(email, length(email)-INSTR(email, '@')) as domain, COUNT(*) as count FROM Users
GROUP by domain
ORDER BY count DESC, domain
------------------- интервал
SELECT Rooms.*
FROM Rooms
JOIN Reservations on rooms.id = reservations.room_id
WHERE start_date >= '2020-01-01' + INTERVAL 11 WEEK
and start_date < '2020-01-01' + INTERVAL 12 WEEK
-------------------
INSERT INTO Reviews
SELECT COUNT(*) + 1,
(
SELECT id
FROM Reservations
WHERE user_id =(
SELECT id
FROM Users
WHERE name = "George Clooney"
)
AND room_id =(
SELECT id
FROM Rooms
WHERE address = "11218, Friel Place, New York"
)
),
5
FROM Reviews
------------------- удалить позиции с минимумом
DELETE FROM Company
WHERE id IN (
SELECT company
FROM (
SELECT company,
COUNT(*) AS count
FROM Trip
GROUP BY company
HAVING count = (
SELECT COUNT(*) AS c
FROM Trip
GROUP BY company
LIMIT 1
)
) AS ids
)
------------------- id+1 (ручной auto_increment)
INSERT INTO Goods ( SELECT COUNT(*) + 1, 'Cheese', ( SELECT good_type_id
FROM GoodTypes
WHERE good_type_name = 'food' limit 1 ) FROM Goods
)
------------------- %
SELECT ( SELECT COUNT(*) FROM Student_in_class
JOIN Class ON class = Class.id
WHERE Class.name = '10 A' ) / ( SELECT COUNT(*) FROM Student_in_class
) * 100 AS percent
-------------------
SELECT DISTINCT name
FROM Schedule
JOIN Teacher -- c LEFT будет ошибка
JOIN Class ON class = Class.id
AND teacher = Teacher.id
WHERE Teacher.last_name = 'Krauze'
------------------- MAX + COUNT
SELECT Schedule.classroom
FROM Schedule
GROUP BY classroom
HAVING COUNT(*) =(
SELECT MAX(count)
FROM (
SELECT COUNT(*) AS count
FROM Schedule
GROUP BY classroom
) AS A
)
-------------------
SELECT MAX(TIMESTAMPDIFF(YEAR, Student.birthday, NOW())) AS max_year
FROM Student
INNER JOIN Student_in_class ON Student_in_class.student = Student.id
INNER JOIN Class ON Student_in_class.class = Class.id
WHERE Class.name LIKE '10%'
------------------- максимальный возрост
SELECT DISTINCT town_to
FROM Pass_in_trip,
Passenger,
Trip
WHERE Pass_in_trip.passenger = Passenger.id
AND Pass_in_trip.trip = Trip.id
AND Passenger.name = 'Bruce Willis'
------------------- разница по времени
SELECT TIMEDIFF( ( SELECT end_pair
FROM Timepair
WHERE id = 4 ), ( SELECT start_pair
FROM Timepair
WHERE id = 2 ) ) AS time
-------------------
SELECT COUNT(*) AS count
FROM ( SELECT DISTINCT classroom
FROM Schedule
WHERE Schedule.date LIKE '2019-09-02%' ) AS ClassRoom
-------------------
SELECT DISTINCT name
FROM Passenger as p, Pass_in_trip as pit, Trip as t
WHERE town_to = 'Moscow' and plane = 'TU-134'
and trip = t.id AND p.id = passenger;
-------------------
SELECT COUNT(*) AS count FROM Trip
WHERE town_from = 'Rostov' and town_to = 'Moscow';
-------------------
SELECT good_type_name , SUM(unit_price * amount) AS costs FROM Goods, Payments, GoodTypes
WHERE YEAR(date) = 2005 AND good_id = good AND type = good_type_id
GROUP BY good_type_name;
-------------------
SELECT name, COUNT(*) AS count
FROM Passenger, Pass_in_trip
WHERE Pass_in_trip.passenger = Passenger.id
GROUP BY Passenger.id
ORDER BY COUNT(*) DESC, name ASC
-------------------
SELECT member_name, status, SUM(unit_price * amount) AS costs
FROM FamilyMembers, Payments
WHERE member_id = family_member
and YEAR(date) = '2005' GROUP BY member_id
SELECT member_name
FROM FamilyMembers
WHERE birthday =(
SELECT MIN(birthday)
FROM FamilyMembers
)
-------------------
SELECT good_name, unit_price FROM Goods RIGHT JOIN GoodTypes ON good_type_id = type
RIGHT JOIN Payments On good = good_id
WHERE good_type_name = 'delicacies'
ORDER BY unit_price DESC
LIMIT 1;
Правильный вариант
SELECT good_name,
unit_price
FROM Payments,
Goods,
GoodTypes
WHERE good_type_id = type
AND good_type_name = 'delicacies'
AND good_id = good
AND unit_price =(
SELECT MAX(unit_price)
FROM Payments,
Goods,
GoodTypes
WHERE good_id = good
AND good_type_id = type
AND good_type_name = 'delicacies'
)
-------------------
SELECT member_name, SUM(unit_price * amount) AS costs
FROM FamilyMembers, Payments
WHERE member_id = family_member
AND YEAR(date) = 2005 AND MONTH(date) = 6 GROUP BY member_name
-------------------
SELECT good_name
FROM Goods
WHERE good_id NOT IN (
SELECT DISTINCT good
FROM Payments
WHERE YEAR(date) = 2005
)
-------------------
SELECT DISTINCT good_type_name FROM GoodTypes, Goods
WHERE type NOT IN
(SELECT DISTINCT type FROM Payments, Goods
WHERE YEAR(date) = 2005 AND good = good_id)
AND type = good_type_id;