6827
Embedded queries (subqueries) in SQL Oracle
Лабораторная работа
Информатика, кибернетика и программирование
Embedded queries (subqueries) in SQL Oracle Purpose of the lab To study SQL Oracle possibilities to formulate and process subqueries. To acquire practical skills in formulating and processing subqueries. Theoretical backgroun...
Английский
2013-01-08
92 KB
8 чел.
Embedded queries (subqueries) in SQL Oracle
A subquery is a query whose results are passed as the argument for another query. Subqueries enable you to bind several queries together.
A query is an operation that retrieves data from one or more tables. In this reference, a top-level query is called a SELECT statement, and a query nested within a SELECT statement is called a subquery. That is a subquery is a query whose results are passed as the argument for another query. Subqueries enable you to bind several queries together.
Subqueries are used to:
You do this by placing the subquery in the FROM clause of the containing query as you would a table name. You may use subqueries in place of tables in this way as well in INSERT, UDPATE, and DELETE statements.
Subqueries so used can employ correlation variables, but only those defined within the subquery itself, not outer references. Outer references ("left-correlated subqueries") are allowed only in the FROM clause of a SELECT statement.
A subquery answers multiple-part questions. For example, to determine who works in John's department, you can first use a subquery to determine the department in which John works. You can then answer the original question with the parent SELECT statement.
A subquery can contain another subquery. Oracle places no limit on the level of query nesting.
If tables in a subquery have the same name as tables in the containing statement, you must prefix any reference to the column of the table from the containing statement with the table name or alias. To make your statements easier for you to read, always qualify the columns in a subquery with the name or alias of the table.
Oracle performs a correlated subquery when the subquery references a column from a table referred to in the parent statement. A correlated subquery is evaluated once for each row processed by the parent statement. The parent statement can be a SELECT, UPDATE, or DELETE statement.
A correlated subquery answers a multiple-part question whose answer depends on the value in each row processed by the parent statement. For example, you can use a correlated subquery to determine which teachers earn more than the average salaries for their departments. In this case, the correlated subquery specifically computes the average salary for each department.
Syntax:
Description:
The following rules are applied to the simple comparison condition with subquery in WHERE clause:
Examples:
1. Select departments that are located in the same building as informatics faculty:
SELECT Name
FROM DEPARTMENT
WHERE Building = (SELECT Building
FROM FACULTY
WHERE Name = 'informatics');
2. Select faculties that have funds less than fund of CAD department
SELECT Name
FROM FACULTY
WHERE Fund < (SELECT Fund
FROM DEPARTMENT
WHERE Name = 'CAD');
3. Select teachers that salary + commission is more than 100 of the half of salary + commission if Bill:
SELECT Name
FROM TEACHER
WHERE Salary + Commission + 100 > (SELECT (Salary + Commission) / 2
FROM TEACHER
WHERE Name = 'Bill');
4. Select teachers that have the same department No and post as Bill:
SELECT Name
FROM TEACHER
WHERE (DepNo, Post) = (SELECT (DepNo, Post
FROM TEACHER
WHERE Name = 'Bill');
Syntax:
Description:
The following rules are applied to the group comparison condition with subquery in WHERE clause:
ANY and SOME are equivalent and compares a value to each value in a list of rows returned by a query. Query may return zero or more rows. Evaluates toTRUE if at least one row returned by query is in relation (corresponding comparison operator) with value (list of values) defined by the first operand, otherwise it evaluates FALSE. If query does not returns any row it evaluates to FALSE
ALL compares a value (list of values) to every value (list of values) in a list of rows returned by a query. It Evaluates toTRUE if ALL rows returned by query is in relation (corresponding comparison operator) with value (list of values) defined by the first operand, otherwise it evaluates FALSE. If query does not returns any row it evaluates to TRUE
Examples:
1. Display departments with fund that is more that fund of at least one faculty:
SELECT Name
FROM DEPARTMENT
WHERE Fund > ANY (SELECT Fund FROM FACULTY);
ANY and aggregate functions. Pay attention, that operator <ANY is equivalent the following statement: “the left value is less than maximum of right values”, and operator >ANY is equivalent the following statement: “the left value is more than minimum of right values”. That is why these ANY operators may be expressed with the help of MAX and MIN functions in subquery.
2. Display departments with fund that is more that fund of at least one faculty:
SELECT Name
FROM DEPARTMENT
WHERE Fund > ANY (SELECT Fund FROM FACULTY);
SELECT Name
FROM DEPARTMENT
WHERE Fund > (SELECT MIN(Fund) FROM FACULTY);
3. Display groups that have ratings more than ratings of all groups of the fifth course of “DBMS” department:
SELECT Num
FROM GROUP
WHERE Rating >ALL (SELECT Rating
FROM GROUP, DEPARTMENT
WHERE GROUP.DepNo = DEPARTMENT.DepNo AND
DEPARTMENT.Name = 'DBMS' AND GROUP.Course = 5);
Syntax:
Description:
A membership condition tests for membership in a subquery.
Examples:
1. Select teachers that have lectures by at least one subject that have lectures the teacher 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 TCH.Name = 'Bill');
Syntax:
Description:
Evaluates to TRUE if a subquery returns at least one row.
Because of EXISTS are usually used in correlated subquery we will consider it more detailed later.
In order to correlate subquery it is necessary that subquery references to the column name of the parent query. A correlated subquery is evaluated once for each row processed by the parent statement. The parent statement can be a SELECT, UPDATE, or DELETE statement.
The following examples show the general syntax of a correlated subquery:
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);
Now we will discuss correlated subqueries in WHERE clause of SELECT statement.
Examples:
1. Display teachers that have at least one lecture:
SELECT Name
FROM TEACHER
WHERE EXISTS (SELECT *
FROM LECTURE
WHERE LECTURE.#T = TEACHER.#T);
Here by defining condition LECTURE.#T = TEACHER.#T we reference from subquery to outer query
2. Display teachers that have no lectures:
SELECT Name
FROM TEACHER
WHERE NOT EXISTS (SELECT *
FROM LECTURE
WHERE LECTURE.#T = TEACHER.#T);
You may use simple and correlated subqueries in HAWING clause.
If you use correlated subquery in HAVING clause in subquery you may reference to those elements of parent query that may be used in HAVING clause (usually they are grouped columns).
Examples:
1. List faculties where sum of all funds of all its departments exceeds more than 20000 the fund of the facultys department with maximum fund.
SELECT FACULTY.Name
FROM FACULTY F1, DEPARTMENT D1
WHERE F1.#F = D1.#F
GROUP BY F1.Name
HAVING SUM(D1.Fund) > (SELECT 200000 + MAX(Fund)
FROM FACULTY F2, DEPARTMENT D2
WHERE F2.#F = D2.#F AND F1.Name = F2.Name);
FROM clause may contain not only list of table names, but also subqueries. An aliases must be assigned to the subqueries in order to have possibilities to reference to such tables.
There are class of queries that cannot be expressed without subqueries in FROM clause. They are the queries that demand independent calculation of two or more queries and after that common usage of results of such queries.
Example:
Display average fund of all faculties and average salary of all teachers:
SELECT Fac.AvgFund, Tch.AvgSalary
FROM (SELECT AVG(Fund) AS AvgFund FROM FACULTY) Fac,
(SELECT AVG(Salary) AS AvgSalary FROM TEACHER) Tch
Please formulate SQL SELECT statements that correspond to the following queries:
The same table in parent and embedded query:
Correlated subqueries in WHERE clause
EXISTS operator in WHERE clause:
ANY, SOME and ALL operators in WHERE clause
Please, give answers to the following questions:
Please formulate SQL SELECT statements that correspond to the following queries:
SELECT Name
FROM DEPARTMENT
WHERE Building = (SELECT Building
FROM FACULTY
WHERE Name = 'Computer Science');
SELECT Name
FROM FACULTY
WHERE Fund < (SELECT Fund
FROM DEPARTMENT
WHERE Name = 'ASU');
The same table in parent and embedded query:
SELECT Name
FROM TEACHER
WHERE Salary + Increment + 100 > (SELECT (Salary + Increment) / 2
FROM TEACHER
WHERE Name = 'Иванов');
Correlated subqueries in WHERE clause
SELECT Name
FROM FACULTY
WHERE 5 IN (SELECT Building
FROM DEPARTMENT
WHERE FACULTY.#F = DEPARTMENT.#F);
SELECT Name
FROM TEACHER
WHERE 3 < (SELECT COUNT(*)
FROM LECTURE
WHERE LECTURE.#T = TEACHER.#T AND Week = 1);
SELECT Building
FROM FACULTY F1
WHERE 1 = (SELECT COUNT ( Building)
FROM FACULTY F2
WHERE F1.Building = F2.Building);
Using EXISTS in WHERE clause:
SELECT Name
FROM TEACHER
WHERE EXISTS (SELECT *
FROM LECTURE
WHERE LECTURE.#T = TEACHER.#T);
SELECT Name
FROM TEACHER
WHERE Post = professor AND
NOT EXISTS (SELECT * FROM GROUP
WHERE GROUP.#Curator = TEACHER.#T AND Course = 1);
Usage ANY, SOME and ALL.
SELECT Name
FROM TEACHER T, DEPARTMENT D
WHERE T.#D = D.#D AND D.Name = 'ASU' AND
Salary + Increment < ANY (SELECT Salary + Increment
FROM TEACHER T2, DEPARTMENT D2
WHERE T2.#D = D2.#D AND D.Name = 'CS');
SELECT Name
FROM DEPARTMENT
WHERE Fund < ANY (SELECT Fund FROM FACULTY);
SELECT Name
FROM DEPARTMENT
WHERE Fund < (SELECT MAX(Fund) FROM FACULTY);
SELECT Num
FROM GROUP
WHERE Rating >ALL (SELECT Rating
FROM GROUP, DEPARTMENT
WHERE GROUP.#D = DEPARTMENT.#D AND
DEPARTMENT.Name = 'CS' AND GROUP.Course = 5);
SELECT Num
FROM GROUP outer
WHERE NOT EXISTS (SELECT Rating
FROM GROUP inner, DEPARTMENT
WHERE GROUP.#D = DEPARTMENT.#D AND
outer.rating <= inner.rating
DEPARTMENT.Name = 'CS' AND GROUP.Course = 5);
SELECT Tch.Name
FROM DEPARTMENT Dep, TEACHER Tch
WHERE Dep.#D = Tch.#T AND Dep.Name = 'ASU' AND
Tch.Salary + Tch.Increment > (SELECT AVG(Salary+Increment) FROM TEAHER);
SELECT Name
FROM FACULTY
WHERE 7 < (SELECT COUNT(*)
FROM DEPARTMENT
WHERE DEPARTMENT.#F = FACULTY.#F);
SELECT Name
FROM TEACHER
WHERE 10 < (SELECT COUNT(*)
FROM LECTURE
WHERE LECTURE.#T = TEACHER.#T AND Week = 1);
SELECT Name
FROM TEACHER, LECTURE
WHERE TEACHER.#T = LECTURE.#T AND
TEACHER.#D = (SELECT #D FROM DEPARTMENT AND Name = 'Programming')
GROUP BY TEACHER.#T, TEACHER.Name
HAVING COUNT (*) > ALL (SELECT COUNT(*)
FROM TEACHER, LECTURE
WHERE TEACHER#T = LECTURE.#T AND
TEACHER.#D =(SELECT #D FROM DEPARTMENT AND Name = 'DBMS')
GROUP BY TEACHER.#T)
PAGE 4
А также другие работы, которые могут Вас заинтересовать | |||
37027. | Весна пришла. Сценарий класного часа | 17.01 KB | |
Настя: Ребята мы сегодня поговорим об одном из прекрасных времен года о весне. Далее Настя рассказывает о праздниках весны какие праздники знают дети Рассказ о космонавте Ю. Настя Аня Полет Белки и Стрелки в космос был осуществлен 19 августа 1960 года когда одна из ракет стартовавшая с космодрома Байконур вывела на орбиту космический корабль Восток с собаками на борту. Настя Какой бывает весна 3. | |||
37029. | Что нами движет при выборе профессии. Классный час | 18.3 KB | |
Как вы думаете о чем сегодня пойдет речь Сегодня разговор наш будет о профессиональном самоопределении процессе и результате самостоятельного и сознательного выбора профессии. Означает ли это что к выбору профессии можно идти путем проб и ошибок А может надо сознательно готовить себя к будущей профессиональной деятельности Что мы имеем в виду когда произносим слово âпрофессияâ Профессия понятие многозначное: общность всех людей занятых данным видом труда. Выбор профессии можно отнести к самым сложным от... | |||
37031. | В гостях у светофора | 26.16 KB | |
Игра Разрешается запрещается. Ребята давайте вспомним правила дорожного движения и поиграем в игру Разрешается запрещается. Если вы считаете что моё высказывание верное то говорите разрешается а если не верное то запрещается. Играть на мостовойзапрещается. | |||
37034. | В ГОСТЯХ У СКАЗОК БРАТЬЕВ ГРИММ | 84.86 KB | |
Und ds ist mein Zuberstock. lle zuschuen Wer ist ds Heinz ist ful Ist Heinz ful Wie ist Heinz Он любит много спать и много есть совсем не любит работать. Nennt lle Lebensmittel die Heinz mg Дети: Ds ist У. Rtet ws er will Дети задают вопросы типа: Ist ds Kse Вот так мечтая о благополучии и вкусной пище но не работая Гейнц лишился козы которая у него была улья с пчелами и даже последнего горшка с медом. | |||
37035. | Дети войны. Последние свидетели | 73.5 KB | |
Оборудование: плакаты компьютер мультимедийный проектор фотографии военных лет Презентация 1 отрывок из документального фильма Дети войны. Презентация 1. Слайд 1 Презентация 1. Презентация 1. | |||