# Relational Algebra - PowerPoint by gabyion

VIEWS: 64 PAGES: 36

• pg 1
```									          Lecture 6

More SQL

Aggregates, Ordering, Grouping,
Subqueries and Data Definition

1
Aggregates
Loan
Loan#   catno   Memno   LoanDate    DueDate    Fine
L0002   B0001   M0001   05/10/97   04/12/97   £62.10
L0003   B0002   M0001   05/12/97   05/03/98   £53.00
L0004   B0003   M0001   05/12/97   05/03/98   £53.00
L0006   B0004   M0002   13/12/97   13/03/98   £52.20
L0008   B0000   M0002   16/01/98   16/04/98   £48.80
L0009   B0005   M0003   18/08/99   18/11/99   £75.00
L0010   B0006   M0004   19/08/99   20/11/99   NULL

SELECT MAX(Fine) as maxfine maxfine                    SELECT Count(*)
7
£75.00                     FROM Loan;
FROM Loan;

SELECT SUM(Fine)                                       SELECT Count(Fine)
£354:10                                                6
FROM Loan;                                             FROM Loan;

SELECT AVG(Fine)
£59.02
FROM Loan;
2
Ordering
Memno Fine

SELECT Memno, Fine           M0001 £53.00
M0001 £53.00
M0001 £62.10
FROM Loan                    M0002 £48.80
M0002 £52.20
M0003 £75.00
ORDER BY Memno, Fine;

Memno Fine

SELECT Memno, Fine                  M0001 £62.10
M0001 £53.00

FROM Loan                           M0001 £53.00
M0002 £52.20
M0002 £48.80
ORDER BY Memno, Fine DESC;          M0003 £75.00

3
Grouping
Loan#   Book#   Memno
L0002   B0001   M0001
L0003   B0002   M0001   How many loans does each
L0004   B0003   M0001
member have?
L0006   B0004   M0002
L0008   B0000   M0002

SELECT Memno, COUNT(*) AS num_loans
FROM Loan
GROUP BY Memno;
Memno num_loans
M0001 3
M0002 2

4
Grouping
memno   catno    fine
M0001   B0002   £53.00      What is the total fine paid by
M0001   B0003   £53.00      each member?
M0002   B0004   £52.20
M0003   B0005   £75.00

SELECT memno, SUM(fine) AS total_fine
FROM Loan
GROUP BY memno ;
memno    total_fine
M0001      £106.00
M0002      £52.20
M0003      £75.00

5
memno catno    fine
M0001 B0002   £53.00   The Select attributes can only contain the
M0001 B0003   £53.00   attribute grouped on + aggregate functions
M0002 B0004   £52.20
M0003 B0005   £75.00

SELECT memno, sum(fine) as memfine, catno
FROM Loans
GROUP BY memno                         memno     memfine   Catno
M0001    £106.00   B002, B003
M0002    £52.20    B004
M0003    £75.00    B005

6
Condition on the group - Having
memno catno    fine
M0001 B0002   £53.00   What is the total fine paid by each member?
M0001 B0003   £53.00   Only display members with total fine > £100.
M0002 B0004   £52.20
M0003 B0005   £75.00

SELECT memno, sum(fine) as memfine
FROM Loans
GROUP BY memno
HAVING sum(fine) > 100 ;         memno             memfine
M0001    £106.00

7
Subqueries

8
Subqueries
Consider the following tables from which we will do subqueries:
Books
catno   title                 author     publisher   category
C100    Physics Handbook      Jones      Wiley       Physics
C200    Simply the Best       Advacaat   Rangers     Football
C300    Database Design       Wilson     McCall      Computing
C500    The Metro             Abbey      Wiley       Leisure
C600    Graphics              Sedge      Maxwell     Computing
C700    Cell Biology          Norton     West        Biology

Loans                                                   Members
catno   memno      borrowed   date_ret   fine           memno     name    address    age
C100    M100       12/09/01   20/09/01   NULL           M100      Fred    Aberdeen   22
C300    M100       01/09/01   NULL       NULL           M150      Colin   Stirling   31
C400    M200       04/06/01   16/09/01   £16.30         M200      Dave    Dundee     21
C500    M200       04/08/01   16/09/01   £16.30         M250      Betty   Aberdeen   67
C600    M250       02/10/01   24/10/01   £30.00         M300      Jean    Dundee     17
C700    M300       10/09/01   19/10/01   NULL

9
Subqueries
Let‟s say you wanted the names of all members who have borrowed a
business or a computing book - a possible query is as follows:
SELECT name
FROM Books, Members, Loans
WHERE Books.catno = Loans.catno AND Members.memno = Loans.memno

The problem here is that the join in the query (i.e Book.catno = Loan.catno AND
Member.memno = Loan.memno) creates the intermediate table as shown below:

catno   title             author publisher   category    catno   memno   borrowed     date_ret    fine      memno   name    address    age
C100    Physics Handbook Jones Wiley         Physics     C100     M100    12/09/01    20/09/01    NULL      M100    Fred    Aberdeen   22
C300    Database Design Wilson McCall        Computing   C300     M100    01/09/01    NULL        NULL      M100    Fred    Aberdeen    22
C400    Business Society Neal Wiley          Business    C400    M200     04/06/01    16/09/01    £16.30    M200    Dave    Dundee      21
C500    The Metro        Abbey Wiley         Leisure     C500    M200     04/08/01    16/09/01    £16.30    M200    Dave    Dundee     21
C600    Graphics         Sedge Maxwell       Computing   C600    M250      02/10/01    24/10/01    £30.00   M250    Betty   Aberdeen    67
C700    Cell Biology     Norton West         Biology     C700    M300     10/09/01    19/10/01    NULL      M300    Jean     Dundee    17

With more loans the above table can become huge - this is inefficient
- better to use subqueries                                                                                                               10
Subqueries
• Subqueries are SELECT statements embedded
within another SELECT statement
– the results of the inner SELECT statement (or
subselect) are used in the outer statement to help
determine the contents of the final result
• inner to outer means evaluating statements from right to
left
• a subselect can be used in the WHERE and HAVING
clauses of an outer SELECT statement

11
Subqueries
• Subqueries can be used with a number of
operators:
–   Relational operators (=, <, >, <=, >=, < >)
–   IN, NOT IN
–   ALL
–   SOME, ANY
–   EXISTS, NOT EXISTS

12
Relational and Aggregate Operators
Relational Operators (=, <, >, <=, >=, <>) can only be used if the result of
the subquery returns a single value i.e subquery must be a scalar subquery.
In general, relational operators are used in conjunction with aggregate
operators i.e sum, avg, count, max, min
EXAMPLE
What is the name of the oldest Member
SELECT name
FROM Members
WHERE age = (SELECT MAX(age)
FROM Members);
Which equates to:
SELECT name
FROM Members
WHERE age = 67;
Scalar subquery returns 67   SELECT MAX(age)
13
FROM Members;
IN and NOT IN Operators
Let us again say you wanted the names of all members who have
borrowed a business or a computing book - a possible solution using
subqueries and the IN operator

SELECT name                                     Works backwards
FROM Members                                    i.e from inner to
WHERE memno IN (SELECT memno                    outer statements
FROM Loans
WHERE catno IN (SELECT catno
FROM Books

14
IN and NOT IN Operators
The previous query works as follows:

SELECT name
FROM Members
WHERE memno IN {M100, M200, M250};

Table subquery returns
{M100, M200, M250}
SELECT memno
FROM Loan
WHERE catno IN {C300, C400, C600};
Table subquery returns
{C300,C400,C600}
SELECT catno
FROM Book

15
ALL Operator
The ALL operator may be used with subqueries that produce a single
column of numbers.
If the subquery is preceded by the keyword ALL, the condition will
only be TRUE if it is satisfied by all the values produced by the subquery

EXAMPLE
What is the name of the oldest member
SELECT name
FROM Member
WHERE age >= ALL (SELECT age FROM Member);

SELECT name                          SELECT age FROM Member;
FROM Member
WHERE age >= ALL {22, 31, 21, 67, 17};
look for the rows in Members whose age
is greater than or equal to all the values in list
16
Example
Staff (staffNo, staffName, salary, branchNo*)

• What does this query do?

Select staffName, salary
From Staff
Where salary > ALL (Select salary
From Staff
Where branchNo = „B003‟);

List all staff whose salary is larger than the salary of
every member of staff at branch B003.                 17
SOME/ANY Operator
The SOME operator may be used with subqueries that produce
a single column of numbers. SOME and ANY can be used interchangeably.
If the subquery is preceded by the keyword SOME, the condition will only be
TRUE if it is satisfied by any (one or more) values produced by the subquery

EXAMPLE
List the names of members who have borrowed books (i.e., members who appear in
the Loan table)

SELECT name
FROM Member
WHERE memno = ANY (SELECT DISTINCT memno FROM Loans);

SELECT name                 SELECT DISTINCT memno FROM Loans
FROM Member
WHERE memno = ANY (“M100”, “M200”, “M250”, “M300”);
18
Example
Staff (staffNo, staffName, salary, branchNo*)

• What does this query do?

Select staffName, salary
From Staff
Where salary > ANY (Select salary
From Staff
Where branchNo = „B003‟);

List all staff whose salary is larger than the salary of
at least one member of staff at branch B003.             19
EXISTS and NOT EXISTS Operators -
Correlated Queries
EXISTS and NOT EXISTS produce a simple TRUE/FALSE result.
EXISTS is TRUE if and only if there exists at least one row in the result
table returned by the subquery; it is FALSE if the subquery returns an
empty result table. NOT EXISTS is the opposite of EXISTS

EXAMPLE

List the titles that have been borrowed by members

SELECT title
FROM Book B
WHERE EXISTS (SELECT *         The outer query iterates through all the books,
testing if each book appears in the Loan table
FROM Loan L
WHERE L.catno = B.catno);
20
Features of correlated queries

• A table in the outer query is referenced in the
WHERE field of the inner query.
• The query runs by iterating though the records
of the outer FROM table.
• The inner query is evaluated once for each
record, and passes the result back to the outer
query.

21
Some questions can be answered using joins
or queries
Member            Loan               Book
memno catno        catno         List names of members who
memno
have borrowed books on
History or Computing

SELECT Member.name
FROM Book, Member, Loan
WHERE Book.catno =Loan.catno
AND Member.memno=Loan.memno
SELECT name                       AND Book.category IN ("History", "Computing") ;
FROM Member
WHERE memno IN (SELECT memno
FROM Loan
WHERE catno IN (SELECT catno
FROM Book
WHERE category
IN (“History”, “Computing”)));
22
Equivalent ways of using Subqueries
memno catno fine
M0001 B0002 £53.00
Which member paid the smallest fine?
M0002 B0004 £52.00
M0003 B0005 £75.00
M0004 B0007 £26.00

SELECT memno, fine
FROM Loan
WHERE fine <= ALL (SELECT fine FROM Loan);

SELECT memno, fine
FROM Loan
WHERE fine = (SELECT Min(fine) FROM Loan);
23
Exercise (May 2004 Exam)
Employee(empid, name)
Project(projid, name)

•   List the number of projects that each employee
(empid) is working on.
•   List, in descending order, employee names that are
working on the project called “Databases”.
•   List the employee (empid) who spent the longest
duration working on a project.
•   List the employees (name) who have not worked on
any project.

24
Solution
Select empid, count(projid)
Group by empid;

Select E.name
From Employee E, Project P, WorkLoad L
Where E.empid=L.empid
AND L.projid=P.projid
AND P.name=‟Databases‟
Order By E.name Desc;
Select empid
Where duration = ( Select Max(duration)

Select name
From Employee E
Where Not Exists (Select *
Where E.empid = L.empid);

25
SQL (Data Definition)

26
SQL Data Types
Data Type         Declarations

Boolean           BOOLEAN
Character         CHAR           VARCHAR
Exact Numeric     NUMERIC        DECIMAL   INTEGER        ...
Approx. Numeric   FLOAT          REAL      …
Date/Time         DATE           TIME      …

27
Creating Domains and Tables

CREATE DOMAIN name
AS CHAR(12);

CREATE TABLE Dept (
deptcode         CHAR(3),
deptname       name
CHAR(12)
);

CREATE TABLE Driver (
first_name    name,
CHAR(12),
second_name    name,
CHAR(12),
age             INTEGER(2)
);

28
Creating Domains and Tables
CREATE DOMAIN student_id                   S followed by
AS CHAR(5)                           any number of
CHECK (VALUE LIKE ‘S%’);              characters
CREATE DOMAIN student_id                S followed by exactly
AS CHAR (5)                          four characters
CHECK (VALUE LIKE ‘S_ _ _ _’);

CREATE TABLE Student (
identity        student_id,
extension      integer(4)      UNIQUE,
student_name   name            NOT NULL);

Attribute name   Attribute          Constraint
domain

29
Constraints in tables
CREATE TABLE Dept (
deptcode         CHAR(3) CONSTRAINT dep_con1 PRIMARY KEY,
deptname         CHAR(12)
);
Name of the
CREATE TABLE Dept (                                 constraint
deptcode      CHAR(3),
deptname      CHAR(12),
CONSTRAINT dep_con1 PRIMARY KEY (deptcode)
);

CREATE TABLE Loan (
bookcode          CHAR(5),
memname           CHAR(15),
constraint dep_con1 PRIMARY KEY (bookcode, memcode)
);
Composite primary key must be
entered as table constraint, i.e.,
separately from the attributes

30
More Constraints in tables
CREATE TABLE Staff (
Staffcode          CHAR(4),
StaffTitle         CHAR(3) CONSTRAINT s1_con
CHECK (StaffTitle IN (‘Mr’, ‘Ms’, ‘Dr’))
);

CREATE TABLE Staff (
Staffcode     CHAR(4),
StaffTitle    CHAR(3),
CONSTRAINT s1_con CHECK (StaffTitle IN (‘Mr’, ‘Ms’, ‘Dr’))

);

31
Foreign Keys
CREATE TABLE Department (
Deptcode        CHAR(4),
Deptname        CHAR(3),
CONSTRAINT dep_con1 PRIMARY KEY (Deptcode)
);

CREATE TABLE Staff (                             Optional, if it’s the primary key
Staffcode          CHAR(4),
StaffTitle         CHAR(3),
Dept               CHAR(4) REFERENCES Department(Deptcode)
);

CREATE TABLE Staff (
Staffcode       CHAR(4),
StaffTitle      CHAR(3),
Dept            CHAR(4),
FOREIGN KEY (Dept) REFERENCES Department
);                          Can be multiple valued, to match composite
primary key

32
Link Properties: On Delete, On Update

Staff                              Dept
SID        Name DID*               DID   Name
S1         Fred D1                 D1    Art
S2         Bill D1                 D2     Computing
S3              D2
Jim NULL

On delete: Set Null

On delete: Set Default

33
Link Properties: On Delete, On Update

Staff                          Dept
SID        Name DID*           DID    Name
S1               D1
Fred D42            D1
D42   Art
S2               D1
Bill D42           D79
D2    Computing
S3               D2
Jim NULL

On update: Set Null
On delete: Set Default

34
CREATE TABLE Department (
Deptcode        CHARACTER(4),
Deptname        CHARACTER(3),
CONSTRAINT dep_con1 PRIMARY KEY (Deptcode)
);

CREATE TABLE Staff (
Staffcode       CHARACTER(4),
StaffTitle      CHARACTER(3),
FOREIGN KEY (Dept) REFERENCES Department
ON DELETE SET NULL
ON UPDATE SET NULL
);

35
Modifying tables.
• You can