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

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

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

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

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

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

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

Выполнили:

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

гр. …………

……………………..

Принял:

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

Омск 2022

Ход работы

Шаг 1. Выполнение запросов в Colab

Подключение к бд world:

Рисунок 1 Добавление файла world.sql в директорию

Рисунок 2 Создание пустой базы

Рисунок 3 Выполнение скрипта SQL

Рисунок 4 Проверка наличии базы данных

Рисунок 5 Проверка наличии таблицы city

Рисунок 6 проверка всех таблиц

Импорт БД в MySQL Workbench

1) Выведение всех стран, которые начинаются на Ar

SELECT NAME FROM country WHERE NAME LIKE 'Ar%'

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

2) Выведение столицы России.

SELECT city.name FROM country JOIN city on country.Capital = city.ID

WHERE country.Name LIKE 'Rus%'

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

3) Выведение стран, где продолжительность жизни больше 70 и площадь больше 1М.

SELECT Name FROM country 

WHERE LifeExpectancy>70 AND SurfaceArea>1000000

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

4) Выведение десяти первых городов, отсортировав их по населению и по возрастанию.

SELECT Name, Population FROM city ORDER BY Population LIMIT 10

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

5) Выведение всех стран, которые располагаются на континенте Азия и их площадь меньше 1000 и страны, которые располагаются на континенте Европа и их площадь более 5000

SELECT Name FROM country 

WHERE Continent = 'Asia' AND SurfaceArea < 1000

OR Continent = 'Europe' AND SurfaceArea > 5000

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

6) Группировка все языков стран для того, чтобы понять сколько языков на земном шаре

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

7) Выведение названия страны с самой высокой средней продолжительностью жизни.

SELECT Name, LifeExpectancy FROM country

WHERE LifeExpectancy = (SELECT MAX(LifeExpectancy) FROM country)

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

8) Выведение количество языков страны Bermuda.

SELECT COUNT(countrylanguage.Language) FROM country 

JOIN countrylanguage on country.Code = countrylanguage.CountryCode 

WHERE country.Name = 'Bermuda'

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

9) Выведение средней площади стран, где средняя продолжительность жизни меньше 50 и код содержит букву A или букву O

SELECT AVG(SurfaceArea) FROM country

WHERE LifeExpectancy < 50 AND Code LIKE '%A%' OR '%O%'

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

10) Нахождение общей численности населения городов России

SELECT SUM(city.Population) FROM city

JOIN country ON city.CountryCode = country.Code

WHERE country.Name LIKE 'Russian%'

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

11) Выведение страны с наименее многочисленным населением

SELECT Name FROM country

WHERE Population = (SELECT MIN(Population) FROM country)

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

12) Подсчет средней длины названий городов России.

SELECT AVG(LENGTH(city.Name)) FROM city

JOIN country ON city.CountryCode = country.Code

WHERE country.Name LIKE 'Russian%'

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

13) Нахождение страны со средней продолжительностью жизни менее 50. Какая страна первая в списке?

SELECT Name, LifeExpectancy FROM country

WHERE LifeExpectancy < 50 LIMIT 1

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

14) Выведение стран, у которых года независимости после 1975, но вывести только последние 2 цифры года.

SELECT Name, SUBSTRING(IndepYear, 3, 2) FROM country

WHERE IndepYear > 1975

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

15) Нахождение остатка от деления 16 на 5.

SELECT MOD(16, 5)

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

Шаг 2. Запросы в MySQL Workbench

1. Все страны с площадью от 1000 до 5000

SELECT Name FROM country WHERE SurfaceArea BETWEEN 1000 AND 5000

Рис 1. Запрос и результат

2. Языки страны Конго

SELECT * FROM countrylanguage WHERE CountryCode = “COG”

Рис 2. Запрос и результат

3. Найти Омск и его население

SELECT * FROM world.city where Name = ‘Omsk’

Рис 3. Запрос и результат

