mysql的简单练习

-- 查询学生表中不姓王的记录
SELECT *FROM student where Sname NOT LIKE '王%';
-- 查询书生表中每个学生的姓名和年龄
SELECT Sname FROM student ;
-- 查询Score表中最高分的学生学号和课程号
SELECT Sno,Cno,MAX(Degree) FROM score;
-- 查询95033班和95031班全体学生的记录
SELECT *FROM student WHERE class=95033 OR class=95031;
-- 1.查询至少有2名男生的班号。
SELECT class FROM student WHERE Ssex='男' GROUP BY Class having count(*)>1;
-- 2.查询Student表中最大和最小的Sbirthday日期值。
SELECT MAX(Sbirthday),MIN(Sbirthday) FROM student;
-- 3.查询每门课的平均成绩。
SELECT cno, AVG(degree) FROM score GROUP BY Cno;
-- 4.查询“95031”班的学生人数。
SELECT COUNT(*) FROM student WHERE  Class='95031';
-- 5.查询Score表中至少有5名学生选修的并以3开头的课程的平均分数。
-- 1. 查询所有学生的名字、科目编号和成绩。
SELECT Sname,c.Cno,degree FROM student s LEFT JOIN score sc ON s.Sno=sc.Sno LEFT JOIN course c ON c.Cno=sc.Cno;
-- 2.查询所有学生的学号、科目名称和成绩

SELECT s.Sno,Cname,degree FROM student s LEFT JOIN score sc ON s.Sno=sc.Sno LEFT JOIN course c ON c.Cno=sc.Cno;
-- 3. 查询所有学生的名字、科目名称和成绩
SELECT Sname,Cname,degree FROM student s LEFT JOIN score sc ON s.Sno=sc.Sno LEFT JOIN course c ON c.Cno=sc.Cno;
-- 4.查询“男”教师及其所上的课程。
SELECT Tname,Cname FROM teacher t INNER JOIN course c ON t.Tno=c.Tno WHERE Tsex='男';
-- 4.查询所有“女”教师和“女”同学的name、sex和birthday.
select tname,tsex,tbirthday from teacher where tsex='女'union select sname,ssex,sbirthday from student where ssex='女'
SELECT Tname ,Sname ,TSex,Ssex,Sbirthday,Tbirthday FROM student s LEFT JOIN score sc ON s.Sno=sc.Sno LEFT JOIN course c ON c.Cno=sc.Cno LEFT JOIN teacher t ON t.Tno=c.Tno;
-- 5. 查询“95033”班学生的平均分
SELECT avg(degree) FROM score sc INNER JOIN student s ON s.Sno=sc.Sno WHERE Class ='95033';
-- 6. 查询“张旭“教师任课的学生成绩
SELECT degree FROM score sc LEFT JOIN course c ON sc.Cno=c.Cno LEFT JOIN teacher t ON t.Tno=c.Tno WHERE Tname='张旭';
-- 8. 查询出“计算机系“教师所教课程的成绩表

SELECT sc.sno,sc.cno ,degree FROM score sc LEFT JOIN course c ON sc.Cno=c.Cno LEFT JOIN teacher t ON t.Tno=c.Tno WHERE Depart='计算机系';
-- 9. 查询成绩高于学号为“109”、课程号为“3-105”的成绩的所有记录。
SELECT *FROM score  WHERE Degree>ALL(SELECT Degree FROM Score WHERE Sno='109' AND Cno='3-105');

 -- 10. 查询成绩比该课程平均成绩低的同学的成绩表。

select * from score as a where a.degree<

(select AVG(degree) from score as b where a.cno = b.cno group by cno)
-- 11. 查询所有任课教师的Tname和Depart.
SELECT Tname ,Depart FROM  teacher;
-- 12. 查询所有未讲课的教师的Tname和Depart.
select tname,depart from teacher where not exists (select * from course where teacher.tno=course.tno);
-- 13查询最高分同学的Sno、Cno和Degree列。
SELECT Sno,Cno,MAX(degree) FROM score ;
-- 14. 查询和“李军”同性别的所有同学的Sname
SELECT  Sname FROM student WHERE Sname LIKE '李%';
-- 15. 查询和“李军”同性别并同班的同学Sname.
SELECT  Sname FROM student WHERE Sname LIKE '李%' AND Class='95033';
-- 16. 查询所有选修“计算机导论”课程的“男”同学的成绩表
select student.sname,score.degree from student,score,course where student.sno=score.sno and course.cno=score.cno and course.cname='计算机导论'

and student.ssex='女';

SELECT sc.sno,sc.cno ,degree FROM student s LEFT JOIN score sc ON s.Sno=sc.Sno LEFT JOIN course c ON c.Cno=sc.Cno WHERE s.Ssex='女' AND Cname='计算机导论';
-- 17查询Score表中的最高分的学生学号和课程号
SELECT Sno,cno,MAX(degree) FROM score;