数据库sql查询语句练习2_习题_结果(单世民)

现在有一教学管理系统，具体的关系模式如下：Student(no,name,sex,birthday,class)Teacher(no,name,sex,birthday,prof,depart)Course(cno,cname,tno)Score(no,cno,degree)其中表中包含如下数据：Course表：Score表：Student表：Teacher表：根据上面描述完成下面问题：（注意：注意保存脚本，尤其是DDL和DML，以便进行数据还原）DDL1.写出上述表的建表语句。命令：createtableStudent(noint,namevarchar(14),sexvarchar(2),birthdaydate,classint);createtableTeacher(noint,namevarchar(14),sexvarchar(2),birthdaydate,profvarchar(10),departvarchar(10));createtableCourse(cnovarchar(8),cnamevarchar(14),tnoint);createtableScore(noint,cnovarchar(8),degreeint);DML2.给出相应的INSERT语句来完成题中给出数据的插入。命令：insertintoStudentvalues(5001,'李勇','男','1987-7-22',95001);insertintoStudentvalues(5002,'刘晨','女','1987-11-15',95002);insertintoStudentvalues(5003,'王敏','女','1987-10-5',95001);insertintoStudentvalues(5004,'李好尚','男','1987-9-25',95003);insertintoStudentvalues(5005,'李军','男','1987-7-17',95004);insertintoStudentvalues(5006,'范新位','女','1987-6-18',95005);insertintoStudentvalues(5007,'张霞东','女','1987-8-29',95006);insertintoStudentvalues(5008,'赵薇','男','1987-6-15',95007);insertintoStudentvalues(5009,'钱民将','女','1987-6-23',95008);insertintoStudentvalues(5010,'孙俪','女','1987-9-24',95002);insertintoStudentvalues(108,'赵里','男','1987-6-15',95007);insertintoStudentvalues(109,'丘处机','男','1987-6-23',95008);insertintoStudentvalues(107,'杨康','男','1987-9-24',95001);insertintoTeachervalues(1,'李卫','男','1957-11-5','教授','电子工程系');insertintoTeachervalues(2,'刘备','男','1967-10-9','副教授','math');insertintoTeachervalues(3,'关羽','男','1977-9-20','讲师','sc');insertintoTeachervalues(4,'李修','男','1957-6-25','教授','elec');insertintoTeachervalues(5,'诸葛亮','男','1977-6-15','教授','计算机系');insertintoTeachervalues(6,'殷素素','女','1967-1-5','副教授','sc');insertintoTeachervalues(7,'周芷若','女','1947-2-23','教授','sc');insertintoTeachervalues(8,'赵云','男','1980-6-13','副教授','计算机系');insertintoTeachervalues(9,'张敏','女','1985-5-5','助教','sc');insertintoTeachervalues(10,'黄蓉','女','1967-3-22','副教授','sc');insertintoTeachervalues(11,'张三','男','1967-3-22','副教授','sc');insertintoCoursevalues('3-101','数据库',1);insertintoCoursevalues('5-102','数学',2);insertintoCoursevalues('3-103','信息系统',3);insertintoCoursevalues('3-104','操作系统',4);insertintoCoursevalues('3-105','数据结构',5);insertintoCoursevalues('3-106','数据处理',5);insertintoCoursevalues('4-107','pascal语言',6);insertintoCoursevalues('4-108','C++',7);insertintoCoursevalues('4-109','java',8);insertintoCoursevalues('3-245','数据挖掘',10);insertintoCoursevalues('3-111','软件工程',11);insertintoScorevalues(5001,'3-105',69);insertintoScorevalues(5001,'5-102',55);insertintoScorevalues(5003,'4-108',85);insertintoScorevalues(5004,'3-105',77);insertintoScorevalues(5005,'3-245',100);insertintoScorevalues(5006,'3-105',53);insertintoScorevalues(5003,'4-109',45);insertintoScorevalues(5008,'3-105',98);insertintoScorevalues(5004,'4-109',68);insertintoScorevalues(5010,'3-105',88);insertintoScorevalues(5003,'3-105',98);insertintoScorevalues(5005,'4-109',68);insertintoScorevalues(5002,'3-105',88);insertintoScorevalues(107,'3-105',98);insertintoScorevalues(108,'4-109',68);insertintoScorevalues(109,'3-105',88);insertintoScorevalues(109,'4-109',80);insertintoScorevalues(107,'3-111',88);insertintoScorevalues(5003,'3-111',80);单表查询3.以class降序输出student的所有记录（student表全部属性）命令：select*fromStudentorderbyclassdesc;4.列出教师所在的单位depart（不重复）。命令：selectdistinctdepartfromTeacher;5.列出student表中所有记录的name、sex和class列命令：selectname,sex,classfromStudent;6.输出student中不姓王的同学的姓名。命令：selectnamefromStudentexceptselectnamefromStudentwherenamelike'王%';或selectnamefromStudentwherenamenotlike'王%';7.输出成绩为85或86或88或在60-80之间的记录（no,cno,degree）命令：selectno,cno,DEGREEfromScorewheredegree=85ordegree=86ordegree=88ordegreebetween60and80;8.输出班级为95001或性别为‘女’的同学（student表全部属性）命令：select*fromStudentwhereclass=95001orsex='女';9.以cno升序、degree降序输出score的所有记录。（score表全部属性）命令：select*fromScoreorderbycnoasc,degreedesc;10.输出男生人数及这些男生分布在多少个班级中命令：selectCOUNT(*),count(distinctclass)fromStudentwheresex='男';11.列出存在有85分以上成绩的课程编号。命令：selectdistinctcnofromScorewheredegree85;12.输出95001班级的学生人数命令：selectCOUNT(*)fromStudentwhereclass=95001;13.输出‘3-105’号课程的平均分命令：selectavg(cast(degreeasfloat))fromScorewherecno='3-105';14.输出student中最大和最小的birthday日期值命令：selectMAX(birthday),MIN(birthday)fromStudent;15.显示95001和95004班全体学生的全部个人信息（不包括选课）。（student表全部属性）命令：select*fromStudentwhereclass=95001orclass=95004;聚合查询16.输出至少有5个同学选修的并以3开头的课程的课程号，课程平均分，课程最高分，课程最低分。命令：selectcno,avg(cast(degreeasfloat)),MAX(degree),MIN(degree)fromScorewherecnolike'3%'groupbycnohavingCOUNT(cno)5;或者：selectcno,AVG(cast(DEGREEasfloat)),MAX(degree),MIN(DEGREE)fromScoregroupbycnohavingCOUNT(cno)=5andcnolike'3%'17.输出所选修课程中最低分大于70分且最高分小于90分的学生学号及学生姓名命令：selectStudent.no,namefromStudentjoinScoreonStudent.no=Score.nogroupbyStudent.no,namehavingMAX(Score.degree)90andMIN(Score.degree)70;18.显示所教课程选修人数多于5人的教师姓名命令：selectnamefromTeacherjoinCourseonTeacher.no=Course.tnowhereCourse.cnoin(selectcnofromScoregroupbycnohavingCOUNT(Score.cno)5);19.输出’95001’班级所选课程的课程号和平均分命令：selectcno,avg(cast(degreeasfloat))fromScorewherenoin(selectnofromStudentwhereclass=95001)groupbycno;或