cs4402 hk2.3 - Copy

cs4402 hk2.3 - Copy - From St,Com where St.Snum = Com.Snum...

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

View Full Document Right Arrow Icon
CS4402 Assignment 2 Shiyin He 1) Create Table Prof (Pnum char(10), Pname Char(20), City Char(25), Dept Char(10), Primary key (Pnum)); Create Table St (Snum char(10), Sname char(20), City Char(25), Dept Char(10), Degree Char(6), Major_Pnum char(10) NOT NULL, Primary key(Snum), Foreign key (Major_Pnum) References Prof On Delete cascade); Create Table Com (Snum char(10), Pnum char(10), Hours SMALLINT, Primary key (Snum, Pnum), Foreign key (Snum) References St On Delete cascade, Foreign key (Pnum) References Prof On Delete cascade, Check (Hours > 0)); 2)
Background image of page 1

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

View Full DocumentRight Arrow Icon
3) (1) select Snum, Sname from St where degree = 'Ph.D.' AND Dept ='CSC' AND Snum IN (Select Snum from Com where Pnum IN (Select Pnum From Prof Group By HOURS Having Count(*)>= 20)); (2) Select St.Snum,Sname,City, SUM(Hours)as Hours, Count(*)as No
Background image of page 2
Background image of page 3
This is the end of the preview. Sign up to access the rest of the document.

Unformatted text preview: From St,Com where St.Snum = Com.Snum and Dept = 'ECE' Group By St.Snum,City Order By Hours desc; (3) Select St.Snum, Prof.Pnum from St, Prof, Com Where St.Snum = Com.Snum AND Prof.Pnum = Com.Pnum AND St.City <> Prof.City AND St.Dept = Prof.Dept (4) select Snum from St where City = 'Baton Rouge' Union select Prof.Pnum from Com,St,Prof where Com.Snum = St.Snum AND Prof.City = 'Baton Rouge'; (5) select Pnum,Pname from Prof where City = 'Lafayette' AND 3<= ( select count(*) from Com,St,Prof where Prof.Pnum = Com.Pnum AND Prof.Dept = St.Dept AND Com.Snum = St.Snum); (6) Select Snum, Dept From St Where Major_Pnum IN(select Pnum from where Dept = ECE);...
View Full Document

Page1 / 3

cs4402 hk2.3 - Copy - From St,Com where St.Snum = Com.Snum...

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

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