6828

Вложенные запросы (подзапросы) в SQL Oracle

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

Информатика, кибернетика и программирование

Вложенные запросы (подзапросы) в SQL Oracle Цели лабораторной работы Изучить возможности SQL Oracle по формулировке и обработке подзапросов. Приобрести практический опыт по формулировке и обработке подзапросов с использованием SQL...

Русский

2013-01-08

163 KB

84 чел.

Вложенные запросы (подзапросы) в SQL Oracle

  1.  Цели лабораторной работы
  •  Изучить возможности SQL Oracle по формулировке и обработке подзапросов.
  •  Приобрести практический опыт по формулировке и обработке подзапросов с использованием SQL*Plus.
  1.  Теоретические основы

Запрос – это операция, которая позволяет отыскивать данные из одной или несколько таблиц. При наличии вложенных запросов запрос верхнего уровня называется предложением SELECT, а запрос, вложенный в предложение SELECT называется подзапросом. Таким образом, подзапрос (вложенный запрос) – это запрос, результат которого передается в качестве аргумента в другой запрос. Подзапросы позволяют связывать в единое целое несколько запросов.

Подзапросы используются для:

  •  определения множества строк, который должны быть вставлены в целевую таблицу в предложениях INSERT или CREATE TABLE;
  •  определения одного или более значений, присваиваемых существующим строка в предложении UPDATE;
  •  получения значений для фраз WHERE, HAVING или START WITH в предложениях SELECT, UPDATE, и DELETE;
  •  определения значений указанного столбца в списке INSERT ... VALUES;
  •  определения таблицы, которая используются соответствующим запросом.

Это производится путем размещения подзапроса во фразе FROM соответствующего запроса как если бы это было именем таблицы. Вы можете также использовать таким образом подзапросы вместо таблиц в предложениях INSERT, UDPATE и DELETE.

Используемые таким образом подзапросы могут использовать переменные связывания (correlation variables), однако только такие, которые определены только в самом подзапросе, ссылки на внешние переменные не допустимы. Внешние ссылки (подзапросы с левой корреляцией - left-correlated subqueries) допустимы только во фразе FROM предложения SELECT .

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

Подзапрос может содержать другие подзапросы. Oracle не ограничивает глубину вложенности подзапросов.

Если таблица в подзапросе имеет такое же имя, что и таблица внешнего запроса, то для ссылки на столбцы внешнего запроса их необходимо уточнять именем таблицы или алиасом таблицы. Чтобы ваши запросы было легче воспринимать, всегда квалифицируйте столбцы в подзапросе именем или алиасом таблицы.

Oracle выполняет корреляционные (связанный) подзапрос, когда подзапрос ссылается на столбец таблицы внешнего запроса. Связанный подзапрос вычисляется для каждой строки, обрабатываемой внешним предложением. Внешним предложением может быть SELECT, UPDATE или DELETE.

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

Далее мы обсудим использование подзапросов в предложении SELECT.

  1.  Подзапрос во фразе WHERE
    1.  Подзапрос в простом условии сравнения

Синтаксис:

Описание:

При использовании простых условий сравнения с подзапросом во фразе WHERE применяются следующие правила:

  •  Подзапрос должен возвращать единственную строку.
  •  Если левая часть равна expr, то подзапрос должен возвращать единственную строку с единственным значением с типом, совместимым с типом expr.
  •  Если левая часть является списком выражений (expr_list), то подзапрос должен возвращать единственную строку со списком значений, который соответствует по количеству и типу значениям из expr_list. В этом случае оператор сравнения дает TRUE, если каждое значение в expr_list равно (в случае =) или не равно (в случае !=, ^=, <>) каждому значение, возвращаемому подзапросом.

Примеры:

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

SELECT Name

FROM  DEPARTMENT

WHERE  Building = (SELECT Building

        FROM  FACULTY

        WHERE UPPER(Name) = 'INFORMATICS');

2. Выбрать факультеты, чьи фонды меньше фонда кафедры CAD:

SELECT Name

FROM   FACULTY

WHERE  Fund < (SELECT Fund

      FROM  DEPARTMENT

      WHERE  UPPER(Name) = 'CAD');

3. Выбрать преподавателей, у которых salary + commission превышает более чем на 100 половину salary + commission преподавателя Bill:

SELECT Name

FROM  TEACHER

WHERE  Salary + Commission + 100 > (SELECT (Salary + Commission) / 2

              FROM  TEACHER

              WHERE UPPER(Name) = 'BILL');

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

SELECT Name

FROM  TEACHER

WHERE  (DepNo, Post) = (SELECT DepNo, Post

              FROM  TEACHER

              WHERE UPPER(Name) = 'BILL');

  1.  Подзапрос в условии сравнения групп

Синтаксис:

Описание:

При использовании условий сравнения групп с подзапросом во фразе WHERE применяются следующие правила:

  •  Подзапрос может возвращать ноль или более строк.
  •  Если левая часть равна expr, то подзапрос должен возвращать строки с единственным значением, которые совместимы по типу с expr.
  •  Если левая часть равна expr_list, то подзапрос должен возвращать строки со списком значений, который соответствует по количеству и типу с expr_list.

