复杂查询（1）

1. 基本知识

2. 连接查询（含普通连接、自然连接和外连接）

本次编程任务是：

1.根据4-1SELECT语法规则，参照4-2关于普通连接、自然连接和外连接等复杂查询T-SQL方法示例，尽量先在本地或平台上完成示例内容练习；

2.“评测”通关。

 SET NOCOUNT ON
GO

USE JX
GO

----------------------BEGIN-------------------
--*****1、查询选修了‘3’号课程且成绩在70分以上的学生信息*****
select Student.*
from Student,SC
where Student.sno=SC.sno and SC.cno='3'and SC.grade>70
--*****2、查询选修了‘3’课程的学生姓名，成绩及任课教师姓名*****
select Student.Sname,SC.Grade,Teacher.Tname
from Student,SC,Teacher
where Student.sno=SC.sno  and SC.Tno=Teacher.Tno and SC.cno='3'
--*****3、列出所有参加了"操作系统"课程考试的学生姓名和成绩*****
select Student.Sname,SC.Grade
from Student,SC,Course
where Student.sno=SC.sno and Course.cno=SC.cno and Course.cname='操作系统' and  SC.Grade is not null
--*****4、查询已开出课程中选课人数不足4人的课程号及人数信息*****
select cno 课程号,count(sno) 选课人数
from SC
group by cno
having count(sno)<4

--*****5、查询有间接先修课的课程信息，以‘课程名’、‘间接先修课名’作列名*****
select a.cname 课程名, c.cname 间接先修课名
from Course a,Course b,Course c
where a.cpno=b.cno and b.cpno=c.cno

--*****6、查询所有同学的基本信息及选课情况（包含未选课同学的信息）*****
select Student.Sno,Student.Sname,Student.Ssex,Student.Sbirth,Student.Sdept,Student.Total,SC.Cno,SC.Tno,SC.Grade
from Student left outer join SC on  Student.Sno=SC.Sno
----------------------END-----------------------

GO

3. 嵌套循环（含相关子查询和不相关子查询）

编程任务是：

根据4-1SELECT语法规则，参照4-2关于嵌套查询的使用方法示例，尽量先在本地或平台上完成示例内容练习；

SET NOCOUNT ON
GO
USE JX
GO

----------------------BEGIN-------------------
--*****1、查询没有选修课程的同学的基本信息*****

select *
from Student
where Sno  not in
(
  select distinct Sno
  from SC
   )
--*****2、查询选修课程数低于3门的学生信息（需含未选课的学生信息）*****

select *
from Student
where Sno in(
            select Sno
            from SC
            group by Sno
            having count(Cno)<3
            )
or Sno  not in(
              select distinct Sno
              from SC
               )

--*****3、查询所有不比“电子工程”系所有学生年龄大的其他系的学生信息*****
select * 
from Student
where Sbirth >all(
select Sbirth
from Student
where Sdept ='电子工程'
)and Sdept<>'电子工程'

--*****4、查询选修‘3’号或‘4’号课程的同学的学号和姓名*****
select Sno,Sname
from Student
where Sno in (
              select Sno
     from SC
     where Cno='3'or Cno='4'
     )


--*****5、查询选修‘3’号和‘4’号课程的同学的学号和姓名*****
select Sno,Sname
from Student
where Sno in  (
              select Sno
              from SC
              where  Cno='3'
              )
intersect 
select Sno,Sname
from Student
where Sno in  (
              select Sno
              from SC
              where  Cno='4'
              )

----------------------END-----------------------

GO

4. 集合查询（并、交、差）

编程要求：

根据4-1SELECT语句关于集合查询的语法规则，参照4-2关于并集、交集和差集的查询使用方法示例，尽量先在本地或平台上完成示例内容练习；

SET NOCOUNT ON
GO
USE JX
GO

----------------------BEGIN-------------------
--*****1、查询选课人数不足4人的课程号及人数信息（含无学生选修的课程）****
select Cno 课程号,count(Sno) 选课人数
from SC
group by Cno
having count(Sno)<4

union 

select Cno 课程号,count(Cno)-1 选课人数
from Course
group by  Cno
having count(Cno)>=1 and Cno not in(
         select distinct Cno
         from SC
         )

--*****2、查询讲授‘3’号或 ‘4’号课程的老师姓名和课程名*****
select distinct Tname ,Cname
from Teacher,SC,Course
where (SC.Cno='3'or SC.Cno='4') and Teacher.Tno=SC.Tno and Course.Cno=SC.Cno



--*****3、查询同时选修了‘信息系统’和‘数据结构’课程的学生名单*****

select Sname
from Student
where Sno in(
                select Sno 
                from SC
                where Cno in (
                select Cno
                from Course
                where Cname='数据结构'
                    ) 
         intersect
             select Sno 
             from SC
             where Cno in (
                            select Cno
                            from Course
                            where Cname='信息系统'
                                )
                                

    
)

order by sno DESC
----------------------END-----------------------

GO 

5. 基于派生表的查询

编程要求：

根据3-1SELECT语句关于ORDER BY子句的语法规则，参照3-2关于单表的排序使用方法示例，尽量先在本地或平台上完成示例内容练习；

SET NOCOUNT ON
GO
USE JX
GO

----------------------BEGIN-------------------
--*****1、查询年龄小于所在系平均年龄的学生信息及所在系学生的平均年龄*****

SELECT Sno,Sname,Ssex,Sbirth,Sdept,Total,avg_year
FROM Student,(SELECT Sdept avg_Sdept,Avg(year(getdate())-year(Sbirth)) avg_year from Student 
 Group by Sdept) AS Student1
WHERE Student.Sdept=Student1.avg_Sdept
and year(getdate())-year(Sbirth)<Student1.avg_year


--*****2、查询所有讲授‘2’号课程的教师姓名*****
select*,Tno
from Teacher
where Tno in (
   select Tno
   from SC
   where Cno='2'
   )



----------------------END-----------------------

GO 

6. 复杂查询综合应用

编程要求：

结合前面的内容，综合运用复杂查询语句进行查询

SET NOCOUNT ON
GO
USE JX
GO

----------------------BEGIN-------------------
--*****1、查询所有学生和教师的姓名（允许重名）并标识人员类型（学生/老师）*****
alter table Student add  S_type  char(4) 
 go
 alter table Teacher add  T_type  char(4)
 go
 
 update Student
 set S_type ='学生'
 update Teacher
 set T_type ='教师'

select Sname 姓名,S_type 人员类型     
from Student

union

select Tname 姓名,T_type 人员类型
from Teacher

--*****2、查询没有选修2号课程的同学的学号、姓名和系别*****

select Sno,Sname,Sdept
 from Student
 where Sno not in (
    select Sno 
    from SC
    where Cno='2'
    )

----------------------END-----------------------

GO