数据库例题50

最新推荐文章于 2023-10-19 20:34:03 发布

fanniefan

最新推荐文章于 2023-10-19 20:34:03 发布

阅读量922

点赞数 1

本文链接：https://blog.csdn.net/fanniefan/article/details/78984403

版权

本文提供了50道关于SQL查询的例题，涵盖了平均成绩排名、选课人数、特定条件筛选等多个方面，旨在帮助读者加深对SQL操作的理解和应用。例题包括：按平均成绩降序排列的查询、各科成绩前三名、每门课程的选修人数、只选修一门课程的学生、男女学生数量等。通过这些实例，读者可以熟悉并掌握SQL的高级查询技巧。

摘要由CSDN通过智能技术生成

SELECT *FROM sc;
SELECT *FROM teacher;
SELECT* FROM course;
SELECT* FROM student;

DROP TABLE sc;
CREATE TABLE sc(
       sno VARCHAR(10),
       cno VARCHAR(10),
       score NUMERIC(4,2),
       CONSTRAINT pk_sc PRIMARY KEY(sno,cno)
);
INSERT INTO sc VALUES
('s001','c001',78.9),
('s002','c001',80.9),
('s003','c001',81.9),
('s004','c001',60.9),
('s001','c002',82.9),
('s002','c002',72.9),
('s003','c002',81.9),
('s001','c003',59)
;
1、查询“c001”课程比“c002”课程成绩高的所有学生的学号；
-- SELECT a.sno
-- FROM sc a,sc b
-- WHERE a.sno=b.sno AND a.cno='c001' AND b.cno='c002' AND a.score > b.score;

2、查询平均成绩大于60 分的同学的学号和平均成绩；
SELECT sno ,AVG(score)
FROM sc
GROUP BY sno
HAVING AVG(score)>60;

-- 3、查询所有同学的学号、姓名、选课数、总成绩；
SELECT student.sno,student.sname,COUNT(cno),SUM(score)
FROM student LEFT JOIN sc ON student.sno=sc.sno
GROUP BY student.sno

4、查询姓“刘”的老师的个数；
-- SELECT tname ,COUNT(tname)
-- FROM teacher
-- WHERE tname LIKE '刘%';
5、查询没学过“谌燕”老师课的同学的学号、姓名；

-- SELECT sno
-- FROM sc WHERE cno IN(SELECT cno
-- FROM course WHERE tno IN (SELECT tno
-- FROM teacher WHERE tname='谌燕') );

SELECT student.sno,student.sname
FROM student WHERE student.sno not in
(SELECT DISTINCT sc.sno
FROM sc WHERE sc.cno in
(SELECT course.cno
FROM course WHERE course.tno = (
SELECT teacher.tno
FROM teacher WHERE teacher.tname='攒燕')));

6、查询学过“c001”并且也学过编号“c002”课程的同学的学号、姓名；

and
7、查询学过“谌燕”老师所教的所有课的同学的学号、姓名；

8、查询课程编号“c002”的成绩比课程编号“c001”课程低的所有同学的学号、姓名；

SELECT student.sno,student.sname
FROM student
WHERE student.sno=a.sno
(SELECT a.sno
FROM sc a, sc b
WHERE a.sno=b.sno
AND a.cno='c001'
AND b.cno='c002'
AND a.score>b.score);

9、查询所有课程成绩小于60 分的同学的学号、姓名；
SELECT student.sno,student.sname
FROM sc ,student
WHERE sc.sno=student.sno
GROUP BY cno
HAVING SUM(s