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

Размещено на http://www.allbest.ru/

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

Исследование системы адаптации и социализации персонала (на примере МБОУ "БСОШ")



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

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

Цель работы

Изучить интерфейс среды управления SQL Server Management Studio.

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

Всякая профессиональная деятельность так или иначе связана с информацией, с орга- низацией ее сбора, хранения, выборки. Можно сказать, что неотъемлемой частью повседнев- ной жизни стали базы данных, для поддержки которых требуется некоторый организационный метод, или механизм. Такой механизм называется системой управления базами данных (СУБД).

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

СУБД (система управления базами данных) - программное обеспечение, с помощью которого пользователи могут определять, создавать и поддерживать базу данных, а также по- лучать к ней контролируемый доступ. Системы управления базами данных существуют уже много лет, многие из них обязаны своим происхождением системам с неструктурированными файлами на больших ЭВМ. Наряду с общепринятыми современными технологиями в области систем управления базами данных начинают появляться новые направления, что обусловлено требованиями растущего бизнеса, все увеличивающимися объемами корпоративных данных и, конечно же, влиянием технологий Internet.

Microsoft SQL Server является одной из наиболее популярных систем управления ба- зами данных в мире. Данная СУБД подходит для самых различных проектов: от небольших приложений до больших высоконагруженных проектов. SQL Server был создан компанией Microsoft. Первая версия вышла в 1987 году. SQL Server долгое время был исключительно системой управления базами данных для Windows, однако начиная с версии 16 эта система доступна и на Linux (и на Mac, с помощью Docker).

Центральным аспектом в MS SQL Server, как и в любой СУБД, является база данных. База данных представляет хранилище данных, организованных определенным способом. Не- редко физически база данных представляет файл на жестком диске, хотя такое соответствие необязательно. Для хранения и администрирования баз данных применяются системы управ- ления базами данных (database management system) или СУБД (DBMS). И как раз MS SQL Server является одной из такой СУБД.

Для организации баз данных MS SQL Server использует реляционную модель. Эта мо- дель баз данных была разработана еще в 1970 году Эдгаром Коддом. А на сегодняшний день она фактически является стандартом для организации баз данных. Реляционная модель пред- полагает хранение данных в виде таблиц, каждая из которых состоит из строк и столбцов. Каждая строка хранит отдельный объект, а в столбцах размещаются атрибуты этого объекта.

Для взаимодействия с базой данных применяется язык SQL (Structured Query Language). Клиент (например, внешняя программа) отправляет запрос на языке SQL посредством специ- ального API. СУБД должным образом интерпретирует и выполняет запрос, а затем посылает клиенту результат выполнения.

Язык SQL был разработан в компании IBM для системы баз данных, которая называ- лась System/R. При этом сам язык назывался SEQUEL, расшифровывалась как Structured English QUEry Language - «структурированный английский язык запросов». Хотя в итоге ни база данных, ни сам язык не были впоследствии официально опубликованы, по традиции сам термин SQL нередко произносят как "сиквел".

В 1989 году Американский Национальный Институт Стандартов (ANSI) кодифициро- вал язык и опубликовал его первый стандарт. После этого стандарт периодически обновлялся и дополнялся. Последнее его обновление состоялось в 2016 году (SQL:2016). Но несмотря на наличие стандарта нередко производители СУБД используют свои собственные реализации языка SQL, которые немного отличаются друг от друга. Практически в каждой СУБД приме- няется свой процедурный язык, в частности, в Oracle Database используется PL/SQL (поддер- живается также в DB2 и Timesten), в Interbase и Firebird - PSQL, в DB2 - SQL PL, в Microsoft SQL Server и Adaptive Server Enterprise - Transact-SQL, в PostgreSQL - PL/pgSQL. В рамках текущей книги будет рассматриваться T-SQL.

Подмножества языка SQL:

DDL (Data Definition Language / Язык определения данных). К этому типу относятся различные команды, которые создают базу данных, таблицы, индексы, хранимые процедуры и т.д.

DML (Data Manipulation Language / Язык манипуляции данными). К этому типу относят команды на выбор данных, их обновление, добавление, удаление - в общем все те команды, с помощью которых можно управлять данными.

DCL (Data Control Language / Язык управления доступа к данным). К этому типу отно- сят команды, которые управляют правами доступа к данным.

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

Установка SQL Server Developer Edition

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

Для установки SQL Server - downloads выберите Developer Edition и нажмите кнопку «Скачать». После скачивание авто- матически запускается установщик. Выберите тип установки - «Базовая» (рис. 1):



Размещено на http://www.allbest.ru/

Рис. 1. Выбор тип установки

После принятия условия соглашения, укажите целевое расположение SQL Server.

Установка SQL Server Management Studio

SQL Server Management Studio (SSMS) - это интегрированная среда для управления любой инфраструктурой SQL, от SQL Server до баз данных SQL Azure. SSMS предоставляет средства для настройки, наблюдения и администрирования экземпляров SQL Server и баз дан- ных. С помощью SSMS можно развертывать, отслеживать и обновлять компоненты уровня данных, используемые вашими приложениями, а также создавать запросы и скрипты.

Скачайте SQL Server Management Studio (SSMS)

Открытие среды SQL Server Management Studio

Запускайте SQL Server Management Studio.

В диалоговом окне Соединение с сервером подтвердите заданные по умолчанию параметры и нажмите кнопку Подключиться. Для соединения необходимо, чтобы поле Имя сервера содержало имя компьютера, на котором установлен SQL Server. Если компонент Database Engine представляет собой именованный экземпляр, то поле «Имя сервера» должно также содержать имя экземпляра в формате <имя_компьютера> \<имя_экземпляра>.

Выбрать имя сервера (в данном случае это REM-DESKTOP).

Рис. 2. Соединение с SQL Server

MS SQL Server использует два режима аутентификации: аутентификацию Windows и аутентификацию MS SQL Server (SQL Server authentication). Для аутентификации по умолча- нию используется аутентификация Windows. Windows Authentification mode (Проверка под- линности Windows) - это режим, который использует для подключения к серверу только ло- гины Windows (рис. 2). В этом случае пользователям нет необходимости вводить какие-то па- роли при подключении к SQL Server, если они уже вошли в сеть Windows.

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

Создание учетной записи

