Знакомства с MS SQL Server

Вывести количество стран по количеству букв в названии. Результат отсортировать по убыванию.

Ожидается, что через 20 лет население мира вырастет на 10%. Вывести список континентов с прогнозируемым населением:

Вывести список континентов, где разница по площади между наибольшими и наименьшими странами не более в 10000 раз:

Вывести среднюю длину названий Африканских стран.

Вывести список континентов, у которых средняя плотность среди стран с населе- нием более 1 млн. чел. больше, чем 30 чел. на кв. км.

Лабораторная работа 6

Соединения таблиц

Цель работы

Изучить неявные соединения таблиц.

Изучить явные соединения таблиц.

Изучить внутреннее соединение таблиц.

Изучить внешнее соединение таблиц.

Изучить соединения таблиц со своей копией.

Теоретическая часть

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

Когда выбор данных осуществляется из нескольких таблиц, в конструкции SELECT для каждого поля указывается таблица в виде <таблица>.<поле>. Если название поля уникальное, то можно его указать без таблицы, иначе это обязательно, чтобы избежать коллизий. Чтобы не повторить длинные названия таблиц, можно использовать псевдоним для таблиц. Псевдоним указывается в конструкции как FROM.

При неявном соединении таблиц формат конструкций FROM и WHERE имеет следу- ющий вид:

FROM <таблица1> [псевдоним1], <таблица2> [псевдоним2]… [WHERE <условие_соединения> [AND <условие_поиска>]… ]

Таким образом, если более одной таблицы присутствует в конструкции FROM, то их разделяют запятой.

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

Для явного соединения таблиц используется команда JOIN. У явного соединения есть следующие разновидности:

Внутреннее соединение - осуществляется с помощью команды INNER JOIN. Из двух таблиц берутся только связанные строки. INNER JOIN имеет следующий формат записи:

<таблица1> INNER JOIN <таблица2> ON <таблица1>.<связующее_поле> = <таб- лица2>.<связующее_поле>.

При внутреннем соединении слово INNER можно пропустить.

Внешнее соединение - осуществляется с помощью команды OUTER JOIN. OUTER JOIN имеет следующий формат записи:

<таблица1> LEFT | RIGHT | FULL OUTER JOIN <таблица2> ON <таблица1>.<связую- щее_поле> = <таблица2>.<связующее_поле>.

У внешнего соединения есть три разновидности:

Левое внешнее соединение LEFT OUTER JOIN - из таблицы, название которой явля- ется левым операндом команды JOIN, выбираются все строки, из второй таблицы - только те записи, которые имеют связь с первой таблицей.

Правое внешнее соединение RIGHT OUTER JOIN - из таблицы, название которой является правым операндом команды JOIN, выбираются все строки, из первой таблицы только те записи, которые имеют связь со второй таблицей.

Полное внешнее соединение FULL OUTER JOIN - из обоих таблиц выбираются все строки.

При использовании внешних соединений, слово OUTER можно пропустить.

Перекрестное соединение CROSS JOIN - декартово произведение двух таблиц.

CROSS JOIN имеет следующий формат записи:

<таблица1> CROSS JOIN <таблица2>.

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

Используя соединение, можно связать таблицу с собой. При таком соединении псевдо- ним обязателен.

Набор данных, полученных из нескольких таблиц, не отличается от набора, получен- ного из одной таблицы. Ему тоже можно применить группировки и т.д.

Практическая часть

Даны следующие таблицы:

Таблица 1. Факультет

Аббревиатура	Название
Ен	Естественные науки
Гн	Гуманитарные науки
Ит	Информационные технологии
Фм	Физико-математический

Таблица 2. Кафедра

Шифр	Название	Факультет
вм	Высшая математика	ен
ис	Информационные системы	ит
мм	Математическое моделирование	фм
оф	Общая физика	ен
пи	Прикладная информатика	ит
эф	Экспериментальная физика	фм

Таблица 3. Сотрудник

Таб_номер	Шифр	Фамилия	Должность	Зарплата	Шеф
101	пи	Прохоров П.П.	зав. кафедрой	35 000,00 р.	101
102	пи	Семенов С.С.	преподаватель	25 000,00 р.	101
105	пи	Петров П.П.	преподаватель	25 000,00 р.	101
153	пи	Сидорова С.С.	инженер	15 000,00 р.	102
201	ис	Андреев А.А.	зав. кафедрой	35 000,00 р.	201
202	ис	Борисов Б.Б.	преподаватель	25 000,00 р.	201
241	ис	Глухов Г.Г.	инженер	20 000,00 р.	201
242	ис	Чернов Ч.Ч.	инженер	15 000,00 р.	202
301	мм	Басов Б.Б.	зав. кафедрой	35 000,00 р.	301
302	мм	Сергеева С.С.	преподаватель	25 000,00 р.	301
401	оф	Волков В.В.	зав. кафедрой	35 000,00 р.	401
402	оф	Зайцев З.З.	преподаватель	25 000,00 р.	401
403	оф	Смирнов С.С.	преподаватель	15 000,00 р.	401
435	оф	Лисин Л.Л.	инженер	20 000,00 р.	402
501	вм	Кузнецов К.К.	зав. кафедрой	35 000,00 р.	501
502	вм	Романцев Р.Р.	преподаватель	25 000,00 р.	501
503	вм	Соловьев С.С.	преподаватель	25 000,00 р.	501
601	эф	Зверев З.З.	зав. кафедрой	35 000,00 р.	601
602	эф	Сорокина С.С.	преподаватель	25 000,00 р.	601
614	эф	Григорьев Г.Г.	инженер	20 000,00 р.	602

Таблица 4. Специальность

Номер	Направление	Шифр
01.03.04	Прикладная математика	мм
09.03.02	Информационные системы и технологии	ис
09.03.03	Прикладная информатика	пи
14.03.02	Ядерные физика и технологии	эф
38.03.05	Бизнес-информатика	ис



Таблица 5. Дисциплина

Код	Объем	Название	Исполнитель
101	320	Математика	вм
102	160	Информатика	пи
103	160	Физика	оф
202	120	Базы данных	ис
204	160	Электроника	эф
205	80	Программирование	пи
209	80	Моделирование	мм

Таблица 6. Заявка

Номер	01.03.04	09.03.02	09.03.03	14.03.02	38.03.05
Код	101	205	209	101	102	103	202	205	209	101	102	103	202	205	101	102	103	204	101	103	202	209

Таблица 7. Зав_кафедрой

Таб_номер	101	201	301	401	501	601
Стаж	15	18	20	10	18	8

Таблица 8. Инженер

Таб_номер	153	241	242	435	614
Специальность	электроник	электроник	программист	электроник	программист

Таблица 9. Преподаватель

Таб_номер	Звание	Степень
101	профессор	д. т.н.
102	доцент	к. ф.-м. н.
105	доцент	к. т.н.
201	профессор	д. ф.-м. н.
202	доцент	к. ф.-м. н.
301	профессор	д. т.н.
302	доцент	к. т.н.
401	профессор	д. т.н.
402	доцент	к. т.н.
403	ассистент	-
501	профессор	д. ф.-м. н.
502	профессор	д. ф.-м. н.
503	доцент	к. ф.-м. н.
601	профессор	д. ф.-м. н.

Таблица 10. Студент

Рег_номер	Номер	Фамилия
10101	09.03.03	Николаева Н. Н.
10102	09.03.03	Иванов И. И.
10103	09.03.03	Крюков К. К.
20101	09.03.02	Андреев А. А.
20102	09.03.02	Федоров Ф. Ф.
30101	14.03.02	Бондаренко Б. Б.
30102	14.03.02	Цветков К. К.
30103	14.03.02	Петров П. П.
50101	01.03.04	Сергеев С. С.
50102	01.03.04	Кудрявцев К. К.
80101	38.03.05	Макаров М. М.
80102	38.03.05	Яковлев Я. Я.

Таблица 11. Экзамен

