Изменение таблицы (добавление полей)

ALTER TABLE имя_таблицы ADD имя_поля тип_поля;

ЗАПОЛНЕНИЕ ТАБЛИЦЫ ДАННЫМИ

INSERT INTO имя_таблицы VALUES (…,…);

УДАЛЕНИЕ ТАБЛИЦЫ

DROP TABLE имя_таблицы;

1. Создайте в MS ACCESS базу данных, назвав ее своей фамилией с указанием номера группы.

2. Создайте запрос с помощью конструктора запросов (таблицы не добавлять).

3. Перейдите в режим команд SQL

Конструктор – Тип запроса / Управление – Режим SQL

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

Студенты (id_student, familiya, imya, otchestvo, address, data_rozhd, nomer_group)

Преподаватели (id_prepod, familiya, imya, otchestvo, address, kafedra)

Кафедра (id_kafedra, nazvanie, kol_sotrudnikov)

Факультет (kod_fakultet, nazvanie, kol_group)

5. В таблицу Студенты добавьте поле licevoi_schet

6. В таблицу Факультет добавьте поле fio_dekana

7. Внесите в каждую таблицу по 2-3 записи

8. Сделайте скриншот экрана и вставьте его в текстовый документ

9. Удалите таблицу Кафедра

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

«SQL-запросы. Создание запроса на выборку.»

Цель работы: получить навыки создания и редактирования запросов по заданным параметрам из таблиц базы данных, используя команды SQL.

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

Справочная информация

Команда SELECT предназначена для выборки из таблиц требуемых полей и записей. В простейшем случае выборки всех полей из таблицы Студенты команда имеет вид:

SELECT * FROM Студенты

По команде SELECT[Фамилия],[Имя] FROM Студенты

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

Сортировка записей по заданному полю выполняется с использованием ключевого слова ORDER BY:

SELECT[Фамилия],[Имя] FROM Студенты ORDER BY[Фамилия] ASC

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

По команде SELECT TOP 5[Фамилия] FROM Студенты ORDER BY[Фамилия] ASC

выбираются первые 5 по алфавиту записей.

По команде SELECT [Фамилия]&” “&[Имя] AS ФИО FROM Студенты

создаётся вычисляемое поле ФИО, являющееся сцеплением строк Фамилия и Имя. Между этими строками вставляется строка из единственного пробела, чтобы слова были записаны раздельно.

По команде SELECT DISTINCT[Дата рождения] AS[Юбилей] FROM Студенты

извлекаются только уникальные записи поля Дата рождения. Это поле в запросе переименовано в поле Юбилей.

Командой SELECT с использованием ключевого слова WHERE осуществляется отбор записей по указанному условию. Любое условие формируется с использованием операций отношения <, >, <=, >=, которые могут объединяться с использованием операций AND (логическое И), OR (логическое ИЛИ) и NOT (логическое НЕ). например, по команде SELECT[Фамилия],[Оценка] FROM Студенты WHERE[Оценка]<=3 OR[Оценка]>4

отбираются только фамилии с оценками 2, 3, 5.

В условии WHERE могут использоваться операторы для построения условий:

BETWEEN … AND … - выполняет проверку на диапазон значений;

LIKE – выполняет проверку сравнение строковых значений;

IN – выполняет проверку выражения на совпадение с любым из элементов списка.

Например, по команде

SELECT * FROM Студенты WHERE[Год рождения] BETWEEN 1981 AND 1990 AND [Группа] IN(“1212”, “1213”)

выбираются записи, у которых год рождения в диапазоне от 1981 до 1990 включительно и которые находятся в группе из указанного списка: 1212, 1213 (элементы списка в IN указаны в двойных кавычках, так как группа является данным текстового типа, для числовых данных элементы списка в кавычках указывать не надо).

По команде: SELECT * FROM Студенты WHERE[Фамилия] LIKE “А*”

из таблицы Студенты выбираются только записи, чьи фамилии начинаются на букву А (звёздочка в инструкции LIKE означает все остальное).