Создайте новую учетную запись с именем STUDENT. B Management Studio список учетных записей, сконфигурированных на сервере, содержится в папке «\Безопас- ность\Имена для входа». Чтобы добавить новую учетную запись, необходимо выделить узел

«Имена для входа» в контекстном меню и выбрать пункт «Создать имя для входа…».

В открывшемся окне (рис. 3) в поле «Имя для входа» введите STUDENT. Далее выбе- рите переключатель «Проверка подлинности SQL Server» и в поле «Пароль» наберите па- роль. Снимите флажок «Пользователь должен сменить пароль при следующем входе». Остальные поля оставьте без изменений.

Рис. 3. Создание новой учетной записи



Создание базы данных

Базу данных может создать только пользователь с правами администратора. Вы- берите в контекстном меню папки «Базы данных» команду «Создать базу данных…» (рис. 4). В поле «Имя базы данных» введите имя создаваемой базы данных (БД) - «Учебная»

(рис. 5). Здесь также можно изменить путь сохранения созданной БД. Обратите внимание, что создаётся пустая база данных (контейнер).

Рис. 4. Пункт контекстного меню «Создать базу данных…»



Рис. 5. Создание новой базы данных

После создания БД окно «Обозреватель объектов» обновится. В ветке «Базы дан- ных» появится новая база данных «Учебная». Эта база данных принадлежит пользователю по имени sysadmin (системный администратор).

Создание нового пользователя

Создание пользователя в SQL Server осуществляется посредством создания учетной за- писи. Пользователи БД - это специальные объекты, которые создаются на уровне базы данных и используются для предоставления разрешений в базе данных (на таблицы, представления, хранимые процедуры). Логины и пользователи БД - это совершенно разные объекты.

Чтобы добавить в базу данных «Учебная» нового пользователя, надо:

выделить узел «\Базы данных\Учебная\Безопасность\Пользователи» и в кон- текстном меню выбрать пункт «Создать пользователя…» (рис. 6).



Рис. 6. Пункт контекстного меню «Создать пользователя...»

В открывшемся окне в поля «Имя пользователя» и «Имя для входа» ввести имя - Student.

На странице «Собственные схемы» в списке «Схемы, принадлежащие дан- ному пользователю:» установить флажок db_owner>OK (рис. 7).

Таким образом, на основе учетной записи Student будет создан новый пользователь для БД «Учебная» с правами владельца этой базы данных.



Рис. 7. Добавление в БД нового пользователя

Создание и заполнение таблиц

В «Обозревателе объектов» выберите базу данных «Учебная». На строке инструментов нажмите «Создать запрос» или используйте горячие клавиши <Ctrl>+<N> (рис. 8).



Рис. 8. Выбор БД «Учебная»

На открывшим окне редактора наберите код из приложения.

На строке инструментов нажмите «Выполнить» или используйте горячую клавишу

<F5> для выполнения команд.

Резервное копирование БД

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

Выберем в контекстном меню базы данных «Учебная» пункт «Задачи» > «Создать резервную копию» (рис. 9).



Рис. 9. Пункт контекстного меню «Создать резервное копию…»

В открывшемся диалоге «Резервное копирование базы данных - Учебная» (рис. 10) установите некоторые параметры:

База данных - Учебная (это база данных для резервного копирования);

Тип архивной копии - Полная (тип резервной копии - полный; для первого резерв- ного копирования применяется только такой; если резервная копия уже имеется, и вы желаете не создавать заново полную копию, а внести изменения в старую, выберите Разностная);

Компонент архивной копии - База данных;

Архивировать в: - Диск.



Рис. 10. Диалоговое окно Back Up Database

Нажмите кнопку «Добавить…» в этом диалоге. Откроется диалоговое окно «Выбор места расположения резервной копии» (рис. 11). В поле окна «Имя файла» введите имя файла, в который будет выполнено резервное копирование.

Рис. 11. Диалоговое окно «Выбор места расположения резервной копии»

Нажмите OK в диалоговом окне «Выбор места расположения резервной копии». Оно закроется, а в списке «Назначение» появится строка, которую ввели. Выберите ее и нажмите кнопку ОК. Произойдет резервное копирование по указанному пути.

Задание

Установите SQL Server Developer Edition.

Установите SQL Server Management Studio (SSMS).

Создайте учетную запись пользователя MS SQL-Server.

Создайте новую базу данных.

Создайте нового пользователя базы данных.

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

Создайте структуру базы в соответствии с порядком выполнения работы.

Выполните резервное копирование базы данных.

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

Команда SELECT

Цель работы

Изучение основ выборки данных.

Изучение конструкции DISTINCT | ALL.

Изучение сортировки данных.

Изучение конструкции TOP.

Изучение конструкции OFFSET и FETCH.

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

Подавляющее большинство пользователей используют SQL для организации выборки данных. Для выборки данных из БД используется запрос SELECT. Он позволяет фильтровать выбранные данные и преобразовать их к нужному виду. Результатом выполнения запроса SE- LECT является другая таблица, к которой снова может быть применен запрос SELECT.

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

[ WITH { [ XMLNAMESPACES ,] [ <common_table_expression> ] } ] SELECT select_list [ INTO new_table ]

[ FROM table_source ]

[ WHERE search_condition ]

[ GROUP BY group_by_expression ] [ HAVING search_condition ]

[ ORDER BY order_expression [ ASC | DESC ] ]

Обработка элементов запроса SELECT выполняется в следующей последовательности:

FROM - определяет имена используемых таблиц;

WHERE - фильтрует строки таблицы в соответствии с заданными условиями;

GROUP BY - группирует строки, имеющие одинаковые значения в указанном столбце;

HAVING - фильтрует группы строк в соответствии с указанным условием;

SELECT - форматирует выходные данные;

ORDER BY - сортирует результаты выполнения запроса.

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

В предложении SELECT указывается список столбцов, которые должны быть возвра- щены запросом. Можно указать исходные элементы или вычисляемые поля во время выпол- нения запроса.

Конструкция DISTINCT | ALL исключает / разрешает вывод повторяющихся строк.

Конструкция ALL используется по умолчанию.

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

SQL-запрос может содержать вычисляемые столбцы, значения которых могут опреде- лятся на основе значений данных, хранящихся в БД конструкции. Вычисляемым столбцам следует давать название с помощью ключевого слова AS.

Вычисляемый столбец можно создать как: <Новое поле> = <выражение>

