第3章习题参考解答.doc

-coursecreate table course(cno char(1) primary key,cname varchar(20) not null,credit smallint check (credit in (1,2,3,4,5,6,7)-classcreate table class(clno char(5) primary key,speciality varchar(20) not null,inyear char(4) not null,number integer check(number1 and number14 and sage 0 and gmark100),primary key (sno,cno)-insert into class (clno,speciality,inyear,number)values (00311,2000,120)insert into course values (1,4)insert into studentvalues (2000101,20,00311)update classset monitor=2000101where clno=00311insert into gradevalues (2000101,1,92)-select distinct cnofrom grade- 01311select *from studentwhere clno=01311 and ssex=- 0131101312-select sname,ssex,sagefrom studentwhere clno=01311 or clno=01312-select sname,ssex,sagefrom studentwhere clno in (01311,01312)-select sname,ssex,sagefrom studentwhere clno=01311unionselect sname,ssex,sagefrom studentwhere clno=01312- select *from studentwhere sname like %- -select numberfrom class join student on class.clno=student.clnowhere sname=-select numberfrom class,studentwhere class.clno=student.clno and sname=-select numberfrom classwhere clno=(select clnofrom studentwhere sname=)-select numberfrom classwhere clno in(select clnofrom studentwhere sname=)-select count(*) -count(sno)from studentwhere clno = (select clnofrom studentwhere sname=)- select avg=avg(gmark),max=max(gmark),min=min(gmark)from grade,coursewhere o=o and cname=- select count(distinct sno) as from grade- -select count(sno) as from grade,coursewhere o=o and cname=-select count(*) as from gradewhere cno=(select cnofrom coursewhere cname=)- 2000-select distinct snamefrom student inner join classon student.clno=class.clnojoin gradeon student.sno=grade.snowhere inyear=2000 and speciality=and gmark is null-Select distinct snameFrom student,grade,classWhere student.sno=grade.sno and student.clno=class.clnoand speciality= and inyear=2000 and gmark is null-Select snameFrom studentWhere clno in (select clnofrom classwhere speciality= and inyear=2000)and sno=any(select snofrom gradewhere gmark is null)-Select snameFrom studentWhere sno like 2000%and clno in (select clnofrom classwhere speciality=)and sno=any(select snofrom gradewhere gmark is null)-SELECT-select *from studentwhere clno=(select clnofrom studentwhere sname=) and sname-select *from studentwhere sno in(select distinct snofrom gradewhere cno in(select cnofrom gradewhere sno=(select snofrom studentwhere sname=)-25-select *from studentwhere sage between(select sagefrom studentwhere sname=) and 25-select s1.*from student s1,student s2where s1.sage between s2.sage and 25 and s2.sname= and s2.snos1.sno-select sno,snamefrom studentwhere sno in(select snofrom gradewhere cno=(select cnofrom coursewhere cname=)-Select sno,snamefrom studentwhere exists(select*from gradewhere sno=student.snoand cno=(select cnofrom coursewhere cname=)-select student.sno,snamefrom student,grade,coursewhere student.sno=grade.sno and o=oand ame=-1-select snamefrom studentwhere sno not in - not in all (select snofrom gradewhere cno=1)-select snamefrom studentwhere sno all(select snofrom gradewhere cno=1)-SQL SERVERselect snamefrom studentexceptselect snofrom gradewhere cno=1-select snamefrom studentwhere not exists(select *from gradewhere sno=student.sno and cno=1)- -select snamefrom studentwhere not exists(select *from coursewhere not exists(select *from grade)where grade.sno=student.snoand o=o)-select snamefrom studentwhere sno in ( select snofrom gradegroup by snohaving count(cno) ( select count(cno)from course ) )-SQL-3select sno,gmarkfrom gradewhere cno=3order by gmark desc-select *from studentorder by clno,sage desc-select cno,count(distinct sno) as from gradegroup by cno-select o,count(sno)from course c left outer join grade gon o=ogroup by o-select cno,count(distinct sno) as from gradegroup by cnounionselect cno,0from coursewhere cno all(select cnofrom grade)-3-Select sno ,count(cno) as From gradeGroup by snoHaving count(cno)=3-Select snoFrom gradeGroup by snoHaving count(*)=3-SQL-CLASSALTER TABLE CLASSadd avgage smallint-update classset avgage = casewhen clno=00311 then (select avg(sage) from student where clno=00311)when clno=00312 then (select avg(sage) from student where clno=00312)when clno=01311 then (select avg(sage) from student where clno=01311)end-update classset avgage=(select avg(sage) from studentwhere clno=class.clno)-select clno,avg(student.sage) as avg_ageinto class_avg_agefrom studentgroup by clno-create table t_avg(clno char(6) primary key,avg_age int)goinsert into t_avgselect clno,avg(sage)from studentgroup by clno-create view v_avg_ageasselect clno,avg(sage) as avg_agefrom studentgroup by clno-01311update gradeset gmark=0where sno in (select snofrom studentwhere clno=01311)-2001-delete gradefrom grade,student,classwhere grade.sno=student.sno and student.clno=class.clno and speciality= and inyear=2001-delete from gradewhere sno in(select snofrom studentwhere clno in(select clnofrom classwhere inyear=2001 and speciality=)-deletefrom gradewhere sno=(select snofrom studentwhere sname=)go-update classset number=number-1where clno=(select clnofrom studentwhere sname=)go-update classset monitor= casewhen monitor=(select sno from student where sname=) then endwhere clno in (select clno from student where sname=)-if (select monitor from class where clno=(select clno from student where sname=)= (select sno from student where snam