Задание 1. Создать запрос, содержащий все поля таблицы Эксперты.

Задание 2.Создать запрос, содержащий поля Фамилия, Имя, Ученая степень таблицы Эксперты.

Задание 3.Создать запрос, содержащий поля Фамилия, Имя, Контактный телефон таблицы Эксперты с упорядочением по алфавиту записей по полям Фамилия и Имя.

Задание 4.Создать запрос, содержащий поля Фамилия, Имя, Сфера компетентности таблицы Эксперты и выбирающий только первые 5 записей по алфавиту поля Фамилия.

Задание 5. Создать запрос, содержащий поля Фамилия, Имя таблицы Эксперты и выбирающий записи с фамилией Котов.

Задание 6.Создать запрос, содержащий поля Фамилия, Имя таблицы Эксперты и выбирающий записи фамилий, начинающихся на букву К.

Задание 7.Создать запрос, содержащий поля Фамилия, Имя таблицы Эксперты и выбирающий только кандидатов наук.

Задание 8.Создать запрос, содержащий поля Эксперт, Количество часов таблицы Выполненные работы и выбирающий записи с количеством часов <3.

Задание 9.Создать запрос, содержащий поля Фамилия, Имя, Код организации таблицы Эксперты и выбирающий записи с фамилиями от буквы К до буквы Н включительно.

Задание 10.Создать запрос, содержащий поля Эксперт, Тема, Дата таблицы Выполненные работы и выбирающий записи с датой до 1 марта 1990 года.

Задание 11.Создать запрос, содержащий поля Эксперт, Дата, Количество часов таблицы Выполненные работы и выбирающий записи с датой от 5 марта 1985 года до 4 февраля 2001 года.

Задание 12.Создать запрос, содержащий поля Код организации, Город, Телефон таблицы Организации и выбирающий записи с кодом организации до 3 и после 6.

Задание 13.Создать запрос, содержащий поля Код организации, Город, Телефон таблицы Организации и выбирающий записи с кодом организации от 4 до 7 включительно и с городом Минск.

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

«SQL-запросы. Запросы с вычислениями.»

Цель работы: получить навыки создания и редактирования запросов по заданным параметрам с вычислениями из таблиц базы данных, используя команды SQL.

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

Справочная информация

Групповые функции SQL

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

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

· Avg – вычисление среднего арифметического;

· Sum – вычисление суммы;

· Min, Max – вычисление минимума или максимума;

· Count – вычисление количества записей;

· First – выводит первое встретившееся значение поля

Например, по команде:

SELECT First([фамилия]), Avg([оценка]) FROM Результаты

GROUP BY [№ зачетной книжки]

группировка осуществляется по номеру зачетной книжки; при этом в запрос выводятся фамилии и средний балл для каждой фамилии.

Более правильно для полей, над которыми выполнена групповая функция, указывать еще и новое имя поля, т.к. группировка функции – это арифметическая операция над полем:

SELECT First ([фамилия]) AS фамилия,Avg ([оценка]) AS [средний балл],

FROM Результаты GROUP BY [№ зачетной книжки]

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

SELECT Avg ([оценка]) AS [средний балл], FROM Результаты

WHERE [оценка] >2 GROUP BY [фамилия]

В этом запросе средняя оценка по заданной фамилии будет вычисляться без учета оценки 2.

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

SELECT Avg ([оценка]) AS [средний балл],FROM Результаты

GROUP BY [Код дисциплины] HAVING Avg ([оценка]) < 4

в запросе помещаются только записи со средней оценкой меньше 4.

В условии HAVING можно использовать все операции доступные в условии WHERE.

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

Командой SELECT можно выполнять выборку записей из двух таблиц. Для этого используется ключевое слово INNER JOIN. По этому ключевому слову в запрос помещаются записи, одновременно именующиеся в обеих таблицах. Например, по команде:

SELECT студенты.фамилия, оценки.оценка

FROM студенты INNER JOIN оценки

ON студенты.[код студента]=оценки.[код студента]

