数据库应用程序设计解答

最新问答

• 多啦X梦521

  二、 更新teachers_courses表：请写出符合下列要求的SQL语句（不要仅针对给出的），把所有SQL语句放入文本文件Exam02.SQL中提交。
  
  1． 目前的teachers_courses中只包含了必修课的教师任课情况，假设所有编号IT开始的都上了所有编号开始为SPE的选修课，请在teachers_courses插入相应的记录，写出insert语句。
  insert into teachers_courses
  select teachers.teacherid,courses.courseid, ‘’ from teachers,courses
  where teachers.teacherid like 'IT%' and courses.courseid like 'SPE%' and courses.attri=1
  
  2． 假设学校规定年龄小于25岁(当前年份-出生年份)的教师不能上必修课，请删除teachers_courses中的相应记录，写出delete语句。
  Delete from teachers_courses where teacherid in (select teacherid from teachers where year(getdate())-year(birthday)<25) and courseid in (select courseid from courses where attri=1)
  
  3． 教师的工作量为其担任教学课程的学分总和，写出更新teachers.workload的update语句。
  update teachers set workload=(select sum(courses.grade)
  from teachers_courses,courses
  where teachers_courses.courseid=courses.courseid
  and teachers_courses.teacherid=teachers.teacherid)
  
  三、 写出select语句，实现下列查询：把所有SQL语句放入文本文件Exam03.SQL中提交。
  1． 查询所有教师的任课情况，包括教师编号、教师姓名、性别、任课课程名、学分，按教师性别排序，女教师在前。(没有任课的教师可以不显示)。性别显示“男”和“女”。
  
  select a.teacherid,a.teachername,
  case when a.sex=0 then '男' when a.sex=1 then '女' end,
  b.coursename,b.grade
  from teachers a,courses b,teachers_courses c
  where a.teacherid=c.teacherid and b.courseid=c.courseid order by 3 desc
  
  2． 上述查询要求包括没有任课的教师编号和姓名，在任课课程名中显示“未任课”，学分列显示为0。
  select a.teacherid,a.teachername,
  case when a.sex=0 then '男' when a.sex=1 then '女' end,
  isnull(c.coursename,'未任课'),isnull(c.grade,0)
  from (teachers a left join teachers_courses b on a.teacherid=b.teacherid) left join courses c on b.courseid=c.courseid
  order by 3 desc
  
  3． 查询所有没有任课的教师编号、姓名和性别。
  select teacherid,teachername,sex from teachers where teacherid not in (select teacherid from teachers_courses)
  
  4． 查询每个教师的总的学时数，包括教师编号、姓名、性别、年龄(当前年份-出生年份)和总学时
  select a.teacherid,a.teachername,a.sex,year(getdate())-year(a.birthday), sum(b.grade) from teachers a,courses b,teachers_courses c
  where a.teacherid=c.teacherid and b.courseid=c.courseid
  group by a.teacherid,a.teachername,a.sex,year(getdate())-year(a.birthday)
  
  5． 对上述查询，仅查询总学时数小于8的并且年龄小于50岁的教师的编号、姓名、性别、年龄和总学时。
  select a.teacherid,a.teachername,a.sex,year(getdate())-year(a.birthday), sum(b.grade) from teachers a,courses b,teachers_courses c
  where a.teacherid=c.teacherid and b.courseid=c.courseid and year(getdate())-year(a.birthday)<50
  group by a.teacherid,a.teachername,a.sex,year(getdate())-year(a.birthday)
  having sum(b.grade)<8
  6． 查询总学时最多的教师编号，姓名和总学时(可使用视图)。
  create view sumgrade (teacherid,grade)
  as
  select c.teacherid,sum(b.grade) from courses b,teachers_courses c
  where b.courseid=c.courseid
  group by a.teacherid
  
  select teacherid,teachername,grade from teschers,sumgrade
  where teachers.teacherid=sumgrade.teacherid and grade=(select max(grade) from sumgrade)
  或
  select a.teacherid,a.teachername,sum(b.grade)
  from teachers a,courses b,teachers_courses c
  where a.teacherid=c.teacherid and b.courseid=c.courseid
  group by a.teacherid,a.teachername
  having sum(b.grade)=(select max(grade) from sumgrade)
  
  四、 编写表值函数dept_teachlist，参数为部门编号，返回该部门所有教师的教师编号、教师姓名、必修课总学时、选修课总学时（不包括未任课的教师）。写出验证该函数的select语句（参数为‘IT01’）。把完成的程序放入文本文件Exam04.SQL中提交。
  
  CREATE FUNCTION dept_teachlist (@deptid CHAR(6)) RETURNS @tb_deptlist TABLE (teacherid1 CHAR(6) PRIMARY KEY,teachername varchar(10),num1 INT,num2 INT) AS
  BEGIN
  --插入该部门教师信息
  INSERT into @tb_deptlist
  SELECT teacherid,teachername,0,0 FROM teachers WHERE departmentid=@deptid
  --计算必修课总学时数
  UPDATE @tb_deptlist SET num1=(SELECT sum(b.grade) FROM courses b,teachers_courses c
  WHERE b.courseid=c.courseid and b.attri=0 and b.teacherid=teacherid1)
  --计算选修课总学时数
  UPDATE @tb_deptlist SET num2=(SELECT sum(b.grade) FROM teachers a,courses b,teachers_courses c
  WHERE a.teacherid=c.teacherid and b.courseid=c.courseid and b.attri=1 and a.teacherid=teacherid1)
  RETURN
  END
  
  select * from dept_teachlist('IT01')

  浏览 290赞 156时间 2023-07-12