лаба4

Федеральное государственное автономное образовательное учреждение

высшего профессионального образования

«ОМСКИЙ ГОСУДАРСТВЕННЫЙ ТЕХНИЧЕСКИЙ УНИВЕРСИТЕТ»

Кафедра «Комплексная защиты информации»

Лабораторная работа

по курсу «Базы данных»

Лабораторная работа №4

Выполнили:

Студентки 2-го курса

…………………

Принял:

Самотуга А.Е.

Омск 2022

Импорт БД в Collab:

Импорт БД в MySQL Workbench

Часть 1. Написать запрос, проверить на базе данных, скачанной из архива.

В отчет включить задание к запросу, текст запроса, скриншот с sql запросом

и результатом выполнения, сделанный в СУБД( именно в таком порядке

для каждого запроса). При написании запроса воспользоваться

подзапросами, либо объединениями (если можно сделать подзапросом, то

делать подзапросом, если нет, при помощи объединения)

1) Вывести всех клиентов с их адресами

SELECT first_name,last_name,address FROM sakila.customer JOIN sakila.address ON sakila.address.address_id = sakila.customer.address_id

Рисунок 1 Запрос и результат его выполнения

2) Вывести всех клиентов с их адресами, а адреса с городами

SELECT first_name, last_name, address, city FROM customer INNER JOIN address ON customer.address_id = address.address_id INNER JOIN city ON address.city_id=city.city_id

Рисунок 2 Запрос и результат его выполнения

3) Вывести всех клиентов с их адресами, а адреса с городами, а города со странами

SELECT first_name,last_name,address,city,country FROM sakila.customer JOIN sakila.address ON sakila.address.address_id = sakila.customer.address_id JOIN sakila.city ON sakila.address.city_id = sakila.city.city_id JOIN sakila.country ON sakila.city.country_id = sakila.country.country_id

Рисунок 3 Запрос и результат его выполнения

4) Вывести все фильмы категории драма. Нужны данные только по фильмам SELECT title, name FROM film INNER JOIN film_category ON film.film_id=film_category.category_id INNER JOIN category ON film_category.category_id=category.category_id WHERE name LIKE 'Drama'

Рисунок 4 Запрос и результат его выполнения

5) В какой категории больше всего фильмов?

SELECT category_id, name, last_update FROM `sakila`.`category` WHERE category_id = (SELECT category_id FROM `sakila`.`film_category` GROUP BY category_id ORDER BY COUNT(film_id) DESC LIMIT 1);

Рисунок 5 Запрос и результат его выполнения

6) В каких фильмах снималась Scarlett Damon SELECT title FROM actor INNER JOIN film_actor ON actor.actor_id=film_actor.actor_id INNER JOIN film ON film_actor.film_id=film.film_id WHERE first_name LIKE 'Scarlett' AND last_name LIKE 'Damon'

Рисунок 6 Запрос и результат его выполнения

7) Кому из сотрудников заплатили больше?

SELECT * FROM `sakila`.`staff`WHERE staff_id=(SELECT staff_id FROM `sakila`.`payment` GROUP BY staff_id ORDER BY SUM(amount) DESC LIMIT 1);

Рисунок 7 Запрос и результат его выполнения

8) Выведите адреса точек хранения фильмов и продавцов этих точек SELECT address,first_name,last_name FROM store INNER JOIN address ON store.address_id=address.address_id JOIN staff ON store.manager_staff_id=staff.staff_id

Рисунок 8 Запрос и результат его выполнения

9) Какие фильмы брала Anna Hill?

SELECT * FROM `sakila`.`film` where film_id in(SELECT film_id FROM `sakila`.`inventory` WHERE inventory_id in(SELECT inventory_id FROM `sakila`.`rental` WHERE customer_id = (SELECT customer_id FROM `sakila`.`customer` WHERE first_name = 'ANNA'and last_name = 'HILL')));

Рисунок 9 Запрос и результат его выполнения

10) Вывести по городам количество активных пользователей SELECT city, count(customer_id) FROM customer INNER JOIN address ON customer.address_id=address.address_id INNER JOIN city ON address.city_id=city.city_id WHERE active LIKE '1' group by city

Рисунок 10 Запрос и результат его выполнения

11) Вывести все города Российской Федерации одним запросом

SELECT city_id, city, country_id, last_update FROM `sakila`.`city` WHERE country_id = (SELECT country_id FROM `sakila`.`country` WHERE country = 'Russian Federation');

Рисунок 11 Запрос и результат его выполнения

12) Вывести категории отсортировав их по популярности (сколько клиентов выбирают их) SELECT name, rental_rate FROM category INNER JOIN film_category ON category.category_id=film_category.category_id INNER JOIN film ON film_category.film_id=film.film_id INNER JOIN inventory ON film.film_id=inventory.film_id order by rental_rate desc

Рисунок 12 Запрос и результат его выполнения

13) Вывести только фильмы на японском языке

SELECT film_id, title, description, release_year, language_id, original_language_id, rental_duration, rental_rate, length, replacement_cost, rating, special_features, last_update

FROM `sakila`.`film` WHERE original_language_id = (SELECT language_id FROM `sakila`.`language` WHERE name = 'Japanese');

Рисунок 13 Запрос и результат его выполнения

