数据透视表中级班练习八

JLxiangwei

数据透视表中级班练习八.rar (38.21 KB, 下载次数: 82)

2014-6-3 18:24 上传

点击文件名下载附件

lnjyzxp

练习八都出来了，老师辛苦了！我是箫风，这是我的另一个马甲，在向老师的帮助下，练习终于做完了！代码如下：

序号	要求
3	查询所有同学的学号、姓名、选课数、总成绩	select A.StuId,StuName,CountCourse,  SumScore from (select StuId,count(CourseId) as CountCourse,sum(Score) as  SumScore from [tblScore$] group by StuId) A left join [TblStudent$] B on  A.StuId=B.StuId
5	查询没学过“叶平”老师课的同学的学号、姓名；	select StuId,StuName  from [tblStudent$] where StuId not in (select StuId from (select  A.StuId,B.TeaId from [tblScore$] A left join [tblCourse$] B on  A.CourseId=B.CourseId) C where  TeaId=(select TeaId from [tblTeacher$] where TeaName="叶平"))
6	查询课程编号“002”的成绩比课程编号“001”课程低的所有同学的学号、姓名	select StuId,StuName  from [tblStudent$] where StuId   in(select  A.StuId from  (select * from [tblScore$] where  CourseId="002") A , (select * from [tblScore$] where  CourseId="001") B where A.StuId=B.StuId and  A.Score<B.Score)
8	查询没有学全所有课的同学的学号、姓名	select StuId,StuName  from [tblStudent$] where StuId in (select StuId from (select StuId,StuName, 0  as CourseId from [tblStudent$] union select StuId,null as StuName,CourseId  from [tblScore$]) group by StuId having count(CourseId)<(select count(*)  from [tblCourse$]))
9	查询至少有一门课与学号为“1001”的同学所学相同的同学的学号和姓名；	select StuId,StuName  from [tblStudent$] where StuId in (select distinct StuId from [tblScore$]  where CourseId  in (select CourseId  from [tblScore$] where StuId="1001"))

车仁静

3

1. SELECT B.StuId,StuName,CountCourse,SumScore
   
2. FROM [tblStudent$]A
   
3. RIGHT JOIN
   
4.         (SELECT StuId,COUNT(CourseId) AS CountCourse,SUM(Score) AS SumScore
   
5.          FROM [tblScore$]        
   
6.          GROUP BY StuId)B
   
7. ON A.StuId=B.StuId

复制代码

5

1. SELECT StuId,StuName
   
2. FROM [tblStudent$]
   
3. WHERE StuId NOT IN
   
4.         (SELECT B.StuId
   
5.          FROM [tblCourse$]A, [tblScore$]B,[tblStudent$]C,[tblTeacher$]D
   
6.          WHERE D.TeaId = A.TeaId
   
7.                 AND A.CourseId = B.CourseId
   
8.                 AND B.StuId = C.StuId AND TeaName='叶平')

复制代码

6

1. SELECT StuId,StuName
   
2. FROM [tblStudent$]
   
3. WHERE StuId IN
   
4.         (SELECT A.StuId FROM
   
5.                 (SELECT StuId,Score
   
6.                  FROM [tblScore$]
   
7.                  WHERE CourseId="002")A
   
8.         INNER JOIN
   
9.                 (SELECT StuId,Score
   
10.                  FROM [tblScore$]
    
11.                  WHERE CourseId="001")B
    
12.         ON A.StuId=B.StuId AND A.Score<B.Score)

复制代码

8

1. SELECT A.StuId,StuName
   
2. FROM [tblStudent$]A,[tblScore$]B  
   
3. WHERE A.StuId=B.StuId
   
4. GROUP BY A.StuId,StuName
   
5. HAVING COUNT(StuName)<(SELECT COUNT(1) FROM [tblCourse$])

复制代码

9

1. SELECT A.StuID,StuName
   
2. FROM (SELECT DISTINCT StuId
   
3.                     FROM [tblScore$]
   
4.         WHERE CourseId
   
5.         IN(SELECT CourseId
   
6.                 FROM [tblScore$]
   
7.                 WHERE StuId="1001"))A   
   
8. LEFT JOIN [tblStudent$]B
   
9. ON A.StuID=B.StuID

复制代码

lasharks

1

1. select t1.Stuid,t1.Stuname,t2.CountCourse,t2.TotalScore from [tblstudent$a:d]t1,(select stuid,count(*) as CountCourse,sum(score) as TotalScore from [tblscore$a:c] group by stuid)t2 where t1.stuid=t2.stuid

复制代码

2

1. select Stuid,StuName from [tblstudent$a:d] where stuid not in (select t3.stuid from [tblstudent$a:d]t3,[tblscore$a:c]t4,(select courseid from [tblcourse$a:c]t1,[tblteacher$a:b]t2 where t1.teaid=t2.teaid and teaname="叶平")t5 where t3.stuid=t4.stuid and t4.courseid=t5.courseid group by t3.stuid)

