Министерство образования и науки Российской Федерации
Федеральное агентство по образованию
Государственное образовательное учреждение
Высшего профессионального образования
Новосибирский государственный технический университет
Кафедра вычислительной техники
 |
Лабораторная работа № 6
по дисциплине “Базы данных”
“ Создание подзапросов ”
Группа: АМ-411
Выполнил:
Вариант: 4
Преподаватель:
Новосибирск, 2007 г.
I. Цель работы
Изучить средства быстрого поиска информации в базе данных и получение ответов на разнообразные запросы в среде FoxPro. Изучение конструктора запросов FoxPro. Научиться создавать запросы любого уровня сложности, производить различные вычисления над полями, выбираемыми из таблиц, осуществлять группировку полей запроса.
II. Задание
Выполнить выбор записей по диапазону значений для БД. Осуществить выборку из нескольких таблиц. Продемонстрировать приобретённые навыки построения многотабличных запросов. Одна таблица (минимально) должна содержать поля: факультет, идентификатор факультета в обязательном порядке, другая (минимально) – курс идентификатор факультета в обязательном порядке.
Выполнить запросы:
1). Вывод авторов учебников со стоимостью от 20 до 100 рублей и в возрасте больше, чем средний возраст студентов 1 и 3 курса.
2). Вывод фамилий студентов, кроме обучающихся на первом курсе всех факультетов и в возрасте больше, чем средний возраст студентов 1 и 3 курса.
3). Выборка фамилий всех студентов 2 и 3 курса факультета ПМТ и в возрасте больше, чем средний возраст студентов 1 и 3 курса.
4). Вывести студентов, имеющих книги издательства «Наука» или «Сибирь», выпущенных с 1985 по 1995 год, обучающихся в аудиториях с 301 по 320 и получающих стипендию больше, чем средняя стипендия студентов, обучающихся у лекторов Иванова и Петрова, кроме студентов 2 и 5 курса факультетов АВТФ и ФЛА.
III. Результаты работы
Для выполнения данной работы создадим проект с 2 таблицами (рис.1):
Рис.1 Структура БД
Рис.2 Запросы к БД
Подзапрос №1: Вывод авторов учебников со стоимостью от 20 до 100 рублей и в возрасте больше, чем средний возраст студентов 1 и 3 курса.
Текст SQL - запроса:
SELECT (AVG(YEAR(DATE()) - YEAR(Knigi2.дата_рождения))) as "Средний_Возраст" ;
FROM data1!knigi2 INNER JOIN data1!fakultet_table ;
ON Knigi2.id_fakultet = fakultet_table. id_fakultet;
WHERE Knigi2.курс IN ("1","3");
SELECT Knigi2.автор, (YEAR(DATE()) - YEAR(knigi2.дата_рождения)) as "Возраст_владельца";
FROM data1!knigi2 INNER JOIN data1!fakultet_table ;
ON Knigi2.id_fakultet = fakultet_table. id_fakultet;
WHERE (YEAR(DATE()) - YEAR(knigi2.дата_рождения)) > (SELECT (AVG(YEAR(DATE()) - YEAR(Knigi2.дата_рождения)));
FROM data1!knigi2 INNER JOIN data1!fakultet_table ;
ON Knigi2.id_fakultet = fakultet_table. id_fakultet;
WHERE Knigi2.курс IN ("1","3"));
AND Knigi2.стоимость BETWEEN 20 AND 100
Результат запроса:
Рис.3 Вывод среднего возраста
Рис.4 Подзапрос №1
Подзапрос №2: Вывод фамилий студентов, кроме обучающихся на первом курсе всех факультетов и в возрасте больше, чем средний возраст студентов 1 и 3 курса.
SELECT (AVG(YEAR(DATE()) - YEAR(Knigi2.дата_рождения))) as "Средний_Возраст" ;
FROM data1!knigi2 INNER JOIN data1!fakultet_table ;
ON Knigi2.id_fakultet = fakultet_table. id_fakultet;
WHERE Knigi2.курс IN ("1","3");
SELECT Knigi2.фамилия+LEFT(Knigi2.имя,1)+"."+LEFT(Knigi2.отчество,1)+"." AS фио, Knigi2.курс, (YEAR(DATE()) - YEAR(knigi2.дата_рождения)) as "Возраст";
FROM data1!knigi2 INNER JOIN data1!fakultet_table ;
ON Knigi2.id_fakultet = fakultet_table. id_fakultet;
WHERE (YEAR(DATE()) - YEAR(knigi2.дата_рождения)) > (SELECT (AVG(YEAR(DATE()) - YEAR(Knigi2.дата_рождения)));
FROM data1!knigi2 INNER JOIN data1!fakultet_table ;
ON Knigi2.id_fakultet = fakultet_table. id_fakultet;
WHERE Knigi2.курс IN ("1","3"));
AND Knigi2.курс <> "1"
Результат запроса:
Рис.5 Подзапрос №2.
Подзапрос №3. Выборка фамилий всех студентов 2 и 3 курса факультета ПМТ и в возрасте больше, чем средний возраст студентов 1 и 3 курса.
SELECT (AVG(YEAR(DATE()) - YEAR(Knigi2.дата_рождения))) as "Средний_Возраст" ;
FROM data1!knigi2 INNER JOIN data1!fakultet_table ;
ON Knigi2.id_fakultet = fakultet_table. id_fakultet;
WHERE Knigi2.курс IN ("1","3");
SELECT Knigi2.фамилия+LEFT(Knigi2.имя,1)+"."+LEFT(Knigi2.отчество,1)+"." AS фио, Knigi2.курс, fakultet_table. факультет,(YEAR(DATE()) - YEAR(knigi2.дата_рождения)) as "Возраст";
FROM data1!knigi2 INNER JOIN data1!fakultet_table ;
ON Knigi2.id_fakultet = fakultet_table. id_fakultet;
WHERE (YEAR(DATE()) - YEAR(knigi2.дата_рождения)) > (SELECT (AVG(YEAR(DATE()) - YEAR(Knigi2.дата_рождения)));
FROM data1!knigi2 INNER JOIN data1!fakultet_table ;
ON Knigi2.id_fakultet = fakultet_table. id_fakultet;
WHERE Knigi2.курс IN ("1","3"));
AND fakultet_table. факультет == "ПМТ";
AND Knigi2.курс IN ("2","3")
Рис.6 Подзапрос №3
Подзапрос №4. Вывести студентов, имеющих книги издательства «Наука» или «Сибирь», выпущенных с 1985 по 1995 год, обучающихся в аудиториях с 301 по 320 и получающих стипендию больше, чем средняя стипендия студентов, обучающихся у лекторов Иванова и Петрова, кроме студентов 2 и 5 курса факультетов АВТФ и ФЛА.
SELECT (AVG(Knigi2.стипендия)) as "Средняя_стипендия" ;
FROM data1!knigi2 INNER JOIN data1!fakultet_table ;
ON Knigi2.id_fakultet = fakultet_table. id_fakultet;
WHERE Knigi2.лектор IN ("Иванов","Петров");
SELECT Knigi2.фамилия+LEFT(Knigi2.имя,1)+"."+LEFT(Knigi2.отчество,1)+"." AS фио, Knigi2.курс, fakultet_table. факультет, Knigi2.стипендия, Knigi2.лектор, Knigi2.издательство, Knigi2.год_издания, Knigi2.аудитория;
FROM data1!knigi2 INNER JOIN data1!fakultet_table ;
ON Knigi2.id_fakultet = fakultet_table. id_fakultet;
WHERE (Knigi2.стипендия) > (SELECT (AVG(Knigi2.стипендия));
FROM data1!knigi2 INNER JOIN data1!fakultet_table ;
ON Knigi2.id_fakultet = fakultet_table. id_fakultet;
WHERE Knigi2.лектор IN ("Иванов","Петров"));
AND not (Knigi2.курс in ("2","5") and fakultet_table. факультет in ("АВТФ","ФЛА"));
AND Knigi2.издательство IN ("Наука","Сибирь");
AND Knigi2.год_издания BETWEEN 1985 AND 1995;
AND Knigi2.аудитория BETWEEN 301 AND 320
Рис.7 Вывод средней стипендии студентов лекторов Иванова и Петрова
Рис.8 Подзапрос №4
Если изменить у студента Петрова факультет – на ФЛА, то результат будет следующим:
Рис.9 Подзапрос №4
Вывод: Изучил средства быстрого поиска информации в базе данных, научился создавать запросы и подзапросы более сложного уровня, производить различные вычисления над полями, выбираемыми из таблиц, осуществлять группировку полей запроса. Были получены и закреплены знания по SQL-запросам, а также многотабличным связываниям.