Если название столбца состоит из нескольких слов, разделенных пробелами, следует их записать в квадратных скобках: [].

Сортировка данных выполняется с помощью команды ORDER BY, которая добавля- ется в конец запроса, после чего перечисляется список столбцов. Для каждого столбца указы- вается тип сортировки ASC | DESC (ascending - по возрастанию | descending - по убыванию). ASC - по умолчанию, можно не указывать.

Конструкция TOP <N> позволяет выбрать определенное количество строк из таблицы. Дополнительный оператор PERCENT позволяет выбрать процентное количество строк из таб- лицы. Дополнительный оператор WITH TIES позволяет выбрать все строки с такими же свой- ствами.

Конструкция OFFSET <N> ROWS указывает число строк, которые необходимо пропу- стить, прежде чем будет начат возврат строк из выражения запроса.

Конструкция FETCH NEXT <N> ROWS ONLY указывает число строк, возвращаемых после обработки предложения OFFSET.

На языке T-SQL регистр не имеет значение (case insensitive).

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

Дана таблица Академики:

ФИО	Дата_рождения	Специализация	Год_присвоения_ звания
Аничков Николай Николаевич	1885-11-03	медицина	1939
Бартольд Василий Владимирович	1869-11-15	историк	1913
Белопольский Аристарх Аполлонович	1854-07-13	астрофизик	1903
Бородин Иван Парфеньевич	1847-01-30	ботаник	1902
Вальден Павел Иванович	1863-07-26	химик-технолог	1910
Вернадский Владимир Иванович	1863-03-12	геохимик	1908
Виноградов Павел Гаврилович	1854-11-30	историк	1914
Ипатьев Владимир Николаевич	1867-11-21	химик	1916

ФИО	Дата_рождения	Специализация	Год_присвоения_ звания
Истрин Василий Михайлович	1865-02-22	филолог	1907
Карпинский Александр Петрович	1847-01-07	геолог	1889
Коковцов Павел Константинович	1861-07-01	историк	1906
Курнаков Николай Семёнович	1860-12-06	химик	1913
Марр Николай Яковлевич	1865-01-06	лингвист	1912
Насонов Николай Викторович	1855-02-26	зоолог	1906
Ольденбург Сергей Фёдорович	1863-09-26	историк	1903
Павлов Иван Петрович	1849-09-26	физиолог	1907
Перетц Владимир Николаевич	1870-01-31	филолог	1914
Соболевский Алексей Иванович	1857-01-07	лингвист	1900
Стеклов Владимир Андреевич	1864-01-09	математик	1912

Пример 1: Вывести список академиков: SELECT

FROM

Академики

Пример 2: Вывести ФИО и дату рождения всех академиков: SELECT

ФИО, Дата_рождения

FROM

Академики

Пример 3: Создайте вычисляемое поле «Информация», содержащее информацию об академиках в таком виде: «Академик Петров Петр Петрович, специализация: математика»:

SELECT

'Академик ' + ФИО + ', специализация: ' + Специализация AS Информация

FROM

Академики

Пример 4: Вывести ФИО академиков и номер следующего года после присвоения звания: SELECT

ФИО

,[Через год] = Год_присвоения_звания + 1

FROM

Академики

Пример 5: Выведите список специализаций, убрав дубликаты: SELECT DISTINCT

Специализация

FROM

Академики

Пример 6: Вывести список академиков, отсортированный по возрастанию года присво- ения звания:

SELECT

FROM

Академики

ORDER BY

Год_присвоения_звания

Пример 7: Вывести список академиков, отсортированный в обратном алфавитном по- рядке по полю «Специализация» и в алфавитном порядке по полю «ФИО»:

SELECT

FROM

Академики

ORDER BY

Специализация DESC

,ФИО ASC

Пример 8: Вывести первые две строки из списка академиков, отсортированного в ал- фавитном порядке по полю «ФИО»:

SELECT TOP 2

FROM

Академики

ORDER BY

ФИО ASC

Пример 9: Вывести первые 30% строк из списка академиков, отсортированного по воз- растанию года присвоения звания:

SELECT TOP 30 PERCENT

FROM

Академики

ORDER BY

Год_присвоения_звания

Пример 10: Вывести из таблицы «Академики», отсортированной по возрастанию года присвоения звания, список академиков, у которых год присвоения звания - один из первых четырех в отсортированной таблице:

SELECT TOP 4 WITH TIES

FROM

Академики

ORDER BY

Год_присвоения_звания

Пример 11: Вывести, начиная с третьего, список академиков, отсортированный в алфа- витном порядке ФИО:

SELECT

FROM

Академики

ORDER BY

ФИО

OFFSET 2 ROWS

Пример 12: Вывести, начиная с третьего и до десятого, список академиков, отсортиро- ванный в алфавитном порядке ФИО:

SELECT

FROM

Академики

ORDER BY

ФИО

OFFSET 2 ROWS

FETCH NEXT 8 ROWS ONLY

Задание

Вывести ФИО, специализацию и дату рождения всех академиков.

Создать вычисляемое поле «О присвоении звания», которое содержит информацию об академиках в виде: «Петров Петр Петрович получил звание в 1974».

Вывести ФИО академиков и вычисляемое поле «Через 5 лет после присвоения звания».

Вывести список годов присвоения званий, убрав дубликаты.

Вывести список академиков, отсортированный по убыванию даты рождения.

Вывести список академиков, отсортированный в обратном алфавитном порядке специализаций, по убыванию года присвоения звания, и в алфавитном порядке ФИО.

Вывести первую строку из списка академиков, отсортированного в обратном ал- фавитном порядке ФИО.

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

Вывести первые 10% строк из списка академиков, отсортированного в алфавитном порядке ФИО.

Вывести из таблицы «Академики», отсортированной по возрастанию года присво- ения звания, список академиков, у которых год присвоения звания - один из первых пяти в отсортированной таблице.

Вывести, начиная с десятого, список академиков, отсортированный по возраста- нию даты рождения.

Вывести девятую и десятую строку из списка академиков, отсортированного в ал- фавитном порядке ФИО.



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

Фильтрация данных

Цель работы

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

Изучение операций сравнения.

Изучение логических операторов.

Изучение BETWEEN.

Изучение LIKE.

Изучение NULL.

Изучение IN.

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

