27011

Построение запросов с использованием обобщающих функций

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

Информатика, кибернетика и программирование

Таблица 3: onum – номер заявки amt – сумма заявкиodate – дата cnum – номер покупателя snun – номер продавца Чтобы найти сумму на которую сделаны заявки: SELECT SUMamt FROM Orders; Подсчитать число продавцов имеющих заказы: SELECT COUNTDISTINCT snum FROM Orders; Результат: 5. Подсчитать количество читателей имеющих отчество Иванович Подсчитать количество книг которое числится за каждым читателем Отыскать читателя который взял максимальное число книг. Подсчитать общее число экземпляров книг издательства Мир Подсчитать...

Русский

2013-08-19

86 KB

39 чел.

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

Построение запросов с использованием обобщающих функций.

Цель работы:

приобретение практических навыков по созданию  запросов на языке SQL с использованием  конструкции GROUP BY, ORDER BY и агрегатных функций.

Краткие теоретические сведения.

Для обобщения данных в SQL предусмотрены следующие функции агрегирования:

COUNT – определяет количество значений, выбранных посредством  запроса и не являющимися NULL.

SUM – вычисляет арифметическую сумму всех выбранных значений одного поля.

AVG – вычисляет среднее значение для выбранных значений данного поля.

MAX – наибольшее из выбранных значений данного поля

MIN – наименьшее из выбранных значений данного поля.

Пример реляционной БД. Состоит из трех таблиц.

Таблица 1. Salespeople (продавцы)

snum

sname

city

comm.

1001

Peel

London

.12

1002

Serres

Sanhose

.15

1004

Motica

London

.11

1007

Rifkin

Barselona

.15

1003

Axelrod

New York

.10

Таблица 2. Customers(Покупатели)

cnum

cname

city

rating

snum

2001

Hoffman

London

100

1001

2002

Giovamn

Rome

200

1003

2003

Lia

Sanhose

200

1002

2004

Grass

Berlin

300

1002

2006

Cleimes

London

100

1001

2008

Cisneros

Sanhose

300

1007

2007

Rersira

Rome

100

1004

 Таблица 3. Ordes (заявки).

onum

amt

odate

cnum

snum

3001

18.69

10.03.1990

2008

1007

3003

767.19

10.03.1990

2001

1001

3002

1900.10

10.03.1990

2007

1004

3005

5160.45

10.03.1990

2003

1002

3006

1098.16

10.03.1990

2008

1007

3009

1713.23

10.04.1990

2002

1003

3007

75.75

10.04.1990

2004

1002

3008

4723.00

10.05.1990

2006

1001

3010

1309.95

10.06.1990

2004

1002

3011

9891.88

10.06.1990

2006

1001

Таблица 1: snum – уникальный номер продавца, sname – имя продавца, city – место продаж, comm – вознаграждение.

Таблица 2: cnum – номер покупателя, rating – определяет уровень предпочтения покупателя.

Таблица 3: onum – номер заявки, amt – сумма заявки,odate – дата, cnum – номер покупателя, snun – номер продавца

Чтобы найти сумму, на которую сделаны заявки:

SELECT SUM(amt) FROM Orders;

Подсчитать число продавцов, имеющих заказы:

SELECT COUNT(DISTINCT snum) FROM Orders;

Результат: 5.

Для подсчета общего числа строк, например, в таблице покупатели:

SELECT COUNT(*) FROM Customers;

Предложение GROUP BY команды SELECT позволяет комбинировать поля и агрегативные функции в одном запросе. Например, чтобы найти наибольший заказ из тех, что получил каждый из продавцов:

SELECT snum, MAX(amt) FROM Orders

GROUP BY snum;

Результат:  1098.16 для 1007

 9891.98 для 1001

 1900.10 для 1004

 5160.45 для 1002

 1713.23 для 1003.

Можно этот запрос расширить и просматривать наибольший заказ на каждую дату:

SELECT snum, MAX(amt), odate

FROM Orders

GROUP BY snum, odate;

Можно в дополнение к предыдущему запросу включить предложение HAVING, который будет выдавать заявки превышающие 3000.00:

HAVING MAX(amt) > 3000.00;

В предложении WHERE использовать агрегированные функции нельзя.

Задание по работе:

Для созданной в лабораторной работе№1 базы данных построить следующие запросы:

Вариант1.

  1.  Подсчитать количество читателей, имеющих отчество «Иванович»
  2.  Подсчитать количество книг, которое числится за каждым читателем
  3.  Отыскать читателя, который взял максимальное число книг.
  4.  Подсчитать общее число экземпляров книг издательства «Мир»
  5.  Подсчитать сколько книг в среднем берут читатели.
  6.  Подсчитать общее число книг каждого издательства, изданные в период 2005-2010г.г.
  7.  Отыскать какого года издания меньше всего книг в библиотеке.

Вариант2.

  1.  Подсчитать сколько карточек имеет каждый владелец
  2.  Подсчитать сколько карточек каждого типа выдано
  3.  Отыскать у кого наибольшая сумма на счету
  4.  Сколько карточек с истекшим сроком действия?
  5.  Подсчитать какую сумму в среднем снимают владельцы с карточек
  6.  Определить какого типа карточек выдано меньше всего
  7.  Определить сколько карточек выдано в текущем году?

Вариант3.

  1.  Отыскать общее число сотрудников в возрасте от 18 до 25 лет.
  2.  Подсчитать число сотрудников в каждом подразделении
  3.  Определить среднюю заработную плату сотрудников отделения АСУ.
  4.  Отыскать сотрудника с максимальной заработной платой
  5.  Какая должность имеет минимальный оклад?
  6.  Подсчитать сколько сотрудников в каждом подразделении работает в должности «инженер»
  7.  Подсчитать общую сумму заработной платы для каждого подразделения.

Вариант 4.

  1.  Подсчитать число студентов в каждой группе.
  2.  Определить на каком факультете обучается максимальное число студентов.
  3.  Определить среднее число студентов в группах факультета ФИТР.
  4.  Подсчитать число фамилий, которые начинаются на «Иван»
  5.  Найти фамилию старосты группы , в которой меньше всего студентов.
  6.  Определить сколько студентов с именем Денис обучается  на АТФ,
  7.  Определить среднюю численность групп на каждом факультете.

Вариант5.

  1.  Определить средний стаж водителей.
  2.  Отыскать сколько автомобилей требуют ремонта
  3.  Отыскать общий километраж поездок каждого водителя
  4.  Подсчитать сколько водителей было отправлено в поездки в текущем месяце.
  5.  Отыскать водителя, который был в поездке с наибольшим километражом.
  6.  Подсчитать общее число поездок для каждого водителя.
  7.  Определить сколько машин ежедневно отправляют в поездки.

Вариант 6.

  1.  Подсчитать число пропусков в каждой группе.
  2.  Подсчитать количество дисциплин, пропущенных студентом Ивановым.
  3.  Наити фамилию студента, имеющего максимальное число пропусков.
  4.  В какой группе больше всего пропусков.
  5.  По какой дисциплине нет пропусков.
  6.  Подсчитать среднее число пропусков  в каждой группе.
  7.  Составить сведения о пропусках каждого студента в заданной группе.

Вариант 7.

  1.  Подсчитать сколько номеров каждого типа в гостинице «Беларусь»
  2.  Отыскать гостиницу, в которой минимальная стоимость одноместного номера
  3.  Составить список лиц, пребывавших в гостинице «Минск» более двух раз в

текущем году

  1.  Подсчитать сколько гостей проживает  в каждой гостинице в настоящее время
  2.  Отыскать гостиницу, в которой есть более 10 свободных мест

6.Какая средняя цена одноместных номеров

7. Какая гостиница имеет наибольшее число мест

Вариант 8.

1.Определить число моделей каждого типа для каждого производителя

2.Отыскать параметры РС по минимальной цене.

3. Какая средняя цена принтеров для заданного производителя.

4.Найти число ПК-блокнотов с заданными характеристиками для заданного производителя.

5.Подсчитать сколько компьютеров и блокнотов имеют одинаковую цену.

6.Какой производитель выпускает только принтеры

7.  Подсчитать сколько моделей каждого типа выпускает каждый производитель

Вариант 9.

  1.  На какой должности наибольщая зарплата
  2.  Каков штат в каждой организации
  3.  Какая в каждом городе минимальная зарплата, какая максимальная
  4.  Вывести все фирмы, средний возраст сотрудников в которых больше 35.
  5.  Вывести страны в порядке убывания средней зарплаты на душу населения.
  6.  Вывести должности в порядке убывания количества сотрудников на этой должности.
  7.  Вывести города и клиентов этих городов и отсортировать клиентов каждого города по убыванию их заработной платы и возрастанию их возраста.

