Lab 4 Filimonova Arina [PDF]

Zitiervorschau

Некоммерческое акционерное общество «Алматинский университет энергетики и связи» имени Гумарбека Даукеева Институт Информационных Технологий Кафедра информационные системы и кибербезопасность

Отчет по лабораторной работе № 4

По дисциплине: Проектирование баз данных На тему: Простые запросы к базе данных Специальность: B-058 Системы Информационной Безопасности Группа: СИБ 19-5 Выполнила: Филимонова А.А. Проверила: доц. Сатимова Е.Г. « » 2021 г.

Алматы 2021

Содежание Задания.......................................................................................................................3 Задание 1....................................................................................................................4 Задание 2....................................................................................................................5 Задание 3....................................................................................................................5 Задание 4....................................................................................................................6 Задание 5....................................................................................................................7 Задание 6....................................................................................................................7 Задание 7....................................................................................................................8 Задание 8....................................................................................................................8 Задание 9..................................................................................................................10 Задание 10................................................................................................................10 Задание 11................................................................................................................11 Задание 12................................................................................................................11 Задание 13................................................................................................................12 Задание 14................................................................................................................13 Задание 15................................................................................................................14 Задание 16................................................................................................................15 Задание 17................................................................................................................15 Вывод.......................................................................................................................17 Список литературы.................................................................................................18

2

Задания Выполнить поиск информации в отдельных таблицах: 1. Список преподавателей с указанием их должностей в алфавитном порядке. 2. Названия кафедр с фамилиями заведующих. 3. Список студентов с различными фамилиями, обучающихся в первой группе (предполагается, что в этой группе есть однофамильцы). 4. Список студентов, у которых стипендия больше 2000. 5. Список студентов, проживающих в Астане и Караганде. 6. Список студентов второй группы, у которых нет стипендии. 7. Список студентов третьей группы, фамилии которых начинаются на букву «А». 8. Список студентов, которые родились в 1996 году. 9. Посчитайте суммарную стипендию студентов третьей группы. 10. Посчитайте среднее число лекционных часов по всем предметам. 11. Сколько студентов введено в базу данных? 12. Выведите всю информацию о предметах. 13. Список студентов, которые не проживают в Алматы. 14. Список студентов, чьи дни рождения в мае. 15. Номера студентов с минимальной оценкой из ведомости успеваемости. 16. Номера студентов с максимальной оценкой из ведомости успеваемости. 17. Выведите список студентов и их возраст.

Задание 1 Необходимо вывести список преподавателей с указанием их должностей в алфавитном порядке. Используем инструкцию SELECT. Фамилии, имена и отчества соединим, с помощью + и ' ', в одно поле и дадим ему название «Teacher». Далее указываем таблицу, из которой извлекаем данные. С помощью ключевого слова ORDER BY сортируем результат в алфавитном порядке по полю Teacher. Запрос 1: SELECT teach_FAM +' '+ teach_IMYA +' '+ teach_OTCH as Teacher, teach_POSITION FROM DekanatSchema.Teacher ORDER BY Teacher; Так же для соединения несколько полей можно использовать встроенную функцию CONCAT. Результат будет таким же, как и результат предыдущего запроса. Запрос 2: SELECT CONCAT(teach_FAM, ' ', teach_IMYA, ' ', teach_OTCH) as Teacher, teach_POSITION FROM DekanatSchema.Teacher ORDER BY Teacher; После ввода запроса нажимаем на панели инструментов на кнопку «Выполнить», либо на клавишу F5.

Задание 2 Необходимо вывести список кафедр с фамилиями заведующих. Используем инструкцию SELECT. Выбираем поля: chair_NAME, chair_CHIEF. Далее указываем таблицу, из которой извлекаем данные. Запрос: SELECT chair_NAME, chair_CHIEF FROM DekanatSchema.Chair; Задание 3 Необходимо вывести список студентов с различными фамилиями, обучающихся в первой группе. Используем инструкцию SELECT. Выбираем поле stud_FAM и для того чтобы вывести только уникальные фамилии после SELECT вставляем ключевое слово DISTINCT, которое удалите все повторяющиеся строки.

Далее указываем таблицу, из которой извлекаем данные. Задаем условие по полю grup_ID, где grup_ID=100. Запрос: SELECT DISTINCT stud_FAM FROM StudySchema.Students WHERE grup_ID=100; После выполнения запроса вывелся список из 7 студентов с уникальными фамилиями, а в списке первой группы всего 10 студентов. Можно сделать вывод, что у 3 студентов одинаковые фамилии. Задание 4 Необходимо вывести список студентов, у которых стипендия больше 2000. Используем инструкцию SELECT. Выбираем поля из таблицы: stud_FAM, stud_IMYA, stependia. Далее указываем таблицу, из которой извлекаем данные. Затем задаем условие по полю stependia, где stependia>20000. Запрос: SELECT stud_FAM, stud_IMYA, stependia FROM StudySchema.Students WHERE stependia>20000;

