sql operation_显示所有同学的学号、姓名和系别类型,系别类型分为文科系和理科系,其中数学系、计

--一、表的创建

1. 使用SQL语句， 创建CourseDB数据库，并在其中创建学生信息表Student和班级表class，class表的Clno与Student表的Clno建立联系。 Student表和class表的结构及数据类型如下所示。

 CREATE DATABASE CourseDB;

 CREATE TABLE Student (

    Sno CHAR(8) PRIMARY KEY,

    Clno CHAR(6) NOT NULL,

    Sname CHAR(8) NOT NULL UNIQUE,

    Ssex CHAR(2) CHECK (Ssex IN ('男', '女')),

    Sbir DATETIME,

    Sdept CHAR(25) DEFAULT '大数据学院',

    Sage INT CHECK (Sage BETWEEN 18 AND 25)

);

CREATE TABLE class (

    Clno CHAR(6) PRIMARY KEY,

    Clname VARCHAR(20) NOT NULL,

    Special VARCHAR(20),

    Dno CHAR(8) NOT NULL

);

2. 使用SQL语句， 创建CourseDB数据库，并在其中创建学生信息表Student和选修课程情况表SC，SC表的Sno与Student表的Sno建立联系。 Student表和SC表

的结构及数据类型如下所示。

-- 创建CourseDB数据库

CREATE DATABASE CourseDB;

-- 切换到CourseDB数据库

USE CourseDB;

-- 创建学生信息表Student

CREATE TABLE Student (

    Sno CHAR(8) PRIMARY KEY,

    Clno CHAR(6) NOT NULL,

    Sname CHAR(8) NOT NULL UNIQUE,

    Ssex CHAR(2) CHECK (Ssex IN ('男', '女')),

    Sbir DATETIME,

    Sdept CHAR(25) DEFAULT '大数据学院',

    Sage INT CHECK (Sage >= 18 AND Sage <= 25)

);

-- 创建选修课程情况表SC

CREATE TABLE SC (

    Sno CHAR(8) PRIMARY KEY,

    Cno CHAR(5) NOT NULL,

    Score NUMERIC(5,1) CHECK (Score >= 0 AND Score <= 100),

    FOREIGN KEY (Sno) REFERENCES Student (Sno)

);

3. 使用SQL语句， 在CourseDB数据库中创建一个教师信息表teacher和一个部门情况表department， teacher表的dno与department表的dno建立联系。 teacher表和department

的结构及数据类型如下所示。

-- 创建department表

CREATE TABLE department (

    Dno CHAR(8) PRIMARY KEY,

    Dname CHAR(20) DEFAULT '大数据学院'

);

-- 创建teacher表

CREATE TABLE teacher (

    Tno CHAR(8) PRIMARY KEY,

    Tname CHAR(8) NOT NULL UNIQUE,

    Tsex CHAR(2) CHECK(Tsex IN ('男', '女')),

    Tbir DATETIME,

    Ttitle CHAR(10),

    Dno CHAR(8) REFERENCES department(Dno),

    jbgz MONEY,

    gwjt MONEY,

    yfgz MONEY

);

其中，department表有Dno和Dname两个字段，Dno是主键，Dname设置默认值为“大数据学院”。teacher表有Tno、Tname、Tsex、Tbir、Ttitle、Dno、jbgz、gwjt、yfgz这9个字段，

其中Tno是主键，Tname设为非空唯一，Tsex只能是“男”或“女”，Dno是外键，引用department表的Dno字段。jbgz、gwjt、yfgz分别表示基本工资、岗位津贴和应发工资。

--1.查询选修课程01001或02002，成绩在80至95之间，学号为20080xxx的学生的学号、课程号和成绩。

select  sno,cno,score

from SC

where (cno='01001'or cno='02002')and(score between 80 and 95)and(sno like'20080___')

--2．查询选修成绩不为空值的选课信息。

select *

from sc

where score is not null

--3. 查询姓名中含有“良”的学生。

select *

from student

where sname like'%良%'

--4. 查询每个学生选修的课程的总分、平均分，并按学号升序排序。

select sno,sum(score) 总分,avg(score) 平均分

from sc

group by sno

order by sno

--5．查询学生选修的课程平均分数大于等于70分的课程号、成绩。

select cno,score

from sc

group by cno,score

having avg(score)>=70

--6．查询各门课程的选修人数，并按人数的升序排序。

select cno,count(*) 选修人数

from sc

