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
- Purpose of the lab
- To study SQL Oracle possibilities to formulate and process subqueries.
- To acquire practical skills in formulating and processing subqueries.
- Theoretical backgrounds
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:
- define the set of rows to be inserted into the target table of an INSERT or CREATE TABLE statement;
- define one or more values to be assigned to existing rows in an UPDATE statement;
- provide values for conditions in a WHERE clause, HAVING clause, or START WITH clause of SELECT, UPDATE, and DELETE statements;
- provide values for a specified column in an INSERT ... VALUES list;
- define a table to be operated on by a containing query.
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.
- Subquery in WHERE clause
- Subquery in simple comparison condition
Syntax:
Description:
The following rules are applied to the simple comparison condition with subquery in WHERE clause:
- Subquery should returns single row.
- If left hand side is expr, than subquery must return single row with the only value that type compatible with expr.
- If left hand side is expr_list, than subquery must return single row with the list of values that corresponds in number and type with expr_list. In this case comparison operator returns TRUE if every value in expr_list is equal (in case =) or not equal (in case !=, ^=, <>) to the every value returned by query.
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');
- Subquery in group comparison condition
Syntax:
Description:
The following rules are applied to the group comparison condition with subquery in WHERE clause:
- Subquery may return zero or more rows.
- If left hand side is expr, than subquery must return rows with the only value that type compatible with expr.
- If left hand side is expr_list, than subquery must return rows with the list of values that corresponds in number and type with expr_list.
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);
- Subquery in membership condition
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');
- Subquery in EXISTS condition
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.
- Correlated subqueries
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.
- Correlated subqueries in WHERE clause
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);
- Simple and correlated subqueries in HAVING clause
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 faculty’s 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);
- Simple subqueries in FROM clause
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
- Lab tasks
Please formulate SQL SELECT statements that correspond to the following queries:
Simple embedded queries
- Display departments that are located in the same building as informatics faculty
- Display faculties that have fund less than fund of ASU department:
The same table in parent and embedded query:
- Display teachers that have the same salary+increment more that have of Ivanov:salary + increment:
Correlated subqueries in WHERE clause
- Display faculties that have departments in building 5
- Display teachers that have more than 3 lectures in the 1st week:
- Display buildings that have only one faculty:
EXISTS operator in WHERE clause:
- Select teaches that have at least one lecture:
- Select teaches-professors that are not curators of first year groups
ANY, SOME and ALL operators in WHERE clause
- Display teachers of ASU department that have salary less at least one teacher of CS department
- Select departments that have fund less that fund at least one faculty:
- Display groups that have rating more that all ratings of groups of 5th course of CS department
Aggregate functions in subqueries
- Display teaches that have salary+increment more that average salary+increment in the university:
- Dislay faculties that have more than 7 departments:
- Display teachers that have more than 10 lectures in the 1st week:
Subqueries in HAVING clause
- Display teaches of CS department that have more lectures that any teacher of DBMS department:
- Control questions
Please, give answers to the following questions:
- Appendix A. Answer to the lab task
Please formulate SQL SELECT statements that correspond to the following queries:
Simple embedded queries
- Display departments that are located in the same building as informatics faculty
SELECT Name
FROM DEPARTMENT
WHERE Building = (SELECT Building
FROM FACULTY
WHERE Name = 'Computer Science');
- Display faculties that have fund less than fund of ASU department:
SELECT Name
FROM FACULTY
WHERE Fund < (SELECT Fund
FROM DEPARTMENT
WHERE Name = 'ASU');
The same table in parent and embedded query:
- Display teachers that have the same salary+increment more that have of Ivanov:salary + increment:
SELECT Name
FROM TEACHER
WHERE Salary + Increment + 100 > (SELECT (Salary + Increment) / 2
FROM TEACHER
WHERE Name = 'Иванов');
Correlated subqueries in WHERE clause
- Display faculties that have departments in building 5
SELECT Name
FROM FACULTY
WHERE 5 IN (SELECT Building
FROM DEPARTMENT
WHERE FACULTY.#F = DEPARTMENT.#F);
- Display teachers that have more than 3 lectures in the 1st week:
SELECT Name
FROM TEACHER
WHERE 3 < (SELECT COUNT(*)
FROM LECTURE
WHERE LECTURE.#T = TEACHER.#T AND Week = 1);
- Display buildings that have only one faculty:
SELECT Building
FROM FACULTY F1
WHERE 1 = (SELECT COUNT ( Building)
FROM FACULTY F2
WHERE F1.Building = F2.Building);
Using EXISTS in WHERE clause:
- Select teaches that have at least one lecture:
SELECT Name
FROM TEACHER
WHERE EXISTS (SELECT *
FROM LECTURE
WHERE LECTURE.#T = TEACHER.#T);
- Select teaches-professors that are not curators of first year groups
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.
- Display teachers of ASU department that have salary less at least one teacher of CS department
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 departments that have fund less that fund at least one faculty:
SELECT Name
FROM DEPARTMENT
WHERE Fund < ANY (SELECT Fund FROM FACULTY);
SELECT Name
FROM DEPARTMENT
WHERE Fund < (SELECT MAX(Fund) FROM FACULTY);
- Display groups that have rating more that all ratings of groups of 5th course of CS department
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);
Aggregate functions in subqueries
- Display teaches that have salary+increment more that average salary+increment in the university:
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);
- Dislay faculties that have more than 7 departments:
SELECT Name
FROM FACULTY
WHERE 7 < (SELECT COUNT(*)
FROM DEPARTMENT
WHERE DEPARTMENT.#F = FACULTY.#F);
- Display teachers that have more than 10 lectures in the 1st week:
SELECT Name
FROM TEACHER
WHERE 10 < (SELECT COUNT(*)
FROM LECTURE
WHERE LECTURE.#T = TEACHER.#T AND Week = 1);
Subqueries in HAVING clause
- Display teaches of CS department that have more lectures that any teacher of DBMS department:
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
