Chapter 8 database redesign
This presentation is the property of its rightful owner.
Sponsored Links
1 / 9

Chapter 8 Database Redesign PowerPoint PPT Presentation


  • 70 Views
  • Uploaded on
  • Presentation posted in: General

Chapter 8 Database Redesign. We only discuss complex sub-queries. Referring to itself. Find the student names used by more than one student First try Select name From S Group By Name Having count(*) > 1 Second Try Select s.name Form S, S as S2

Download Presentation

Chapter 8 Database Redesign

An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -

Presentation Transcript


Chapter 8 database redesign

Chapter 8Database Redesign

We only discuss complex sub-queries


Referring to itself

Referring to itself

  • Find the student names used by more than one student

    • First try

      Select name

      From S

      Group By Name

      Having count(*) > 1

    • Second Try

      Select s.name

      Form S, S as S2

      Where s.name = s2.name and s.sid <> s2.SID


Referring to itself 2

Referring to itself (2)

  • Find the pair of SIDs using the same name

    Select S.Name, S.SID, S2.SID

    Form S, S as S2

    Where s.name = s2.name and s.sid < s2.SID

Note: you cannot use subquery or “Group By” to do this one


Exists and not exists

EXISTS and NOT EXISTS

  • EXISTS and NOT EXISTS are specialized forms of subqueries

    • An EXISTS condition is true if any row in the subquery meets the specified conditions

    • A NOT EXISTS condition is true only if all rows in the subquery do not meet the specified condition

  • Double use of NOT EXISTS can be used to find rows that have some specified condition to every row of a table


Example exists

Example: EXISTS

  • List the names of students who take at least one class

    SELECT Name

    FROM S

    WHERE EXISTS

    (SELECT *

    FROM E

    WHERE SID =StudentNumber

    );

  • What is the difference?

    SELECT Name

    FROM S

    WHERE SID in

    (SELECT StudentNumber

    FROM E

    );


Example not exists

Example: NOT EXISTS

  • List the names of students who take no class

    SELECT Name

    FROM S

    WHERE NOT EXISTS

    (SELECT *

    FROM E

    WHERE SID =StudentNumber

    );

  • What is the difference?

    SELECT Name

    FROM S

    WHERE SID NOT in

    (SELECT StudentNumber

    FROM E

    );


Example not exists all

Example: NOT EXISTS -- ALL

  • List the names of students who take all classes

    SELECT Name

    FROM S

    WHERE SID in

    (SELECT StudentNumber

    FROM E

    group by student number

    Having count(*) = (select count(*) from C)

    );


Example not exists all 2

Example: NOT EXISTS – ALL (2)

  • List the names of students who take all classes

  • List the names of students for whom there does not exist a class the students are not taking

    SELECT Name

    FROM S

    WHERE Not Exists

    (SELECT *

    FROM C

    where not exists

    (select *

    from E

    where c.name = className and Sid = studentnumber

    );


Example not exists all 3

Example: NOT EXISTS – ALL (3)

  • List the names of students who take all classes taking by PAKERs

    SELECT Name

    FROM S

    WHERE Not Exists

    (SELECT *

    FROM E as E2

    where

    studentnumber in (select SID from S where S.name = ‘Paker’)

    and not exists

    (select *

    from E

    where E2.Classname = E.className and Sid = studentnumber

    );


  • Login