大连理工大学软件学院——数据库上机题2

Using the university schema that you have write the following queries. In some cases you

might need to insert extra data to show the effect of a particular feature.

1. Insert each student as an instructor of department ‘拳脚学院’, with salary=40000

2. Now delete all the newly added "instructors" above (note: already existing instructors who happened to have salary=40000 should not get deleted)

3. Update the salary of each instructor to 10000 times the number of course sections they have taught.

4. The university rules allow an F grade to be overridden by any pass grade (for example, A).

Now, lists students who have fail grades that have not been overridden. For each student as such, information displayed (in one row) should involve:

l Identifier of student

l Name of student

l Count of F grades that have not been overridden.

5. In one result, list the instructors who have never taught any courses and the students who have never registered for any courses. For each person, information displayed (in one row) should involve:

l Id of the person

l Name of the person

l Role of the person. The value of role should be ‘student’ or ‘instructor’.

[sql] 
insert into  instructor  
  
select S.id,S.name,'拳脚学院',40000  
  
from student as S  
  
where S.id not in (select instructor.id from instructor)  
  
   
  
delete from instructor  
  
where name+id in (select name+id from student)  
  
   
  
sp_help instructor  
  
sp_helpconstraint instructor  
  
alter table instructor  
  
drop constraint CK__instructo__salar__1920BF5C  
  
update instructor  
  
set salary= 10000*  
  
(select COUNT(*) from teaches where instructor.id=teaches.id)  
  
   
  
with S(id,course_id)   
  
as (select distinct id,course_id   
  
from takes   
  
where grade='F'),  
  
R(id,course_id)   
  
as (select distinct T.id,T.course_id  
  
from takes as T,S  
  
where T.id=S.id   
  
and T.course_id=S.course_id  
  
and T.grade<>'F' )  
  
select student.ID,student.name,count(*) as FailedTimes  
  
from (select * from S except select *from R) as target,student  
  
where target.id=student.id  
  
group by student.ID,student.name  
  
   
  
select S.id,S.name,'Student' as Role  
  
from Student as S  
  
where S.id not in(select takes.id from takes)  
  
union  
  
select T.id,T.name,'Instructor' as Role  
  
from instructor as T  
  
where T.id not in (select teaches.id from teaches)