Вывести список названий компаний поставщиков

Изменение наименований полей

Часть 4. Многотабличные запросы (выборка из двух таблиц, выборка из трех таблиц с использованием JOIN)

Задание: Вывести список названий компаний-поставщиков (поле Name_company) и названия книг (поле Title_book), которые они поставили в период с 01.01.2002 по 31.12.2003 (условие по полю Date_order).

SELECT books.title_book, deliveries.name_company

FROM(books INNERJOIN purchases

ON books.code_book=purchases.code_book)INNERJOIN deliveries

WHERE purchases.date_order BETWEEN’01.01.2010’AND’31.12.2011′

Самостоятельно:

· Вывести список авторов (поле Name_author), книги которых были выпущены в издательстве ‘Лань’ (условие по полю Publish).

· Вывести список поставщиков (поле Name_company), которые поставляют книги издательства ‘Питер’ (условие по полю Publish).

· Вывести список авторов (поле Name_author) и названия книг (поле Title_book), которые были поставлены поставщиком ‘ОАО Книготорг’ (условие по полю Name_company).

Часть 5. Вычисления

Рассмотрим вычисления с использованием агрегатных функций

Агрегатные функции – это функции столбца, предназначенные для того, чтобы вычислять некоторое значение для заданного множества строк. Таким множеством строк может быть группа строк, если агрегатная функция применяется к сгруппированной таблице, или вся таблица. Если запросом определено условие WHERE, то для вычисления используются только значения выбранных таким образом строк (то есть тех записей, которые удовлетворяют условию WHERE).

Рассмотрим следующие агрегатные функции:

· SUM – возвращает сумму значений столбца группы записей;

· COUNT – возвращает количество записей группы;

· AVG – возвращает среднее значение столбца группы записей;

· MIN – возвращает минимальное значение столбца группы записей;

· MAX – возвращает максимальное значение столбца группы записей;

Агрегатные функции используются подобно именам полей в предложении SELECT запроса, но с одним исключением: они берут имя поля как аргумента. Только числовые поля могут использоваться с SUM и AVG. С COUNT, MAX, и MIN, могут использоваться и числовые, и символьные поля.

Задание: Определим количество поставщиков книг

Задание: Определить количество книг, имеющих 300 страниц

WHERE Pages >=300

Предложение GROUP BY используется для определения группы выходных строк, к которым могут применяться агрегатные функции.

Задание:Определить количество книг, соответствующих определенным авторам

WHERE Pages >=300

Предложение HAVING определяет критерии отбора для групп записей, является аналогом предложения WHERE, которое определяет критерии отбора для индивидуальных строк. В запросе SQL предложение HAVING следует за предложением GROUP BY. Пример. Вывести коды авторов, написавших более одной книги

WHERE Pages >=300

Самостоятельно:

· Вывести суммарную стоимость партии одноименных книг (использовать поля Amount и Cost) и название книги (поле Title_book) в каждой поставке.

· Вывести стоимость одной печатной страницы каждой книги (использовать поля Cost и Pages) и названия соответствующих книг (поле Title_book).

· Вывести количество лет с момента рождения авторов (использовать поле Birthday) и имена соответствующих авторов (поле Name_author).

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

Задание: Вывести общую сумму поставок книг (использовать поле Cost),выполненных `Спектр` (условие по полю Name_company).

FROM deliveries INNERJOIN purchases

ON deliveries.code_delivery = purchases.code_delivery

WHERE deliveries.name_company =’Спектр’

Самостоятельно:

· Вывести общее количество всех поставок (использовать любое поле из таблицы Purchases), выполненных в период с 01.01.2010 по 01.02.2013 (условие по полю Date_order).

· Вывести среднюю стоимость (использовать поле Cost) и среднее количество экземпляров книг (использовать поле Amount) в одной поставке, где автором книги является `Акунин` (условие по полю Name_author).

· Вывести все сведения о поставке (все поля таблицы Purchases), а также название книги (поле Title_book) с минимальной общей стоимостью (использовать поля Cost и Amount).

