SQL数据库的案例分析

小编给大家分享一下SQL数据库的案例分析，相信大部分人都还不怎么了解，因此分享这篇文章给大家参考一下，希望大家阅读完这篇文章后大有收获，下面让我们一起去了解一下吧！

数据表

/*Navicat SQLite Data TransferSource Server         : schoolSource Server Version : 30808Source Host           : :0Target Server Type    : SQLiteTarget Server Version : 30808File Encoding         : 65001Date: 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
2、查询平均成绩大于60分的同学的学号和平均成绩
select userid,avg(score) from Markgroup by userid having avg(score)>60;
3、查询所有同学的学号、姓名、选课数、总成绩
select s.userid ,s.username ,count_courseid as 选课数, sum_score  as 总成绩from Student sleft join (select userid,count(courseid ) as count_courseid,sum(score) as sum_score from Mark group by userid  )scon s.userid = sc.userid;
4、查询姓'李'的老师的个数：
select count(teachername )from Teacher where teachername  like '张%';
5、检索语文课程分数小于60，按分数降序排列的同学学号：
select userid ,scorefrom Markwhere courseid ='3001'and score<60order 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 Markgroup by userid having count(8) == 2;
9、查询各个课程及相应的选修人数
select courseid ,count(*) from Course group by courseid ;
10、查询选修"张三"老师所授课程的学生中，成绩最高的学生姓名及其成绩
select Student.username ,Mark.scorefrom 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.scorefrom Course cleft join Mark on Mark.courseid  = c.courseid LEFT JOIN Student  on Student.userid  = Mark.userid where c.courseme = '数学' and Mark.score>85;
以上是"SQL数据库的案例分析"这篇文章的所有内容，感谢各位的阅读！相信大家都有了一定的了解，希望分享的内容对大家有所帮助，如果还想学习更多知识，欢迎关注行业资讯频道！