mysql 练习1

course表

 

 

grade表

 

 

score表

 

 

student表

 

 

teacher表

 

 

#1. 查询Student表中的所有记录的Sname、Ssex和Class列。
SELECT sname,ssex,class FROM student;
#2.查询教师所有的单位即不重复的Depart列。
SELECT tname,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 in (85,86,88);
#6.查询Student表中“95031”班或性别为“女”的同学记录。
SELECT * FROM student WHERE class in (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(1) FROM student WHERE class='95031';
#10. 查询Score表中的最高分的学生学号和课程号。（子查询或者排序）
SELECT sno,cno FROM score WHERE degree in (SELECT MIN(degree) FROM score);
#11. 查询每门课的平均成绩。
SELECT class,AVG(degree) FROM score,student WHERE score.sno=student.sno GROUP BY class;
#12. 查询Score表中至少有5名学生选修的并以3开头的课程的平均分数。
SELECT sname,AVG(degree) FROM student s LEFT JOIN score sco ON sco.cno LIKE '3%' AND s.sno=sco.sno GROUP BY sname;
#13.查询分数大于70，小于90的Sno列。
SELECT DISTINCT sno FROM score sco WHERE degree<90 AND degree>70;
#13. 查询所有学生的Sname、Cno和Degree列。
SELECT sname,cno,degree FROM student s,score sco WHERE s.sno=sco.sno;
#15.查询所有学生的Sno、Cname和Degree列。
SELECT sno,cname,degree FROM score sco,course c WHERE sco.cno=c.cno;
#16.查询所有学生的Sname、Cname和Degree列。
SELECT sname,cname,degree FROM student s,score sco,teacher t,course c WHERE s.sno=sco.sno AND sco.cno=c.cno AND t.tno=c.tno;
#17. 查询“95033”班学生的平均分。
SELECT class,avg(degree) FROM score sco,student s WHERE class=95033 AND sco.sno=s.sno;
#18. 假设使用如下命令建立了一个grade表：
#create table grade(low  int(3),upp  int(3),rank  char(1))
#insert into grade values(90,100,’A’)
#insert into grade values(80,89,’B’)
#insert into grade values(70,79,’C’)
#insert into grade values(60,69,’D’)
#insert into grade values(0,59,’E’)
#现查询所有同学的Sno、Cno和rank列。
SELECT sno,cno,rank FROM score s LEFT JOIN grade g ON s.degree BETWEEN g.low AND g.upp;
#19. 查询选修“3-105”课程的成绩高于“109”号同学成绩的所有同学的记录。
SELECT * FROM score WHERE degree>(SELECT degree FROM score WHERE score.sno=109 AND cno='3-105') AND cno='3-105';
#20. 查询score中选学多门课程的同学中分数为非最高分成绩的记录。
#最高分为所有学生的最高分
SELECT sno,degree FROM score WHERE degree <> (SELECT MAX(degree) FROM score);
#21.查询成绩高于学号为“109”、课程号为“3-105”的成绩的所有记录。
SELECT * FROM score WHERE degree>(SELECT degree FROM score WHERE score.sno=109 AND cno='3-105') AND cno='3-105';
#22.查询和学号为105的同学同年出生的所有学生的Sno、Sname和Sbirthday列。
SELECT sno,sname,sbirthday FROM student WHERE year(sbirthday)=(SELECT year(sbirthday) FROM student WHERE sno=105);
#23.查询“张旭“教师任课的学生成绩。
SELECT sno,degree FROM score sco WHERE cno=(SELECT cno FROM teacher t,course c WHERE tname='张旭' AND t.tno=c.tno);
#24.查询选修某课程的同学人数多于5人的教师姓名。
SELECT tname FROM 
(SELECT tname,cno FROM teacher t,course c WHERE t.tno=c.tno) x,
(SELECT cno,COUNT(1) FROM score GROUP BY cno HAVING COUNT(cno)>5) y 
WHERE x.cno=y.cno;

#25.查询95033班和95031班全体学生的记录。
SELECT * FROM student WHERE class in (95031,95033);
#26.查询存在有85分以上成绩的课程Cno.
SELECT cno FROM score WHERE degree>85 GROUP BY cno;
#27.查询出“计算机系“教师所教课程的成绩表。
SELECT DISTINCT * FROM score WHERE cno in (SELECT cno FROM course WHERE tno in (SELECT tno FROM teacher WHERE depart='计算机系'));
#28.查询“计算 机系”与“电子工程系“不同职称的教师的Tname和Prof。
SELECT tname,prof FROM teacher WHERE depart='计算机系' AND prof NOT in (SELECT prof FROM teacher WHERE depart='电子工程系');
#29. 查询选修编号为“3-105“课程且成绩至少高于选修编号为“3-245”的同学的Cno、Sno和Degree,并按Degree从高到低次序排序。
#至少高于其中一个
SELECT cno,sno,degree FROM score WHERE degree>ANY(SELECT degree FROM score WHERE cno='3-245') AND cno='3-105' ORDER BY degree DESC;
#30.查询选修编号为“3-105”且成绩高于选修编号为“3-245”课程的同学的
SELECT * FROM score WHERE cno='3-105' AND degree>ALL(SELECT degree FROM score WHERE cno='3-245');
#31.查询所有教师和同学的name、sex和birthday.
SELECT sname,ssex,sbirthday FROM student UNION SELECT tname,tsex,tbirthday FROM teacher;
#32.查询所有“女”教师和“女”同学的name、sex和birthday.
SELECT sname,ssex,sbirthday FROM student WHERE ssex='女' UNION SELECT tname,tsex,tbirthday FROM teacher WHERE tsex='女';
#33.查询成绩比该课程平均成绩低的同学的成绩表。
SELECT * FROM score s WHERE degree<(SELECT avg(degree) FROM score sco WHERE s.cno=sco.cno);
#34.查询所有任课教师的Tname和Depart.
SELECT tname,depart FROM teacher t WHERE EXISTS (SELECT * FROM course c WHERE t.tno=c.tno);
#35. 查询所有未讲课的教师的Tname和Depart. 
SELECT tname,depart FROM teacher t 
WHERE NOT EXISTS (
SELECT * FROM course c 
WHERE t.tno=c.tno);
#36.查询至少有2名男生的班号。
SELECT class FROM student WHERE ssex='男' GROUP BY class HAVING COUNT(1)>2
#37.查询Student表中不姓“王”的同学记录。
SELECT * FROM student WHERE sname not LIKE '王%';
#38.查询Student表中每个学生的姓名和年龄。
SELECT sname 姓名,(YEAR(SYSDATE())-YEAR(sbirthday)) 年龄 FROM student;
#39.查询Student表中最大和最小的Sbirthday日期值。
SELECT sname,sbirthday 最大 FROM student WHERE sbirthday=(SELECT MIN(sbirthday) FROM student);
SELECT sname,sbirthday 最小 FROM student WHERE sbirthday=(SELECT MAX(sbirthday) FROM student);
#40.以班号和年龄从大到小的顺序查询Student表中的全部记录。
SELECT class,sname,sbirthday FROM student ORDER BY class desc,sbirthday;
#41.查询“男”教师及其所上的课程。
SELECT t.tname,c.cname FROM teacher t,course c WHERE t.tno=c.tno AND t.tsex='男';
#42.查询最高分同学的Sno、Cno和Degree列。
SELECT * FROM score WHERE degree=(SELECT MAX(degree) FROM score);
#43.查询和“李军”同性别的所有同学的Sname.
SELECT sname FROM student WHERE ssex=(SELECT ssex FROM student WHERE sname='李军');
#44.查询和“李军”同性别并同班的同学Sname.
SELECT sname FROM student WHERE ssex=(SELECT ssex FROM student WHERE sname='李军') AND class=(SELECT class FROM student WHERE sname='李军');
#45.查询所有选修“计算机原理”课程的“男”同学的成绩表。
SELECT * FROM score WHERE sno in (SELECT sno FROM student WHERE ssex='男') AND cno=(SELECT cno FROM course WHERE cname='计算机原理');