Docsity
Docsity

Prepare for your exams
Prepare for your exams

Study with the several resources on Docsity


Earn points to download
Earn points to download

Earn points by helping other students or get them with a premium plan


Guidelines and tips
Guidelines and tips

Notes on Advanced SQL - introduction to Programming Principles | CSCI 1301, Study notes of Computer Science

Material Type: Notes; Professor: Liang; Class: INTRO PROGRAMMING PRINCIPLES; Subject: Computer Science; University: Armstrong Atlantic State University; Term: Unknown 2005;

Typology: Study notes

Pre 2010

Uploaded on 08/04/2009

koofers-user-r4a
koofers-user-r4a 🇺🇸

10 documents

1 / 32

Toggle sidebar

This page cannot be seen from the preview

Don't miss anything!

bg1
CHAPTER
5
Advanced SQL
Objectives
To write queries using nested select statements.
To write quires using joins.
To use table aliases to join same tables.
To use the in, all, any, exists and unique operators
for conditions involving sets.
To understand the various join operations: natural
join, inner join, left outer join, right outer join,
and full outer join.
To use the union, union all, intersect, and minus set
operators.
To create and use views.
To understand the Oracle rowId and rowNum columns.
To create and use indexes and clusters.
To create and use database links for distributed
quires.
To create and use synonyms for database objects.
To create and use sequences.
5.1 Introduction
In the preceding chapter, you learned simple SQL statements,
SQL functions, the order by clause, the group by clause, and
the having clause. The SQL statements in the preceding
chapter use one select clause that involves a single table.
© Copyright Y. Daniel Liang, 2005 1
pf3
pf4
pf5
pf8
pf9
pfa
pfd
pfe
pff
pf12
pf13
pf14
pf15
pf16
pf17
pf18
pf19
pf1a
pf1b
pf1c
pf1d
pf1e
pf1f
pf20

Partial preview of the text

Download Notes on Advanced SQL - introduction to Programming Principles | CSCI 1301 and more Study notes Computer Science in PDF only on Docsity!

CHAPTER

Advanced SQL

Objectives

• To write queries using nested select statements.

• To write quires using joins.

• To use table aliases to join same tables.

• To use the in, all, any, exists and unique operators

for conditions involving sets.

• To understand the various join operations: natural

join, inner join, left outer join, right outer join,

and full outer join.

• To use the union, union all, intersect, and minus set

operators.

• To create and use views.

• To understand the Oracle rowId and rowNum columns.

• To create and use indexes and clusters.

• To create and use database links for distributed

quires.

• To create and use synonyms for database objects.

• To create and use sequences.

5.1 Introduction

In the preceding chapter, you learned simple SQL statements,

SQL functions, the order by clause, the group by clause, and

the having clause. The SQL statements in the preceding

chapter use one select clause that involves a single table.

This chapter introduces nested queries and the queries that

involve multiple tables. You will also learn how to create

and use views, indexes, clusters, sequences, and database

links.

5.2 Nested Queries

A select clause can be nested inside another select clause.

This is known as nested queries or subqueries. Sometimes, a

simple select statement cannot get the query. You have to

use nested queries.

Example 5.1: Find the faculty with the highest salary.

You may attempt to solve the query using the following

statement:

select lastName || ', ' || firstName as "Name", salary from Faculty where salary = max(salary);

This is erroneous because the aggregate function max cannot

be used in the where clause. To fix it, use a nested query

as follows:

select lastName || ', ' || firstName as "Name", salary from Faculty where salary = (select max(salary) from Faculty);

The result is shown in Figure 5.1.

Figure 5.

Example 5.1 demonstrates the nested queries.

Example 5.2: Find the department with the largest total

salary. The result is shown in Figure 5.2.

select deptId, sum(salary) from Faculty

The tables Student and Enrollment are listed in the from

clause. The query examines all pairs of rows, one from

Student and the other from Enrollment and selects the pairs

that satisfy the condition in the where clause. The rows in

Student have last name Smith and first name Jacob and both

rows from Student and Enrollment have the same ssn values.

For each pair selected, lastName and firstName from Student

and courseId from Enrollment are used to produce the result,

as shown in Figure 5.4.

Figure 5.

Example 5.3 demonstrates queries involving multiple tables.

Student and Enrollment have the same attribute ssn. To

distinguish them in a query, use Student.ssn and

Enrollment.ssn.

When joining two tables without the where clause, the result

is a Cartesian product, i.e. all pairs of two rows from each

table are selected.

NOTE: SQL2 introduced the natural join and join,

operators that can be used in the from clause to

