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



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

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)

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




©emirsaba.org 2024
әкімшілігінің қараңыз

    Басты бет