资源描述：
《经典SQL面试题》由会员上传分享，免费在线阅读，更多相关内容在行业资料-天天文库。
1、.表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)NOTNUL
2、L,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)教师表CREATETABLEta
3、acher(tidint(10)DEFAULTNULL,tNamevarchar(10)DEFAULTNULL)ENGINE=InnoDBDEFAULTCHARSET=utf8;数据：（MySQL）insertintotaacher(tid,tName)values(1,'李老师'),(2,'何以琛'),(3,'叶平');insertintostudent(sid,sName,sAge,sSex)values('1001','张三丰','1980-10-1223:12:36','男'),('1002','张无极','1995-10-1223:12:36'
4、,'男'),('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','女');insertintosc(sid,cid,score)values('1','001',80),('1','002',60),('1','003',75)
5、,('2','001',85),('2','002',70),('3','004',100),('3','001',90),('3','002',55),('4','002',65),('4','003',60);insertintocourse(cid,cName,tid)values('001','企业管理',3),('002','马克思',3),('003','UML',2),('004','数据库',1),('005','英语',1);问题：1、查询“001”课程比“002”课程成绩高的所有学生的学号;selecta.Sidfrom(select
6、sid,scorefromSCwhereCid='001')a,(selectsid,scorefromSCwhereCid='002')bwherea.score>b.scoreanda.sid=b.sid;..2、查询平均成绩大于60分的同学的学号和平均成绩;selectSid,avg(score)fromscgroupbySidhavingavg(score)>60;3、查询所有同学的学号、姓名、选课数、总成绩;selectStudent.Sid,Student.Sname,count(SC.Cid),sum(score)fromStudentle
7、ftOuterjoinSConStudent.Sid=SC.SidgroupbyStudent.Sid,Sname4、查询姓“李”的老师的个数;selectcount(distinct(Tname))fromTeacherwhereTnamelike'李%';5、查询没学过“叶平”老师课的同学的学号、姓名;selectStudent.Sid,Student.SnamefromStudentwhereSidnotin(selectdistinct(SC.Sid)fromSC,Course,TeacherwhereSC.Cid=Course.CidandTe
8、acher.Tid=Course.TidandTeacher.Tname='叶平
