Скачать презентацию Лекция 7 Специальные возможности запросов Вопросы Скачать презентацию Лекция 7 Специальные возможности запросов Вопросы

L_7_SVZ.ppt

  • Количество слайдов: 30

Лекция № 7 Специальные возможности запросов Лекция № 7 Специальные возможности запросов

Вопросы: n Групповая операция n Использование подзапросов n Специальные возможности Вопросы: n Групповая операция n Использование подзапросов n Специальные возможности

Структура запросов SELECT T 1. FAM, SUM(T 2. WORK) FROM T 1, T 2 Структура запросов SELECT T 1. FAM, SUM(T 2. WORK) FROM T 1, T 2 WHERE T 1. ID=T 2. ID_T 1 GROUP BY T 1. FAM HAVING SUM(T 2. WORK)>1 ORDER BY T 1. FAM

АГРЕГАТНЫЕ ФУНКЦИИ n COUNT- подсчет количества (не NULL значений полей) записей; n SUM- подсчет АГРЕГАТНЫЕ ФУНКЦИИ n COUNT- подсчет количества (не NULL значений полей) записей; n SUM- подсчет арифметической суммы всех значений поля; n AVG- усреднение всех выбранных значений данного поля; n MAX- нахождение наибольшего из всех выбранных значений; n MIN- нахождение наименьшее из всех выбранных значений.

Структура БД Work. mdb Преобразуем нашу таблицу к более рациональному виду. Обратите внимание, что Структура БД Work. mdb Преобразуем нашу таблицу к более рациональному виду. Обратите внимание, что в ее структуру были добавлены два новых столбца: Цена - за деталь (руб. ) Брак - испорчено деталей (шт. ) Они позволят продемонстрировать возможность несложных расчетов. В литературе вы можете встретить это действие под называнием "построение агрегатов, основанных на скалярном выражении".

Использование GROUP BY Группа - это набор записей, у которых совпадает набор ключевых полей. Использование GROUP BY Группа - это набор записей, у которых совпадает набор ключевых полей. Все остальные поля группы должны быть приведены к единому значению Найдем максимальное количество деталей, произведенных каждым токарем за весь диапазон времени: SELECT TFIO. ФИО, MAX (Twork. Объем) FROM Twork, TFIO WHERE (TFIO. ID = Twork. Смена) GROUP BY TFIO. ФИО;

Порядок образования групп Порядок образования групп

Использование GROUP BY Подсчитаем, сколько в среднем бракованных деталей производится токарем, в различные дни Использование GROUP BY Подсчитаем, сколько в среднем бракованных деталей производится токарем, в различные дни недели. SELECT TFIO. ФИО, TDay. День, AVG(Twork. Брак) FROM Twork, TFIO, TDay WHERE (TFIO. ID = Twork. Смена) AND (TDay. ID = Twork. День) GROUP BY TFIO. ФИО, TDay. День;

Порядок образования групп Порядок образования групп

Использование предиката Having Требуется вывести всех токарей, у которых среднее число отбракованных деталей за Использование предиката Having Требуется вывести всех токарей, у которых среднее число отбракованных деталей за рабочую смену превышает норму 1 SELECT TFIO. ФИО, AVG(Twork. Брак) FROM Twork, TFIO WHERE TFIO. ID = Twork. Смена GROUP BY TFIO. ФИО HAVING AVG(Twork. Брак)>1;

Фильтрация среднего брака (>1) Фильтрация среднего брака (>1)

Использование предиката Having n Найти минимальное количество отбракованных деталей, получившееся в течение рабочих смен Использование предиката Having n Найти минимальное количество отбракованных деталей, получившееся в течение рабочих смен токарей Иванова и Петрова: SELECT TFIO. ФИО, MIN(Twork. Брак) FROM Twork, TFIO WHERE (TFIO. ID = Twork. Смена) GROUP BY TFIO. ФИО HAVING TFIO. ФИО IN (“Иванов”, “Петров”);

