27011

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

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

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

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

Русский

2013-08-19

86 KB

41 чел.

Лабораторная работа №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?

.


 

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

53737. Правописание местоимений с предлогами 48.5 KB
  Задачи: 1 формировать умение правильно употреблять и писать местоимения 1 2 3 лица навык раздельного написания местоимений с предлогами; 2 отработать навык определения падежа личных местоимений единственного и множественного числа; воспитывать интерес к изучению русского языка. 3 вопроса Перечисли все падежи русского языка Перечисли личные местоимения множественного числа Мы вы они Вместо какой части речи употребляются имена существительные. Итак ребята тема нашего сегодняшнего урока: Личные местоимения с предлогами. Знаете ли...
53738. Распознавание частей речи. Местоимение 58.5 KB
  Задачи: закрепление знаний об основных частях речи и развитие умения определять часть речи слов по вопросу; формирование общего понятия о местоимении как части речи; совершенствование навыка разбора предложения по членам; развитие умения правильно употреблять разные части речи в предложении. К какому новому большому разделу вы перешли на прошлом уроке...
53739. Распознавание рода у имен существительных множественного числа 58 KB
  Цель урока: Научить учащихся определять род у имен существительных стоящих во множественном числе. Задачи: совершенствование навыка изменения имен существительных по числам; закрепление знаний о роде имен существительных и развитие умения определять род имени существительного с помощью опорных слов местоимений; формирование и закрепление умения...
53740. Часть речи – прилагательное 34 KB
  Цель: Ознакомить с новой частью речи именем прилагательным его ролью в речи. Ознакомить с существенными признаками имени прилагательного как части речи. Что вы для этого сделали изменили окончания О чем это говорит Это говорит о том что эти слова зависят от имени прилагательного Знаете ли вы к какой части речи относятся слова 2 столбика Значит какую цель мы ставим перед собой на этом уроке узнать о новой части речи.
53741. Правописание гласных против шипящих 41.5 KB
  Что же нам нужно сделать Щука в озере жила Червячка с крючка сняла Наварила щука щей Пригласила всех ершей Говорили всем ерши: Щи у Щуки хороши Давайте запишем наше упражнение в тетрадь. Запись в тетрадь. Нам нужно вставить пропущенные буквы и записать его в тетрадь. Разбор с учителем стихотворения запись его в тетрадь.
53742. Подготовка к сочинению-рассуждению 36 KB
  Цели урока: познакомить с работами писателей посвящённых русскому языку; развивать речевые умения и навыки; прививать любовь к родному краю к малой и большой Родине. В гостях у нас учителя русского языка и литературы. Мы будем говорить о русском языке.
53743. Обобщающий урок по теме “Природа”. Урок - КВН 51 KB
  Природа Да все что нас окружает это природа. Природа прекрасна в любое время года. Весной мы можем наблюдать как вся природа просыпается от зимней спячки.
53744. Роль имени прилагательного в русском языке 88.5 KB
  Цель: Обучающая учить детей правильно использовать имена прилагательные в речи находить прилагательные и по существенным признакам определять предмет; Развивающая развивать критическое мышление умение ставить проблемные вопросы выдвигать гипотезы анализировать и сравнивать обобщать полученные данные и делать выводы; развивать устную и письменную речь учащихся; Воспитывающая создать условия для формирования познавательного интереса к русскому языку воспитания культуры общения в группе со сверстниками; воспитывать...
53745. Столетняя война 72 KB
  Учитель приветствует учеников и организует повторение ими материала прошлого урока. Учитель проводит фронтальный опрос класса по теме прошлого урока. которая подтвердила права и вольности церкви городов рыцарей баронов свободного населения Что такое совет двадцати пяти совет баронов который следил за исполнением Великой хартии вольностей; в случае нарушения условий этой хартии бароны могли начать военные действия против короля всяческими способами добиваться исправления нарушений Затем учитель заслушивает письменные ответы...