数据库实验四董文豪还有

1、实验三重点create table student1 (snum char(4)not null primary key check(snum like s0-90-90-9) sanme varchar(20),sex char(2) check (sex in (男, 女),dept varchar(30),birthday time,ephone char(12) CHECK(ephone like0-90-90-9%0-90-90-90-90-90-90-90-9)create table course1 (cnum varchar(4) primary key check(cnum

2、like c0-90-90-9),credits(2) check(credits=0and credits=0),snum char(4)foreign key referencheck(score=100andscore)create (um(4)tablesection1char(5) primary key check(um like0-90-90-90-90-9),cnum char！)(4) foreign key referencourse,pnum char(4) check(substring(pnum,1,1)=p),inserto coursevalues(c116,大,

3、6,必修课,外语系,大，同济大学)字符长度 len（sname）首字母 substring(snum,1,1)实验四 SQL 数据查询1153371 董文豪 航空航天类.1.列出不及格成绩的学生select distinct student.snum,sname from student,scwhere student.snum=sc.snum and score602.列出选修了计算机系课程的学生和.select distinct sname,YEAR(GETDATE()-YEAR(birthday )as age fromstudent,sc,course,sectionswhere st

4、udent.snum=sc.snum andum and course.dept=计算机系um and3.检索选修了数据库技术课程的学生和系别.select sname,student.dept from sc,sections,course,studentwhere student.snum=sc.snum andum and cname=数据库技术um and4列出选修了所有课程的学生。select snum,sname from studentwhere not existect * from course where not existect*fromsc,sectionswhereu

5、m and sc.snum=student.snum)umand5检索每门课程成绩都在 80 分以上的学生。select snum,sname from studentwhere snum not in (select snum from sc where score80)6检索获奖学金的学生（每门课程在 80 分以上，平均成绩在 90 分以上）。select student.snum,sname from student,scwhere sc.snum=student.snum and sc.snum not in (select snum from sc where score=907.

6、检索选修了大的学生和成绩，并按成绩从高到低排序。select student.snum,sname,score as englishscore fromstudent,sc,course,sectionswhere sc.snum=student.snum andum and cname=大order by score descum and8. 统计每门课程的选修人数，输出列名为课程号、人数。select wheregroup byum,COUNT(*) as numbers from sc,sections umumselectfrom student,scwhereum as 课程号 ,

7、COUNT (student.snum)as 人数,course,sectionsum andumandsc.snum=student.snumgroup byum9. 查询选修了数据库技术、没有选修高等数学的学生和系别。select sname,student.dept from student,sc,sections,coursewhere sc.snum=student.snum andum and cname=数据库技术 and (select snum from sc,sections,course whereum and student.snum not inumum and cn

8、ame=高等数学)and10.检索使用高等教育select cname from coursewhere textbook like %高等教育的的课程名。11. 统计所有课程的最高成绩、最低成绩、平均成绩。select cname,MAX(score) as ma,MIN(score) as minscore,AVG(score) asavgscorewhere group byfrom sc,course,sectionsum andcnameumselect cname,max(score) as 最高成绩 ,MIN (score) as 最低成绩,avg(score) as平均成绩fr

9、om course ,sc ,sectionswheregroup by cnameum andumum12. 统计每门课程的选课人数及不及格人数。ame,选课人数,不及格人数 fromselect(select cname,COUNT(*)as 选课人数 from sc,course,sectionswhereum andumgroup by cname)s left join(select cname,COUNT(*)as 不及格人数 from sc,course,sectionswhere score60 group by on或者um andum andall cname)fameselect cname,COUNT(*)as 选课人数,SUM(caseend)as 不及格人数 from sc,course,sectionswhen score60then 1 else0wheregroup by cnameum andum13.查询土木工程系、交通工程系和城市规划系的学生学号和select dept,snum,sname from studentwhere dept in(土木工程,交通工程,城市规划)。14.查询选修课数据库技术或选修了多的学生学号。select distinct snum fr