几个表的联合查询练习

Student(S#,Sname,Sage,Ssex) 学生表

Course(C#,Cname,T#) 课程表

StudentScore(S#,C#,score) 成绩表

teacher(T#,Tname) 教师表

1.查询"001"课程比"002"课程成绩高的所有学生的

学号


2.查询平均成绩大于60分的同学的学号和平均成绩
(group by 的作用)

select StudentNum ,avg(score) from 

studentscore where( select avg(Score) as 

avgScore from studentscore)>60 group by 

StudentNum

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




4 //查询姓"李"老师的个数
select count(*) from teacher where 

teacher.TeacherName like '李%'

1	89.0000
30000	83.0000
40000	85.0000
200000	82.0000
1000000	83.0000


5. 查询没学过"叶平" 老师课的学号,姓名
// "没学过"  执行步骤 1. 先查出选了课的id,2.

从所有学员中排出选了课的(^^^not in ( 1 )

select * from(select student.StudentNum 学号, 

student.StudentName 姓名 from student) as k


 

where 学号 not in ( (select studentNum test2 

from (select sc.StudentNum  studentNum, 

c.TeacherNum tn1 from studentscore sc,
course 

c where sc.CourseNum=c.CourseNum) as a, 

(select t.teacherName 

teacherName,t.TeacherNum tn2 from course 

c,teacher t where
t.TeacherNum=c.TeacherNum   

) as b where tn1=tn2 and TeacherName in ('叶

平')))


6.查询学过"叶平"老师所教的课的同学的学号,姓名

select * from(select student.StudentNum 学号, 

student.StudentName 姓名 from student) as k


 

where 学号  in ( (select studentNum test2 

from (select sc.StudentNum  studentNum, 

c.TeacherNum tn1 from studentscore sc,
course 

c where sc.CourseNum=c.CourseNum) as a, 

(select t.teacherName 

teacherName,t.TeacherNum tn2 from course 

c,teacher t where
t.TeacherNum=c.TeacherNum   

) as b where tn1=tn2 and TeacherName in ('叶

平')))


7.查询各科成绩最高和最低分:以如下形式显示:课

程ID,最高分,最低分
// on 条件的使用可以找出 编码相同的项,最大最

小有函数,根据课程编码group by 就可以得到每一

项的最大或最小值 ,用inner join 则可以将两个表

合并在一起

select 课程,最高分, 最低分 from (select max

(score) as 最高分 ,sc.CourseNum t1

from 

studentscore sc group by t1) a  inner join 

(select min(score) as 最低分 ,sc.CourseNum 课

程

from studentscore sc group by sc.CourseNum) 

b on a.t1=b.课程  group by 课程




参考了如下写法
select name1 name, java, jdbc, 

hibernate,total
  from (select sc1.name name1, sc1.mark java
   from student_course2 sc1
   where sc1.course='java') as a,
   (select sc2.name name2, sc2.mark jdbc
   from student_course2 sc2
   where sc2.course='jdbc') as b,
   (select sc3.name name3, sc3.mark hibernate
   from student_course2 sc3
   where sc3.course='hibernate') as c,
 (select sc4.name name4,sum(sc4.mark) total
 from student_course2 sc4 group by sc4.name) 

as d
  where name1=name2 and name2=name3 and 

name3=name4 order by total ASC;

//
select a.* from 表a  a inner join (select  

max(FInterid) as maxf from 表a group by 

fitemid) b on a.finterid=b.maxf

//
SELECT Persons.LastName, Persons.FirstName, 

Orders.OrderNo
FROM Persons
FULL JOIN Orders
ON Persons.Id_P=Orders.Id_P
ORDER BY Persons.LastName


//修改列名字段的语句

alter table student change column Sname 

StudentName varchar(20)


//从两个表中查出总数 按表中一个字段排序
select count(studentscore.CourseNum) from 

studentscore,student where 

student.StudentNum=studentscore.StudentNum 

group by student.StudentName