Лабораторная работа 4. Создание запросов на выборку 1 Цель работы

• Название: Лабораторная работа 4. Создание запросов на выборку 1 Цель работы
• Дата: 06.03.2023
• Размер: 332.65 Kb.
• Имя файла: Laboratornaya_rabota_4_MDK0102_OIB.docx
• Тип: Лабораторная работа; #972411

Лабораторная работа №4. Создание запросов на выборку
4.1 Цель работы
Изучить используемый в реляционных СУБД оператор извлечения данных из таблиц. Получить навыки работы с оператором SELECT в программе SQL Server Managmant Studio
4.2 Ход работы
1. Изучить синтаксис оператора SELECT и примеры запросов к учебной базе данных ‘College.mdf’.

2. Для созданной базы данных, согласно номеру варианта, самостоятельно создать на языке Transact-SQL 30 запросов с отбором строк по условию, среди которых:

• 
  простейшие запросы с использованием операторов сравнения;
  
• 
  запросы с использованием логических операторов AND, OR и NOT;
  
• 
  запросы на использование комбинации логических операторов;
  
• 
  запросы с проверкой на принадлежность множеству;
  
• 
  запросы с проверкой на принадлежность диапазону значений;
  
• 
  запросы с проверкой на соответствие шаблону;
  
• 
  многотабличные запросы;
  
• 
  запросы с использованием функций;
  
• 
  запросы с группировкой и сортировкой.
  

4.3 Содержание отчета

1. 
   цель, ход работы;
   
2. 
   постановка задачи;
   
3. 
   формулировка индивидуального задания;
   
4. 
   результат работы ;
   
5. 
   вывод.
   

4.4 Контрольные вопросы

1. 
   охарактеризуйте основные понятия баз данных;
   
2. 
   язык SQL, его назначение;
   
3. 
   команды SQL, которые используются для возможности модификации записей таблицы БД;
   
4. 
   команда SELECT, его основные опции.
   

4.5 Методические рекомендации
4.5.1 Оператор SELECT
В SQL имеется единственный оператор, который предназначен для выборки данных из базы данных.

Оператор SELECT — важнейший оператор языка SQL. Он используется для отбо­ра записей, удовлетворяющих сложным критериям поиска, и имеет следующий формат:

SELECT [DISTINCT] {* | <Список полей>} 

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

[WHERE <Условия отбора >]

[ORDER BY <Список полей для сортировки >]

[GROUP BY <Список полей для группирования >] 

[HAVING <Условия группирования >]

[UNION <Вложенный оператор SELECT>]
Результат выполнения SQL-запроса, заданного оператором SELECT, представляет собой выборку записей, отвечающих заданным в нем условиям. При рассмотре­нии оператора SELECT будем предполагать, что SQL-запрос набран и выполнен с помощью компонента Query. В этом случае результатом выполнения запроса является соответствующий этому компоненту набор данных.

В таком результирующем наборе данных могут быть разрешены или запрещены повторяющиеся записи (т. е. имеющие одинаковые значения всех полей). Этим режимом управляет описатель DISTINCT. Если он отсутствует, то в наборе дан­ных разрешаются повторяющиеся записи.

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

Список полей определяет состав полей результирующего набора данных, эти поля могут принадлежать разным таблицам. В списке должно быть задано хотя бы одно поле. Если в набор данных требуется включить все поля таблицы (таблиц), то вместо перечисления имен полей можно указать символ *. Если список содержит поля нескольких таблиц, то для указания принадлежности по­ля к той или иной таблице используют составное имя, включающее в себя имя таблицы и имя поля, разделенные точкой: <имя таблицы>. <Имя поля>.

Операнд WHERE задает условия (критерии) отбора, которым должны удовлетво­рять записи в результирующем наборе данных. Выражение, описывающее усло­вия отбора, является логическим. Его элементами могут быть имена полей, опе­рации сравнения, арифметические и логические операции, скобки, специальные функции LIKE, NULL, IN и др.

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

Операнд HAVING действует совместно с операндом GROUP BY и используется для отбора записей внутри групп. Правила записи условий группирования анало­гичны правилам формирования условий отбора в операнде WHERE.

Операнд ORDER BY содержит список полей, определяющих порядок сортировки записей результирующего набора данных. По умолчанию сортировка по каждо­му полю выполняется в порядке возрастания значений; если необходимо задать для поля сортировку по убыванию, то после имени этого поля указывается опи­сатель DESC.

Операторы SELECT могут иметь сложную структуру и быть вложенными друг в друга. Для объединения операторов используется операнд UNION, в котором рас­полагается вложенный оператор SELECT, называемый также подзапросом. Резуль­тирующий набор данных представляют записи, отобранные в результате выпол­нения условий отбора, заданных операндами WHERE обоих операторов.
4.5.2 Создание многотабличных запросов
Одна из наиболее важных особенностей предложения SELECT — это способность использования связей между различными таблицами, а также вывода содержащейся в них информации. Операция, которая приводит к соединению из двух таблиц всех пар строк, для которых выполняется заданное условие, называется соединением таблиц. Для того чтобы указать соединяемые таблицы, их следует перечислить через запятую во фразе FROM.

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

