Создание подзапросов. Осуществление выборки из нескольких таблиц

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

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

Кафедра вычислительной техники

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

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

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

Факультет: АВТ

Группа: АМ-509

Вариант: 2

Выполнил студент:

Дмитриев Д. А.

Преподаватель:

Трошина Г.В.

Новосибирск 2008

1. Цель работы

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

2. Задание

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

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

3. Ход работы

            Для выполнения работы необходимо создать проект, в который мы поместим 3 таблицы. Первая таблица, будет немного модернизированная таблица, которую мы сделали в ходе выполнения пятой лабораторной работы. Мы в нее добавим всего 2 поля, это id_fakultet(для привязки таблицы «факультет») и id_prozh(для привязки таблицы «проживание»). Вторая таблица будет содержать поле «Факультет» и поле id_fakultet для правильного соединения строк этой таблицы со строками первой таблицы. Третья таблица По-смыслу будет аналогична второй, но будет содержать поля Проживание и id_prozh. На Рис.1 я продемонстрировал структуру базы данных.

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

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

Рис.2 Основная форма

            В верхней части формы расположены кнопки для демонстрации каждой таблицы и результата объединения этих таблиц. Ниже я предоставлю код каждой из них и результат выполнения:

ü  Показать Таблицу «Студенты»

SELECT t1.*;

 FROM  data!t1

Рис.3 Показать Таблицу «Студенты»

ü  Показать Таблицу «Факультет»

SELECT t2.*;

 FROM  data!t2

Рис.4 Показать Таблицу «Факультет»

ü  Показать Таблицу «Проживание»

SELECT t3.*;

 FROM  data!t3

Рис.5 Показать Таблицу «Проживание»

ü  Показать объединение этих таблиц

SELECT DISTINCT  T1_a.f, T1_a.i, T1_a.o, T2.fakultet,T3.prozh;

 FROM  data!t2 INNER JOIN data!t1 T1_a;

    RIGHT OUTER JOIN data!t3 ;

   ON  T1_a.id_prozh = T3.id_prozh ;

   ON  T2.id_fakultet = T1_a.id_fakultet

Рис.6 Показать объединение этих таблиц

Далее по заданию мне надо было написать 3 разных запроса к БД содержащих вложенные запросы(подзапросы). Для выполнения каждого из них я разместил на форме соответствующую кнопку. Ниже я продемонстрирую текст запроса и результат его выполнения.

1) Вывод фамилий студентов получивших 5 и 4 за экзамены и обучающихся на 1 и 4 курсах факультетов ФЛА и ФБ, и в возрасте больше, чем средний возраст студентов факультета ФЭН.

SELECT t1.f AS "Фамилия", (YEAR(DATE()) - YEAR(t1.daterozh)) as "Возраст" ;

 FROM  data!t1 INNER JOIN data!t2 ;

   ON  t1 .id_fakultet = t2.id_fakultet;

 WHERE (YEAR(DATE()) - YEAR(t1.daterozh)) >(SELECT (AVG(YEAR(DATE()) - YEAR(t1.daterozh))) ;

 FROM  data!t1 INNER JOIN data!t2 ;

   ON  t1.id_fakultet = t2.id_fakultet;

WHERE t2.fakultet = "FEN");

  AND((t2.fakultet = "FLA";

                        OR t2.fakultet = "FB");

            AND     (t1.kurs = 1;

                        OR t1.kurs = 4);          

            AND     (t1.exam_1 = 5;

                        OR t1.exam_1= 4);                             

            AND     (t1.exam_2 = 5;

                        OR t1.exam_2= 4);      

            AND     (t1.exam_3 = 5;

                        OR t1.exam_1= 4);

            AND     (t1.exam_4 = 5;

                        OR t1.exam_1= 4))      

Рис.7 Результат выполнения запроса (1)

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

SELECT t1.f AS "Фамилия", (YEAR(DATE()) - YEAR(t1.daterozh)) as "Возраст" ;

 FROM  data!t1 INNER JOIN data!t2 ;

   ON  t1 .id_fakultet = t2.id_fakultet;

 WHERE (YEAR(DATE()) - YEAR(t1.daterozh)) >(SELECT (AVG(YEAR(DATE()) - YEAR(t1.daterozh))) ;

 FROM  data!t1 INNER JOIN data!t2 ;

   ON  t1.id_fakultet = t2.id_fakultet;

WHERE t2.fakultet = "FEN");

  AND(NOT      (t2.fakultet = "RTF";

            AND     (t1.kurs = 2;

                        OR t1.kurs = 5)))         

Рис.8 Результат выполнения запроса (2)

3) выборка фамилий всех студентов 1 курса факультета АВТ, приехавших из Куйбышева и Бердска, и в возрасте больше, чем средний возраст студентов факультета ФЭН

SELECT DISTINCT  T1_a.f, T1_a.i, T1_a.o, T2.fakultet,T3.prozh,(YEAR(DATE()) - YEAR(t1.daterozh)) as "Возраст" ;

 FROM  data!t2 INNER JOIN data!t1 T1_a;

    RIGHT OUTER JOIN data!t3 ;

   ON  T1_a.id_prozh = T3.id_prozh ;

   ON  T2.id_fakultet = T1_a.id_fakultet;

 WHERE (YEAR(DATE()) - YEAR(T1_a.daterozh)) >(SELECT (AVG(YEAR(DATE()) - YEAR(t1.daterozh))) ;

 FROM  data!t1 INNER JOIN data!t2 ;

   ON  t1.id_fakultet = t2.id_fakultet;

WHERE t2.fakultet = "FEN");

 AND(t2.fakultet = "AVTF";

            AND     T1_a.kurs = 1;

            AND     (t3.prozh = "Бердск";

                        OR t3.prozh= "Куйбышев            "))  

Рис.9 Результат выполнения запроса (3)

4. Выводы

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