pr6 SQL

ЦК "Информатики, программирования и программных средств", преподаватель Е.А.Ларионова Базы данных
Практическая работа №6
Тема: «Сложные запросы»
Цель: «Научиться писать запросы к БД на языке SQL»
Задание
Используя базу данных студентов колледжа, выполните запросы на SQL языке:
Вариант 1
Напечатать фамилии последних трёх студентов из списка, упорядоченного по фамилии.
Напечатать коды специальности, встречающиеся в таблице студентов.
Создать запрос на выборку студентов, фамилии которых начинаются на буквы “А-М”, а заканчиваются на буквы “в”, “н”, “а”.
Напечатать мужские (женские) имена из таблицы.
Заменить фамилию последнего студента (студентки ) на собственную, таблица упорядочена по номеру студента.
Удалить из таблицы студентов, которым более 20 лет.
Вывести на печать фамилии студентов, дни их рождения с указанием количества лет и дня недели даты рождения.
Напечатать самое распространённое мужское (женское) имя.
Напечатать фамилии отличников: студентов, у которых все пятёрки в таблице Успеваемость.
Вывести на печать количество двоек по каждой дисциплине.
Вывести на печать количество девушек, юношей и общее количество студентов в каждой группе.

Вариант 2
Напечатать фамилии первых пяти студентов из списка, упорядоченного по фамилии.
Напечатать номера групп, встречающиеся в таблице студентов, список упорядочить по алфавиту.
Создать запрос на выборку студентов, фамилии которых начинаются на первую букву Вашей фамилии, а заканчиваются на последнюю букву Вашей фамилии.
Перевести студента (студентку ) из внебюджетной группы в бюджетную.
Удалить из таблицы студентов 4ого курса, номер курса извлечь из поля [группа].
Напечатать самую распространённую оценку в таблице Успеваемость.
Перевести всех студентов 1-3 курсов на курс старше, номер курса извлечь из поля [группа].
Вывести на печать количество студентов, родившихся в каждом месяце.
Вывести на печать количество двоек, полученных каждым студентом, если их больше 2х.
Вывести на печать количество студентов, родившихся в 1ой половине и во 2 ой половине, каждого месяца.
Напечатать номера групп, где больше всего студентов.

Порядок выполнения работы

В конструкторе запросов с помощью правой клавиши мыши вызвать контекстное меню
Выбрать пункт: запросы SQL(управление
Записать оператор на SQL-языке
Выполнить запрос
Теоретическая подготовка
Для каждого пункта задания записать оператор на SQL-языке:

Формальное описание команды Select.
SELECT [предикат] список полей
FROM таблица1 [,]
[WHERE условия отбора]
[GROUP BY]
[HAVING условия отбора]
[ORDER BY]


Список полей
*/таблица*/
[таблица.] поле_1
[AS псевдоним_1] [,таблица.] поле_2
[AS псевдоним_2] [,] ]
где * - выбираются все поля
поле_1, поле_2 – имена полей
псевдоним_1, псевдоним_1 – имена, которые станут заголовками столбцов

Минимальный синтаксис команды SELECT
SELECT поля FROM таблица
Выбор всех столбцов
SELECT * FROM таблица
Выбор конкретных столбцов
SELECT фамилия, имя, [дата рождения] FROM таблица
Использование псевдонимов
SELECT fam as [фамилия], name as [имя], dr as [дата рождения]
FROM таблица

В команде SELECT в списке полей могут быть выражения, представляющие новые вычисляемые поля
SELECT фамилия, имя, YEAR(NOW()-YEAR([дата рождения]))
FROM таблица


Примечания
Символьные строки заключаются в кавычки “ ”
Даты заключаются в решётки #
Псевдонимы не могут использоваться в предложении WHERE


В командах на SQL языке могут использоваться вычисляемые выражения, которые могут состоять из названия полей, констант, встроенных функций, операторов:
Виды операторов:
математические +, -, /, *
булевы or, and, not
отношения >, <
строковые &, like
like – используется для сравнения строки с шаблоном
Пример : строка like шаблон, в шаблон могут входить символы подстановки, указанные в таблице.

символ подстановки
значение

*
любая последовательность символов

?
1 символ А-Я, A-Z

#
1 цифра 0-9

[список]
1 символ из списка

[!список]
символ не из списка

Пример
SELECT fam FROM students WHERE fam LIKE “Р*” OR fam LIKE “C*”


Базовые команды SQL языка

Устранение строк-дубликатов в операторе SELECT
SELECT DI
·STINCT список полей FROM таблица

