Задание №2. Лабораторная работа построение запросов
 Скачать 0.64 Mb.
|
|
ЛАБОРАТОРНАЯ РАБОТА № 2. ПОСТРОЕНИЕ ЗАПРОСОВ Цель работы – построение запросов с помощью различных ко- манд и операторов языка T-SQL в среде SQL Server Management Studio. Основные теоретические сведения Примечание: в данном разделе при объяснении теоретического материала используется БД «Факультет», имеющая структуру, пред- ставленную на рис. 2.1. БД Institute состоит из 6 таблиц: Таблица Teachers содержит сведения о преподавателях; Таблица Lessons содержит сведения о предметах; Таблица Groups содержит сведения об учебных группах; Таблица Students содержит сведения о преподавателях; Таблица Kafedra содержит сведения о кафедрах; Таблица Progress содержит сведения об успеваемости студентов. Рис. 2.1. Структура БД «Факультет» 2 § 2.1. Команда Select Команда SELECT – средство, которое полностью абстрагировано от вопросов представления данных, что помогает сконцентрировать внимание на проблемах доступа к данным. Примеры использования SELECT наглядно демонстрируют один из основополагающих принципов больших (промышленных) СУБД: средства хранения данных и доступа к ним отделены от средств пред- ставления данных. Операции над данными производятся в масштабе наборов дан- ных, а не отдельных записей. Один и тот же запрос может быть реализован несколькими спо- собами, которые будучи правильными, тем не менее, могут суще- ственно отличаться по времени исполнения, и это особенно важно для больших БД. Команда SELECT не изменяет данные в базе данных, а только производит их выборку в соответствии с заданными критериями. Команда SELECT имеет следующий формат: SELECT {*|[имя_столбца [AS новое_имя]]} [,...n] FROM имя_таблицы [[AS]псевдоним] [,...n] [WHERE <условие_отбора>] [GROUP BY имя_столбца [,...n]] [HAVING <критерии выбора групп>] [ORDER BY имя_столбца[,...n]]; Команда SELECT определяет поля (столбцы), которые будут входить в результат выполнения запроса. В списке они разделяются запятыми и приводятся в такой последовательности, в какой должны быть представлены в результате запроса. Если используется имя поля, содержащее пробелы или разделители, его следует заключить в квад- ратные скобки. Если обрабатывается несколько таблиц, то (при наличии одно- именных полей в разных таблицах) в списке полей используется пол- ная спецификация поля, то есть Имя_таблицы.Имя_поля. Порядок выполнения оператора SELECT: 1.FROM – вначале определяются имена используемых таблиц; 2. WHERE – из указанной таблицы выбираются записи, удовле- творяющие заданным условиям; 3 3. GROUP BY – выполняется группировка полученных записей, то есть образуются группы строк, имеющих одно и то же значение в указанном столбце; 4. HAVING – выбор группы строк, удовлетворяющих указанным условиям; 5. ORDER BY – выполняется сортировка записей в указанном порядке; 6. SELECT – устанавливается, какие столбцы должны присут- ствовать в выходных данных. Порядок предложений и фраз в команде SELECT не может быть изменен. Только два предложения SELECT и FROM являются обяза- тельными, все остальные могут быть опущены Предложение FROM FROM задает имена таблиц и просмотров, которые содержат по- ля, перечисленные в команде SELECT. Необязательный параметр псевдонима – это сокращение, устанавливаемое для имени таблицы. Пример 2.1: Составить список сведений обо всех студентах: SELECT * FROM Students; Предложение WHERE Это предложение определяет, какие записи из таблиц, перечис- ленных в предложении FROM, следует включить в результат выпол- нения команды SELECT. За ключевым словом WHERE следует перечень условий поиска, определяющих те строки, которые должны быть выбраны при выпол- нении запроса. Предложение WHERE может содержать до 40 выражений, свя- занных логическими операторами, такими как AND и OR. Основные типы условий отбора: 1. Сравнение. В этой операции сравниваются результаты вычисления одного выражения с результатами вычисления другого. В языке SQL можно использовать следующие операторы срав- нения: = – равенство; < – меньше; > – больше; 4 <= – меньше или равно; >= – больше или равно; <> – не равно. Более сложные предикаты могут быть построены с помощью ло- гических операторов AND, OR или NOT, а также скобок, используемых для определения порядка вычисления выражения. Правила вычисления выражения в условиях: – выражение вычисляется слева направо; – первыми вычисляются подвыражения в скобках; – операторы NOT выполняются до выполнения операторов AND и OR; – операторы AND выполняются до выполнения операторов OR. Для устранения любой возможной неоднозначности рекоменду- ется использовать скобки. Пример 2.2: Вывести список студентов, которые получают сти- пендию больше 1500 руб. и меньше или равную 2000 руб.: SELECT Fio, Stipendiya FROM Students WHERE (Stipendiya>=1500) And (Stipendiya<=2000); 2. Диапазон. В этой операции проверяется, попадает ли результат вычисления выражения в заданный диапазон значений. Диапазон задается с по- мощью ключевого слова BETWEEN. Диапазон определяется своими минимальным и максимальным значениями. При этом указанные значения включаются в условие поиска. Пример 2.3: Вывести список студентов, которые получают сти- пендию больше 1500 руб. и меньше или равную 2000 руб. (запрос эквивалентен примеру 2.2): SELECT Fio, Stipendiya FROM Students WHERE Stipendiya BETWEEN 1500 AND 2000; 3. Принадлежность множеству. Здесь проверяется, принадлежит ли результат вычислений выра- жения заданному множеству значений. 5 Оператор IN используется для сравнения некоторого значения со списком заданных значений. При этом проверяется, соответствует ли результат вычисления выражения одному из значений в представ- ленном списке. С помощью оператора IN может быть достигнут тот же результат, что и в случае применения оператора OR, однако опера- тор IN выполняется быстрее. NOT IN используется для отбора любых значений, кроме тех, которые указаны в представленном списке. Пример 2.4: Вывести список преподавателей, у которых стаж ра- боты составляет 20 или 25 лет: SELECT Familia, Stazh FROM Teachers WHERE Stazh IN (20, 25); 4. Соответствие шаблону. Здесь проверяется, отвечает ли некоторое строковое значение заданному шаблону. С помощью оператора LIKE можно выполнять сравнение выра- жения с заданным шаблоном. Символы-заменители, используемые в шаблоне: – % – вместо этого символа может быть подставлено любое ко- личество произвольных символов. – _ – заменяет один символ строки. – [] – вместо символа строки будет подставлен один из возмож- ных символов, указанный в этих ограничителях. – [^] – вместо соответствующего символа строки будут подстав- лены все символы, кроме указанных в ограничителях. Пример 2.5: Найти студентов, у которых в фамилии встречается слог 'ко': SELECT Fio FROM Students WHERE Fio LIKE '%ко%'; 5. Значение NULL. Значение NULL проверяет, содержит ли данный столбец опреде- литель NULL (неопределенное значение). Оператор IS NULL используется для сравнения текущего зна- чения со значением NULL – специальным значением, указывающим на отсутствие любого значения. NULL – это не то же самое, что знак 6 пробела (пробел – допустимый символ) или нуль (0 – допустимое число). NULL отличается и от строки нулевой длины (пустой строки). IS NOT NULL используется для проверки присутствия значения в поле. Пример 2.6: Найти студентов, которые получают стипендию: SELECT Fio, Stipendiya FROM Students WHERE Stipendiya IS NOT NULL; Предложение ORDER BY ORDER BY сортирует данные выходного набора в заданной по- следовательности. Сортировка может выполняться по нескольким полям, в этом случае они перечисляются за ключевым словом ORDER BY через запятую. По умолчанию реализуется сортировка по возрас- танию, она задается ключевым словом ASC. Для выполнения сорти- ровки в обратной последовательности необходимо указать ключевое слово DESC. Фраза ORDER BY позволяет упорядочить выбранные записи в порядке возрастания или убывания значений любого столбца или комбинации столбцов, независимо от того, присутствуют ли эти столбцы в таблице результата или нет. Фраза ORDER BY всегда должна быть последним элементом в операторе SELECT. Пример 2.7: Вывести ФИО студентов и номера их зачетных книжек. Фамилии студентов упорядочить в алфавитном порядке, а номера зачетных книжек – по убыванию: SELECT Fio, Nomer_zachetki FROM Students ORDER BY Fio, Nomer_zachetki DESC; Использование итоговых (агрегатных) функций С помощью итоговых (агрегатных) функций в SQL-запросе можно получить ряд обобщающих статистических сведений о множе- стве отобранных значений выходного набора. Пользователю доступны следующие основные итоговые функ- ции: – Count(Выражение) – определяет количество записей в вы- ходном наборе SQL-запроса; – Min/Max(Выражение) – определяют наименьшее и наибольшее из множества значений в некотором поле запроса; 7 – Avg(Выражение) – эта функция позволяет рассчитать сред- нее для множества значений, хранящихся в определенном поле запи- сей, отобранных запросом. Оно является арифметическим средним значением, то есть суммой значений, деленной на их количество. – Sum(Выражение) – вычисляет сумму множества значений, содержащихся в определенном поле записей, отобранных запросом. Чаще всего в качестве выражения выступают имена столбцов. Выражение может вычисляться и по значениям нескольких таблиц. Все эти функции оперируют со значениями в единственном столбце таблицы или с арифметическим выражением и возвращают единственное значение. Функции COUNT, MIN и MAX применимы как к числовым, так и к нечисловым полям. Функции SUM и AVG могут использоваться только в случае чис- ловых полей. Вариант COUNT(*) – особый случай использования функции COUNT . Назначение – подсчет всех строк в результирующей таблице, независимо от того, содержатся там пустые, дублирующиеся или лю- бые другие значения. Если до применения обобщающей функции необходимо исклю- чить дублирующиеся значения, следует перед именем столбца в определении функции поместить ключевое слово DISTINCT. Оно не имеет смысла для функций MIN и MAX, однако его использование может повлиять на результаты выполнения функций SUM и AVG. Ключевое слово DISTINCT может быть указано в любом запросе не более 1 раза. Особые случаи применения итоговых функций: SUM (DISTINCT <поле>) – суммирование различных зна- чений поля; AVG (DISTINCT <поле>) – среднее арифметическое различ- ных значений поля; COUNT (DISTINCT <поле>) – подсчет количества различных значений поля; COUNT (<поле>) – подсчет количества ненулевых значений поля; COUNT (*) – подсчет количества строк в результате. 8 Итоговые функции могут использоваться только в списке пред- ложения SELECT и в составе предложения HAVING. Во всех других случаях это недопустимо. Пример 2.8: Подсчитать среднюю стипендию студентов. SELECT AVG (Stipendiya) FROM Students; Пример 2.9: Определить общее количество студентов. SELECT COUNT (*) FROM Students; Предложение GROUP BY Группирование данных – это размещение данных в столбцах с повторяющимися значениями в определенном логическом порядке. Запрос, в котором присутствует GROUP BY, называется группи- рующим запросом. В нем группируются данные, полученные в ре- зультате выполнения команды SELECT, после чего для каждой от- дельной группы создается единственная суммарная строка. Стандарт SQL требует, чтобы команда SELECT и фраза GROUP BY были тесно связаны между собой. При наличии в команде SELECT фразы GROUP BY каждый элемент списка должен иметь единствен- ное значение для всей группы. Более того, команда SELECT может включать только следующие типы элементов: имена полей; итоговые функции; константы; выражения, включающие комбинации перечис- ленных выше элементов. Все имена полей, приведенные в списке предложения SELECT, должны присутствовать и во фразе GROUP BY – за исключением слу- чаев, когда имя столбца используется в итоговой функции. Однако во фразе GROUP BY могут быть имена столбцов, отсутствующие в спис- ке предложения SELECT (но данные из этих столбцов не выводятся!) Если совместно с GROUP BY используется предложение WHERE, то оно обрабатывается первым, а группированию подвергаются толь- ко те строки, которые удовлетворяют условию поиска. Пример 2.10: Определить максимальную и минимальную сти- пендии у студентов каждой группы: SELECT ID_Group, MAX(Stipendiya) AS Максимальная_стипендия, MIN(Stipendiya) AS Минимальная_стипендия FROM Students GROUP BY ID_Group; 9 Предложение HAVING Это дополнительная возможность «профильтровать» выходной набор. Предложение HAVING работает следующим образом: 1) GROUP BY разделяет строки на наборы (по типу); 2) на полученные группы накладываются условия предложения HAVING. Сравнение условий в HAVING и условий в WHERE: – WHERE накладывает ограничения на строки, HAVING – на группы; – предложение WHERE отсеивает строки до группировки, а пред- ложение HAVING – после; – в условии поиска WHERE нельзя задавать агрегатные функции; – в большинстве систем элементы предложения HAVING должны включаться в список выбора. На предложение WHERE подобное огра- ничение не распространяется. § 2.2. Команды модификации данных К командам модификации данных относятся: – INSERT INTO – команда добавления; – DELETE FROM – команда удаления; – UPDATE – команда обновления. Команда добавления Оператор INSERT INTO применяется для добавления записей в таблицу. Формат команды: INSERT INTO <имя_таблицы>[(список_столбцов)] VALUES (список_значений); Здесь имя_таблицы представляет собой либо имя таблицы ба- зы данных, либо имя обновляемого представления. Эта форма команды INSERT с параметром VALUES предназна- чена для вставки единственной строки в указанную таблицу. Список_столбцов указывает столбцы, которым будут присво- ены значения в добавляемых записях. Список может быть опущен. Тогда подразумеваются все столб- цы таблицы (кроме объявленных как счетчик), причем в порядке, установленном при создании таблицы. 10 Список_значений должен соответствовать списку столбцов следующим образом: 1) количество элементов в обоих списках должно быть одинаковым; 2) должно существовать прямое соответствие между позицией одного и того же элемента в обоих списках. Поэтому I элемент списка значений должен относиться к I столбцу в списке столбцов, II – ко II столбцу и т.д. 3) типы данных элементов в списке значений должны быть сов- местимы с типами данных соответствующих столбцов таблицы. Пример 2.11: Добавить в таблицу Teachers новую запись: INSERT INTO Teachers (Familia, Imja, Surname, Data_Rozhd, Adres, Stazh) VALUES ('Иванов', 'Иван', 'Иванович', ’1961- 07-07', 'ул. Комсомольская, д.24, кв. 26', 20); Команда удаления Команда DELETE FROM удаляет данные из таблицы: DELETE FROM <имя_таблицы> [WHERE <условие_отбора>]; Здесь имя_таблицы представляет собой либо имя таблицы ба- зы данных, либо имя обновляемого представления; усло- вие_отбора – задается условие, в соответствии с которым будут удаляться записи. Если предложение WHERE присутствует, удаляются записи из таблицы, удовлетворяющие условию отбора. Если опустить предло- жение WHERE, из таблицы будут удалены все записи без предупре- ждения и без запроса на подтверждения, однако сама таблица сохра- нится. При удалении строк с помощью DELETE эти строки сохраняют- ся в системных сегментах отката на случай восстановления. Это мо- жет потребовать значительного времени. Поэтому лучше использо- вать TRUNCATE для удаления всех данных. Пример 2.12: Удалить все предметы, на которые отводится 72 часа: DELETE FROM Lessons WHERE Kol_chas=72; 11 Команда обновления UPDATE имя_таблицы SET имя_столбца = <выраже- ние>[,...n] [WHERE <условие_отбора>] Здесь имя_таблицы – это либо имя таблицы базы данных, либо имя обновляемого представления. В предложении SET указываются имена одного и более столб- цов, данные в которых необходимо изменить. Выражение представляет собой новое значение соответствую- щего столбца и должно быть совместимо с ним по типу данных. Оператор UPDATE без предложения WHERE следует использо- вать с осторожностью, так как в этом случае будут затронуты все строки таблицы. Пример 2.13: Для студента Иванова С.В. установить стипендию в размере 2000 руб.: UPDATE Students SET Stipendiya=2000 WHERE Fio='Иванов С.В.'; § 2.3. Подзапросы Часто невозможно решить поставленную задачу путем одного запроса. Это особенно актуально, когда при использовании условия поиска в предложении WHERE значение, с которым надо сравнивать, заранее не определено и должно быть вычислено в момент выполне- ния команды SELECT. В таком случае используют законченные ко- манды SELECT, внедренные в тело другой команды SELECT. Подзапрос – это запрос, содержащийся в выражении ключевого слова WHERE другого запроса с целью дополнительных ограничений на выводимые данные. Подзапросы называют также вложенными запросами. Базовый синтаксис команды с подзапросом: SELECT имя_столбца FROM таблица WHERE имя_столбца = (SELECT имя_столбца FROM таблица WHERE условия); Подзапрос можно использовать в выражении ключевых слов WHERE или HAVING внешних операторов выбора SELECT, вставки INSERT , обновления UPDATE или удаления DELETE. Можно исполь- 12 зовать логические операции и операции сравнения типа =, >, <, IN, NOT IN, AND, OR и т.п. Правила составления подзапросов 1. Во фразах WHERE и HAVING подзапрос записывается как второй операнд условия отбора, то есть после знака операции сравне- ния (=, <, >, <=, >=, <>). 2. Текст подзапроса заключается в круглые скобки. Подзапрос может ссылаться только на один столбец в выражении своего ключе- вого слова SELECT. Исключение – это случаи, когда в главном запро- се используется сравнение с несколькими столбцами из подзапроса. 3. Ключевое слово ORDER BY нельзя использовать в подзапросе, а только во внешнем подзапросе. Вместо ORDER BY в подзапросе можно использовать GROUP BY. 4. Подзапрос, возвращающий несколько строк данных, можно использовать только в операторах, допускающих множество значе- ний, например, в IN. 5. Подзапрос нельзя использовать как аргумент функции, допус- кающей множество значений. 6. Подзапросы нельзя использовать в списках предложений ORDER BY и GROUP BY. 7. Список выбора внутреннего подзапроса, которому предше- ствует операция сравнения, может содержать только одно выражение или название столбца, и подзапрос должен возвращать единственный результат. При этом тип данных столбца, указанного в конструкции WHERE внешнего оператора, должен быть совместим c типом данных в столбце, указанным в списке выбора подзапроса. 8. В подзапросах не допускаются текстовые (TEXT) и графиче- ские (IMAGE) данные. 9. Подзапросы не могут обрабатывать свои результаты внутрен- ним образом, то есть подзапрос не может содержать конструкций ORDER BY или ключевого слова INTO. 10. Количество вложенных уровней для подзапросов не должно превышать 16. 11. Операцию BETWEEN нельзя использовать по отношению к подзапросу, но ее можно использовать в самом подзапросе. 12. В предложении SELECT подзапроса нельзя использовать символ «*» и константы (кроме EXISTS-подзапросов). 13 13. Имена столбцов в подзапросе относятся по умолчанию к таб- лице, указанной во фразе FROM подзапроса. Если они относятся к таблице внешнего запроса, необходимо задать полное (уточненное) имя столбца в виде «таблица.столбец». 14. Список в предложении SELECT состоит из имен отдельных столбцов или составленных из них выражений – за исключением слу- чая, когда в подзапросе присутствует ключевое слово EXISTS. Пример 2.14: Вывести название предмета, на изучение которого отводится максимальное количество часов: SELECT Nazvanie, Kol_chas FROM Lessons WHERE Kol_chas = (SELECT MAX (Kol_chas) FROM Lessons); Использование ключевых слов EXISTS и NOT EXISTS Предикат EXISTS (NOT EXISTS) – квантор существования, используется в логическом выражении для определения того, должен ли подчиненный запрос возвращать какие-либо записи. В языке T-SQL предикат с квантором существования представ- ляется выражением EXISTS (SELECT * FROM ...). Ключевые слова EXISTS и NOT EXISTS предназначены для использования только совместно с подзапросами. Результат их обра- ботки представляет собой логическое значение TRUE или FALSE. Для ключевого слова EXISTS результат равен TRUE в том и только в том случае, если в возвращаемой подзапросом результиру- ющей таблице присутствует хотя бы одна строка. Если результирующая таблица подзапроса пуста, результатом обработки операции EXISTS будет значение FALSE. Для ключевого слова NOT EXISTS используются правила обра- ботки, обратные по отношению к ключевому слову EXISTS. Поскольку по ключевым словам EXISTS и NOT EXISTS прове- ряется лишь наличие строк в результирующей таблице подзапроса, то эта таблица может содержать произвольное количество столбцов. Пример 2.15: Вывести список студентов, которые сдавали экза- мены. SELECT Fio FROM Students WHERE EXISTS (SELECT ID_Student FROM Progress WHERE Students.ID_Student=Progress.ID_Student); 14 § 2.4. Соединение таблиц Для соединения таблиц с одноименными столбцами или таблицы с самой собой используются алиасы или псевдонимы. Они задаются в предложении FROM через пробел после имени таблицы. При этом ис- тинное имя таблицы в базе данных не меняется. Например: SELECT R.a1, R.a2, S.b1, S.b2 FROM R t1, S t2 WHERE R.a1=S.b2; Внутреннее соединение (INNER JOIN) При внутреннем естественном соединении группируются только те строки, значения которых по соединяемым (одноименным) столб- цам совпадают: SELECT R.a1, R.a2, S.b1, S.b2 FROM R, S WHERE R.a2=S.b1; или SELECT R.a1, R.a2, S.b1, S.b2 FROM R INNER JOIN S ON R.a2=S.b1; Внешнее соединение При внешнем соединении в результирующую таблицу помеща- ются не только парные строки, но и строки, не нашедшие себе пару. По способу добавления непарных строк различают: – левое открытое соединение, когда непарные строки добавля- ются из таблицы, расположенной слева по отношению к опции JOIN; – правое открытое соединение, когда непарные строки добавля- ются из правой по отношению к JOIN таблицы; – полное открытое соединение, когда добавляются все непарные строки обеих соединяемых таблиц. Внешнее левое соединение (LEFT JOIN) При внешнем левом соединении в результирующий набор будут выбраны все строки из левой таблицы (указываемой первой). При совпадении значений по соединяемым (одноименным) столбцам значения второй таблицы заносятся в результирующий набор в соответствующие строки. При отсутствии совпадений в каче- стве значений второй таблицы проставляется значение NULL: SELECT R.a1, R.a2, S.b1, S.b2 15 FROM R LEFT JOIN S ON R.a2=S.b1; Внешнее правое соединение (RIGHT JOIN) При внешнем правом соединении в результирующий набор бу- дут выбраны все строки из правой таблицы (указываемой второй). При совпадении значений по соединяемым (одноименным) столбцам значения первой таблицы заносятся в результирующий набор в соот- ветствующие строки. При отсутствии совпадений в качестве значений первой таблицы проставляется значение NULL: SELECT R.a1, R.a2, S.b1, S.b2 FROM R RIGHT JOIN S ON R.a2=S.b1; Контрольные вопросы 1. Какие символы-заменители могут использоваться в шаблоне? 2. Как задать сортировку по нескольким полям? 3. Какие итоговые функции могут использоваться в запросах? 4. В каких случаях используется группировка выводимых запи- сей? 5. Какие операторы T-SQL используются: а) для вставки строки в таблицу; б) удаления строки из таблицы; в) обновления строки в таблице? 6. Какое соответствие должно быть между списком столбцов и списком значений в операторе INSERT? 7. Что такое подзапрос? 8. Почему возникает необходимость создания подзапросов? 9. Каковы правила составления подзапросов? 10. Меняется ли имя таблицы в базе данных при использовании алиасов? 11. В чем различие между внутренним и внешним соединения- ми? 16 Задания к лабораторной работе № 2 Задание 1: Создать с помощью приведенных операторов пример базы данных «Книжное дело», описанный в предыдущей лаборатор- ной работе (если БД отсутствует на сервере). Задание 2: С помощью операторов Insert создать программу в SQL Server Management Studio через «Создать запрос» для заполне- ния таблиц данными (по 3-5 записей). Задание 3: С помощью операторов языка T-SQL по заданиям (табл. 2.1) выполнить запросы к БД. Таблица 2.1 Варианты и списки соответствующих номеров заданий к лабораторной работе №2 Вариант Список номеров заданий 1 1 6 11 16 21 26 31 36 41 46 51 56 2 2 7 12 17 22 27 32 37 42 47 52 57 3 3 8 13 18 23 28 33 38 43 48 53 58 4 4 9 14 19 24 29 34 39 44 49 54 59 5 5 10 15 20 25 30 35 40 45 50 55 60 6 6 13 17 24 28 35 39 46 53 57 64 68 7 7 14 18 25 29 36 43 47 51 58 62 69 8 8 15 19 26 30 37 41 48 52 59 63 70 9 9 16 20 27 32 38 42 49 53 60 67 71 10 10 17 22 26 33 39 43 47 54 57 68 72 11 1 7 13 19 25 31 37 43 49 55 66 76 12 2 8 14 20 26 30 38 44 50 56 67 77 13 3 9 15 21 27 33 36 45 51 57 68 78 14 4 11 17 23 28 34 40 41 47 53 69 79 15 5 12 18 24 29 33 39 42 48 54 70 80 17 Продолжение табл. 2.1 16 6 14 21 29 32 40 49 57 65 70 75 5 17 7 15 22 30 33 41 50 58 66 71 76 3 18 8 16 23 31 34 42 51 59 67 72 77 4 19 9 17 24 32 36 44 52 60 68 73 78 2 20 10 18 25 33 37 45 53 61 69 74 79 1 Сортировка 1. Вывести все сведения об авторах из таблицы Authors в алфа- витном порядке (поле Name_author). 2. Выбрать из таблицы Books коды книг, названия и количество страниц (поля Code_book, Title_book и Pages), отсортировать резуль- тат по названиям книг (поле Title_book по возрастанию) и по полю Pages (по убыванию). 3. Вывести все сведения о закупках книг из таблицы Purchases по возрастанию цены (поле Cost). 4. Выбрать из таблицы Deliveries список поставщиков (поля Name_delivery, Phone и INN), отсортировать результат по полю INN (по убыванию). 5. Вывести все сведения об издательствах из таблицы Publishing_house в алфавитном порядке (поле Publish). Изменение порядка следования полей 6. Выбрать все поля из таблицы Publishing_house таким обра- зом, чтобы в результате порядок столбцов был следующим: Publish, City, Code_publish. 7. Выбрать все поля из таблицы Authors таким образом, чтобы в результате порядок столбцов был следующим: Name_author, Birthday, Code_author. 8. Выбрать все поля из таблицы Deliveries таким образом, чтобы в результате порядок столбцов был следующим: Name_delivery, INN, Phone, Address, Code_delivery. 9. Выбрать все поля из таблицы Books таким образом, чтобы в результате порядок столбцов был следующим: Title_book, Pages, Code_book, Code_author, Code_publish. 10. Выбрать все поля из таблицы Purchases таким образом, чтобы в результате порядок столбцов был следующим: Date_order, 18 Type_purchase, Cost, Amount, Code_purchase, Code_delivery, Code_book. Выбор некоторых полей из двух таблиц 11. Выбрать из таблицы Books названия книг и количество стра- ниц (поля Title_book и Pages), а из таблицы Authors выбрать имя со- ответствующего автора книги (поле Name_author). 12. Выбрать из таблицы Purchases дату заказа книги и тип закуп- ки (поля Date_order и Type_purchase), а из таблицы Deliveries выбрать имя соответствующего поставщика книги (поле Name_delivery). 13. Выбрать из таблицы Books названия книг и количество стра- ниц (поля Title_book и Pages), а из таблицы Publishing_house выбрать название соответствующего издательства и места издания (поля Pub- lish и City). 14. Выбрать из таблицы Books названия книг и количество стра- ниц (поля Title_book и Pages), а из таблицы Purchases выбрать соот- ветствующую дату заказа книги (поле Date_order). Точное несовпадение значений одного из полей 15. Вывести список авторов (поле Name_author) из таблицы Authors, книги которых выпущены издательствами, не находящимися в городе «Москва» (поле City из таблицы Publishing_house). 16. Вывести список издательств (поле Publish) из таблицы Pub- lishing_house, книги которых были поставлены не в текущем месяце (поле Date_order из таблицы Purchases). 17. Вывести список названий книг (поле Title_book) из таблицы Books, которые выпущены любыми издательствами, кроме издатель- ства «Питер-Софт» (поле Publish из таблицы Publishing_house). 18. Вывести список названий книг (поле Title_book) из таблицы Books, которые закупались только в розницу (поле Type_purchase из таблицы Purchases). Выбор записей по диапазону значений (BETWEEN) 19. Вывести список авторов (поле Name_author) из таблицы Authors, у которых дата рождения (поле Birthday) находится в диапа- зоне 01.01.1840 – 01.06.1860. 20. Вывести список названий книг (поле Title_book из таблицы Books) и количество экземпляров (поле Amount из таблицы Purchases), которые были закуплены в период с 12.03.2017 по 15.06.2017 (поле Date_order). 19 21. Вывести список названий книг (поле Title_book) и количество страниц (поле Pages) из таблицы Books, у которых объем в страницах укладывается в диапазон 200 – 300 (поле Pages). 22. Вывести список авторов (поле Name_author) из таблицы Authors, у которых фамилия начинается на одну из букв диапазона «А» – «Г» (поле Name_author). Выбор записей по диапазону значений (IN) 23. Вывести список названий книг (поле Title_book из таблицы Books) и количество (поле Amount из таблицы Purchases), которые были поставлены поставщиками с кодами 1,3,5,7 (поле Code_delivery из таблицы Purchases). 24. Вывести список названий книг (поле Title_book) из таблицы Books, которые выпущены следующими издательствами: «Питер- Софт», «Альфа», «Наука» (поле Publish из таблицы Publishing_house). 25. Вывести список названий книг (поле Title_book) из таблицы Books, которые написаны следующими авторами: Толстой Л.Н., Бул- гаков М.А., Чехов А.П. (поле Name_author из таблицы Authors). 26. Вывести список названий книг (поле Title_book) из таблицы Books, которые имеют следующую цену (в рублях): 250, 500, 750 (по- ле Cost из таблицы Purchases). Выбор записей с использованием LIKE 27. Вывести названия издательств (поле Publish) из таблицы Publishing_house, которые содержат в названии сочетание «софт». 28. Вывести список авторов (поле Name_author) из таблицы Authors, значения которых начинаются с «Иванов». 29. Выбрать названия компаний (поле Name_company) из табли- цы Deliveries, у которых значение оканчивается на «ский». 30. Выбрать список книг (поля Title_book) из таблицы Books, значения которых начинаются со слова «Мемуары». 31. Выбрать из справочника поставщиков (таблица Deliveries) названия компаний, телефоны и ИНН (поля Name_company, Phone и INN), у которых название компании (поле Name_company) начинает- ся с «ОАО». Выбор записей по нескольким условиям 32. Выбрать коды поставщиков (поле Code_delivery), даты зака- зов (поле Date_order) и названия книг (поле Title_book), если количе- ство книг (поле Amount) в заказе меньше 100, и цена (поле Cost) за книгу находится в диапазоне от 200 до 500. 20 33. Выбрать коды авторов (поле Code_author), имена авторов (поле Name_author), названия соответствующих книг (поле Title_book), если код издательства (поле Code_Publish) находится в диапазоне от 5 до 10, и количество страниц в книге (поле Pages) больше 150. 34. Вывести список издательств (поле Publish) из таблицы Publishing_house, в которых выпущены книги, названия которых (по- ле Title_book) начинаются со слова «Труды» и город издания (поле City) – «Екатеринбург». 35. Вывести список книг (поле Title_book) из таблицы Books, для которых фамилии авторов (поле Name_author) начинаются с буквы «А», а количество страниц в книге (поле Pages) находится в диапа- зоне от 100 до 200. Многотабличные запросы (выборка из нескольких таблиц с использованием INNER JOIN) 36. Вывести список названий компаний-поставщиков (поле Name_company) и названия книг (поле Title_book), которые они по- ставили в период с 01.01.2016 по 31.12.2017 (поле Date_order). 37. Вывести список авторов (поле Name_author), книги которых были выпущены в издательстве «Мир» (поле Publish). 38. Вывести список поставщиков (поле Name_company), которые поставляют книги издательства «Питер» (поле Publish). 39. Вывести список авторов (поле Name_author) и названия книг (поле Title_book), которые были поставлены поставщиком «ОАО Книготорг» (поле Name_company). 40. Вывести список издательств (поле Publish), книги которых были поставлены в период с 01.06.2017 по 31.08.2017 (поле Date_order). Вычисление итоговых значений с использованием агрегат- ных функций 41. Вывести общую сумму поставок книг (использовать поле Cost), выполненных «ЗАО Оптторг» (поле Name_company). 42. Вывести общее количество всех поставок (использовать лю- бое поле из таблицы Purchases), выполненных в период с 01.09.2016 по 01.12.2016 (поле Date_order). 43. Вывести среднюю стоимость (использовать поле Cost) и среднее количество экземпляров книг (использовать поле Amount) в одной поставке, где автором книги является Пушкин А.С. (поле Name_author). 21 44. Вывести все сведения о поставке (все поля таблицы Purchases), а также название книги (поле Title_book) с минимальной общей стоимостью (использовать поля Cost и Amount). 45. Вывести все сведения о поставке (все поля таблицы Purchases), а также название книги (поле Title_book) с максимальной общей стоимостью (использовать поля Cost и Amount). Изменение наименований полей 46. Вывести название книги (поле Title_book), суммарную стои- мость партии одноименных книг (использовать поля Amount и Cost), поместив в результат в поле с названием Itogo, в поставках за период с 01.01.2017 по 01.06.2017 (поле Date_order). 47. Вывести стоимость одной печатной страницы каждой книги (использовать поля Cost и Pages), поместив результат в поле с назва- нием One_page, и названия соответствующих книг (поле Title_book). 48. Вывести список названий компаний-поставщиков (поле Name_company) и соответствующую суммарную стоимость закупок книг (использовать поля Amount и Cost), поместив результат в поле с названием Value. 49. Вывести общую сумму поставок книг (использовать поле Cost) и поместить результат в поле с названием Sum_cost, выполнен- ных «ОАО Луч» (поле Name_company). 50. Вывести список авторов (поле Name_author), их возраст (ис- пользовать поле Birthday), поместив результат в поле с названием Age, а также перечень соответствующих произведений (поле Title_book). Использование псевдонимов вместо названий таблиц 51. Вывести список названий компаний-поставщиков (поле Name_company) и названия книг (поле Title_book), которые они по- ставили. 52. Вывести список книг (поле Title_book), заказы по которым были оформлены весной текущего года (поле Date_order). 53. Вывести список авторов (поле Name_author), книги которых были выпущены в издательствах «Мир», «Питер-Софт», «Наука» (по- ле Publish). 54. Вывести список издательств (поле Name_company), книги ко- торых были поставлены по цене 300 руб. (поле Cost). 55. Вывести список книг (поле Title_book), автором которых яв- ляется Достоевский Ф.М. (поле Name_author). 22 Использование агрегатных функций совместно с подзапро- сом 56. Вывести список книг (поле Title_book), у которых количество страниц (поле Pages) больше среднего количества страниц всех книг в таблице. 57. Вывести список авторов (поле Name_author), возраст которых меньше среднего возраста всех авторов в таблице (поле Birthday). 58. Вывести список заказов книг (поле Date_order), оформленных на минимальную сумму (поля Cost и Amount). 59. Вывести список книг (поле Title_book), у которых количество страниц (поле Pages) равно минимальному количеству страниц книг, представленных в таблице. 60. Вывести список заказов книг (поле Date_order), оформленных на максимальную сумму (поля Cost и Amount). Использование квантора существования в запросах 61. Вывести список издательств (поле Publish), книги которых были приобретены оптом (поле Type_Purchase). 62. Вывести список книг (поле Title_book), которые были изданы в Москве (поле City). 63. Вывести список авторов (поле Name_author), книг которых нет в таблице Books. 64. Вывести список названий компаний-поставщиков (поле Name_company), которые поставили книги на сумму от 3000 руб. (по- ле Cost). 65. Вывести список книг (поле Title_book), которые были по- ставлены поставщиком «ЗАО Квантор» (поле Name_company). Оператор обработки данных Update 66. Изменить в таблице Books содержимое поля Pages на 300, ес- ли код автора (поле Code_author) =5 и название книги (поле Title_book) = «Мемуары». 67. Изменить в таблице Purchases содержимое поля Type_purchase на «опт», если количество книг в заказе (поле Amount) больше 100. 68. Изменить в таблице Deliveries содержимое поля Address на «нет сведений», если значение данного поля является пустым. 69. Увеличить в таблице Purchases цену (поле Cost) на 20%, если заказы были оформлены в текущем году (условие по полю Date_order). 23 70. Изменить в таблице Publishing_house содержимое поля City на «неизвестен», если значение данного поля является пустым. Оператор обработки данных Insert 71. Добавить в таблицу Purchases новую запись, причем так, что- бы код покупки (поле Code_purchase) был автоматически увеличен на единицу, а в тип закупки (поле Type_purchase) внести значение «опт». 72. Добавить в таблицу Books новую запись, причем вместо ключевого поля поставить код (поле Code_book), автоматически уве- личенный на единицу от максимального кода в таблице, вместо названия книги (поле Title_book) написать «Наука. Техника. Иннова- ции». 73. Добавить в таблицу Publish_house новую запись, причем вме- сто ключевого поля поставить код (поле Code_publish), автоматиче- ски увеличенный на единицу от максимального кода в таблице, вме- сто названия города – «Москва» (поле City), вместо издательства – «Наука» (поле Publish). 74. Добавить в таблицу Authors новую запись, причем вместо ключевого поля поставить код (поле Code_author), автоматически увеличенный на единицу от максимального кода в таблице, вместо ФИО автора – «Лермонтов Михаил Юрьевич» (поле Name_author), вместо даты рождения автора – «15.10.1814» (поле Birthday). 75. Добавить в таблицу Deliveries новую запись, причем вместо ключевого поля поставить код (поле Code_delivery), автоматически увеличенный на единицу от максимального кода в таблице, вместо ФИО ответственного лица (поле Name_delivery) написать «Иванов Петр Алексеевич». Оператор обработки данных Delete 76. Удалить из таблицы Publishing_house все записи, у которых не указан город (поле City пустое). 77. Удалить из таблицы Authors все записи, у которых нет имени автора в поле Name_Author. 78. Удалить из таблицы Purchases все записи, у которых количе- ство книг в заказе (поле Amount) = 0. 79. Удалить из таблицы Books все записи, у которых количество страниц (поле Pages) = 0. 80. Удалить из таблицы Deliveries все записи, у которых не ука- зан ИНН (поле INN пустое). |