Фильтрация фамилий и минимального брака Фильтрация фамилий и минимального брака

Использование подзапросов Получим общее количество деталей, выточенных токарем Ивановым в дни недели, в которых Использование подзапросов Получим общее количество деталей, выточенных токарем Ивановым в дни недели, в которых имеется буква "д": SELECT “ Всего сделано =“, SUM(Объем) AS [Итого объем] FROM Twork WHERE (День IN (SELECT ID FROM TDay WHERE День LIKE “*д*”)) AND (Смена = (SELECT ID FROM TFIO WHERE ФИО = “Иванов”));

Использование подзапросов Выберем все данные о работах, в которых объем выточенных деталей больше среднего Использование подзапросов Выберем все данные о работах, в которых объем выточенных деталей больше среднего значения работ в пятницу: SELECT Tfio. ФИО, Tday. День, Twork. Объем FROM Twork, TFIO, TDay WHERE (TFIO. ID = Twork. Смена) AND (TDay. ID = Twork. День) AND Twork. Объем > (select avg(Twork. Объем) from Twork, Tday where TDay. ID = Twork. День and Tday. День Like “Пят*”);

Схема выполнения запроса Внешний запрос Результат Подзапрос Схема выполнения запроса Внешний запрос Результат Подзапрос

Использование зависимых подзапросов Выберем все данные, в которых каждый токарь перевыполнил свою среднюю производительность: Использование зависимых подзапросов Выберем все данные, в которых каждый токарь перевыполнил свою среднюю производительность: SELECT Tfio. Фио, Tday. День, Tw. Объем FROM Twork AS Tw, TFIO, Tday WHERE (TFIO. ID = Tw. Смена) AND (TDay. ID = Tw. День) AND Tw. Объем > (SELECT Avg(Twx. Объем) FROM Twork as Twx WHERE Tw. Смена=Twx. Смена);

Схема выполнения запроса Внешний запрос Результат Гришин Tw. Смена=4 Сидоров Tw. Смена=3 Петров Tw. Схема выполнения запроса Внешний запрос Результат Гришин Tw. Смена=4 Сидоров Tw. Смена=3 Петров Tw. Смена=2 Иванов Tw. Смена=1

Специальные возможности запросов Специальные возможности запросов