Задание 5 Необходимо вывести список студентов, проживающих в Астане и Караганде. Используем инструкцию SELECT. Выбираем поля из таблицы: stud_FAM, stud_IMYA, stud_ADDRESS. Далее указываем таблицу, из которой извлекаем данные. Затем задаем условие по полю stud_ADDRESS, используя логический оператор OR. Запрос 1: SELECT stud_FAM, stud_IMYA, stud_ADDRESS FROM StudySchema.Students WHERE stud_ADDRESS='Астана' OR stud_ADDRESS='Караганда'; Так же можно использовать специальный оператор IN, указав список значений, которые должны входить. Запрос 2: SELECT stud_FAM, stud_IMYA, stud_ADDRESS FROM StudySchema.Students WHERE stud_ADDRESS IN ('Астана', 'Караганда');

Оба запроса выводят одинаковый результат. Задание 6 Необходимо вывести список студентов второй группы, у которых нет стипендии. Используем инструкцию SELECT. Выбираем все поля из таблицы, для этого после SELECT вставляем *. Далее указываем таблицу, из которой извлекаем данные. Затем с помощью логического оператора AND объединяем два условия: grup_ID=200 и stependia IS NULL. Запрос: SELECT * FROM StudySchema.Students WHERE grup_ID=200 and stependia IS NULL; Задание 7 Необходимо вывести список студентов третьей группы, фамилии которых начинаются на букву «А». Используем инструкцию SELECT. Выбираем поля из таблицы: stud_FAM, stud_IMYA, grup_ID. Далее указываем таблицу, из которой извлекаем данные. Затем с помощью логического оператора AND объединяем два условия: grup_ID=300 и указываем шаблон 'А%'. Запрос: SELECT stud_FAM, stud_IMYA, grup_ID FROM StudySchema.Students WHERE grup_ID=300 and stud_FAM LIKE 'А%'; Задание 8 Необходимо вывести список студентов, которые родились в 1996 году. Используем инструкцию SELECT. Выбираем поля из таблицы: stud_FAM, stud_IMYA, stud_DATE. Далее указываем таблицу, из которой извлекаем данные. Затем задаем условие по полю stud_DATE. С помощью функции DATENAME выводим символьное значение года и приравниваем к 1996. Запрос 1: SELECT stud_FAM, stud_IMYA, stud_DATE FROM StudySchema.Students WHERE DATENAME(yy, stud_DATE) = '1996';

Так же в условии вместо DATENAME можно использовать функцию DATEPART, которая выводит числовое значение года. Запрос 2: SELECT stud_FAM, stud_IMYA, stud_DATE FROM StudySchema.Students WHERE DATEPART(yy, stud_DATE)=1996; Еще один способ вывести список студентов, которые родились в 1996 году, использовать шаблон '1996%'. Запрос 3: SELECT stud_FAM, stud_IMYA, stud_DATE FROM StudySchema.Students WHERE stud_DATE LIKE '1996%'; Также можно использовать функцию LEFT(), которая выводит определенное количество символов слева. Запрос 4: SELECT stud_FAM, stud_IMYA, stud_DATE FROM StudySchema.Students WHERE LEFT(stud_DATE,4) = 1996; Можно использовать функцию YEAR, которая возвращает только год. Запрос 5: SELECT stud_FAM, stud_IMYA, stud_DATE FROM StudySchema.Students WHERE YEAR(stud_DATE) = 1996; Все пять способа выводят одинаковый результат. Задание 9 Необходимо посчитать суммарную стипендию студентов третьей группы. Используем инструкцию SELECT. Для того чтобы вычислить сумму, воспользуемся агрегатной функцией SUM(). При выводе результата дадим имя полю «sum_stependia». Далее указываем таблицу, из которой извлекаем данные. Затем задаем условие по полю grup_ID, где номер группы равен 300. Запрос: SELECT sum(stependia) as sum_stependia FROM StudySchema.Students WHERE grup_ID=300; Задание 10 Необходимо посчитать среднее число лекционных часов по всем предметам.

Используем инструкцию SELECT. Для того чтобы вычислить среднее число лекционных часов по всем предметам, воспользуемся агрегатной функцией AVG(). При выводе результата дадим имя полю «avg_lec_hours». Далее указываем таблицу, из которой извлекаем данные. Запрос 1: SELECT AVG(lection_hours) as avg_lec_hours FROM StudySchema.Study; Так же можно математически вычислить среднее значение, суммировать все значения lection_hours и поделить на количество записей. Запрос 2: SELECT SUM(lection_hours)/COUNT(lection_hours) as avg_lec_hours FROM StudySchema.Study; Результат первого и второго запроса будут одинаковыми, так как в поле lection_hours отсутствует значение NULL.

