SQL经典面试题集(二)

SQL经典面试题集（二）第十一题：有表students(name,class,grade),请用标准sql语句完成nameclassgrade张三数学81李四语文70王五数学90张三语文60李四数学100王五语文90王五英语81要求:用sql语句输出各门功课都大于80分的同学姓名?createtablestudents(namevarchar(25),classvarchar(25),gradeint)insertintostudentsvalues('张三','语文',20)insertintostudentsvalues('张三','数学',90)insertintostudentsvalues('张三','英语',50)insertintostudentsvalues('李四','语文',81)insertintostudentsvalues('李四','数学',60)insertintostudentsvalues('李四','英语',90)insertintostudentsvalues('王二','数学',81)insertintostudentsvalues('王二','英语',90)insertintostudentsvalues('李五','数学',83)insertintostudentsvalues('李五','英语',90)insertintostudentsvalues('李五','化学',90)---选出所有成绩大于80分的学生姓名-----------解法一------selectnamefromstudentsgroupbynamehavingmin(grade)80------解法二------selectdistinctNamefromstudentswheregrade80andNamenotin(selectNamefromstudentswheregrade80)------解法三------selectdistinctnamefromstudentswherenamenotin(selectnamefromstudentswheregrade=80groupbyname)-----解法四-------selectnamefromstudentsgroupbynamehavingnamenotin(selectnamefromstudentswheregrade=80)第十二题：已知一个表的结构为：姓名科目成绩张三语文20张三数学30张三英语50李四语文70李四数学60李四英语90怎样通过select语句把他变成以下结构：姓名语文数学英语张三203050李四706090createtablestudents(namevarchar(25),classvarchar(25),gradeint)insertintostudentsvalues('张三','语文',20)insertintostudentsvalues('张三','数学',90)insertintostudentsvalues('张三','英语',50)insertintostudentsvalues('李四','语文',81)insertintostudentsvalues('李四','数学',60)insertintostudentsvalues('李四','英语',90)--解答：selectA.Name,A.gradeas语文,B.gradeas数学,C.gradeas英语fromstudentsA,studentsB,studentsCwhereA.Name=B.NameandB.Name=C.NameandA.class='语文'andB.class='数学'andC.class='英语'第十三题：我现在有两张表个表createtableuserinfo(idint,usernamevarchar(32),u_idint)createtablecheckinfo(idint,checktypevarchar(32)--出勤的类型（正常，病假，事假）u_idint)两张表通过u_id关联的怎么查询出每个用户的某个月出勤的情况：比如说，1月份，正常出勤多少天，事假多少天，病假多少天?例如：username病假（天数）事假（天数）病假（天数）张三1552createtableuserinfo(idint,usernamevarchar(32),u_idint)createtablecheckinfo(idint,checktypevarchar(32),--出勤的类型（正常，病假，事假）u_idint)deletefromuserinfoinsertintouserinfovalues(1,'user1',1)insertintouserinfovalues(2,'user2',2)insertintouserinfovalues(3,'user3',3)insertintouserinfovalues(4,'user4',4)insertintocheckinfovalues(1,'正常',1)insertintocheckinfovalues(2,'正常',1)insertintocheckinfovalues(3,'病假',1)insertintocheckinfovalues(4,'正常',2)insertintocheckinfovalues(5,'事假',2)insertintocheckinfovalues(6,'病假',2)insertintocheckinfovalues(7,'正常',2)insertintocheckinfovalues(8,'病假',2)insertintocheckinfovalues(9,'正常',3)insertintocheckinfovalues(10,'事假',3)insertintocheckinfovalues(11,'病假',3)insertintocheckinfovalues(12,'正常',3)insertintocheckinfovalues(13,'正常',3)insertintocheckinfovalues(14,'正常',3)insertintocheckinfovalues(15,'正常',3)insertintocheckinfovalues(16,'病假',3)insertintocheckinfovalues(17,'正常',4)insertintocheckinfovalues(18,'事假',4)insertintocheckinfovalues(19,'病假',4)insertintocheckinfovalues(20,'正常',4)insertintocheckinfovalues(21,'事假',4)insertintocheckinfovalues(22,'病假',4)insertintocheckinfovalues(23,'事假',4)insertintocheckinfovalues(24,'病假',4)---解法一:selectb.*,m.正常,m.事假,m.病假fromuserinfobjoin(selecta.u_id,count(casewhena.checktype='病假'then'1'end)病假,count(casewhena.checktype='正常'then'1'end)正常,count(casewhena.checktype='事假'then'1'end)事假fromcheckinfoagroupbya.u_id)monm.u_id=b.u_id---解法二:selectb.*,m1.正常,m2.病假,m3.事假fromuserinfobleftjoin(selecta.u_id,count(a.checktype)正常fromcheckinfoawherea.checktype='正常'groupbya.u_id)m1onb.u_id=m1.u_idleftjoin(selecta.u_id,count(a.checktype)病假fromcheckinfoawherea.checktype='病假'groupbya.u_id)m2onb.u_id=m2.u_idleftjoin(selecta.u_id,count(a.checktype)事假fromcheckinfoawherea.checktype='事假'groupbya.u_id)m3onb.u_id=m3.u_id第十四题：产品颜色数量产品1红色100产品1蓝色80产品2蓝色103产品2红色NULL产品2红色89产品1红色1001：按产品分类，仅列出各类商品中红色多于蓝色的商品名称及差额数量createtableproducts(namevarchar(20),colorchar(20),quantitiesint)insertintoproductsvalues('产品1','红色',100)insertintoproductsvalues('产品1','蓝色',80)insertintoproductsvalues('产品2','红色',null)insertintoproductsvalues('产品2','蓝色',103)insertintoproductsvalues('产品2','红色',89)insertintoproductsvalues('产品1','红色',100)-----解答:---第一步：查询出每种商品中兰色和红色数量及产品名称--红色：selectname,sum(quantities)fromproductswherecolor='红色'groupbyname--蓝色：selectname,sum(quantities)fromproductswherecolor='蓝色'groupbyname---第二步：查询出要求的结果：selectt1.name,t1.x-t2.xasbalancefrom(selectname,sum(quantities)asxfromproductswherecolor='红色'groupbyname)t1,(selectname,sum(quantities)asxfromproductswherecolor='蓝色'groupbyname)t2wheret1.xt2.xandt1.name=t2.name第十五题：--查询学生表中，选修课超过5门的名字!createtablestudents(idintIDENTITY,namevarchar(20),elective_coursevarchar(20))insertintostudentsvalues('student1','course1')insertintostudentsvalues('student1','course2')insertintostudentsvalues('student1','course3')insertintostudentsvalues('student1','course4')insertintostudentsvalues('student1','course6')insertintostudentsvalues('student1','course6')insertintostudentsvalues('student2','course1')insertintostudentsvalues('student2','course2')insertintostudentsvalues('student2','course3')insertintostudentsvalues('student2','course4')insertintostudentsvalues('student2','course5')insertintostudentsval