Chapter 6: Multiple Tables II

1
Chapter 6 Multiple Tables II

• Data Retrieval from Multiple Tables
• Various Types of Joins
• Combining Results through Set Operators
• Data Retrieval with Sub-queries or nested
  Queries
• Sub-queries with DDL Statement
• Sub-queries with DML Statements

2
Set Operators

• A set operator combines output from two
  independent queries.
• (Union compatibility) The output from both
  queries must return same number of columns, and
  respective columns must have similar domain.
• The general syntax for any set operation is
• SELECT Query1
• Set operator
• SELECT Query2

3
More on Set Operators
4
E.g. lists all faculty and employee in the result
SQLgt select facultyid ID, Name from faculty 2
UNION 3 select employeeid ID, Lname Name from
employee ID NAME ----------
--------------- 111 Jones 111
Smith 123 Mobley 123 Roberts
135 Garner 200 Shaw 222 Chen
222 Williams 235 Vajpayee 246
Houston 333 Collins ID
NAME ---------- --------------- 345 Sen
433 McCall 444 Rivera 543 Dev
555 Chang 16 rows selected.
5
Sub-query

• A sub-query is also known as a nested query.
• A sub-query normally has a SELECT query within
  another SELECT query.
• A sub-query is very useful when a query based on
  a table depends on the data in that table itself.
• The sub-query can be used within a WHERE, HAVING,
  or FROM clause of another SELECT query.
• Two types of sub-queries are
• Single-row sub-query a sub-query that returns
  only one row of data.
• Multiple-row sub-query a sub-query that returns
  more than one row of data.

6
Single-Row Sub-query

• General Syntax
• SELECT columnlist/ FROM tablename
• WHERE columnname operator
• (SELECT columnlist FROM tablename
• WHERE condition)
• A sub-query must be enclosed within a pair of
  parentheses.
• An ORDER BY clause is not allowed in a sub-query.
• The sub-query is used on the right-hand-side of
  the condition.
• The inner query is executed first, then the outer
  query.

7
Examples to Single-row Sub-query

• Find employyes who work in Finance department.
• SELECT Lname, Fname
• FROM employee
• WHERE DeptId (SELECT DeptId FROM dept
• WHERE UPPER(DeptName) FINANCE)
• Who makes higher salary than employee 433?
• SELECT Lname, Fname, Salary
• FROM employee
• WHERE Salary gt (SELECT Salary FROM employee
• WHERE EmployeeId 433)

8
Multiple-row Sub-query
A multiple row sub-query returns more than one
row. Special operators are used with such
sub-queries.
ANY and ALL operators can be used with other
relational operators. ltANY means less than the
maximum value. ANY is similar to IN. gtANY means
larger than the minimum value. gtALL means larger
than the maximum value. ltALL means less than the
minimum value.
9
Examples to Multiple-row Sub-query

• SELECT StudentId, Last, First
• FROM student
• WHERE FacultyId IN (SELECT FacultyId
• FROM faculty WHERE DeptId 1)
• SELECT EmployeeId, Lname, Fname, Salary
• FROM employee
• WHERE Salary gt ANY
• (SELECT Salary FROM employee WHERE PositionId
  2)
• AND PositionId ltgt 2
• SELECT EmployeeId, Lname, Fname, Salary
• FROM employee
• WHERE Salary lt ALL
• (SELECT AVG (Salary) FROM employee GROUP BY
  DeptId)

10
Creating a Table Using a Sub-query

• You can create a table by using a nested SELECT
  query.
• The new table is populated with rows selected
  from another table.
• The general syntax
• CREATE TABLE tablename
• AS
• SELECT query
• For example,
• CREATE TABLE depart10
• AS
• SELECT EmployeeId, Lname, Fname, Salary
• FROM employee WHERE DeptId 10
• Primary Key constraint is not transferred to the
  new table, but NOT NULL constraint is.

11
INSERT Using a Sub-Query

• An existing table can be populated with a
  sub-query.
• The table must already exist to insert rows into
  it.
• The general syntax is
• INSERT INTO tablename (column aliases)
• SELECT columnnames FROM tablename
• WHERE condition
• Example
• INSERT INTO depart30 (Empno, Last, First)
• SELECT EmployeeId, Lname, Fname
• FROM employee WHERE DeptId 30

12
UPDATE Using a Sub-Query

• UPDATA can be performed with a sub-query.
• The general syntax
• UPDATE tablename
• SET (columnnames)
• (SELECT-FROM-WHERE sub-query)
• WHERE condition
• For example
• UPDATE employee
• SET (PositionId, Supervisor, Salary)
• (SELECT PositionId, Supervisor, Salary
• FROM employee WHERE EmployeeId 135)
• WHERE EmployeeId 200

13
DELETE Using a Sub-Query

• A row or rows can be deleted based on a value
  returned by a sub-query.
• The general syntax
• DELETE FROM tablename
• WHERE Columnname
• (SELECT-FROM-WHERE sub-query)
• For example
• DELETE FROM employee
• WHERE DeptId
• (SELECT DeptId FROM dept
• WHERE UPPER (DeptName) MARKETING)

14
TOP-N ANALYSIS

• Top-N queries are used to sort rows in a table
  and then find the first N largest values or first
  N smallest values.
• Top-N query uses ORDER BY clause to sort rows.
• The sorted rows are numbered with a pseudo-column
  named ROWNUM.

15
SQLgt select rownum, building, roomno, capacity
2 from (select building, roomno, capacity from
location 3 order by capacity desc) 4 where
rownum lt5 ROWNUM BUILDIN ROO
CAPACITY ---------- ------- --- ----------
1 Kennedy 204 50 2 Nehru 301
50 3 Nehru 309 45
4 Kennedy 206 40 5 Kennedy
202 35
SQLgt SELECT ROWNUM, Lname, Fname, Salary 2
FROM (SELECT Lname, Fname, Salary FROM employee
3 ORDER BY Salary) 4 WHERE ROWNUM lt 3
ROWNUM LNAME FNAME
SALARY ---------- --------------- ---------------
---------- 1 Shaw Jinku
24500 2 Chen Sunny
35000 3 Garner
Stanley 45000