specify a join of two tables. The syntax A

natural join B joins A with B on their common

attributes. The syntax A join B on condition

joins A with B with a specified condition.

Example 5.3 can be rewritten using the natural

join or join operators as follows:

select distinct lastName || ', ' || firstName as "Name", courseId from Student natural join Enrollment where lastName = 'Smith' and firstName = 'Jacob'; select distinct lastName || ', ' || firstName as "Name", courseId

from Student join Enrollment on Student.ssn = Enrollment.ssn and lastName = 'Smith' and firstName = 'Jacob' ;

***End of NOTE

5.4 Tuple Aliases

In the preceding query, you distinguish attributes by

prefixing table names before the attributes using the dot

notation (e.g. Student.ssn). Occasionally, you need to join

rows from the same table, as in the following query.

Example 5.4: Find all pairs of students with the same phone

number. List the students and their common phone number. To

solve this query, you need to join tables Student with

Student as shown in Figure 5.5.

Student Table A tuple lastName mi firstName … phone Student Table lastName mi firstName … phone Equal

Figure 5.

Student is joined with Student on the attribute phone.

To distinguish the rows from both Student tables, use table

aliases. You can alias the first Student table as s1 and the

second as s2. The query can be written as follows:

select s1.firstName || ' ' || s1.lastName as "Student 1", s2.firstName || ' ' || s2.lastName as "Student 2", s1.phone from Student s1 , Student s where s1.phone = s2.phone and s1.ssn < s2.ssn;

s1 and s2 are the aliases for Student. The query examines

all pairs of rows, one from s1 and the other from s2 and

selects the pairs whose rows have the same phone number. For

each pair selected, lastName and firstName from s1 and s

are used to produce the result, as shown in Figure 5.6.

no duplicate tuples. The unique condition is currently

not supported in Oracle. However, you can write queries

without using the unique condition.

NOTE: All the Boolean expressions can be negated

by putting the not keyword in front of the

expression.

NOTE: You can use e op S provided that S has a

single value. If S is a query that results in

more than one value, an error would occur.

Example 5.5: Find all faculty in the Computer Science and

Mathematics departments. The query result is shown in Figure

select firstName || ' ' || lastName as "CS/MATH Faculty" from Faculty where deptId in ( select deptId from Department where name = 'Computer Science' or name = 'Mathematics');

Figure 5.

Example 5.5 demonstrates the in operator.

Many quires involving the in operator are nested queries.

They can often be rewritten using joins. Example 5.5 can be

rewritten using joins as follows:

select firstName || ' ' || lastName as "CS/MATH Faculty" from Faculty, Department where Faculty.deptId = Department.deptId and (name = 'Computer Science' or name = 'Mathematics');

There are many ways to write queries. All solutions are

acceptable and there is no difference in the performance.

SQL is a non-procedural language. Each query is translated

to procedures and optimized by the DBMS. The advantage of

using joins is that you can display attributes from both

tables. For example, you can display department name in the

query as follows:

select firstName || ' ' || lastName as "CS/MATH Faculty", Department.name from Faculty, Department where Faculty.deptId = Department.deptId and (name = 'Computer Science' or name = 'Mathematics');

Example 5.6: Find all faculty whose salary is greater than

the salary of all the faculty in the MATH department. The

query result is shown in Figure 5.8.

select firstName || ' ' || lastName as "Faculty" from Faculty where salary > all ( select salary from Faculty where deptId = 'MATH');

Figure 5.

Example 5.6 demonstrates the all operator.

This query is equivalent to the following:

select firstName || ' ' || lastName as "Faculty" from Faculty where salary > (select max(salary) from Faculty where deptId = 'MATH');

Example 5.7: Find all students who take at least one course

taught by Alex Bedat. List the student names and course

titles. The query result is shown in Figure 5.9.

where f.ssn = TaughtBy.ssn);

Figure 5.

Example 5.8 demonstrates the exist operator.

Example 5.9: Find the department with unique faculty last

names.

select Department.name from Dapartment d where unique ( select lastName from Faculty where d.deptId = Faculty.deptId);

The unique condition currently not supported in Oracle.

However, you can write this query without using the unique

condition as follows:

select name from Department d where (select count(lastName) from Faculty where d.deptId = Faculty.deptId) = (select count(distinct lastName) from Faculty where d.deptId = Faculty.deptId);

The output the query is shown in Figure 5.11. Note that the

CS department has two faculty Frank Goldman and Kim Goldman

with the same last name. So, the CS department is not

selected in the query.

Figure 5.

Example 5.9 demonstrates quires without using the unique

keyword.

