SQL语句练习题答案.doc

1、 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 s

2、sex=; 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 3-4- 80 (2 5, 675

3、7( 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 studentv

4、alues( 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=1

5、001 ; 4 select sno, sname from studentwhere sdept in (jsj,sx,wl) and sage25 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(s

6、age),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

7、 sc group by sno having count(*)2 ;select cno from sc where grade85 group by cno having count(*)=10 ; select sno from sc group by sno having avg(grade)60 ;select sno from sc where grade60 group by sno having count(*)2 ;select cno from student,sc where student.sno=sc.sno and sdept=jsj ; a:select snam

8、e 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 grade80 and cname= - b:selec

9、t sname from student where sno in (select sno from sc where grade80 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 grou

10、p 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

11、 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

12、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

13、 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 dis

14、tinct 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

15、, 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 grou

16、p 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)

17、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- c2-select ,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

18、.=. ;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

19、 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

20、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 w

21、here 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

22、. 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- c2-select ,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