Relational Algebra - PowerPoint by gabyion

VIEWS: 64 PAGES: 36

									          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
          Warning about Grouping
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
   C400    Business Society      Neal       Wiley       Business
   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
                AND category IN (“Business”, “Computing”);


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
                       WHERE category IN („Business‟, „Computing‟)));




                                                                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
                                       WHERE category IN („Business‟, „Computing‟);

                                                                              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*)
Branch (branchNo, branchAddress)

• 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*)
  Branch (branchNo, branchAddress)

  • 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)
WorkLoad(empid*, projid*, duration)

•   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)
From WorkLoad
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
From WorkLoad
Where duration = ( Select Max(duration)
                   From WorkLoad);

                                     Select name
                                     From Employee E
                                     Where Not Exists (Select *
                                                      From WorkLoad L
                                                      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

Link properties
           On delete: Cascade
           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

Link properties

     On update: Cascade
      On update: Set Null
      On delete: Set Default


                                               34
   Setting link properties in SQL
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
   – Add a column
   – Add/Delete rows
   – Add/Drop constraints


• This is not part of the syllabus, but you can refer to
  Lab 3 for more details…




                                                     36

								
To top