Этот вид запросов характерен тем, что фраза WHERE содержит только условие соединения, а список фразы SELECT содержит имена столбцов из различных таблиц.

Сформулируем общую процедуру составления многотабличного запроса и приведем пример ее использования.

1. 
   Определить множество таблиц, необходимых для ответа на запрос. В это множество должны входить таблицы, на столбцах которых сформулированы условия, а также те, столбцы которых необходимо вывести. Это так называемые базовые таблицы запроса.
   
2. 
   В структуре взаимосвязанных таблиц найти путь, соединяющий базовые таблицы. Это так называемый путь вычисления запроса. В результате вы получите перечень таблиц, необходимых для формулировки запроса. Это так называемые таблицы запроса.
   
3. 
   Во фразе FROM перечислить необходимые таблицы.
   
4. 
   Во фразе WHERE соединить таблицы запроса и при необходимости задать условия отбора строк в базовых таблицах запроса.
   
5. 
   Во фразе SELECT перечислить выводимые столбцы.
   

4.5.3 Создание запросов с использованием функций
SQL Server содержит множество встроенных функций, а также поддерживает создание определяемых пользователем функций.

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

AVG - среднее MIN - минимум

CHECKSUM_AGG - Возвращает контрольную сумму значений в группе.

Значения NULL не учитываются.

SUM - сумма

COUNT - количество

STDEV – среднее квадратическое отклонение

COUNT_BIG - Возвращает количество элементов в группе.

STDEVP - Возвращает статистическое стандартное отклонение всех значений в указанном выражении.

GROUPING - Указывает, является ли указанное выражение столбца в списке GROUP BY статистическим или нет. В результирующем наборе функция GROUPING возвращает 1 (статистическое выражение) или ноль (нестатистическое выражение).

VAR - дисперсия

GROUPING_ID - Представляет собой функцию, которая вычисляет уровень группирования.

VARP - Возвращает статистическую дисперсию для заполнения всех значений в указанном выражении.

MAX - максимум
4.5.4 Выполнение оператора SELECT в SQL Server Managmant Studio
Для выполнения запросов SELECT в программе SQL Server Managmant Studio необходимо выполнить следующие действия:

Подключиться к базе данных и выполнить команду Создать запрос. В результате откроется окно Конструктора запросов. Ввести текст запроса. Нажать на панели инструментов кнопку [Выполнить]. Если запрос правильный, то в результате произойдет его выполнение и результат будет отображен на вкладке Результаты (рисунок 23).


Рисунок 23 – Результат выполнения запроса
Примеры выполнения запросов:

1. Операторы сравнения
Вывести фамилии, имена студентов женского пола

select fam,nam from student

where pol='женский'

Вывести студентов, дата рождения которых после 01.01.2003

select * from student

where data_r > '01/01/2003'


2. Логические операторы

Вывести всех студентов группы 16ПКС-1 мужского пола

select * from student

where Number_gr = '16ПКС-1' and Pol='мужской'

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

Вывести группы 2017-го, 2018-го года поступления

select * from gruppa

where year_admission in (2017,2018)


4. Проверка на принадлежность диапазону значений

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

select * from student

where data_r between '01/01/2003' and '01/01/2004'

5. Проверка на соответствие шаблону

Вывести фамилии студентов, начинающиеся на букву «Н»

Select fam from Student

where fam like 'Н%'


Вывести отделения, в названии которых есть слово «электротехническое»

Select * from Department

where Lower(Name_department) like '%электротехническое%'


6. Многотабличные запросы

Вывести наименования отделений из таблицы Department и соответствующие им группы из таблицы Gruppa

Select Name_Department, Gruppa.* from Department, Gruppa

where Department.Kod_department = Gruppa.Kod_department


Вывести все группы отделения информационных технологий

Select Gruppa.Number_gr from Department, Gruppa

where Department.Kod_department = Gruppa.Kod_department and

Name_department='Отделение информационных технологий'


Вывести группы, в которых есть студенты с иностранным гражданством

Select Gruppa.Number_gr from Gruppa, Student

where Gruppa.Number_gr=Student.Number_gr and

Student.In_gr=1


Запрос по трем таблицам.

Вывести наименования отделений, на которые обучаются студенты – иностранные граждане

Select Department.Name_department from Department,Gruppa, Student

where Department.Kod_department=Gruppa.Kod_department and

Gruppa.Number_gr=Student.Number_gr and

Student.In_gr=1


7. Запросы с использованием функций

Вывести количество отделений колледжа

SELECT COUNT(*) AS "Количество отделений"

FROM DEPARTMENT


8. Запросы с группировкой и сортировкой

Для каждого отделения посчитать количество групп

SELECT kod_department AS "Отделение",

COUNT(*) AS "Количество групп" FROM Gruppa

GROUP BY kod_department;


Вывести список студентов, упорядочив по алфавиту по полю «Фамилия»

SELECT * from student order by fam


4.6 Индивидуальные задания
Выполнить SQL-запросы выборки согласно своей предметной области.