1 / 9

# Chapter 8 Database Redesign - PowerPoint PPT Presentation

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

I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.

## PowerPoint Slideshow about 'Chapter 8 Database Redesign' - buckminster-church

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 8Database Redesign

We only discuss complex sub-queries

• 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

• 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 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

• 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

);

• 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

);

• 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)

);

• 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

);

• 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

);