Слайд 1Лекция №2
Структурированный язык запросов
Слайд 2Вопросы:
Структура запросов
Операторы выборки и фильтрации
Агрегатные функции, сортировка и группировка
данных
Подзапросы
Слайд 3Структура запросов
SELECT T1.FAM,SUM(T2.WORK)
FROM T1, T2
WHERE T1.ID=T2.ID_T1
GROUP BY T1.FAM
HAVING SUM(T2.WORK)>1
ORDER BY
T1.FAM
Слайд 5Запросы, реализующие операцию проекции
SELECT * FROM TPOL;
SELECT ФИО, Адрес
FROM TAbonents;
Слайд 6Запросы, поясняющие исключение дубликатов
SELECT DISTINCT Дата_установки
FROM TAbonents;
SELECT ALL Дата_установки
FROM TAbonents;
Слайд 7Запросы, поясняющие выполнение операции фильтрации в одной таблице
SELECT * FROM
TAbonents
WHERE ПАСПОРТ = 679237
SELECT * FROM TAbonents
WHERE ПАСПОРТ=679237
OR
ФИО="Жуйченко Женя";
Слайд 8Выборка данных из двух связанных таблиц
SELECT TAbonents.ФИО, TPol.ПОЛ
FROM TAbonents,
TPol
WHERE (TAbonents.Pol_ID = TPol.ID) AND (TPol.ПОЛ = “м”) ;
Слайд 9Выборка данных из трех связанных таблиц
SELECT TAbonents.ФИО, Tpol.ПОЛ, NomTel.НОМЕР_ТЕЛЕФОНА
FROM Tpol,
TAbonents, NomTel
WHERE TNomTel.id = TAbonents.NomTel_ID and
Tpol.id = TAbonents.Pol_ID and
TAbonents.ДАТА_УСТАНОВКИ=#12/5/1997#;
Слайд 10Операторы сравнения
> Больше чем
< Меньше чем
>= Больше чем
или равно
TAbonents WHERE (ДАТА_ВЫДАЧИ <> #6/1/1997#);
Слайд 11Оператор Not предиката Where
SELECT * FROM TAbonents
WHERE NOT (ФИО
= “Иванов Иван Иванович”);
или
SELECT * FROM TAbonents
WHERE Not (ФИО="Иванов Иван
Иванович" and id=1)
Слайд 12Оператор IN предиката Where
SELECT * FROM TAbonents
WHERE ФИО In ("Иванов
Иван Иванович","Петров");
SELECT * FROM TAbonents
WHERE ПАСПОРТ In (675537,326757,656729);
Слайд 13Оператор BETWEEN предиката Where
SELECT * FROM TAbonents
WHERE ПАСПОРТ Between 628513
and 679237;
SELECT * FROM TAbonents WHERE ФИО Between "А" and
"К";
Слайд 14Оператор LIKE предиката Where
SELECT * FROM TAbonents
WHERE АДРЕС Like
"ул.*";
SELECT * FROM TAbonents
WHERE АДРЕС Like "*ма*";
Слайд 15Оператор IS Null предиката Where
SELECT *FROM TAbonents WHERE АДРЕС iS
Null;
SELECT * FROM TAbonents
WHERE Not АДРЕС Is Null;
Слайд 16АГРЕГАТНЫЕ ФУНКЦИИ
COUNT- подсчет количества (не NULL значений полей) записей;
SUM- подсчет
арифметической суммы всех значений поля;
AVG- усреднение всех выбранных значений данного
поля;
MAX- нахождение наибольшего из всех выбранных значений;
MIN- нахождение наименьшее из всех выбранных значений.
Слайд 17Примеры использования агрегатов
SELECT SUM (Объем) as Сумма
FROM TWork;
SELECT Avg(TWork.Объем) AS
Среднее
FROM TWork;
SELECT Max(TWork.Объем) AS Max_V
FROM TWork WHERE TWork.День Like "п*";
SELECT
Count(id) AS Количество FROM TWork
WHERE Смена Like "*ов";
Слайд 19Примеры использования агрегатов
Найдем наибольшую долю брака при работе токаря Иванова.
SELECT
MAX(Twork.Брак / (Twork.Объем + Twork.Брак ))
FROM Twork, TFIO
WHERE (TFIO.ID
= Twork.Смена) AND (TFIO.ФИО = “Иванов” )
Результат
Шаг №1
Слайд 20Примеры использования агрегатов
Найдем максимальную стоимость деталей произведенных за
первые три дня недели токарем в фамилии которого присутствуют сочетание
«ро».
SELECT Max(TWork.Объем*TWork.Цена) AS Стоимость
FROM TFIO,TDay,TWork
WHERE TDay.ID = TWork.День and TFIO.id = TWork.Смена
And TDay.День In ("понедельник","вторник","среда“) and TFIO.ФИО) Like "*ро*”;
Результат
Шаг №1
Слайд 21Упорядочивание данных
Найдем отсортированную таблицу о величине брака
ФИО – ДЕНЬ – БРАК
SELECT TFIO.ФИО, TDay.День, TWork.Брак
FROM TFIO, TDay,
TWork
WHERE TDay.ID=TWork.День AND TFIO.id=TWork.Смена
ORDER BY TFIO.ФИО, TDay.День DESC , TWork.Брак;
Слайд 22Упорядочивание данных
Найдем отсортированную таблицу о величине брака
ДЕНЬ
- ФИО – Объем
SELECT TDay.День, TFIO.ФИО, TWork.ОБЪЕМ
FROM TFIO, TDay, TWork
WHERE
TDay.ID=TWork.День AND TFIO.id=TWork.Смена
ORDER BY TDay.День, TFIO.ФИО, TWork.ОБЪЕМ;
Слайд 23Использование GROUP BY
Группа - это набор записей, у которых совпадает
набор ключевых полей. Все остальные поля группы должны быть приведены
к единому значению
Найдем максимальное количество деталей, произведенных каждым токарем за весь диапазон времени:
SELECT TFIO.ФИО, MAX (Twork.Объем)
FROM Twork, TFIO
WHERE (TFIO.ID = Twork.Смена)
GROUP BY TFIO.ФИО;
Слайд 25Использование GROUP BY
Подсчитаем, сколько в среднем бракованных деталей производится токарем,
в различные дни недели.
SELECT TFIO.ФИО,TDay.День,
AVG(Twork.Брак)
FROM Twork, TFIO, TDay
WHERE (TFIO.ID = Twork.Смена) AND
(TDay.ID = Twork.День)
GROUP BY TFIO.ФИО, TDay.День;
Слайд 27Использование предиката Having
Требуется вывести всех токарей, у которых
среднее число отбракованных деталей за рабочую смену превышает норму 1
SELECT TFIO.ФИО, AVG(Twork.Брак) FROM Twork, TFIO
WHERE TFIO.ID = Twork.Смена
GROUP BY TFIO.ФИО
HAVING AVG(Twork.Брак)>1;
Слайд 29Использование предиката Having
Найти минимальное количество отбракованных деталей, получившееся в течение
рабочих смен токарей Иванова и Петрова:
SELECT TFIO.ФИО, MIN(Twork.Брак)
FROM Twork, TFIO
WHERE
(TFIO.ID = Twork.Смена)
GROUP BY TFIO.ФИО
HAVING TFIO.ФИО IN (“Иванов”, “Петров”);
Слайд 30Фильтрация фамилий и минимального брака
Слайд 31Использование подзапросов
Получим общее количество деталей, выточенных токарем Ивановым в дни
недели, в которых имеется буква "д":
SELECT ‘ Всего сделано
=‘,
SUM(Объем) AS ‘Итого объем’
FROM Twork
WHERE (День IN (SELECT ID
FROM TDay
WHERE День LIKE ‘%д%’)) AND
(Смена = (SELECT ID
FROM TFIO
WHERE ФИО = ‘Иванов’));
Слайд 32Использование подзапросов
Выберем все данные о работах, в которых объем выточенных
деталей больше среднего значения работ в пятницу:
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 ‘Пят%’);
Слайд 33Схема выполнения запроса
Результат
Подзапрос
Внешний запрос
Слайд 34Использование зависимых подзапросов
Выберем все данные, в которых каждый токарь перевыполнил
свою среднюю производительность:
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.Смена);
Слайд 35Схема выполнения запроса
Иванов Tw.Смена=1
Петров Tw.Смена=2
Сидоров Tw.Смена=3
Гришин Tw.Смена=4
Результат
Внешний запрос
Слайд 36Специальные возможности запросов
Слайд 37Использование 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 раз
Слайд 38Использование All,ANY
Вариант №3
SELECT Город FROM Т_Города
WHERE not( id=ANY(
SELECT Distinct
ID_Город FROM Т_Рейсы));
Вариант №4
SELECT Город FROM Т_Города
WHERE
id<>ALL(
SELECT Distinct id_Город FROM Т_Рейсы));
6 раз
6 раз
Слайд 39Использование Left Join
Вариант №5
SELECT Т_Города.Город, Т_Рейсы. Id_Город
FROM Т_Города LEFT JOIN
Т_Рейсы ON Т_Города.id = Т_Рейсы.id_Город
SELECT Т_Города.Город
FROM Т_Города LEFT JOIN Т_Рейсы
ON Т_Города.id = Т_Рейсы.id_Город
WHERE Т_Рейсы.id_Город IS NULL;
Слайд 40Использование ALL для поиска максимумов или минимумов
Минимум
SELECT TFIO.ФИО, Sum(TWork.Брак) AS
Sumx
FROM TFIO, TWork
WHERE TFIO.id=TWork.Смена
GROUP BY TFIO.ФИО
HAVING Sum(TWork.Брак)
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 Смена);
Слайд 41Схема выполнения запроса
Результат
Подзапрос
Внешний запрос
Минимум
Максимум
Слайд 42Проектирование запросов с использованием конструктора
Слайд 43Использование возможностей конструктора запросов
Определить кто из работников в возрасте от
25 до 60 лет, имеющих выговоры за прогул и опоздание,
получают наибольший средний уровень заработной платы ?
SELECT top 1 Т_Работник.ФИО, Avg(Т_Зарплата.Выдано) AS Ср_ЗП
FROM (Т_Работник INNER JOIN Т_Выговоры ON Т_Работник.ID = Т_Выговоры.ID_Работник) INNER JOIN Т_Зарплата ON Т_Работник.ID = Т_Зарплата.ID_Работник
WHERE Т_Выговоры.Выговор In ("опоздание","прогул") AND DateDiff("yyyy",[Т_Работник]![ДатаРождения],Now()) Between 25 And 60
GROUP BY Т_Работник.ФИО
ORDER BY Avg(Т_Зарплата.Выдано) DESC;
Слайд 44Использование возможностей конструктора шаг №1
Слайд 45Использование возможностей конструктора шаг №2
Слайд 46Использование возможностей конструктора шаг №3
SELECT top 1 Т_Работник.ФИО, Avg(Т_Зарплата.Выдано) AS
Ср_ЗП
FROM (Т_Работник INNER JOIN Т_Выговоры ON Т_Работник.ID = Т_Выговоры.ID_Работник) INNER
JOIN Т_Зарплата ON Т_Работник.ID = Т_Зарплата.ID_Работник
WHERE Т_Выговоры.Выговор In ("опоздание","прогул") AND DateDiff("yyyy",Т_Работник.ДатаРождения,Now()) Between 25 And 60
GROUP BY Т_Работник.ФИО
ORDER BY Avg(Т_Зарплата.Выдано) DESC;
Слайд 47Поиск дубликатов, с помощью зависимых запросов
Определить кто из работников имеет
одинаковые паспорта?
SELECT Т_Р.ФИО, Т_Р.СерияПасорта, Т_Р.НомерПаспорта
FROM Т_Работник AS Т_Р
WHERE Т_Р.СерияПасорта
In (SELECT T_P2.СерияПасорта
FROM Т_Работник As T_P2
GROUP BY T_P2 .СерияПасорта, T_P2 .НомерПаспорта
HAVING Count(*)>1 And
T_P2 .НомерПаспорта = Т_Р.НомерПаспорта)
ORDER BY Т_Р.СерияПасорта, Т_Р.НомерПаспорта;