ANY и SOME эквивалентны и сравнивают значение слева с каждым значением списка справа, возвращаемого подзапросом. Подзапрос может вернуть ноль или более строк. Условие равно TRUE, если по крайней мере одна строка подзапроса удовлетворяет условию (соответствует оператору сравнения) по отношению к значению (списку значений) определенному левым операндом, в противном получаем FALSE. Если подзапрос не возвращает строк, то получаем FALSE.

ALL сравнивают значение слева с каждым значением списка справа, возвращаемого подзапросом. Дает TRUE, если ВСЕ строки, возвращаемые подзапросом, удовлетворяют условию (соответствуют оператору сравнения) по отношению к значению (списку значений) определенному левым операндом, в противном получаем FALSE. Если подзапрос не возвращает строк, то получаем TRUE

Примеры:

1. Выдать кафедры, фонд которых больше фонда по крайней мере одного из факультетов:

SELECT Name

FROM  DEPARTMENT

WHERE  Fund  > ANY (SELECT Fund FROM FACULTY);

ANY, ALL и агрегатные функции. Обратите внимание, что <ANY эквивалентно утверждению: “левое значение меньше, чем максимальное значение из множества, задаваемого правым операндом”, а оператор >ANY эквивалентен следующему утверждению “левое значение больше, чем минимальное значение из множества, задаваемого правым операндом”. Поэтому операторы ANY могут быть выражены через функции MAX и MIN в подзапросе. В свою очередь,  <ALL (>ALL) эквивалентно утверждению: «левое значение меньше (больше), чем минимальное (максимальное) значение из множества, задаваемого правым операндом ».

2. Выдать кафедры, фонд которых больше фонда по крайней мере одного из факультетов:

SELECT Name

FROM  DEPARTMENT

WHERE  Fund  > ANY (SELECT Fund FROM FACULTY);

SELECT Name

FROM  DEPARTMENT

WHERE  Fund > (SELECT MIN(Fund) FROM FACULTY);

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

SELECT Num

FROM  SGROUP

WHERE  Rating >ALL (SELECT Rating

        FROM  SGROUP, DEPARTMENT

        WHERE SGROUP.DepNo = DEPARTMENT.DepNo AND

          UPPER(DEPARTMENT.Name) = 'DBMS' AND SGROUP.Course = 5);

  1.  Подзапрос в условии проверки вхождения элемента во множество

Синтаксис:

Описание:

Это условие в таком синтаксисе проверят вхождение элемента (списка элементов) во множество (множество списков), создаваемое подзапросом.

Пример:

1. Выбрать преподавателей, которые имеют лекции по крайней мере одному такому предмету, по которым читает лекции преподаватель Bill:

SELECT Name

FROM   TEACHER T, LECTURE L

WHERE  T.TchNo = L.TchNo AND

  SbjNo IN (SELECT SbjNo

        FROM  TEACHER TCH, LECTURE LEC

        WHERE  TCH.TchNo = LEC.TchNo AND UPPER(TCH.Name) = 'BILL');

  1.  Подзапрос в условии EXISTS

Синтаксис:

Описание:

Дает TRUE, если подзапрос возвращает по крайней мере одну строку.

Так как EXISTS обычно используется в связанных подзапросах, мы его обсудим подробнее позже.

  1.  Связанные подзапросы 

Для того, чтобы связать подзапрос с внешним запросом (предложением), необходимо в подзапросе была ссылка на столбец внешнего запроса. Подзапрос вычисляется для каждой строки, обрабатываемой внешним запросом (предложением). В качестве внешнего предложения могут выступать SELECT, UPDATE или DELETE.

Следующие примеры дают общий синтаксис использования связанных подзапросов:

SELECT select_list

FROM table1 t_alias1

WHERE expr operator

(SELECT column_list

FROM table2 t_alias2

WHERE t_alias1.column operator t_alias2.column);

UPDATE table1 t_alias1

SET column =

(SELECT expr

FROM table2 t_alias2

WHERE t_alias1.column = t_alias2.column);

DELETE FROM table1 t_alias1

WHERE column operator

(SELECT expr

FROM table2 t_alias2

WHERE t_alias1.column = t_alias2.column);

Далее мы обсудим использование связанных подзапросов во фразе WHERE предложения SELECT.

  1.  Связанные подзапросы во фразе WHERE

Примеры:

1. Выдать преподавателей, которые имеют по крайней мере одну лекцию:

SELECT Name

FROM  TEACHER

WHERE  EXISTS (SELECT *

       FROM  LECTURE

       WHERE LECTURE.TchNo = TEACHER.TchNo);

Здесь в условии LECTURE.TchNo = TEACHER.TchNo подзапроса мы ссылаемся на внешний запрос. Поэтому подзапрос является связанным.

2. Выдать преподавателей, которые не имеют ни одной лекции:

SELECT Name

FROM  TEACHER

