sql的50道练习题(更新中。。)

方方的我马上要准备面试了，感觉自己脑子里面什么都没有，只能拿出50个sql题先复习一下基础了。。
哎。。。

drop database if exists student_manager;

create database student_manager;
use student_manager;

create table student(
	snu varchar(2) primary key,
    sname varchar(10) not null,
    age date default "1994-12-09",
    sex enum('male','famela')
);

create table cource(
	cnu varchar(2) primary key,
    cname varchar(10) not null,
    tnu int
);

create table teacher(
	tnu varchar(2) primary key,
    tname varchar(10) not null
);

alter table cource
add constraint fk_cource_t foreign key(cnu)
references teacher(tnu) on delete cascade;

create table score(
	snu varchar(2) references student(snu),
    cnu varchar(2) references cource(cnu),
    score float(10,2) not null
);


insert into student(snu,sname,age,sex) values
("01" , "赵雷" , "1990-01-01" , "famela"),
("02" , "钱电" , "1990-12-21" , "famela"),
("03" , "孙风" , "1990-05-20" , "famela"),
("04" , "李云" , "1990-08-06" , "famela"),
("05" , "周梅" , "1991-12-01" , "male"),
("06" , "吴兰" , "1992-03-01" , "male"),
("07" , "郑竹" , "1989-07-01" , "male"),
("08" , "王菊" , "1990-01-20" , "male");

insert into teacher(tnu,tname) values
("01" , "张三"),
("02" , "李四"),
("03" , "王五");

insert into cource(cnu,cname,tnu) values
("01" , "语文" , "02"),
("02" , "数学" , "01"),
("03" , "英语" , "03");

insert into score(snu, cnu, score) values
("01" , "01" , 80.2),
("01" , "02" , 90.5),0
("01" , "03" , 99),
("02" , "01" , 70),
("02" , "02" , 60),
("02" , "03" , 80),
("03" , "01" , 80),
("03" , "02" , 80),
("03" , "03" , 80),
("04" , "01" , 50),
("04" , "02" , 30),
("04" , "03" , 20),
("05" , "01" , 76),
("05" , "02" , 87),
("06" , "01" , 31),
("06" , "03" , 34);

1、查询"01"课程比"02"课程成绩高的学生的信息及课程分数

select * from student join 
(select A.snu,A.score s1, B.score s2
from
  (select * from score sc where sc.cnu='01') as A cross join
  (select * from score sc where sc.cnu='02') as B
where A.snu = B.snu and A.score > B.score) as r
on student.snu=r.snu;

-- 思路
将选择了01课程和02课程的同学分表select出两个表做笛卡尔积，通过where后的条件过滤数据
再与学生表相连接，取出相关记录

2、查询"01"课程比"02"课程成绩低的学生的信息及课程分数

select * from student join 
(select A.snu,A.score s1, B.score s2
 from
     (select * from score sc where sc.cnu='01') as A,
     (select * from score sc where sc.cnu='02') as B
 where A.snu = B.snu and A.score < B.score) as r
 on student.snu=r.snu;

3、查询平均成绩大于等于60分的同学的学生编号、学生姓名和平均成绩

select s.snu,s.sname,round(avg(score),2) as avg_point
from student s
join score 
on s.snu = score.snu
group