Использование Exists, Count Определить города рейсы к которым не производились Вариант № 1 SELECT Использование Exists, Count Определить города рейсы к которым не производились Вариант № 1 SELECT Город FROM Т_Города AS ТГ WHERE 0= (select count(ID) from Т_Рейсы Where (ТГ. ID=Т_Рейсы. ID_Город); Вариант № 2 SELECT Город FROM T_Города as ТГ WHERE NOT EXISTS (SELECT * FROM Т_Рейсы as. ТР WHERE ТГ. ID = ТР. ID_Город); 24 раза 15 раз

Использование All, ANY Вариант № 3 SELECT Город FROM Т_Города WHERE not( id=ANY( SELECT Использование All, ANY Вариант № 3 SELECT Город FROM Т_Города WHERE not( id=ANY( SELECT Distinct ID_Город FROM Т_Рейсы)); 6 раз Вариант № 4 SELECT Город FROM Т_Города WHERE id<>ALL( SELECT Distinct id_Город FROM Т_Рейсы)); 6 раз

Использование Left Join Вариант № 5 SELECT Т_Города. Город, Т_Рейсы. Id_Город FROM Т_Города LEFT Использование Left Join Вариант № 5 SELECT Т_Города. Город, Т_Рейсы. Id_Город FROM Т_Города LEFT JOIN Т_Рейсы ON Т_Города. id = Т_Рейсы. id_Город SELECT Т_Города. Город FROM Т_Города LEFT JOIN Т_Рейсы ON Т_Города. id = Т_Рейсы. id_Город WHERE Т_Рейсы. id_Город IS NULL;

Использование ALL для поиска максимумов или минимумов Минимум SELECT TFIO. ФИО, Sum(TWork. Брак) AS Использование ALL для поиска максимумов или минимумов Минимум SELECT TFIO. ФИО, Sum(TWork. Брак) AS Sumx FROM TFIO, TWork WHERE TFIO. id=TWork. Смена GROUP BY TFIO. ФИО HAVING Sum(TWork. Брак)<=All (SELECT Sum(Брак) FROM TWork Group by Смена); Максимум SELECT TFIO. ФИО, Sum(TWork. Брак) AS Sumx FROM TFIO, TWork WHERE TFIO. id=TWork. Смена GROUP BY TFIO. ФИО HAVING Sum(TWork. Брак)>=All (SELECT Sum(Брак) FROM TWork Group by Смена);

Схема выполнения запроса Внешний запрос Подзапрос Результат Минимум Максимум Схема выполнения запроса Внешний запрос Подзапрос Результат Минимум Максимум

Проектирование запросов с использованием конструктора Проектирование запросов с использованием конструктора

Использование возможностей конструктора запросов Определить кто из работников в возрасте от 25 до 60 Использование возможностей конструктора запросов Определить кто из работников в возрасте от 25 до 60 лет, имеющих выговоры за прогул и опоздание, получают наибольший средний уровень заработной платы ? SELECT top 1 Т_Работник. ФИО, Avg(Т_Зарплата. Выдано) AS Ср_ЗП FROM (Т_Работник INNER JOIN Т_Выговоры ON Т_Работник. ID = Т_Выговоры. ID_Работник) INNER JOIN Т_Зарплата ON Т_Работник. ID = Т_Зарплата. ID_Работник WHERE Т_Выговоры. Выговор In ("опоздание", "прогул") AND Date. Diff("yyyy", [Т_Работник]![Дата. Рождения], Now()) Between 25 And 60 GROUP BY Т_Работник. ФИО ORDER BY Avg(Т_Зарплата. Выдано) DESC;

Использование возможностей конструктора шаг № 1 Использование возможностей конструктора шаг № 1

Использование возможностей конструктора шаг № 2 Использование возможностей конструктора шаг № 2

Использование возможностей конструктора шаг № 3 SELECT top 1 Т_Работник. ФИО, Avg(Т_Зарплата. Выдано) AS Использование возможностей конструктора шаг № 3 SELECT top 1 Т_Работник. ФИО, Avg(Т_Зарплата. Выдано) AS Ср_ЗП FROM (Т_Работник INNER JOIN Т_Выговоры ON Т_Работник. ID = Т_Выговоры. ID_Работник) INNER JOIN Т_Зарплата ON Т_Работник. ID = Т_Зарплата. ID_Работник WHERE Т_Выговоры. Выговор In ("опоздание", "прогул") AND Date. Diff("yyyy", Т_Работник. Дата. Рождения, Now()) Between 25 And 60 GROUP BY Т_Работник. ФИО ORDER BY Avg(Т_Зарплата. Выдано) DESC;

Поиск дубликатов, с помощью зависимых запросов Определить кто из работников имеет одинаковые паспорта? SELECT Поиск дубликатов, с помощью зависимых запросов Определить кто из работников имеет одинаковые паспорта? SELECT Т_Р. ФИО, Т_Р. Серия. Пасорта, Т_Р. Номер. Паспорта FROM Т_Работник AS Т_Р WHERE Т_Р. Серия. Пасорта In (SELECT T_P 2. Серия. Пасорта FROM Т_Работник As T_P 2 GROUP BY T_P 2. Серия. Пасорта, T_P 2. Номер. Паспорта HAVING Count(*)>1 And T_P 2. Номер. Паспорта = Т_Р. Номер. Паспорта) ORDER BY Т_Р. Серия. Пасорта, Т_Р. Номер. Паспорта;