WHERE  NOT EXISTS (SELECT *

         FROM  LECTURE

         WHERE LECTURE.TchNo = TEACHER.TchNo);

  1.  Простые и связанные подзапросы во фразе HAVING

Вы можете использовать простые и связанные подзапросы во фразе HAVING.

Если вы используете связанный подзапрос в фразе HAVING, то в подзапросе можно ссылаться на те столбцы внешнего запроса, которые могут использоваться в фразе HAVING (обычно это столбцы, по которым производится группирование).

Примеры:

1. Перечислить факультеты, у которых сумма фондов финансирования всех их кафедр превышает более чем на 20000 фонд финансирования той кафедры факультета, которая имеет максимальный фонд.

SELECT F1.Name

FROM  FACULTY F1, DEPARTMENT D1

WHERE  F1.FacNo = D1.FacNo

GROUP BY F1.Name

HAVING SUM(D1.Fund) > (SELECT 200000 + MAX(D2.Fund)

          FROM  FACULTY F2, DEPARTMENT D2

          WHERE  F2.FacNo = D2.FacNo AND F1.Name = F2.Name);

  1.  Простые подзапросы во фразе FROM

Фраза FROM может содержать не только список имен таблиц, но и подзапросы. Для ссылки на такие таблицы-подзапросы следует приписать подзапросу алиас.

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

Пример:

Выдать средний фонд финансирования факультетов и среднюю зарплату преподавателей:

SELECT Fac.AvgFund, Tch.AvgSalary

FROM  (SELECT AVG(Fund) AS AvgFund FROM FACULTY) Fac,

  (SELECT AVG(Salary) AS AvgSalary FROM TEACHER) Tch;

  1.  Подзапросы во фразе SELECT

Во фравзе SELECT можно использовать простые (независимые, несвязанные) и связанные (коррелированные) запросы. В обоих случаях подзапрос должен возвращать одно значение.

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

Пример. Для каждого факультета вывести его название, фонд финансирование, а также максимальный и минимальный фонды финансирования среди всех кафедрВУЗа

SELECT Name AS "Факультет",

      Fund AS "Фонд факультета",

     (SELECT MAX(Fund) FROM DEPARTMENT) AS "МАКС фонд кафедр",

     (SELECT MIN(Fund) FROM DEPARTMENT) AS "МИН фонд кафедр"

FROM FACULTY

Подзапрос является связанным (коррелированным), если в нем используются атрибуты таблиц, определенных во внешней запросе. В этом случае подзапрос вычисляется для каждой строки, формируемой для фразы SELECT.

Пример. По каждому факультету, расположенному в корпусе 6, вывести:

- название факультета

- количество групп этого факультета с рейтингом, более 20

- количество преподавателей-профессоров

SELECT Name AS "Факультет",

     (SELECT COUNT (DISTINCT GrpPK)

      FROM DEPARTMENT d, SGROUP g

      WHERE f.FacPK=d.FacFK AND d.DepPK=g.DepFK AND g.Rating > 20) AS "К-во групп",

     (SELECT COUNT (DISTINCT TchPK)

      FROM DEPARTMENT d, TEACHER t

      WHERE f.FacPK=d.FacFK AND d.DepPK=t.DepFK AND

            UPPER(t.Post)= 'профессор')                         AS "К-во профессоров"

FROM FACULTY f

WHERE Building= '6';

  1.  
    Варианты заданий

Далее приводится 15 вариантов заданий. Каждый вариант состоит из 7 запросов, которые относятся к следующим категориям (в порядке их следования):

  1.  Некоррелируемые подзапросы
  2.  Коррелируемые (зависимые, связанные) подзапросы
  3.  Коррелируемые подзапросы и предикат EXISTS
  4.  Коррелируемые подзапросы и предикат ANY, SOME, ALL
  5.  Подзапросы во фразе HAVING
  6.  Подзапросы во фразе FROM
  7.  Подзапросы во фразе  SELECT

ВНИМАНИЕ. В предлагаемых запросах используются константы (имена преподавателей, названия кафедр и факультетов, названия дисциплин), которые могут отсутствовать в вашей базе данных. ЗАМЕНЯЙТЕ ИХ НА ТЕ, КОТОРЫЕ ДЕЙСТВИТЕЛЬНО ИМЕЮТСЯ В ВАШЕЙ БАЗЕ ДАННЫХ!

  1.  Вариант 1

1) По каждой кафедере, расположенной в том же корпусе, что и факультет, деканом которого является Иванов, вывести следующую информацию в столбцах с соответствующими именами:

- название кафедры    Кафедра

- имя заведующего   Заведующий.

2) Вывести названия  факультетов, которые имеют кафедры в корпусе 6

3) Вывести названия факультетов из корпуса 6 и имена их деканов, на которых имеется хотя бы одна кафедра

4) Вывести названия факультетов, фонды финансирования которых. увеличенные на 200000,  больше фондов финансирования любой из их кафедр. Привести два варианта – с оператором ALL и функцией MAX.

