Создание подзапросов. Построение многотабличных запросов

МИНИСТЕРСТВО ОБРАЗОВАНИЯ И НАУКИ РОССИЙСКОЙ ФЕДЕРАЦИИ

ФЕДЕРАЛЬНОЕ АГЕНТСТВО ПО ОБРАЗОВАНИЮ

ГОСУДАРСТВЕННОЕ ОБРАЗОВАТЕЛЬНОЕ УЧРЕЖДЕНИЕ ВЫСШЕГО

ПРОФЕССИОНАЛЬНОГО ОБРАЗОВАНИЯ

НОВОСИБИРСКИЙ ГОСУДАРСТВЕННЫЙ ТЕХНИЧЕСКИЙ УНИВЕРСИТЕТ

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

«Создание подзапросов»

Группа: Ам-510                                                                   Проверил: Трошина Г.В.

Вариант: 3

Выполнил:  Мельников А.В.

Новосибирск

2008г.

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

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

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

2. По предложенному преподавателем варианту выполнить запросы  и подзапросы в среде Visual FoxPro.

3. Продемонстрировать навыки построения SQL-запросов и подзапросов.

4. Оформить отчет по лабораторной работе. Отчет должен содержать: титульный лист, цель работы, задание, тексты запросов, результаты работы, анализ результатов и выводы по работе.         

5. Защитить лабораторную работу у преподавателя, ответив на контрольные вопросы.

Задание

Выполнить выбор записей по диапазону значений для заданной базы данных. Осуществить выборку из нескольких таблиц. Продемонстрировать приобретенные навыки построения многотабличных запросов. Одна таблица (минимально) должна содержать поля: факультет, идентификатор факультета в обязательном порядке, другая  (минимально) – курс, идентификатор факультета в обязательном порядке.

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

Выполнение работы:

Рис.1. Использование формы для представления результатов запросов

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

Рис.2. Исходная таблица 1.dbf

Исходная таблица включает такие поля, как индекс – уникальный идентификатор также Имя, Фамилия, Отчество и другие.

Рис.3. Исходная таблица 11.dbf

Исходная таблица включает такие поля, как индекс – уникальный идентификатор, Откуда - уникальный идентификатор места жительства. Стипендия- денежное довольствие студента.

Рис.4. Исходная таблица 111.dbf

Исходная таблица включает такие поля, как индекс– уникальный идентификатор названия Факультета, факультет - собственно само название факультета

Текст вспомогательного запроса для вывода среднего возраста студентов факультета АВТ

SELECT AVG(YEAR(DATE())-YEAR(_1.родился));

 FROM 1 _1;

   INNER JOIN 111 _111 ;

   ON  _1.факультет = _111.индекс;

 WHERE _1.курс In (2,3);

   AND _111.факультет = "ФАМ"

Рис.5. Результат работы запроса

Запрос 1

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

Код 1 запроса

SELECT _1.фамилия, _1.корпус,_11.откуда,YEAR(DATE())-YEAR(_1.родился) ;

 FROM  1 _1;

 INNER JOIN 11 _11;

   ON  _1.индекс = _11.индекс;

     INNER JOIN 111 _111 ;

   ON  _1.факультет = _111.индекс;

 WHERE _1.корпус IN (7,2);

 AND _11.откуда IN ("Бердск","Томск");

 AND  YEAR(DATE())-YEAR(_1.родился) >  (SELECT AVG(YEAR(DATE())-YEAR(_1.родился)) FROM 1 _1  INNER JOIN 111 _111  ON  _1.факультет = _111.индекс WHERE _1.курс In (2,3) AND _111.факультет = "ФАМ") ;

Рис.6. Результат работы первого запроса

Данному запросу удовлетворяют только один студент.

Запрос 2

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

Код 2 запроса

SELECT _1.фамилия, _1.курс, _111.факультет,YEAR(DATE())-YEAR(_1.родился);

 FROM  1 _1 INNER JOIN 11 _11 ;

   ON  _1.индекс = _11.индекс;

     INNER JOIN 111 _111 ;

   ON  _1.факультет = _111.индекс;

 WHERE   ( _1.курс NOT IN (1,2);

    OR (_111.факультет NOT IN ("ФЭН","РТФ")));

AND  YEAR(DATE())-YEAR(_1.родился) > (SELECT AVG(YEAR(DATE())-YEAR(_1.родился)) FROM 1 _1 INNER JOIN 111 _111  ON  _1.факультет = _111.индекс WHERE _1.курс In (2,3) AND _111.факультет = "ФАМ")

Рис.7. Результат работы второго запроса

Данному запросу удовлетворяют только семь студентов

Запрос 3

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

Код 3 запроса

SELECT _1.фамилия,_1.курс,_111.Факультет,_11.стипендия,YEAR(DATE())-YEAR(_1.родился);

 FROM  1 _1 INNER JOIN 11 _11 ;

   ON  _1.индекс = _11.индекс;

    INNER JOIN 111 _111 ;

   ON  _1.факультет = _111.индекс;

 WHERE _1.курс IN (1,3);

   AND _111.факультет IN ("АВТФ","ПМТ");

   AND _11.стипендия BETWEEN "1000" AND "2500";

   AND  YEAR(DATE())-YEAR(_1.родился) >  (SELECT AVG(YEAR(DATE())-YEAR(_1.родился)) FROM 1 _1 INNER JOIN 111 _111  ON  _1.факультет = _111.индекс  WHERE _1.курс In (2,3) AND _111.факультет = "ФАМ") ;

  

Рис.9. Результат работы третьего запроса

Данному запросу удовлетворяют только один студент.

Защита

Вывести студентов имеющие книги издательства наука или сибири посещающие занятия: лекции или практику и получающие стипендию больше чем средняя стипендия студентов 2 и 4 курса факультетов ФПМИ и АВТФ, кроме студентов 3и 5 курсов факультетов ФЛА И ФБ

Код запроса на защиту

select _1.фамилия, _1.курс, _11.факультет, _11.стипендия;

from 1 _1;

 INNER JOIN 11 _11 ;

   ON  _1.индекс = _11.индекс;

  INNER JOIN 111 _111 ;

 ON  _1.факультет = _111.индекс;

   INNER JOIN 1111 _1111 ;

   ON  _1.книги = _1111.индекс;

where _1111.изд in(«наука», «сибирь»);

and _1.занятия in(«Лекция», «Практика»);

and(_1.курс not in(3,5);

    or (_111.факультет not in(«ФЛА», «ФБ»)));

and _11.стипендия >(select avg(_11.стипедия) from 1 _1 INNER JOIN 11 _11    ON  _1.индекс = _11.индекс INNER JOIN 111 _111  ON  _1.факультет = _111.индекс where _111.факультет =(«ФПМИ», «АВТФ»)and _1.курс in(2,4))

////////////

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