Дата	Код	Рег_номер	Таб_номер	Аудитория	Оценка
05.06.2015	102	10101	102	т505	4
05.06.2015	102	10102	102	т505	4
05.06.2015	202	20101	202	т506	4
05.06.2015	202	20102	202	т506	3
07.06.2015	102	30101	105	ф419	3
07.06.2015	102	30102	101	т506	4
07.06.2015	102	80101	102	м425	5
09.06.2015	205	80102	402	м424	4
09.06.2015	209	20101	302	ф333	3
10.06.2015	101	10101	501	т506	4
10.06.2015	101	10102	501	т506	4
10.06.2015	204	30102	601	ф349	5
10.06.2015	209	80101	301	э105	5
10.06.2015	209	80102	301	э105	4
12.06.2015	101	80101	502	с324	4
15.06.2015	101	30101	503	ф417	4
15.06.2015	101	50101	501	ф201	5
15.06.2015	101	50102	501	ф201	3
15.06.2015	103	10101	403	ф414	4
17.06.2015	102	10101	102	т505	5

Пример 1: Выбрать факультет и кафедры, используя неявное соединение. Результат отсортировать по алфавиту:

SELECT

Ф.Название AS Факультет

, К.Название AS Кафедра

FROM

Факультет Ф, Кафедра К

WHERE

Ф.Аббревиатура = К.Факультет ORDER BY

Факультет, Кафедра

Пример 2: Выбрать факультет и кафедры, используя явное соединение. Результат от- сортировать по алфавиту:

SELECT

Ф.Название AS Факультет

, К.Название AS Кафедра

FROM

Факультет Ф

INNER JOIN Кафедра К ON Ф.Аббревиатура = К.Факультет

ORDER BY

Факультет, Кафедра

Пример 3: Выбрать все факультеты и их кафедры, если существуют. Результат отсор- тировать по алфавиту:

SELECT

Ф.Название AS Факультет

, К.Название AS Кафедра

FROM

Факультет Ф

LEFT OUTER JOIN Кафедра К ON Ф.Аббревиатура = К.Факультет

ORDER BY

Факультет, Кафедра

Пример 4: Вывести из таблиц «Кафедра», «Специальность» и «Студент» данные о сту- дентах:

SELECT

С.Фамилия

, П.Направление

, К.Название AS Кафедра

FROM

Студент С

INNER JOIN Специальность П ON С.Номер = П.Номер INNER JOIN Кафедра К ON П.Шифр = К.Шифр

Пример 5: Вывести для каждого сотрудника фамилию, должность, зарплату и фамилию его непосредственного руководителя:

SELECT

С.Фамилия

, С.Должность

, С.Зарплата

, П.Фамилия AS Руководитель

FROM

Сотрудник С

INNER JOIN Сотрудник П ON С.Шеф = П.Таб_номер

Пример 6: Вывести список студентов, сдавших хотя бы один экзамен. По правилам соединения, студенты, не сдававшие экзамены, в выборке представлены не будут:

SELECT

С.Фамилия

FROM

Студент С

INNER JOIN Экзамен Э ON С.Рег_номер = Э.Рег_номер

GROUP BY

С.Фамилия

Пример 7: Вывести из таблиц «Студент» и «Экзамен» учетные номера и фамилии сту- дентов, а также количество сданных экзаменов и средний балл для каждого студента:

SELECT

С.Фамилия

, COUNT(Э.Оценка) AS [Количество экзаменов]

, AVG(Э.Оценка) AS [Средний балл]

FROM

Студент С

INNER JOIN Экзамен Э ON С.Рег_номер = Э.Рег_номер

GROUP BY

С.Фамилия

Пример 8: Вывести список заведующих кафедрами и их зарплаты, и стаж работы: SELECT

С.Фамилия

, С.Зарплата

, З.Стаж

FROM

Сотрудник С

INNER JOIN Зав_кафедрой З ON С.Таб_номер = З.Таб_номер

Пример 9: Вывести список кандидатов и докторов физико-математических наук: SELECT

С.Фамилия

, П.Степень

FROM

Сотрудник С

INNER JOIN Преподаватель П ON С.Таб_номер = П.Таб_номер

WHERE

П.Степень IN ('к.ф.-м.н.', 'д.ф.-м.н.')

Пример 10: Вывести название дисциплины, фамилию, должность и степень препода- вателя, дату и место проведения экзаменов в хронологическом порядке:

SELECT DISTINCT

Д.Название AS Дисциплина

, С.Фамилия, С.Должность

, П.Степень, Э.Дата, Э.Аудитория

FROM

Экзамен Э

INNER JOIN Дисциплина Д ON Э.Код = Д.Код

INNER JOIN Сотрудник С ON Э.Таб_номер = С.Таб_номер INNER JOIN Преподаватель П ON Э.Таб_номер = П.Таб_номер

ORDER BY

Э.Дата

Пример 11: Вывести фамилию преподавателей и количество их экзаменов: SELECT

С.Фамилия, COUNT(Э.Дата) AS [Количество экзаменов]

FROM

Экзамен Э

INNER JOIN Сотрудник С ON Э.Таб_номер = С.Таб_номер

GROUP BY

С.Фамилия

Пример 12: Вывести список студентов, не сдавших ни одного экзамена: SELECT

С.Фамилия

FROM

Студент С

LEFT OUTER JOIN Экзамен Э ON С.Рег_номер = Э.Рег_номер

WHERE

Э.Рег_номер IS NULL

Задание

Вывести из таблиц «Кафедра», «Специальность» и «Студент» данные о студентах, которые обучаются на данном факультете (например, «ит»).

Вывести из таблиц «Кафедра», «Специальность» и «Сотрудник» данные о выпус- кающих кафедрах (факультет, шифр, название, фамилию заведующего). Выпускающей счита- ется та кафедра, на которую есть ссылки в таблице «Специальность».

Вывести в запросе для каждого сотрудника номер и фамилию его непосредствен- ного руководителя. Для заведующих кафедрами поле руководителя оставить пустым.

Вывести список студентов, сдавших минимум два экзамена.

Вывести список инженеров с зарплатой, меньшей 20000 руб.

Вывести список студентов, сдавших экзамены в заданной аудитории.

Вывести из таблиц «Студент» и «Экзамен» учетные номера и фамилии студентов, а также количество сданных экзаменов и средний балл для каждого студента только для тех студентов, у которых средний балл не меньше заданного (например, 4).

Вывести список заведующих кафедрами и их зарплаты, и степень.

Вывести список профессоров.

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

Вывести фамилию преподавателей, принявших более трех экзаменов.

Вывести список студентов, не сдавших ни одного экзамена в указанной дате.



Лабораторная работа 7

Объединение результатов нескольких запросов

Цель работы

Изучить основы объединения результатов запросов.

Изучить UNION.

Изучить EXCEPT.

Изучить INTERSECT.

Теоретическая часть

Для объединения результатов двух или более запросов в одну таблицу используется команда UNION. Команда UNION объединяет вывод двух или более запросов в единый набор строк и столбцов и имеет вид:

Первый запрос UNION [ALL]

Второй запрос

…

Для объединения результатов нескольких запросов с помощью UNION, они должны соответствовать следующим требованиям:

? содержать одинаковое количество столбцов;

? типы данных столбцов должны совпадать во всех запросах;

? в промежуточных запросах нельзя использовать сортировку ORDER BY.

Чтобы отсортировать результат объединения, в конце запроса добавляется ORDER BY. Названия столбцов в запросах могут отличаться. Поэтому в команде ORDER BY указывается название столбцов с первого запроса.

Если объединяемые наборы содержат в строках идентичные значения, то при объеди- нении повторяющиеся строки удаляются.

Если необходимо при объединении сохранить повторяющиеся строки, то для этого ис- пользуется параметр ALL.

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

Чтобы найти разность двух выборок, то есть те строки, которые есть в первой выборке, но которых нет во второй, используется команда EXCEPT. Команда EXCEPT имеет следую- щий вид:

Первый запрос EXCEPT

Второй запрос.