5) Вывести такие пары значений: «название дисциплины-имя преподавателя», что

- данный преподаватель преподает эту дисциплину;

- он преподает ее более, чем 2-м группам

- он имеет больше занятий по этой дисциплине, чем преподаватель Иванов по дисциплине СУБД

6) Вывести среднее количество дисциплин на один факультет

7) По каждому факультету вывести:

- название факультета

- количество кафедр

- суммарный фонд кафедр

- количество  студентов

  1.  Вариант 2

1) По каждому преподавателю факультета компьютерных наук, который имеет зарлату (salary+commission) больше, чем  зарплата преподавателя Иванова с кафедры ИПО, вывести следующую информацию в столбцах с соответствующими именами:

- имя этого преподавателя     Преподаватель

- должность преподавателя    Должность

- имя декана факультета компьютерных наук  Декан факультета

2) Вывести названия факультетов, в которых имеется менее 20 профессоров

3) Вывести названия факультетов и имена их деканов, на которых имеется хотя бы один преподаватель-профессор

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

5)  Вывести такие тройки значений «имя преподавателя-номер группы-курс группы», что

- этот преподаватель преподает этой группе данного курса

-  он преподает более одной дисциплины в этой группе этого курса

- он имеет в этой группе этого курса больше занятий, чем количество занятий преподавателя Иванова в этой же группе этого курса

6) Вывести среднее количество дисциплин на одну кафедру

7) По каждому факультету вывести:

- название факультета

- количество кафедр на факультете

- количество студентов 3-го курса на факультете

  1.  Вариант 3

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

- имя преподавателя    Преподаватель

- дата поступления на работу   Дата поступления

2) Вывести названия факультетов, в которых  имеется менее 5 групп третьего курса

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

4)  Вывести названия факультетов, которые расположены в одном из корпусов, в котором расположены ее кафедры

5) Вывести такие тройки значений «имя преподвателя-название дисциплины-номер группы», что

- данный преподаватель преподает данную дисциплину данной группе и

- он проводит занятия в этой группе по этой дисциплине в более, чем 1-й аудитории и

- у него в этой группе по этой дисциплине больше занятий, чем у любого другого преподавателя в этой группе по этой дисциплине

6)  Вывести среднее количество студентов на одного преподавателя

7) По каждому факультету вывести

- название факультета

- количество групп на 3-м курсе

- количество преподавателей-доцентов

  1.  Вариант 4

1)  По каждому преподавателю кафедры, заведующим которой является Иванов, который (преподаватель) поступил на работу в диапазоне от минимальной до максимальной дат поступления на работу преподавателей факультета компьютерных наук, вывести следующую информацию в столбцах с соответствующими именами

- имя преподавателя    Преподаватель

- дата поступления на работу   Дата поступления

2) Вывести названия и корпуса факультетов, фонд финансирования которых меньше более, чем на 1000, суммарного фонда финансирования всех кафедр факультета

3) Вывести названия факультетов, которые расположены не в корпусе 5 и не имеют преподавателей, поступивших на работу в диапазоне 01.01.2000-01.06.2000

4) Вывести названия факультетов и имена их деканов, которые (факультеты) расположены в одном из корпусов, в котором расположены аудитории, в которых проводятся занятия по дисциплине СУБД

5) Вывести такие пары значений «номер группы-название дисциплины», что:

- этой группе преподается эта дисциплина и

- этой группе эту дисциплину преподает более, чем  1 преподаватель

- этой группе эта дисциплина преподается в более, чем одной аудитории

- количество лекций, читаемых этой группе по этой дисциплине, больше, чем среднее количество занятий, проводимых по всем дисциплинам

6) Вывести среднее количество студентов на один факультет

7) По каждому факультету вывести

- название факультета

- количество дисциплин, изучаемых студентами факультета

- количество дисциплин, преподаваемых преподавателями факультета

  1.  Вариант 5

1)  По каждому преподавателю факультета компьютерных наук, который имеет зарплату (salary+commission)  в диапазоне между минимальной и максимальной зарплатой преподавателей кафедры, заведующим которой является Иванов, вывести следующую информацию в столбцах с соответствующими именами:

- имя преподавателя    Преподаватель

- его зарплата (salary+commission)  Зарплата

- должность     Должность

2)  Вывести названия факультетов и имена их деканов, в которых меньше 100 студентов 3-го курса

3) Вывести названия факультетов и имена их деканов, у которых нет кафедр с фондом финансирования, превышающим фонд финансирования факультета

4) Вывести  названия кафедр факультета, деканом которого является Иванов, которые (кафедры), расположены  в одном из корпусов, в котором  расположены кафедры факультета компьютерных наук.

5) Вывести имена преподавателей кафедры ИПО, которые имеют больше лекций, чем любой из преподавателей факультета компьютерных наук

6)  Вывести среднее количество студентов в группе и средний фонд финансирования кафедр

7) По каждой кафедре факультета компьютерных наук вывести:

- название кафедры

- количество групп, которые изучают дисциплину СУБД