· Вывести все сведения о поставке (все поля таблицы Purchases), а также название книги (поле Title_book) с максимальной общей стоимостью (использовать поля Cost и Amount).

Изменение наименований полей

Задание: Вывести название книги (поле Title_book), суммарную стоимость партии одноименных книг (использовать поля Amount и Cost), поместив в результат в поле с названием Itogo, в поставках за период с 01.01.2010 по 01.06.2012 (условие по полю Date_order).

SELECT books.title_book, purchases.cost*purchases.amount AS Itogo

FROM books INNERJOIN purchases

ON books.code_book = purchases.code_book

WHERE purchases.date_order BETWEEN’01.01.2010’AND’01.06.2012′

Самостоятельно:

· Вывести стоимость одной печатной страницы каждой книги (использовать поля Cost и Pages), поместив результат в поле с названием One_page, и названия соответствующих книг (поле Title_book).

· Вывести общую сумму поставок книг (использовать поле Cost) и поместить результат в поле с названием Sum_cost, выполненных ‘ОАО Луч’ (условие по полю Name_company).

Источник

Выбор записей по диапазону значений (In)

Задание:Вывести список названий книг (поле Title_book из таблицы Books)и количество (поле Amount из таблицы Purchases), которые были поставлены поставщиками с кодами 3, 7, 9, 11 (условие по полю Code_delivery изтаблицы Purchases).

SELECT Books.Title_book, Purchases.Amount

FROM Books INNERJOIN Purchases

ON Books.Code_book = Purchases.Code_book

WHERE Purchases.Code_delivery IN(3,7,9,11)

· Вывести список названий книг (поле Title_book) из таблицы Books, которые выпущены следующими издательствами: ‘Питер-Софт’, ‘Альфа’,‘Наука’ (условие по полю Publish из таблицы Publishing_house).

· Вывести список названий книг (поле Title_book) из таблицы Books,которые написаны следующими авторами: ‘Толстой Л.Н.’, ‘Достоевский Ф.М.’, ‘Пушкин А.С.’ (условие по полю Name_author из таблицы Authors).

Условие неточного совпадения. Выбор записей с использованием Like

Задание: Выбрать из справочника поставщиков (таблица Deliveries) названия компаний, телефоны и ИНН (поля Name_company, Phone и INN), у которых название компании (поле Name_company) начинается с ‘ОАО’.

SELECT Name_company, Phone, INN

WHERE Name_company LIKE’ОАО%’

· Выбрать из таблицы Books названия книг и количество страниц (поля Title_book и Pages), а из таблицы Authors выбрать имя соответствующего автора книг (поле Name_ author), у которых название книги начинается со слова ‘Мемуары’.

· Выбрать из таблицы Authors фамилии, имена, отчества авторов (поле Name_ author), значения которых начинаются с ‘Иванов’.

· Вывести названия издательств (поле Publish) из таблицы Publishing_house, которые содержат в названии сочетание ‘софт’.

· Выбрать названия компаний (поле name_company) из таблицы Deliveries, у которых значение оканчивается на ‘ский’.

Выбор записей по нескольким условиям

Задание: Выбрать коды поставщиков (поле Code_delivery), даты заказов (поле Date_order) и названия книг (поле Title_book), если количество книг (поле Amount) в заказе больше 100 или цена (поле Cost) за книгу находится в диапазоне от 200 до 500.

SELECT Code_delivery, Date_order, Title_book

FROM Purchases INNERJOIN Books

WHERE Amount >’100’OR

· Выбрать коды авторов (поле Code_author), имена авторов (поле Name_author), названия соответствующих книг (поле Title_book), если код издательства (поле Code_Publish) находится в диапазоне от 10 до 25 и количество страниц (поле Pages) в книге больше 120.

· Вывести список издательств (поле Publish) из таблицы Publishing_house, в которых выпущены книги, названия которых (поле Title_book)начинаются со слова ‘Труды’ и город издания (поле City) – ‘Новосибирск’.

Источник

База данных «Книжное дело»

База данных «Книжное дело»

Рис. 21. Фрагмент базы данных «Книжное дело»

Создать таблицы с помощью SQL запросов.