Выбор записей:
SELECT список полей FROM таблица WHERE условие отбора

В условии отбора используются операторы:
арифметические
+, -, *, /
логические
or, not, and
отношения
=,<>, >=
строковые
like, &
вспомогательные
BETWEENAND – проверяет принадлежность значения выражения диапазону.
Выражение BEETWEEN нижняя граница AND верхняя граница

IS – заполненность поля
Поле IS NULL

IN – проверяет принадлежность значения выражения списку
Выражение IN (список значений)

Сортировка строк в операторе SELECT
SELECT список полей FROM таблица
ORDER BY выражение1{ASC/DESC} [,выражение2{ASC/DESC}

ASC сортировка по алфавиту или возрастанию, DESC в обратном порядке
По умолчанию используется ASC

ПРИМЕР
SELECT fam FROM student ORDER BY fam ASC

Использование предиката (условие top в операторе SELECT)
Предикат top позволяет выбрать первые n отсортированных записей
SELECT top n список полей FROM таблица ORDER BY выражение

ПРИМЕР
Выбрать трёх сотрудников с наивысшей зарплатой
SELECT top 3 fam FROM sotr ORDER BY oklad DESC

Соединение таблиц в операторе SELECT
SELECT таблица1.поле1, таблица2.поле1 FROM таблица1 INNER {left,right} JOIN таблица2 ON таблица1.поле1 = таблица2.поле1

ПРИМЕР
В базе данных имеются таблицы студент (n,фамилия), успеваемость (n,оценка). Напечатать фамилии студентов, у которых есть оценки в таблице успеваемости.

SELECT stud.fam FROM stud INNER JOIN usp ON stud.n=usp.n

Создание групп данных

SELECT поле1, групповая функция (поле2) [,] FROM таблица
GROUP BY поле1
[HAVING условие отбора для групповых данных]

GROUP BY
разбивает таблицу на группы, групповая функция возвращает один результат на группу

Примечание 1. Список столбцов (полей) в GROUP BY обязателен.
Примечание 2. Использование псевдонимов не допустимо.
Примечание 3. Все столбцы, которые входят в список SELECT и к которым не применяются групповые функции, должны быть указаны в GROUP BY.
Групповые функции работают с множеством строк и возвращают один результат на группу.
Множество строк может быть целой таблицей или таблицей разбитой на группы.
Групповые функции
COUNT(поле) – считает только те записи, в которых задано значение
COUNT(*) – считает все записи
AVG(поле) и SUM(поле) применяются к числовым полям
MAX(поле) и MIN(поле) – к полям любого типа

Задание1 Посчитать количество студентов в каждой группе

SELECT gr AS [группа], COUNT(n) AS [количество] FROM student
GROUP BY gr

Изменение данных в таблице

UPDATE таблица SET поле1=новое значение1 [,поле2=новое значение2] [WHERE условия отбора]

ПРИМЕР
Поменять фамилию студентки из группы П-323
UPDATE student SET fam=“Кузнецова” WHERE fam=“Иванова” AND gr=“П323”
Удаление записей

DELETE * FROM таблица [WHERE условия отбора]

ПРИМЕР
Удалить из таблицы сотрудники тех сотрудников, у которых трудовой стаж меньше года.
DELETE * FROM sort WHERE year(NOW())-year([date])<1

Добавление записи в таблицу:

INSERT INTO имя_таблицы [(поле1[,поле2[, ...]])] VALUES (значение1 [,значение2 [, ...])

Пример: INSERT INTO Сотрудники (Номер, Имя, Фамилия, Должность) VALUES (5, 'Борис', 'Колесников', 'Ученик');
Если номер-счётчик, его значение не вводится.


Контрольные вопросы:
Назначение предикатов distinct и top в операторе SELECT.
Назначение ключевого слова GROUP BY в операторе SELECT.
Назовите групповые функции и их назначение.
Чем отличается условие отбора записей, указанное в WHERE, от условия отбора записей в HAVING?
Может ли в групповом запросе отсутствовать GROUP BY? Если может, то в каком случае?

Отчёт по практической работе N 6 студента _____________________ группы ___

Тема: «Сложные запросы»
Цель: «Научиться писать запросы к БД на языке SQL»
Выполнение работы

Записать отлаженные SQL команды:

1


2


3


4


5


6


7

8

9

10


11




Ответы на контрольные вопросы








Выводы о полученных практических навыках


Дата _____________ оценка __________________ подпись преподавателя

замечание


Приложенные файлы

  • doc 19153999
    Размер файла: 64 kB Загрузок: 0

Добавить комментарий