Написать запросы к разработанной в ходе выполнения лабораторной работы №1базе данных, используя простые и коррелированные подзапросы. Результаты запросов должны выводиться со смысловыми названиями столбцов и сопровождаться при необходимости текстовыми комментариями.
| №
| Название разрабатываемой БД и запросы.
|
|
| Торговля
|
| 1. Получить список изделий, которые в данный момент времени отсутствуют на складе.
2. Получить общую стоимость и количество проданных за последние три месяца изделий всех имеющихся в прайс-листе наименований.
3. Получить данные об изделиях, проданных по безналичному расчету. Вывести количество единиц каждого наименования.
4. Получить количество проданных единиц и общую сумму выручки от их продажи для данного изделия по каждому из производителей. Результат упорядочить по убыванию суммы выручки.
5. Получить счета, оплаченные по наличному расчету с начала года. Список упорядочить по убыванию суммарной стоимости изделий, фигурирующих в счете.
|
|
| Коммунальные платежи.
|
| 1. Получить списки клиентов, не оплативших на данный момент времени более 1 услуги. По каждому клиенту выдать общую сумму задолженности.
2. Получить перечень предоставляемых коммунальных услуг с указанием тарифов, среднемесячных объемов потребления.
3. Получить списки клиентов, у которых потребление данного вида коммунальных услуг за последний месяц превышает среднее значение.
4. Получить ведомость оплаты коммунальных услуг за последний месяц клиентами, проживающими по указанному адресу. Вывести итоговые суммы оплаты по каждому клиенту и по каждому виду услуг.
5. Получить данные о задолженности по каждому виду коммунальных услуг за каждый месяц с начала года.
|
|
| Услуги.
|
| 1. По каждому типу работ получить его долю в процентном выражении от общего числа зарегистрированных заказов.
2. По каждому мастеру получить общее количество выполненных заказов и суммарную стоимость выполненных при этом работ. Данные упорядочить по убыванию суммарной стоимости.
3. Получить список первых 10 клиентов от 20 до 45 лет с максимальным количеством заказов. Для каждого из них вывести также суммарную стоимость заказов.
4. Получить список клиентов, обращавшихся в агентство с начала текущего года, упорядоченный по убыванию количества различных типов выполненных работ.
5. Получить список клиентов, суммарная стоимость заказов которых превышает среднюю стоимость заказа по агентству.
|
|
| Начисление зарплаты.
|
| 1. Получить ведомость выплаты зарплаты за последний месяц, в которой для каждого работника указать: фамилию и инициалы, должность, начисленную сумму, сумму удержаний, сумму к выдаче. Данные упорядочить по должности и фамилии сотрудников.
2. Для каждой категории работников, указанных в штатном расписании, получить суммарное количество пропущенных рабочих дней без учета отпускных, а также среднемесячный размер выплаченных премий. Результат упорядочить по убыванию количества пропущенных рабочих дней
3. Для каждой категории работников, указанных в штатном расписании получить количество работников со средним, средним специальным и высшим образованием.
4. Получить упорядоченный по фамилиям список работников, у которых зарплата за последние три месяца превышала среднемесячную зарплату по предприятию.
5. Получить в процентном выражении долю заработной платы работников со средним, средним специальным и высшим образованием от общего фонда заработной платы.
|
|
| Поставки.
|
| 1. Получить список поставщиков данного типа продукции, упорядоченный в порядке убывания оптовой цены этой продукции.
2. Получить список договоров, заключенных за последние три месяца, с истекшими сроками поставки. Данные вывести в порядке убывания задержки поставки.
3. Получить среднемесячное количество договоров, заключаемых по каждому виду продукции.
4. По каждому типу продукции получить долю договоров, заключаемых на поставку продукции с наивысшим показателем качества, от общего количества договоров на поставку продукции данного типа.
5. Получить рейтинг поставщиков в зависимости от суммарного объема поставок для каждого типа продукции.
|
|
| Билетная касса.
|
| 1. Получить информацию на данный момент времени обо всех рейсах, выполняемых в указанный пункт назначения, дата и время прибытия которых в этот пункт находятся в указанном диапазоне значений. По каждому рейсу вывести также общее количество мест и количество проданных билетов.
2. Для каждого рейса получить среднее число зарегистрированных пассажиров за все время эксплуатации. Данные вывести в порядке убывания значений.
3. Для каждого из типов транспортных средств получить его долю в общем пассажиропотоке и суммарное количество рейсов за неделю.
4. Для каждого из пунктов назначения, фигурирующих в расписании, получить их долю в общем пассажиропотоке. Упорядочить по убыванию значений.
5. По каждой категории билетов получить суммарную стоимость проданных билетов и их долю от общей суммы выручки, полученной от продажи билетов.
|
|
| Отдел кадров.
|
| 1. Получить список сотрудников, занимающих указанную должность, упорядоченный по времени принятия на работу и ФИО сотрудников.
2. Получить упорядоченный по датам список сотрудников, прошедших повышение квалификации. Для каждого сотрудника вывести номер специальности и номер свидетельства.
3. По каждому типу взыскания получить общее количество наложенных взысканий для всех типов должностей.
4. По каждому типу должности получить общее количество поощрений за прошедший год. Данные упорядочить по убыванию значений.
5. Получить рейтинг сотрудников по количеству поощрений, взысканий и прохождения курсов повышения квалификации. Данные вывести в порядке убывания рейтинга.
|
|
| Отель.
|
| 1. Получить список клиентов, проживавших в отеле в указанный период. Список должен быть упорядочен по длительности проживания.
2. Получить список клиентов, проживающих в данный момент в отеле. Строки в списке должны быть сгруппированы по категориям номеров и упорядочены по дате регистрации.
3. Получить данные о дополнительных услугах, оказанных с начала текущего года, по категориям номеров. Размеры дополнительных услуг вывести в денежном и процентном выражении в отношении к общей сумме оплаты для данной категории.
4. Получить данные о количестве клиентов, проживавших в отеле от 1 до 3, от 3 до 10 и свыше 10 суток, в процентном выражении от общего количества клиентов.
5. Получить данные о количестве клиентов, являющихся иностранными гражданами, проживавших в отеле помесячно за прошедший календарный год.
|
|
| Производство.
|
| 1. Получить список выпускаемой на предприятии продукции по годам, упорядоченный по себестоимости.
2. Получить данные о планируемых на год объемах расхода сырья по типам выпускаемой продукции. Данные упорядочить по величине потерь сырья при изготовлении.
3. По каждому типу продукции получить данные об объемах выпуска с начала текущего года в процентном выражении от плана, а также данные о количестве бракованной продукции в процентном выражении от общего объема продукции данного вида.
4. Получить объемы поставок готовой продукции и полученную прибыль в денежном выражении для каждого вида продукции за прошедший год.
5. Получить долю экспорта в общем объеме произведенной продукции по всем типам продукции.
|
|
| Банк.
|
| 1. Получить списки клиентов банка, юридических и физических лиц, упорядоченные по величине остатков на счетах. Каждый из списков должен начинаться соответствующим заголовком – «Юридические лица» или «Физические лица». Вывести наименование для юридических лиц или фамилию и инициалы для физических лиц.
2. Получить список юридических лиц, упорядоченный по суммарным объемам платежей, произведенных с начала текущего года.
3. По каждому виду платных услуг, оказываемых банком физическим лицам, вывести общую сумму, полученную банком с начала текущего года. Получить также итоговое значение в целом.
4. Получить данные о кредитах, выданных физическим лицам. Значения вывести по каждому типу кредитов в процентном выражении от общей суммы выданных кредитов.
5. По каждому юридическому лицу получить среднюю сумму выданных кредитов. Данные вывести в порядке убывания значений.
|
|
| Экспорт/импорт.
|
| 1. Получить данные о трех предприятиях с наибольшим рейтингом за последние 5 балансовых лет.
2. По каждому коду ТНВД получить суммарные объемы экспорта/импорта за прошедший год.
3. Получить средний объем экспорта/импорта по каждой ГВ. Результат упорядочить по убыванию значений.
4. Получить список предприятий, у которых чистая прибыль за прошедший балансовый год превысила 50% валового дохода.
5. По каждому коду ТНВД получить списки 3 предприятий с наибольшими рейтингами, которые осуществляли экспортно/импортные операции за прошедший балансовый год.
|
|
| Проект.
|
| 1. Получить данные об исполнителях, для которых показатели объема выполненных работ и количества использованных материалов не соответствуют показателям ближайшей по дате прошедшей вехи.
2. По каждому типу работ получить данные о текущем состоянии выполненного объема в процентном выражении от общего запланированного объема и количества использованных материалов в процентном выражении от общей потребности.
3. Получить список «критических работ», т.е. работ, которые не были завершены к указанной самой поздней дате окончания. Данные вывести по убыванию величины задержки завершения.
4. Получить общую продолжительность проекта в днях от момента начала первой работы (дата начала и самая ранняя дата начала совпадают) до момента завершения последней для нее (дата окончания и самая поздняя дата окончания совпадают).
5. По каждому типу работ получить список исполнителей, упорядоченный по убыванию запланированной части общего объема данной работы.
|
Требования к отчету:
Отчет по лабораторной работе должен содержать SQL-скрипты запросов контрольного задания и результаты их выполнения.
Контрольные вопросы:
1. Объясните назначение операторов BETWEEN, IN, LIKE.
2. Что такое агрегирующие функции и как они используются?
3. Как называются функции SUM, AVG, MAX, MIN, COUNT? Почему?
4. Почему агрегирующие функции не могут применяться одновременно с именами полей в отсутствие модификатора GROUP BY?
5. Что будет выдано при использовании в списке выбора COUNT(*), COUNT(DISTINCT <Имя>)? Почему DISTINCT нельзя применять совместно с COUNT(*)?
6. Каково назначение предложений WHERE и HAVING?
7. Как можно вывести несколько данных разных типов в одной строке символов?
8. Что такое декартово произведение двух таблиц? Как его получить?
9. Что такое эквисоединение двух таблиц?Как его получить?
10. Что такое естественное соединение двух таблиц? Как его получить?
11. Что такое композиция двух таблиц? Как его получить?
12. Для чего используется объединение таблицы со своей копией?
13. Почему нельзя использовать операторы отношения для обработки результата простого вложенного подзапроса, если он возвращает более одного значения?
14. Что такое коррелированный вложенный подзапрос и как он обрабатывается?
15. В чем заключается сходство и отличие подзапросов с ANY и ALL от подзапросов с EXISTS?
16. Какие требования необходимо выполнить для объединения двух запросов?
17. Что такое селекция и проекция таблицы?
18. Как средствами SQL выполнить объединение, пересечение и разность двух таблиц?
19. Для чего предназначена конструкция WITH?
20. Каковы особенности применения предложения UNION?
