SQL: Interactive Queries (2)
Prof. Weining Zhang
Aggregate Functions
Functions that take a set of tuples and
compute an aggregated value.
Five standard functions:
count, min, max, avg, sum
They ignore null values.
Find the total number, the average, minimum,
and maximum GPA of students whose age is 17.
select count(*), avg(GPA), min(GPA), max(GPA)
from Students
where Age = 17
Lecture 12 SQL: Interactive Queries (2) 2
Aggregate Functions (cont.)
Find id and name of students who take 5 or
more courses.
select SID, Name
from Students s
where 5 50
Conditions on aggregate functions are specified
in the having clause.
Select & Having may have different functions.
Lecture 12 SQL: Interactive Queries (2) 6
Order By Clause
List student names in ascending order.
select Name from Students
order by Name asc
The default is ascending order.
List students with GPA higher than 3.5, first in
descending order of GPA, and then in ascending
order of name.
select * from Students
where GPA > 3.5
order by GPA desc, Name asc
Lecture 12 SQL: Interactive Queries (2) 7
Some Complex Queries
Find the average number of CS courses a
student takes.
For non-CS major students who take more CS
courses than he does with his major courses,
and have taken at lease 2 CS courses, list their
id, name, number of CS courses, number of
major courses, sorted first in descending order
of number of CS courses, then in ascending
order of name.
Lecture 12 SQL: Interactive Queries (2) 8
Interactive SQL Summary
A query may have six clauses: select, from,
where, group by, having, order by.
Conceptual evaluation of the query:
1. Evaluate From (cross product)
2. Evaluate Where (selection)
3. Evaluate Group By (form groups)
4. Evaluate Aggregate functions on groups
5. Evaluate Having (choose groups to output)
6. Evaluate Order By (sorting)
7. Evaluate remaining Select (projection)
Lecture 12 SQL: Interactive Queries (2) 9
Interactive SQL Summary (count.)
Many ways to express a query.
Flat queries may be more efficient.
Nested queries may be easier to understand.
Duplicate elimination may be costly.
(not equal) at predicate level often gives a
wrong answer. Use set difference, not in, not
exists, etc. instead.
Need to handle null values explicitly.
DBMSs often provide many convenient
functions. But need to check the compatibility.
Lecture 12 SQL: Interactive Queries (2) 10
Expressive Power of SQL
SQL is relational complete.
Can express any relational algebraic query.
SQL is more powerful then relational algebra.
Can express aggregation, ordering,
recursion, etc.
SQL is not computational complete.
Can not do everything a general programming
language can do.
Lecture 12 SQL: Interactive Queries (2) 11
Create Table Re-visited
Can combine table creation with insertion of
tuples using a query.
create table Full-Professors
as select FID, Name, Office
from Faculty
where Rank = „Full Professor‟
Lecture 12 SQL: Interactive Queries (2) 12
Update By Queries
Relation: Top_Students (SID, Name, GPA)
Insert students with a GPA 3.8 or higher into
the Top_Students table.
insert into Top_Students
select SSN, Name, GPA
from Students where GPA >= 3.8
Delete all students who take no courses.
delete from Students where SID not in
(select SID from Enrollment)
Lecture 12 SQL: Interactive Queries (2) 13
Update Statement
For every student who takes Database I, set
the Grade to „A‟.
update Enrollment
set Grade = 'A'
where Cno in
(select Cno
from Courses
where Title = „Database I')
Lecture 12 SQL: Interactive Queries (2) 14
Truncate vs Delete *
Use delete to remove data and keep the table
storage space.
delete from Departments;
Use truncate to remove data and release table
storage space.
truncate table Departments;
Lecture 12 SQL: Interactive Queries (2) 15
Views
A view is a virtual table (as opposed to stored
base table) defined by a query, directly or
indirectly, on base tables.
create view Top_Students
as select SSN, Name, GPA
from Students
where GPA >= 3.8
A view may be defined in terms of other views.
Lecture 12 SQL: Interactive Queries (2) 16
Views (cont.)
The query in view definition is usually not
executed until the view is queried. Typically, no
data is stored for a view.
A view is queried as if it is a base table.
Find name and GPA of top students whose name
starts with a `K'.
select Name, GPA
from Top_Students
where Name like 'K%'
Lecture 12 SQL: Interactive Queries (2) 17
Query Modification
Queries on a view are translated into queries
on base tables by folding the view.
Previous query is translated first into:
select Name, GPA
from (select SSN, Name, GPA
from Students where GPA >= 3.8)
where Name like 'K%„
Then into
select Name, GPA from Students
where GPA >= 3.8 and Name like 'K%'
Lecture 12 SQL: Interactive Queries (2) 18
Why Use Views?
Data independence: keep existing application
programs from changes of base table schemas.
Access control: provide a mechanism for hiding
sensitive data from certain users.
Productivity improvement: make user queries
easier to express.
Lecture 12 SQL: Interactive Queries (2) 19
Example of Using Views
Consider following base tables and a view:
Students (SID, Name, Birthday, GPA, Phone)
Emrollment(SID, Cno, Grade)
Courses(Cno, Title, Hours, Dept)
create view Student-Course
as select SID, Name, Age(Birthday) Age, GPA,
c.Cno, Title
from Students s, Enrollment e, Courses c
where s.SID=e.SID and e.Cno = c.Cno
Lecture 12 SQL: Interactive Queries (2) 20
Example of Using Views (cont.)
Data independence: Applications using the view
are not affected if Age is stored or derived.
Access control: Phone and Birthday of students
are hidden from users.
Productivity improvement: “Find all courses
taken by a given student” is much simpler:
select Cno, Title
from Student_Course
where SID = X
Lecture 12 SQL: Interactive Queries (2) 21
Views and Updates
What should happen if a user changes the data
in the Student-Course view?
insert into Student-Course
values (1234, „Dave Hall‟, 32, 3.15, „CS334‟, „B‟)
A view can not be updated if
Contains group by and aggregate functions
Involves multiple tables
A single-table view can be updated if it
contains a key of the table
Lecture 12 SQL: Interactive Queries (2) 22
View Update Example *
Which student should be deleted?
create view Age_distribution
as select Age, count(*) TotalNo
from Students group by Age
update Age_distribution
set TotalNo = TotalNo – 1 where Age = 20
Which base relation should be changed?
delete from Student_Course
where SID = '1234'
Lecture 12 SQL: Interactive Queries (2) 23
Maintaining Materialized Views
One may want to materialize a view (i.e., run
its definition query and store the result) as is
commonly done in industry (data warehouse).
(Why?)
View Maintenance: How to maintain the
consistency between a view and its base tables,
when base tables are updated?
Incremental View Maintenance: How to
maintain a view without re-computing the entire
view?
Lecture 12 SQL: Interactive Queries (2) 24
Assertions
Constraints defined over multiple tables.
No student is allowed to take more than six
courses.
SQL> create assertion Course_Constraint
check (not exists
(select * from Students s
where 6
Conceptual Evaluation
Compute R1, ..., Rn into temporary relations
Evaluate the query
Destroy R1, ..., Rn
Can also specify schema for Ri's:
with R1(A1, A2, ..., Am) as (query), ...
Lecture 12 SQL: Interactive Queries (2) 27
Example of With
Relation: Apply(ID, Name, Location, Date)
with DL as(select ID, Date from Apply
where Location = „Dallas‟),
HO as (select ID, Date from Apply
where Location = „Houston‟)
select ID, DL.Date DLdate, HO.Date HOdate
from DL, HO where DL.ID = HO.ID
Just like "temporary view definitions".
The Ri's can be recursive or mutually recursive
- Must use keyword recursive
- Usually need to union base case & recursion
Lecture 12 SQL: Interactive Queries (2) 28
Recursion in SQL
Find Mary's ancestors from ParentOf relation.
with recursive Ancestor(Anc, Desc) as
((select Parent Anc, Child Desc from ParentOf)
union
(select A.Anc Anc, P.Child Desc
from Ancestor A, ParentOf P
where A.Desc = P.Parent))
select Anc from Ancestor where Desc = „Mary‟
Ancestor = ParentOf
Repeat Ancestor = Ancestor joins ParentOf
Until no more changes to Ancestor
Lecture 12 SQL: Interactive Queries (2) 29
Restrictions & Features
Only support “linear recursion”: each from-
clause can have at most one recursively defined
relation.
With relations can be defined as views. Not
evaluated until being queried (Why useful?).
Can define “mutual recursion”: two recursive
relations mutually define each other.
Lecture 12 SQL: Interactive Queries (2) 30
Commit and Rollback
Changes to data in a user session may not be
visible to other users immediately (why?)
Use commit to make changes made by insert,
delete and update permanent and visible to
other users.
Use rollback to undo uncommitted changes
made by insert, delete and update.
Normal exit performs a commit.
Abnormal exit performs a rollback.
Used for transaction processing (more later).
Lecture 12 SQL: Interactive Queries (2) 31
Grant Statement
The owner of a table can grant privileges of
access to the table to other users.
Syntax: grant {all | privilege {, privilege} on
table_name | view_name
to {public | user_name {, user_name} }
[with grant option]
Privileges: select | delete | insert |
update [column_name {, column_name ...}] |
references [column_name {, column_name ...}]
Lecture 12 SQL: Interactive Queries (2) 32
Sample Grant Statements
Grant select and insert access to Students
table to users john and terry.
grant select, insert on Students to john, terry
Grant all privileges to user john.
grant all on Students to john
Allow all user to update Age and GPA.
grant update (Age, GPA) on Students to public
Allow user john to create a foreign key to
referencing SID.
grant references (SID) on Students to john
Lecture 12 SQL: Interactive Queries (2) 33
Features of Grant
The owner of a table has all privileges.
Public includes current and future users.
If columns are not named, all current and
future columns are implied.
To grant privileges on a view, one must be the
owner of the view and have the privileges on all
base tables used to define the view.
With grant option allows the grantee to grant
the privileges transitively.
Lecture 12 SQL: Interactive Queries (2) 34
Roles
A role is a named group of privileges that can
be granted to users.
Used to ease the task of granting privileges.
create role TA;
grant create table, create view to TA;
grant TA to Wang, Johnson;
Lecture 12 SQL: Interactive Queries (2) 35
Create External Schema *
Create a view and grant privileges to a group of
intended users.
Allow John the select and insert access only to
SID, Name and Age of students with GPA
higher than 3.8.
create view Stud1
as select SID, Name, Age
from Students where GPA > 3.8
grant select, insert on Stud1 to john
Lecture 12 SQL: Interactive Queries (2) 36
Revoke Statement
Revoke granted privileges on DB objects.
Syntax:
revoke {all | privilege {, privilege ...} }
on table_name | view_name
from {public | user_name {, user_name ...}}
Owner's privileges can not be revoked.
Revoke all privileges of John on Students.
revoke all on Students from john
Lecture 12 SQL: Interactive Queries (2) 37
Sequence in Oracle SQL
An Oracle object for generating a sequence of
integer values. Often used to generate unique
key values.
Syntax of create sequence:
create sequence sequence_name
[increment by integer]
[start with integer]
[maxvalue integer | nomaxvalue]
[minvalue integer | nominvalue]
[cycle | nocycle]
Lecture 12 SQL: Interactive Queries (2) 38
Sample Sequences
create sequence emp_seq start with 1000;
create sequence even_seq
increment by 2 start with 2 maxvalue 2000;
create sequence negative_seq
increment by -1 start with -1;
Default increment value is 1.
Default start value for positive (negative)
increment value is minvalue = 1 (maxvalue).
With cycle specified, the integers between
minvalue and maxvalue will be recycled
Lecture 12 SQL: Interactive Queries (2) 39
Use Sequences
Two functions for sequence seq:
seq.currval returns the current value of seq.
seq.nextval returns the next value of seq.
insert into Employees(Emp_no, Name,Age)
values (emp_seq.nextval, „John‟, 22);
nextval must be used at least once before using
currval.
Lecture 12 SQL: Interactive Queries (2) 40
Oracle SQL*Loader
sqlldr (SQL*Loader) is a Unix command (in
CSLab) to load data into an Oracle table from a
Unix text file.
Requires two files:
control file (with extension .ctl)
data file (with extension .dat)
Usage: sqlldr userid/passwd control=foo.ctl
Other options: direct (direct load), skip (skip
n lines), load (load m lines)
Will generate .log .bad files.
Lecture 12 SQL: Interactive Queries (2) 41
A Sample Control File
The control file is pub.ctl for Publishers table.
load data
infile 'pub.dat‟ into table publishers
fields terminated by ","
(pub_id, pub_name, city, state)
It expects an empty table.
Other options: append into, replace into.
Lecture 12 SQL: Interactive Queries (2) 42
A Sample Data File
The data file is pub.dat.
0736,New Age Books,Boston,MA
0877,Binnet & Hardley,Washington,DC
1111,stone Age BooAo,Boston,MA
1389,Algodata Infosystems,Berkeley,CA
2222,Harley % adkfj,Wash,DC
3333,adfadh adfhj,Berkey,CA
Other formats of control and data files are
also supported.
Lecture 12 SQL: Interactive Queries (2) 43
Look Ahead
Next topic: More advanced features of SQL
Read textbook:
Oracle8 book: Chapter 3.
Lecture 12 SQL: Interactive Queries (2) 44