- количество преподавателей на кафедре, которые преподают дисциплину СУБД

  1.  Вариант 6

1)  По каждой группе кафедры, заведующим которой является Иванов,  и рейтинг которой совпадает с рейтингом хотя бы одной из групп факультета компьютерных наук, вывести ее номер и курс

2) Вывести названия кафедр факультета, деканом которого является Иванов, у которых (кафедр)  суммарная зарплата (salary+commission) преподавателей больше фонда финансирования кафедры

3) Вывести названия кафедр факультета, деканом которого является Иванов, на которых (кафедрах) нет ни одного преподавателя-профессора

4) Вывести названия кафедр, у которых фонд финансирования больше, по крайней мере на 2000, фонда по крайней мере одного из факультетов (привести два варианта: с оператором <ANY  и функцией MAX в подзапросе)

5) Вывести имена преподавателей, поступивших на работу в диапазоне дат 01.01.1990-31.12.2008, которые преподают больше дисциплин, чем хотя бы один преподаватель кафедры ИПО

6) Вывести среднее количество студентов в группах кафедры ИПО и средний фонд финансирования кафедр факультета компьютерных наук

7) По каждой кафедре факультета, деканом которого является Иванов, вывести:

- название кафедры

- суммарный фонд зарплаты (salary+commission) всех преподавателей профессоров и доцентов

- общее количество студентов на кафедре  в группах с рейтингом более 10

  1.  Вариант 7

1)  По каждой группе, которой преподается дисциплина «СУБД» и рейтинг которой совпадает с одним из рейтингов групп кафедр ИПО или КИТ, вывести ее номер и курс

2) Вывести имена преподавателей-доцентов  кафедры, заведующим которой является Иванов, у которых (преподавателей)  меньше 4-х пар на первой неделе

3) Вывести названия кафедр факультета, деканом которого является Иванов, на которых (кафедрах) есть хотя бы одни преподаватель-доцент с зарплатой (salary+commission) в диапазоне 1000-12000

4) Вывести названия кафедр факультета компьютерных наук, которые расположены в одном из корпусов, в которых проводят занятия преподаватели кафедры ИПО

5) Вывести имена преподавателей с диапазоном зарплат (salary+commission) 1000-3000, которые проводят занятия типа «лекция» в стольких группах, в скольких проводит занятия типа «лабораторная» хотя бы один преподаватель кафедры ИПО

6) Вывести суммарный фонд финансирования всех факультетов, суммарный фонд финансирования всех кафедр и суммарную зарплату (salary+commission) всех преподавателей.

7) По каждой кафедре, преподаватели которой преподают дисциплину СУБД, вывести:

- название кафедры

- количество лекций, читаемых преподавателями по дисциплине СУБД

- количество групп этой кафедры, которым читаются лекции по дисциплине СУБД

при условии, что на этой кафедре дисциплину СУБД преподают не более 2-х преподавателей

  1.  Вариант 8

1) По каждой группе факультета, деканом которого является Иванов, и рейтинг которой находится в диапазоне между минимальным и максимальным рейтингом групп кафедры ИПО, вывести ее номер, курс и кафедру

2)  Вывести номера и курс групп факультета, деканом которого является Иванов, у которых (групп)  менее 3-х пар на первой по дисциплине СУБД.

3) Вывести названия кафедр факультета, деканом которого является Иванов, на которых (кафедрах) нет групп третьего курса, у которых нет куратора

4)  Вывести названия кафедр, фонд финансирования которых больше суммарной зарплаты (salary+ commission)  всех преподавателей по крайней мере одной кафедры факультета компьютерных наук

5) Вывести названия кафедр факультета компьютерных наук, у которых суммарная зарплата (salary+commission)  их преподавателей-профессоров превышает, по крайней мере, на 200 суммарную зарплату всех преподавателей-доцентов кафедры ИПО

6) Вывести количество студентов, количество преподавателей и количество дисциплин

7) По каждому преподавателю факультета компьютерных наук вывести:

- его имя,

- количество дисциплин, которые он преподает

- количество занятий, которые он имеет на первой неделе,

- количество занятий, которые он имеет на второй неделе

при условии, что:

- он проводит занятия  не более, чем в 3-х группах

  1.  Вариант 9

1) Для каждой аудитории, в которой проводят занятия преподаватели факультета компьютерных наук, которые (преподаватели)  имеют зарплату (salary+commission)  в диапазоне между минимальной и максимальной зарплатой кафедры, заведующим которой является Иванов, вывести номер аудитории, ее корпус и количество в ней мест

2) Вывести корпуса, в которых располагается только одна кафедра

3) Вывести имена и должности преподавателей кафедры, заведующим которой является Иванов, которые (преподаватели) не преподают ни одной дисциплины

4) Вывести номера групп кафедры ИПО, у которых рейтинг больше, чем хотя бы одной группы третьего курса этой кафедры. Привести два варианта: с оператором >ANY и с оператором EXISTS)