Для фильтрации данных применяется оператор WHERE. Синтаксис: WHERE <усло- вие>. В условиях поле таблицы сравнивается с константой или с выражением. Символьные константы пишутся в одинарных кавычках. Числовые константы и названия столбцов пишутся без кавычек.

Чтобы строка попала в результат, условие должно быть истинно. В условиях использу- ются операции сравнения. В Transact-SQL применяются следующие операции сравнения:

= - равенство; <> или != - неравенство;

< - меньше; > - больше;

!< - не меньше; !> - не больше;

<= - меньше или равно;

>= - больше или равно.

Можно использовать несколько условий для фильтрации данных. Для объединения их в одно выражение используются логические операторы. В Transact-SQL применяются следу- ющие логические операторы:

AND - логическое умножение или конъюнкция (И). Бинарный оператор, объединяет два условия; если оба условия истинны, результат - истина, иначе - ложь.

OR - логическое сложение или дизъюнкция (ИЛИ). Бинарный оператор, объединяет два условия; если хотя бы одно из этих условий истинно, то общее условие оператора OR также будет истинно.

NOT - логическое отрицание или инверсия (НЕ). Унарный оператор, применяется к одному условию. Если выражение в этой операции ложно, то общее условие истинно.

Самый высокий приоритет у оператора NOT. Самый низкий - у OR. Если эти опера- торы встречаются в одном выражении, то сначала выполняется NOT, потом AND, а затем OR. При записи условий использование скобок - хороший тон программирования.

Оператор BETWEEN используется для сравнения с диапазоном от начального и до ко- нечного значения. Начальное и конечное значения включены в промежуток.

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

% - любая последовательность символов, в том числе пустая;

_ - любой символ;

[ ] - символ, который указан в квадратных скобках; [ - ] - символ из определенного диапазона;

[ ^ ] - символ, который не указан после символа ^.

В базах данных, для обозначения неизвестного значения, используется понятие NULL. Для проверки неизвестного значения нельзя использовать операторы сравнения. Допускается только IS NULL или IS NOT NULL.

Оператор IN используется для сравнения с набором значений. Список значений указы- вается в скобках.

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

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



Таблица

Название	Столица	Площадь	Население	Континент
Австрия	Вена	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 млн. кв. км: SELECT Название

,Столица

,Площадь

,Население

,Континент FROM

Страны WHERE

Площадь > 1000000

Пример 2: Вывести список стран, население которых не больше 1 млн. чел.: SELECT Название

,Столица

,Площадь

,Население

,Континент FROM

Страны WHERE

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

Пример 3: Вывести список африканских стран: SELECT Название

,Столица

,Площадь

,Население

,Континент FROM

Страны WHERE

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

Пример 4: Вывести список всех стран, кроме европейских: SELECT Название

,Столица

,Площадь

,Население

,Континент FROM

Страны WHERE

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

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

SELECT Название

,Столица

,Площадь

,Население

,Континент FROM

Страны WHERE

(Население > 1000000) AND (Площадь < 100000)

Пример 6: Вывести список стран, которые находятся в Европе и их население больше 10 млн. чел., или находятся в Азии, а население больше 50 млн. чел.:

SELECT Название

,Столица

,Площадь

,Население

,Континент FROM

Страны WHERE

(Континент = 'Европа') AND (Население > 10000000) OR

(Континент = 'Азия') AND (Население > 50000000)

Пример 7: Вывести список стран, население которых от 10 до 100 млн. чел., а площадь от 100 до 200 тыс. кв. км:

SELECT Название

,Столица

,Площадь

,Население

,Континент FROM

Страны WHERE

(Население BETWEEN 10000000 AND 100000000) AND

(Площадь >= 100000) AND (Площадь <= 200000)

Пример 8: Вывести отсортированный в алфавитном порядке список стран от Бенина до Ватикана:

SELECT Название

,Столица

,Площадь

,Население

,Континент FROM

Страны WHERE

Название BETWEEN 'Бенин' AND 'Ватикан' ORDER BY

Название

Пример 9: Вывести список стран, название которых начинается с буквы «С»: SELECT Название

,Столица

,Площадь

,Население

,Континент FROM

Страны WHERE

Название LIKE 'С%'

Пример 10: Вывести список стран, в названии которых вторая буква - «а», а последняя - я»: SELECT Название

,Столица

,Площадь

,Население

,Континент FROM

Страны WHERE

Название LIKE '_а%я'

Пример 11: Вывести список стран, в названии которых третья буква - «а», «о» или «у»: SELECT Название

,Столица

,Площадь

,Население

,Континент FROM

Страны WHERE

Название LIKE ' [аоу]%'

Пример 12: Вывести список стран, название которых начинается с буквы от «А» до «Г»: SELECT Название

,Столица

,Площадь

,Население

,Континент FROM

Страны WHERE

Название LIKE '[А-Г]%'

Пример 13: Вывести список стран, название которых не начинается с буквы от «А» до

«Г» или с буквы «С»:

SELECT Название

,Столица

,Площадь

,Население

,Континент FROM

Страны WHERE

Название LIKE '[^А-Г,^С]%'

Пример 14: Вывести список стран, столицы которых не введены в базу: SELECT Название

,Столица

,Площадь

,Население

,Континент FROM

Страны WHERE

Столица IS NULL

Пример 15: Вывести список европейских, азиатских и африканских стран: SELECT Название

,Столица

,Площадь

,Население

,Континент FROM

Страны WHERE

Континент IN ('Европа', 'Азия', 'Африка')

Задание

Вывести названия и столицы пяти наибольших стран по площади.

Вывести список африканских стран, население которых не превышает 1 млн. чел.

Вывести список стран, население которых больше 5 млн. чел., а площадь меньше 100 тыс. кв. км, и они расположены не в Европе.

Вывести список стран Северной и Южной Америки, население которых больше 20 млн. чел., или стран Африки, у которых население больше 30 млн. чел.

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

Вывести список стран, названия которых не начинаются с буквы «К».

Вывести список стран, в названии которых третья буква - «а», а предпоследняя - «и».

Вывести список стран, в названии которых вторая буква - гласная.

Вывести список стран, названия которых начинаются с букв от «К» до «П».

Вывести список стран, названия которых начинаются с букв от «А» до «Г», но не

с буквы «Б».

Вывести список стран, столицы которых есть в базе.

Вывести список стран Африки, Северной и Южной Америки.

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

