SQL实验第七第八大题

create database ud_sno;

use ud_sno;

 

create table S

(

Sno char(6) primary key,

Sname char(10) not null unique,

Ssex char(6) not null check(Ssex in ('male','female')),

Sage smallint check(Sage>16),

Sdept char(8) default 'JSJ' #http://www.w3school.com.cn/sql/sql_default.asp

);

 

create table C

(

Cno char(4) primary key,

Cname char(16),

Cpno char(4),

Ccredit smallint check(Ccredit > 0 and Ccredit < 6),

check(Cpno!=Cno)

);

 

 

create table SC

(

Sno char(6),

Cno char(4),

primary key (Sno,Cno),

foreign key (Sno) references S(Sno) on update cascade, #级联更新

foreign key (Cno) references C(Cno),

Grade smallint

);

 

 

/*http://www.w3school.com.cn/sql/sql_update.asp*/

#7.1

update S set Sdept = 'JSJ' where Sno = '0078'; 

 

 

#7.2

update SC set Grade = 82 

where Cno = '1001'

and Sno = (select Sno from S where Sname = 'Jimmy') ;

 

 

#7.3

update SC set Grade = Grade + 2 where Cno = 

(select Cno from C where Cname = 'SJJG'); 

 

 

/*http://www.w3school.com.cn/sql/sql_delete.asp*/

#7.4 

delete from S where Sdept = 'JSJ'; 

 

 

#7.5

delete from SC where Cno =

(select Cno from C where Cname = 'SJKYL');

 

 /*http://www.w3school.com.cn/sql/sql_select.asp

http://www.w3school.com.cn/sql/sql_orderby.asp*/

#8.1

select Sno,Sname,Sage from S where Ssex = 'female' order by Sage desc; 

#8.2 #http://www.w3school.com.cn/sql/sql_func_count.asp

select count(Sno) from S where Sname like 'Geoff%'; 

 #8.3

select Sno from SC where Cno = '1003' and Grade is null; 

 #8.4

select Sno,Sname from SC where Sdept in('JSJ','SX','WL') order by Sdept, Sno; 

 

#按某列一类的需要group by，http://www.w3school.com.cn/sql/sql_groupby.asp  #8.5

select avg(Grade) from SC group by Cno;

select max(Grade) from SC group by Cno;

select min(Grade) from SC group by Cno;

select sum(Grade) from SC group by Cno;

 

#http://dev.yesky.com/230/2669730.shtml    #8.6

select avg(Grade),Sno from SC group by Sno having avg(Grade)>80 ;

  

#8.7

select S.Sname

from S

inner join SC

on SC.Sno = S.Sno

group by S.Sname

having count(SC.Cno)>5;

 

 

#8.8

select distinct SC.Cno

from SC

inner join S

on S.Sno = SC.Sno

where S.Sdept = 'JSJ';

 

 

#8.9

#嵌套

select Sname

from S

inner join SC

on SC.Sno = S.Sno

where SC.Cno = '1005';

 #非嵌套

select Sname

from S

where Sno =

(select Sno from SC where SC.Cno = '1005');

 

#8.10

#题目不知所谓

 

#8.11

#非嵌套

select Sname from S

where Sno =

(select Sno from SC where Cno =

(select Cno from C where Cname = 'SJKYL')

and Grade>80)

 #嵌套

#等待补完

 

#8.12

select S.Sname, SC.Sno, avg(SC.Grade)

from SC

inner join S

on SC.Sno = S.Sno

group by SC.Sno

having avg(SC.Grade) < 100;

  

#8.13

select count(Sno) from S where Sno in(
select Sno from SC
where Sno not in(
select Sno from SC
group by Sno
having avg(Grade) < 60
)
);

 

 

#8.14  http://dbajun.iteye.com/blog/117945

select Sname from S where Sname not in

(select distinct S.Sname

from S

inner join SC

on SC.Sno = S.Sno

where SC.Cno = '1005');

  

#8.15 方法较为作弊，用了top因为是mysql所以用了limit再加上order by desc的语句

select avg(SC.Grade), S.Sno

from SC

inner join S

on SC.Sno = S.Sno

group by SC.Sno

having avg(SC.Grade)

order by avg(SC.Grade) desc

limit 1;

  

#8.16 较复杂，待做。

第三大题

alter table S Add address char(60);
alter table S add inDate datetime;
alter table S delete column indate;
alter table S drop column address;
alter table SC add constraint Grade check(Grade < 100 and Grade > 0);
alter table C alter column Cname char(14);
alter table C add constraint ud_sno foreign key (Cpno) references C (Cno);