资源描述:
《经典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)NOTNULL,cNameva
2、rchar(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)DE
3、FAULTNULL,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','男'),('1003','李奎','1992-10
4、-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),('2','001',85),('2','002',70),('3',
5、'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(selectsid,scorefromSCwhereCid='001')a,(selectsid,sc
6、orefromSCwhereCid='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)fromStudentleftOuterjoinSConStudent.Sid=SC.SidgroupbyStudent.Sid,Sn
7、ame4、查询姓“李”的老师的个数;selectcount(distinct(Tname))fromTeacherwhereTnamelike'李%';5、查询没学过“叶平”老师课的同学的学号、姓名;selectStudent.Sid,Student.SnamefromStudentwhereSidnotin(selectdistinct(SC.Sid)fromSC,Course,TeacherwhereSC.Cid=Course.CidandTeacher.Tid=Course.TidandTeacher.Tname='叶平