Типы данных и встроенные функции

Цель работы

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

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

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

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

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

Изучить CASE и IIF.

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

Типы данных

На языке Transact-SQL используется множество различных типов данных. Всех их можно разделить на следующие группы:

Числовые типы данных: BIT (значение 0 или 1), TINYINT (от 0 до 255), SMALLINT (от

-32768 до 32767), INT (от -2147483648 до 2147483647), BIGINT (от -9223372036854775808 до

9223372036854775807), DECIMAL (числа c фиксированной точностью), NUMERIC: (аналоги- чен типу DECIMAL), SMALLMONEY (дробные значения от -214748.3648 до 214748.3647), MONEY (дробные значения от -922337203685477.5808 до 922337203685477.5807), FLOAT (от

-1.79E+308 до 1.79E+308), REAL (числа от -340E+38 до 3.40E+38);

Типы данных, представляющие дату и время: DATE (дата от 01/01/0001 до 31/12/9999), TIME (время в диапазоне от 00:00:00.0000000 до 23:59:59.9999999), DATETIME (дата и время от 01/01/1753 до 31/12/9999), DATETIME2 (дата и время от 01/01/0001 00:00:00.0000000 до 31/12/9999 23:59:59.9999999), SMALLDATETIME (дата и время от 01/01/1900 до 06/06/2079),

DATETIMEOFFSET (дата и время от 01/01/0001 до 31/12/9999);

Строковые типы данных: CHAR (фиксированная строка длиной от 1 до 8000 симво- лов), VARCHAR (переменная строка длиной от 1 до 8000 символов), NCHAR (Unicode - фик- сированная строка длиной от 1 до 4000 символов), NVARCHAR (Unicode - переменная строка длиной от 1 до 4000 символов), TEXT и NTEXT (устаревшие, не рекомендуется использовать);

Бинарные типы данных: BINARY (фиксированные бинарные данные от 1 до 8000 байт), VARBINARY (переменные бинарные данные от 1 до 8000 байт), IMAGE (устаревшая, не рекомендуется использовать);

Другие типы данных: UNIQUEIDENTIFIER (уникальный идентификатор GUID), TIMESTAMP (номер версии строки в таблице), CURSOR (набор строк таблицы),

HIERARCHYID (позиция в иерархии), SQL_VARIANT (данные любого типа), XML (доку- менты или фрагменты XML), TABLE (таблица), GEOGRAPHY (географические данные, такие как широта и долгота), GEOMETRY (координаты на плоскости).

Встроенные функции Transact-SQL

Функции SQL производят действия с данными и возвращают результат. Встроенные функции делятся на три основные группы:

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

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

- функции для списка значений.

Скалярные функции бывают следующих категорий:

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

числовые функции - возвращают числовые значения на основании заданных в аргу- менте значений того же типа;

функции времени и даты - выполняют различные действия над входными значениями времени и даты и возвращают строковое, числовое значение или значение в формате даты и времени;

функции преобразования типа.

Список часто используемых строковых функций:



LEN(строка)	возвращает количество символов в заданной строке
TRIM(строка) TRIM([символ FROM] строка)	удаляет символ пробела или другие заданные символы в начале и в конце строки.
LTRIM(строка)	удаляет начальные пробелы из заданной строки
RTRIM(строка)	удаляет конечные пробелы из заданной строки
CHARINDEX(подстрока, строка) CHARINDEX(подстрока, строка, началь- ная позиция)	возвращает индекс, по которому находится первое вхождение подстроки в строке.
PATINDEX('%шаблон%', строка)	возвращает индекс, по которому находится первое вхождение определенного шаблона в строке
LEFT(строка, число)	возвращает с начала строки определенное количество символов
RIGHT(строка, число)	возвращает с конца строки определенное количество символов
SUBSTRING(строка, начальная позиция, длина )	возвращает подстроку заданной длиной, начиная с дан- ной позиции
REPLACE(строка, подстрока, замена)	заменяет одну подстроку другой
REVERSE(строка)	переворачивает строку наоборот

CONCAT(строка1, строка2 [, строкаN ] )	объединяет заданные строки в одну
LOWER(строка)	переводит строку в нижний регистр
UPPER (строка)	переводит строку в верхний регистр
SPACE(число)	возвращает заданное количество пробелов
REPLICATE(строка, число)	повторяет значение строки указанное число раз
STUFF(строка, начальная позиция, коли- чество, замена)	удаляет указанное количество символов первой строки в начальной позиции и вставляет на их место замену.

Список часто используемых числовых функций:

ABS(число)	возвращает абсолютное значение числа
CEILING(число)	возвращает наименьшее целое, большее или равное задан- ного числа.
FLOOR(число)	возвращает наибольшее целое число, меньшее или равное заданного числа
POWER(число, степень)	возвращает значение указанного выражения, возведенное в заданную степень
RAND([начальное значение])	возвращает псевдослучайное значение от 0 до 1
ROUND(число, точность)	возвращает число, округленное до указанной точности
SIGN(число)	возвращает положительное (+1), нулевое (0) или отрица- тельное (-1) значение, обозначающее знак заданного выра- жения
SQRT(число)	возвращает квадратный корень данного числа
SQUARE(число)	возвращает квадрат указанного числа
PI()	возвращает константное значение р
ACOS(число)	возвращает угол в радианах, косинус которого задан - арк- косинус.
ASIN(число)	возвращает угол в радианах, синус которого задан - аркси- нус.
ATAN(число)	возвращает угол в радианах, тангенс которого задан - арк- тангенс.
COS(число)	возвращает косинус указанного угла в радианах.
SIN(число)	возвращает синус указанного угла в радианах.
TAN(число)	возвращает тангенс указанного угла в радианах.
COT(число)	возвращает котангенс указанного угла в радианах
DEGREES(число)	возвращает для значения угла в радианах соответствующее значение в градусах.
RADIANS(число)	возвращает для значения угла в градусах соответствующее значение в радианах
EXP(число)	возвращает экспонент заданного числа
LOG(число)	возвращает натуральный логарифм указанного числа
LOG(число, основа)	возвращает логарифм указанного числа
LOG10(число)	возвращает десятичный логарифм указанного числа

Список часто используемых функций времени и даты:

GETDATE()	возвращает текущую дату и время
CURRENT_TIMEZONE()	возвращает имя часового пояса
GETUTCDATE()	возвращает текущую дату и время по Гринвичу (UTC/GMT)
DAY(дата)	возвращает день месяца указанной даты
MONTH(дата)	возвращает номер месяца указанной даты
YEAR(дата)	возвращает год указанной даты
DATEPART(часть, дата)	возвращает целое число, представляющее указанную часть заданной даты
DATENAME(часть, дата)	возвращает строку символов, представляющую указан- ную часть заданной даты
DATEADD(часть, число, дата)	добавляет указанное целое число со знаком к части входного значения даты, а затем возвращает это изме- ненное значение
DATEDIFF(часть, начальная дата, конеч- ная дата)	возвращает разницу как целое число со знаком между частями заданных дат
EOMONTH(дата)	возвращает последний день месяца, заданной даты

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

Часть даты и времени	Сокращения
year	yy, yyyy
quarter	qq, q
month	mm, m
dayofyear	dy, y
day	dd, d
week	wk, ww
weekday	dw
hour	hh
minute	mi, n
second	ss, s
millisecond	ms
microsecond	mcs
nanosecond	ns
tzoffset	tz
iso_week	isowk, isoww

Список часто используемых функций преобразования:

CAST(выражение AS тип)	преобразуют выражение в заданный тип
CONVERT(тип, выражение [, стиль])
ASCII(строка)	возвращает код ASCII первого символа указанного символьного выражения
UNICODE(строка)	возвращает код Юникод первого символа указанного символьного выражения
CHAR(число)	возвращает символ ASCII с указанным кодом
NCHAR(число)	возвращает символ Юникода с указанным кодом
STR(число)	возвращает символьные данные, преобразованные из числовых данных

Список часто используемых функций проверки значений:

ISDATE(выражение)	возвращает 1, если выражение имеет допустимое значение типа даты и времени, иначе возвращает значение 0
ISNUMERIC(выражение)	возвращает 1, если выражение имеет допустимое значение числовой тип данных, иначе возвращает 0
ISNULL(выражение, замена)	заменяет значение NULL указанным замещающим значением
COALESCE(выражение[,...n ])	вычисляет аргументы по порядку и возвращает текущее зна- чение первого выражения, изначально не вычисленного как NULL.

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

Выражение CASE имеет два формата:

простое выражение CASE для определения результата сравнивает выражение с набо- ром простых выражений;

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

Оба формата поддерживают дополнительный аргумент ELSE.

Функция IIF(условие, выражение_если_истина, выражение_если_ложь) - возвращает одно из двух значений в зависимости от того, принимает логическое выражение значение true или false.

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

Дана таблица Академики:



ФИО	Дата_рождения	Специализация	Год_присвое- ния_звания
Аничков Николай Николаевич	1885-11-03	медицина	1939
Бартольд Василий Владимирович	1869-11-15	историк	1913
Белопольский Аристарх Аполлонович	1854-07-13	астрофизик	1903
Бородин Иван Парфеньевич	1847-01-30	ботаник	1902
Вальден Павел Иванович	1863-07-26	химик-технолог	1910
Вернадский Владимир Иванович	1863-03-12	геохимик	1908
Виноградов Павел Гаврилович	1854-11-30	историк	1914
Ипатьев Владимир Николаевич	1867-11-21	химик	1916
Истрин Василий Михайлович	1865-02-22	филолог	1907
Карпинский Александр Петрович	1847-01-07	геолог	1889
Коковцов Павел Константинович	1861-07-01	историк	1906
Курнаков Николай Семёнович	1860-12-06	химик	1913
Марр Николай Яковлевич	1865-01-06	лингвист	1912
Насонов Николай Викторович	1855-02-26	зоолог	1906
Ольденбург Сергей Фёдорович	1863-09-26	историк	1903
Павлов Иван Петрович	1849-09-26	физиолог	1907
Перетц Владимир Николаевич	1870-01-31	филолог	1914
Соболевский Алексей Иванович	1857-01-07	лингвист	1900
Стеклов Владимир Андреевич	1864-01-09	математик	1912

Пример 1: Вывести ФИО академиков и длину ФИО: SELECT

ФИО

,LEN(ФИО) AS Количество_символов

FROM

Академики

Пример 2: Вывести список академиков, убрать лишние пробелы в ФИО: SELECT

TRIM(ФИО) AS ФИО

,Дата_рождения

,Специализация

,Год_присвоения_звания

FROM

Академики

Пример 3: Найти позиции буквы «о» в ФИО каждого академика. Вывести ФИО и по- зицию:

SELECT

ФИО

,CHARINDEX('о',ФИО) AS Позиция_о

FROM

Академики

Пример 4: Вывести ФИО и первые три буквы специализации каждого академика: SELECT

ФИО

,LEFT(Специализация, 3) AS Спец_3

FROM

Академики

Пример 5: Вывести ФИО и от второй до пятой буквы специализации каждого академика:

SELECT

ФИО

,SUBSTRING(Специализация, 2, 4) AS Спец_2_5

FROM

Академики

Пример 6: Вывести список академиков, заменить специализацию «лингвист» на «язы- ковед»:

SELECT

ФИО

,Дата_рождения

,REPLACE(Специализация, 'лингвист', 'языковед') AS Спец

,Год_присвоения_звания

FROM

Академики

Пример 7: Вывести список академиков, специализацию на верхнем регистре: SELECT

ФИО

,Дата_рождения

,UPPER(Специализация) AS Спец

,Год_присвоения_звания

FROM

Академики

Пример 8: Вывести ФИО академиков в правильном и обратном виде: SELECT

ФИО

,REVERSE(ФИО) AS ФИО_Обр

FROM

Академики Название

Пример 9: Вывести каждую специализацию 4 раза в одной строке. Убрать дубликаты: SELECT DISTINCT

REPLICATE(Специализация, 4) AS Спец_4

FROM

Академики

Пример 10: Вывести абсолютное значение тригонометрических функций на точке р: SELECT

ABS(COS(PI())) AS Косинус_Пи

,ABS(SIN(PI())) AS Синус_Пи

,ABS(TAN(PI())) AS Тангенс_Пи

,ABS(COT(PI())) AS КоТангенс_Пи

Пример 11: Вывести число 132.456, округленное с точностью от 3 до -3: SELECT

ROUND(123.456, 3) AS Окр3

,ROUND(123.456, 2) AS Окр2