Вариант 10.

  1.  Подсчитать сколько горелок имеет каждый клиент, результат отсортировать в порядке возрастания числа горелок.
  2.  Сколько горелок каждого типа не выдержали гарантийный срок
  3.  Какая средняя стоимость горелок  заданного типа
  4.  Определить в среднем сколько горелок регистрируется ежедневно
  5.  Составить список горелок, стоимость ремонта которых выше средней
  6.  Подсчитать общее число зарегистрированных горелок  и сколько горелок не были в ремонте.
  7.  Составить список клиентов, имеющих более двух горелок.

Вариант 11.

  1.  Подсчитать общую сумму выполненных заказов для каждого продавца
  2.  Отыскать покупателя, сделавшего заказ на наибольшую сумму.
  3.  Составить список покупателей , сделавших заказы на сумму выше средней.
  4.  Определить средний размер заработной платы для продавцов старше 35 лет
  5.  Сколько заказов выполнил каждый продавец.
  6.  Составить список покупателей , сделавших более 5 заказов. Результат отсортировать в алфавитном порядке фамилий покупателей
  7.  Общая сумма заказов для заданного покупателя.

Вариант 12.

  1.  Какой поставщик имеет наибольший рейтинг?
  2.  Определить общее количество поставки каждой детали
  3.  Определить сколько деталей поставлялось для заданного изделия
  4.  Определить общий вес деталей для каждого изделия
  5.  Определить общее количество поставщиков в каждом городе
  6.  Составить список поставщиков, имеющих рейтинг выше среднего
  7.  Какая деталь заказывалась чаще всего

Вариант 13.

1.Определить общее количество поставки каждой детали.

2.Отыскать детали, имеющие цену ниже средней

3.Отыскать потребителей , сделавщих более трех заказов.

4. Сколько поставок выполнил каждый поставщик.

5. Сколько поставщиков в каждом городе.

6. Подсчитать общее число потребителей из городов, в которых нет поставщиков.

7. Какая деталь заказывалась чаще всего, какая в наибольшем количестве.

Вариант 14.

1.Подсчитать число сотрудников в каждом отделе.

2. Составить штатное расписание для каждого отдела: номер отдела, название, должность, число сотрудников.

3.Отыскать сотрудников , за которыми закреплено менее 10 объектов.

4. Подсчитать число коттеджей в городе Минске.

5. Отыскать однокомнатную квартиру, арендная плата за которую ниже средней. Вывести Адрес, номер владельца, арендную плату.

6. Подсчитать число мужчин в каждом отделе в возрасте от 18 до 25 лет.

7. Определить объект с максимальной арендной платой.

Вариант 15.

  1.  Подсчитать сколько арендаторов закреплено за каждым отделом.
  2.  Подсчитать число арендаторов по каждому типу объектов.
  3.  Составить список арендаторов, которые могут арендовать объекты с арендной платой выше средней.
  4.  Сколько арендаторов закреплено за каждым отделением. Результат вывести в порядке убывания числа объектов.
  5.  Составить список сотрудников с указанием их должности, возраста, имеющих зарплату выше средней. Результат вывести в порядке возрастания зарплаты.
  6.  Сколько отделений находится в каждом городе.
  7.  Определить среднюю зарплату для каждой должности. Результат вывести в алфавитном порядке должностей.

Вариант 16.

  1.  Вычислить число водителей, направленных по крайней мере в одну командировку.
  2.  Подсчитать число водителей в каждом отделе.
  3.  Отыскать водителя, который ездил в командировку наибольшей протяженности.
  4.  Составить список водителей, имеющих зарплату выше средней. Результат вывести в алфавитном порядке фамилий водителей.
  5.  Подсчитать сколько дней в командировках был каждый водитель.
  6.  Подсчитать общую сумму  заработной платы для каждого отдела.
  7.  Подсчитать сколько водителей в каждом отделе не были в командировках в течении текущего месяца.

Контрольные вопросы.

  1.  В чем отличие конструкций ORDER BY и GROUP BY.
  2.  Как можно форматировать результаты запросов?
  3.  В каких случаях используется конструкция HAVING?

.


 

А также другие работы, которые могут Вас заинтересовать