Возвращаются все различные значения, указанные слева от оператора EXCEPT. Эти значения возвращаются, если они отсутствуют в результатах выполнения правого запроса.

Требования к использованию команды EXCEPT такие же, как к команде UNION.

Если сравниваются значения столбцов с целью определения различных строк, два значения NULL считаются равными.

Команда INTERSECT позволяет найти общие строки в результатах двух запросов, то есть данный оператор выполняет операцию пересечения множеств. Команда INTERSECT имеет следующий вид:

Первый запрос INTERSECT

Второй запрос.

Требования к использованию команды INTERSECT такие же, как к командам UNION и EXCEPT.

Практическая часть

Дана таблица Страны:

Название	Столица	Площадь	Население	Континент
Австрия	Вена	83858	8741753	Европа
Азербайджан	Баку	86600	9705600	Азия
Албания	Тирана	28748	2866026	Европа
Алжир	Алжир	2381740	39813722	Африка
Ангола	Луанда	1246700	25831000	Африка
Аргентина	Буэнос-Айрес	2766890	43847000	Южная Америка
Афганистан	Кабул	647500	29822848	Азия
Бангладеш	Дакка	144000	160221000	Азия
Бахрейн	Манама	701	1397000	Азия
Белиз	Бельмопан	22966	377968	Северная Америка
Белоруссия	Минск	207595	9498400	Европа
Бельгия	Брюссель	30528	11250585	Европа
Бенин	Порто-Ново	112620	11167000	Африка
Болгария	София	110910	7153784	Европа
Боливия	Сукре	1098580	10985059	Южная Америка
Ботсвана	Габороне	600370	2209208	Африка
Бразилия	Бразилиа	8511965	206081432	Южная Америка
Буркина-Фасо	Уагадугу	274200	19034397	Африка
Бутан	Тхимпху	47000	784000	Азия
Великобритания	Лондон	244820	65341183	Европа
Венгрия	Будапешт	93030	9830485	Европа
Название	Столица	Площадь	Население	Континент
Венесуэла	Каракас	912050	31028637	Южная Америка
Восточный Тимор	Дили	14874	1167242	Азия
Вьетнам	Ханой	329560	91713300	Азия

Пример 1: Вывести объединенный результат выполнения запросов, которые выбирают страны с площадью больше 1 млн. кв. км и с населением больше 100 млн. чел.:

SELECT

Название

,Столица

,Площадь

,Население

,Континент FROM

Страны WHERE

Площадь > 1000000 UNION

SELECT

Название

,Столица

,Площадь

,Население

,Континент FROM

Страны WHERE

Население > 100000000

Пример 2: Вывести объединенный результат выполнения запросов, которые выбирают страны с площадью больше 1 млн. кв. км и с населением больше 100 млн. чел., при этом остав- ляет дубликаты:

SELECT

Название

,Столица

,Площадь

,Население

,Континент FROM

Страны WHERE

Площадь > 1000000 UNION ALL

SELECT

Название

,Столица

,Площадь

,Население

,Континент FROM

Страны WHERE

Население > 100000000

Пример 3: Вывести объединенный результат выполнения запросов, которые выбирают европейские страны с плотностью более 300 чел. на кв. км, азиатские страны с плотностью более 200 чел. на кв. км. и африканские страны с плотностью более 150 чел. на кв. км. Резуль- тат отсортировать по континентам:

SELECT

Название

,Столица

,Площадь

,Население

,Континент FROM

Страны WHERE

Континент = 'Европа' AND

CAST(Население AS FLOAT) / Площадь > 400 UNION

SELECT

Название

,Столица

,Площадь

,Население

,Континент FROM

Страны WHERE

Континент = 'Азия' AND

CAST(Население AS FLOAT) / Площадь > 300

UNION SELECT

Название

,Столица

,Площадь

,Население

,Континент FROM

Страны WHERE

Континент = 'Африка' AND

CAST(Население AS FLOAT) / Площадь > 200 ORDER BY

Континент

Пример 4: Вывести список стран с площадью больше 1 млн. кв. км, исключить страны с населением больше 10 млн. чел.:

SELECT

Название

,Столица

,Площадь

,Население

,Континент FROM

Страны WHERE

Площадь > 1000000 EXCEPT

SELECT

Название

,Столица

,Площадь

,Население

,Континент FROM

Страны WHERE

Население > 10000000

Пример 5: Вывести список стран с площадью больше 1 млн. кв. км и с населением больше 100 млн. чел.:

SELECT

Название

,Столица

,Площадь

,Население

,Континент FROM

Страны WHERE

Площадь > 1000000 INTERSECT

SELECT

Название

,Столица

,Площадь

,Население

,Континент FROM

Страны WHERE

Население > 100000000

Задание

Вывести объединенный результат выполнения запросов, которые выбирают страны с площадью меньше 500 кв. км и с площадью больше 5 млн. кв. км: интерфейс база данный фильтрация

Вывести список стран с площадью больше 1 млн. кв. км, исключить страны с насе- лением меньше 100 млн. чел.:

Вывести список стран с площадью меньше 500 кв. км и с населением меньше 100 тыс. чел.

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

Подзапросы

Цель работы

Изучить виды вложенных запросов.

Изучить некоррелирующие подзапросы.

Изучить коррелирующие подзапросы.

Изучить применение конструкции IN к подзапросам.

Изучить конструкцию ALL.

Изучить конструкцию ANY/SOME.

Изучить конструкцию EXISTS.

Теоретическая часть видов:

В зависимости от контекста запрос SELECT может вернуть результат в одном из трех таблица - запрос возвращает набор строк и столбцов;

список значений - запрос возвращает значения только одного столбца, но, возможно, в нескольких строках;

- скалярное значение - запрос возвращает значение одного столбца в одной строке. Результат запроса можно использовать в других запросах. Место использования за-

проса зависит от вида возвращаемого значения.

В предложении SELECT может использоваться только скалярный подзапрос, который возвращает одно значение.

Подзапросы пишутся в скобках.

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

Подзапросы, которые используются в предложении FROM, должны иметь псевдоним. Подзапросы, которые используются в предложении WHERE для сравнения со столбцом, должны возвращать значения соответствующего типа.

Подзапросы, которые используются в предложении WHERE для сравнения со столб- цом, должны быть вторым операндом оператора сравнения.

Каждый вложенный запрос, в свою очередь, может содержать один или более вложен- ный запрос. В инструкцию можно вложить любое количество запросов (в практике, до 32). Подзапросы выполняются, начиная с самого глубокого.

Подзапросы бывают коррелирующими и некоррелирующими. В некоррелирующих подзапросах команды выполняются один раз, то есть результат подзапроса не зависит от строк, выбранных в основном запросе. Такой подзапрос выполняется один раз для всего внеш- него запроса.

Также существуют коррелирующие подзапросы, результаты которых зависят от строк, выбранных в основном запросе. Коррелирующие подзапросы имеют связь с внешним запро- сом и выполняется столько раз, сколько строк в основном запросе. Выполнение коррелирующих подзапросов сильно влияет на эффективность выполне- ния, поэтому их необходимо использовать только в крайних случаях.

Команду IN можно применить к результатам подзапросов, возвращающих список значений.

В предложении WHERE значение столбца можно сравнить со списками значений, воз-

вращаемых подзапросом. Для этого используются операторы ALL и ANY|SOME.

При использовании оператора ALL условие в операции сравнения должно быть верно для всех значений, возвращаемых подзапросом.

При использовании оператора ANY|SOME условие в операции сравнения должно быть верно для всех значений, возвращаемых подзапросом.

Оператор EXISTS проверяет, возвращает ли подзапрос какое-либо значение. Здесь важны не данные, а их существование.

Практическая часть

Дана таблица Страны:

Название	Столица	Площадь	Население	Континент
Австрия	Вена	83858	8741753	Европа
Азербайджан	Баку	86600	9705600	Азия
Албания	Тирана	28748	2866026	Европа
Алжир	Алжир	2381740	39813722	Африка
Ангола	Луанда	1246700	25831000	Африка
Аргентина	Буэнос-Айрес	2766890	43847000	Южная Америка
Афганистан	Кабул	647500	29822848	Азия
Бангладеш	Дакка	144000	160221000	Азия
Бахрейн	Манама	701	1397000	Азия
Белиз	Бельмопан	22966	377968	Северная Америка
Белоруссия	Минск	207595	9498400	Европа
Бельгия	Брюссель	30528	11250585	Европа
Бенин	Порто-Ново	112620	11167000	Африка
Болгария	София	110910	7153784	Европа
Боливия	Сукре	1098580	10985059	Южная Америка
Ботсвана	Габороне	600370	2209208	Африка
Бразилия	Бразилиа	8511965	206081432	Южная Америка
Буркина-Фасо	Уагадугу	274200	19034397	Африка
Бутан	Тхимпху	47000	784000	Азия
Название	Столица	Площадь	Население	Континент
Великобритания	Лондон	244820	65341183	Европа
Венгрия	Будапешт	93030	9830485	Европа
Венесуэла	Каракас	912050	31028637	Южная Америка
Восточный Тимор	Дили	14874	1167242	Азия
Вьетнам	Ханой	329560	91713300	Азия



Пример 1: Вывести список стран и процентное соотношение их населения к суммар- ному населению мира:

SELECT

Название

,Столица

,Площадь

,Население

,Континент

,ROUND(CAST(Население AS FLOAT) * 100 / (

SELECT

SUM(Население)

FROM

Страны

FROM

), 3) AS Процент

Страны

ORDER BY

Процент DESC

Пример 2: Вывести список стран мира, население которых больше, чем среднее насе- ление всех стран мира:

SELECT

Название

,Столица

,Площадь

,Население

,Континент FROM

Страны WHERE

Население > (

SELECT

AVG(Население)

FROM

Страны)

Пример 3: С помощью подзапроса вывести список африканских стран, население ко- торых больше 50 млн. чел.:

SELECT

Название

,Столица

,Площадь

,Население

,Континент FROM

( SELECT

Название

,Столица

,Площадь

,Население

,Континент FROM

Страны WHERE

Континент = 'Африка') A

WHERE

Население > 50000000

Пример 4: Вывести список стран и процентное соотношение их населения к суммар- ному населению к той части мира, где они находятся:

SELECT

Название

,Столица

,Площадь

,Население

,Континент

,ROUND(CAST(Население AS FLOAT) * 100 / (

SELECT

SUM(Население)

FROM

Страны Б

WHERE

А.Континент = Б.Континент

), 3) AS Процент

FROM

Страны А

ORDER BY

Процент DESC

Пример 5: Вывести список стран мира, население которых больше, чем среднее насе- ление стран в той части света, где они находятся:

SELECT

Название

,Столица

,Площадь

,Население

,Континент FROM

Страны А WHERE

Население > (

SELECT

AVG(Население)

FROM

Страны Б

WHERE

Б.Континент = А.Континент

)

Пример 6: Вывести список стран мира, которые находятся в тех частях света, среднее население которых больше, чем общемировое:

SELECT

Название

,Столица

,Площадь

,Население

,Континент FROM

Страны WHERE