4. Вывести первые 5 стран с на иболее многочисленным населением

SELECT Name FROM country ORDER BY Population desc limit 5

Рис 4. Запрос и результат

5. Повторить запрос 14, но так, чтобы коды всех этих стран содержали букву A

SELECT COUNT(SurfaceArea) FROM country WHERE SurfaceArea > 5000 and Code LIKE '%A%'

Рис 5. Запрос и результат

6. Страны из континента Океания, площадью менее 6000 и с кодом начинающимся на N

SELECT * FROM country where Continent = 'Oceania' AND (SurfaceArea < 6000 AND Code LIKE 'N%')

Рис 6. Запрос и результат

7. Количество стран с площадью больше 5000

SELECT COUNT(*) ASКОЛИЧЕСТВО FROM country WHERE SurfaceArea > 5000

Рис 7. Запрос и результата

8. Средняя площадь стран, где средняя продолжительность жизни больше 67

SELECT AVG(SurfaceArea) FROM country WHERE LifeExpectancy > 67

Рис 8. Запрос и результат

9. Вывести среднюю численность населения городов России

SELECT AVG(city.Population) FROM city JOIN country ON city.CountryCode = country.Code WHERE country.Name LIKE 'Russian%'

Рис 9. Запрос и результат

10. Вывести страну с наиболее многочисленным населением

SELECT Name FROM country WHERE Population = (SELECT MAX(Population) FROM country)

Рис 10. Запрос и результат

11. Количество стран, которые располагаются на континенте Азия и их площадь меньше 1000 и страны, которые располагаются на континенте Европа и их площадь более 5000.

SELECT COUNT(Name) FROM country WHERE Continent = 'Asia' AND SurfaceArea < 1000 OR Continent = 'Europe' AND SurfaceArea > 5000

Рис 11. Запрос и результат

12. Посчитать квадратный корень из площадей стран, у которых средняя продолжительность жизни от 45 до 55.

SELECT Name, SQRT(SurfaceArea) FROM country where LifeExpectancy between 45 and 55

Рис 12. Запрос и результат

13. Вывести столицу России, но все буквы заглавные.

SELECT UPPER(city.name) FROM country JOIN city on country.Capital = city.ID WHERE country.Name LIKE 'Rus%'

Рис 13. Запрос и результат

14. Найти |38-20-168|, используя числовые функции.

SELECT ABS(38-20-168)

Рис 14. Запрос и результат

15. Округлить число в меньшую сторону 5,128, округлить число в большую сторону 5,265.

SELECT round(5.128, 1), round(5.265, 1)

Рис 15. Запрос и результат

Шаг 3. Разработка и выполнение запросов на виртуальной машине, используя MySQL Workbench.

1)Вывести все языки страны Австралия

SELECT country.Language FROM country JOIN countryLanguage on country.Code=countryLanguage.countryCode WHERE country.Name LIKE ‘Australia%’

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

2) Вывести 10 городов Китая с наибольшим количеством населения

SELECT * FROM City WHERE CountryCode = (SELECT Code FROM Country WHERE Name = 'China') ORDER BY Population DESC LIMIT 10

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

3) Вывести всю информацию про самый малочисленный город

SELECT * FROM City WHERE Population = (SELECT MIN(Population) FROM City)

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

4) Вывести среднее население стран, где средняя продолжительность жизни меньше 50.

SELECT AVG(Population) FROM country WHERE LifeExpectancy < 50

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

5) Вывести численность населения всех стран

SELECT SUM(Population) FROM сountry

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

Вывод

В ходе лабораторной работы были изучены запросы и агрегированные функции в MySQL. Были выполнены задания с использование Google Colab и MySQL Workbench. Запросы писались для базы данных World, содержащую информацию о странах, городах и языках. Был изучен алгоритм импорта базы данных в СУБД на виртуальной машине. Также были разработаны и выполнены запросы для рассматриваемой базы данных World.