mysql习题

 

1）
具体思路：学号对应姓名，课程编号对应课程名，然后判断Sc表中学号对应的姓名，dsec调整降序输出，distinct去重/group by 去重
select distinct Sname from S,C,SC where S.Sno = SC.Sno and C.Cno = SC.Cno and C.Cname = 's数据库' order by Sname desc;
或者通过group by 进行只保证一个学生
select Sname from S,C,SC where S.Sno = SC.Sno adn C.Cno = SC.Cno and C.Cname = 's数据库' group by Sname desc;

2）
select S.Sno , sum(C.Ccredit)  from C , SC where C.Cno = SC.Cno and SC.Grade >=60  group by Sno having sum(C.Ccredit) > 80 ; 

3）
alter table SC add(Teacher char(20) not null);

4)
create view SCSum as select C.Cno , sum(SC.Grade) from SC group by Sno ;

5)
delete from Sc where not exists (select * from S.Sno = SC.Sno);