SQL语句练习题答案.doc

school student , sc, course school student (sno,sname,ssex,sage,sdept) course(cno,cname,cpno,ccredit) sc(sno,cno,grade) 1. studentsno2 coursecno 3 scsnocno1 create table student , sc, course sql23 student4schoolstudent5studentsbirthdate datetimedelete1. jsj delete from student where sdept=jsj and ssex=; 2 delete from sc where cno in (select cno fromcourse where cname=);update1. 0001 : jsj2. 1 2 1001 93 3 1select 11921, 22 3 10014jsj sxwl 25, 510sno,cno,101-10 1 11-202 30-39390-10010 6 student distinct 7000110011002 1 2jsj 34 5 1001,1002 6 80 7 2 8 1085 9 10 1 jsj 21002 (2 34 80 (2 5, 6757( 1. 2. 1002 3. 2 top , any , all *4 100110025 10022 6 7 jsj 198 10019085 9 10 1. create table student (snochar(6), sname varchar(8), ssexchar(2), sagesmallint, sdept varchar(15), primary key(sno);create table sc(snochar(6), cno char(4),grade decimal(12,2), primary key(sno,cno);insert into studentvalues( 4001,20,sx)delete from studentdrop table studentalter table student add sbirthdate datetime1. select sno, sname, sage from studentwhere ssex= and sage between 19 and 21order by sage desc; 2 select sno, ssexfrom studentwhere sname like _% ; 3 select sno, cnofrom scwhere grade is null and cno=1001 ; 4 select sno, sname from studentwhere sdept in (jsj,sx,wl) and sage>25 group by sdept;select sno, cno, grade/10.0+1 as levelfrom sc ;select distinct sdept from student ; select grade from scwhere sno=0001 and (cno=1001 or cno=1002) ;select count(*) from student where sname like % ; select avg(sage),max(sage) from student where sdept=jsj ; select cno,sum(grade),avg(grade),max(grade),min(grade) from sc group by cnoorder by avg(grade) desc ;select cno, avg(grade) from sc where cno in(1001,1002) group by cno ;select sc.sno ,avg(grade) from scgroup by sc.snohaving avg(grade)>80 ;select sno from sc group by sno having count(*)>2 ;select cno from sc where grade>85 group by cno having count(*)=10 ; select sno from sc group by sno having avg(grade)<60 ;select sno from sc where grade<60 group by sno having count(*)>2 ;select cno from student,sc where student.sno=sc.sno and sdept=jsj ; a:select sname from student,sc where student.sno=sc.sno and cno=1002b:select sname from student where sno in (select sno from sc where cno=1002)select sno,grade from sc,coursewhere o=o and cname= and grade <60 a:select sname from student ,sc,coursewhere student.sno=sc.sno and o=o and grade>80 and cname= b:select sname from student where sno in (select sno from sc where grade>80 and cno in (select cno from course where cname= ) select sno,sname,avg(grade) from sc,studentwhere student.sno=sc.snogroup by student.sno having avg(grade)<60a:select sname from student where ssex= and sno in(select sno from sc group by sno having avg(grade)>75)b:select sname from sc,student where student.sno=sc.sno and ssex=group by student.sno having avg(grade)>75select student.sno,sname,cno,grade from student left join sc on student.sno=sc.sno and ssex=select count(*) from student where sno in( select sno from sc group by sno havingavg(grade)<60)select sname from student where sno not inselect sno from sc where cno=1002student0001 aax 0002 bb0003 ccx sc0001 1001 0001 1002 0002 1001 0003 1002select sname from student where not exists(select* from sc where cno=1002 and sc.sno=student.sno)a:select top 1 sno,avg(grade) from sc group by sno order by avg(grade) desc b:select sno, avg(grade) from sc group by snohaving avg(grade)=(select top 1 avg(grade) from scgroup by sno order by avg(grade) desc) c:select sno, avg(grade) from sc group by snohaving avg(grade)>=all ( select avg(grade) from sc group by sno)select sname from student where not exists(select * from course where cno in(1001,1002) andnot exists(select * from sc where sno =student.sno and cno=o) ) a:select top 1 sno from sc cno=1002 order by grade desc b:select sno from sc where cno=1002 and grade >=all (sqlsql1select * from 2select , from where =3select distinct from 4180select , from where =1 and >80 order by desc5select from where <>select from where not =select from where !=67080select * from where >=70 and <=80select * from where between 70 and 80select * from where not between 70 and 80713select , from where =1 or =3select , from where in (1,3)select , from where not in (1,3)898select * from where like 98%select * from where like 98_ _ _ _select * from where not like 98%9()select , from where is nullselect , from where is not null10select sum() as from 11select ,avg() as from group by 12select ,avg() as ,max() as ,;min() as ,count() as from group by 11select , from , where .=. and =121select ., from s, x where s.=x. and =13150select .,sum() as from ,;where .=. group by . having sum()>1501select a.,b., from a, b where a.<>b. and a.=b.212select x. from x, y where x.=y. and x.=1 and y.=23rate.dbf1 c(2)2 c(2) n(8,4) n(8,4).dbf1 c(4)2 c(4) n(8,4) n(8,4).dbf c(10) c(10)rate12rate12select a. as 1,b. as 2,;, from a, b;where a.=.1 and b.=.2 into table rate4 c2 c6 c2select ,s.,e. from s, e where s.=e.11select , from , where .=. and =1select , from inner join on .=. where =1 innerselect , from join on .=. where =12select ., from ,;where .=. and.=. ;and .=.select ., from join join join ;on .=. on .=. on .=.3select from where not in (select from )select from right join on .=. where .<>.select from left join on .=. where .<>.exists1select from where =(select from where =) existsselect from where exist (select * from ;where = and .=.)2select * from where in; (select from where =) select * from where exist (select * from where = ; and .=.)anysomeall121select , from where =2 and >(select min() from where =1)anysomeanysomesqlsql1select * from 2select , from where =3select distinct from 4180select , from where =1 and >80 order by desc5select from where <>select from where not =select from where !=67080select * from where >=70 and <=80select * from where between 70 and 80select * from where not between 70 and 80713select , from where =1 or =3select , from where in (1,3)select , from where not in (1,3)898select * from where like 98%select * from where like 98_ _ _ _select * from where not like 98%9()select , from where is nullselect , from where is not null10select sum() as from 11select ,avg() as from group by 12select ,avg() as ,max() as ,;min() as ,count() as from group by 11select , from , where .=. and =121select ., from s, x where s.=x. and =13150select .,sum() as from ,;where .=. group by . having sum()>1501select a.,b., from a, b where a.<>b. and a.=b.212select x. from x, y where x.=y. and x.=1 and y.=23rate.dbf1 c(2)2 c(2) n(8,4) n(8,4).dbf1 c(4)2 c(4) n(8,4) n(8,4).dbf c(10) c(10)rate12rate12select a. as 1,b. as 2,;, from a, b;where a.=.1 and b.=.2 into tab