数据库初级练习

--创建学生表

create table student(
Sno int not null,
Sname varchar(10),
Sage int,
Ssex char(10),
Sdept varchar(10)
);

insert into student values
(9512101,'李勇',19,'男','计算机系')
(9512102,'刘晨',20,'男','计算机系')
(9512103,'王敏',20,'女','计算机系')
(9521101,'张立',22,'男','信息系')
(9521102,'吴斌',21,'女','信息系')
(9521103,'张海',20,'男','信息系')
(9531101,'钱小利',18,'女','数学系')
(9531102,'王大利',19,'男','数学系');

--课程表

create table course(
Cno varchar(10),
Cname varchar(10),
Hours int
)CHARACTER SET utf8 COLLATE utf8_general_ci;
insert into course
('c01','计算机文化学',70)
('c02','VB',90)
('c03','计算机网络',80)
('c04','数据库基础',108)
('c05','高等数学',180)
('c06','数据结构',72);

--选课表

create table sc(
Sno int ,
Cno varchar(10),
Grade int
)CHARACTER SET utf8 COLLATE utf8_general_ci;
insert into sc values
(9512101,'c01',90)
(9512101,'c02',86)
(9512101,'c06', )
(9512102,'c02',78)
(9512102,'c04',66)
(9512102,'c01',82)
(9521102,'c02',75)
(9521102,'c04',92)
(9521102,'c05',50)
(9521103,'c02',68)
(9521103,'c06',)
(9531101,'c01',80)
(9531101,'c05',90)
(9531102,'c05',85);

--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 Grade from sc where Cno='c01' order by Grade desc limit 1;
select max(Grade) from sc where Cno='c01' ;
--降序DESC，升序ASC limit n 取前n行

--4.查询学生都选修了哪些课程,要求列出课程号。

select Cname 课程,Cno 课程号 from course  where Cno in(select Cno from sc);

--5.查询修了C02号课程的所有学生的平均成绩、最高成绩和最低成绩。

select max(Grade) 最高, min(Grade) 最低, avg(Grade) 平均成绩 from sc where Cno='c02';

--6.统计每门课程的修课人数和考试最高分。

select Cname 课程, count(*) 选课人数, max(Grade) 最高分  from sc s, course c where s.Cno=c.Cno group by Cname;

--7.统计每个学生的选课门数,并按选课门数的递增顺序显示结果。

select Sname 姓名, count(*) 选课门数 from student s,sc  where s.Sno=sc.Sno group by Sname order by count(sc.Sno) asc;

--8.统计选修课的学生总数和考试的平均成绩。

select count(distinct Sno) 选课总人数, avg(Grade) 平均成绩 from sc;

--9.查询选课门数超过2门的学生的平均成绩和选课门数。

select s.Sname 姓名,avg(sc.Grade) 平均成绩, count(sc.Cno) 选课门数 from student s, sc where s.Sno=sc.Sno group by Sname having Count(sc.Cno)>2;

--10.列出总成绩超过200分的学生,要求列出学号、总成绩。

select Sno 学号,sum(Grade) 总成绩 from sc  group by Sno having sum(Grade)>200;
select s.Sname 姓名,s.Sno 学号,sum(sc.Grade) 总成绩 from student s,sc where s.Sno=sc.Sno group by s.Sname having sum(sc.Grade)>200;

--11.查询选修了c02号课程的学生的姓名和所在系。

select s.Sname 姓名,s.Sdept 所在系 from student s,sc  where sc.Cno='c02' and s.Sno=sc.Sno;

--12.查询成绩80分以上的学生的姓名、课程号和成绩,并按成绩的降序排列结果。

select s.Sname 姓名,sc.Cno 课程号,sc.Grade 成绩 from Student s,sc where s.Sno=sc.Sno and sc.Grade>80 order by sc.Grade desc;

--13.查询计算机系男生修了"数据库基础"的学生的姓名、性别、成绩。

select s.Sname 姓名, s.Ssex 性别, sc.Grade 成绩 from student s,sc,course c where sc.Sno=s.Sno and sc.Cno=c.Cno and c.Cname='数据库基础' and s.Sdept='计算机系';

--14.查询哪些学生的年龄相同,要求列出年龄相同的学生的姓名和年龄。

select s1.Sname 年龄相同的学生姓名,s1.Sage 年龄 from student s1 inner join student s2 on s1.Sage in(select Sage from student where s1.Sage=s2.Sage and s1.Sname !=s2.Sname) group by s1.Sname, s1.Sage order by s1.Sage;

--15.查询哪些课程没有人选,要求列出课程号和课程名。

select Cno 课程号,Cname 课程名 from course where Cno not in (select Cno from sc);

--16.查询有考试成绩的所有学生的姓名、修课名称及考试成绩

select s.Sname 姓名,c.Cname 修课名称,sc.Grade 成绩 from student s,sc,course c where c.Cno=sc.Cno and s.Sno=sc.Sno and sc.Grade is not null order by s.Sname;

--17.分别查询信息系和计算机系的学生的姓名、性别、修课名称、修课成绩,

select s.Sname,s.Ssex,c.Cname,sc.Grade from student s,sc,course c where s.Sdept in ('计算机系','信息系') and s.Sno=sc.Sno and sc.Cno=c.Cno;

--18.用子查询实现如下查询:

--(1)    查询选修了C01号课程的学生的姓名和所在系。

select s.Sname 姓名, s.Sdept 所在系 from student s where s.Sno in (select sc.Sno from sc where Cno='c01');

--(2)    查询数学系成绩80分以上的学生的学号、姓名。
    

select s.Sno 学号, s.Sname 姓名  from student s,sc where s.Sno=sc.Sno and s.Sdept='数学系' and sc.Grade>80;

--(3)    查询计算机系学生所选的课程名.
    

select distinct c.Cname 课程名称 from student s,sc,course c where s.Sdept='计算机系' and s.Sno=sc.Sno and c.Cno=sc.Cno;
select course.Cname 课程名称 from course  where Cno in (select Cno from sc where sc.Sno in (select Sno from student where Sdept='计算机系'));

-