复制代码

3

1. select Stuid,StuName from [tblstudent$a:d] where stuid in (select t1.stuid from (select stuid,score from [tblscore$a:c] where courseid="001")t1 inner join (select stuid,score from [tblscore$a:c] where courseid="002")t2 on t1.stuid=t2.stuid and t1.score>=t2.score)

复制代码

4

1. select Stuid,StuName from [tblstudent$a:d] where stuid not in (select stuid from [tblscore$a:c] group by stuid having count(*)=(select count(courseid) from [tblcourse$a:c]))

复制代码

5

1. select Stuid,StuName from [tblstudent$a:d] where stuid in (select stuid from [tblscore$A:c] where courseid in (select courseid from [tblscore$a:c] where stuid="1001") group by stuid)

复制代码

xdragon

先做第一个的。。。
3

1. select a.StuId,StuName,count(CourseId) as CountCourseId,sum(Score) as SumScore from [tblStudent$] a left join [tblScore$] b on a.StuId=b.StuId group by a.StuId,a.StuName having count(CourseId)>0

复制代码

5

1. select StuId,StuName from [tblStudent$] where StuId not in (select a.StuId from (([tblStudent$]a right join [tblScore$]b on a.StuId=b.StuId) left join [tblCourse$]c on b.CourseId=c.CourseId) left join [tblTeacher$]d on c.TeaId=d.TeaId where TeaName='叶平')

复制代码

6

8

1. select a.StuId,StuName from [tblStudent$] a left join [tblScore$]b on a.StuId=b.StuId group by a.StuId,StuName having count(CourseId)<(select count(*) from [tblCourse$])

复制代码

9

1. select distinct a.StuId,StuName from [tblScore$]a left join [tblStudent$]b on a.StuId=b.StuId where CourseId in (select CourseId from [tblScore$] where StuId='1001')

复制代码

天空的雨

数据透视表中级班练习八-天空的雨答.rar (43.14 KB, 下载次数: 3)

2014-6-8 10:04 上传

点击文件名下载附件

答：
3.查询所有同学的学号、姓名、选课数、总成绩

select a.StuId,b.StuName,选课数,总成绩
from (select StuId,count(CourseId)  as 选课数,sum(Score) as 总成绩
      from[tblScore$]
      group by StuId)a
left join [tblStudent$]b on a.StuId=b.StuId


5.查询没学过“叶平”老师课的同学的学号、姓名

