Introduction to fourteen cases of SQL database

/*
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, 'Chinese', 1001);
INSERT INTO "main"."Course" VALUES (3002, 'Mathematics', 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, 'Xiao Ming', 18, 'Male');
INSERT INTO "main"."Student" VALUES (2002, 'Xiaohong', 18, 'Female');

-- ----------------------------
-- 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, 'Li Si');

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;

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

select s.userid ,s.username ,count_courseid as number of selected courses, 
sum_score as Total score 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;

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

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

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 = 'Zhang San'
);

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

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

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

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 = 'Zhang San'
and Mark.score = (
select max(score) 
from Mark sc_1 
where Mark.courseid = sc_1.courseid);

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

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

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

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 = 'Mathematics' and Mark.score>85;