5) Вывести названия кафедр факультета компьютерных наук, у которых суммарная зарплата (salary+ commission)  их преподавателей-профессоров превышает, по крайней мере, на 200 суммарную зарплату всех преподавателей-доцентов этой кафедры

6) Вывести среднее количество лекций на одного преподавателя и среднее количество преподавателей на один факультет

7)  По каждому преподавателю факультета компьютерных наук вывести:

- его имя,

- количество групп третьего курса, в которых он проводит занятия,

- количество аудиторий корпуса 6, в которых он проводит занятия,

при условии, что:

- он проводит занятия не более, чем по 2 дисциплинам и

  1.  Вариант 10

1)  По каждому преподавателю, являющемуся куратором групп факультета, деканом которого является Иванов, и имеющему дату приема на работу в диапазоне между минимальной и максимальной датой приема на работу преподавателей кафедры ИПО, вывести его имя, зарплату (salary+commission) и должность

2) Вывести дисциплины, которые читаются преподавателями менее, чем трех факультетов

3) Вывести имена и должности преподавателей кафедры, заведующим которой является Иванов, которые (преподаватели) не имеют  занятия  в понедельник первой недели

4) Вывести номера групп тех кафедр, которые расположены в одном из корпусов, в которых расположены факультеты с фондом финансирования в диапазоне 20000-300000

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

6) Вывести номер аудитории корпуса 5 с минимальным количеством мест, вместе с этим количеством, а также номер аудитории корпуса 6 с максимальным количеством мест, вместе с этим количеством

7) По каждой группе факультета, деканом которого является Иванов вывести:

- номер группы

- курс группы

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

- количество занятий в аудитории 313 корпуса  6

при условии, что в группе преподают менее 5 преподавателей

  1.  Вариант 11

1) Вывести имена преподавателей кафедры ИПО, зарплата которых более чем на 100 превышает удвоенную зарплату Иванова

2) Вывести номера аудиторий корпуса 6,  в которых преподают более 3-х преподавателей или в которых проводятся занятия для менее 4-х групп

3) Вывести имена и должности преподавателей факультета, деканом которого является Иванов, которые (преподаватели) не преподают дисциплину СУБД

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

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

6) Вывести минимальную зарплату среди преподавателей-доцентов (вместе с именем этого доцента) и максимальную зарплату среди профессоров (вместе с именем этого профессора

7)  По каждому факультету вывести:

- название факультета,

- количество преподавателей на факультете

- количество студентов на факультете

- суммарная зарплата (salary+commission) всех преподавателей факультета

  1.  Вариант 12

1)  Вывести имена подчиненных заведующего кафедрой ИПО, которые (подчиненные) имеют ставку (salary), не совпадающую ни с одной из ставок преподавателей кафедры КИТ, ИЛИ надбавку (commission), также не совпадающую ни с одной из надбавок преподавателей кафедры КИТ

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

3) Вывести номера, корпус и вместимость аудиторий, в которых нет занятий на 3-й паре во вторник второй недели.

4) Вывести имена преподавателей факультета компьютерных наук, у которых зарплата (salary+commission) превышает зарплату хотя бы одного из преподавателей кафедры ИПО

5)  Вывести названия факультетов, на которых количество кафедр  больше, чем количество кафедр на каждом из факультетов с фондом финансирования в диапазоне 50000-300000

6) Вывести минимальную дату приема на работу, вместе с именем этого преподавателя, и максимальную дату приема на работу, также вместе с именем этого преподавателя.

7)  По каждой кафедре факультета компьютерных наук вывести

- название кафедры

- количество групп 3-го курса на кафедре

- суммарная зарплата всех преподавателей-доцентов факультета

  1.  Вариант 13

1)  По каждой группе, которой читают такую же дисциплину, как и группам кафедры ИПО, вывести номер группы и название ее кафедры. Примечание: не выводить группы кафедры ИПО

2)  Вывести кафедры и их корпуса факультета компьютерных наук, которые (кафедры) располагаются в корпусе, отличающемся от корпуса факультета компьютерных наук

3) Вывести номера и вместимость аудиторий из корпуса 5 или 6, в которых нет занятий на 2-3-й паре в среду первой недели.

4)  Вывести номера аудиторий, в которых проводятся занятия по дисциплине СУБД, и которые расположены в одном из корпусов, в которых имеются занятия в группах 3-го курса кафедры ИПО

5) Вывести названия факультетов, на которых суммарное количество студентов в группах с рейтингом в диапазоне 10-50 больше, чем во всех группах 5-го курса факультета, на котором заведующим является Иванов

6) Вывести номер группы с минимальным рейтингом, вместе с этим рейтингом, и номер группы с максимальным рейтингом, вместе с этим рейтингом)

7) По каждой группе, куратором которой является преподаватель кафедры ИПО, вывести:

- номер группы

- количество преподавателей-профессоров, преподающих в этой группе

- количество аудиторий 6-го корпуса, в которых проводятся занятия в этой группе

при условии, что в этой группе преподается менее 5-ти дисциплин

  1.  Вариант 14

