ID: 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

1.  Purpose of the lab

•  To study SQL Oracle possibilities to formulate and process subqueries.
•  To acquire practical skills in formulating and processing subqueries.

1.  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.

1.  Subquery in WHERE clause
   1.  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');

1.  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);

1.  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');

1.  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.

1.  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.

1.  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);

1.  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);

1.  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

1.  Lab tasks

Please formulate SQL SELECT statements that correspond to the following queries:

Simple embedded queries

1.  Display departments that are located in the same building as informatics faculty
2.  Display faculties that have fund less than fund of ASU department:

The same table in parent and embedded query:

1.  Display teachers that have the same salary+increment more that have of Ivanov:salary + increment:

Correlated subqueries in WHERE clause

1.  Display faculties that have departments in building 5
2.  Display teachers that have more than 3 lectures in the 1st week:
3.  Display buildings that have only one faculty:

EXISTS operator in WHERE clause:

1.  Select teaches that have at least one lecture:
2.  Select teaches-professors that are not curators of first year groups

ANY, SOME and ALL operators in  WHERE clause

1.  Display teachers of ASU department that have salary less at least one teacher of CS department
2.  Select departments that have fund less that fund at least one faculty:
3.  Display groups that have rating more that all ratings of groups of 5th course of CS department

Aggregate functions in subqueries

1.  Display teaches that have salary+increment more that average salary+increment in the university:
2.  Dislay faculties that have more than 7 departments:
3.  Display teachers that have more than 10 lectures in the 1st week:

Subqueries in HAVING clause

1.  Display teaches of CS department that have more lectures that any teacher of DBMS department:

1.  Control questions

Please, give answers to the following questions:

1.  Appendix A. Answer to the lab task

Please formulate SQL SELECT statements that correspond to the following queries:

Simple embedded queries

1.  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');

1.  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:

1.  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

1.  Display faculties that have departments in building 5

SELECT Name

FROM  FACULTY

WHERE  5 IN (SELECT Building

      FROM  DEPARTMENT

      WHERE FACULTY.#F = DEPARTMENT.#F);

1.  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);

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:

1.  Select teaches that have at least one lecture:

SELECT Name

FROM  TEACHER

WHERE  EXISTS (SELECT *

       FROM  LECTURE

       WHERE LECTURE.#T = TEACHER.#T);

1.  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.

1.  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');

1.   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);

1.   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

1.  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);

1.  Dislay faculties that have more than 7 departments:

SELECT Name

FROM  FACULTY

WHERE  7 < (SELECT COUNT(*)

      FROM  DEPARTMENT

      WHERE DEPARTMENT.#F = FACULTY.#F);

1.  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

1.  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