Решение задач в соответствии с вариантом на выполнение sql запросов к учебной базе данных на языке sql
 Скачать 20.25 Kb.
|
|
Контрольная точка №1 Контрольная точка представляет собой решение задач в соответствии с вариантом на выполнение SQL запросов к учебной базе данных на языке SQL. Для выполнения контрольной точки необходимо подключить учебную БД Pubs к серверу БД SQL Server, скачав скрипты с сайта Microsoft, решить соответствующие задания и сформировать отчет. Состав отчета: 1) Титульный лист; 2) цель работы; 3) тексты SQL-запросов и их содержательная интерпретация; 4) результаты выполнения запросов и их интерпретация; 5) выводы. Вариант определяется последней цифрой зачетной книжки (студенческого билета). Для уточнения номера студенческого билета можно обратиться в деканат. Работы, выполненные не по своему варианту, приниматься не будут!
Перечень заданий: 1) Выбрать имена и фамилии авторов книг. 2) Выбрать имена и фамилии авторов, проживающих в Калифорнии. 3) Выбрать информацию о книгах, объеме (стоимость) продаж которых в текущем году меньше стоимости предварительной продажи. Информация о книгах должна включать тип книги, идентификатор и цену книги. 4) Выбрать информацию об авторах, проживающих в штате Калифорния или в городе Salt Lake City. Информация об авторах должна включать идентификатор автора, город и штат проживания. 5) Выбрать все идентификаторы и цены книг, причем цена книги должна лежать в диапазоне от 5 до 10 долларов. В SQL запросе использовать предикат BETWEEN. 6) Выбрать все идентификаторы и цены книг по современной и традиционной кулинарии и по бизнесу. В запросе использовать предикат IN. 7) Выбрать информацию об авторах, проживающих в городах, название которых начинается со строки “spring”. Информация об авторах должна включать имя и фамилию автора, а также штат и город проживания. 8) Выбрать информацию о книгах, идентификаторы которых начинаются буквой “B”, а кончаются строкой “1342”. Информация о книгах должна включать тип, идентификатор и цену книги. 9) Выбрать информацию о книгах, идентификаторы которых начинаются буквой “B”, заканчиваются строкой “1342”, а вторым символом идентификатора являются буквы “A”, “U” или “N”. Информация о книгах должна включать тип, идентификатор и цену книги. 10) Выбрать имена и фамилии всех авторов, упорядоченные по возрастанию фамилий авторов. 11) Выбрать имена и фамилии всех авторов, упорядоченные в первую очередь по возрастанию фамилий и, во вторую очередь, по возрастанию имен. 12) Выбрать информацию о книгах, упорядоченную по возрастанию объема продаж (по стоимости). Информация о книгах должна включать идентификатор, цену, объем продаж (по количеству) и объем продаж (по стоимости). 13) То же, что 12, но использовать упорядочение по убыванию. 14) Выбрать информацию о всех книгах, упорядоченную по убыванию типа книги и числа проданных книг. Информация о книгах должна включать идентификатор и тип книги, а также число проданных книг. 15) Определить среднюю цену книги. 16) Определить среднюю цену книг по бизнесу. 17) Определить среднюю цену и стоимость всех книг по бизнесу и современной кулинарии 18) Определить число авторов, проживающих в Калифорнии. 19) Определить среднюю цену и сумму цен на книги по бизнесу и современной кулинарии отдельно для каждого типа книги. 20) Определить среднюю цену и сумму цен на книги по бизнесу и современной кулинарии для каждой комбинации типа книги и идентификатора издателя. 21) Выбрать те типы книг, средняя цена дорогих экземпляров (стоимостью более 10 долларов) которых превышает 20 долларов. В выбираемые данные помимо типа книги включить и среднюю цену дорогих экземпляров. 22) Подсчитать число строк в таблице authors, включающих одинаковые идентификаторы авторов. В выбираемые данные включить идентификатор автора и соответствующее ему число повторяющихся строк. 23) Выбрать названия книг и имена выпустивших их издателей. 24) То же, что и 23, но в разделе FROM запроса использовать операцию соединения JOIN. 25) Произвести проекцию на столбцы title и pub_name декартова произведения таблиц titles и publishers. 26) Определить среднюю цену выпускаемых каждым издателем книг. В выбираемые данные включить имя издателя и среднюю цену книги. 27) То же, что и 26, но в разделе FROM запроса использовать операцию соединения JOIN. 28) Определить, кто из авторов написал какую книгу по психологии. В выбираемые данные включить имя и фамилию автора, а также название книги. 29) То же, что и 28, но в разделе FROM запроса использовать операцию соединению JOIN. 30) Выбрать все столбцы результата эквисоединения таблиц titles и publishers по идентификатору издателя. 31) Выбрать все столбцы таблицы titles и столбец pub_name таблицы publishers результата эквисоединения данных таблиц по идентификатору издателя. 32) Выбрать все книги издательства Algodata Infosysytems. В запросе использовать подзапрос для определения нужного идентификатора издателя. В условии поиска использовать предикат “=”. В выбираемые данные включить название книги. 33) Выбрать всех издателей литературы по бизнесу. В запросе использовать подзапрос для выборки нужных идентификаторов издателей. В условии поиска использовать предикат IN. В выбираемые данные включить имя издателя. 34) Выбрать всех издателей литературы по бизнесу. В запросе использовать подзапрос, формирующий промежуточную таблицу, в которую включаются те строки из таблицы titles, которые могут “эквисоединиться” по идентификатору издателя со строками из таблицы publishers и которые представляют тип книг по бизнесу. В условии поиска основного запроса использовать предикат EXISTS. В выбираемые данные включить имя издателя. 35) Выбрать издателей, не выпускающих книг по бизнесу. Дополнительные условия формирования запроса взять из варианта 34. 36) Выбрать издателей, не выпускающих книг по бизнесу. Дополнительные условия формирования запроса взять из варианта 33. 37) Выбрать тип и цену для всех книг, цена которых не превышает средней. В запросе использовать подзапрос, определяющий среднюю цену книг. 38) Выбрать тип и среднюю цену книг данного типа, причем эта средняя цена должна быть меньше средней цены всех книг. В запросе использовать подзапрос, определяющий среднюю цену всех книг. 39) Определить города и штаты проживания каждого из авторов и издателей в виде одной результирующей таблицы. 40) Определить все типы книг. Типы книг в результирующей таблице не должны повторяться. Вывести типы книг в порядке возрастания. 41) Определить все города, в которых проживают авторы. Названия городов в результирующей таблице не должны повторяться. Вывести названия городов в порядке убывания. 42) Определить все штаты, в которых проживают авторы. Названия штатов в результирующей таблице не должны повторяться. Вывести названия штатов в порядке возрастания. 43) Определить страны, в которых расположены издательства книг. Названия стран в результирующей таблице не должны повторяться. Вывести названия стран в порядке убывания. 44) Определить все города, в которых проживают авторы и находятся издательства. Названия городов в результирующей таблице не должны повторяться. Вывести названия городов в порядке возрастания. 45) Определить все штаты, в которых проживают авторы и находятся издательства. Названия штатов в результирующей таблице не должны повторяться. Вывести названия штатов в порядке убывания. 46) Определить города и штаты совместного проживания авторов и издателей. (В запросе неявно реализуется операция пересечения). 47) Определить города и штаты проживания авторов, в которых нет издательств. (В запросе неявно реализуется операция разности). 48) Определить города и штаты нахождения издательств, в которых не проживают авторы. (В запросе неявно реализуется операция разности). 49) Определить, какой город в каком штате находится. Вывести названия городов в порядке возрастания. 50) Определить число книг, название которых начинается со строки “The” и заканчивается буквой “e”. 51) Определить авторов на букву “G”, проживающих в штатах Теннесси, Иллинойс, Канзас, Орегон или Калифорния, которые опубликовали книги, в которых есть слово из трех букв, причем средней буквой является буква “a”. 52) Определить минимальную, максимальную и среднюю цену для каждого из типов книг. Выводимые данные должны быть упорядочены по убыванию типа книг. 53) Определить минимальную и максимальную цену для каждого из типов книг. В результирующую таблицу не включать те типы книг, для которых разность между максимальной и средней ценой меньше 7 долларов. 54) Вычислить среднюю цену всех книг и медиану цены. Под медианой понимается среднее значение всех различных цен всех книг. 55) Определить, какие авторы в каких издательствах опубликовали сколько книг. 56) Определить книги, авторы и издатели которых живут в одном городе. 57) Определить для каждого штата минимальную, максимальную и среднюю цену книг авторов, проживающих в одном штате (кроме штата Калифорния). 58) Определить, какие авторы опубликовали какие книги в США по традиционной кулинарии или в Германии по компьютерам. 59) Найти цену самой дешевой книги (книг), вышедшей в США. В запросе использовать операцию группирования. 60) Найти авторов самых дорогих книг, вышедших в США. В запросе использовать подзапрос и операцию группирования. 61) Найти авторов, у которых вышли самые нераспродаваемые книги. 62) Найти цену самой дорогой книги (книг), вышедшей в США. В запросе использовать подзапрос. 63) Определить число книг по компьютерам, выпущенных каждым издательством. 64) Определить авторов из городов, начинающихся с букв “A”, “B” или “C” или имеющих в своем составе слово “Salt”, и написавших книги, в названии которых есть определенный или неопределенный артикль английского языка. 65) Определить города и штаты проживания авторов и издателей, за исключением городов и штатов их совместного проживания. (В запросе неявно реализуется операция симметрической разности). 66) Определить названия и цену самых дешевых книг, вышедших в США. (Самые дешевые книги имеют минимальную цену). 67) Определить издательство, в котором опубликовано меньше всего книг. 68) Найти книги, цена которых меньше цены каждой из книг по традиционной кулинарии. 69) Определить местонахождение издательств, цена каждой книги которых меньше 22 долларов. В запросе использовать подзапросы и предикат с квантором. 70) Определить штаты (кроме штатов Индиана, Канзас, Юта), в которых сумма цен выпущенных в них книг больше суммы цен книг, выпущенных в городе Вашингтон. 71) Найти издательство, выпустившее свою самую дорогую книгу с наиболее низкой ценой среди всех издательств. В запросе использовать подзапрос, определяющий максимальные цены книг, выпущенные каждым издательством. 72) Определить полную информацию об издателях книг по компьютерам, авторы которых живут в США (за исключением штата Юта). В запросе использовать подзапросы. 73) Определить книги, стоимости которых составляют не более средней стоимости по издательству, где издавались эти книги. 74) Определить для каждого штата число находящихся в нем издательств. 75) Определить число городов, в которых выпускается литература по компьютерам. В запросе использовать подзапрос. 76) Определить авторов, хотя бы одна книга которых была опубликована в штате Массачусетс. В запросе использовать подзапросы и предикат с квантором. 77) Найти издательства, среди изданных книг которых найдется хоть одна книга по компьютерам стоимостью более двух долларов. В запросе использовать подзапрос и предикат с квантором. 78) Определить штаты, во всех издательствах которых все изданные книги имеют цену более 10 долларов. В запросе использовать подзапросы и предикат с квантором. 79) Определить издательства, для каждой книги которых выполняется условие: “Если книга выпущена в данном издательстве, то хотя бы один из авторов книги проживает в штате, в котором находится издательство, некоторые выпущенные книги которого посвящены компьютерам”. 80) Выбрать все столбцы таблицы titles. 81) Выбрать все столбцы декартова произведения таблиц titles и publishers. 82) Определить книги, число продаж для которых неопределено. 83) Определить минимальную и максимальную цену книг, выпущенных издательствами. 84) Определить авторов, хотя бы одна книга которых была опубликована в штате Массачусетс. В запросе не использовать предикаты с квантором. 85) Найти издательства, среди изданных книг которых найдется хоть одна книга по традиционной кулинарии стоимостью от 12 до 16 долларов. В запросе не использовать предикаты с квантором. 86) Определить для каждого издательства число изданных им дешевых книг (ценой менее 13 долларов). 87) Определить для штатов число издательств, в которых выпускаются только книги ценой более 7 долларов. В запросе использовать подзапросы и предикат с квантором. 88) Определить, сколько авторов имеет каждая изданная книга. 89) Определить штаты и число находящихся в них издательств, выпустивших книги. 90) Определить издательства, не выпустившие книг. 91) Определить неопубликованные в издательствах книги. 92) Определить авторов, работающих по контракту и имеющих телефон с кодом города 415 (первые три цифры номера телефона). 93) Определить номера телефонов авторов, проживающих на Седьмой Авеню (Seventh Av.) 94) Определить книги, выпущенные в период с 1 июля 1991 г. по 30 октября 1991 г. (По умолчанию сервер работает с датами в формате xx/yy/zz как с последовательностями месяц/день/год). 95) Вычислить для каждого типа книг среднее арифметическое минимальной и максимальной цены. Результат упорядочить по убыванию значений. 96) Определить временные интервалы, в рамках которых опубликованы книги разных типов. P.S. Скрипты учебной базы данных можно здесь: https://www.microsoft.com/en-us/download/ https://www.codeproject.com/Articles/20987/HowTo-Install-the-Northwind-and-Pubs-Sample-Databa Подходящую методику установки БД Pubs можно найти на сайтах: https://msdn.microsoft.com/ru-ru/library/mt710790.aspx https://msdn.microsoft.com/ru-ru/library/8b6y4c7s(v=vs.110).aspx https://msdn.microsoft.com/ru-ru/library/ms239722(v=vs.110).aspx https://technet.microsoft.com/ru-ru/library/ms143221(v=sql.105).aspx https://dzone.com/articles/how-to-attach-pubs-database-in-sql-server С ERD диаграммами БД Pubs можно познакомиться на сайте: https://devjef.wordpress.com/tag/pubs/ |