Методические материалы к компьютерному практикуму по дисциплине «Базы данных»

Министерство образования Российской Федерации

Федеральное агентство по образованию

ГОУ ВПО «Уральский государственный технический университет»

Кафедра «Вычислительная техника»

SQL – язык структурированных запросов

Методические материалы к компьютерному практикуму по дисциплине

«Базы данных»

Cоставил Кара-, доцент кафедры вычислительной техники, кандидат физико-математических наук.

Екатеринбург

2007

Создание, модификация структуры и удаление табЛИЦ

1.  Создать таблицы

Kafedra (Spec, Fak) Spec – первичный ключ

Student (Nomer, Fio, Spec) Nomer – первичный ключ

Spec – внешний ключ – ссылка на Kafedra

Session (Nomer, Sub, Sem, Ball) (Nomer, Sub, Sem) – первичный ключ

Nomer – внешний ключ – ссылка на

Student

CREATE TABLE Kafedra (Spec text(5), Fak text(2),

CONSTRAINT PKKafedra PRIMARY KEY (Spec));

CREATE TABLE Student ( Nomer text(6), Fio text(15), Spec text(5),

CONSTRAINT PKStudent PRIMARY KEY (Nomer),

CONSTRAINT FKStudent FOREIGN KEY (Spec) REFERENCES Kafedra(Spec) );

CREATE TABLE Session (Nomer text(6), Sub text(15), Sem text(2), Ball Integer,

CONSTRAINT PKSession PRIMARY KEY (Nomer, Sub, Sem),

CONSTRAINT FKSession FOREIGN KEY (Nomer) REFERENCES Student(Nomer) );

2.  Изменить структуру таблицы Kafedra, например переименовать поле Fak в Fac.

2.1.  Добавить в структуру таблицы Kafedra новое поле Fac

ALTER TABLE Kafedra ADD COLUMN Fac text(2) NOT NULL;

2.2.  Удалить поле Fak

ALTER TABLE Kafedra DROP COLUMN Fak;

3.  Удалить таблицу Session.

DROP TABLE Session;

4.  Восстановить таблицу Session. Запустить команду CREATE TABLE на создание таблицы Session (задание 1)

Корректирующие запросы

5.  Заполнить таблицу Kafedra непосредственно следующими данными

Spec	Fac
с3	ф2
с2	Ф2
с1	ф1

INSERT INTO Kafedra ( Spec, Fac ) VALUES ('с2','ф2');

6.  Данные для заполнения таблицы Student взять из таблицы Study.

Study (Nomer, Fio, Spec, Fac,Sub, Sem, Ball)

Заполнить таблицу Student данными из таблицы Study

INSERT INTO Student (Nomer, Fio, Spec )

SELECT DISTINCT Nomer, Fio, Spec FROM Study;

7.  Данные для заполнения таблицы Session взять из таблицы Study.

Study (Nomer, Fio, Spec, Fac,Sub, Sem, Ball)

Заполнить таблицу Session данными из таблицы Study

INSERT INTO Session ( Nomer, Sub, Sem, Ball )

SELECT DISTINCT Nomer, Sub, Sem, Ball FROM Study;

8.  Обновить данные в поле Fio таблицы Student – заменить фамилию «» на «»

UPDATE Student SET Fio = ‘’ WHERE Fio = ‘’;

9.  Удалить данные из таблицы Session

DELETE FROM Session;

10.  Восстановить данные в таблице Session. Запустить команду INSERT INTO на заполнение таблицы Session (задание 7)

11.  Удалить таблицу Kafedra. Объясните причину неудачи.

DROP TABLE Kafedra;

12.  Изменить в таблице Kafedra номер специальности (Nomer) ‘931512’ на ‘111111’. Объясните причину неудачи.

UPDATE Kafedra SET Nomer = ‘111111’ WHERE Nomer = ‘031512’;

13.  Удалить все строки таблицы Student. Объясните причину неудачи.

DELETE FROM Student;

Запросы - выборки

