Поиск

Полнотекстовый поиск:
Где искать:
везде
только в названии
только в тексте
Выводить:
описание
слова в тексте
только заголовок

Рекомендуем ознакомиться

Информатика, программирование->Курсовая работа
Гамильтонова задача о путешественнике нередко преобразуется в задачу о коммивояжере. Коммивояжер не свободно путешествующий турист, а деловой человек,...полностью>>
Информатика, программирование->Лабораторная работа
Вывод: В ходе данной лабораторной работы усовершенствовал практические навыки разработки интерфейса пользователя с помощью WindowsForms, приобрела пра...полностью>>
Информатика, программирование->Лабораторная работа
щелкнув указателем под названием поля, для которого задается условие; - если вы не хотите видеть какое-либо поле, то вам необходимо снять галочку с со...полностью>>
Информатика, программирование->Реферат
Сеть Internet можно описать как огромную цифровую магистраль - систему, связывающую миллионы компьютеров, подключенных к тысячам сетей по всему миру. ...полностью>>

Главная > Лабораторная работа >Информатика, программирование

Сохрани ссылку в одной из сетей:

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.

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

2.1.Subquery in WHERE clause

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

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

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

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

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

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

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

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

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

4.Control questions

Please, give answers to the following questions:

5.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)

8




Загрузить файл

Похожие страницы:

  1. Разработка баз данных в Delphi (2)

    Статья >> Информатика
    ... видны в объектах Table и Query, работающих через другое соединение. ... некоторые основные понятия о запросах (queries) и транзакциях. Это достаточно широкие ... записей. Сокращение SQL означает Structured Query Language - Язык Структурированных Запросов, ...
  2. Computer Based Training Business Interchange Essay Research

    Реферат >> Остальные работы
    ... casual users, and accidental queries while allowing access to ... system interacts with and queries a database of video indexes ... decided to begin providing embedded training in the applications ... the group now provides embedded training in the applications ...
  3. Excel (3)

    Реферат >> Информатика
    ... баз данных 11.2. Оболочка программы Query 11.3. Загрузка внешнего набора данных ... называется OLE 2.0 (Object Linking and Embedding). 8.1. Импортирование рисунков в Excel 97. Переведите ...
  4. Компьютерная поддержка коммерческой деятельности фирмы (2)

    Дипломная работа >> Информатика
    ... называется OLE 2.0 (Object Linking and Embedding). Импортирование рисунков в Excel. Переведите указатель ...
  5. Базы данных и системы управления базами данных (3)

    Реферат >> Финансы
    ... OLE-объектов (Object Linking and Embeding) в рамках среды WINDOWS. OLE ... образцов). SQL - запросы (Structured Query Language - Структурированный язык запросов). Пользователь ... знаком или вызывая в меню Query директиву Run. Access индицирует отобранные ...

Хочу больше похожих работ...

Generated in 0.0026810169219971