Связь между таблицами осуществляется с помощью следующих пар полей с типом связи «один-ко-многим» соответственно:

Books. Codebook — Purchases. Codebook; Deliveries. Codedelivery — Purchases. Codedelivery; Authors. Codeauthor — Books. Codeauthor; Publising_house. Code_publish — Books. Code_publish.

Покупки (название таблицы Purchases)

Код закупаемой книги

Дата заказа книги

Тип закупки (опт/ розница)

Стоимость единицы товара

Справочник книг (название таблицы Books)

Таблица 9 Справочник авторов (название таблицы Authors)

Фамилия, имя, отчество автора

Таблица 10 Справочник поставщиков (название таблицы Deliveries)

Фамилия, и., о. ответственного лица

Таблица 11 Справочник издательств (название таблицы Publishinghouse)

Выбор результата в таблицы

48. Вывести список названий книг (поле Titlebook) и количества
страниц (поле Pages) в каждой книге и поместить результат в таблицу с
названием Temp1.

Вывести список названий компаний-поставщиков (поле Namecompany) и поместить результат в таблицу с названием Теmр2. Вывести список авторов (поле Nameauthor) и поместить результат в таблицу с названием ТеmрЗ.

Использование функций совместно с подзапросом

Вывести список книг (поле Titlebook), у которых количество страниц (поле Pages) больше среднего количества страниц всех книг в таблице. Вывести список авторов (поле Nameauthor), возраст которых меньше среднего возраста всех авторов в таблице (условие по полю Birthday). Вывести список книг (поле Titlebook), у которых количество страниц (поле Pages) равно минимальному количеству страниц книг, представленных в таблице.

Использование квантора существования в запросах

Вывести список издательств (поле Publish), книги которых были приобретены оптом (‘опт’ из поля TypePurchase). Вывести список авторов (поле Nameauthor), книг которых нет в таблице Books. Вывести список книг (поле Titlebook), которые были поставлены поставщиком » (условие по полю Namecompany).

Оператор обработки данных Update

Изменить в таблице Books содержимое поля Pages на 300, если код автора (поле Codeauthor) =56 и название книги (поле Titlebook) =’Мемуары’. Изменить в таблице Deliveries содержимое поля Address на ‘нет сведений’, если значение поля является пустым. Увеличить в таблице Purchases цену (поле Cost) на 20 процентов, если заказы были оформлены в течение последнего месяца (условие по полю Dateorder).

Оператор обработки данных Insert

Добавить в таблицу Purchases новую запись, причем так, чтобы код покупки (поле Code_purchase) был автоматически увеличен на единицу, а в тип закупки (поле Type_purchase) внести значение ‘опт’. Добавить в таблицу Books новую запись, причем вместо ключевого поля поставить код (поле Codebook), автоматически увеличенный на единицу от максимального кода в таблице, вместо названия книги (поле Titlebook) написать ‘Наука. Техника. Инновации’.

62. Добавить в таблицу Publishhouse новую запись, причем вместо
ключевого поля поставить код (поле Code_publish), автоматически
увеличенный на единицу от максимального кода в таблице, вместо
названия города — ‘Москва’ (поле City), вместо издательства — ‘Наука’
(поле Publish).

Оператор обработки данных Delete

Удалить из таблицы Purchases все записи, у которых количество книг в заказе (поле Amount) = 0. Удалить из таблицы Authors все записи, у которых нет имени автора в поле NameAuthor. Удалить из таблицы Deliveries все записи, у которых не указан ИНН (поле INN пустое).

Источник

wiki.vspu.ru

портал образовательных ресурсов

Содержание

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

Использование операторов манипулирования данными в Microsoft SQL Server

Цель работы – научиться использовать операторы манипулирования данными Select, Insert, Update, Delete.

Содержание работы:

Пояснения к выполнению работы

Общие положения

