SQL之SQLite数据库案例

一、数据表

/*
Navicat SQLite Data Transfer

Source Server         : ZQW
Source Server Version : 30808
Source Host           : :0

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

Date: 2021-12-23 20:44: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(20),
    teacherid int
);

-- ----------------------------
-- Records of Course
-- ----------------------------
INSERT INTO "main"."Course" VALUES (3001, '英语', 1001);
INSERT INTO "main"."Course" VALUES (3002, '数学', 1002);
INSERT INTO "main"."Course" VALUES (3003, '物理', 1003);
INSERT INTO "main"."Course" VALUES (3004, '化学', 1003);
-- ----------------------------
-- Table structure for SC
-- ----------------------------
DROP TABLE IF EXISTS "main"."SC";
CREATE TABLE SC(
    stu_id integer,
    courseid integer not null,
    score int default 0
);

-- ----------------------------
-- Records of SC
-- ----------------------------
INSERT INTO "main"."SC" VALUES (2001, 3001, 66);
INSERT INTO "main"."SC" VALUES (2001, 3002, 99);
INSERT INTO "main"."SC" VALUES (2002, 3003, 33);
INSERT INTO "main"."SC" VALUES (2003, 3004, 77);
INSERT INTO "main"."SC" VALUES (2004, 3004, 88);
INSERT INTO "main"."SC" VALUES (2002, 3002, 100);
-- ----------------------------
-- 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(
    stu_id integer  primary key autoincrement,
    stu_name varchar(20),
    stu_age int,
    stu_sex varchar(10)
);

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

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

-- ----------------------------
-- Records of Teacher
-- ----------------------------
INSERT INTO "main"."Teacher" VALUES (1001, '老王');
INSERT INTO "main"."Teacher" VALUES (1002, '老张');
INSERT INTO "main"."Teacher" VALUES (1003, '老李');

 二、操作

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

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

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

select stu_id,avg(score) from SC
group by stu_id 
having avg(score)>60;

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

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

-- 4、查询姓‘老’的老师的个数：

select count(teachername )
from Teacher 
where teachername  like '老%';

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

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

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

select stu_name  
from Student 
where stu_id  in (
    select stu_id  
    from SC,Course,Teacher 
    where Course.teacherid  = Teacher.teacherid  and SC.courseid = Course.courseid
    and Teacher.teachername ='老张'
);

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

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

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

select stu_id 
from SC
group by stu_id 
having count(8) == 2;

-- 9、查询各个课程及相应的选修人数
 
select courseid ,count(*) from Course group by courseid ;

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

select Student.stu_name ,SC.score
from SC 
left join Student  on SC.stu_id = Student.stu_id 
left join Course  on SC.courseid = Course.courseid 
left join Teacher  on Course.teacherid  = Teacher.teacherid 
where Teacher.teachername  = '老张'
and SC.score = (
select max(score) 
from SC sc_1 
where SC.courseid = sc_1.courseid);

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

select count(4) from 
(select distinct stu_id from SC)a;

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

select SC.stu_id,Student.stu_name  
from SC 
left join Student on SC.stu_id  = Student.stu_id 
where SC.courseid  = '3001' and SC.score>60;

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

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

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

select c.courseme ,Student.stu_id ,Student.stu_name ,SC.score
from Course c
left join SC on SC.courseid  = c.courseid 
LEFT JOIN Student  on Student.stu_id  = SC.stu_id 
where c.courseme = '数学' and SC.score>80;