1.  Вывести все поля таблицы Study

2.  Вывести (из таблицы Study) данные о студентах (поля Nomer, Fio, Spec, Fac)

3.  Вывести без дублей строк (из таблицы Study) данные о студентах (поля Nomer, Fio, Spec, Fac)

4.  Вывести (из таблицы Study) результаты сдачи экзаменов по предмету, заданному по первым буквам названия (атрибут Sub). Запрос оформить как параметрический с Like

5.  Вывести (из таблицы Study) все данные о студентах, сдавших экзамены на 4 и 5. Запрос оформить как выборку с Between

6.  Вывести (из таблицы Study) все данные о студентах, обучающихся по специальностям факультета «ф2», т. е. {с2, с3}. Запрос оформить как выборку с In.

7.  Вывести (из таблицы Study) все данные о студентах, упорядоченные по алфавиту названий факультетов (атрибут Fac) и по убыванию номеров специальностей (атрибут Spec). Запрос оформить как выборку с ORDER BY.

8.  Вывести (из таблицы Study) учетные номера (Nomer), фамилии (Fio), а так же количество сданных экзаменов (поле с именем «количество экзаменов» на основе функции COUNT(Ball)) и средний балл для каждого студента (поле с именем «средний балл» на основе функции AVG(Ball)). Запрос оформить на основе фразы GROUP BY.

9.  Дополнить предыдущее задание условием выборки только тех студентов, у которых средний бал не меньше 4. Запрос оформить со спецификатором условий отбора групп HAVING.

10.  Вычислить декартово произведение таблиц Kafedra и Student.

11.  Вычислить естественное соединение таблиц Kafedra и Student.

12.  Вычислить естественное соединение таблиц Kafedra и Student на основе команды INNER JOIN.

13.  Вычислить естественное соединение таблиц Kafedra, Student и Session. Результат сравнить с таблицей Study.

14.  Выполнить как запрос с подзапросом выборку (из таблицы Student) всех данных о студентах, обучающихся по той же специальности, что и студент с номером 016510.

15.  Выполнить как запрос с подзапросом выборку (из таблицы Kafedra) названия факультета, на котором обучается студент с номером 036510 (из таблицы Student).

16.  Выполнить как запрос с подзапросом ко множеству значений (на основе предиката IN) выборку (из таблицы Student) всех данных о студентах, обучающихся по специальностям факультета «ф2».

17.  Выполнить как запрос с подзапросом ко множеству значений (на основе предиката ALL) выборку (из таблицы Study) всех данных о студентах, у которых оценка за экзамен меньше, чем у всех студентов специальности «с2».

18.  Выполнить как запрос с подзапросом ко множеству значений (на основе предиката ANY) выборку (из таблицы Study) всех данных о студентах, у которых оценка за экзамен меньше, чем оценка у любого, хотя бы одного студента специальности «с2».

19.  Выполнить как коррелированный подзапрос выборку (из таблицы Student) всех данных о студентах, которые сдавали экзамен по, например, физике.

20.  Выполнить как запрос с подзапросом ко множеству значений (на основе предиката EXISTS) выборку (из таблицы Student) всех данных о студентах, которые учатся на факультете «ф2».

21.  Выполнить запрос на объединение (на основе команды UNION): вывести номера студентов, обучающихся по специальности «с2» (таблица Student) или сдавших экзамены на 4 и 5 (таблица Session).

22.  Выполнить запрос на пересечение (на основе предиката EXISTS): вывести номера студентов, обучающихся по специальности «с2» (таблица Student) и сдавших экзамены на 4 и 5 (таблица Session).

23.  Выполнить запрос на разность (на основе NOT EXISTS): вывести номера студентов, обучающихся по специальности «с2» (таблица Student), но не сдавших экзамены на 4 и 5 (таблица Session).

24.  Выполнить запрос на разность: вывести номера студентов, сдавших экзамены на 4 и 5 (таблица Session), но не обучающихся по специальности «с2» (таблица Student).