Из таблицы Студенты извлекается поле Фамилия, а из таблицы Оценки — поле Оценка. Какую из связанных двух таблиц указывать перед INNER JOIN, а какую – после, в данном случае безразлично. В команде, как видно из примера, необходимо после ключевого слова ON указать поля, по которым таблицы связаны между собой. Заметим, что при выборе записей из двух таблиц извлекаемое поле обязательно необходимо указывать вместе с указанием через точку и самой таблицы.

Вместо ключевого слова INNER JOIN можно использовать LEFT JOIN или RIGHT JOIN. В частности, при использовании LEFT JOIN будут извлекаться все записи таблицы, указанной слева от LEFT JOIN, и соответствующие записи, имеющиеся в правой таблице. Если для записи левой таблицы нет соответствующей записи правой таблицы, то на этом месте выводятся пробелы. Например, по команде:

SELECT студенты.фамилия, оценки.оценка FROM студенты LEFT JOIN оценки

ON студенты.[код студента]=оценки.[код студента]

Запрос будет выглядеть следующим образом:

Фамилия	Оценка
Иванов
Петров
Сидоров
Коробов

Как видим, в поле Оценка для Иванова и Коробова оценка не проставлена (стоит пробел). Это значит, что в таблице Оценки для этих фамилий не существовало соответствующих записей.

При выборке из трех таблиц также используются ключевые слова INNER JOIN, LEFT JOIN и RIGHT JOIN. Для формирования связи для третьей таблицы необходимо взять в круглые скобки всю инструкцию JOIN для первых двух таблиц. При этом открывающая скобка ставится сразу за ключевым словом FROM, а закрывающая скобка – в конце инструкции:

SELECT студенты.*, оценки.*, организации.* FROM (студенты LEFT JOIN оценки

ON студенты.[код студента]=оценки.[код студента]) INNER JOIN организации

ON студенты.[код организации]=организации.[код организации]

При выборе записей из нескольких таблиц можно использовать условие отбора WHERE или группировку GROUP BY, которые должны располагаться за инструкциями JOIN:

SELECT студенты.*, оценки.*

FROM студенты LEFT JOIN оценки

ON студенты.[код студента]=оценки.[код студента]

WHERE студенты.фамилия < ”K”

Удаление записей в таблице выполняется командой DELETE. Например, по команде DELETE * FROM Cтудент удалятся все записи из таблицы студент.

По команде

DELETE * FROM Cтудент

WHERE [дата рождения] > #01/01/81#

удаляются записи с датой рождения > 01.01.81 г.

Отметим, что даты набираются по стандарту США, т.е. месяц/число/год.

По команде

DELETE *FROM Cтудент INNER JOIN [Cтудент-заочник]

ON Cтудент.группа=[Cтудент заочник].группа

удаляются записи в таблице Cтудент, для которых имеются связанные записи в таблице Cтудент заочник.

Удаление поля выполняется командой:

ALTER TABLE Cтудент DROP COLUMN [дата]

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

Добавление в таблицу нового поля выполняется командой:

ALTER TABLE Cтудент ADD COLUMN [группа] TEXT (5)

по которой, в данном случае, в таблицу Cтудент добавляется новое поле группа, имеющего текстовый тип с 5 знаками.

Для указания числового типа можно использовать SINGLE (число одинарной точности) или DOUBLE (число двойной точности).

Формирование новой записи в таблицу выполняется командой INSERT INTO:

INSERT INTO Cтудент ([фамилия], [имя], [дата рождения])