Создать новую базу данных с названием DB_Books с помощью оператора Create Database, создать в ней перечисленные таблицы c помощью операторов Create table по примеру лабораторной работы №1. Сохранить файл программы с названием ФамилияСтудента_ЛАб_1_DB_Books. В утилите SQL Server Management Studio с помощью кнопки «Создать запрос» создать отдельные программы по каждому запросу, которые сохранять на диске с названием: ФамилияСтудента_ЛАб_2_№_задания. В сами программы копировать текст задания в виде комментария. Можно сохранять все выполненные запросы в одном файле. Для проверки работы операторов SELECT предварительно создайте программу, которая с помощью операторов INSERT заполнит все таблицы БД DB_Books несколькими записями, сохраните программы с названием ФамилияСтудента_ЛАб_2_Insert.

Оператор Select используется для выбора данных из таблиц.

Для выбора данных из некоторой таблицы нет необходимости знать имена всех ее полей. Звездочка (*) после оператора SELECT означает выбор всех столбцов таблицы. Другими словами, эта команда просто выводит все данные таблицы. Синтаксис:

Если необходимо выбрать только определенные столбцы, то нужно их непосредственно указать после слова Select.

Пример. Выбрать сведения о количестве страниц из таблицы Books (поле Pages).

Пример. Выбрать все данные из таблицы Books.

Сортировка

Для упорядочения данных по какому-то полю необходимо выполнить команду

Записи можно упорядочивать в восходящем (параметр сортировки ASC) или в нисходящем (параметр сортировки DESC) порядке. Параметр сортировки ASC используется по умолчанию.

Пример. Выбрать все сведения о книгах из таблицы Books и отсортировать результат по коду книги (поле Code_book).

Изменение порядка следования полей

Пример. Выбрать все поля из таблицы Deliveries таким образом, чтобы в результате порядок столбцов был следующим: Name_delivery, INN, Phone, Address, Code_delivery.

Выбор некоторых полей из двух таблиц

Когда нужно выбрать данные, находящиеся в разных таблицах, применяют объединение таблиц. Пусть, например, необходимо выбрать данные, находящиеся в таблицах Books и Authors.

Простейший шаблон объединения двух таблиц выглядит следующим образом:

Есть 4 типа объединения.

Внутреннее объединение (INNER JOIN)

При внутреннем объединении в таблицах А и В соединяются только те строки, для которых найдено совпадение, указанное в критерии объединения (после ключевого слова ON). Это наиболее подходящий в нашем случае вариант. Следующий запрос объединяет две таблицы Books и Authors, связанные по полю Code_author.

Левое внешнее объединение (LEFT OUTER JOIN)

Левое внешнее объединение таблиц А и В включает в себя все строки из левой таблицы А и те строки из правой таблицы В, для которых обнаружено совпадение, указанное в критерии объединения (после ключевого слова ON). Для строк из таблицы А, для которых не найдено соответствия в таблице В, в столбцы, извлекаемые из таблицы В, заносятся значения NULL. Пример запроса:

Правое внешнее объединение (RIGHT OUTER JOIN)

Правое внешнее объединение таблиц А и В включает в себя все строки из правой таблицы В и те строки из левой таблицы А, для которых обнаружено совпадение, указанное в критерии объединения (после ключевого слова ON). Для строк из таблицы В, для которых не найдено соответствия в таблице А, в столбцы, извлекаемые из таблицы А заносятся значения NULL. Пример запроса:

Полное объединение (FULL JOIN)

Это комбинация левого и правого объединений. В полное объединение таблиц включаются все строки из обеих таблиц. Для совпадающих строк поля заполняются реальными значениями, для несовпадающих строк поля заполняются в соответствии с правилами левого и правого соединений. Пример запроса:

Если необходимо вывести не все столбцы, например, два столбца из таблицы Books (Title_book и Authors) и один столбец из таблицы Authors. Для этого нужные столбцы надо явно указать (с учетом идентификатора таблицы, к которой они принадлежат):

Выбор строк с указанием критериев поиска (WHERE)

Предложение WHERE применяется для выбора записей, соответствующих определенным критериям. Критерий поиска состоит из одного или нескольких предикатов. Предикат задает проверку, выполняемую для каждой записи таблицы. Результат проверки может принимать одно из трех значений: «true», «false», «unknown». Команда извлекает для вывода только те строки из таблицы, для которых результат проверки равен «true». В таблице 1 приведен синтаксис различных видов сравнения.

