2021年经典SQL面试题.doc

文档介绍：表
Student(Sid,Sname,Sage,Ssex) 学生表
CREATE TABLE student (
sid varchar(10) NOT NULL,
sName varchar(20) DEFAULT NULL,
sAge datetime DEFAULT '1980-10-12 23:12:36',
sSex varchar(10) DEFAULT NULL,
PRIMARY KEY (sid)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Course(Cid,Cname,Tid) 课程表
CREATE TABLE course (
cid varchar(10) NOT NULL,
cName varchar(10) DEFAULT NULL,
tid int(20) DEFAULT NULL,
PRIMARY KEY (cid)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
SC(Sid,Cid,score) 成绩表
CREATE TABLE sc (
sid varchar(10) DEFAULT NULL,
cid varchar(10) DEFAULT NULL,
score int(10) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Teacher(Tid,Tname) 老师表
CREATE TABLE taacher (
tid int(10) DEFAULT NULL,
tName varchar(10) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
数据：（MySQL）
insert into taacher(tid,tName) values (1,'李老师'),(2,'何以琛'),(3,'叶平');
insert into student(sid,sName,sAge,sSex) values ('1001','张三丰','1980-10-12 23:12:36','男'),('1002','张无极','1995-10-12 23:12:36','男'),('1003','李奎','1992-10-12 23:12:36','女'),('1004','李元宝','1980-10-12 23:12:36','女'),('1005','李世明','1981-10-12 23:12:36','男'),('1006','赵六','1986-10-12 23:12:36','男'),('1007','田七','1981-10-12 23:12:36','女');
insert into sc(sid,cid,score) values ('1','001',80),('1','002',60),('1','003',75),('2','001',85),('2','002',70),('3','004',100),('3','001',90),('3','002',55),('4','002',65),('4','003',60);
insert into course(cid,cName,tid) values ('001','企业管理',3),('002','马克思',3),('003','UML',2),('004','数据库',1),('005','英语',1);
问题：
1、查询“001”课程比“002”课程成绩高全部学生学号;
select from (select sid,score from SC where Cid='001') a,(select sid,score
from SC where Cid='002') b
where > and =;
2、查询平均成绩大于60分同学学号和平均成绩;
select Sid,avg(score)
from sc
group by Sid having avg(score) >60;
3、查询全部同学学号、姓名、选课数、总成绩;
select ,,count(),sum(score)
from Student left Outer join SC on =
group by ,Sname