第四章
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