Создание запросов и подзапросов (база данных "Оcenki")

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

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

Лабораторные работы №5 и №6

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

Группа:       АМ-211

Студент:     Слуцкий А.А.                 Преподаватель:    Трошина Г.В.

Вариант:     2

Новосибирск

2005

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

Задание: Создать и заполнить несколько баз данных в разных областях, взяв за основу поля базы данных из лр № 1, установить связи между ними. Выполнить выбор записи по диапазону значений для заданной БД. Провести вычисления итоговых значений. Осуществить выборку из нескольких таблиц. Использовать результаты запроса для создания диаграмм. Произвести упорядочение данных в запросе по нескольким полям. Организовать вывод результатов запроса в таблицу, на экран, в отчёт. Продемонстрировать приобретённые навыки построения многотабличных запросов. Одна таблица (минимально) должна содержать поля: факультет, идентификатор факультета в обязательном порядке, другая (минимально) курс, идентификатор факультета в обязательном порядке.

Вариант:

Запрос1: Вывод минимального, максимального и среднего значения возроста для студентов факультета ФЭН.

Запрос2: Вывод фамилий студентов, получивших 5 и 4 за экзамены

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

Запрос4: Вывод фамилий всех студентов 1 курса факультета АВТ

 Результаты работы:

Для выполнения лабораторной работы и демонстрации запросов были созданы следующие таблицы:

            ocenki:

                                    фамилия

                                    имя

                                    отчество

                                    дата_рождения                                

identif

                                    курс

                                    осенка_1_экзамен

осенка_2_экзамен

осенка_3_экзамен

осенка_4_экзамен

семестр

                                    город

                                    физика

                                    алгебра

                                    стипендия

                                    аудитория

                                    учебник

                                    издательство

                                    год_выпуска

                                    общежитие

            Ocenki_d:

                                    identif

                                    факультет

Реализация запросов:

Лаб №5

Запрос1

SELECT MIN(YEAR(DATE())-YEAR(Ocenki.дата_рождения)) as Мин_возраст , MAX(YEAR(DATE())-YEAR(Ocenki.дата_рождения)) as Макс_возраст,;

  AVG(YEAR(DATE())-YEAR(Ocenki.дата_рождения)) as Ср_возраст;

FROM  Ocenki_d ;

INNER JOIN Ocenki ;

ON Ocenki_d.identif = Ocenki.identif;

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

Запрос2

SELECT Ocenki.фамилия, Ocenki.осенка_1_экзамен, Ocenki.оценка_2_экзамен, Ocenki.оценка_3_экзамен, Ocenki.оценка_4_экзамен;

FROM  Ocenki ;

WHERE Ocenki.осенка_1_экзамен >= 4;

AND Ocenki.оценка_2_экзамен >= 4;

AND Ocenki.оценка_3_экзамен >= 4;

AND Ocenki.оценка_4_экзамен >= 4;

Запрос3

SELECT Ocenki.фамилия, Ocenki.курс, Ocenki_d.факультет;

FROM  Ocenki_d ;

INNER JOIN Ocenki ;

ON Ocenki_d.identif = Ocenki.identif;

WHERE !(Ocenki_d.факультет = "РТФ" AND         Ocenki.курс = 2)

Запрос4

SELECT Ocenki.фамилия, Ocenki.курс, Ocenki_d.факультет;

FROM  Ocenki_d ;

INNER JOIN Ocenki ;

ON Ocenki_d.identif = Ocenki.identif;

WHERE Ocenki_d.факультет = "АВТ";

AND      Ocenki.курс = 1

Защита:

Вывести студентов, проживающих в Омске и Томске, имеющих стипендию от 100 до 500, сдавших физику или алгебру на 4 и 5, родившихся с 82 по 86 годы, кроме студентов 1 и 4 курса факультетов АВТ и ФЛА

SELECT Ocenki.фамилия, Ocenki.дата_рождения, Ocenki.курс, Ocenki.город, Ocenki.физика,;

  Ocenki.алгебра, Ocenki.стипендия, Ocenki_d.факультет;

FROM  Ocenki_d ;

INNER JOIN Ocenki ;

ON Ocenki_d.identif = Ocenki.identif;

WHERE (Ocenki.город = "Омск" or Ocenki.город = "Томск");

and Ocenki.стипендия >= 100;

and Ocenki.стипендия <= 500;

and (Ocenki.физика > 3 and Ocenki.алгебра > 3);

and YEAR(Ocenki.дата_рождения) >= 1982;

and YEAR(Ocenki.дата_рождения) <= 1986;

and !((Ocenki.курс = 1 or Ocenki.курс = 4) and (Ocenki_d.факультет = "АВТ" or Ocenki_d.факультет = "ФЛА"))

Лаб №6

Запрос2

SELECT AVG(YEAR(DATE())-YEAR(Ocenki.дата_рождения)) as Ср_возраст;

FROM  Ocenki_d ;

INNER JOIN Ocenki ;

ON Ocenki_d.identif = Ocenki.identif;

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

if(ThisForm.Optiongroup1.Option1.value=1)

SELECT Ocenki.фамилия, Ocenki.осенка_1_экзамен, Ocenki.оценка_2_экзамен, Ocenki.оценка_3_экзамен, Ocenki.оценка_4_экзамен, ;

(YEAR(DATE())-YEAR(Ocenki.дата_рождения)) as Возраст_студента ;

FROM  Ocenki ;

WHERE Ocenki.осенка_1_экзамен >= 4;

AND Ocenki.оценка_2_экзамен >= 4;

AND Ocenki.оценка_3_экзамен >= 4;

AND Ocenki.оценка_4_экзамен >= 4;

AND (YEAR(DATE())-YEAR(Ocenki.дата_рождения))<(SELECT AVG(YEAR(DATE())-YEAR(Ocenki.дата_рождения)) as Ср_возраст;

FROM  Ocenki_d ;

INNER JOIN Ocenki ;

ON Ocenki_d.identif = Ocenki.identif;

WHERE Ocenki_d.факультет = "ФЭН")

else