Transcript Chapter 13 Joining Tables - Wah Yan College, Kowloon

Part C. SQL
Chapter 13 Subqueries and Views
Copyright 2005 Radian Publishing Co.
Contents
Chapter 13 Subqueries and Views
13.1 Introduction to Subqueries
13.2 Type 1: Single-valued
Subqueries
• 13.2 A. Subqueries with Aggregate
Functions
• 13.2 B. Involving two different
tables
13.5 Views
• 13.5 A. Creating a Simple view
• 13.5 B. Finding the Mode
13.3 Type 2: Multi-valued Subqueries
• 13.3 A. The IN Operator
• 13.3 B. The = ANY Operator
• 13.3 C. The EXISTS Operator
13.4 Further Examples
2/20
Copyright 2005 Radian Publishing Co.
Chapter 13 Subqueries and Views
A subquery is a query which is embedded in another query.
A view is a subset of the database that is presented to some users.
It is a virtual table that does not take up as much physical space as an
ordinary table does, yet it allows you to perform query and updating to
the database.
3/20
Copyright 2005 Radian Publishing Co.
13.1 Introduction to Subqueries
(1/2)
If a query consists of a subquery, the subquery will always be executed
first. There are several variations in subqueries:
1. The subquery and the main query may refer to the same table, or
different tables.
2. The result of a subquery may be a single value, or a set of values.
3. The subquery may be placed in the WHERE clause, or in the FROM
clause.
4. A subquery may consist of another subquery, i.e. multi-sub-level.
4/20
Copyright 2005 Radian Publishing Co.
13.1 Introduction to Subqueries
(2/2)
Singled-valued subqueries need relational operators,
like =, >, >=, <>, <, <=;
Multi-valued subqueries need operators like IN, ANY and EXISTS.
5/20
Copyright 2005 Radian Publishing Co.
13.2 Type 1: Single-valued Subqueries
Single-valued subquery:
SELECT ... FROM Table1
WHERE FieldName1 =|>|>=|<|<=|<>
(SELECT ... from Table2)
The query and the subquery may use the same table or different tables.
6/20
Copyright 2005 Radian Publishing Co.
13.2 A. Subqueries with Aggregate Functions
A subquery with an aggregate function returns a single value.
7/20
Copyright 2005 Radian Publishing Co.
13.2 B. Involving two different tables
The query and the subquery may use different tables.
8/20
Copyright 2005 Radian Publishing Co.
13.3 Type 2: Multi-valued Subqueries
Some subqueries return a set of records. We cannot use simple
relational operators, like =, >, <, <> etc. Special operators, like IN and
EXISTS, are designed for multi-valued subqueries.
Multi-valued subquery:
SELECT ... FROM Table1
WHERE FieldName1 [IN | = ANY | EXISTS]
(SELECT ... from Table2)
9/20
Copyright 2005 Radian Publishing Co.
13.3 A. The IN Operator
The IN operator compares a piece of data with a set of values and
returns true if any one of the values in the set matches with the data.
10/20
Copyright 2005 Radian Publishing Co.
13.3 B. The = ANY Operator
The operator = ANY is the same as the IN operator.
11/20
Copyright 2005 Radian Publishing Co.
13.3 C. The EXISTS Operator
The function EXISTS() is a boolean function which tells whether the
result of a subquery is successful or not.
12/20
Copyright 2005 Radian Publishing Co.
13.4 Further Examples
Refer to textbook P. 314
13/20
Copyright 2005 Radian Publishing Co.
13.5 Views
(1/2)
A view is a named query result from one or more tables in a database.
A view is often called a virtual table. You can perform queries and
updating on a view, yet it does not take up as much physical space as
an ordinary table.
When a view is changed, the tables that the view is built from will be
changed.
14/20
Copyright 2005 Radian Publishing Co.
13.5 Views
(2/2)
The major advantages of views are:
1. A view can save the effort of entering the same complicated SQL
statement every time.
2. A view can enforce security. It can prevent users from viewing other
data that may be private or confidential.
15/20
Copyright 2005 Radian Publishing Co.
13.5 A. Creating a Simple view
The basic syntax creating a view is
CREATE VIEW ViewName AS SELECT ...
16/20
Copyright 2005 Radian Publishing Co.
(1/2)
13.5 A. Creating a Simple view
To make a query on the view SALESVIEW
17/20
Copyright 2005 Radian Publishing Co.
(2/2)
13.5 B. Finding the Mode
(1/3)
We may use View to help finding the mode, which tells the most
popular quantity.
For example:
18/20
Copyright 2005 Radian Publishing Co.
13.5 B. Finding the Mode
(2/3)
It is clear that “Central”, “Tai Kok Tsui” and
“Tai Po” are popular district.
19/20
Copyright 2005 Radian Publishing Co.
13.5 B. Finding the Mode
(3/3)
The results of the query show the
most popular districts.
20/20
Copyright 2005 Radian Publishing Co.