第四章
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
标签:grade,sno,数据库,sc,cno,where,select From: https://www.cnblogs.com/yindantong/p/18248339