2018-09-18sql练习

1、 查询Student表中的所有记录的Sname、Ssex和Class列。
SELECT sname,ssex,class
FROM students;
2、 查询教师所有的单位即不重复的Depart列。
SELECT DISTINCT depart
FROM teachers;
3、 查询Student表的所有记录。
SELECT *
FROM students;
4、 查询Score表中成绩在60到80之间的所有记录。
SELECT *
FROM scores
WHERE degree BETWEEN 60 AND 80;
5、 查询Score表中成绩为85，86或88的记录。
SELECT *
FROM scores
WHERE degree IN (85,86,88);
6、 查询Student表中“95031”班或性别为“女”的同学记录。
SELECT *
FROM students
WHERE class=95031 OR ssex='女';
7、 以Class降序查询Student表的所有记录。
SELECT *
FROM students
ORDER BY class DESC;
8、 以Cno升序、Degree降序查询Score表的所有记录。
SELECT *
FROM scores
ORDER BY cno,degree DESC;
9、 查询“95031”班的学生人数。
SELECT COUNT(1) AS StuNum
FROM students
WHERE class=95031;
10、查询Score表中的最高分的学生学号和课程号。
SELECT cno,sno
FROM scores
ORDER BY Degree DESC
LIMIT 1;
11.查询‘3-105’号课程的平均分。
SELECT AVG(degree)
FROM scores
WHERE cno='3-105';
12.查询Score表中至少有5名学生选修的并以3开头的课程的平均分数
SELECT cno,AVG(degree)
FROM scores
WHERE cno LIKE'3%'
HAVING COUNT(Sno) >= 5;
13.查询最低分大于70，最高分小于90的Sno列
SELECT sno
FROM scores
GROUP BY Sno
HAVING MAX(Degree)<90 AND MIN(Degree)>70;
14.查询所有学生的Sname、Cno和Degree列
SELECT sname,cno,degree
FROM students INNER JOIN scores
ON(students.sno=scores.sno)
ORDER BY sname;
15.查询所有学生的Sno、Cname和Degree列
SELECT sno,cname,degree
FROM scores INNER JOIN courses
ON(scores.cno=courses.cno)
ORDER BY sno;
16.查询所有学生的Sname、Cname和Degree列。
第一种:SELECT sname,cname,degree
FROM courses INNER JOIN scores
ON(courses.cno=scores.cno)
INNER JOIN students
ON(scores.sno=students.sno)
第二种:SELECT sname,cname,degree
FROM students INNER JOIN scores
ON(students.sno=scores.sno) INNER JOIN courses
ON(scores.cno=courses.cno)
ORDER BY sname;
17.查询“95033”班所选课程的平均分。
SELECT cname,AVG(degree)
FROM students INNER JOIN scores
ON(students.sno=scores.sno) INNER JOIN courses
ON(scores.cno=courses.cno)
WHERE class=95033;
19.查询成绩高于学号为“109”、课程号为“3-105”的成绩的所有记录
SELECT *
FROM scores
WHERE degree > (SELECT degree
FROM scores
WHERE sno='109' AND cno ='3-105')
20、查询score中选学一门以上课程的同学中分数为非最高分成绩的记录。
SELECT *
FROM scores
GROUP BY sno
HAVING COUNT(cno)>1 AND degree != MAX(degree);