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

жүктеу/скачать 206,88 Kb.

Дата	14.11.2022
өлшемі	206,88 Kb.
	#50123

Байланысты:
SQL Practice

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

SELECT FLOOR(AVG(DATE_FORMAT(FROM_DAYS(DATEDIFF(NOW(), birthday)), '%Y') + 0)) as age

FROM FamilyMembers;

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'
);

Задание 35

Сколько различных кабинетов школы использовались 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';

Задание 36

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

Задание 37

Сколько лет самому молодому обучающемуся ?
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 id = 4
),
(
SELECT start_pair
FROM Timepair
WHERE id = 2
)
) 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%";

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

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

FROM Goods;

SELECT good_type_id INTO @good_type_id_food

FROM GoodTypes
WHERE good_type_name = "food";

#SELECT @id_add_1

#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;

Задание 52

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;

Задание 54

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)

жүктеу/скачать 206,88 Kb.

Достарыңызбен бөлісу: