TEACHER
-- 1、 查询Student表中的所有记录的Sname、Ssex和Class列。
-- select * from STUDENG limit 1;
SELECT SNAME,SSEX,CLASS FROM STUDENG
-- 2、 查询教师所有的单位即不重复的Depart列。
SELECT DISTINCT DEPART FROM TEACHER
-- 3、 查询Student表的所有记录。
SELECT * FROM STUDENG
-- 4、 查询Score表中成绩在60到80之间的所有记录。
SELECT * FROM SCORE WHERE DEGREE BETWEEN 60 AND 80
-- 5、 查询Score表中成绩为85,86或88的记录。
select * FROM SCORE WHERE DEGREE IN (85,86,88)
SELECT * FROM SCORE WHERE DEGREE =85 OR DEGREE =86 OR DEGREE =88
-- 6、 查询Student表中“95031”班或性别为“⼥”的同学记录。
SELECT * FROM STUDENG WHERE CLASS='95033' OR SSEX='⼥'
-- 7、 以Class降序查询Student表的所有记录。
select * from STUDENG ORDER BY Class desc
-- 8、 以Cno升序、Degree降序查询Score表的所有记录。
select * from SCORE ORDER BY CNO asc, DEGREE DESC;
SELECT * FROM SCORE ORDER BY 2,3 DESC
-- 9、 查询“95031”班的学⽣⼈数。
-- SELECT COUNT(*) FROM STUDENG WHERE CLASS='95031'
SELECT COUNT(*)AS '学⽣⼈数' FROM STUDENG WHERE CLASS =95031
-- 10、查询Score表中的最⾼分的学⽣学号和课程号。SELECT
SELECT * FROM SCORE ORDER BY DEGREE DESC LIMIT 1
-- 11、查询‘3-105’号课程的平均分。
SELECT ROUND(AVG(DEGREE),2) AS'3-105课程平均分' FROM SCORE WHERE CNO='3-105'
-- 12、查询Score表中⾄少有5名学⽣选修的并以3开头的课程的平均分数。
SELECT CNO,AVG(DEGREE)FROM SCORE WHERE CNO LIKE '3%'GROUP BY CNO HAVING COUNT(*)>5
-- 13、查询最低分⼤于70,最⾼分⼩于90的Sno列。
SELECT SNO FROM SCORE GROUP BY SNO HAVING MIN(DEGREE)>70 AND
MAX(DEGREE)<90