C помощью управляющей программы SQL Server Management Studio либо программы DBeaver cоединитесь с сервером баз данных, используя индивидуальные имя и пароль, которые следует получить у преподавателя. СУБД Microsoft SQL Server находится по адресу 172.20.180.239. Из домена компьютерных классов КФТТ сервер баз данных также доступен по имени server. Для входа в СУБД выберите режим «SQL Server Authentication».
Ваш вариант (в тех заданиях, где требуется работа по вариантам) соответствует последней цифре вашего логина. Все действия, которые вы будете совершать, должны осуществляться только с помощью SQL-инструкций. Результаты работы оформляются в виде отчёта: к каждой написанной команде на языке SQL делается скриншот с результатами выполнения этой команды.
В начале работы нажмите кнопку New Query на панели инструментов и перейдите к базе данных pubs с помощью команды USE pubs. Все выполняемые SQL-инструкции сохраняйте в текстовом файле на вашем рабочем носителе с помощью команды меню File/Save. При нажатии на кнопку Execute или Debug запрос будет отправлен на выполнение СУБД. В случае проблем с отладкой используйте только вариант Execute. Если вы сомневаетесь в синтаксисе команды, откройте online-помощь.
При работе с SQL Server Management Studio запускаются на выполнение все команды, которые вы записали в файл запросов. Чтобы выполнить только один запрос, выделите его мышкой и нажмите на кнопку Execute. Кроме того, можно закомментировать неиспользуемые запросы с помощью символов /* несколько строк */, либо с помощью двойного дефиса -- (в этом случае комментируются символы от знака -- до конца строки).
Задания:
Часть 1. Команда SELECT
1. Подключитесь к базе данных Pubs. Структура базы данных Pubs. Выведите на экран полное содержимое таблиц authors, titles, titleauthor, publishers, pub_info. Выведите последнюю таблицу так, чтобы имена столбцов были на русском языке.
Запросы к одной таблице
2. Создайте запрос, который покажет фамилии и имена авторов, проживающих в городе Salt Lake City. Следующий запрос должен показать, сколько авторов проживает в этом городе.
3. Напишите запрос, выводящий отсортированный по алфавиту список авторов, номера телефонов которых содержат цифру вашего варианта.
4. Создайте запрос, который покажет, сколько различных штатов представлено в таблице Authors.
5. Подсчитайте с помощью запроса, сколько авторов проживает в каждом штате.
6. Выведите отсортированный по алфавиту список тем, по которым выпускались книги (повторы не допускаются).
7. Напишите запрос, выводящий отсортированный по убыванию стоимости список названий книг и их цену (в рублях), игнорируя книги, цена на которые не указана.
8. Напишите запрос, выводящий названия книг, выпущенных по темам "popular_comp" и "psychology".
9. Есть ли названия книг, содержащие первые две буквы вашего имени (в английской транскрипции)?
10(варианты 0-4). Напишите запрос, выводящий название книги, у которой был самый высокий объём продаж.
10(варианты 5-9). Напишите запрос, выводящий тему, по которой издано наибольшее количество книг.
Запросы к связанным таблицам
11. Создайте запрос, выводящий список названий книг вместе с названием выпустившего книгу издательства.
12. Создайте запрос, выводящий следующую таблицу: фамилия, имя автора, город проживания автора, название книги автора, год выпуска книги. Таблица должна быть отсортирована по фамилии автора.
13. Напишите запрос, определяющий, какие книги написаны автором Albert Ringer.
14. Покажите, какие книги имеют более одного автора.
15 (варианты 0-4). Напишите запрос, подсчитывающий, сколько написал книг и сколько всего денег заработал на них каждый из авторов. Доход автора от одной книги есть стоимость книги * объём продаж * % гонорара для всех авторов * % гонорара для одного автора (если их несколько).
15 (варианты 5-9). Представьте, что необходимо отправить рекламную информацию от издательств авторам, которые публиковались после 1992 года. Для этого создайте запрос, формирующий таблицу, содержащую следующие столбцы: фамилия, имя автора, адрес и город автора, название издательства, где публиковался автор, рекламная информация издательства (из таблицы pub_info).
Чуть более сложные запросы
16. Напишите запрос, определяющий, какие книги написаны совместно авторами Ringer Anne и Ringer Albert.
17. Вывести список авторов, которые публиковались в разных издательствах.
Часть 2. Команды CREATE, ALTER, INSERT, UPDATE, DELETE
1. Перейдите в свою базу данных (имя вашей базы данных совпадает с вашим логином). С помощью команды CREATE TABLE создайте аналог таблицы Authors, с некоторыми изменениями. Поле au_id в вашей таблице будет иметь целый тип, будет счётчиком и первичным ключом, а поле state будет отсутствовать. Кроме того, переделайте все типы varchar на типы nvarchar.
2. После создания таблицы с помощью команды INSERT скопируйте содержание таблицы Authors из базы данных pubs в созданную вами таблицу. Также с помощью команды INSERT введите в созданную таблицу нескольких авторов из России.
3. Командой CREATE TABLE создайте таблицу Countries, содержащую два поля: id целого типа и name типа nvarchar(200). Введите в эту таблицу как минимум две страны: США и Россия. С помощью команды ALTER TABLE измените таблицу Authors: создайте новое поле idCountry, которое будет являться внешним ключом к таблице Countries.
4. C помощью нескольких команд UPDATE установите значения этого поля так, чтобы авторы "попали" в разные страны. Критерий отбора может быть любым; в поле idCountry не должно остаться значений NULL. Подсчитайте с помощью SELECT-запроса, сколько авторов находится в каждой стране.
5. С помощью нескольких команд UPDATE замените названия городов в таблице Authors для введённых вами стран так, чтобы эти названия соответствовали городам указанной страны. Например, все авторы, которые проживали в Oakland, будут жить, например, в Paris, если для этих авторов указана в качестве страны Франция.
6. C помощью команды SELECT ... INTO сделайте копию таблицы Authors с именем AuthorsCopy. С помощью команды DELETE из таблицы AuthorsCopy удалите всех авторов, в поле zip которых содержится цифра вашего варианта. С помощью команды ALTER TABLE удалите из этой же таблицы поле contract. И, наконец, с помощью команды UPDATE в таблице Authors замените в значении поля zip первую цифру на цифру вашего варианта для тех авторов, кого не оказалось в таблице AuthorsCopy.
Заключительные замечания
Переходя к выполнению курсового проекта вы можете удалить все таблицы, которые создали в вашей базе данных при выполнении вышеприведённых заданий. После завершения курса "Базы данных" вы также можете удалить всё содержимое вашей базы данных. В начале следующего семестра содержимое всех студенческих баз данных очищается. Поэтому, если у вас оказался долг по практике, заранее позаботьтесь о локальной копии содержимого вашей базы данных.