5.6 Outer Join (Optional)

A join of two tables returns only those rows that satisfy

the join condition. This is known as inner join or simple

join. Sometimes it is convenient to list all tuples from one

or both tables in the result even though they are not

matched in the inner join. SQL 92 provides the outer join

operation, which can be used for this purpose. An outer join

extends the result of an inner join and returns all rows

that satisfy the join condition and also some or all of

those rows from one or both tables for which no rows satisfy

the join condition.

To write a query that performs an outer join of tables A and

B and returns all rows from A (a left outer join), use the

syntax A left [outer] join B on condition , For all rows in A

that have no matching rows in B, Oracle returns null for any

select list expressions containing columns of B.

To write a query that performs an outer join of tables A and

B and returns all rows from B (a right outer join), use the

syntax A right [outer] join B on condition. For all rows in

A that have no matching rows in B, Oracle returns null for

any select list expressions containing columns of A.

To write a query that performs an outer join and returns all

rows from A and B, extended with nulls if they do not

satisfy the join condition (a full outer join), use the

  • Q1 union all Q2 returns all rows from both queries

(duplicates are not eliminated).

  • Q1 intersect Q2 returns all rows that appear in both

queries.

  • Q1 minus Q2 returns all distinct rows selected by the

first query but not the second.

Example 5.11: Display all faculty in the CS department and

all faculty who teach the CS courses. A CS course may be

taught by a faculty in other departments. You can use the

union operator to solve the query. The query result is shown

in Figure 5.13.

select firstName || ' ' || lastName as "Name" from Faculty where deptId = 'CS' union select firstName || ' ' || lastName as "Name" from Faculty, TaughtBy, Course, Subject where Faculty.ssn = TaughtBy.ssn and TaughtBy.courseId = Course.courseId and Course.subjectId = Subject.subjectId and Subject.deptId = 'CS';

Figure 5.

Example 5.11 demonstrates the union operator.

The query that uses the union operator can always be

replaced by a query that uses the or operator. For example,

the preceding query is equivalent to the following:

select distinct firstName || ' ' || lastName as "Name" from Faculty, Subject, Course, TaughtBy where Faculty.deptId = 'CS' or (Faculty.ssn = TaughtBy.ssn and TaughtBy.courseId = Course.courseId and Course.subjectId = Subject.subjectId and Subject.deptId = 'CS');

Example 5.12: Display all faculty in the CS department who

currently teach a CS course. You can solve the query using

the intersect operator. The result is the intersection of

all CS faculty and all faculty who teach a CS course. The

query result is shown in Figure 5.14.

select firstName || ' ' || lastName as "Name" from Faculty where deptId = 'CS' intersect select firstName || ' ' || lastName as "Name" from Faculty, TaughtBy, Course, Subject where Faculty.ssn = TaughtBy.ssn and TaughtBy.courseId = Course.courseId and Course.subjectId = Subject.subjectId and Subject.deptId = 'CS';

Figure 5.

Example 5.12 demonstrates the intersect operator.

The query that uses the intersect operator can always be

replaced by a query that uses the and operator. For example,

the preceding query is equivalent to the following:

select distinct firstName || ' ' || lastName as "Name"

TaughtBy.courseId = Course.courseId and Course.subjectId = Subject.subjectId and Subject.deptId = 'CS' and not (Faculty.deptId = 'CS');

5.8 Using Queries in the create table, insert, update, and

delete Statements

The SQL select statement is the most used statement. You can

use a nested select query to create a table, insert rows,

update rows, and delete rows.

The general syntax for creating a table using a select query

is:

create table TableName as select-statement ;

For example, the following statement creates a table that

contains the CS faculty.

create table TempFaculty as select ssn, firstName || ' ' || lastName as "Name", phone, rank, email, deptId, salary from Faculty where deptId = 'CS';

The general syntax for inserting into a table using a select

query is:

insert into table TableName [ column-list ] select-statement;

To insert rows into a table, the table must already exist.

For example, the following statement inserts Math faculty

into TempFaculty.

insert into TempFaculty select ssn, firstName || ' ' || lastName, phone, rank, email, deptId, salary from Faculty where deptId = 'MATH';

The general syntax for updating rows in a table using a

select query is:

update TableName set columnName-list = (select-statement) where condition;

For example, the following statement changes the salary of

all full professors to the maximum faculty salary:

update TempFaculty set salary = (select max(salary) from TempFaculty) where rank = 'Full Professor';

The following statement changes the salary of the faculty

with the lowest to the highest:

update TempFaculty set salary = (select max(salary) from TempFaculty) where salary = (select min(salary) from TempFaculty);

