有关数据库的编程题

1.学生表Student
Sno 学生编号,Sname 学生姓名,Sage 出生年月,Ssex 学生性别
2.课程表Course
Cno 课程编号,Cname 课程名称,Tno 教师编号
3.教师表 Teacher
Tno 教师编号,Tname 教师姓名
4.成绩表 SC
Sno 学生编号,Cno 课程编号,score 分数
创建表：
Student
create table Student(Sno varchar(10),Sname varchar(10),Sage datetime,Ssex varchar(10))
insert into Student values(‘01’ , ‘赵雷’ , ‘1990-01-01’ , ‘男’)
insert into Student values(‘02’ , ‘钱电’ , ‘1990-12-21’ , ‘男’)
insert into Student values(‘03’ , ‘孙风’ , ‘1990-05-20’ , ‘男’)
insert into Student values(‘04’ , ‘李云’ , ‘1990-08-06’ , ‘男’)
insert into Student values(‘05’ , ‘周梅’ , ‘1991-12-01’ , ‘女’)
insert into Student values(‘06’ , ‘吴兰’ , ‘1992-03-01’ , ‘女’)
insert into Student values(‘07’ , ‘郑竹’ , ‘1989-07-01’ , ‘女’)
insert into Student values(‘08’ , ‘王菊’ , ‘1990-01-20’ , ‘女’)

Course
create table Course(Cno varchar(10),Cname varchar(10),T# varchar(10))
insert into Course values(‘01’ , ‘语文’ , ‘02’)
insert into Course values(‘02’ , ‘数学’ , ‘01’)
insert into Course values(‘03’ , ‘英语’ , ‘03’)

Teacher
create table Teacher(Tno varchar(10),Tname=varchar(10))
insert into Teacher values(‘01’ , ‘张三’)
insert into Teacher values(‘02’ , ‘李四’)
insert into Teacher values(‘03’ , ‘王五’)

SC
create table SC(Sno varchar(10),Cno varchar(10),score decimal(18,1))
insert into SC values(‘01’ , ‘01’ , 80)
insert into SC values(‘01’ , ‘02’ , 90)
insert into SC values(‘01’ , ‘03’ , 99)
insert into SC values(‘02’ , ‘01’ , 70)
insert into SC values(‘02’ , ‘02’ , 60)
insert into SC values(‘02’ , ‘03’ , 80)
insert into SC values(‘03’ , ‘01’ , 80)
insert into SC values(‘03’ , ‘02’ , 80)
insert into SC values(‘03’ , ‘03’ , 80)
insert into SC values(‘04’ , ‘01’ , 50)
insert into SC values(‘04’ , ‘02’ , 30)
insert into SC values(‘04’ , ‘03’ , 20)
insert into SC values(‘05’ , ‘01’ , 76)
insert into SC values(‘05’ , ‘02’ , 87)
insert into SC values(‘06’ , ‘01’ , 31)
insert into SC values(‘06’ , ‘03’ , 34)
insert into SC values(‘07’ , ‘02’ , 89)
insert into SC values(‘07’ , ‘03’ , 98)

1.查询" 01 “课程比” 02 "课程成绩高的学生的信息及课程分数
首先我们要查询01课程的信息和02课程的信息，然后要保证01和02课程是同一个人，最后让01课程成绩大于02课程成绩。
select *
from
(select * from SC where Cno = ‘01’) as A,(select * from SC where Cno = ‘02’) as B
where
A.Sno = B.Sno and A.score > B.score;

2.查询平均成绩大于等于60分的同学的学生编号和学生姓名和平均成绩。
select
s.Sno,
s.Sname,
avg(c.score) as avg_score
from
score c left join student s on c.Sno = s.Sno
group by
c.Sno
having avg(c.score) >= 60;
3.查询在 SC 表存在成绩的学生信息
select
*
from
Student
where
Sno in (select Sno from SC where score is not null);
4.查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩.
select
s.Sno,
s.Sname,
count(sc.Cno) as sum_course,
sum(sc.score) as sum_score
from
student s left join score sc on sc.Sno = s.Sno
group by sc.Sno.
5.查询姓李的老师的总数
select count() from Teacher where Tname like ‘李%’;
6.查询学过「张三」老师授课的同学的信息
select
s.Sno,
s.Sname,
s.Sage,
s.Ssex
from
Student s,Course,Teacher,SC
where
s.Sno = SC.Sno and SC.Cno = Course.Cno and Course.Tno = Teacher.Tno and Teacher.Tname = ‘张三’;
7.查询至少有一门课与学号为" 01 "的同学所学相同的同学的信息 （三表联查）
select *
from
Student
where
Sno in (select distinct Sno from SC where Cno in (select s.Cno from SC s where s.Cno = ‘01’));
8.查询没学过「张三」老师讲授的任一门课程的学生姓名（四表联查）
select
Sname
from
Student
where
Sno not in (select Sno from SC where Cno in (select Cno from Course where Tno in (select Tno from Teacher where Tname = ‘张三’)));
9.查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩
select
s.Sno,s.Sname,round(avg(c.score))
from
Student s left join SC c on s.Sno = c.Sno
where
c.Sno in (select sno from SC where score < 60 group by Sno having count(Sno) >= 2) group by s.Sno,s.Sname;
10.检索" 01 "课程分数小于 60 ，按分数降序排列的学生信息
select
Student.
from
Student,SC
where
Student.Sno = SC.Sno and SC.score < 60 and SC.Cno = ‘01’ order by
SC.score desc;