Континент IN (

SELECT

Континент

FROM

Страны

GROUP BY

Континент HAVING

AVG(Население) > ( SELECT

AVG(Население)

FROM

)

Страны

Пример 7: Вывести список азиатских стран, население которых больше, чем в любой европейской стране:

SELECT

Название

,Столица

,Площадь

,Население

,Континент FROM

Страны WHERE

Континент = 'Азия' AND

Население > ALL (

SELECT

Население

FROM

Страны

WHERE

Континент = 'Европа'

Пример 8: Вывести список европейских стран, население которых больше, чем населе- ние хотя бы одной южноамериканской страны:

SELECT

Название

,Столица

,Площадь

,Население

,Континент FROM

Страны WHERE

Континент = 'Европа' AND

Население > ANY (

SELECT

Население

FROM

Страны

WHERE

Континент = 'Южная Америка'

)

Пример 9: Если в Африке есть хотя бы одна страна, население которой больше 100 млн. чел., вывести список всех африканских стран:

SELECT

Название

,Столица

,Площадь

,Население

,Континент FROM

Страны WHERE

Континент = 'Африка' AND

EXISTS (

SELECT

*

FROM

Страны

WHERE

Континент = 'Африка' AND

Население > 100000000

)

Пример 10: Вывести список стран в той части света, где находится страна «Науру»: SELECT

Название

,Столица

,Площадь

,Население

,Континент FROM

Страны WHERE

Континент = (

SELECT

Континент

FROM

Страны

WHERE

Название = 'Науру'

)

Пример 11: Вывести список стран, население которых не превышает населении страны

«Гондурас»:

SELECT

Название

,Столица

,Площадь

,Население

,Континент FROM

Страны WHERE

Население !> (

SELECT

Население

FROM

Страны

WHERE

Название = 'Гондурас'

Пример 12: Вывести название страны с наибольшим населением среди стран с наименьшим населением на каждом континенте:

SELECT

Название

,Столица

,Площадь

,Население

,Континент FROM

Страны WHERE

Население = (

SELECT

MAX(Мин_Нас)

FROM

(

SELECT

MIN(Население) AS Мин_Нас

FROM

Страны

)

Задание

GROUP BY

Континент

) A

Вывести список стран и процентное соотношение площади каждой из них к общей площади всех стран мира.

Вывести список стран мира, плотность населения которых больше, чем средняя плотность населения всех стран мира.

С помощью подзапроса вывести список европейских стран, население которых меньше 5 млн. чел.

Вывести список стран и процентное соотношение их площади к суммарной пло- щади той части мира, где они находятся.

Вывести список стран мира, площадь которых больше, чем средняя площадь стран той части света, где они находятся.

Вывести список стран мира, которые находятся в тех частях света, средняя плот- ность населения которых превышает общемировую.

Вывести список южноамериканских стран, в которых живет больше людей, чем в любой африканской стране.

Вывести список африканских стран, в которых живет больше людей, чем хотя бы в одной южноамериканской стране.

Если в Африке есть хотя бы одна страна, площадь которой больше 2 млн. кв. км, вывести список всех африканских стран.

Вывести список стран той части света, где находится страна «Фиджи».

Вывести список стран, население которых не превышает население страны «Фиджи».

Вывести название страны с наибольшим населением среди стран с наименьшей площадью на каждом континенте.

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

Основы DDL

Цель работы

Изучить создание базы данных.

Изучить удаление базы данных.

Изучить создание таблиц.

Изучить удаление таблиц.

Теоретическая часть

В стандарте ANSI нет команды для создания базы данных. Но в языке Transact-SQL существует команда CREATE DATABASE. Процедура создания базы данных обычно закреп- ляется только за администратором базы данных, и часто выполняется с использованием гра- фического интерфейса.

Синтаксис команды создания базы данных имеет следующий вид: CREATE DATABASE <название_базы_данных>. В SQL Server можно создать до 32768 баз данных.

После создания базы данных, ее можно установить в качестве текущей с помощью ко- манды USE: USE <название_базы_данных>.

Для удаления базы данных применяется команда DROP DATABASE, которая имеет следующий синтаксис: DROP DATABASE <название_базы_данных>. Перед удалением реко- мендуется создать резервную копию базы данных.

Основные объекты базы данных - таблицы. Они содержат все данные в базе данных. В таблицах данные организованы в виде строк и столбцов. Каждая строка представляет собой уникальную запись, а каждый столбец - поле записи.

В базе данных можно создать до 2147483648 таблиц. Стандартная таблица может со- держать до 1024 столбцов. Число строк и размер таблицы ограничиваются только простран- ством для хранения на сервере.

При создании таблицы можно установить отдельные свойства для каждого столбца. Данные в таблице могут быть сжаты либо по строкам, либо по страницам. Сжатие данных может позволить отображать больше строк на странице.

Название базы данных и таблиц может состоять максимум из 128 символов, а имена локальных временных таблиц - из 116 символов.

Таблицы можно создать с помощью конструктора или с помощью команд Transact- SQL. Для создания таблиц существует команда CREATE TABLE. Ее упрощённый синтаксис имеет следующий вид:

CREATE TABLE <название_таблицы> NULL.

(<название_столбца1> <тип_данных>,…

<название_столбцаN> <тип_данных>)

После типа данных можно добавить ограничения к значениям столбца.

Ограничение NULL | NOT NULL определяет, допустимы ли для столбца значения

Ограничение UNIQUE обеспечивает уникальность значения для указанного столбца. В таблице может быть несколько ограничений UNIQUE.

Ограничение PRIMARY KEY обеспечивает целостность и уникальность значения для указанного столбца с помощью уникального индекса. Можно создать только одно ограниче- ние PRIMARY KEY для таблицы.

Ограничение CHECK обеспечивает целостность домена путем ограничения возможных значений, которые можно ввести в столбец. Условие может включать несколько логических выражений, соединенных операторами AND и OR.

Ограничение DEFAULT позволяет указать значение по умолчанию, если значение не задано. Оно может содержать значения констант, функции, или значение NULL. При этом, нельзя ссылаться на другой столбец таблицы, а также на другие таблицы, представления или хранимые процедуры. Его нельзя создавать для столбцов с типом данных timestamp или столб- цов со свойством IDENTITY.

Свойство IDENTITY указывает, что новый столбец является столбцом идентификато- ров. Для этого столбца формируется уникальное последовательное значение. Обычно исполь- зуется вместе с ограничением PRIMARY KEY для поддержания уникальности идентификато- ров строк в таблице. Свойство IDENTITY может назначаться столбцам типа tinyint, smallint, int, bigint, decimal(p,0) или numeric(p,0). Для таблицы можно создать только один столбец идентификаторов. Ограниченные значения по умолчанию и ограничения DEFAULT не могут использоваться в столбце идентификаторов. Необходимо указать как начальное значение, так и приращение, или же не указывать ничего. Если ничего не указано, применяется значение по умолчанию (1,1).

Для удаления таблиц используется команда DROP TABLE, которая имеет следующий синтаксис: DROP TABLE <название_таблицы>.

Практическая часть

Таблица Страны:

Страна	Столица	Часть света	Население тыс. чел.	Площадь тыс. кв. км	Тип управления
Австрия	Вена	Европа	7513	84	4
Великобритания	Лондон	Европа	55928	244	1
Греция	Афины	Европа	9280	132	4
Страна	Столица	Часть света	Население тыс. чел.	Площадь тыс. кв. км	Тип управления
Афганистан	Кабул	Азия	20340	647	3
Монголия	Улан-Батор	Азия	1555	1565	4
Япония	Токио	Азия	114276	372	1
Франция	Париж	Европа	53183	551	3
Швеция	Стокгольм	Европа	8268	450	1
Египет	Каир	Африка	38740	1001	3
Сомали	Могадишо	Африка	3350	638
США	Вашингтон	Америка	217700	9363	3
Мексика	Мехико	Америка	62500	1973	4
Мальта	Валлетта	Европа	330	0,3	4
Монако	Монако	Европа	25	0,2	1



Таблица Управление:

ID	Вид
1	Конституционная монархия
2	Абсолютная монархия
3	Президентская республика
4	Парламентская республика
5	Военная хунта

Пример 1: Создать таблицу «Управление»:

CREATE TABLE Управление (

ID INT ,

Вид VARCHAR(20)

)

Пример 2: Удалить таблицу «Управление»:

DROP TABLE Управление

Пример 3: Создать таблицу «Управление», значения столбца «ID» сделать уникаль- ными, а столбец «Вид» запретить оставлять незаполненным:

CREATE TABLE Управление (

ID INT UNIQUE,

Вид VARCHAR(20) NOT NULL

)

Пример 4: Создать таблицу «Управление», в столбец «ID» разрешить вводить значения меньше 200, а для столбца «Вид» установить значение по умолчанию «Президентская респуб- лика»:

CREATE TABLE Управление (

ID INT CHECK (ID < 200),

Вид VARCHAR(20) DEFAULT 'Президентская республика'

)

Пример 5: Создать таблицу «Управление», столбец «ID» определить, как основной ключ, и настроить автоматический идентификатор с начальным значением 5 и с шагом 3:

CREATE TABLE Управление (

ID INT PRIMARY KEY IDENTITY(5,3), Вид VARCHAR(20)

)

Задание

Создать таблицу «Управление_ВашаФамилия». Определить основной ключ, иден- тификатор, значение по умолчанию

Создать таблицу «Страны_ВашаФамилия». Определить основной ключ, разреше- ние / запрет на NULL, условие на вводимое значение.

Создать таблицу «Цветы_ВашаФамилия». Определить основной ключ, значения столбца «ID» сделать уникальными, для столбца «Класс» установить значение по умолчанию

«Двудольные».

Создать таблицу «Животные_ВашаФамилия». Определить основной ключ, значе- ния столбца «ID» сделать уникальными, для столбца «Отряд» установить значение по умол- чанию «Хищные»

Лабораторная работа 8

Основы DML

Цель работы

Изучить команду INSERT.

Изучить команду UPDATE.

Изучить команду DELETE.

Изучить команду TRUNCATE TABLE.

Изучить конструкцию SELECT … INTO.

Теоретическая часть

Команда INSERT осуществляет добавление данных в определенную таблицу. После команды INSERT можно добавить необязательное ключевое слово INTO. Упрощенный син- таксис команды имеет следующий вид:

INSERT INTO <таблица> [(<список столбцов>) ] VALUES

(<список значений>)

Если добавляется две и более строки, тогда используется следующий синтаксис: INSERT INTO <таблица>

[(<список столбцов>) ] VALUES

(<список значений>),

…

(<список значений>)

Количество и тип значений должны совпадать со списком столбцов. Последователь- ность столбцов может не совпадать с таблицей. Список столбцов должен быть заключен в круглые скобки, а его элементы должны разделяться запятыми.

Если столбец имеет свойства идентификатор, его нельзя указать в списке. Для таких столбцов сервер автоматически вычисляет новое значение.

Если столбец имеет свойство DEFAULT, при отсутствии его, в таблицу вставляется значение по умолчанию.

Если столбец имеет свойство NULL, при отсутствии его, в таблицу вставляется значе- ние NULL.

Если столбец имеет свойство NOT NULL, его обязательно надо включить в список.

В списке значений для каждого столбца из указанных в списке столбцов должно быть одно значение. Список значений должен быть заключен в скобки.

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

Если одновременно добавляется несколько строк значений, каждый список значений заключается в круглые скобки и разделяется запятыми.

Если значение для столбца неизвестно, и столбец имеет свойство NULL, для него в списке значений можно указать NULL (без кавычек).

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

INSERT INTO <таблица> [(<список столбцов>) ] SELECT

[(<список столбцов>) ]

FROM

исходная_таблица WHERE

<условие>

Типы данных в исходной и целевой таблицах должны совпадать.

Если таблицы имеют одинаковую структуру, можно после команды INSERT пропустить список столбцов, а после команды SELECT указать все столбцы, с помощью астериска «*».

Для изменения строк в таблице применяется команда UPDATE. Она имеет следующий формальный синтаксис:

UPDATE <таблица>

SET столбец1 = значение1, столбец2 = значение2, …, столбецN = значениеN [WHERE <условие>]

Использование условий необязательно, но тогда обновляются все строки таблицы. Ре- комендуется сначала выполнять выборку строк с помощью SELECT, только потом иcпользо- вать команду UPDATE.

Для удаления одной или нескольких строк из таблицы применяется команда DELETE. Она имеет следующий формальный синтаксис:

DELETE [FROM] <таблица> [WHERE <условие>]

Ключевое слово FROM необязательно.

Использование условий необязательно, но тогда удаляются все строки таблицы. Реко- мендуется сначала выполнять выборку строк с помощью SELECT, только потом иcпользовать команду DELETE.

Для удаления всех строк из таблицы можно использовать команду TRUNCATE TABLE. Она имеет следующий формальный синтаксис:

TRUNCATE TABLE <таблица>

Инструкция TRUNCATE TABLE похожа на инструкцию DELETE без предложения WHERE, однако TRUNCATE TABLE выполняется быстрее и требует меньших ресурсов си- стемы и журналов транзакций.

Для создания новой таблицы и ее заполнения можно использовать конструкцию SELECT…INTO. Она имеет следующий формальный синтаксис:

SELECT

<список столбцов> INTO

<новая таблица> FROM

<исходная таблица>

Столбцы в новой таблице создаются в порядке, соответствующем списку выбора и по- лучают такие же имена, значения, типы данных и свойства допустимости значений NULL, ко- торые указаны в соответствующем выражении в списке выбора.

Практическая часть

Таблица Ученики:

ID	Фамилия	Предмет	Школа	Баллы
1	Иванова	Математика	Лицей	98,5
2	Петров	Физика	Лицей	99
3	Сидоров	Математика	Лицей	88
4	Полухина	Физика	Гимназия	78
5	Матвеева	Химия	Лицей	92
6	Касимов	Химия	Гимназия	68
7	Нурулин	Математика	Гимназия	81
8	Авдеев	Физика	Лицей	87
9	Никитина	Химия	Лицей	94
10	Барышева	Химия	Лицей	88

Код для создания данной таблицы:

CREATE TABLE Ученики (

ID INT PRIMARY KEY IDENTITY(1,1), Фамилия VARCHAR(50) NOT NULL, Предмет VARCHAR(50) NOT NULL,

Школа VARCHAR(50) NOT NULL,

Баллы FLOAT CHECK ((Баллы >= 0) AND (Баллы <= 100)) NULL

)

Пример 1: В таблицу «Ученики» внести новую запись для ученика гимназии Маркина, который по физике набрал 96 баллов:

INSERT INTO Ученики

(Фамилия, Предмет, Школа, Баллы) VALUES

('Маркин', 'Физика', 'Гимназия', 96)

Пример 2: В таблицу «Ученики» внести две строки, для ученицы лицея Никишиной, которая по химии набрала 77 баллов, и для ученика школы № 18 Андреева, оценка которого по математике неизвестна:

INSERT INTO Ученики

(Фамилия, Предмет, Школа, Баллы) VALUES

('Никишина', 'Химия', 'Лицей', 77),

('Андреев', 'Математика', 'Школа №18', NULL)

Пример 3: В таблице «Ученики» изменить данные Андреева, оценку исправить на 87: UPDATE

Ученики

SET

Баллы = 87

WHERE

Фамилия = 'Андреев'

Пример 4: В таблице «Ученики» изменить данные Никишиной, школу исправить на

«Школа №31», а предмет на математику: UPDATE

Ученики

SET

Школа = 'Школа №31', Предмет = 'Математика'

WHERE

Фамилия = 'Никишина'

Пример 5: В таблице «Ученики» изменить данные всех учеников по математике, оценку уменьшить на 5 баллов:

UPDATE

Ученики

SET

Баллы = Баллы - 5

WHERE

Предмет = 'Математика'

Пример 6: В таблице «Ученики» удалить данные всех учеников из школы №18: DELETE FROM

Ученики WHERE

Школа = 'Школа №18'

Пример 7: Создать таблицу «Лицеисты» и скопировать туда всех лицеистов: SELECT

ID

,Фамилия

,Предмет

,Школа

,Баллы

INTO FROM

Лицеисты Ученики

WHERE

Школа = 'Лицей'

Пример 8: Очистить таблицу «Лицеисты»:

TRUNCATE TABLE Лицеисты

Задание

В таблицу «Ученики» внести новую запись для ученика школы № 18 Трошкова, оценка которого по химии неизвестна.

В таблицу «Ученики» внести три строки.

В таблице «Ученики» изменить данные Трошкова, школу исправить на № 21, пред- мет на математику, а оценку на 56.

В таблице «Ученики» изменить данные всех учеников по химии, оценку увеличить на 10%, если она ниже 60 баллов.

В таблице «Ученики» удалить данные всех учеников из школы №21.

Создать таблицу «Гимназисты» и скопировать туда данные всех гимназистов, кроме тех, которые набрали меньше 60 баллов.

Очистить таблицу «Гимназисты».

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

Программирование на SQL

Цель работы

Изучение переменных в T-SQL.

Изучение условных выражений.

Изучение циклов.

Теоретическая часть

Несмотря на то, что T-SQL - декларативный язык, у него есть расширение, позволяю- щее обрабатывать ошибки, создавать и выполнять хранимые процедуры и пользовательские функции, триггеры и сценарии с использованием локальных переменных, операторов присва- ивания, ветвлений и циклов.

Объявление переменной осуществляется с помощью оператора DECLARE. Упрощен- ный синтаксис команды имеет следующий вид:

DECLARE <@название> AS <тип>

Имена переменных в Transact-SQL начинаются с символа @.

Объявить сразу несколько переменных одним оператором DECLARE можно так: DECLARE <@название1> AS <тип1>, …, <@названиеN> AS <типN>

Ключевое слово AS необязательно.

При объявлении переменной можно ее инициализировать:

DECLARE <@название> AS <тип> = <значение>

Объявленным переменным можно присвоить различные значения с помощью опера- тора присваивания SET. Переменным должны присваиваться значения того типа данных, с каким они были объявлены. Упрощенный синтаксис команды имеет следующий вид:

SET <@название> = <значение>

Переменным можно присваивать скалярный результат выполнения запросов: SET <@название> = (SELECT <значение> FROM <таблица>)

Неинициализированные переменные имеют значение NULL, их нельзя использовать в выражениях.

Переменным можно присваивать значения с помощью команды SELECT:

SELECT <@переменная1> = <столбец1>, …, <@переменнаяN> = <столбецN> FROM <таблица>)

Значения переменных можно вывести с помощью команды PRINT. Синтаксис команды имеет следующий вид:

PRINT <сообщение>

Сообщение может быть символьной константой, переменной символьного типа, пере- менной, неявно преобразуемой в последовательность символов, или выражения, возвращаю- щего символьный результат.

Значения переменных можно вывести с помощью команды SELECT. Синтаксис ко- манды имеет следующий вид:

SELECT <@переменная1> [AS псевдоним1], …, <@переменнаяN> [AS псевдонимN]

Для выполнения команды в зависимости от условия используется управляющая ко- манда IF ... ELSE … . Инструкция, следующая за ключевым словом IF и его условием, выпол- няется только в том случае, если логическое выражение возвращает TRUE. Необязательное ключевое слово ELSE представляет другую инструкцию, которая выполняется, если условие IF не удовлетворяется и логическое выражение возвращает FALSE. Упрощенный синтаксис команды имеет следующий вид:

IF <условие> [BEGIN]

<команды> [END]

[ ELSE [BEGIN]

<команды> [END]

]Условие должно возвращать только TRUE (ИСТИНА) или FALSE (ЛОЖЬ).

Если в блоке более чем одна команда, использование [BEGIN] … [END] обязательно.

Для выполнения повторяющихся операций применяется цикл WHILE. Упрощенный синтаксис команды имеет следующий вид:

WHILE <условие> [BEGIN]

<команды| BREAK | CONTINUE > [END]

Команда BREAK приводит к выходу из цикла и вызывает инструкции, следующие за ключевым словом END, обозначающим конец цикла.

Команда CONTINUE пропускает все команды после себя до конца цикла и переводит цикл на следующий шаг.

Практическая часть

Таблица Ученики:

ID	Фамилия	Предмет	Школа	Баллы
1	Иванова	Математика	Лицей	98,5
2	Петров	Физика	Лицей	99
3	Сидоров	Математика	Лицей	88
4	Полухина	Физика	Гимназия	78
5	Матвеева	Химия	Лицей	92
6	Касимов	Химия	Гимназия	68
7	Нурулин	Математика	Гимназия	81
8	Авдеев	Физика	Лицей	87
9	Никитина	Химия	Лицей	94
10	Барышева	Химия	Лицей	88

Пример 1: Даны числа a и b. Найти и вывести их сумму:

DECLARE @a INT, @b INT, @c INT SET @a = 5

SET @b = 10

SET @c = @a + @b PRINT @c

Пример 2: В таблице «Ученики» найти разницу между наибольшими баллами среди лицеистов и гимназистов:

DECLARE @licey FLOAT, @gimn FLOAT, @diff FLOAT SET @licey = (

SELECT

MAX(Баллы)

FROM

Ученики

WHERE

Школа = 'Лицей'

)

SET @gimn = (

SELECT

MAX(Баллы)

FROM

Ученики

WHERE

Школа = 'Гимназия'

)

SET @diff = ABS(@licey - @gimn) PRINT @diff

Пример 3: В таблице «Ученики» найти разницу между наибольшими и наименьшими баллами:

DECLARE @maxp FLOAT, @minp FLOAT, @diff FLOAT SELECT

@maxp = MAX(Баллы), @minp = MIN(Баллы)

FROM

Ученики

SET @diff = @maxp - @minp PRINT @diff

Пример 4: Дано случайное целое число меньше 1000. Вывести его квадрат: DECLARE @a INT = RAND() * 1000, @b INT

SET @b = SQUARE(@a) PRINT @b

Пример 5: Даны случайные целые числа a и b. Найти наибольшие из них: DECLARE @a INT = RAND() * 100, @b INT = RAND() * 100

IF @a > @b

PRINT '@a = ' + CAST(@a AS VARCHAR(3))

ELSE

PRINT '@b = ' + CAST(@b AS VARCHAR(3))

Пример 6: Дано случайное целое число a. Проверить, делится ли данное число на 3: DECLARE @a INT = RAND() * 100

IF @a % 3 = 0

PRINT CAST(@a AS VARCHAR(3)) + ' делится на 3'

ELSE

PRINT CAST(@a AS VARCHAR(3)) + ' не делится на 3'

Пример 7: Дано случайное целое число N (N < 1000). Если оно является степенью числа 5, то вывести «Да», если не является - вывести «Нет»:

DECLARE @a INT = RAND() * 1000 WHILE @a % 3 = 0

SET @a = @a / 3 IF @a = 1

PRINT 'Да'

ELSE

PRINT 'Нет'

Пример 8: Даны случайные целые числа a и b. Найти наибольший общий делитель (НОД):

DECLARE @a INT = RAND() * 1000, @b INT = RAND() * 1000 PRINT '@a = ' + CAST(@a AS VARCHAR(4))

PRINT '@b = ' + CAST(@b AS VARCHAR(4))

WHILE @a != @b BEGIN

IF @a > @b

SET @a = @a - @b

END

ELSE

SET @b = @b - @a

PRINT 'НОД = ' + CAST(@a AS VARCHAR(4))

Пример 9: Даны два целых числа A и B (A < B). Найти сумму всех целых чисел от A до B включительно:

DECLARE @a INT = 5, @b INT = 10, @s INT = 0

WHILE @a <= @b BEGIN

SET @s = @s + @a SET @a = @a + 1

END

PRINT 'Сумма = ' + CAST(@s AS VARCHAR(5))

Пример 10: Дано случайное целое число N (N < 100). Найти квадрат данного числа, используя для его вычисления следующую формулу:

??2 = 1 + 3 + 5 + ? + (2 • ?? ? 1)

После добавления к сумме каждого слагаемого выводить текущее значение суммы (в результате будут выведены квадраты всех целых чисел от 1 до N):

DECLARE @N INT = RAND() * 10, @M INT = 1, @S INT = 0 WHILE @M <= 2 * @N - 1

BEGIN

SET @S = @S + @M PRINT @S

SET @M = @M + 2

END

Пример 11: Даны случайные целые числа A и B (A < B). Вывести все целые числа от A до B включительно; при этом число A должно выводиться 1 раз, число A + 1 должно выво- диться 2 раза и т.д.:

DECLARE @A INT = RAND() * 5, @C INT = 1 DECLARE @B INT = @A + RAND() * 5

PRINT '@A = ' + CAST(@A AS CHAR(1)) + ', @B = ' + CAST(@B AS CHAR(1)) WHILE @A <= @B

BEGIN

PRINT REPLICATE(@A, @C) SET @A = @A + 1

SET @C = @C + 1

END

Пример 12: Напечатать те из двузначных чисел, которые делятся на 4, но не делятся на 6: DECLARE @A INT = 10

WHILE @A < 100 BEGIN

IF (@A % 4 = 0) AND (@A % 6 != 0) PRINT @A

SET @A = @A + 1

END

Пример 13: Даны два целых числа D (день) и M (месяц), определяющие правильную дату невисокосного года. Вывести значения D и M для даты, следующей за указанной:

DECLARE @D INT = 31, @M INT = 12 SET @D = CASE

END SET @M = CASE

END

WHEN @M IN (1, 3, 5, 7, 8, 10, 12) AND @D = 31 THEN 1

WHEN @M IN (4, 6, 9, 11) AND @D = 30 THEN 1 WHEN @M = 2 AND @D = 29 THEN 1

ELSE @D + 1

WHEN @D = 1 AND @M = 12 THEN 1 WHEN @D = 1 AND @M < 12 THEN @M + 1 ELSE @M

PRINT CAST(@D AS VARCHAR(2)) + '/' + CAST(@M AS VARCHAR(2))

Пример 14: Вывести слово «Нижневартовск» на экран столько раз, сколько в нем букв: DECLARE @L INT, @N CHAR(13) = 'Нижневартовск'

SET @L = LEN(@N)

WHILE @L > 0 BEGIN

PRINT @N

SET @L = @L - 1

END

Пример 15: Напишите код для вывода на экран с помощью цикла: НижневартовскксвотравенжиН

Нижневартовс свотравенжиН Нижневартов вотравенжиН Нижневарто отравенжиН Нижневарт травенжиН Нижневар равенжиН

Нижнева авенжиН

Нижнев венжиН

Нижне енжиН

Нижн нжиН

Ниж жиН

Ни иН

Н Н

Ни иН

Ниж жиН

Нижн нжиН

Нижне енжиН

Нижнев венжиН

Нижнева авенжиН

Нижневар равенжиН Нижневарт травенжиН Нижневарто отравенжиН Нижневартов вотравенжиН Нижневартовс свотравенжиН НижневартовскксвотравенжиН

DECLARE @L INT, @M INT, @N CHAR(13)

SET @N = 'Нижневартовск' SET @L = LEN(@N)

SET @M = @L WHILE @L > 0 BEGIN

PRINT LEFT(@N, @L) + SPACE(2 * (@M - @L)) + RIGHT(REVERSE(@N), @L) SET @L = @L - 1

END

SET @L = 2 WHILE @L <= @M BEGIN

PRINT LEFT(@N, @L) + SPACE(2 * (@M - @L)) + RIGHT(REVERSE(@N), @L) SET @L = @L + 1

END

Задание

Даны числа A и B. Найти и вывести их произведение.

В таблице «Ученики» найти разницу между средними баллами лицеистов и гимна- зистов.

В таблице «Ученики» проверить на четность количество строк.

Дано четырехзначное число. Вывести сумму его цифр.

Даны случайные целые числа a, b и c. Найти наименьшее из них.

Дано случайное целое число a. Проверить, делится ли данное число на 11.

Дано случайное целое число N (N < 1000). Если оно является степенью числа 3, то вывести «Да», если не является - вывести «Нет».

Даны случайные целые числа a и b. Найти наименьший общий кратный (НОК).

Даны два целых числа A и B (A<B). Найти сумму квадратов всех целых чисел от A до B включительно.

Найти первое натуральное число, которое при делении на 2, 3, 4, 5, и 6 дает остаток 1, но делится на 7.

Вывести свою фамилию на экран столько раз, сколько в нем букв.

Напишите код для вывода на экран с помощью цикла:

Н

иНи жиНиж нжиНижн енжиНижне венжиНижнев авенжиНижнева

равенжиНижневар травенжиНижневарт отравенжиНижневарто вотравенжиНижневартов свотравенжиНижневартовс ксвотравенжиНижневартовск

Лабораторная работа 9

Пользовательские функции

Цель работы

Изучение скалярных функций.

Изучение функции INLINE.

Изучение функции MULTI-STATEMENT.

Изучение удаления пользовательских функций.

Теоретическая часть

На языке Transact-SQL очень много встроенных функций. Несмотря на этот факт, ино- гда требуются функции, которых нет в стандартной библиотеке. Transact-SQL предоставляет возможность создать собственную функцию для решения задач. Пользовательская скалярная функция возвращает в качестве ответа единственное значение.

Пользовательскую функцию можно использовать следующими способами:

В инструкциях Transact-SQL, например, SELECT.

В приложениях, вызывающих функцию.

В определении другой пользовательской функции.

Для определения столбца таблицы.

Для определения ограничения CHECK на столбец.

Пользовательские функции не могут выполнять действия, изменяющие состояние базы данных.

Пользовательские функции не могут возвращать несколько результирующих наборов.

Пользовательские функции не могут использовать динамический SQL и временные таблицы. Табличные переменные разрешены к использованию.

Пользовательские функции могут быть вложенными, то есть из одной функции может быть вызвана другая. Вложенность функций не может превышать 32 уровней.

Упрощенный синтаксис создания пользовательской скалярной функции имеет следую- щий вид:

CREATE FUNCTION <название> (

[{<@параметр> [AS] <тип> [= <значение по умолчанию>]}]

)

RETURNS <тип возврата> [AS]

BEGIN

<команды>

RETURN <значение> END

Значение, переменная или выражение после ключевого слова RETURN имеет такой же тип, который указан после ключевого слова RETURNS.

Созданная функция может быть вызвана, как и обычная встроенная функция, но при этом должны вызываться с помощью имени владельца. Простой вызов имеет следующий вид:

SELECT <владелец>.<функция>(<параметры>)

Для пользовательских функций допускается не более 2100 параметров. При выполне- нии функции значение каждого из объявленных параметров должно быть указано пользовате- лем, если для них не определены значения по умолчанию.

Имя параметра, как и имя переменных, использует знак @ как первый символ.

Для INLINE функций ключевого слова RETURNS указывается тип TABLE без указания списка столбцов. Тело такой функции представляет собой единственный оператор SELECT, который начинается сразу после ключевого слово RETURN. Упрощенный синтаксис создания пользовательской функции INLINE имеет следующий вид:

CREATE FUNCTION <название> (

[{<@параметр> [AS] <тип> [= <значение по умолчанию>]}]

)

RETURNS TABLE AS

RETURN ( SELECT

<список столбцов> FROM

<таблица> WHERE

<условие>

)

В MULTI-STATEMENT функциях после ключевого слова RETURNS указывается тип TABLE с определением столбцов и их типов данных. Упрощенный синтаксис создания поль- зовательской MULTI-STATEMENT функции имеет следующий вид:

CREATE FUNCTION <название> (

[{<@параметр> [AS] <тип> [= <значение по умолчанию>]}]

)

RETURNS <@таблица> TABLE (<определение таблицы>) AS

BEGIN

<команды> RETURN END

Для MULTI-STATEMENT функций оператор RETURN не имеет аргумента. Значение возвращаемой переменной функции возвращается как значение функции.

Для удаления пользовательских функций используется команда DROP FUNCTION. Упрощенный синтаксис имеет следующий вид:

DROP FUNCTION [IF EXISTS] <название функции>

Ключевые слова IF EXISTS удаляют функцию только в том случае, если она уже суще- ствует.

Практическая часть

Дана таблица Страны:

Название	Столица	Площадь	Население	Континент
Австрия	Вена	83858	8741753	Европа
Азербайджан	Баку	86600	9705600	Азия
Албания	Тирана	28748	2866026	Европа
Алжир	Алжир	2381740	39813722	Африка
Ангола	Луанда	1246700	25831000	Африка
Аргентина	Буэнос-Айрес	2766890	43847000

Подобные документы

• Создание баз данных в среде MS SQL Server 2005

  Основные конструкции структурированного языка запросов SQL. Изучение среды MS SQL Server Management Studio, проверка подлинности. Создание таблиц базы данных. Таблица specialit, сourse, group, discipline, account. Проектирование структур данных.
  
  лабораторная работа [963,2 K], добавлен 14.01.2016
  

• Создание с помощью SQL Server базы данных для магазина продуктов

  Цель инфологического моделирования базы данных. Создание с помощью СУБД Microsoft SQL Server шести сущностей с определенными атрибутами, представлений, основанных на соединении столбцов нескольких таблиц и связей между ними. Создание процедур и запросов.
  
  курсовая работа [721,4 K], добавлен 29.11.2009
  

• Разработка базы данных "Служба АТС"

  Освоение сервисной системы управления базами данных Microsoft SQL. Разработка базы данных "Служба АТС" в среде Microsoft SQL Server Management Studio и создание запросов на языке SQL. Апробация инфологической модели "сущность - связь" базы данных.
  
  курсовая работа [2,9 M], добавлен 29.06.2015
  

• Создание базы данных в среде SQL SERVER 2012

  Установка "Microsoft SQL SERVER 2012". Создание файла данных, журнала транзакций, таблиц, запросов и фильтров, диаграмм и триггеров, табличных форм и отчетов. Подключение файла данных к проекту. Создание простых и сложных ленточных форм для работы с ними.
  
  курсовая работа [1,9 M], добавлен 13.12.2013
  

• Создание и индексирование однотабличной базы данных

  Создание однотабличных баз данных и ключей, индексирование однотабличной БД с помощью конструктора таблиц Table Designer в SQL Server Management Studio. Понятие и назначение индексов кластерного и некластерного типов, инструкция по их созданию в БД.
  
  лабораторная работа [684,9 K], добавлен 01.12.2011
  

• Сервис-центр

  Основные сведения об SQL Server. Логическая структура реляционной базы данных. Создание базы данных Server. Обработка элементов оператора SELECT. Структура таблиц inserted и deleted. Ввод данных в таблицу "Клиенты". Краткая справка по языку запросов SQL.
  
  курсовая работа [2,9 M], добавлен 11.05.2012
  

• Разработка базы данных для учета деятельности службы технической поддержки

  Системный анализ и анализ требований к базе данных. Особенности создания отчетов, запросов и форм в Visual Studio 2012. Программная реализация ER-диаграммы. Создание инфологической, логической и физической модели базы данных. Генерация ее в SQL Server.
  
  курсовая работа [1,0 M], добавлен 22.11.2012
  

• Администрирование информационных систем

  Подготовка к установке SQL Server 2000. Аппаратные ресурсы, влияющие на производительность работы сервера. Выбор учетной записи для служб SQL Server и SQL Server Agent. Создание файлов инициализации установки. Содержимое уникальных папок экземпляра.
  
  презентация [440,0 K], добавлен 10.11.2013
  

• Проектирование базы данных "Библиотека" с использованием программного продукта Microsoft SQL Server 2008

  Программные продукты, используемые при проектировании базы данных. Разработка базы данных "Библиотека" с использование программного проекта Microsoft SQL Server. Создание таблиц, триггеров, пользователей, репликации, запросов, функций, процедур.
  
  курсовая работа [897,6 K], добавлен 21.11.2011
  

• Базы данных

  Словесное описание предметной области. Построение схемы функциональных зависимостей. Реализация базы данных средствами утилиты Enterprise Manager в формате SQL Server Management Studio. Разработка алгоритмов работы программы и приложения пользователя.
  
  дипломная работа [1,8 M], добавлен 26.03.2015
  

• главная
• рубрики
• по алфавиту
• вернуться в начало страницы
• вернуться к началу текста
• вернуться к подобным работам