Assignment 3 - Copy

Assignment 3 - Copy - PR.PNUM) = (select count(*) from CM)...

Info iconThis preview shows pages 1–2. Sign up to view the full content.

View Full Document Right Arrow Icon
Assignment 3 Shiyin he 1) 1.( select distinct ID,NAME From EM Where ID IN(select ID from EP )) Except (select distinct ID,NAME From CR Where ID IN(select ID from CR)) Select distinct ID,NAME From EM Where ID IN(select ID from EP) and ID not in(select ID from CR) 2) Select ID(select avg(GRADE)as avg_GPA from ER,EM where ER.ID =EM.ID) ,(select count(*) from ER where ER.ID = EM.ID) From ER Where ID IN (select ID from EM where CITY = ‘NEW ORLEANS’); 3) Select distinct DEPT FROM EM Where 3<=(select count(*) from EP,EM where EP.ID = EM.ID); 4) Select distinct DEPT, BUDGET, (select count(*) from EP,EM where EP.ID = EM.ID),(select count (*) from PR where PR.DEPT = CM.DEPT) AS PROJNUM From CM Order by BUDGET desc 5) Select distinct NAME From EM,PR Where EM.CITY = PR.CITY AND 20<=(select count(*) from EP where EP.ID = EM.ID) AND PR.DEPT not IN (select DEPT from EM) 6) Select Pnum From PR Where (select count(distinct EM.DEPT)from EP,EM where EM.ID=EP.ID AND EP.PNUM =
Background image of page 1

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon
Background image of page 2
This is the end of the preview. Sign up to access the rest of the document.

Unformatted text preview: PR.PNUM) = (select count(*) from CM) 2) e. select course id , sec id , count ( ID ) from section natural join takes where semester = Autumn and year = 2009 group by course id , sec id f. select max ( enrollment ) from ( select count ( ID ) as enrollment from section natural join takes where semester = Autumn and year = 2009 group by course id , sec id ) g. with sec enrollment as ( select course id , sec id , count ( ID ) as enrollment from section natural join takes where semester = Autumn and year = 2009 group by course id , sec id ) select course id , sec id from sec enrollment where enrollment = ( select max ( enrollment ) from sec enrollment )...
View Full Document

Page1 / 2

Assignment 3 - Copy - PR.PNUM) = (select count(*) from CM)...

This preview shows document pages 1 - 2. Sign up to view the full document.

View Full Document Right Arrow Icon
Ask a homework question - tutors are online