1) По каждому преподавателю, который читает хотя бы одну из дисциплин, которые читаются группам 1-3 курсов кафедры ИПО,  вывести его имя, должность и читаемую им дисциплину

2) Вывести фамилии преподавателей, имеющих на первой неделе больше пар, чем на второй.

3)  Вывести номера аудиторий корпуса 6, в которых нет занятий на первой неделе в группах 3-го курса

4) Вывести номера аудиторий корпуса 6 с вместимостью, превышающей количество студентов во всех группах кафедры ИПО

5) Вывести названия факультетов, на которых  значение зарплаты (salary+ commission) всех преподавателей-профессоров превышает более, чем на 10000 суммарное значение зарплаты всех преподавателей-доцентов факультета компьютерных наук.

6) Вывести номер и курс группы с минимальным количеством студентов, вместе с этим количеством, и номер группы и курс с максимальным количеством студентов, вместе с этим количеством)

7) По каждой дисциплине, преподаваемой студентам кафедры ИПО, вывести

- название дисциплины

- количество преподавателей профессоров, доцентов и ассистентов, преподающих эту дисциплину

- количество групп с рейтингом в диапазоне 10-80, которым эта дисциплина читается

- количество аудиторий корпуса 6, в которых эта дисциплина преподается

  1.  Вариант 15

1) По каждой дисциплине, читаемой на 1-3 курсах факультета компьютерных наук, которая (дисциплина) читается  преподавателем с зарплатой (salary+commission), НЕ совпадающей ни с одной из зарплат преподавателей кафедры ИПО, вывести  название дисциплины, номер и курс группы, которой эта дисциплина читается

2)  Вывести фамилии и должности преподавателей, имеющих больше пар на первой неделе, чем преподаватель Иванов

3)  Вывести такие названия дисциплин и имена преподавателей, что в принципе преподаватель преподает эту дисциплину, однако он не преподает ее студентам 1-го и 2-го курса

4) Вывести названия дисциплин, которые преподаются в аудиториях, вместимость которых меньше количеству студентов, по крайней мере, одной из групп, в которых эта дисциплина в этой аудитории преподается.

5) Вывести названия факультетов, на которых  значение зарплаты (salary+ commission) всех преподавателей-профессоров превышает более, чем на 10000 суммарное значение зарплаты всех преподавателей-доцентов этого факультета.

6) Вывести средний фонд финансирования всех кафедр факультета компьютерных наук и среднюю зарплату (salary+commission) всех преподавателей кафедры ИПО

7) По каждой аудитории с количеством мест более 15 вывести:

- номер аудитории

- корпус аудитории

- количество преподавателей, преподающих в этой аудитории которые поступили на работу в диапазоне 01.01.2000-31.12.2008

- количество групп 3-го курса, которые имеют занятия в этой аудитории

- количество дисциплин, которые преподаются в этой аудитории

  1.  Вариант 16

1)  По каждому преподавателю кафедры, заведующим которой является Иванов, который (преподаватель) поступил на работу в диапазоне от минимальной до максимальной дат поступления на работу преподавателей факультета компьютерных наук, вывести следующую информацию в столбцах с соответствующими именами

- имя преподавателя    Преподаватель

- дата поступления на работу   Дата поступления

2) Вывести корпуса, в которых располагается только одна кафедра

3) Вывести названия факультетов и имена их деканов, на которых имеется хотя бы один преподаватель-профессор

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

5)  Вывести названия факультетов, на которых количество кафедр  больше, чем количество кафедр на каждом из факультетов с фондом финансирования в диапазоне 50000-300000

6) Вывести среднее количество лекций на одного преподавателя и среднее количество преподавателей на один факультет

7) По каждому преподавателю факультета компьютерных наук вывести:

- его имя,

- количество дисциплин, которые он преподает

- количество занятий, которые он имеет на первой неделе,

- количество занятий, которые он имеет на второй неделе

при условии, что:

- он проводит занятия  не более, чем в 3-х группах

  1.  Вариант 17

1) Для каждой аудитории, в которой проводят занятия преподаватели факультета компьютерных наук, которые (преподаватели)  имеют зарплату (salary+commission)  в диапазоне между минимальной и максимальной зарплатой кафедры, заведующим которой является Иванов, вывести номер аудитории, ее корпус и количество в ней мест

2) Вывести номера аудиторий корпуса 6,  в которых преподают более 3-х преподавателей или в которых проводятся занятия для менее 4-х групп

3) Вывести названия факультетов, которые расположены не в корпусе 5 и не имеют преподавателей, поступивших на работу в диапазоне 01.01.2000-01.06.2000

4) Вывести номера групп кафедры ИПО, у которых рейтинг больше, чем хотя бы одной группы третьего курса этой кафедры. Привести два варианта: с оператором >ANY и с оператором EXISTS)

5) Вывести названия факультетов, на которых суммарное количество студентов в группах с рейтингом в диапазоне 10-50 больше, чем во всех группах 5-го курса факультета, на котором заведующим является Иванов