group by cno

order by 选修人数

--7．查询学生人数不足4人的班级及其相应的学生数。

select clno,count(*) 学生数

from student

group by clno

having count(*)<4

--8．查询选修了2门课程以上的学生的学号及其总成绩，查询结果按总成绩降序排序。

select sno,sum(score) 总成绩

from sc

group by cno

having count(*)>=2

order by 总成绩 desc

--9. 查询选修了课程01001且成绩在70分以下或成绩在90分以上的学生的姓名、课程名称和成绩。

select sname,cname,score

from student s join sc on s.sno=sc.sno

               join course c on sc.cno=c.cno

where sc.cno='01001' and (score<70 or score>90)

--10. 查询有学生选修的每门课程的课程号、课程名、平均分数、学分及任课教师姓名，且平均分数大于等于70分。

select sc.cno,cname,avg(score) 平均分,credits,tname

 from sc join course on sc.cno=course.cno

         join teaching on teaching.cno=course.cno

          join teacher on teaching.tno=teacher.tno

group by sc.cno,cname,credits,tname

having avg(score)>70

--11. 查询职称为副教授的教师编号、所在部门名称、职称、应发工资（Tjbgz+Tgwjt）。

select tno,dno,ttitle,Tjbgz+Tgwjt 应发工资

from teacher

where ttitle='副教授'

--12. 查询每个学生的学号、姓名、专业、选课成绩的总分、平均分，并按总分的降序排序。

select sc.sno,sname,special,score,sum(score) 总成绩,avg(score) 平均分

from student join class on student.clno=class.clno

             join sc on student.sno=sc.sno

group by sc.sno,sname,special,score

order by score desc

--13. 查询课程号、学分、任课教师、选课成绩总分，并按课程号升序排序。

select sc.cno,credits,tname,sum(score)

from sc join course on sc.cno=course.cno

        join teaching on course.cno=teaching.cno

         join teacher on teaching.tno=teacher.tno

group by sc.cno,credits,tname

order by cno

--14. 查询选修了“计算机网络”的学生的学号、姓名、成绩。

select sc.sno,sname,score

from student join sc on sc.sno=student.sno

where exists(select cno from course where cname='计算机网络')

--15. 与 ‘王一夫’ 同班，且年龄大于 ‘赵良明’ 的学生的信息。

select *

from student

where clno=(select clno from student where sname='王一夫')

      and sage>(select sage from student where sname='赵良明')

--16. 查询没有选修课程的学生信息。

select *

from student

where sno not in(select distinct sno from sc)

17. 查询有学生参加考试的课程号，课程名，学分，平均分。

SELECT Course.Cno, Course.Cname, Course.Credit, AVG(SC.Score) AS AvgScore

FROM Course

INNER JOIN SC ON Course.Cno = SC.Cno

GROUP BY Course.Cno, Course.Cname, Course.Credit

--18. 查询选修了课程“01001”并且与‘关鹏’同班的学生信息。

select *

from student join sc on student.sno=sc.sno

where cno='01001' and clno=(select clno from student where sname='关鹏')

select *

from student

where sno in(select sno from sc where cno='01001')

intersect

select *

from student

where clno=(select clno from student where sname='关鹏')

--19. 查询与李闲教师职称相同并且基本工资大于‘王大有’的教师号、姓名、职称和基本工资。

select tno,tname,ttitle,tjbgz

from teacher

where ttitle=(select ttitle from teacher where tname='李闲')

      and tjbgz>(select Tjbgz from teacher where tname='王大有')

--20.查询有学生选修的每门课程的课程号、课程名、平均分数、学分及任课教师姓名，且平均分数大于等于70分。

select sc.cno,cname,sum(score),credits,tname

from course join sc on course.cno=sc.cno

        join teaching on teaching.cno=sc.cno

        join teacher on teaching.tno=teacher.tno

group by sc.cno,cname,credits,tname

having score not null and avg(score)>=70

--三、数据更新

--1. 将选修了课程‘01001’学生的分数加2。

update sc

set score=score+2

where cno='01001'

--2. 将Course表中‘C语言程序设计 ’学分数修改成与‘数据库原理及应用’的学分数相同。

update course

set credits=(select credits from course where cname='数据库原理及应用')

where cname='C语言程序设计'

--3. 将Student表中‘关鹏’同学的姓名修改为‘关红’，并且性别改为女。

update student

set sname='关红',ssex='女'

where sname='关鹏'

