Поиск информации в реляционных базах данных
 Скачать 0.68 Mb.
|
|
© К. Поляков, 2009-2021 3 (базовый уровень, время – 3 мин)Тема: Поиск информации в реляционных базах данных. Что проверяется: Знание о технологии хранения, поиска и сортировки информации в реляционных базах данных. 3.5.1. Системы управления базами данных. Организация баз данных 2.2. Умение создавать и использовать структуры хранения данных Что нужно знать: реляционные базы данных обычно хранятся в памяти компьютера в виде нескольких связанных таблиц столбцы таблицы называются полями, а строки – записями каждая таблица содержит описание одного типа объектов (человека, бригады, самолета) или одного типа связей между объектами (например, связь между автомобилем и его владельцем) в каждой таблице есть ключ – некоторое значение (это может быть одно поле или комбинация полей), которое отличает одну запись от другой; в таблице не может быть двух записей с одинаковыми значениями ключа на практике часто используют суррогатные ключи – искусственно введенное числовое поле (обычно оно называется идентификатор, ID) таблицы связываются с помощью ключей; чаще всего используется связь 1:N (или 1:), когда одной записи в первой таблице может соответствовать много записей во второй таблице, но не наоборот; например:  Согласно этой таблице, бумага и канцелярские принадлежности поставляются компанией Бета (ID = 23), бензин – компанией Альфа (ID = 14), а корм для кошек – компанией Гамма (ID = 24). для решения задач этого типа нужно уметь выполнять сортировку и фильтрацию в электронных таблицах для выполнения сортировки в Excel выделяем все столбцы таблицы, переходим на вкладку «Данные» и щёлкаем по кнопке «Сортировка»:  затем в появившемся окне устанавливаем режим сортировки:  фильтрация – это отбор записей, соответствующих условию; при этом все записи, не соответствующие условию, скрываются, чтобы снова их увидеть, нужно отключить фильтр для выполнения фильтрации в Excel выделяем все столбцы таблицы, переходим на вкладку «Данные» и щёлкаем по кнопке «Фильтр»:  после этого справа от заголовков столбцов появляются стрелки; если нажат на такую стрелку, можно выбрать только некоторые значения; все строки, для которых значения в этом столбце не совпадают ни с одним из выбранных, скрываются:  Пример задания:Р-00. (демо-2022) В файле 3-0.xls приведён фрагмент базы данных «Продукты» о поставках товаров в магазины районов города. База данных состоит из трёх таблиц. Таблица «Движение товаров» содержит записи о поставках товаров в магазины в течение первой декады июня 2021 г., а также информацию о проданных товарах. Поле Тип операции содержит значение Поступление или Продажа, а в соответствующее поле Количество упаковок, шт. занесена информация о том, сколько упаковок товара поступило в магазин или было продано в течение дня. Таблица «Товар» содержит информацию об основных характеристиках каждого товара. Таблица «Магазин» содержит информацию о местонахождении магазинов. На рисунке приведена схема указанной базы данных.  Используя информацию из приведённой базы данных, определите на сколько увеличилось количество упаковок яиц диетических, имеющихся в наличии в магазинах Заречного района, за период с 1 по 10 июня включительно. В ответе запишите только число. Решение (Microsft Excel): Для таблицы «Магазин» включаем фильтр по району (или сортируем по району) и определяем ID всех магазинов Заречного района:  В таблице «Товар» находим артикул (код) товара «Яйцо диетическое». Для этого можно использовать поиск (Ctrl+F или кнопка  на вкладке Главная). Ещё лучше отсортировать таблицу Товары по столбцу «Наименование товара», потому что один и тот же товар может быть у разных поставщиков (и иметь разные артикулы).В нашем случае есть единственный поставщик этого товара, артикул товара равен 15:  В таблице «Движение товаров» включаем фильтры: артикул = 15, ID магазинов: М3, М9, М11 и М14; сортируем оставшиеся строки по типу операции:  Выделяем ячейки столбца «Количество упаковок» в тех строках, где операция – «Поступление»; в строке состояния читаем сумму – 1420. Выделяем ячейки столбца «Количество упаковок» в тех строках, где операция – «Продажа»; в строке состояния читаем сумму – 454. (Б.С. Михлин) Если вывод суммы в строке состояния отключен, то щёлкаем по строке состояния правой кнопкой мыши и в контекстном меню читаем Сумму (или можем ее добавить):  Обратите внимание, что вычисление суммы в отдельной ячейке с помощью встроенной функции СУММ даст неверный результат. Поскольку данные отфильтрованы, часть строк скрыто. Функция СУММ принимает адрес диапазона, то есть суммирует не только видимые ячейки, но и скрытые. Находим разность: 1420 – 454 = 966. Есть ещё один вариант – можно скопировать отфильтрованные данные из столбцов «Количество упаковок» и «Тип операции» на новый лист и потом использовать формулу с двумя вызовами функции СУММ:  Ответ: 966. Задачи для тренировки:(  Е. Джобс) В файле 3-1.xls приведён фрагмент базы данных «Рейсы» о движении грузов на базе. База данных состоит из одной таблицы. Таблица «Рейсы» содержит записи о водителе, объеме перевезенного груза в килограммах и характере переводки («привоз» на базу или «вывоз» с базы). На рисунке приведена схема данных.Используя информацию из приведённой базы данных, определите на сколько килограммов отличается суммарное количество вывезенных и привезенных Ивановым грузов. В ответе запишите только число. (Е. Джобс) В файле 3-1.xls приведён фрагмент базы данных «Рейсы» о движении грузов на базе. База данных состоит из одной таблицы. Таблица «Рейсы» содержит записи о водителе, объеме перевезенного груза в килограммах и характере переводки («привоз» на базу или «вывоз» с базы). На рисунке приведена схема данных. Используя информацию из приведённой базы данных, определите сколько было совершено рейсов с объёмом груза больше 3500 кг. В ответе запишите только число. (Е. Джобс) В файле 3-1.xls приведён фрагмент базы данных «Рейсы» о движении грузов на базе. База данных состоит из одной таблицы. Таблица «Рейсы» содержит записи о водителе, объеме перевезенного груза в килограммах и характере переводки («привоз» на базу или «вывоз» с базы). На рисунке приведена схема данных.   Используя информацию из приведённой базы данных, определите сколько раз Уточкин и Сидоров вывезли с базы грузы объемом не менее 1500 кг и не более 2000 кг. В ответе запишите только число.(Е. Джобс) В файле 3-2.xls приведён фрагмент базы данных «Рейсы» о рейсах самолетов. База данных состоит из одной таблицы. Таблица «Рейсы» содержит записи о городах отправления и прибытия, и также номер борта, совершающего рейс. На рисунке приведена схема данных. Используя информацию из приведённой базы данных, определите сколько рейсов совершил борт 110. В ответе запишите только число. (Е. Джобс) В файле 3-2.xls приведён фрагмент базы данных «Рейсы» о рейсах самолетов. База данных состоит из одной таблицы. Таблица «Рейсы» содержит записи о городах отправления и прибытия, и также номер борта, совершающего рейс. На рисунке приведена схема данных. И Рейсы
спользуя информацию из приведённой базы данных, определите какой борт больше всего летал по маршруту Екатеринбург — Краснодар. В ответе запишите только число – номер борта. (Е. Джобс) В файле 3-2.xls приведён фрагмент базы данных «Рейсы» о рейсах самолетов. База данных состоит из одной таблицы. Таблица «Рейсы» содержит записи о городах отправления и прибытия, и также номер борта, совершающего рейс. На рисунке приведена схема данных. Используя информацию из приведённой базы данных, определите сколько рейсов совершил борт 128 таких, что Москва была одним из концов маршрута - городом отправления или городом прибытия. В ответе запишите только число. (Е. Джобс) В файле 3-3.xls приведён фрагмент базы данных «Оператор» об оказанных услугах. База данных состоит из трёх таблиц. Таблица «Клиенты» содержит записи об абонентах, которым были оказаны услуги. О каждом абоненте содержится следующая информация: район, в котором проживает абонент, адрес (улица и дом) и фамилия с инициалами. Таблица «Услуги» содержит записи об оказываемых оператором услугах - наименование и цена оказанной услуги. Таблица «Оказанные услуги» содержит информацию о том когда (поле дата), кому (ID клиента) и какая услуга (ID услуги) была оказана. На рисунке приведена схема базы данных.  Используя информацию из приведённой базы данных, определите какое количество услуг было оказано жителям Центрального района. В ответе запишите только число. (Е. Джобс) В файле 3-3.xls приведён фрагмент базы данных «Оператор» об оказанных услугах. База данных состоит из трёх таблиц. Таблица «Клиенты» содержит записи об абонентах, которым были оказаны услуги. О каждом абоненте содержится следующая информация: район, в котором проживает абонент, адрес (улица и дом) и фамилия с инициалами. Таблица «Услуги» содержит записи об оказываемых оператором услугах - наименование и цена оказанной услуги. Таблица «Оказанные услуги» содержит информацию о том когда (поле дата), кому (ID клиента) и какая услуга (ID услуги) была оказана. На рисунке приведена схема базы данных. Используя информацию из приведённой базы данных, определите какую услугу чаще всего оказывал оператор. В ответе запишите только число - идентификатор (ID) услуги. (Е. Джобс) В файле 3-3.xls приведён фрагмент базы данных «Оператор» об оказанных услугах. База данных состоит из трёх таблиц. Таблица «Клиенты» содержит записи об абонентах, которым были оказаны услуги. О каждом абоненте содержится следующая информация: район, в котором проживает абонент, адрес (улица и дом) и фамилия с инициалами. Таблица «Услуги» содержит записи об оказываемых оператором услугах - наименование и цена оказанной услуги. Таблица «Оказанные услуги» содержит информацию о том когда (поле дата), кому (ID клиента) и какая услуга (ID услуги) была оказана. На рисунке приведена схема базы данных. Используя информацию из приведённой базы данных, определите сколько услуг, связанных с телевидением, было оказано в период с 8 по 12 августа жителям речного района. В ответе запишите только число. (Е. Джобс) В файле 3-3.xls приведён фрагмент базы данных «Оператор» об оказанных услугах. База данных состоит из трёх таблиц. Таблица «Клиенты» содержит записи об абонентах, которым были оказаны услуги. О каждом абоненте содержится следующая информация: район, в котором проживает абонент, адрес (улица и дом) и фамилия с инициалами. Таблица «Услуги» содержит записи об оказываемых оператором услугах - наименование и цена оказанной услуги. Таблица «Оказанные услуги» содержит информацию о том когда (поле дата), кому (ID клиента) и какая услуга (ID услуги) была оказана. На рисунке приведена схема базы данных. Используя информацию из приведённой базы данных, определите на какую сумму оператор оказал услуги хостинга, видеонаблюдения и установки антивируса жителям Нового района. В ответе запишите только число. (Е. Джобс) В файле 3-3.xls приведён фрагмент базы данных «Оператор» об оказанных услугах. База данных состоит из трёх таблиц. Таблица «Клиенты» содержит записи об абонентах, которым были оказаны услуги. О каждом абоненте содержится следующая информация: район, в котором проживает абонент, адрес (улица и дом) и фамилия с инициалами. Таблица «Услуги» содержит записи об оказываемых оператором услугах - наименование и цена оказанной услуги. Таблица «Оказанные услуги» содержит информацию о том когда (поле дата), кому (ID клиента) и какая услуга (ID услуги) была оказана. На рисунке приведена схема базы данных. Используя информацию из приведённой базы данных, на сколько чаще жители района Острова оплачивали услугу выделенного IP-адреса, в сравнении с жителями Полярного района. В ответе запишите только число. (Е. Джобс) В файле 3-3.xls приведён фрагмент базы данных «Оператор» об оказанных услугах. База данных состоит из трёх таблиц. Таблица «Клиенты» содержит записи об абонентах, которым были оказаны услуги. О каждом абоненте содержится следующая информация: район, в котором проживает абонент, адрес (улица и дом) и фамилия с инициалами. Таблица «Услуги» содержит записи об оказываемых оператором услугах - наименование и цена оказанной услуги. Таблица «Оказанные услуги» содержит информацию о том когда (поле дата), кому (ID клиента) и какая услуга (ID услуги) была оказана. На рисунке приведена схема базы данных. Используя информацию из приведённой базы данных, суммарную выручку оператора за оказанные услуги в Центральном и Речном районе в период с 4 по 9 августа (включительно). В ответе запишите только число. (Е. Джобс) В файле 3-4.xls приведён фрагмент базы данных «Родственники» о родственных отношениях между людьми. База данных состоит из двух таблиц. Таблица «Люди» содержит записи о людях - Фамилия ИО, пол («м» или «ж») и город рождения. Таблица «Родственные связи» содержит информацию о родительских связях - ID родителя из таблицы «Люди» и ID ребенка и той же таблицы. На рисунке приведена схема базы данных.  Используя информацию из приведённой базы данных, у скольких мужчин есть хотя бы один ребенок. В ответе запишите только число. (Е. Джобс) В файле 3-4.xls приведён фрагмент базы данных «Родственники» о родственных отношениях между людьми. База данных состоит из двух таблиц. Таблица «Люди» содержит записи о людях - Фамилия ИО, пол («м» или «ж») и город рождения. Таблица «Родственные связи» содержит информацию о родительских связях - ID родителя из таблицы «Люди» и ID ребенка и той же таблицы. На рисунке приведена схема базы данных. Используя информацию из приведённой базы данных, укажите максимальное количество детей у одного родителя. В ответе запишите только число. (Е. Джобс) В файле 3-4.xls приведён фрагмент базы данных «Родственники» о родственных отношениях между людьми. База данных состоит из двух таблиц. Таблица «Люди» содержит записи о людях - Фамилия ИО, пол («м» или «ж») и город рождения. Таблица «Родственные связи» содержит информацию о родительских связях - ID родителя из таблицы «Люди» и ID ребенка и той же таблицы. На рисунке приведена схема базы данных. Используя информацию из приведённой базы данных, укажите количество людей, которые родились в том же городе, что и хотя бы один из их родителей. В ответе запишите только число. (Е. Джобс) В файле 3-4.xls приведён фрагмент базы данных «Родственники» о родственных отношениях между людьми. База данных состоит из двух таблиц. Таблица «Люди» содержит записи о людях - Фамилия ИО, пол («м» или «ж») и город рождения. Таблица «Родственные связи» содержит информацию о родительских связях - ID родителя из таблицы «Люди» и ID ребенка и той же таблицы. На рисунке приведена схема базы данных. Используя информацию из приведённой базы данных, укажите количество людей, родители которых родились в одном городе. В ответе запишите только число. (Е. Джобс) В файле 3-4.xls приведён фрагмент базы данных «Родственники» о родственных отношениях между людьми. База данных состоит из двух таблиц. Таблица «Люди» содержит записи о людях - Фамилия ИО, пол («м» или «ж») и город рождения. Таблица «Родственные связи» содержит информацию о родительских связях - ID родителя из таблицы «Люди» и ID ребенка и той же таблицы. На рисунке приведена схема базы данных. Используя информацию из приведённой базы данных, укажите количество людей, у которых в базе данных указан только один родитель. В ответе запишите только число. (А. Кабанов) В следующей серии задач используется файл 3-5.xls, в котором приведён фрагмент базы фрагмент базы данных «Аудиотека». База данных состоит из четырёх таблиц. Таблица «Альбомы» содержит записи о записанных альбомах, а также информацию о исполнителях. Таблица «Артисты» содержит записи о названии исполнителей. Таблица «Треки» содержит записи о записанных композициях, а также информацию о альбомах и жанрах. Поле Длительность содержит длительность аудиозаписи в миллисекундах, поле Размер содержит размер аудиозаписи в байтах, а поле Стоимость содержит стоимость аудиозаписи в рублях. Таблица «Жанры» содержит данные о названии жанров. На рисунке приведена схема указанной базы данных.  (А. Кабанов) Используя информацию из базы данных в файле 3-5.xls, определите, сколько минут длятся все песни группы Guns N' Roses. В ответе укажите целую часть получившегося числа. (А. Кабанов) Используя информацию из базы данных в файле 3-5.xls, определите, сколько рублей стоят все песни группы U2. (А. Кабанов) Используя информацию из базы данных в файле 3-5.xls, определите, сколько Мегабайт занимают все песни группы The Rolling Stones В ответе укажите целую часть получившегося числа. (А. Кабанов) Используя информацию из базы данных в файле 3-5.xls, определите, сколько минут длится самый продолжительный альбом группы Nirvana. В ответе укажите целую часть получившегося числа. (А. Кабанов) Используя информацию из базы данных в файле 3-5.xls, определите, сколько рублей стоит самый дешёвый альбом группы Red Hot Chili Peppers. Стоимость альбома оценивается как сумма стоимости входящих в него треков. (А. Кабанов) Используя информацию из базы данных в файле 3-5.xls, определите, сколько Мегабайт занимает наибольший по размеру альбом группы Queen. В ответе укажите целую часть получившегося числа. (А. Кабанов) Используя информацию из базы данных в файле 3-5.xls, найдите исполнителя с наибольшей суммарной длительностью песен. В ответе укажите целую часть длительности его песен в минутах. (А. Кабанов) Используя информацию из базы данных в файле 3-5.xls, найдите исполнителя с наибольшим суммарным размером песен. В ответе укажите целую часть размера его песен в Мегабайтах. (А. Кабанов) Используя информацию из базы данных в файле 3-5.xls, найдите исполнителя с наибольшей суммарной стоимостью. В ответе укажите суммарную стоимость его песен в рублях. (А. Кабанов) Используя информацию из базы данных в файле 3-5.xls, найдите исполнителя в жанре Rock с наименьшей суммарной длительностью песен в этом жанре. В ответе укажите целую часть длительности его песен в секундах. (А. Кабанов) Используя информацию из базы данных в файле 3-5.xls, найдите исполнителя в жанре Metal с наименьшим суммарным размером песен в этом жанре. В ответе укажите целую часть размера его песен в Мегабайтах. (А. Кабанов) Используя информацию из базы данных в файле 3-5.xls, найдите исполнителя в жанре Blues с наименьшей суммарной стоимостью песен в этом жанре. В ответе укажите суммарную стоимость его песен в рублях. http://kpolyakov.spb.ru |