Лабораторная работа по дисциплине «Основы проектирования баз данных» для ОАТК

Лабораторная работа «Оператор SELECT»

Оператор SELECT является фактически самым важным для пользователя и самым сложным оператором SQL. Он предназначен для выборки данных из таблиц, т.е. он, собственно, и реализует одно их основных назначение базы данных — предоставлять информацию пользователю. Оператор SELECT всегда выполняется над некоторыми таблицами, входящими в базу данных. Замечание. На самом деле в базах данных могут быть не только постоянно хранимые таблицы, а также временные таблицы и так называемые представления. Представления — это просто хранящиеся в базе данные SELECT-выражения. С точки зрения пользователей представления — это таблица, которая не хранится постоянно в базе данных, а «возникает» в момент обращения к ней. С точки зрения оператора SELECT и постоянно хранимые таблицы, и временные таблицы и представления выглядят совершенно одинаково. Конечно, при реальном выполнении оператора SELECT системой учитываются различия между хранимыми таблицами и представлениями, но эти различия скрыты от пользователя. Результатом выполнения оператора SELECT всегда является таблица. Таким образом, по результатам действий оператор SELECT похож на операторы реляционной алгебры. Любой оператор реляционной алгебры может быть выражен подходящим образом сформулированным оператором SELECT. Сложность оператора SELECT определяется тем, что он содержит в себе все возможности реляционной алгебры, а также дополнительные возможности, которых в реляционной алгебре нет. Примечание. Запросы называть по номеру задания, например, Запрос 1.2.б.

1.     Отбор данных из одной таблицы

1) Выбрать все данные из таблицы Типы (ключевые слова SELECT… FROM…): SELECT * FROM Типы; Замечание. В результате получим новую таблицу, содержащую полную копию данных из исходной таблицы Типы. 2) Выбрать все строки из таблицы Поставщиков, удовлетворяющих некоторому условию (ключевое слово WHERE…): SELECT * FROM Поставщики WHERE Страна = «Франция»; Замечание. В качестве условия в разделе WHERE можно использовать сложные логические выражения, использующие поля таблиц, константы, сравнения (>, <, = и т.д.), скобки, союзы AND и OR, отрицание NOT. 2а) Выбрать клиентов, которые находятся в Париже или в Канаде. 2б) Выбрать товары которые относятся к категории напитки и поставляются ООО Экзотика. 2в) Выбрать приправы, которых нет на складе или по которым прекращены поставки 2г) Выбрать все заказы кроме Вороновой и Кротова. 3) Выбрать некоторые колонки из исходной таблицы (указание списка отбираемых колонок): Перед выполнением задания создайте в таблицах Поставщики и Клиенты поле Пол. SELECT Поставщики.Пол FROM Поставщики; Замечание. В результате получим таблицу с одной колонкой, содержащую все наименования поставщиков. Замечание. Если в исходной таблице присутствовало несколько поставщиков с разными номерами, но одинаковыми наименованиями, то в результатирующей таблице будут строки с повторениями — дубликаты строк автоматически не отбрасываются. 4) Выбрать некоторые колонки из исходной таблицы, удалив из результата повторяющиеся строки (ключевое слово DISTINCT): SELECT DISTINCT Поставщики.Пол FROM Поставщики; Замечание. Использование ключевого слова DISTINCT приводит к тому, что в результирующей таблице будут удалены все повторяющиеся строки. 4а) Получите список должностей сотрудников. 4б) Получите Фамилию, Имя, Дату Рождения и Телефон сотрудника 4в) Получите список стран, в которых находятся клиенты 4г) Получите список единиц измерения товаров 4д) Получите список заказов (с указанием Клиента), доставка которых осуществлялась почтой. 4е) Получите список Клиентов (с указанием города, адреса и телефона), которые находятся в Германии или Франции 5) Использование скалярных выражений и переименований колонок в запросах (ключевое слово AS…): SELECT   Заказано.Товар,    Заказано.Цена AS PRICE,   Заказано.Количество,   Заказано.Цена*Заказано.Количество AS СуммаБезСкидкиFROM Товары; В результате получим таблицу с колонками, которых не было в исходной таблице Заказано 5а) Добавьте к предыдущему запросу столбцы СуммаСкидки и СуммаСоСкидкой, использйте скалярные выражения 5б) Получите список сотрудников и их фотографии, поле фотографии переименуйте в ФОТО 6) Упорядочение результатов запроса (ключевое слово ORDER BY…): SELECT   Типы.КодТипа   Типы.Категория,   Типы.Изображения, FROM Типы ORDER BY Категория; В результате получим таблицу, упорядоченную по полю Категория. 6а) Запрос 5а отсортируйте по полю СуммаСоСкидкой 7) Упорядочение результатов запроса по нескольким полям с возрастанием или убыванием (ключевые слова ASC, DESC): SELECT   Заказано.КодЗаказа,   Заказано.КодТовара,   Заказано.ЦенаFROM Заказано ORDER BY    КодЗаказа ASC   Цена DESC; В результате получим таблицу, в которой строки идут в порядке возрастания значения поля КодЗаказа, а строки, с одинаковым значением КодЗаказа идут в порядке убывания значения поля Цена. Замечание. Если явно не указаны ключевые слова ASC или DESC, то по умолчанию принимается упорядочение по возрастанию (ASC). 7а) Упорядочьте таблицу Поставщиков: Страна – по убыванию, Название – по возрастанию 7б) Упорядочьте таблицу Клиенты: Страна — по возрастанию, Название — по убыванию

