-- 新建学生表 CREATE TABLE student( sno VARCHAR(20), sname VARCHAR(10), ssex VARCHAR(10), sbirthday DATETIME, class VARCHAR(20) ) INSERT INTO student VALUES (108,'曾华','男',19770901,95033); INSERT INTO student VALUES (105,'匡明','男',19751002,95031); INSERT INTO student VALUES (107,'王丽','女',19760123,95033); INSERT INTO student VALUES (101,'李军','男',19760220,95033); INSERT INTO student VALUES (109,'王芳','女',19750210,95031); INSERT INTO student VALUES (103,'陆君','男',19740603,95031); -- 新建课程表 CREATE TABLE course ( cno VARCHAR(20), cname VARCHAR(10), tno VARCHAR(20) ) INSERT INTO course VALUES( '3-205','计算机导论',825); INSERT INTO course VALUES( '9-245','操作系统',804); INSERT INTO course VALUES( '6-166','数字电路',856); INSERT INTO course VALUES( '9-888','高等数学',831); -- 新建成绩表 CREATE TABLE score( sno VARCHAR(20), cno VARCHAR(20), degree DECIMAL(4,1) ) INSERT INTO score VALUE (103,'3-245',86); INSERT INTO score VALUE (105,'3-245',75); INSERT INTO score VALUE (109,'3-245',68); INSERT INTO score VALUE (103,'3-105',92); INSERT INTO score VALUE (105,'3-105',88); INSERT INTO score VALUE (109,'3-105',76); INSERT INTO score VALUE (101,'3-105',64); INSERT INTO score VALUE (107,'3-105',91); INSERT INTO score VALUE (108,'3-105',78); INSERT INTO score VALUE (101,'6-166',85); INSERT INTO score VALUE (107,'6-166',79); INSERT INTO score VALUE (108,'6-166',81); -- 新建教师表 CREATE TABLE teacher ( tno VARCHAR(20), tname VARCHAR(10), tsex VARCHAR(20), tbirthday DATETIME, prof VARCHAR(20), depart VARCHAR(20) ) INSERT INTO teacher VALUE (804,'李诚','男',19581202,'副教授','计算机系'); INSERT INTO teacher VALUE (856,'张旭','男',19690312,'讲师','电子工程系'); INSERT INTO teacher VALUE (825,'王萍','女',19770814,'助教','计算机系'); INSERT INTO teacher VALUE (831,'刘冰','女',19770814,'助教','电子工程系'); -- 1查询Student表中的所有记录的Sname、Ssex和Class列 SELECT sname,ssex,class FROM student; -- 2查询教师所有的单位即不重复的Depart列 SELECT DISTINCT depart FROM teacher; -- 3查询Student表的所有记录 SELECT * FROM student; -- 4查询Score表中成绩在60到80之间的所有记录 SELECT * FROM score WHERE degree>=60 AND degree<=80; -- 5查询Score表中成绩为85，86或88的记录 SELECT * FROM score WHERE degree=85 OR degree=86 OR degree=88; -- 6 查询Student表中“95031”班或性别为“女”的同学记录 SELECT * FROM student WHERE class=95031 OR ssex='女'; -- 7以Class降序查询Student表的所有记录 SELECT * FROM student ORDER BY class DESC; -- 8以Cno升序、Degree降序查询Score表的所有记录 SELECT * FROM score ORDER BY cno ASC, degree DESC; -- 9查询“95031”班的学生人数 SELECT COUNT(*) FROM student WHERE class=95031; -- 10查询每门课的平均成绩 SELECT cno,AVG(degree) FROM score GROUP BY cno; -- 11、查询Score表中至少有5名学生选修的并以3开头的课程的平均分数。 SELECT AVG(degree) FROM score WHERE cno LIKE '3%' GROUP BY cno HAVING COUNT(cno)>4 SELECT AVG(Degree) FROM score WHERE Cno IN (SELECT Cno FROM score GROUP BY Cno HAVING COUNT(*)>5) AND Cno LIKE '3%' GROUP BY Cno SELECT AVG(Degree) FROM score WHERE Cno LIKE '3%' GROUP BY Cno HAVING COUNT(*)>5 -- 12、查询分数大于70，小于90的Sno列。 SELECT sno AS '分数' FROM score WHERE degree BETWEEN 70 AND 90 -- 13、查询所有学生的Sname、Cno和Degree列。 SELECT sname,cno,degree FROM student JOIN score ON student.Sno=score.Sno -- 15、查询所有学生的Sname、Cname和Degree列。 SELECT * FROM student JOIN score ON student.`sno`=score.`sno` JOIN course ON score.`cno` = course.`cno` -- 16、查询“95033”班学生的平均分。 SELECT AVG(degree) AS '平均分'FROM score WHERE sno IN (SELECT sno FROM student WHERE class='95033') -- 17、查询和学号为108的同学同年出生的所有学生的Sno、Sname和Sbirthday列。 SELECT sno AS '编号',sname AS '姓名',sbirthday AS '出生日期' FROM student WHERE YEAR(student.`sbirthday`)=(SELECT YEAR(sbirthday) FROM student WHERE sno='108') -- 18、查询“张旭“教师任课的学生成绩（姓名）。 SELECT degree AS '分数'FROM score,teacher,course WHERE teacher.tname='张旭' AND teacher.`tno`=course.`tno` AND course.`cno`=score.`cno` SELECT tno FROM teacher WHERE tname='张旭' SELECT cno FROM course WHERE tno=856 SELECT sno,degree FROM score JOIN student ON score.`sno` WHERE cno='6-116' -- 19、查询考计算机导论的学生成绩 SELECT sno,cno,degree FROM score WHERE cno IN (SELECT cno FROM course WHERE tno IN (SELECT tno FROM teacher WHERE depart='计算机系')) -- 20、查询李诚老师教的课程名称 -- 21、教高等数学的老师是哪个系的 -- 22、查询选修某课程的同学人数多于5人的教师姓名。 SELECT tname FROM teacher WHERE tno IN (selecet tno FROM course WHERE cno IN(SELECT cno FROM score GROUP BY cno HAVING COUNT(*)>5)) -- 23、查询95033班和95031班全体学生的记录。 SELECT * FROM Student WHERE Class IN('95031','95033') -- 24、查询存在有85分以上成绩的课程Cno. SELECT DISTINCT Cno FROM Score WHERE Degree > 85 -- 25、查询出“计算机系“教师所教课程的成绩表。 SELECT * FROM Score WHERE Cno IN(SELECT Cno FROM Course WHERE Tno IN(SELECT Tno FROM Teacher WHERE depart = '计算机系')) -- 26、 查询所有教师和同学的name、sex和birthday. SELECT Sname,Ssex,Sbirthday FROM Student UNION SELECT Tname,Tsex,Tbirthday FROM Teacher -- 27、查询所有“女”教师和“女”同学的name、sex和birthday. SELECT Sname,Ssex,Sbirthday FROM Student WHERE Ssex='女' UNION SELECT Tname,Tsex,Tbirthday FROM Teacher WHERE Tsex='女' -- 28、 查询所有任课教师的Tname和Depart. SELECT tname,depart FROM teacher WHERE tname IN (SELECT DISTINCT tname FROM teacher,course,score WHERE teacher.`tno`=course.`tno` AND course.cno=score.`cno`) -- 29、查询所有未讲课的教师的Tname和Depart. SELECT Tname,depart FROM Teacher WHERE Tno IN(SELECT Tno FROM Course WHERE Cno NOT IN(SELECT Cno FROM Score)) -- 30、查询至少有2名男生的班号。 SELECT class AS '班级'班号 FROM student WHERE ssex='男' GROUP BY class HAVING COUNT(*)>1 -- 31、查询Student表中不姓“王”的同学记录。 SELECT * FROM student WHERE sname NOT LIKE (‘王%’) -- 32、查询Student表中每个学生的姓名和年龄。 SELECT Sname,YEAR(NOW())-YEAR(Sbirthday) FROM Student -- 33、查询Student表中最大和最小的Sbirthday日期值。 SELECT MAX(Sbirthday) FROM Student UNION SELECT MIN(Sbirthday) FROM Student -- 34、以班号和年龄从大到小的顺序查询Student表中的全部记录。 SELECT * FROM Student ORDER BY Class DESC,Sbirthday -- 35、查询“男”教师及其所上的课程。 SELECT * FROM Teacher JOIN Course ON Teacher.Tno = Course.Tno WHERE Teacher.Tsex='男' -- 36、查询最高分同学的Sno、Cno和Degree列。 SELECT * FROM Score WHERE Degree = (SELECT MAX(Degree) FROM Score ) -- 37、查询和“李军”同性别的所有同学的Sname. SELECT Sname FROM Student WHERE Ssex = (SELECT Ssex FROM Student WHERE Sname='李军') -- 38、查询和“李军”同性别并同班的同学Sname. SELECT Sname FROM Student WHERE Ssex = (SELECT Ssex FROM Student WHERE Sname='李军') AND Class=(SELECT Class FROM Student WHERE Sname = '李军') -- 39、查询所有选修“计算机导论”课程的“男”同学的成绩表。 SELECT * FROM Score WHERE Sno IN(SELECT Sno FROM Student WHERE Ssex='男') AND Cno IN(SELECT Cno FROM Course WHERE Cname='计算机导论')