,ROUND(123.456, 1) AS Окр1

,ROUND(123.456, 0) AS Окр0

,ROUND(123.456, -1) AS Окр_1

,ROUND(123.456, -2) AS Окр_2

,ROUND(123.456, -3) AS Окр_3

Пример 12: Вывести наименьшее целое число, которое больше или равно 123.456, и наибольшее целое число, которое меньше или равно 123.456:

SELECT

CEILING(123.456) AS Больше

,FLOOR(123.456) AS Меньше

Пример 13: Вывести квадратный корень, квадрат и куб числа 25: SELECT

SQRT(25) AS Корень

,SQUARE(25) AS Квадрат

,POWER(25, 3) AS Куб

Пример 14: Вывести текущую дату и время:

SELECT

GETDATE() AS Сейчас

Пример 15: Вывести день, месяц, год, час, минуту, секунду, номер квартала, номер не- дели, день года, день недели для текущей даты и времени:

SELECT

DAY(GETDATE()) AS День

,MONTH(GETDATE()) AS Месяц

,YEAR(GETDATE()) AS Год

,DATEPART(HOUR, GETDATE()) AS Час

,DATEPART(MINUTE, GETDATE()) AS Минута

,DATEPART(SECOND, GETDATE()) AS Секунда

,DATEPART(QUARTER, GETDATE()) AS Квартал

,DATEPART(WEEK, GETDATE()) AS Неделя

,DATEPART(DAYOFYEAR, GETDATE()) AS День_года

,DATEPART(WEEKDAY, GETDATE()) AS День_недели

Пример 16: Вывести дату 100 дней назад от текущей:

SELECT

DATEADD(DAY, -100, GETDATE()) AS День_100_Назад

Пример 17: Академик Игорь Евгеньевич Тамм родился 8 июля 1895 года. И.Е. Тамм скончался 12 апреля 1971 года. Вывести количество прожитых дней:

SELECT

DATEDIFF(DAY, '18950708', '19710412') AS Количество_прожитых_дней

Пример 18: Вывести ФИО и время года рождения каждого академика: SELECT

ФИО

, CASE MONTH(Дата_рождения) WHEN 3 THEN 'Весна' WHEN 4 THEN 'Весна' WHEN 5 THEN 'Весна' WHEN 6 THEN 'Лето' WHEN 7 THEN 'Лето' WHEN 8 THEN 'Лето' WHEN 9 THEN 'Осень' WHEN 10 THEN 'Осень' WHEN 11 THEN 'Осень' ELSE 'Зима'

END AS Времени_года FROM Академики

Пример 19: Вывести ФИО, дату рождения и знак зодиака каждого академика: SELECT

ФИО

, Дата_рождения

, CASE

WHEN (MONTH(Дата_рождения)=3 AND DAY(Дата_рождения) >= 21) OR (MONTH(Дата_рождения)=4 AND DAY(Дата_рождения) <= 20) THEN 'Овен'

WHEN (MONTH(Дата_рождения)=4 AND DAY(Дата_рождения) >= 21) OR (MONTH(Дата_рождения)=5 AND DAY(Дата_рождения) <= 21) THEN 'Телец'

WHEN (MONTH(Дата_рождения)=5 AND DAY(Дата_рождения) >= 22) OR (MONTH(Дата_рождения)=6 AND DAY(Дата_рождения) <= 21) THEN 'Близнецы'

WHEN (MONTH(Дата_рождения)=6 AND DAY(Дата_рождения) >= 22) OR (MONTH(Дата_рождения)=7 AND DAY(Дата_рождения) <= 22) THEN 'Рак'

WHEN (MONTH(Дата_рождения)=7 AND DAY(Дата_рождения) >= 23) OR (MONTH(Дата_рождения)=8 AND DAY(Дата_рождения) <= 21) THEN 'Лев'

WHEN (MONTH(Дата_рождения)=8 AND DAY(Дата_рождения) >= 22) OR (MONTH(Дата_рождения)=9 AND DAY(Дата_рождения) <= 23) THEN 'Дева'

WHEN (MONTH(Дата_рождения)=9 AND DAY(Дата_рождения) >= 24) OR (MONTH(Дата_рождения)=10 AND DAY(Дата_рождения) <= 23) THEN 'Весы'

WHEN (MONTH(Дата_рождения)=10 AND DAY(Дата_рождения) >= 24) OR (MONTH(Дата_рождения)=11 AND DAY(Дата_рождения) <= 22) THEN 'Скорпион'

WHEN (MONTH(Дата_рождения)=11 AND DAY(Дата_рождения) >= 23) OR (MONTH(Дата_рождения)=12 AND DAY(Дата_рождения) <= 22) THEN 'Стрелец'

WHEN (MONTH(Дата_рождения)=12 AND DAY(Дата_рождения) >= 23) OR (MONTH(Дата_рождения)=1 AND DAY(Дата_рождения) <= 20) THEN 'Козерог'

WHEN (MONTH(Дата_рождения)=1 AND DAY(Дата_рождения) >= 21) OR (MONTH(Дата_рождения)=2 AND DAY(Дата_рождения) <= 19) THEN 'Водолей'

WHEN (MONTH(Дата_рождения)=2 AND DAY(Дата_рождения) >= 20) OR (MONTH(Дата_рождения)=3 AND DAY(Дата_рождения) <= 20) THEN 'Рыбы'

END AS Знак_зодиака FROM Академики

Пример 20: Вывести список академиков. Для каждого академика, в зависимости от воз- раста, при присвоении звания вывести «молодой» или «старый» в дополнительном столбце:

SELECT

ФИО

,Дата_рождения

,Специализация

,Год_присвоения_звания

,IIF(Год_присвоения_звания - Year(Дата_рождения) <= 45, 'Молодой','Старый') AS Возраст_при_присвоении

FROM Академики

Задание

Вывести список академиков, отсортированный по количеству символов в ФИО.

Вывести список академиков, убрать лишние пробелы в ФИО.

Найти позиции «ов» в ФИО каждого академика. Вывести ФИО и номер позиции.

Вывести ФИО и последние две буквы специализации для каждого академика.

Вывести список академиков, ФИО в формате Фамилия и Инициалы.

Вывести список специализаций в правильном и обратном виде. Убрать дубликаты.

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

