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.