--4. 将 ‘计算机网络’学分数修改成与‘数据库原理及应用’的学分数相同。

update course

set credits=(select credits from course where cname='数据库原理及应用')

where cname='计算机网络'

--四、视图

--1. 在sc上建立一个sc_view的学生视图，再通过该视图把所有01001的课程成绩增加5分。

create view sc_view

select *

from sc

update sc_view

set score=score+5

where cno='01001'

--2.在老师信息表Teacher上建立一个职称是副教授、

--基本工资大于等2000元的教师视图view_teacher。

--并通过视图teacher_view：

--①往教师表里插入一条记录；②删除姓“王”的教师；

--③把年龄大于等于35的教师的基本工资上调300元。

create view view_teacher

as

select *

from teacher

where ttitle='副教授'and tjbgz>=2000

--①往教师表里插入一条记录

insert into view_teacher(tno,tname)

values('02005','王宇')

--②删除姓“王”的教师；

delete from view_teacher

where tname like '王%'

--③把年龄大于等于35的教师的基本工资上调300元。

update view_teacher

set Tjbgz=tjbgz+300

where (year(GETDATE())-YEAR(tbir))>=35

--3. 在Student表上创建一个视图stu_view，

--视图包括这个表的sno, clno ,ssex字段，

--然后通过这个视图向基本表Student中插入一条记录('20100103','201001','男'),

--分析执行结果。

create view stu_view

as

select sno,clno,ssex

from student

insert into stu_view

values('20100103','201001','男')

--插入失败，插入的数据中，sname为null，基本表sname列不能为空

--五、程序设计

--1. 查询所有同学的学号、姓名和系别类型，系别类型分为文科系和理科系，其中数学系、计算机系、电子与信息工程系属于理科系，其余属于文科系。

select  sno 学号,sname 姓名,

    case dname

      when '数学系' then '理科系'

      when '计算机系' then '理科系'

      when '电子与信息工程系' then '理科系'

      else '文科系'

    end 系别类型

from student s join Class on s.Clno=class.clno

               join Department on Class.Dno=Department.dno

--2.查询教师的编号，姓名，性别，职称，职称类别：教授或副教授为高级职称，讲师为中级职称，否则为低级职称.

select tno 编号,tname 姓名,tsex 性别,

      case ttitle

      when '教授' then '高级职称'

      when '副教授' then '高级职称'

      when '讲师' then '中级职称'

      else '低级职称'

      end 职称类别

from teacher

--2. 查询学生的学号，姓名，班级，选修课程名及其成绩，并把成绩转换成‘优秀’（>=90）、‘良好’ （>=80）、 ‘中等’ （>=70）、 ‘及格’ （>=60） ‘不及格’ （<60）等级。

    select s.sno 学号,sname 姓名,clno 班级,cname 选修课程名,score 成绩,

    case

    when score>=90 then '优秀'

    when score>=80 then '良好'

    when score>=70 then '中等'

    when score>=60 then '及格'

    when score<60  then '不及格'

    end 成绩等级

    from student s join sc on s.sno=sc.sno

             join  course c on sc.cno=c.cno

--3. 判断是否有教师的岗位津贴少于1200，若有则将所有教师的岗位津贴增加100，直到所有教师的岗位津贴都在1200以上。

while exists(select * from teacher  where tgwjt<1200)

   update teacher

   set tgwjt=tgwjt+100

--4. 声明变量@x, @y为整型，并进行赋值，如果@x>@y，程序终止执行，否则程序等待10秒钟，查询男学生信息，并且在'17:20'时间点查询全体学生信息保存到stu_info表。

declare @x int,@y int

select @x=9,@y=10

 if @x>@y

  return

 else

   begin

    waitfor delay '00:00:05'

     select *

      from Student

  --  waitfor time '17:42:00'

     select * into stu_info

      from Student

   end

--六、存储过程

--1. 创建修改指定课程的学分的存储过程p1，输入参数为：课程号和修改后的学分，修改后的学分默认值为3。并执行此存储过程。

create proc p1

@cno char(8),

@credits int=3

as

  update course

  set credits=@credits

  where cno=@cno

 

  exec p1 '01001',4

--2. 创建存储过程p2：输入一个学生的学号，通过输出参数返回该学生的姓名和平均分。并执行此存储过程。

create proc p2

@sno char(8)

  as

   select sname,sum(score) 平均分

   from student join sc on sc.sno=student.sno

   group by sname,sc.sno

   having sc.sno=@sno

   exec p2 '20070101'

