Изучение средств быстрого поиска информации в базе данных, создание запросов и подзапросов более сложного уровня

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

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

Государственное образовательное учреждение

Высшего профессионального образования

Новосибирский Государственный Технический Университет

Кафедра ВТ

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

по дисциплине «Базы данных»

Группа: АМ-411

Студент: Клещенев М.И.

Преподаватель: Трошина Г.В.

Вариант: 3

                                                            Новосибирск, 2007

Цель работы: Изучить средства быстрого поиска информации в базе данных и получение ответов на разнообразные запросы в среде FoxPro. Изучение конструктора запросов FoxPro. Научиться создавать запросы любого уровня сложности, производить различные вычисления над полями, выбираемыми из таблиц, осуществлять группировку полей запроса.

Индивидуальное задание: Выполнить запросы: вывод фамилий студентов, обучающихся в 2 и 7 корпусах и возрасте больше, чем средний возраст студентов 2,3 курсов ФЛА; вывод фамилий студентов, кроме обучающихся на 1,2 курсах ФЭН и ФЛА и возрасте больше, чем средний возраст студентов 2,3 курсов ФЛА; вывод фамилий студентов, обучающихся на 1,3 курсах АВТ и ФЛА и возрасте больше, чем средний возраст студентов 2,3 курсов ФЛА.

Для выполнения работы были созданы 2 таблицы, одна из которых содержит поле курс и идентификатор факультета, а вторая факультет и идентификатор факультета:

Рис.1  Структура БД

Рис.2 Форма для выбора подзапросов

Подзапрос №1: вывод фамилий студентов, обучающихся в 2 и 7 корпусах и возрасте больше, чем средний возраст студентов 2,3 курсов ФЛА

SELECT AVG(YEAR(DATE()) - YEAR(Table1.дата_рожде)) as Sr_vosrast;

 FROM  podsapros!table1 INNER JOIN podsapros!table2 ;

   ON  Table1.id_f = Table2.id_fac;

 WHERE Table2.факультет = "ФЭН";

   AND (Table1.курс = 2;

   OR Table1.курс = 3)

SELECT Table1.фамилия, Table1.дата_рожде, Table1.курс, Table1.корпус, Table2.факультет;

 FROM  podsapros!table1 INNER JOIN podsapros!table2 ;

   ON  Table1.id_f = Table2.id_fac;

 WHERE (YEAR(DATE()) - YEAR(Table1.дата_рожде)) > (select AVG(YEAR(DATE()) - YEAR(Table1.дата_рожде));

 FROM  podsapros!table1 INNER JOIN podsapros!table2 ;

   ON  Table1.id_f = Table2.id_fac;

 WHERE Table2.факультет = "ФЭН";

   AND (Table1.курс = 2;

   OR Table1.курс = 3));

 AND (Table1.корпус = 2;

   OR Table1.корпус = 3)

Рис.3 Вывод среднего возраста

Рис.4 Подзапрос №1

Подзапрос №2: вывод фамилий студентов, кроме обучающихся на 1,2 курсах ФЭН и ФЛА и возрасте больше, чем средний возраст студентов 2,3 курсов ФЛА

SELECT AVG(YEAR(DATE()) - YEAR(Table1.дата_рожде)) as Sr_vosrast;

 FROM  podsapros!table1 INNER JOIN podsapros!table2 ;

   ON  Table1.id_f = Table2.id_fac;

 WHERE Table2.факультет = "ФЭН";

   AND (Table1.курс = 2;

   OR Table1.курс = 3)

SELECT Table1.фамилия, Table1.дата_рожде, Table1.курс, Table1.корпус, Table2.факультет;

 FROM  podsapros!table1 INNER JOIN podsapros!table2 ;

   ON  Table1.id_f = Table2.id_fac;

 WHERE (YEAR(DATE()) - YEAR(Table1.дата_рожде)) > (select AVG(YEAR(DATE()) - YEAR(Table1.дата_рожде));

 FROM  podsapros!table1 INNER JOIN podsapros!table2 ;

   ON  Table1.id_f = Table2.id_fac;

 WHERE Table2.факультет = "ФЭН";

   AND (Table1.курс = 2;

   OR Table1.курс = 3));

 AND NOT ((Table1.курс = 1;

   OR Table1.курс = 2);

 AND (Table2.факультет = "ФЭН";

   OR Table2.факультет = "ФЛА"))

Рис.5 Подзапрос 2

Подзапрос №3: вывод фамилий студентов, обучающихся на 1,3 курсах АВТ и ФЛА и возрасте больше, чем средний возраст студентов 2,3 курсов ФЛА.

SELECT AVG(YEAR(DATE()) - YEAR(Table1.дата_рожде)) as Sr_vosrast;

 FROM  podsapros!table1 INNER JOIN podsapros!table2 ;

   ON  Table1.id_f = Table2.id_fac;

 WHERE Table2.факультет = "ФЭН";

   AND (Table1.курс = 2;

   OR Table1.курс = 3)

SELECT Table1.фамилия, Table1.дата_рожде, Table1.курс, Table1.корпус, Table2.факультет;

 FROM  podsapros!table1 INNER JOIN podsapros!table2 ;

   ON  Table1.id_f = Table2.id_fac;

 WHERE (YEAR(DATE()) - YEAR(Table1.дата_рожде)) > (select AVG(YEAR(DATE()) - YEAR(Table1.дата_рожде));

 FROM  podsapros!table1 INNER JOIN podsapros!table2 ;

   ON  Table1.id_f = Table2.id_fac;

 WHERE Table2.факультет = "ФЭН";

   AND (Table1.курс = 2;

   OR Table1.курс = 3));

 AND (Table1.курс = 1;

   OR Table1.курс = 3);

 AND (Table2.факультет = "АВТ";

   OR Table2.факультет = "ФЛА")

Рис.6 Подзапрос №3

Подзапрос №4: вывод фамилий студентов, обучающихся в аудиториях с 301 по 320, родившихся в Омске или Томске, получающих стипендию больше чем средняя стипендия студентов, имеющих книги издательства «Наука» и «Сибирь».

SELECT AVG(Table1.стипендия);

 FROM  podsapros!table1 INNER JOIN podsapros!table2 ;

   ON  Table1.id_f = Table2.id_fac;

 WHERE Table1.книги = "Наука";

   OR Table1.книги = "Сибирь"

SELECT Table1.фамилия, Table1.курс, Table1.корпус, Table2.факультет, Table1.книги, Table1.аудитория, Table1.город, Table1.стипендия;

 FROM  podsapros!table1 INNER JOIN podsapros!table2 ;

   ON  Table1.id_f = Table2.id_fac;

 WHERE (Table1.стипендия) > (select AVG(Table1.стипендия);

 FROM  podsapros!table1 INNER JOIN podsapros!table2 ;

   ON  Table1.id_f = Table2.id_fac;

 WHERE Table1.книги = "Наука";

   OR Table1.книги = "Сибирь");

 AND (Table1.аудитория >= 301;

   AND Table1.аудитория <= 320);

 AND (Table1.город = "Омск";

   OR Table1.город = "Томск")

Рис.7 Подзапрос №4

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