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;

到此这篇关于sql数据库十四种案例介绍的文章就介绍到这了,更多相关sql数据库案例内容请搜索www.887551.com以前的文章或继续浏览下面的相关文章希望大家以后多多支持www.887551.com！

SQL数据库十四种案例介绍

数据表

问题：

相关推荐