27011

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

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

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

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

Русский

2013-08-19

86 KB

38 чел.

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

.


 

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

5427. Изучение методов контроля линейных размеров деталей с помощью штангенинструментов 2.34 MB
  Цель работы: изучить метод измерений размеров деталей с помощью штангенциркуля и освоить методику представления результатов измерений. Общие сведения. Штангенинструменты, предназначенные для измерений линейных размеров деталей, пре...
5428. Проектирование привода ленточного транспортера 1.35 MB
  Анализ схемы привода. Привод состоит из асинхронного двигателя, цилиндрического соосного двухпоточного редуктора и приводного вала с барабаном и муфтой. В ходе проектирования транспортера были приняты следующие конструктивные решения: для выравни...
5429. Концептуальные основы реформирования бухгалтерского учета и отчетности в Российской Федерации 192.5 KB
  Введение Переход экономики России к рыночным отношениям поставил перед бухгалтерским учетом совершенно новые цели. Существовавшая ранее в условиях планируемой командной экономики система бухгалтерского учета, была обусловлена общественным характером...
5430. Смутное время 43.82 KB
  Смутное время На рубеже 16 и 17 вв. Московское государство переживало тяжелый и сложный морально-политический и социально-экономический кризис, который особенно проявлялся в положении центральных областей государства. С открытием для русской колон...
5431. Изучение кодеков ИКМ 203.5 KB
  Изучение кодеков ИКМ Цель работы Изучить процессы квантования и кодирования речевых сигналов в цифровых системах передачи. Задание на лабораторную работу Задание по теоретической части Изучить процедуры линейного и нелинейног...
5432. Термодинаміка. Виникнення термодинаміки 61 KB
  Термодинаміка Виникнення термодинаміки Теплові явища відрізняються від механічних і електромагнітних тем, що закони теплових явищ необоротні (тобто теплові процеси самі йдуть лише в одному напрямку) і що теплові процеси здійснюються лише в макрос...
5433. Проектирование СТО по кузовному ремонту автомобилей в советском АО 467.5 KB
  Данный проект предназначен для предприятия, занимающегося оказанием услуг по кузовному ремонту и покраске автомобилей. Так как бизнес план составляется для нового предприятия, то лучшим вариантом будет создание предприятия малого бизнеса, зан...
5434. Первая помощь при травмах и ранениях нижней конечности 351.61 KB
  Первая помощь при травмах и ранениях нижней конечности. Вступление. Анатомические особенности нижней конечности. Признаки травм и переломов нижней конечности. Принципы оказания первой помощи при ранениях нижней конечности...
5435. Радянська Україна в 20х 30 х рр. Західноукраїнські землі у 20х 30 х рр. 48.5 KB
  Радянська Україна в 20х 30 х рр. Західноукраїнські землі у 20х 30 х рр. Місце України у Версальсько-Вашингтонській системі (самостійне вивчення). Національно-державне будівництво. Соціально-економічне та політичне становище. Політика...