数据库作业2答案.pdfVIP

. SQL 作业： 第三章：课后习题 2 ： (1) select sno,sname from student where height1.80 (2) select cno,credit from course where semester=秋 and cno like CS%; (3) select sname,c.cno,credit,grade from student s,course c,sc where s.sno=sc.sno and c.cno=sc.cno and c.cno like CS% and sex=男and semester= 秋and grade is not null (4) select sname from student s,sc where s.sno=sc.sno and cno like EE% and sex=女 or: select sname from student s,sc where s.sno=sc.sno and cno like EE% and sex=女 group by s.sno,sname having count(cno)=1 (5) select sno,count(cno),avg(grade) from sc where grade is not null group by sno (6) select cno,count(sno),max(grade),min(grade),avg(grade) from sc where grade is not null group by cno (7) select s.sno,sname from student s,sc where s.sno=sc.sno and s.sno not in(select sno from sc where grade is null) and grade is not null group by s.sno,sname having min(grade)=80 (8) select sname,sc.cno,credit from student s,course c,sc where s.sno=sc.sno and c.cno=sc.cno and grade is null (9) select sname from student s,course c,sc . . where s.sno=sc.sno and sc.cno=c.cno and credit=3 and grade70 or: select sname from student s,course c,sc where s.sno=sc.sno and sc.cno=c.cno and credit=3 group by sc.sno,sname having min(grade)70 (10) select sname,avg(grade),sum(credit) from student s,course c,sc where s.sno=sc.sno and sc.cno=c.cno and year(bdate) between 1974 and 1976 group by sc.sno,sname 习题 3： delete from Students where SNO LIKE 91% delete from SC where SNO LIKE 91% 习题 4 ： INSERT INTO Students(SNO,SNAME,SEX,BDATE,HEIGHT) VALUES (9409101, 何平 ,女,1977-03-02,1.62) INSERT INTO Students(SNO,SNAME,SEX,BDATE,HEIGHT) VALUES (9408130, 向阳,男,1976-12-11,1.75) 习题 5： UPDATE Courses SET CREDIT=3,LHOUR=60