Лабораторная работа: Знакомства с MS SQL Server

Вывести список стран с площадью больше 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 проверяет, возвращает ли подзапрос какое-либо значение. Здесь важны не данные, а их существование.

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

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

Пример 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 определяет, допустимы ли для столбца значения