27011

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

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

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

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

Русский

2013-08-19

86 KB

43 чел.

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

.


 

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

31511. Продуктивність праці 116 KB
  Продуктивність праці Зміст учбового матеріалу: Сутність продуктивної праці та продуктивності праці. Показники продуктивності праці та методи її вимірювання Фактори впливу на зростання продуктивності праці та їх класифікація Резервів зростання продуктивності праці їх класифікація та оцінка. Сутність продуктивності та продуктивності праці Згідно з рекомендаціями Міжнародної організації праці МОП розрізняють поняття продуктивність і продуктивність праці. Продуктивність це ефективність використання ресурсів праці капіталу...
31512. Політика доходів та сутність заробітної плати 232 KB
  Робоча сила найманих працівників на ринку праці є товаром який має вартість. На вартість робочої сили впливають результати праці власника робочої сили. Вартість робочої сили формується на ринку через порівняння результативності корисності праці із затратами на відтворення робочої сили. Вона встановлюється на рівні який узгоджує граничну продуктивність праці тобто цінність послуг праці для покупцяпідприємця з витратами які потрібні для відтворення робочої сили.
31513. Тарифна система оплати праці та її складові 224 KB
  Тарифна система оплати праці та її складові Зміст учбового матеріалу: Тарифна система та її призначення. Довідник кваліфікаційних характеристик професій працівників. Тарифні ставки працівників. Тарифна система та її призначення Тарифне нормування оплати праці це складова організації заробітної плати що здійснюється за допомогою тарифної системи і являє собою сукупність фіксованих норм оплати за роботу в межах норм праці трудових обовязків а також норм оплати праці за роботу понад норму праці.
31514. Оплата праці та її організація в умовах трансформації соціально-трудових відносин 192.5 KB
  Оплата праці та її організація в умовах трансформації соціальнотрудових відносин Зміст учбового матеріалу: Системи оплати праці: сутність ознаки взаємозвязок та взаємозалежність. Відрядна форма оплати праці: сутність та види. Почасова форма оплати праці: сутність та види. Безтарифна система оплати праці.
31515. Аналіз і планування трудових показників 131.5 KB
  Аналіз і планування трудових показників Зміст учбового матеріалу: Планування продуктивності праці: методичні основи Розроблення плану продуктивності праці Планування й аналіз чисельності працівників Планування й аналіз заробітної плати 1. Методичні основи планування продуктивності праці Основними трудовими показниками є: продуктивність праці; чисельність працівників; фонд заробітної плати; середня заробітна плата Трудові показники визначаються технікоекономічними та іншими факторами виробництва: технікоорганізаційним рівнем...
31516. Міжнародна організація праці: значення, вплив на розвиток та методи регулювання соціально-трудових відносин 101.02 KB
  МОП: цілі завдання та структура 2. Основні види діяльності МОП 3. МОП: цілі завдання та структура Міжнародна організація праці МОП це міжурядова установа яка вирішує проблеми зайнятості населення. МОП була створена у 1919 р.
31517. Праця як об’єкт вивчення дисципліни „Економіка праці і соціально-трудові відносини” та основа життєдіяльності суспільства 96 KB
  Праця як обєкт вивчення дисципліни Економіка праці і соціальнотрудові відносиниâ та основа життєдіяльності суспільства Зміст учбового матеріалу: Діяльність та її види Праця як обєкт вивчення дисципліни âЕкономіка праці і соціальнотрудові відносиниâ: предмет завдання зміст методи вивчення Система понять про працю: сутність характер аспекти розгляду Виробничий фактор праці Місце дисципліни в загальній системі економічних наук 1. Праця як обєкт вивчення дисципліни âЕкономіка праці і соціальнотрудові ресурсиâ:...
31518. Ринок праці: зовнішній та внутрішньофірмовий 171 KB
  Ринок праці: зовнішній та внутрішньофірмовий Зміст учбового матеріалу: І. Зовнішній ринок праці: 1. Поняття ринку праці його елементи та функції 2. Структура типи сегменти ринку праці 3.
31519. Трудові ресурси і трудовий потенціал суспільства 133.5 KB
  Трудові ресурси і трудовий потенціал суспільства Зміст учбового матеріалу: Населення як субєкт соціальноекономічних відносин. Населення як субєкт соціальноекономічних відносин. Населення це сукупність людей яка склалася історично та проживає на певній території. В залежності від його ролі у формуванні розвитку та реалізації виробничих відносин можна поділити на три функціональні групи: перша група населення повязане з виконанням господарських функцій промислові сільськогосподарські транспортні тощо.