SQL数据库十四种案例介绍

数据表

/*
Navicat SQLite Data Transfer

Source Server         : school
Source Server Version : 30808
Source Host           : :0

Target Server Type    : SQLite
Target Server Version : 30808
File Encoding         : 65001

Date: 2021-12-23 16:06:04
*/

PRAGMA foreign_keys = OFF;

-- ----------------------------
-- Table structure for Course
-- ----------------------------
DROP TABLE IF EXISTS "main"."Course";
CREATE TABLE Course(
   courseid integer  primary key autoincrement,
   courseme varchar(32),
   teacherid int
);

-- ----------------------------
-- Records of Course
-- ----------------------------
INSERT INTO "main"."Course" VALUES (3001, '语文', 1001);
INSERT INTO "main"."Course" VALUES (3002, '数学', 1002);

-- ----------------------------
-- Table structure for Mark
-- ----------------------------
DROP TABLE IF EXISTS "main"."Mark";
CREATE TABLE Mark(
   userid integer,
   courseid integer not null,
   score int default 0
);

-- ----------------------------
-- Records of Mark
-- ----------------------------
INSERT INTO "main"."Mark" VALUES (2001, 3001, 89);
INSERT INTO "main"."Mark" VALUES (2001, 3002, 90);
INSERT INTO "main"."Mark" VALUES (2002, 3001, 66);
INSERT INTO "main"."Mark" VALUES (2003, 3002, 85);

-- ----------------------------
-- Table structure for sqlite_sequence
-- ----------------------------
DROP TABLE IF EXISTS "main"."sqlite_sequence";
CREATE TABLE sqlite_sequence(name,seq);

-- ----------------------------
-- Records of sqlite_sequence
-- ----------------------------
INSERT INTO "main"."sqlite_sequence" VALUES ('Teacher', 1002);
INSERT INTO "main"."sqlite_sequence" VALUES ('Student', 2002);
INSERT INTO "main"."sqlite_sequence" VALUES ('Course', 3002);

-- ----------------------------
-- Table structure for Student
-- ----------------------------
DROP TABLE IF EXISTS "main"."Student";
CREATE TABLE Student(
   userid integer  primary key autoincrement,
   username varchar(32),
   userage int,
   usersex varchar(32)
);

-- ----------------------------
-- Records of Student
-- ----------------------------
INSERT INTO "main"."Student" VALUES (2001, '小明', 18, '男');
INSERT INTO "main"."Student" VALUES (2002, '小红', 18, '女');

-- ----------------------------
-- Table structure for Teacher
-- ----------------------------
DROP TABLE IF EXISTS "main"."Teacher";
CREATE TABLE Teacher(
   teacherid integer primary key autoincrement,
   teachername varchar(32)
);

-- ----------------------------
-- Records of Teacher
-- ----------------------------
INSERT INTO "main"."Teacher" VALUES (1001, '张三');
INSERT INTO "main"."Teacher" VALUES (1002, '李四');

问题：

1、查询“语文”课程比“数学”课程成绩低的所有学生的学号

select a.userid from
(select userid,score from Mark where courseid ='3001')a,
(select userid,score from Mark where courseid ='3002')b
where a.userid = b.userid and a.score<b.score;

2、查询平均成绩大于60分的同学的学号和平均成绩

select userid,avg(score) from Mark
group by userid
having avg(score)>60;

3、查询所有同学的学号、姓名、选课数、总成绩

select s.userid ,s.username ,count_courseid as 选课数,
sum_score  as 总成绩
from Student s
left join
(select userid,count(courseid ) as count_courseid,sum(score) as sum_score
from Mark group by userid  )sc
on s.userid = sc.userid;

4、查询姓‘李'的老师的个数：

select count(teachername )
from Teacher
where teachername  like '张％';

5、检索语文课程分数小于60，按分数降序排列的同学学号：

select userid ,score
from Mark
where courseid ='3001'
and score<60
order by score desc;

6、查询学/没学过”张三”老师讲授的任一门课程的学生姓名

select username  
from Student
where userid  in (
   select userid  
   from Mark,Course,Teacher
   where Course.teacherid  = Teacher.teacherid  and Mark.courseid = Course.courseid
   and Teacher.teachername ='张三'
);

7、查询全部学生选修的课程和课程号和课程名：

select courseid ,courseme
from Course
where courseid  in (select courseid from Mark group by courseid);

8、检索选修两门课程的学生学号：

select userid
from Mark
group by userid
having count(8) == 2;

9、查询各个课程及相应的选修人数

select courseid ,count(*) from Course group by courseid ;

10、查询选修“张三”老师所授课程的学生中，成绩最高的学生姓名及其成绩

select Student.username ,Mark.score
from Mark
left join Student  on Mark.userid = Student.userid
left join Course  on Mark.courseid = Course.courseid
left join Teacher  on Course.teacherid  = Teacher.teacherid
where Teacher.teachername  = '张三'
and Mark.score = (
select max(score)
from Mark sc_1
where Mark.courseid = sc_1.courseid);

11、求选了课程的学生人数：

select count(2) from
(select distinct userid from Mark)a;

12、查询课程编号为“语文”且课程成绩在80分以上的学生的学号和姓名

select Mark.userid,Student.username  
from Mark
left join Student on Mark.userid  = Student.userid
where Mark.courseid  = '3001' and Mark.score>80;

13、查询每门课程的平均成绩，结果按平均成绩升序排序，平均成绩相同时，按课程号降序排列

select courseid ,avg(score)
from Mark
group by courseid
order by avg(score),courseid desc;

14、查询课程名称为“数学”，且分数高于85的学生名字和分数：

select c.courseme ,Student.userid ,Student.username ,Mark.score
from Course c
left join Mark on Mark.courseid  = c.courseid
LEFT JOIN Student  on Student.userid  = Mark.userid
where c.courseme = '数学' and Mark.score>85;

来源：https://blog.csdn.net/qq_34623621/article/details/122110093