VALUES («Петров», «Иван», #03/23/80#)

по которой добавляется новая запись со значениями соответственно Петров, Иван, 23.03.80.

По команде:

INSERT INTO Cтудент SELECT [Cтудент-заочник].* FROM [Cтудент-заочник]

все записи таблицы Cтудент-заочник будут добавлены в таблицу Cтудент. Заметим, что в операторе SELECT этой команды можно использовать ключевое слово WHERE.

Замена значения полей выполняется с использованием команды UPDATE. Например, по команде:

UPDATE Cтудент SET [группа]=1212

WHERE [фамилия] LIKE “B*”

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

По команде:

UPDATE Cтудент INNER JOIN [Cтудент-заочник] ON Cтудент.Группа = [Cтудент-заочник].группа

SET [группа]=[группа]&”З”

в таблице Cтудент изменяется номер группы добавлением буквы З, если эта запись встречается в таблице Cтудент-заочник.

Удаление всей таблицы выполняется по команде:

DROP TABLE Cтудент

по которой будет удалена таблица Cтудент.

Наиболее просто запросы формировать совместно в конструкторе и для нереализованных в конструкторе возможностей в SQL.

Например, в сформированной нами базе данных необходимо выбрать эксперта, который раньше всех провел тему. Для этого в конструкторе сформируем запрос, состоящий из полей Фамилия, Имя таблицы Эксперты и поля Дататаблицы Выполненные работы. Отсортируем записи в порядке возрастания даты. Перейдем в режим SQL и для выбора одного первого эксперта добавим после команды SELECT ключевое слово TOP 1.

Задание 1. Создать запрос, содержащий поля Эксперт, Кол-во часов таблицы Выполненные работы и вычисляющий суммарное число часов для каждого Эксперт.

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

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

Задание 4. Создать запрос, содержащий поля Эксперт, Кол-во часов таблицы Выполненные работы и вычисляющий суммарное число часов по каждому коду эксперта с отбором суммарного числа часов < 6.

Задание 5. Создать запрос, выбирающий поля Фамилия, Имя таблицы Экспертыи поле Кол-во часов таблицы Выполненные работы.

Задание 6. Создать запрос, выбирающий поля Фамилия, Имя таблицы Эксперты, поле Город таблицы Организации и поле Тема таблицы Выполненные работы.

Задание 7. Создать запрос, выбирающий поля Фамилия, Имя таблицы Эксперты, поле Кол-во часов таблицы Выполненные работы и вычисляющий суммарное число часов по каждому коду эксперта.

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

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

Задание 10. Создать запрос, выбирающий поля Фамилия, Имя таблицы Эксперты, поле Организация таблицы Организации и выбирающий организации с номером < 6.

Задание 11. Создать запрос, выбирающий поля Фамилия, Имя, Тарифтаблицы Эксперты, поле Кол-во часов таблицы Выполненные работы, вычисляемое поле Гонорар как произведение тарифа на кол-во часов и вычисляющий посредством группировки по полю Код эксперта суммарный гонорар по каждому эксперту.

Задание 12. Создать запрос, выбирающий поля Фамилия, Имя таблицы Эксперты, поле Организация из таблицы Организациии выбирающий среди сотрудников только канд. наук.

Задание 13.

Удалить из таблицы Эксперты записи с фамилией Котов.

Задание 14

Удалить из таблицы Организации записи с Кодом организации от 4 до 6.

Задание 15

Удалить из таблицы Эксперты записи, для которых имеются связанные записи в таблице Выполненные работы.

Задание 16

Из таблицы Эксперты удалить поле Тариф.

Задание 17

В таблице Эксперты добавить числовое поле Тариф.

Задание 18

В таблицу Эксперты добавить новую запись. Значения полей выбрать по своему усмотрению.

Задание 19

Заменить запись поля Ученая степень на д. ф-м. н. с фамилией Баранов.

Задание 20

Заменить запись поля Тариф на 10000, если существует соответствующая запись в таблице Выполненные работы.

Задание 21

В таблице Организации добавить текстовое поле Страна.

Задание 22

Заменить значение поля Страна на Беларусь для записей, у которых поле Город равно Минск.

Задание 23

Совместным использованием конструктора и SQL выберите трех экспертов, прочитавших наибольшее суммарное число часов.

Задание 24

Совместным использованием конструктора и SQL выберите эксперта, прочитавшего наименьшее число тем.

Задание 25

Совместным использованием конструктора и SQL выберите трех экспертов, прочитавших темы последними.

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

«Структура Web-страницы. Язык HTML.