-- 1、 查询Student表中的所有记录的Sname、Ssex和Class列。SELECT Sname,Ssex,Class FROM students;-- 2、 查询教师所有的单位即不重复的Depart列。SELECTDISTINCT(depart) as 系别 FROM teachers;-- 3、 查询Student表的所有记录。SELECT * FROM students;-- 4、 查询Score表中成绩在60到80之间的所有记录。SELECT * FROM scores WHERE degree BETWEEN 60AND80;
#解法二
SELECT * FROM scores WHERE degree>60AND degree<80;-- 5、 查询Score表中成绩为85,86或88的记录。SELECT * FROM scores WHERE degree in(85,86,88); #效率高
SELECT * FROM scores WHERE degree=85or degree=86or degree=88;-- 6、 查询Student表中“95031”班或性别为“女”的同学记录。SELECT * FROM students WHERE class='95031'OR ssex='女';-- 7、 以Class降序查询Student表的所有记录。SELECT * FROM students ORDERBY class DESC;-- 8、 以Cno升序、Degree降序查询Score表的所有记录。SELECT * FROM scores ORDERBY cno ASC,degree DESC;-- 9、 查询“95031”班的学生人数。SELECTCOUNT(*) as 学生人数 FROM students WHERE class='95031';-- SELECT class,COUNT(*) as 学生人数 from students GROUP BY class ;-- 10、查询Score表中的最高分的学生学号和课程号。SELECTMAX(a.degree) as 最高分,a.cno AS 课程编号,a.sno as 学号,b.sname AS 学生姓名,c.cname AS 课程名 FROM scores a,students b,courses c WHERE a.sno =b.sno AND a.cno =c.cno;-- 11、查询‘3-105’号课程的平均分。SELECTAVG(degree) AS 平均分 FROM scores WHERE cno='3-105';-- 12、查询Score表中至少有5名学生选修的并以3开头的课程的平均分数。SELECTavg(degree),cno FROM scores WHERE cno LIKE'3%'GROUPBY cno HAVINGCOUNT(sno)>=5;-- 13、查询最低分大于70,最高分小于90的Sno列。SELECT sno FROM scores GROUPBY sno HAVINGMIN(degree)>70ANDMAX(degree)<90 ;-- 14、查询所有学生的Sname、Cno和Degree列。SELECT a.sname ,b.cno,b.degree FROM students a,scores b WHERE a.sno =b.sno;SELECT Sname,Cno,Degree
FROM Students INNERJOIN Scores
ON(Students.Sno=Scores.Sno);-- 15、查询所有学生的Sno、Cname和Degree列。SELECT Sno,cname,degree FROM scores a,courses b WHERE a.cno=b.cno;SELECT Sno,cname,degree FROM scores INNERJOIN courses ON(scores.cno=courses.cno);-- 16、查询所有学生的Sname、Cname和Degree列。SELECT sname,cname,degree FROM scores a,courses b,students c WHERE a.cno=b.cno AND a.sno=c.sno;SELECT Sname,Cname,Degree
FROM Students INNERJOIN Scores
ON(Students.Sno=Scores.Sno) INNERJOIN Courses
ON(Scores.Cno=Courses.Cno)
ORDERBY Sname;-- 17、查询“95033”班所选课程的平均分。SELECT cname,AVG(degree) FROM scores INNERJOIN students ON(scores.sno=students.sno) INNERJOIN courses ON(scores.cno=courses.cno) WHERE students.class = '95033'GROUPBY courses.cno;SELECT Cname,AVG(Degree)
FROM Students INNERJOIN Scores
ON(Students.Sno=Scores.Sno) INNERJOIN Courses
ON(Scores.Cno=Courses.Cno)
WHERE Class='95033'GROUPBY Courses.Cno
ORDERBY Cname;-- 18、假设使用如下命令建立了一个grade表:-- create table grade(low NUMERIC(3,0),upp NUMERIC(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');-- commit;-- 现查询所有同学的Sno、Cno和rank列。SELECT sno,cno,rank FROM scores INNERJOIN grade ON(scores.degree>=grade.low AND scores.degree<=grade.upp);SELECT a.sno,a.cno,b.rank FROM scores a,grade b WHERE a.degree>=b.low AND a.degree<=b.upp;-- 19、查询选修“3-105”课程的成绩高于“109”号同学成绩的所有同学的记录。SELECT s1.Sno,s1.Degree
FROM Scores AS s1 INNERJOIN Scores AS s2
ON(s1.Cno=s2.Cno AND s1.Degree>s2.Degree)
WHERE s1.Cno='3-105'AND s2.Sno='109'ORDERBY s1.Sno;-- 20、查询score中选学一门以上课程的同学中分数为非最高分成绩的记录。SELECT sno,cno,degree FROM scores GROUPBY sno HAVINGCOUNT(*)>1AND degree<MAX(degree);SELECT *
FROM Scores
GROUPBY Sno
HAVINGCOUNT(cno)>1AND Degree<MAX(Degree);-- 21、查询成绩高于学号为“109”、课程号为“3-105”的成绩的所有记录。SELECT s1.Sno,s1.Degree FROM scores as s1 INNERJOIN scores AS s2 ON(s1.cno=s2.cno AND s1.degree>s2.degree)
WHERE s1.cno='3-105'AND s2.sno='109';-- 22、查询和学号为108的同学同年出生的所有学生的Sno、Sname和Sbirthday列。SELECT s1.Sno AS 学号,s1.Sname AS 姓名,s1.Sbirthday AS 生日 FROM students s1 INNERJOIN students s2 on(YEAR(s1.Sbirthday)=YEAR(s2.Sbirthday)) WHERE s2.sno='108';SELECT s1.Sno,s1.Sname,s1.Sbirthday
FROM Students AS s1 INNERJOIN Students AS s2
ON(YEAR(s1.Sbirthday)=YEAR(s2.Sbirthday))
WHERE s2.Sno='108';-- 23、查询“张旭“教师任课的学生成绩。SELECT SNO,degree FROM scores INNERJOIN courses ON(scores.cno=courses.cno) INNERJOIN teachers ON(courses.tno =teachers.tno) WHERE teachers.tname='张旭';SELECT Sno,Degree
FROM Scores INNERJOIN Courses
ON(Scores.Cno=Courses.Cno) INNERJOIN Teachers
ON(Courses.Tno=Teachers.Tno)
WHERE Teachers.Tname='张旭';-- 24、查询选修某课程的同学人数多于5人的教师姓名。SELECT teachers.tname FROM teachers INNERJOIN courses ON(teachers.tno =courses.tno) INNERJOIN scores ON (courses.cno=scores.cno) GROUPBY scores.cno HAVINGCOUNT(scores.sno)>5 ;SELECTDISTINCT Tname FROM Scores INNERJOIN Courses ON(Scores.Cno=Courses.Cno) INNERJOIN Teachers
ON(Courses.Tno=Teachers.Tno)
WHERE Courses.Cno IN(SELECT Cno FROM Scores GROUPBY(Cno) HAVINGCOUNT(Sno)>5);-- 25、查询95033班和95031班全体学生的记录。SELECT * FROM students WHERE class in('95033','95031') ORDERBY class;-- 26、查询存在有85分以上成绩的课程Cno.SELECTDISTINCT Cno
FROM Scores
WHERE Degree>85;-- 27、查询出“计算机系“教师所教课程的成绩表。SELECT scores.sno,scores.degree FROM scores INNERJOIN courses ON(scores.cno=courses.cno) INNERJOIN teachers ON(teachers.tno = courses.tno) WHERE teachers.depart='计算机系';SELECT Tname,Cname,SName,Degree
FROM Teachers INNERJOIN Courses
ON(Teachers.Tno=Courses.Tno) INNERJOIN Scores
ON(Courses.Cno=Scores.Cno) INNERJOIN Students
ON(Scores.Sno=Students.Sno)
WHERE Teachers.Depart='计算机系'ORDERBY Tname,Cname,Degree DESC;-- 28、查询“计算机系”与“电子工程系“不同职称的教师的Tname和Prof。SELECT Tname,Prof
FROM Teachers
WHERE Depart='计算机系'AND Prof NOTIN(
SELECTDISTINCT Prof
FROM Teachers
WHERE Depart='电子工程系');-- 29、查询选修编号为“3-105“课程且成绩至少高于选修编号为“3-245”的同学的Cno、Sno和Degree,并按Degree从高到低次序排序。SELECT s1.cno,s1.sno,s1.degree FROM scores s1 INNERJOIN scores s2 ON(s1.sno=s2.sno AND s1.degree>s2.degree) WHERE s1.cno='3-105'AND s2.cno='3-245' ;SELECT Cno,Sno,Degree
FROM Scores
WHERE Cno='3-105'AND Degree > ANY(
SELECT Degree
FROM Scores
WHERE Cno='3-245')
ORDERBY Degree DESC;-- 30、查询选修编号为“3-105”且成绩高于选修编号为“3-245”课程的同学的Cno、Sno和Degree.SELECT Cno,Sno,Degree
FROM Scores
WHERE Cno='3-105'AND Degree > ALL(
SELECT Degree
FROM Scores
WHERE Cno='3-245')
ORDERBY Degree DESC;-- 31、查询所有教师和同学的name、sex和birthday.SELECT
sname AS NAME,
ssex AS sex,
sbirthday AS birthday
FROM
students
UNION
(
SELECT
tname AS NAME,
tsex AS sex,
tbirthday AS birthday
FROM
teachers
);-- 32、查询所有“女”教师和“女”同学的name、sex和birthday.SELECT
sname AS NAME,
ssex AS sex,
sbirthday AS birthday
FROM
students WHERE ssex='女'UNION
(
SELECT
tname AS NAME,
tsex AS sex,
tbirthday AS birthday
FROM
teachers WHERE tsex='女'
) ;-- 33、查询成绩比该课程平均成绩低的同学的成绩表。SELECT s1.*
FROM scores AS s1 INNERJOIN (
SELECT Cno ,AVG(Degree) as aDegree
FROM Scores
GROUPBY Cno) s2
ON(s1.Cno=s2.Cno AND s1.degree<s2.aDegree);SELECT s1.*
FROM Scores AS s1 INNERJOIN (
SELECT Cno,AVG(Degree) AS aDegree
FROM Scores
GROUPBY Cno) s2
ON(s1.Cno=s2.Cno AND s1.degree<s2.aDegree);-- 34、查询所有任课教师的Tname和Depart.SELECT Tname,Depart FROM teachers WHERE tno in(SELECT tno FROM courses);SELECT tname,Depart FROM teachers t INNERJOIN courses c ON(t.tno=c.tno);-- 35 查询所有未讲课的教师的Tname和Depart. SELECT Tname,Depart FROM teachers WHERE tno NOTin(SELECT tno FROM courses);-- 36、查询至少有2名男生的班号。SELECT
class,count(sno) as stuNUms
FROM
students
WHERE
ssex = '男'GROUPBY
class
HAVING
stuNUms >= 2;-- 37、查询Student表中不姓“王”的同学记录。SELECT
*
FROM
students
WHERE
sname NOTLIKE'王%';-- 38、查询Student表中每个学生的姓名和年龄。SELECT sname,YEAR(NOW())-YEAR(Sbirthday) AS Sage FROM students;-- 39、查询Student表中最大和最小的Sbirthday日期值。SELECTMIN(sbirthday),MAX(sbirthday) FROM students ;-- 40、以班号和年龄从大到小的顺序查询Student表中的全部记录。SELECT
*
FROM
students
ORDERBY
class DESC,
sbirthday ASC;-- 41、查询“男”教师及其所上的课程。SELECT
*
FROM
teachers t
INNERJOIN courses c ON (t.tno = c.tno)
WHERE
t.tsex = '男';-- 42、查询最高分同学的Sno、Cno和Degree列。SELECT * FROM scores
GROUPBY cno HAVING degree=max(degree);-- 43、查询和“李军”同性别的所有同学的Sname.SELECT
s1.sname
FROM
students s1
INNERJOIN students s2 ON (s1.ssex = s2.ssex)
WHERE
s2.sname = '李军';-- 44、查询和“李军”同性别并同班的同学Sname.SELECT
s1.sname
FROM
students s1
INNERJOIN students s2 ON (s1.ssex = s2.ssex AND s1.class=s2.class)
WHERE
s2.sname = '李军';-- 45、查询所有选修“计算机导论”课程的“男”同学的成绩表SELECT
scores.*
FROM
scores
INNERJOIN students ON (scores.sno = students.sno)
INNERJOIN courses ON (scores.cno = courses.cno)
WHERE
students.ssex = '男'AND courses.cname = '计算机导论';SELECT
*
FROM
Scores
WHERE
Sno IN (
SELECT
Sno
FROM
Students
WHERE
Ssex = '男'
)
AND Cno IN (
SELECT
Cno
FROM
Courses
WHERE
Cname = '计算机导论'
);-- 46、查询各科的前两名SELECT
sno,
cno,
degree
FROM
scores s1
WHERE
(
SELECTcount(1)
FROM
scores s2
WHERE
s2.cno = s1.cno
AND s2.degree >= s1.degree
) <= 2orderby cno;-- 47、查询总分的前三名SELECT sno,sum(degree) as total FROM scores GROUPBY sno ORDERBY total DESC LIMIT 3;