The general syntax for deleting rows from a table using a

select query is:

delete [from] TableName where columnName = select-statement;

For example, the following statement deletes all CS Faculty

from TempFaculty who don’t teach any courses.

delete from TempFaculty where ssn in ( select Faculty.ssn from Faculty, Enrollment where not exists ( select courseId from TaughtBy where Faculty.ssn = TaughtBy.ssn));

5.9 Solving Queries Using Multiple Statements

Sometimes, it is difficult to get a solution for a query in

one statement. You may break a program into several

subproblems and store result for subproblems into temporary

tables.

Example 5.14: Find the students who take all the courses

taught by Professor George Franklin. This problem can be

divided into the following subproblems:

1. Find all the courseId for the courses taught by George

Franklin and save the result into a temporary table

Temp1:

create table Temp as select courseId from TaughtBy, Faculty where TaughtBy.ssn = Faculty.ssn and firstName = 'George' and lastName = 'Franklin';

2. Store all the students and the courseId into table

Temp2 for the students who take courses in Temp1.

create table Temp as select Student.ssn, Enrollment.courseId from Student, Enrollment, Temp where Student.ssn = Enrollment.ssn and Enrollment.courseId = Temp1.courseId;

3. Display the students in Temp2 whose count is the same

as the count in Temp1.

select Student.ssn, firstName, mi, lastName from Student, Temp where Student.ssn = Temp2.ssn group by Student.ssn, firstName, mi, lastName having count() = ( select count() from Temp1);

as select firstName || ' ' || lastName, title from Faculty, TaughtBy, Course where Faculty.ssn = TaughtBy.ssn and TaughtBy.courseId = Course.courseId with read only;

The following statement creates a view that lists the CS

faculty.

create or replace view CSFaculty (ssn, firstName, lastName, deptId) as select ssn, firstName, lastName, deptId from Faculty where deptId = 'CS' with check option;

The view has the with check option specified. If you attempt

to insert a row using the following statement, DBMS rejects

it because DBMS checks the condition and catches the

violation (deptId is 'MATH'). Without the with check option

specified, the row would be inserted.

insert into CSFaculty values ('111224444', 'Tim', 'Jones', 'MATH');

There are many restrictions on updating views:

  • A view cannot be updated if it contains derived

columns. The following view has a column that is a

combination of firstName and lastName. This view cannot

be updated.

create or replace view CSFaculty (ssn, name, deptId) as select ssn, firstName || ' ' || lastName, deptId from Faculty where deptId = 'CS'

  • No insertion if its base table contains a column with

the not null constraint, which is not selected in the

view.

  • No insertion if the select-statement in the view

definition has an order by clause.

  • No insertion if a view is defined on multiple tables.
  • Update and delete operations can be performed on a view

that is defined on multiple tables, provided that the

table has a primary key and other restrictions are not

violated.

NOTE: To drop a view, use the command drop view

ViewName. You can also alter a view using the

alter view command. Often it is more convenient

to use create or replace to alter a view rather

than altering it.

5.10.1 Solving Queries Using Views

Section 5.9, “Solving Queries Using Multiple Statements,”

uses temporary tables to help solve complex queries. You can

also use views instead of temporary tables. You can rewrite

Example 5.14 using views as follows:

1. Create a view Temp1 for the courses taught by George

Franklin:

create view Temp as select courseId from TaughtBy, Faculty where TaughtBy.ssn = Faculty.ssn and firstName = 'George' and lastName = 'Franklin';

2. Create a view Temp2 for the students who take courses

in Temp1.

create view Temp as select Student.ssn, Enrollment.courseId from Student, Enrollment, Temp where Student.ssn = Enrollment.ssn and Enrollment.courseId = Temp1.courseId;

3. Display the students in Temp2 whose count is the same

as the count in Temp1.

select Student.ssn, firstName, mi, lastName from Student, Temp where Student.ssn = Temp2.ssn group by Student.ssn, firstName, mi, lastName having count() = ( select count() from Temp1);

4. Drop all temporary views.

drop view Temp2; drop view Temp1;

A view is a virtual table. Its contents are dynamically

generated upon execution. The contents of the view may be

different in a transaction due to the change of the base

table. Therefore, you should be careful to watch for the

changes in the view after the base tables are changed.

Example 5.15: Exercise 4.3 defined two tables Card and

Customer. Delete the customer who has the most cards and

also delete the cards owned by that customer.

You may attempt to solve the problem as follows:

1. Create a view Temp1 that contains the ssn of the

customer and the number of the cards owned by the

customer.