67826. УНІФІКОВАНІ ВУЗЛИ ПРОМИСЛОВИХ РОБОТІВ (ПРОДОВЖЕННЯ) 1.04 MB
  Мальтійські механізми використовують для повороту ПР, коли необхідно здійснити преривистий рух робочого органу, тобто рух в одному напрямку з періодичними зупинками. Вони отримали розповсюдження в зв’язку з їх конструктивною простотою, простотою виготовлення і експлуатації.
67827. ПРАВОВЕ РЕГУЛЮВАННЯ ВИКОРИСТАННЯ ТА ОХОРОНИ РОСЛИННОГО СВІТУ 117.5 KB
  Рослинний світ становить сукупність усіх видів рослин, а також грибів та утворених ними угруповань на певних території. Це правове визначення рослинного світу як об’єкта навколишнього природного середовища міститься в ст. З Закону України «Про рослинний світ».
67828. ЗАХОПЛЮЮЧІ ПРИСТРОЇ (ЗП) ПРОМИСЛОВИХ РОБОТІВ 1 MB
  Технологічне призначення: захопюючі і утримуючі ЗП призначені для зхахоплення і утримування об‘єктів за допомогою гачків петель вилок лопаток губок пальців голок кліщів еластичних камер струменів повітря магнімного поля вакууму електростатичного притягання адгезії липучих накладок та інших засобів...
67829. ПРАВОВЕ РЕГУЛЮВАННЯ ВИКОРИСТАННЯ ТА ОХОРОНИ ЛІСІВ 101.5 KB
  Ліс є невід’ємною та незамінною частиною світової екосистеми. Значення лісів для навколишнього природного середовища проявляється в корисних властивостях лісів. Корисними властивостями лісів є їх здатність зменшувати вплив негативних природних явищ, захищати ґрунти від ерозії, регулювати стік води...
67830. СИСТЕМА ТРАНСПОРТНИХ І НАКОПИЧУВАЛЬНИХ ЗАСОБІВ РТС. НАВАНТАЖУВАЛЬНО-РОЗВАНТАЖУВАЛЬНІ ЗАСОБИ РТС 1.82 MB
  В загальному випадку транспортна система складається з складів 12 заготівок, оброблених деталей і зібраних виробів, складів 24 напівфабрикатів, інструментів і технологічного оснащення, а також транспортних засобів їхньої доставки і завантажувально-розвантажувальних пристроїв, що забезпечують...
67831. ПРАВОВЕ РЕГУЛЮВАННЯ ВИКОРИСТАННЯ ТВАРИННОГО СВІТУ 86.5 KB
  Відносини у галузі охорони, використання і відтворення тваринного світу, об’єкти якого перебувають у стані природної волі, у напіввільних умовах чи в неволі, на суші, у воді, ґрунті та повітрі, постійно чи тимчасово населяють територію України або належать до природних багатств її континентального...
67832. МІЖНАРОДНО-ПРАВОВЕ РЕГУЛЮВАННЯ ВИКОРИСТАННЯ ПРИРОДНИХ РЕСУРСІВ 125 KB
  Планета Земля, яка є нашим спільним домом, для сучасної людини перестала бути безмежною, в зв’язку з чим, всі природні та інші процеси, які відбуваються в сучасному світі, стали взаємозалежними та взаємопов’язаними. Так, наприклад, пестициди (ДДТ), що використовувалися...
67833. ТРАНСПОРТНІ ТА СКЛАДСЬКІ ЗАСОБИ РТС 418.5 KB
  Конвейєром називають машину для безперервного транспортування виробів. Відмітною особливістю багатьох конструкцій конвейєрів, разом з виконанням функцій по переміщенню заготівок, є можливість утворення невеликих міжопераційних заділів, що забезпечують незалежну роботу складних верстатів в складі РТС.
67834. ІНФОРМАЦІЙНО-ВИМІРЮВАЛЬНІ ПРИСТРОІ СИСТЕМ КЕРУВАННЯ РТС. СИЛОМОМЕНТНІ СИСТЕМИ НАДАННЯ ЧУТЛИВОСТІ РТС 208 KB
  Якщо при великосерійному виробництві з невеликими змінами продукції що випускається сумарні витрати на створення жорсткопрограмованого робототехнологічного комплексу складаються з трьох приблизно рівних частин одна з яких вартість сучасного високоточного промислового...