北京动力节点MySQL课程34道SQL经典题

13.ÓÐ3¸ö±íS(ѧÉú±í)£¬C£¨¿Î³Ì±í£©£¬SC£¨Ñ§ÉúÑ¡¿Î±í£©S£¨SNO£¬SNAME£©´ú±í£¨Ñ§ºÅ£¬ÐÕÃû£©C£¨CNO£¬CNAME£¬CTEACHER£©´ú±í£¨¿ÎºÅ£¬¿ÎÃû£¬½Ìʦ£©SC£¨SNO£¬CNO£¬SCGRADE£©´ú±í£¨Ñ§ºÅ£¬¿ÎºÅ£¬³É¼¨£©ÎÊÌ⣺1¡¢ÕÒ³öûѡ¹ý¡°ÀèÃ÷¡±ÀÏʦµÄËùÓÐѧÉúÐÕÃû¡£2¡¢Áгö2ÃÅÒÔÉÏ£¨º¬2ÃÅ£©²»¼°¸ñѧÉúÐÕÃû¼°Æ½¾ù³É¼¨¡£3¡¢¼´Ñ§¹ý1ºÅ¿Î³ÌÓÖѧ¹ý2ºÅ¿ÎËùÓÐѧÉúµÄÐÕÃû¡£createtables(snoint(4)primarykeyauto_increment,snamevarchar(32));insertintos(sname)values('zhangsan');insertintos(sname)values('lisi');insertintos(sname)values('wangwu');insertintos(sname)values('zhaoliu');createtablec(cnoint(4)primarykeyauto_increment,cnamevarchar(32),cteachervarchar(32));insertintoc(cname,cteacher)values('Java','ÎâÀÏʦ');insertintoc(cname,cteacher)values('C++','ÍõÀÏʦ');insertintoc(cname,cteacher)values('C##','ÕÅÀÏʦ');insertintoc(cname,cteacher)values('MySQL','¹ùÀÏʦ');insertintoc(cname,cteacher)values('Oracle','ÀèÃ÷');createtablesc(snoint(4),cnoint(4),scgradedouble(3,1),constraintsc_sno_cno_pkprimarykey(sno,cno),constraintsc_sno_fkforeignkey(sno)referencess(sno),constraintsc_cno_fkforeignkey(cno)referencesc(cno));insertintosc(sno,cno,scgrade)values(1,1,30);insertintosc(sno,cno,scgrade)values(1,2,50);insertintosc(sno,cno,scgrade)values(1,3,80);insertintosc(sno,cno,scgrade)values(1,4,90);insertintosc(sno,cno,scgrade)values(1,5,70);insertintosc(sno,cno,scgrade)values(2,2,80);insertintosc(sno,cno,scgrade)values(2,3,50);insertintosc(sno,cno,scgrade)values(2,4,70);insertintosc(sno,cno,scgrade)values(2,5,80);insertintosc(sno,cno,scgrade)values(3,1,60);insertintosc(sno,cno,scgrade)values(3,2,70);insertintosc(sno,cno,scgrade)values(3,3,80);insertintosc(sno,cno,scgrade)values(4,3,50);insertintosc(sno,cno,scgrade)values(4,4,80);/*1¡¢ÕÒ³öûѡ¹ý¡°ÀèÃ÷¡±ÀÏʦµÄËùÓÐѧÉúÐÕÃû¡£2¡¢Áгö2ÃÅÒÔÉÏ£¨º¬2ÃÅ£©²»¼°¸ñѧÉúÐÕÃû¼°Æ½¾ù³É¼¨¡£3¡¢¼´Ñ§¹ý1ºÅ¿Î³ÌÓÖѧ¹ý2ºÅ¿ÎËùÓÐѧÉúµÄÐÕÃû¡£*/--ÏÈÕÒ³öÑ¡¹ýÀèÃ÷ÀÏʦµÄѧÉú±àºÅ-ÀèÃ÷ÀÏʦµÄÊڿεıàºÅselectcnofromcwherecteacher='ÀèÃ÷';selectsnofromscwherecno=(selectcnofromcwherecteacher='ÀèÃ÷');select*fromswheresnonotin(selectsnofromscwherecno=(selectcnofromcwherecteacher='ÀèÃ÷'));--Áгö2ÃÅÒÔÉÏ£¨º¬2ÃÅ£©²»¼°¸ñѧÉúÐÕÃû¼°Æ½¾ù³É¼¨t1selectsc.sno,s.sname,count(*)asstudentNumfromscjoinsonsc.sno=s.snowherescgrade60groupbysc.sno,s.snamehavingstudentNum=2;t2selectsc.sno,avg(sc.scgrade)asavgscgradefromscgroupbysc.sno;selectt1.sname,t2.avgscgradefrom(selectsc.sno,s.sname,count(*)asstudentNumfromscjoinsonsc.sno=s.snowherescgrade60groupbysc.sno,s.snamehavingstudentNum=2)t1join(selectsc.sno,avg(sc.scgrade)asavgscgradefromscgroupbysc.sno)t2ont1.sno=t2.sno;--¼´Ñ§¹ý1ºÅ¿Î³ÌÓÖѧ¹ý2ºÅ¿ÎËùÓÐѧÉúµÄÐÕÃûselectsnofromscwherecno=1;selectsnofromscwherecno=2;selects.snamefromscjoinsonsc.sno=s.snowherecno=1andsc.snoin(selectsnofromscwherecno=2);1.È¡µÃÿ¸ö²¿ÃÅ×î¸ßнˮµÄÈËÔ±Ãû³ÆµÚÒ»²½£ºÇó³öÿ¸ö²¿ÃŵÄ×î¸ßнˮselecte.deptno,max(e.sal)asmaxsalfromempegroupbye.deptno;+--------+---------+|deptno|maxsal|+--------+---------+|10|5000.00||20|3000.00||30|2850.00|+--------+---------+½«ÒÔÉϲéѯ½á¹ûµ±³ÉÒ»¸öÁÙʱ±ít(deptno,maxsal)selecte.deptno,e.ename,t.maxsal,e.salfrom(selecte.deptno,max(e.sal)asmaxsalfromempegroupbye.deptno)tjoinempeont.deptno=e.deptnowheret.maxsal=e.salorderbye.deptno;+--------+-------+---------+---------+|deptno|ename|maxsal|sal|+--------+-------+---------+---------+|10|KING|5000.00|5000.00||20|SCOTT|3000.00|3000.00||20|FORD|3000.00|3000.00||30|BLAKE|2850.00|2850.00|+--------+-------+---------+---------+2.ÄÄЩÈ˵ÄнˮÔÚ²¿ÃÅƽ¾ùнˮ֮ÉϵÚÒ»²½£ºÇó³öÿ¸ö²¿ÃŵÄƽ¾ùнˮselecte.deptno,avg(e.sal)asavgsalfromempegroupbye.deptno;+--------+-------------+|deptno|avgsal|+--------+-------------+|10|2916.666667||20|2175.000000||30|1566.666667|+--------+-------------+½«ÒÔÉϲéѯ½á¹ûµ±³ÉÁÙʱ±ít(deptno,avgsal)selectt.deptno,e.enamefrom(selecte.deptno,avg(e.sal)asavgsalfromempegroupbye.deptno)tjoinempeone.deptno=t.deptnowheree.salt.avgsal;+--------+-------+|deptno|ename|+--------+-------+|30|ALLEN||20|JONES||30|BLAKE||20|SCOTT||10|KING||20|FORD|+--------+-------+3.È¡µÃ²¿ÃÅÖУ¨ËùÓÐÈ˵ģ©Æ½¾ùнˮµÈ¼¶3.1È¡µÃ²¿ÃÅÖÐËùÓÐÈ˵Äƽ¾ùнˮµÄµÈ¼¶µÚÒ»²½£ºÇó³ö²¿ÃŵÄƽ¾ùнˮselecte.deptno,avg(e.sal)asavgsalfromempegroupbye.deptno;½«ÒÔϲéѯ½á¹ûµ±³ÉÁÙʱ±ít(deptno,avgsal)+--------+-------------+|deptno|avgsal|+--------+-------------+|10|2916.666667||20|2175.000000||30|1566.666667|+--------+-------------+select*fromsalgrade;+-------+-------+-------+|GRADE|LOSAL|HISAL|+-------+-------+-------+|1|700|1200||2|1201|1400||3|1401|2000||4|2001|3000||5|3001|9999|+-------+-------+-------+selectt.deptno,t.avgsal,s.gradefrom(selecte.deptno,avg(e.sal)asavgsalfromempegroupbye.deptno)tjoinsalgradesont.avgsalbetweens.losalands.hisal;+--------+-------------+-------+|deptno|avgsal|grade|+--------+-------------+-------+|30|1566.666667|3||10|2916.666667|4||20|2175.000000|4|+--------+-------------+-------+3.2È¡µÃ²¿ÃÅÖÐËùÓÐÈ˵Äƽ¾ùµÄнˮµÈ¼¶µÚÒ»²½£ºÇó³öÿ¸öÈ˵ÄнˮµÈ¼¶selecte.deptno,e.ename,s.gradefromempejoinsalgradesone.salbetweens.losalands.hisalorderbye.deptno;+--------+--------+-------+|deptno|ename|grade|+--------+--------+-------+|10|CLARK|4||1