Вывести абсолютное значение функций ??????2 (??) ? ?????? (3??) с точностью два знака 2 2 после десятичной запятой.

Вывести количество дней до конца семестра.

Вывести количество месяцев от вашего рождения.

Вывести ФИО и високосность года рождения каждого академика.

Вывести список специализаций без повторений. Для каждой специализации выве- сти «длинный» или «короткий», в зависимости от количества символов.



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

Агрегатные функции

Цель работы

Изучить основы агрегации данных.

Изучить функцию MAX.

Изучить функцию MIN.

Изучить функцию SUM.

Изучить функцию AVG.

Изучить функцию COUNT.

Изучить группировки данных.

Изучить применение фильтрации в группировке данных.

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

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

SUM - вычисляет итог;

MAX - возвращает наибольшее значение;

MIN - возвращает наименьшее значение;

AVG - вычисляет среднее значение;

COUNT - вычисляет количество значений в столбце.

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

Вложенность не допускается.

Из агрегатных функций можно составлять любые выражения.

Для функций SUM и AVG столбец должен содержать числовые значения.

Для функций COUNT() можно указать аргумент * для подсчета всех строк без исключения.

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

вычисления результата. Но выборка может содержать повторяющиеся значения. Если необхо- димо выполнить вычисления только над уникальными значениями, исключив из набора зна- чений повторяющиеся данные, то для этого применяется оператор DISTINCT (кроме COUNT (*)). По умолчанию вместо DISTINCT применяется оператор ALL, который выбирает все строки. Так как этот оператор неявно подразумевается при отсутствии DISTINCT, то его можно не указывать.

Агрегатные функции можно применить не только на всю таблицу, но также на группу значений. Для этого применяется команда GROUP BY, которая пишется после WHERE. После команды GROUP BY перечисляется название столбцов, по которым следует группировать данные. Предложение GROUP BY указывает, что результаты запроса следует разделить на группы, применить агрегатную функцию по отдельности к каждой группе и получить для каж- дой группы одну строку результатов.

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

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

Команда HAVING <условие> применяется для фильтрации строк, возвращаемых при использовании предложения GROUP BY. HAVING пишется после GROUP BY, имеет такой формат, как WHERE, но в качестве значения используется значение, возвращаемое агрегат- ными функциями.

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

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

Название	Столица	Площадь	Население	Континент
Австрия	Вена	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

MAX(Площадь) AS Макс_площадь

FROM

Страны

Пример 2: Вывести наименьшее население стран в Африке: SELECT

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

FROM

Страны

WHERE

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

Пример 3: Вывести суммарное население стран Северной и Южной Америки: SELECT

SUM(Население) AS Суммарное_население

FROM

Страны

WHERE

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

Пример 4: Вывести среднее население стран, кроме европейских. Результат округлить до двух знаков:

SELECT

ROUND(AVG(CAST(Население AS FLOAT)), 2) AS Среднее_население

FROM

Страны

WHERE

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

Пример 5: Вывести количество стран, название которых начинается с буквы «С»: SELECT

COUNT(*) AS Количество

FROM

Страны

WHERE

LEFT(Название, 1) = 'С'

Пример 6: Вывести количество континентов, где есть страны: SELECT

COUNT(DISTINCT Континент) AS Количество_Континентов

FROM

Страны

Пример 7: Вывести разницу населения между странами с наибольшим и наименьшим количеством граждан:

SELECT

MAX(Население) - MIN(Население) AS Разница

FROM

Страны

Пример 8: Вывести количество стран на каждом континенте. Результат отсортировать по количеству стран по убыванию:

SELECT

Континент

, COUNT(Название) AS Количество_Стран FROM

Страны GROUP BY

Континент ORDER BY

Количество_Стран DESC

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

SELECT

LEFT(Название, 1) AS Первая_буква

, COUNT(Название) AS Количество_Стран

FROM

Страны

GROUP BY

LEFT(Название, 1) ORDER BY

Первая_буква

Пример 10: Вывести список континентов, где плотность населения больше, чем 100 чел. на кв. км:

SELECT

Континент

, AVG(CAST(Население AS FLOAT) / Площадь) AS Сред_Плотность

FROM

Страны

GROUP BY

Континент HAVING

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

Пример 11: Ожидается, что через 25 лет население Европы и Азии вырастет на 20%, Северной Америки и Африки на 50%, а остальных частей мира - на 70%. Вывести список континентов с прогнозируемым населением:

SELECT

Континент

, CASE

WHEN Континент IN ('Европа', 'Азия') THEN FLOOR(SUM(Население) * 1.2)

ление) * 1.5)

FROM

WHEN Континент IN ('Северная Америка', 'Африка') THEN FLOOR(SUM(Насе-

ELSE FLOOR(SUM(Население) * 1.7) END AS Суммарное_Население

Страны

GROUP BY

Континент

Пример 12: Вывести список континентов, где разница по населению между наиболь- шими и наименьшими странами не более в 1000 раз:

SELECT

Континент

FROM

Страны

GROUP BY

Континент

HAVING MAX(Население) <= 1000 * MIN(Население)

Пример 13: Вывести количество стран, у которых нет столицы (не введена в базу): SELECT

COUNT(*) AS Количество

FROM

Страны

WHERE

Столица IS NULL

Пример 14: Вывести количество символов в самых длинных и коротких названиях стран и столиц:

SELECT

MAX(LEN(Название)) AS Дл_Название

, MAX(LEN(Столица)) AS Дл_Столица

, MIN(LEN(Название)) AS Кр_Название

, MIN(LEN(Столица)) AS Кр_Столица

FROM

Страны

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

SELECT

Континент

,AVG(CAST(Население AS FLOAT) / Площадь) AS Плотность

FROM

Страны

WHERE

Площадь > 1000000 GROUP BY

Континент HAVING

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

Плотность DESC

Задание

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

Вывести наибольшую по населению страну в Северной и Южной Америке.

Вывести среднее население стран. Результат округлить до одного знака.

Вывести количество стран, у которых название заканчивается на «ан», кроме стран, у которых название заканчивается на «стан».

Вывести количество континентов, где есть страны, название которых начинается с буквы «Р».

Сколько раз страна с наибольшей площадью больше, чем страна с наименьшей пло- щадью?

Вывести количество стран с населением больше, чем 100 млн. чел. на каждом кон- тиненте. Результат отсортировать по количеству стран по возрастанию.