2021年经典SQL面试题总结.doc

文档介绍：表
Student(S#,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(C#,Cname,T#) 课程表
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(S#,C#,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(T#,Tname) 老师表
CREATE TABLE teacher (
tid int(10) DEFAULT NULL,
tName varchar(10) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
数据：（MySQL）
insert into teacher(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 s#,score from SC where C#='001') a,(select s#,score
from SC where C#='002') b
where > and #=#;
2、查询平均成绩大于60分同学学号和平均成绩;
select S#,avg(score)
from sc
group by S# having avg(score) >60;
3、查询全部同学学号、姓名、选课数、总成绩;
select #,,count(#),sum(score)
from Student left Outer join SC on #=#
group by #,Sname
4、查询姓“李”老师个数;
select count(d