数据库4

第四章

1.select * from sc

2.select sname, sage from student where sdept = '计算机系'

3.select sno, cno,grade from sc where grade between 70 and 80

4.select sname, sage from student

 

where sdept = '计算机系' and sage between 18 and 20

5.select max( grade) from sc where cno = 'c001'

6.select max(sage)最大年龄,min (sage)最小年龄from student

 

where sdept=’计算机系’

7.select sdept, count (*)人数from student

 

Group by sdept

8.select cno, count(*)选课人数,max (grade)最高分from sc

 

Group by cno

9.select sno, count (*)选课门数，sum (grade) from sc

 

Group by sno order by count(*) asc

10.select sno, sum (grade)总成绩from sc

Group by sno having sum (grade) >200

11.select sname, sdept from student s join sc on s.sno=sc.sno

 

where cno = 'c02'

12.select sname , cno ,grade

 

From student s join sc on s.sno = sc.sno

 

where grade > 80

 

order by grade desc

13.select c.cno, cname sdept from course c left join sc on c.cno = sc.cno

 

where sc.cno is null

14.select c1.cname,c1.s emester from course cl

 

join course c2 on c1.semester = c2.semester

 

where c2.cname = 'Java' and cl.cn ame != 'Java'

15.select sl.sname,s1.sdept,s1.sage from student s1

 

join student s2 on s1.sage = s2.sage

 

where s2.sname ='李勇’ and s1.sname !='李勇'

16.（1）select sname, sdept from student

 

Where sno in (select sno from sc where cno = 'co01′)

（2）Select sno, sname , cno, grade from student s

 

Join sc on s.sno = sc.sno

 

where sdept =数学系' and sno in (select sno from sc where grade > 80)

（3）select sname from student s join sc on s.sno = sc.snowhere sdept ='计算机系’ and grade = (

 

Select max (grade) from sc join student s on s.sno=sc.sno

 

where sdept ='计算机系')

（4）select sname , sdept ,ssex, grade from student s

 

Join sc on s.sno = sc.sno

 

Join course c on c.cno = sc.cnowhere cname ='数据结构'

 

And grade in (

 

Select max (grade) from sc join course c on c.cno=sc.cno

 

whe re cn ame =数据结构’)

17.select sname, sdept from student

 

where sno not in (

 

select sno from sc join course c on c.cno = sc.cno

 

whe reenameAJavaoOCIn.com

18.select sname, ssex from student

 

where sdept ='计算机系'

 

And sno not in (

 

select sno from sc )

19.Create table test_t (cOL1 int,

 

CoL2 char (10) not null，coL3 char (10) )

 

Insert into test_t (COL2) values (' B1')

 

Insert into test_t (COL1 ,COL2) values (1 ,'B2’ )

 

Insert into test_t values ( 2,'B3’,NULL)

20.Delete from sc where grade < 50

21.Delete from course where cno not in (select cno from course)

22.Delete from sc

From sc join student s on s.sno = sc.snoJoin course c on c.cno = sc.cno

where cname = 'Java' and grade < 60and sdept ='计算机系'

23.Update Course set credit = credit +2

 

where semester = 2

24.Update course set credit = 3where cname = 'Java'

25.Update student set sage = sage + 1where sdept ='计算机系'

26.Update sc set grade = grade + 5

 

From student s join sc on s.sno =sc.snoJoin course c on c.cno = sc.cno

 

where sdept ='信息系’ and cname ='计算机文化学’

27.select sdept, count (*)人数into Dept_Age from student

 

where sage >=20

 

Group by sdept