6) Вывести минимальную зарплату среди преподавателей-доцентов (вместе с именем этого доцента) и максимальную зарплату среди профессоров (вместе с именем этого профессора

7) По каждой кафедре, преподаватели которой преподают дисциплину СУБД, вывести:

- название кафедры

- количество лекций, читаемых преподавателями по дисциплине СУБД

- количество групп этой кафедры, которым читаются лекции по дисциплине СУБД

при условии, что на этой кафедре дисциплину СУБД преподают не более 2-х преподавателей

  1.  Вариант 18

1)  Вывести имена подчиненных заведующего кафедрой ИПО, которые (подчиненные) имеют ставку (salary), не совпадающую ни с одной из ставок преподавателей кафедры КИТ, ИЛИ надбавку (commission), также не совпадающую ни с одной из надбавок преподавателей кафедры КИТ

2)  Вывести кафедры и их корпуса факультета компьютерных наук, которые (кафедры) располагаются в корпусе, отличающемся от корпуса факультета компьютерных наук

3) Вывести названия кафедр факультета, деканом которого является Иванов, на которых (кафедрах) нет ни одного преподавателя-профессора

4) Вывести названия кафедр факультета компьютерных наук, которые расположены в одном из корпусов, в которых проводят занятия преподаватели кафедры ИПО

5) Вывести названия факультетов, на которых  значение зарплаты (salary+ commission) всех преподавателей-профессоров превышает более, чем на 10000 суммарное значение зарплаты всех преподавателей-доцентов факультета компьютерных наук.

6) Вывести минимальную дату приема на работу, вместе с именем этого преподавателя, и максимальную дату приема на работу, также вместе с именем этого преподавателя.

7) По каждому факультету вывести

- название факультета

- количество дисциплин, изучаемых студентами факультета

- количество дисциплин, преподаваемых преподавателями факультета

  1.  Контрольные вопросы

Ответьте на следующие вопросы:

  1.  В каких фразах предложения SELECT может использоваться подзапрос?
  2.  Что такое связанный подзапрос? Как подзапрос становится связанным? Как он вычисляется?
  3.  С какими операторами может использоваться подзапрос во фразе WHERE?
  4.  Какие правила использования подзапроса в простых условий сравнения?
  5.  Какие правила связывания подзапроса во фразе HAVING?
  6.  Приведите пример, когда запрос не может быть выражен иначе, чем использование подзапроса во фразе FROM.

PAGE  1


 

А также другие работы, которые могут Вас заинтересовать

8629. Философия И.Канта 102.5 KB
  Философия И.Канта Вопросы: 1. В чём отличие эмпирических знаний от априорных? 2. Какие признаки априорного знания выделяет И.Кант? Какова задача философии, по Канту? Чем обусловлена постановка задачи? 3. Какую классификацию суждений предлагает Кант?...
8630. Русская религиозная философия 19-20 веков 68 KB
  Русская религиозная философия 19-20 вв. Вопросы: 1. Какой должна стать русская философия? С каким предметом она должна иметь дело? 2. Как И.Ильин аргументирует необходимость отказа от намеренного выдумывания философских систем? 3. Каков закон иссл...
8631. Сознание как философская категория 56.5 KB
  Сознание как философская категория Вопросы: 1. Какие правила мышления выделяет И.Кант? Как понимается мышление в европейской традиции? 2. Что такое сознание? Как формулирует своё предназначение человек сознающий? 3. Каковы свойства гармонии, носител...
8632. Проблема познания в философии 105 KB
  В каком смысле акты научного познания - свободные явления. Можно ли рассматривать знание законов как актуализацию готовых смыслов и сущностей. Можно ли рассматривать знание как превращение...
8633. Личность как философская категория 158.5 KB
  Человек бесчувственный. За что критикует К.Льюис авторов одного из английских учебников для старших классов. Какие следствия имеют взгляды данных авторов. Какая мыслительная традиция...
8634. Философия общества. Соотношение понятий культура и цивилизация 180.5 KB
  Философия общества. Соотношение понятий культура и цивилизация. Изложите критику социального материализма. Изложите критику социального психологизма. Каковы сущностные признаки общественного явления...
8635. Глобальные проблемы современности 435.5 KB
  Глобальные проблемы современности Вопросы: 1. В чём специфика технической эпохи 19-20 веков? 2. Как связаны техника и культура? 3. Какие три стадии в истории человечества выделяет Н.Бердяев? Чем отличается организм от организации?. В чём...
8636. Философия Платона. Платоновская идея 125.5 KB
  Дайте толкование мифу о пещере. Что символизируют люди в пещере, тени, вещи, костёр, солнце. Дайте определения Платоновскому понятию «идея». Дайте определения Платоновскому понятию «идея идей».
8637. Западная философия 19 века (философия жизни, экзистенциализм, позитивизм, психоанализ) 174.5 KB
  Западная философия 19 века (философия жизни, экзистенциализм, позитивизм, психоанализ) Вопросы: 1. Сформулируйте основной тезис экзистенциализма. Какие виды экзистенциализма следует различать? 2. Раскройте содержание основных принципов экзисте...