SQlserver2005面试题

SELECT *FROM audition_student;--学生表

SELECT *FROM audition_scorse;--成绩表

SELECT *FROM audition_teacher;--教师表

SELECT *FROM audition_course;--课程表

?

--1.查询每个学生的平均成绩大于分的同学的学号和平均成绩；

select

??? stuId,avg(scorse)'平均成绩'

from

??? audition_scorse

??? group by stuId

??? having avg(scorse)>60;

--2.查询所有同学的学号、姓名、选课数、总成绩；

select

??? st.stuId '学号',stuName'姓名',count(courId)'选课数',sum(scorse)'总分'

from

??? audition_student st,audition_scorsesc

where

??? st.stuId=sc.stuId

group by

??? st.stuId,st.stuName

?

----------------------------------------------------------------------------

SELECT

??? stu.stuid '学号', stu.stuname'姓名', s.coursenum'选课数', s.scorsenum'总分'?

FROM

??? audition_student stu,?

??? --算出选课数,总成绩?

??? (SELECT

?????? sco.stuidstuid,COUNT(sco.courid) coursenum,SUM(sco.scorse)scorsenum??

??? FROM

?????? audition_scorse sco

??? GROUP BY

?????? sco.stuid) s

??? WHERE

?????? stu.stuid=s.stuid

??? ORDER BY

?????? stu.stuid;

--3查询姓“李”的老师的个数；

select

??? count(*)as 人数

from

??? audition_teacher

where

??? teaName like'李_'

--4查询没学过“李白”老师的授课科目的同学的学号、姓名；

select

??? st.stuId,stuName

from

??? audition_student st,audition_teachert,audition_course c,audition_scorsesc

where

??? t.teaName<>'李白'and

??? t.teaId = c.teaId and

??? c.courId = sc.courId and

??? sc.stuId = st.stuId

group by

??? st.stuId,stuName

--5通过教师表和课程表找到教师执教的课程，再通过课程表和成绩表找到学生号

??? --不要学生号中的则为不学此教师的课程

--没看懂

--6查询学过“java”并且也学过“.NET”课程的同学的学号、姓名；

select

??? st.stuId,stuName

from

??? audition_student st,audition_coursec,audition_scorse sc

where

??? c.courName='java'and

??? exists(select* from audition_course cwherec.courName='.NET')and

??? c.courId = sc.courId and

??? sc.stuId = st.stuId

--7查询“java”课程比“NET”课程成绩高的所有学生的学号；

----------------这个作废----------------------------------------------

select sc.stuId

from?audition_course c,audition_scorse sc

where c.courId=sc.courIdand

?????? (select scorsefromaudition_scorse sc,audition_course c

???????????????????? where

???????????????????? sc.courId=c.courIdand

???????????????????? c.courName='java')>

?????? (select scorsefromaudition_scorse sc,audition_course c

???????????????????? where

???????????????????? sc.courId=c.courIdand

???????????????????? c.courName='.net')

----------------------------这个--------------------------------------

select

??? a.stuId

from

??? (select stuId,scorsefrom audition_scorse wherecourId='1') a,

??? (select stuId,scorsefrom audition_scorse wherecourId='4') b

where

??? a.scorse>b.scorseand

??? a.stuId=b.stuId

---------------------------改进---------------------------------------

select

??? a.stuId

from

??? (select stuId,scorsefrom audition_scorse sc,audition_coursecwhere c.courName='java'and c.courId=sc.courId) a,audition_coursecwhere c.courName='.net'and c.courId=sc.courId) b

where

??? a.scorse>b.scorseand

??? a.stuId=b.stuId

--8? 查询所有课程成绩都小于分的同学的学号、姓名；

select

??? st.stuId,audition_scorsesc

where

??? st.stuId=sc.stuIdand

??? st.stuId not in (select stuIdfrom audition_scorse wherescorse>90);

group by st.stuId,st.stuName

--9? 查询没有学全所有课的同学的学号、姓名；

select

??? st.stuId,st.stuName

from

??? audition_student st,st.stuName

having

??? count(courId)< (selectcount(courId)from audition_course);

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!