资源描述：
《经典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,ame,Tid)课程表CREATETABLEcourse(cidvarchar(10)NOTNU
2、LL,amevarchar(10)DEFAULTNULL,tidint(20)DEFAULTNULL,PRIMARYKEY(cid))ENGINE=InnoDBDEFAULTCHARSET=utf8;SC(Sid,Cid,score)成绩表CREATETABLEsc(sidvarchar(10)DEFAULTNULL,cidvarchar(10)DEFAULTNULL,scoreint(10)DEFAULTNULL..word.zl-.-)ENGINE=InnoDBDEFAULTCHARSET=utf8;Teacher(Tid,Tname)教师表CR
3、EATETABLEtaacher(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-1
4、223: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','女');insertintosc(sid,cid,score)values('1','001',80),('1','002',60),('1','
5、003',75),('2','001',85),('2','002',70),('3','004',100),('3','001',90),('3','002',55),('4','002',65),('4','003',60);insertintocourse(cid,ame,tid)values('001','企业管理',3),('002','马克思',3),('003','UML',2),('004','数据库',1),('005','英语',1);问题：1、查询“001〞课程比“002〞课程成绩高的所有学生的学号;selecta.Sidfro
6、m(selectsid,scorefromSCwhereCid='001')a,(selectsid,scorefromSCwhereCid='002')bwherea.score>b.scoreanda.sid=b.sid;..word.zl-.-2、查询平均成绩大于60分的同学的学号和平均成绩;selectSid,avg(score)fromscgroupbySidhavingavg(score)>60;3、查询所有同学的学号、、选课数、总成绩;selectStudent.Sid,Student.Sname,count(SC.Cid),sum(s
7、core)fromStudentleftOuterjoinSConStudent.Sid=SC.SidgroupbyStudent.Sid,Sname4、查询姓“〞的教师的个数;selectcount(distinct(Tname))fromTeacherwhereTnamelike'%';5、查询没学过“叶平〞教师课的同学的学号、;selectStudent.Sid,Student.SnamefromStudentwhereSidnotin(selectdistinct(SC.Sid)fromSC,Course,TeacherwhereSC.Cid
8、=Course.CidandTeacher.Tid=Course.TidandTeacher.Tname='