Задание 11 Необходимо определить сколько студентов введено в базу данных. Используем инструкцию SELECT. Для того чтобы вычислить количество студентов в базе, воспользуемся агрегатной функцией COUNT(). При выводе результата дадим имя полю «count_students». Далее указываем таблицу, из которой извлекаем данные. Запрос: SELECT COUNT(*) as count_students FROM StudySchema.Students; Задание 12 Необходимо вывести всю информацию о предметах. Используем инструкцию SELECT. Для того чтобы вывести всю информацию, после SELECT указываем *. Далее указываем таблицу, из которой извлекаем данные. Запрос: SELECT * FROM StudySchema.Subjects; Так же информация о предметах содержится в таблице Учебный процесс. Запрос: SELECT * FROM StudySchema.Study;

Задание 13 Необходимо вывести список студентов, которые не проживают в Алматы. Используем инструкцию SELECT. Выбираем поля из таблицы: stud_FAM, stud_IMYA, stud_ADDRESS. Далее указываем таблицу, из которой извлекаем данные. Затем задаем условие по полю stud_ADDRESS, используя операцию не равно (). Запрос 1: SELECT stud_FAM, stud_IMYA, stud_ADDRESS FROM StudySchema.Students WHERE stud_ADDRESS'Алматы'; Так же можно использовать специальный оператор IN с отрицанием, указав список значений, которые не должны входить. Запрос 2: SELECT stud_FAM, stud_IMYA, stud_ADDRESS FROM StudySchema.Students WHERE stud_ADDRESS NOT IN ('Алматы'); Задание 14 Необходимо вывести список студентов, чьи дни рождения в мае. Напишем запросы аналогично 8 заданию, только указывая месяц. Запрос 1: SELECT stud_FAM, stud_IMYA, stud_DATE FROM StudySchema.Students WHERE DATENAME(mm, stud_DATE) = 'Май'; Запрос 2: SELECT stud_FAM, stud_IMYA, stud_DATE FROM StudySchema.Students WHERE DATEPART(mm, stud_DATE)=5; Запрос 3: SELECT stud_FAM, stud_IMYA, stud_DATE FROM StudySchema.Students WHERE stud_DATE LIKE '%-05-%'; Можно использовать функцию MONTH, которая возвращает номер месяца. Запрос 4: SELECT stud_FAM, stud_IMYA, stud_DATE FROM StudySchema.Students WHERE MONTH(stud_DATE)=5;

Все четыре способа выводят одинаковый результат. Задание 15 Необходимо вывести номера студентов с минимальной оценкой из ведомости успеваемости. Используем инструкцию SELECT. Выбираем поле из таблицы: stud_ID. Находим с помощью агрегатной функции MIN() минимальную оценку и задаем полю имя «min_ocenka». Далее указываем таблицу, из которой извлекаем данные. Затем группируем по полю stud_ID. Запрос: SELECT stud_ID, MIN(Ocenka)as min_ocenka FROM StudySchema.Progress GROUP BY stud_ID; Задание 16 Необходимо вывести номера студентов с максимальной оценкой из ведомости успеваемости. Пишем запрос аналогично заданию 15, только вместо функции MIN() используем MAX(). Запрос: SELECT stud_ID, MAX(Ocenka)as max_ocenka FROM StudySchema.Progress GROUP BY stud_ID; Задание 17 Необходимо вывести список студентов и их возраст. Используем инструкцию SELECT. Выбираем поля из таблицы: stud_FAM, stud_IMYA, stud_DATE. Используя функцию DATEDIFF, вычисляем количество дней между текущей даты и stud_DATE. Далее делим на количество дней в году, чтобы получить возраст студента и называем поле «Age». Далее указываем таблицу, из которой извлекаем данные. Запрос: SELECT stud_FAM, stud_IMYA, stud_DATE, DATEDIFF(dd, stud_DATE, GETDATE())/365 as Age FROM StudySchema.Students;

Вывод В результате выполнения лабораторной работы было выполнено 17 заданий. Научились писать простые запросы к базе данных скриптом. Познакомились с инструкцией SELECT. Используя DICTINCT, выводили уникальные значения. Научились:  сортировать результат запроса с помощью ключевого слова ORDER BY;  группировать записи (GROUP BY);  ограничивать строки таблицы, используя ключевое слово WHERE. Изучили агрегатные функции COUNT, SUM, MIN, MAX, AVG. Они позволяют не просто выбирать определенные значения из базы, но и производить их обобщение и анализ. Разобрали математические функции, строковые функции, функции дат.

Список литературы 1. Методические указания по выполнению лабораторной работы №4 для студентов специальности 5В100200 по предмету Проектирование баз данных – Системы информационной безопасности.