实验答案四五六从数据库中stu infoxk gdept gf

EDU_Dstu_info、xk、gdept、gfied、gban、gcourse表中做以查询每位学生的学号、、、出生日期、所在学院号并按学号升SELECTxh,xm,xbm,csrq,xshFROMstu_infoORDERBYSELECTzyh,zym,xshFROMgfiedORDERBYzyhSELECTxh,kscjFROMxkWHEREORDERBY在stu_info表中查询各个班的班号、所属专业号、所在学院号（ditintSELECTDISTINCTbh,zyh,xshFROMstu_info查询课程号以“01SELECTkch,kmFROMgcourseWHEREkchlike'01%'SELECTbh,xsh,rsFROMgbanSELECTbh,xsh,COUNT(xh)asrsFROMstu_infoGROUPBYbh,xshSELECT*FROMWHEREbhLIKE计SELECTCOUNT(bhAS含计字的班级个数FROMgbanWHEREbhLIKE计查询xsh='01'、男生的学号、、、班号SELECTFROMWHERExsh='01'ANDxbm='男'SELECTzyh,COUNT(xh)AS专业人数FROMstu_infoWHERExsh='12'GROUPBYzyhSELECT*FROMGBANWHERExshIS（kkny='2032'“010108SELECTMIN(kscj)ASFROMWHEREkch='010108'ANDSELECT*FROMWHEREkmLIKE'%规划SELECTCOUNT(*)AS课程个数FROMWHEREkmLIKE'%规划SELECTxh,SUM(kscj)AS总分,AVG(kscj)AS平均分,COUNT(kch)AS数FROMWHERExh=' GROUPBYxh2001—2002学年第一学期（kkny='20011'）SELECTxh,SUM(kscj)AS总分,AVG(kscj)ASFROMWHEREkkny='20011'GROUPBYxh2001—2002学年第一学期（kkny='20011'）SELECTkch,COUNT(xh)ASFROMWHEREkkny='20011'GROUPBYkchXKSELECTCOUNT(DISTINCTxh)ASFROMWHEREKCHISNOT02SELECTzyh,zymFROMgfiedWHERExsh='02'02SELECTCOUNT(zyh)ASFROMgfiedWHERExsh='02'从EDU_Dstu_info、xk、gdept、gfied、gban、gcourse表中做以查询信息科学与的学生的学号、、并按学号升序排序SELECTxh,xm,xbmFROMWHEREstu_info.xsh=gdept.xshANDgdept.xsm='信息科学与工程ORDERBY查询管理学院的学号、、班号并按学号升序排序SELECTxh,xm,bhFROMstu_info,gdeptWHEREstu_info.xsh=gdept.xshANDgdept.xsm='管理ANDxbm='女ORDERBY查询材料科学与姓徐的学生的学号、、、专业号、班SELECTxh,xm,xbm,zyh,bh,jgFROMstu_info,gdeptWHEREstu_info.xsh=gdept.xshANDgdept.xsm='材料科学与工程'ANDLIKE'徐SELECTzyh,zymFROMWHEREgfied.xsh=gdept.xshANDgdept.xsm='管理SELECTxsmFROMWHEREgban.xsh=gdept.xshANDbh='查询“应用化学”专业的学生的学号、、并按学号降序排序SELECTxh,xm,xbmFROMWHEREstu_info.zyh=gfied.zyhANDzym='应用化学ORDERBYxh SELECTgcourse.kch,km,kscjFROMgcourse,xkWHEREgcourse.kch=xk.kchANDxh=' ORDERBYgcourse.kchSELECTa.xh,b.kch,km,kscjFROMstu_infoa,gcourseb,xkcWHEREa.xh=c.xhANDb.kch=c.kchANDbh='查询“材0169”的学生的学号、以及所选修的课程号、课程名称、SELECTa.xh,xm,b.kch,km,kscjFROMstu_infoa,gcourseb,xkWHEREa.xh=c.xhANDb.kch=c.kchANDbh='ORDERBY查询“材0169”的学生的学号、以及所选修的课程门数、平均分SELECTa.xh,xm,COUNT(b.kch)AS课程门数,AVG(kscj)ASFROMstu_infoa,gcourseb,xkWHEREa.xh=c.xhANDb.kch=c.kchANDbh='0169'GROUPBYa.xh,xm查询学号的前4位是“2001”的学生的学号、、学院名称SELECTxh,xm,xsmFROMWHEREstu_info.xsh=gdept.xshandxhLIKE、、ORDERBYa.xhORDERBYa.xhSELECTCOUNT(c.xh)ASFROMgcourseb,xk b.kch=c.kchANDkm='高等数学ANDSELECTCOUNT(xk.xh)AS查询大成绩不及格的学生的学号、、考试成绩，并按学号升ORDERBYa.xhSELECTa.xh,xm,km,kscjFROMstu_infoa,gcourseb,xkcWHEREa.xh=c.xhANDb.kch=c.kchANDxsh='12'ANDSELECTFROMstu_infoa,gcourseb,xkc,gdeptWHEREa.xh=c.xhANDb.kch=c.kchANDa.xsh=d.xshANDxsm='管理'AND2001—2002学年第一学期（kkny='20011'）SELECTa.xh,xm,xsm,COUNT(c.kch)ASFROMstu_infoa,xkc,gdeptWHEREa.xh=c.xhANDa.xsh=d.xshANDkkny='20011'GROUPBYa.xh,xm,xsm2001—2002学年第一学期（kkny='20011'）8门的学生的学号、、学院名称SELECTa.xh,xm,xsm,COUNT(b.kchASFROMstu_infoa,gcourseb,xkc,gdeptWHEREa.xh=c.xhANDb.kch=c.kchANDa.xsh=d.xshANDkkny='20011'GROUPBYa.xh,xm,xsm查询考试成绩在85分以上的学生的学号、、课程名称、考试成绩SELECTa.xh,xm,km,kscjFROMstu_infoa,gcourseb,xkcWHEREa.xh=c.xhANDb.kch=c.kchANDkscj>8520．查询管理学院考试成绩在85分以上的学生的学号、、课程名称、SELECTFROMstu_infoa,gcourseb,xkc,gdeptWHEREa.xh=c.xhANDb.kch=c.kchANDa.xsh=d.xshAND kscj>85AND1、SELECTxh,xmFROMstu_infoWHERExhin(SELECTFROMWHEREKCH='090101')ORDERBYxhDESC2SELECTxh,xm,xbmFROMstu_infoWHERExsh=(SELECTxshFROMWHERExsm='信息科学与工程3SELECTxh,xm,xbm,bhFROMstu_infoWHEREzyhin(SELECTzyhFROMstu_infoWHERExm='')4SELECTzyh,zymFROMgfiedWHERExsh=(SELECTxshFROMWHERExsm='材料科学与工程5SELECTxh,xm,xbm,bhFROMstu_infoWHERExmLIKE'徐ANDxshSELECTxshFROMWHERExsm='材料科学与工程6SELECTbh,rsFROMgbanWHERExsh=(SELECTxshFROMWHERExsm='化学化工SELECTxhFROMWHEREkkny='20032'ANDANDkscj=(SELECTFROMWHEREkkny='20032'ANDSELECTFROMWHEREkkny='20032'ANDANDkscj<=ALL(SELECTFROMWHEREkkny='20032'AND8SELECTxh,xmFROMstu_infoWHERExhin(SELECTFROMWHEREkkny='20032'ANDANDkscj=(SELECTFROMWHEREkkny='20032'ANDSELECTxh,xmFROMstu_infoWHERExhin(SELECTFROMWHEREkkny='20032'ANDANDkscj<=ALL(SELECTFROM 9SELECTFROMgbanWHEREzyh=(SELECTzyhFROMWHEREzym='应用化学SELECTbh,rsFROMgbanWHEREzyh=(SELECT

FROMWHEREzym='应用化学SELECTxsmFROMgdeptWHERExsh=(SELECTFROMWHEREbh='12SELECTxh,xm,bhFROMstu_infoWHERExbm='女ANDxsh=SELECTFROMgdeptWHERExsm='管理')ORDERBY13SELECTxh,xm,bhFROMstu_info zyh=(SELECTFROMWHEREzym='应用化学')ORDERBYxhDESC14SELECTxh,xmFROM xhin(SELECTFROMWHEREkch='090101'ANDORDERBY15SELECTxh,xmFROM xhin(SELECTFROMWHEREkscj<60ANDkch=(SELECTFROMWHEREkm='大ORDERBY16SELECTCOUNT(xhASFROMstu_infoWHERExsh=(SELECTxshFROMWHERExsm='信息科学与工程17SELECTxm,xbm,bhFROMstu_info xshin(SELECTFROMWHERExsm='材料科学与工程'ORxsm='化学化工SELECTxm,xbm,bhFROMstu_info xshin(SELECTFROMWHERExsm='材料科学与工程SELECTxm,xbm,bhFROMstu_info xshin(SELECTFROM xsm='化学化工18SELECTxh,xm,bhFROMstu_infoWHERExhin(SELECTFROMWHEREkch=(SELECTFROMWHEREkm='办公自动化19SELECTCOUNT(xhASFROMWHEREkch=(SELECTFROMWHERE