2021年经典SQL面试题.docx

文档介绍：经典SQL面试题

　　表
　　Student(Sid,Sname,Sage,Ssex)学生表
　　CREATETABLEstudent(
　　sidvarchar(10)NOTNULL,
　　sNamevarchar(20)DEFAULTNULL,
　　sAgedatetimeDEFAULT"1980-10-1223:12:36",
　　sSexvarchar(10)DEFAULTNULL,
　　PRIMARYKEY(sid)
　　)ENGINE=InnoDBDEFAULTCHARSET=utf8;
　　Course(Cid,Cname,Tid)课程表
　　CREATETABLEcourse(
　　cidvarchar(10)NOTNULL,
　　cNamevarchar(10)DEFAULTNULL,
　　tidint(20)DEFAULTNULL,
　　PRIMARYKEY(cid)
　　)ENGINE=InnoDBDEFAULTCHARSET=utf8;SC(Sid,Cid,score)成绩表
　　CREATETABLEsc(
　　sidvarchar(10)DEFAULTNULL,
　　cidvarchar(10)DEFAULTNULL,
　　scoreint(10)DEFAULTNULL
　　)ENGINE=InnoDBDEFAULTCHARSET=utf8;Teacher(Tid,Tname)老师表
　　CREATETABLEtaacher(
　　tidint(10)DEFAULTNULL,
　　tNamevarchar(10)DEFAULTNULL
　　)ENGINE=InnoDBDEFAULTCHARSET=utf8;
　　数据：MySQL
　　insert
　　intotaacher(tid,tName)values(1,"李老师"),(2,"何以琛"),(3,"叶平");
　　insert
　　intostudent(sid,sName,sAge,sSex)values("1001","张三丰","1980-10-1223:12:36","男"),("1002","张无极","1995-10-1223:12:36","男"),("1003","李奎","1992-10-1223:12:36","女"),("1004","李***","1980-10-1223:12:36","女"),("1005","李世明","1981-10-1223:12:36","男"),("1006","赵六","1986-10-1223:12:36","男"),("1007","田七","1981-10-1223:12:36","女");
　　insert
　　intosc(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
　　intocourse(cid,cName,tid)values("001","企业管理",3),("002","马克思",3),("003","UML",2),("004","数据库",1),("005","英语",1);
　　问题：
　　1、查询“001”课程比“002”课程成绩高的全部学生的学号;
　　selectfrom(selectsid,scorefromSCwhereCid="001")a,(selectsid,score
　　fromSCwhereCid="002")b
　　where>and=;
　　2、查询平均成绩大于60分的同学的学号和平均成绩;
　　selectSid,avg(score)
　　fromsc
　　groupbySidhavingavg(score)>60;
　　3、查询全部同学的学号、姓名、选课数、总成绩;
　　select,,count,sum(score)
　　fromStudentleftOuterjoinSCon=
　　groupby,Sname
　　4、查询姓“李”的老师的个数;
　　selectcount(distinct(Tname))
　　fromTeacher
　　whereTnamelike"李%";
　　5、查询没学过“叶平”老师课的同学的学号、姓名;
　　select,
　　fromStudent
　　w