--3. 创建存储过程p3:查询指定班号、指定性别的学生中年龄大于等于指定年龄的学生的情况。班号的默认值为“200801”，默认性别为“男”，默认的年龄为20。并执行此存储过程。

create proc p3

@clno char(8)='200801',

@ssex char(2)='男',

@sage int =20

as

  select *

  from student

  where clno=@clno and ssex=@ssex and sage>=@sage

  exec p3

--4. 创建存储过程p4:查询学生的学号、姓名、课程号、课程名、学分，将学生所在的班级作为输入参数。执行此存储过程。

create proc p4

@clno char(8)

as

  select sc.sno,sname,sc.cno,cname,credits

  from sc join student on sc.sno=student.sno

          join course on sc.cno=course.cno

  where clno=@clno

  

   exec p4 '200801'

--5. 创建存储过程p5：查询学生的学号、姓名、课程号、课程名、学分，将学生所在的系作为输入参数。并执行此存储过程。

create proc p5

@dno char(20)

as

  select sc.sno,sname,sc.cno,cname,credits

  from class join student on class.clno=student.clno

          join sc on sc.sno=student.sno

          join course on sc.cno=course.cno

  where dno=@dno

  exec p5 '0001'

--6. 创建存储过程p6：统计指定课程的平均成绩和选课人数，将统计的结果作为输出参数。并执行此存储过程。

create proc p6

@cno char(8),

@avg int output,

@n int output

as

  select @avg=AVG(score),@n=COUNT(*)

  from sc

  where cno=@cno

  group by cno

  declare @avg int,@n int

  exec p6 '02001',@avg output,@n output   select @avg 平均成绩,@n 选课人数

--七、触发器

--1. 建立一个触发器tr1，当删除Teacher表中的教师信息时，如果要删除的教师的职称是副高以上，则将禁止删除此教师。

create trigger tr1

on teacher

for delete

as

  if exists(select * from deleted  where ttitle='教授' or ttitle='副教授')

  begin

    rollback

    print '删除的教师的职称是副高以上，禁止删除此教师'

  end

  delete from teacher

  where tno='02006'

--2. 建立一个触发器tr2，当修改Teacher表中职称为讲师的教师的基本工资时，如果修改的值大于2000，则不能修改此教师的基本工资。并给出触发事件。

create trigger tr2

on teacher

for update

as

  if exists(select * from updated where tjbgz>2000 and ttitle='讲师')

  begin

   rollback

   print'修改的值大于2000,不能修改此教师的基本工资'

  end

  update teacher

set tjbgz=tjbgz+2000

where tno='02002'

--3.创建触发器tr3，当向Student表中添加数据时，如果添加的学生的年龄不在12岁～60岁之间，则将禁止插入此学生。

create trigger tr3

on student

for insert

as

  if exists(select * from inserted where sage not between 12 and 60)

  begin

   rollback

   print '年龄不在12到60之间，不能添加'

  end

  insert into student

values('20070104','200701','赵伟婷','女','2002-11-19',2)

--4. 为SC建立一个insert触发器tr4，当SC中插入的记录中的学号是student表中没有的学号，则提示“学生表中无此学生，不能插入该记录”，否则提示记录插入成功。并给出触发事件。

create trigger tr4

on sc

for insert

as

  declare @sno char(20)

  select @sno=sno from inserted

  if not exists(select * from student where @sno=sno)

  begin

   rollback

   print'学生表中无此学生，不能插入该记录'

  end

  else

  print'记录插入成功'

  insert into sc

values('20070105','01001',82)

--5. 在SC表中定义一个限制学生选课门数不能超过5门的触发器tr5并给出触发事件。

create trigger tr5

on sc

for insert

as

  declare @m int,@sno char(8)

  select @sno=sno from inserted

  select @m=Count(*) from sc where sno=@sno

  if @m>=5

   begin

    rollback

    print'学生选课门数不能超过5门的触发器'

   end

  insert into sc

  values('20080101','03002',90)

--6.在SC表中定义一个更新和插入触发器tr6，在此触发器中保证成绩在0~100范围内。并给出触发事件。

create trigger tr6

on sc

for update,insert

as

   if exists(select * from inserted where score not between 0 and 100)

   begin

   rollback

   print'成绩不在范围内'

   end

   insert into sc

values('20070101','01003',-20)

update sc

 set score=score+2

 where sno='20070101'