select t.StuId,t.StuName
from[tblStudent$]t
left join (select StuId
           from[tblScore$]
           where DCOUNT("CourseId","[tblCourse$]","TeaId = (select TeaId
                                                            from[tblTeacher$]
                                                            WHERE TeaName='叶平')
                                                   AND CourseId = '" & CourseId & "'")=1)t2  on t.StuId=t2.StuId
where t2.StuId is null


6.查询课程编号“002”的成绩比课程编号“001”课程低的所有同学的学号、姓名

select a.StuId,StuName   
from [tblStudent$]t
inner JOIN  (select a.StuId
            from[tblScore$]a               
            left join (select StuId,Score
                       from[tblScore$]
                       where CourseId="002")b  on a.StuId =b.StuId   
            WHERE  (a.CourseId="001")  
            and (a.Score>IIF(IsNumeric(B.Score),B.Score,0)))t2 on t.StuId=t2.StuId

8.查询没有学全所有课的同学的学号、姓名

select StuId,StuName
from[tblStudent$]  
where StuId   not in (select StuId
                        from[tblScore$]
                        group by StuId
                        having count(StuId)=(select count(*)
                                             from[tblCourse$]))

9查询至少有一门课与学号为“1001”的同学所学相同的同学的学号和姓名

select StuId,StuName
from [tblStudent$]A  
WHERE  Exists (SELECT  StuId
               FROM(SELECT StuId
                    FROM[tblScore$]
                    WHERE CourseId IN (SELECT CourseId
                                       FROM[tblScore$]
                                       WHERE StuId ="1001")  
                    GROUP BY StuId) B
               WHERE A.StuId=B.StuId)

xiaoni

第一题：select T1.Stuid,StuName,count(courseid) as CountCcurse,sum(score) as SumScore from [tblstudent$]t1,[tblscore$]t2 where t1.stuid=t2.stuid group by T1.stuid,stuname
第二题：select t5.stuid,t5.stuname from [tblstudent$]t5 where t5.stuid not in(SELECT stuid from(select t1.StuId,StuName from [tblstudent$]t1,[tblcourse$]t2,[tblscore$]t3,[tblteacher$]t4 where t1.stuid=t3.stuid and t3.courseid=t2.courseid and t2.teaid=t4.teaid and teaname="叶平" group by t1.stuid,stuname))或另一种嵌套查询
第二题嵌套：select stuid,stuname from [tblstudent$] where stuid not in(select stuid from [tblscore$] where courseid in(select courseid from [tblcourse$] where teaid in(select teaid from [tblteacher$] where teaname="叶平")))
第三题：select t3.stuid,stuname from [tblstudent$]t3,(select t1.stuid,t1.score,t2.score from (select stuid,score from [tblscore$] where courseid="002")t1,(select stuid,score from [tblscore$] where courseid="001")t2 where t1.stuid=t2.stuid and t1.score<t2.score)t4 where t3.stuid=t4.stuid
第三题另类方法：select t1.stuid,stuname from [tblstudent$]t1,(select stuid from [tblscore$] where courseid in("001","002") group by stuid having count(1)=2 and first(score)<last(score))t2 where t1.stuid=t2.stuid
第四题：select t1.stuid,stuname from [tblstudent$]t1 left join [tblscore$]t2 on t1.stuid=t2.stuid group by t1.stuid,stuname having count(courseid)<(select count(*) from(select distinct courseid from [tblscore$]))
第五题：select stuid,stuname from [tblstudent$] where stuid in(select stuid from [tblscore$] where courseid in(select courseid from [tblscore$] where stuid="1001"))

jio1ye

这个比较不难了，所以说回复的人多了一米米？ 5.数据透视表中级班_练习8.rar (41.81 KB, 下载次数: 1)

2014-7-10 20:51 上传

点击文件名下载附件

第一题：

1. Select T1.StuID, StuName, Count(CourseID) as CountCourse, Sum(Score) as SumScore from ([tblScore$] T1 left join [tblStudent$] T2 on T1.StuID= T2.StuID) group by T1.StuID, StuName

复制代码

第二题：这里代码取巧，直接用CourseID=“002”代表“叶平”懒得关联了

1. Select StuID,StuName from [tblStudent$] where StuID not in (Select StuID from [tblScore$] where courseId="002")

复制代码

第三题：这一题是答案错了吧。跟我做出来不一样

1. Select T1.StuID,StuName from ((Select T1.StuID from ((Select * from [tblScore$] where courseID="001") T1 left join (Select * from [tblScore$] where courseID="002") T2 on T1.StuID = T2.StuID) where T1.Score <T2.Score) T3 left join (Select * from [tblstudent$])T4 on T3.StuID = T4.StuID)

复制代码

第四题：这里直接用课程数17代表上了所有课，懒得计算课程数了

1. select t1.stuid,stuname from ((Select * from (Select T1.stuID,count(courseID) as coursecount from ([tblstudent$] T1 left join [tblscore$] T2 on T1.StuID= T2.StuID) group by t1.stuid) where coursecount <17) T3 left join [tblstudent$] T4 on T3.stuID =T4.stuID)

复制代码

第五题：慢慢上手了。。。。

1. Select T1.StuID,StuName from ([tblScore$] T1 left join [tblStudent$] T2 on T1.StuID = T2.StuID) where t1.courseID in (Select CourseID from [tblScore$] where StuID="1001") group by T1.StuID ,StuName

复制代码

zx_wl

结果3、

1. SELECT T1.STUID,STUNAME,COUNT(SCORE) AS COUNTCOURSE,SUM(SCORE) AS SUMSCORE FROM([TBLSTUDENT$]T1 LEFT JOIN [TBLSCORE$]T2 ON T1.STUID=T2.STUID) GROUP BY T1.STUID,STUNAME HAVING COUNT(SCORE)>0
   

复制代码

结果5、

1. select t1.StuId,StuName from([tblscore$]t1 left join [tblStudent$]t2 on t1.StuId=t2.StuId) where CourseId not in(select CourseId from [tblCourse$] where TeaId=(select TeaId from [tblTeacher$] where TeaName='叶平')) group by T1.StuId,StuName
   

复制代码

结果6、

1. select t1.StuId,StuName from(select t1.StuId from(select * from [tblScore$] where CourseId='002')t1,(select * from [tblScore$] where CourseId='001')t2 where t1.StuId=t2.StuId and t1.Score<t2.Score)t3 left join [tblStudent$]t4 on t3.StuId=t4.StuId
   

复制代码

结果8、

1. select t1.StuId,StuName from[tblStudent$]t1 left join [tblScore$]t2 on t1.StuId=t2.StuId GROUP by t1.StuId,StuName having count(CourseId)<(select count(*) from [tblCourse$])
   

复制代码

结果9、

1. select t1.StuId,StuName from [tblStudent$]t1,(select StuId from [tblScore$] where courseId in(select CourseId from [tblScore$] where StuId='1001'))t2 where t1.StuId=t2.StuId group by t1.StuId,StuName
   

复制代码