14) Вывести только фильмы на французском языке и категории “семейный” SELECT category.name, language.name FROM film INNER JOIN film_category ON film.film_id=film_category.film_id INNER JOIN category ON film_category.category_id=category.category_id INNER JOIN language ON film.language_id=language.language_id WHERE language.name LIKE 'French' AND category.name LIKE 'Family';

Рисунок 14 Запрос и результат его выполнения

15) Сколько клиентов из города Lethbridge?

SELECT * FROM `sakila`.`customer` WHERE address_id in (SELECT address_id

FROM `sakila`.`address`

WHERE city_id = (SELECT city_id FROM `sakila`.`city` WHERE city = 'Lethbridge'));

Рисунок 15 Запрос и результат его выполнения

16) Сколько клиентов из города Lethbridge и бравших фильмы во второй точке? SELECT COUNT(customer_id) FROM customer INNER JOIN store ON customer.store_id=store.store_id WHERE store.store_id LIKE '2'

Рисунок 16 Запрос и результат его выполнения

17) Сколько клиентов с именем Карл?

SELECT customer_id, store_id, first_name, last_name, email, address_id, active, create_date, last_update

FROM `sakila`.`customer` WHERE first_name = 'Carl';

Рисунок 17 Запрос и результат его выполнения

18) Какое самое популярное имя в списке клиентов? SELECT first_name FROM customer group by first_name ORDER BY COUNT(*)

Рисунок 18 Запрос и результат его выполнения

19) Сколько фильмов взяли в мае 2005?

SELECT COUNT(*) as 'Count Films from May 2005'

FROM `sakila`.`rental` WHERE YEAR(rental_date) = 2005 and Month(rental_date) = 5;

Рисунок 19 Запрос и результат его выполнения

20) Список клиентов которые брали фильмы в мае 2005 с первой точки SELECT rental_date,first_name,store.store_id FROM rental INNER JOIN customer ON rental.customer_id=customer.customer_id INNER JOIN store ON customer.store_id=store.store_id WHERE MONTHNAME(rental_date)='May' AND store.store_id LIKE "1"

Рисунок 20 Запрос и результат его выполнения

21) В какой стране больше всего городов

SELECT COUNT(city) as 'Count city',country

FROM `sakila`.`city`

JOIN sakila.country ON sakila.country.country_id = sakila.city.country_id GROUP BY country ORDER BY COUNT(city) DESC LIMIT 1;

Рисунок 21 Запрос и результат его выполнения

22) Из какой страны клиентов больше всего SELECT country, COUNT(first_name) FROM customer

RIGHT JOIN address ON customer.address_id=address.address_id

RIGHT JOIN city ON address.city_id=city.city_id

RIGHT JOIN country ON city.country_id=country.country_id

GROUP BY country.country ORDER BY COUNT(first_name) DESC LIMIT 1

Рисунок 22 Запрос и результат его выполнения

23) Вывести всех неактивных клиентов с адресами и городами

SELECT first_name,last_name,email,active,address,city

FROM `sakila`.`customer` JOIN sakila.address on sakila.address.address_id = sakila.customer.address_id

JOIN sakila.city on sakila.city.city_id = sakila.address.city_id

WHERE active = 0;

Рисунок 23 Запрос и результат его выполнения

24) Какая сумма была выплачена Джону за август 2005? SELECT first_name,SUM(amount) FROM staff INNER JOIN payment ON staff.staff_id=payment.staff_id WHERE monthname(payment_date)='August' AND first_name LIKE 'Jon' group by first_name

Рисунок 24 Запрос и результат его выполнения

25) Сколько актрис с именем Сара играет в драме?

SELECT actor_id, film_id, last_update

FROM `sakila`.`film_actor` WHERE actor_id in (SELECT actor_id FROM `sakila`.`actor` WHERE first_name = 'Sarah')

and film_id in (SELECT film_id FROM `sakila`.`film_category`

where category_id = (SELECT category_id FROM `sakila`.`category` WHERE name = 'Drama'));

Рисунок 25 Запрос и результат его выполнения

Часть 2 Разработать в рамках тематики лабораторной 5 запросов

«SELECT SUM(amount) FROM payment JOIN customer on customer.customer_id = payment.customer_id GROUP BY  concat(first_name,' ',last_name) HAVING SUM(amount) < 100»

2) Вывести оплаты, которые больше среднего значений всех оплат (15 штук)

«SELECT amount FROM payment WHERE amount > (SELECT AVG(amount) FROM payment) ORDER BY amount DESC LIMIT 15»

3) Вывести фильмы с рейтингом G и PG-13

«SELECT title FROM film where film_id in (SELECT film_id FROM film WHERE rating in ('G', 'PG-13'))»

4) Вывести 3 любых фильма, в которых снялся Adam Hopper

«SELECT title FROM film_list WHERE FID IN(SELECT FID FROM film_actor WHERE actor_id = (SELECT actor_id FROM actor WHERE first_name = 'ADAM' and last_name = 'HOPPER')) ORDER BY title DESC LIMIT 3»

5) в каких 5 странах больше всего городов (вывести страну и количество городов)

«SELECT COUNT(city), country FROM city JOIN country ON country.country_id = city.country_id GROUP BY country ORDER BY COUNT(city) DESC LIMIT 5»

Вывод: В ходе данной лабораторной работы мы изучили подзапросы и объединение различных типов с использованием JOIN. Были выполнены запросы с использованием Google Collab и MySQL Workbench. Запросы были произведен в базе данных Sakila. Был изучен алгоритм импорта базы данных в СУБД на виртуальной машине.