Точное совпадение значений одного из полей

Пример: вывести список названий издательств (поле Title_book) из таблицы Books, которые находятся в(поле Publish) из таблицы Publishing_house .

Использование вложенного запроса

Пример: Найти название книг, автором которых является Толстой

Этот запрос работает если вложенный подзапрос выдает одно число. Если в базе данных есть несколько записей, соответствующих фамилия писателя «Толстой» то запрос завершится с ошибкой в этом случае лучше использовать конструкцию In:

Точное несовпадение значений одного из полей

Пример. Вывести список названий издательств (поле Publish) из таблицы Publishing_house, которые не находятся в городе ‘Москва’ (условие по полю City).

Выбор записей по диапазону значений (Between)

Пример. Вывести фамилии, имена, отчества авторов (поле Name_author) из таблицы Authors, у которых дата рождения (поле Birthday) находится в диапазоне 01.01.1840 – 01.06.1860.

Выбор записей по диапазону значений (In)

Пример. Вывести список названий книг (поле Title_book из таблицы Books)и количество (поле Amount из таблицы Purchases), которые были поставлены поставщиками с кодами 3, 7, 9, 11 (условие по полю Code_delivery из таблицы Purchases).

Условие неточного совпадения. Выбор записей с использованием Like

Пример. Выбрать из справочника поставщиков (таблица Deliveries) названия компаний, телефоны и ИНН (поля Name_company, Phone и INN), у которых название компании (поле Name_company) начинается с ‘ОАО’.

Выбор записей по нескольким условиям

Пример. Выбрать коды поставщиков (поле Code_delivery), даты заказов (поле Date_order) и названия книг (поле Title_book), если количество книг (поле Amount) в заказе больше 100 или цена (поле Cost) за книгу находится в диапазоне от 200 до 500.

Многотабличные запросы (выборка из двух таблиц, выборка из трех таблиц с использованием JOIN)

Пример. Вывести список названий компаний-поставщиков (поле Name_company) и названия книг (поле Title_book), которые они поставили в период с 01.01.2002 по 31.12.2003 (условие по полю Date_order).

Вычисления

Рассмотрим вычисления с использованием агрегатных функций

Агрегатные функции – это функции столбца, предназначенные для того, чтобы вычислять некоторое значение для заданного множества строк. Таким множеством строк может быть группа строк, если агрегатная функция применяется к сгруппированной таблице, или вся таблица. Если запросом определено условие WHERE, то для вычисления используются только значения выбранных таким образом строк (то есть тех записей, которые удовлетворяют условию WHERE).

Рассмотрим следующие агрегатные функции:

Агрегатные функции используются подобно именам полей в предложении SELECT запроса, но с одним исключением: они берут имя поля как аргумента. Только числовые поля могут использоваться с SUM и AVG. С COUNT, MAX, и MIN, могут использоваться и числовые, и символьные поля.

Пример. Определим количество поставщиков книг

Пример. Определить количество книг, имеющих 300 страниц

Предложение GROUP BY используется для определения группы выходных строк, к которым могут применяться агрегатные функции. Пример. Определить количество книг, соответствующих определенным авторам

Предложение HAVING определяет критерии отбора для групп записей, является аналогом предложения WHERE, которое определяет критерии отбора для индивидуальных строк. В запросе SQL предложение HAVING следует за предложением GROUP BY. Пример. Вывести коды авторов, написавших более одной книги

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

Пример. Вывести общую сумму поставок книг (использовать поле Cost),выполненных `Спектр` (условие по полю Name_company).

Изменение наименований полей

Пример. Вывести название книги (поле Title_book), суммарную стоимость партии одноименных книг (использовать поля Amount и Cost), поместив в результат в поле с названием Itogo, в поставках за период с 01.01.2010 по 01.06.2012 (условие по полю Date_order).

Использование переменных в условии

Пример. Вывести список книг (поле Title_book), которых закуплено меньше, чем указано в запросе пользователя (условие с использованием поля Amount).

Источник