ylb：多表的连接与练习（第三方关联表的应用）

-- =============================================
-- ylb:
-- ylb目的：多表之间的连接，第三方关系的运用
--       练习和使用
-- 13:13 2011/12/7
-- =============================================
USE master
GO

-- Drop the database if it already exists
IF  EXISTS (
 SELECT name 
  FROM sys.databases 
  WHERE name = N'db'
)
DROP DATABASE db
GO

CREATE DATABASE db
GO
go
use db
go
--1,课程表
create table course
(
 c_no int identity(1,1) primary key, --编号
 c_name varchar(20) not null,  --课程名称
 c_time int check(c_time>=0)  --课时
)
go
--2，学生表
create table stu
(
 s_no int identity(1,1) primary key, --编号
 s_name varchar(10) not null,  --姓名
 age int check(age>=15 and age<=80) --年龄
)
go
--3，成绩表
create table sco
(
 s_no int not null references stu(s_no), --学生编号
 c_no int not null references course(c_no), --课程编号
 sco int     --成绩
)
go
--联合主键
alter table sco add constraint pk_s_c primary key(s_no,c_no)
go
insert into course values('计算机软件基础',16)
insert into course values('C语言程序设计',64)
insert into course values('网站设计综合技术',56)
insert into course values('SQL Server',32)
insert into course values('ASP',64)
insert into course values('JAVA',100)
insert into course values('高级SQL Server',16)
insert into course values('ASP DoNet',100)
select * from course
go
insert into stu values('张三',29)
insert into stu values('李司',39)
insert into stu values('王二',22)
insert into stu values('麻子',21)
insert into stu values('赵钱',25)
insert into stu values('孙李',24)
insert into stu values('周吴',20)
insert into stu values('郑王',20)
insert into stu values('合乎',29)
insert into stu values('人五',21)
insert into stu values('人六',17)
go
select * from course
select * from stu
go
insert into sco values(1,1,70)
insert into sco values(1,2,74)
insert into sco values(1,3,60)
insert into sco values(1,4,69)
go
insert into sco values(2,1,60)
insert into sco values(2,2,71)
insert into sco values(2,3,56)
insert into sco values(2,4,78)
go
insert into sco values(3,1,50)
insert into sco values(3,2,57)
insert into sco values(3,3,74)
insert into sco values(3,4,72)
go
insert into sco values(4,1,55)
insert into sco values(4,2,45)
insert into sco values(4,3,65)
insert into sco values(4,4,72)
go
insert into sco values(5,6,75)
insert into sco values(6,6,85)
insert into sco values(7,6,82)
go
select * from course
select * from stu
select * from sco
go
--一、单表的查询 
go
--1、查询年龄最大的三个同学的信息；
--2、在成绩表中，查询课程编号为1号的所有记录

--3、在成绩表中，查询课程编号为1号的纪录，并按成绩降序排序

--4、在成绩表中，查询1号学生的总分，平均分，最高分和最低分，

--5、在成绩表中，查询单科成绩最高的学号，课程编号和成绩

--6、在成绩表中，查询每个学生参加考试的总分及参加考试的数量

--7、在成绩表中，查询每个学生的总分，平均分，最高分，最低分，并在总分降序排序

--8、在成绩表中，查询总分最高的学生的学号，总分

--9、在成绩表中，查询学号为2号的学生的学号，总分，平均分

--10、在成绩表中，查询每一门课程的考试人数，总分，最高分，并按总分升序排序

--二、多表连接查询

--1、查询所有举行考试的课程名称；
select * from course
select * from sco
go
select * from course c inner join sco s
on c.c_no=s.c_no
go
--结果
select distinct c_name from course c inner join sco s
on c.c_no=s.c_no
go
--2、查询所有举行考试的课程名称以及对应的学号和分数；
select c_name,s_no,sco from course c inner join sco s
on c.c_no=s.c_no
go
--3、查询所有参加考试的学生姓名以及其对应的课程编号和分数；
select s_name,c_no,sco from stu st inner join sco s
on st.s_no=s.s_no
go
--4、查询每个参加考试的学生的姓名，考试科目数量，总分，平均分；
select s_name,COUNT(*) '考试科目数量',SUM(sco) '总分',AVG(sco) '平均分' 
from stu inner join sco
on stu.s_no=sco.s_no
group by s_name
go
--5、查询总分最高的同学的姓名，和总分
select top 1 s_name,SUM(sco) from stu inner join sco
on stu.s_no=sco.s_no
group by s_name
order by SUM(sco) desc

go
--6、查询总分前三名的同学的姓名
select top 3 s_name from stu inner join sco
on stu.s_no=sco.s_no
group by s_name
order by SUM(sco) desc
go
--7、查询参加考试的同学的姓名，科目名称及分数；
select s_name,c_name,sco from stu inner join sco
on stu.s_no=sco.s_no
inner join course 
on sco.c_no=course.c_no
go
--8、查询 张三同学 的姓名，年龄，课程编号，成绩，及对应的课程名称
select s_name,age,sco.c_no,sco from stu inner join sco
on stu.s_no=sco.s_no
inner join course 
on sco.c_no=course.c_no
where s_name='张三'
go
--9、查询 张三同学的姓名参加考试的总分，平均分以及参加考试的数量
select SUM(sco) '总分',AVG(sco) '平均分',COUNT(*) '考试数量' from stu 
inner join sco
on stu.s_no=sco.s_no
inner join course 
on sco.c_no=course.c_no
where s_name='张三'
go
--10、查询所有学生的姓名，以及其对应的分数；
select s_name,sco from stu
inner join sco on stu.s_no=sco.s_no
go
--11、查询所有的课程名称，以及其对应的学号和成绩；
select c_name,s_no,sco from course
inner join sco on course.c_no=sco.c_no
go
--12、查所有的同学的姓名，其对应的成绩，以及所有课程名称；
select s_name,sco,c_name from stu
inner join sco on stu.s_no=sco.s_no
inner join course on sco.c_no=course.c_no
go
--13、假设，所有同学都参加所有课程的考试，那么总共需要多少份试卷；
select COUNT(*) from course cross join stu

go
--左外连接
select * from stu 
left join sco on stu.s_no=sco.s_no