sqlite数据库练习

表student
Sno|Sname|Ssex|Sage|Sdept
9512101|李勇|男|19|计算机系
9512102|刘晨|男|20|计算机系
9512103|王敏|女|20|计算机系
9522101|张立|男|22|信息系
9522102|吴宾|女|21|信息系
9522103|张海|男|20|信息系
9532101|钱小力|女|18|数学系
9532102|王大力|男|19|数学系


表course
Cno|Cname|Hourse
C01|计算机文化学|70
C02|VB|90
C03|计算机网络|80
C04|数据库基础|108
C05|高等数学|180
C06|数据结构|72




表SC
Sno|Cno|Grade
9512101|C01|90
9512101|C02|86
9512101|C06|
9512102|C02|78
9512102|C04|66
9522102|C01|82
9522102|C02|75
9522102|C04|93
9522102|C05|50
9522103|C02|68
9522103|C06|
9532101|C01|80
9532101|C05|95
9532102|C05|90


1.分别查询学生表和学生修课表中的全部数据
----->select * from student;
----->select * from course;
2.查询成绩在70到80之间的学生的学号、课程号和成绩
 ---->select Sno,Cno,Grade from SC where Grade between 70 and 80; 
3.查询C01课程成绩最高的分数
----->select max(Grade) as C01最高分 from SC where Cno = "C01"; 
4.查询学生都选修了哪些课程，要求列出课程号
----->select DISTINCT(Cno) as 被选课程号 from SC order by 被选课程号 asc; 
5.查询修了C02号课程的所有学生的平均成绩、最高成绩和最低成绩
----->select avg(Grade) as C02平均成绩, max(Grade) as C02最高成绩, min(Grade) as C02最低成绩 from SC where Cno = "C02"; 
6.统计每个系的学生人数
-----> select Sdept,count(Sno) as 系部人数 from student group by Sdept order by 系部人数 asc;
7.统计每门课程的选修人数和考试最高分
----->select Cno,count(Cno) as 选修人数, max(Grade) as 考试最高分 from Sc group by Cno;
8.统计每个学生的选修门数，并按选课门数的递增顺序显示结果
----->select Sno,count(Sno) as 选课门数 from SC group by Sno order by 选课门数 asc;
9.统计每个选修课的学生总数和考试的平均成绩
----->select Cno,count(Cno) as 学生总数,avg(grade) from SC group by Cno;
10.统计选课门数超过2门的学生的平均成绩和选课门数
-----> select Sno,count(Cno) as 选课门数, avg(Grade) from SC group by Sno having 选课门数 > 2;
11.列出总成绩超过200分的学生，要求列出学号、总成绩
----->select Sno ,sum(Grade) 总成绩 from SC group by Sno having 总成绩 > 200;
12.查询选修了C02课程的学生的姓名和所在系
----->select * from student as t1,SC as t2 where t1.Sno = t2.Sno and Cno = "C02";
13.查询成绩80分以上的学生姓名、课程号和成绩，并按成绩的降序排列结果
----->select  t1.Sname,t1.Sno,t2.Grade from student as t1, SC as t2 where t1.Sno = t2.Sno and t2.Grade > 80 order by t2.Grade desc;
14.查询计算机系男生修了"数据库基础"的学生的姓名、性别、成绩
----->select * from student as t1 where Sno in(select Sno from SC where Cno = (select Cno  from course where Cname = "数据库基础"))  and t1.Ssex = "男"; 
or
----->select t1.Sname,t1.Ssex,t3.Grade from student as t1, course as t2 ,SC as t3 where t1.Sdept = "计算机系" and t1.Sno = t3.Sno and t2.Cno = t3.Cno and t2.Cname = "数据库基础" and t1.Ssex = "男";
15.查询哪些学生的年龄相同，要求列出年龄相同的学生的姓名和年龄
----->select t1.Sname,t1.Sage from student as t1 where t1.Sage in(select Sage from student group by Sage having count(Sno) > 1) order by t1.Sage asc;
16.查询哪些课程没有人选，要求列出课程号和课程名
----->select t1.Cno,t1.Cname from course as t1 where t1.Cno not in(select DISTINCT(Cno) from SC); 
17.查询有考试成绩的所有学生的信息、修课名称及考试成绩
--要求将查询结果放在一张新的永久表（假设新表名为new_sc中)
//1.查询有考试成绩的学生的学号
----->select  DISTINCT(t1.Sno) from SC as t1 where t1.Grade is not null; 
//2.与student关联，获取学生的信息
----->select t2.Sname from student as t2 where t2.Sno in(select DISTINCT(t1.Sno) from SC as t1 where t1.Grade is not null); 
//3.先得到学生的成绩
----->select * from SC as t4,(select * from student as t2 where t2.Sno in(select DISTINCT(t1.Sno) from SC as t1 where t1.Grade is not null)) as t3 where t3.Sno = t4.Sno;
//通过Cno得到修课名称
----->select t6.Cname,t5.* from course as t6,(select t4.Grade,t4.Cno,t3.* from SC as t4,(select * from student as t2 where t2.Sno in(select DISTINCT(t1.Sno) from SC as t1 where t1.Grade is not null)) as t3 where t3.Sno = t4.Sno and t4.Grade is not null) as t5
where t6.Cno = t5.Cno;
//5.创建一张新的永久表
----->create table temp as select t6.Cname,t5.* from course as t6,(select t4.Grade,t4.Cno,t3.* from SC as t4,(select * from student as t2 where t2.Sno in(select DISTINCT(t1.Sno) from SC as t1 where t1.Grade is not null)) as t3 where t3.Sno = t4.Sno and t4.Grade is not null) as t5
where t6.Cno = t5.Cno;
18.分别查询信息系和计算机系的学生和姓名、性别、修课名称、修课成绩
--并要求将这个查询结果合并成一个结果集
--并以系名、姓名、性别、修课名称、修课成绩的序列显示各列
方法一：
//1.得到信息系和计算机系的学生信息
------>select Sno,Sname,Ssex,Sdept from student where Sdept in("信息系","计算机系") ;
//2.与SC关联，得到成绩和课程号
------>select t2.Grade,t2.Cno ,t1.* from SC as t2,(select Sno,Sname,Ssex,Sdept from student where Sdept in("信息系","计算机系")) as t1 where t1.Sno = t2.Sno ;
//与course关联，得到课程名称
------>select t3.Cno,t3.Cname,t4.* from course as t3,(select t2.Grade,t2.Cno ,t1.* from SC as t2,(select Sno,Sname,Ssex,Sdept from student where Sdept in("信息系","计算机系")) as t1 where t1.Sno = t2.Sno) as t4 where t3.Cno = t4.Cno;
19.用子查询实现如下查询：
(1)查询选修了C01课程的学生的姓名和所在系。
------>select t1.Sname,t1.Sdept from student as t1,SC as t2 where t2.Sno = t1.Sno and t2.Cno = "C01";
(2)查询数学系成绩80分以上的学生的学号、姓名
//1.得到成绩大于80分的学号
------>select DISTINCT(t1.Sno) from SC as t1  where t1.Grade > 80;
//2.关联学生表，得到姓名
------> select t2.Sno,t2.Sname from student as t2 where t2.Sno in (select DISTINCT(t1.Sno) from SC as t1  where t1.Grade > 80) and t2.Sdept = "数学系";
20.查询计算机系学生所选的课程名
------>select t1.Sname,t2.Cname,t1.Sdept from student as t1,course as t2,SC as t3 where t1.Sno = t3.Sno and t3.Cno = t2.Cno and t1.Sdept = "计算机系";
21.将计算机系成绩高于80分的学生的修课情况插入到另一张表中
------>create table SC_Info2 as select t1.*,t2.Cname,t3.Grade from student as t1,course as t2,SC as t3 where t1.Sno = t3.Sno and t3.Cno = t2.Cno and t3.Grade > 80;
22.删除修课成绩小于60分的学生的修课记录
------>delete from SC where Grade < 60;
23.将所有选修了“c01”课程的学生的成绩加10分
------>update SC set Grade = Grade+10 where Cno = "C01";