2.     Отбор данных из нескольких таблиц

1) Естественное соединение таблиц : SELECT   Клиенты.КодКлиента   Заказы.КодКлиента,   Заказы.КодЗаказа,   Заказы.КодСотрудника,    Заказы.ДатаИсполнения FROM Клиенты, Заказы WHERE Клиенты.КодКлиента = Заказы.КодКлиента; В результате получим новую таблицу, в которой строки с данными о клиентах соединены со строками с данными о заказах. Замечание. Соединяемые таблицы перечислены в разделе FROM оператора, условие соединения приведено в разделе WHERE. Раздел WHERE, помимо условия соединения таблиц, может также содержать и условия отбора строк. 1а) Получите список товаров, которые поставляются из Австралии. 1б) Получите список Поставщиков кондитерских изделий и приправ 2) Прямое произведение таблиц: SELECT   Типы.Описание,   Товары.Марка FROM Типы, Товары; Замечание. Т.к. не указано условие соединения таблиц, то каждая строка первой таблицы соединится с каждой строкой второй таблицы. Подумайте о смысле такого произведения. 3) Соединение таблиц по произвольному условию. SELECT   Клиенты.Название,   Клиенты.ОбращатьсяК,   Клиенты.Пол,   Поставщики.Название,   Поставщики.ОбращатьсяК,   Поставщики.Пол FROM Клиенты, Поставщики WHERE Клиенты.Пол <> Поставщики.Пол;

3.     Использование имен корреляции (псевдонимов)

Иногда приходится выполнять запросы, в которых таблица соединяется сама с собой, или одна таблица соединяется дважды с другой таблицей. При этом используются имена корреляции (псевдонимы), которые позволяют различать соединяемые копии таблиц. Имена корреляции вводятся в разделе FROM и идут через пробел после имени таблицы. Имена корреляции должны использоваться в качестве префикса перед именем столбца и отделяются от имени столбца точкой. Если в запросе указываются одни и те же поля из разных экземпляров одной таблицы, они должны быть переименованы для устранения неоднозначности в именованиях колонок результирующей таблицы. Определение имени корреляции действует только во время выполнения запроса. 1) Отобрать все пары товаров: SELECT   Товары1.Марка AS Марка1,   Товары2.Марка AS Марка2,FROM   Товары Товары1, Товары Товары2;

4.     Использование агрегатных функций в запросах

1) Получить общее количество поставщиков (ключевое слово COUNT): SELECT COUNT(*) AS КоличествоПоставщиков FROM Поставщики; 1а) Получить количество поставщиков из Японии1б) Сформируйте запрос о количестве заказов на 1 квартал 1997г 2) Получить максимальную, минимальную и среднюю цену товара (ключевые слова SUM, MAX, MIN, AVG): SELECT   MAX(Товары.Цена) AS Макс,   MIN(Товары.Цена) AS Мин,   AVG(Товары.Цена) AS Сред FROM Товары;2а) Получите общую сумму заказа 104002б) Для товаров из России найдите максимальную, минимальную и среднюю цены

5.     Использование агрегатных функций с группировками

1) Для каждого клиента получить общее количество сделанных заказов:

SELECT   Заказы.КодКлиента,   COUNT(Заказы.КодЗаказа) AS КоличествоЗаказовFROM Заказы GROUP BY Заказы.КодКлиента;1а) Для каждого сотрудника посчитайте общее количество заказов1б) Для каждого заказа посчитайте общую сумму 1в) Получите среднюю сумму заказа1г) Получите общие и средние суммы заказов по странам1д) Получить общие, средние, максимальные и минимальные суммы по сотрудникам

6.     Использование операторов LIKE и IN

1) Найти наименование товара по маске:

SELECT Товары.Марка,

     Товары.НаСкладе

FROM Товары

WHERE (((Товары.Марка) Like «G?n*»));

1а) Найдите все товары на букву S или С

1б) Найдите всех поставщиков (Обращаться к), фамилия которых заканчивается на en

2) Найти поставщиков из США и Германии

SELECT Поставщики.Название, Поставщики.Страна

FROM Поставщики

WHERE (((Поставщики.Страна) In («США»,»Франция»)));

2a) Найти заказы с датой исполнения 1-го числа каждого месяца 1997г

2б) Найти поставщиков, контактным лицом которых является директор, бухгалтер, совладелец

Файл с выполненными запросами сохранить и прикрепить к ресурсу “Результат практической работы №9»