department(dNo,dName,officeRoom,homepage)
student(sNo,sName,sex,age,dNo)
course(cNo,cName,cPNo,credit,dNo)
sc(sNo,cNo,score,recordDate)
Query
一、单表
from student
from student
from course
from student
from department
where homepage is null
二、聚集
from student
where age is not null and dNo is not null
from sc
where score is not null
from sc
where score is not null
from course
from sc
(1)查询“信息学院”所有学生学号与姓名;
select sno,sname
from student
where dno in
(select dno
from department
where dname='信息学院'
);
(2)查询“软件学院”开设的所有课程号与课程名称;
select cno,cname
from course
where dno in
(select dno
from department
where dname='软件学院'
);
(3)查询与“陈丽”在同一个系的所有学生学号与姓名;
select sno,sname
from student
where dno in
(select dno
from student
where sname='陈丽'
);
(4)查询与“张三”同岁的所有学生学号与姓名;
select sno,sname
from student
where sname!='钱多多'and age in
(select age
from student
where sname='钱多多'
);
(5)查询与“张三”同岁且不与“张三”在同一个系的学生学号与姓名;
select sno,sname
from student
where age in
(select age
from student
where sname='钱多多'
)
and dno not in
(select dno
from student
where sname='钱多多'
)
(6)查询学分大于“离散数学”的所有课程名称;
select cname
from course
where credit >
(select credit
from course
where cname='离散数学'
)
(7)查询选修了课程名为“组合数学”的学生人数;
select count(sno)
from sc
where cno in
(select cno
from course
where cname='组合数学'
)
(8)查询没有选修“离散数学”的学生姓名;
select sname
from student
where sno not in
(select sno
from sc
where cno in
(select cno
from course
where cname='离散数学'
)
)
(9)查询与“算法设计与分析”、“移动计算”学分不同的所有课程名称;
select cname
from course
where credit not in
(select credit
from course
where cname='算法设计与分析'or cname='移动计算'
)
(10)查询平均分大于等于90分的所有课程名称;
select cname
from course
where cno in
(select cno
from sc
group by cno
having avg(score)>=90
)
(11)查询选修了“离散数学”课程的所有学生姓名与成绩;
select sname,score
from student,sc
where student.sno=sc.sno and
cno in
(select cno
from course
where cname='离散数学'
)
(12)查询“王兵”所选修的所有课程名称及成绩;
select cname,score
from sc,course
where sno in
(select sno
from student
where sname='尹江月'
)
and sc.cno=course.cno
(13)查询所有具有不及格课程的学生姓名、课程名与成绩;
select sname,cname,score
from sc,course,student
where sc.score<60 and sc.sno=student.sno
and sc.cno=course.cno
(14)查询选修了“文学院”开设课程的所有学生姓名;
select sname
from student
where sno in
(select sno
from sc,course
where sc.cno=course.cno and
course.dno =
(select dno
from department
where dname='文学院'
)
)
(15)查询“信息学院”所有学生姓名及其所选的“信息学院”开设的课程名称。
select sname,cname
from student,course,sc
where student.sno=sc.sno and sc.cno=course.cno
and student.dno in
(select dno
from department
where dname='信息学院'
)
and course.dno in
(select dno
from department
where dname='信息学院'
)
四、综合
(1)查询所有学生及其选课信息(包括没有选课的学生);
select *
select second.cname
from course first, course second
where first.cpno=second.cno
and first.cname='形式语言与自动机'
(3)查询“形式语言与自动机”间接先修课课程名称;
select third.cname
from course first, course second ,course third
where first.cpno=second.cno and second.cpno=third.cno
select first.cname
from course first, course second
where first.cpno=second.cno
and second.cname='编译原理'
(5)查询间接先修课为离散数学的课程名称;
select first.cname
from course first, course second ,course third
where first.cpno=second.cno and second.cpno=third.cno
and third.cname='离散数学'
(6)查询所有没有先修课的课程名称;
select first.cname
from course first
where first.cpno is null
(7)查询所有没选修“形式语言与自动机”课程的学生姓名;
select sname
from student
where sno not in
(
select sno
from sc
where cno in
(
select cno
from course
where cname='形式语言与自动机'
)
)
(8)查询所有选修了“概率论”但没选修其先修课的学生姓名;
select sname
from student
where sno in
(select sno
from sc
where cno in
(select cno
from course
where cname='概率论'
)
)
and sno not in
(select sno
from sc
where cno in
(select cpno
from course
where cname='概率论'
)
)
(9)查询选修课程总学分大于等于28的学生姓名及其选修课程总学分;
select s.sNo,s.sName,v.sumCredit
from student s, (select sNo,SUM(credit)
from sc, course c
where sc.cNo=c.cNo
group by sc.sNo
having SUM(credit)>=28) as v(sNo,sumCredit)
where s.sNo=v.sNo;
(10)查询选修了3门以上课程且成绩都大于85分的学生学号与姓名;
select sno,sname
from student
where sno in
(select sno
from sc
group by sno
having count(*)>3 and min(score)>85)
(11)查询恰好选修了3门课并且都及格的学生姓名;
select sname
from student
where sno in
(select sno
from sc
group by sno
(12)查询人数多于6的学院名称及其学生人数;
select dname,dpt_count
from department ,
(select dno,count(*)
from student
group by dno
having count(*)>6)
as dpt(dpt_dno,dpt_count)
where department.dno=dpt.dpt_dno
(13)查询平均成绩高于王兵的学生姓名;
select sname
from student
where sno in
(select sno
from sc
group by sno
having avg(score)>
(select avg(score)
from sc
group by sno
having sno in
(select sno
from student
where sname='王兵'
)
)
)
and sname<>'王兵'
(14)查询所有选修了离散数学并且选修了编译原理课程的学生姓名;
select sname
from student
where sno in
(select sno
from sc
where cno in
(select cno
from course
where cname='离散数学'
)
)
and sno in
(select sno
from sc
where cno in
(select cno
from course
where cname='编译原理'
)
)
(15)查询软件学院离散数学课程平均分;
select avg(score)
from student,sc
where student.sno=sc.sno
and sc.cno in
(select cno
from course
where cname='离散数学'
)
and student.dno in
(select dno
from department
where dname='软件学院'
)
(16)查询年龄与“软件学院”所有学生年龄都不相同学生姓名及其年龄和学院;
select sname,age,dno
from student
where age <>all
(select age
from student
where dno in
(select dno
from department
where dname='软件学院'
)
and age is not null
)
(17)查询各学院选修同一门课人数大于4的学院、课程及选课人数;
select d.dname,c.cname,v.countOf
from course c,department d,
(select dno,cno,count(s.sno)
from student s, sc
where s.sno=sc.sno
group by dno,cno
having count(sc.sno)>4)
as v(dno,cno,countOf)
where c.cno=v.cno and d.dno=v.dno;
(18)查询仅仅选修了“高等数学”一门课程的学生姓名;(学号、姓名及所在学院名称)
select student.sname,student.sno,department.dname
from student,depatment
where student.sno not in
(
select sno
from sc
where cno in
(
select cno
from course
where cname<>'高等数学'
)
)
and student.sno in
(
select sno
from sc
where cno in
(
select cno
from course
where cname=='高等数学'
)
)
and student.dno=department.dno
(19)查询平均学分积小于70分的学生姓名。
SELECT student.sName,sub.sumcre
FROM student,
(SELECT sc.sNo AS ssno,
SUM(sc.score * course.credit)/SUM(course.credit) AS sumcre
FROM sc,course
WHERE course.cNo=sc.cNo AND sc.score IS NOT NULL
GROUP BY sc.sNo
HAVING SUM(sc.score * course.credit)/SUM(course.credit)<70
)
AS sub(ssno,sumcre)
WHERE sub.ssno=student.sNo;
(20)查询选修了“信息学院”开设全部课程的学生姓名。
(没有一门信息学院开的课他是不选修的 双重否定 全称量词转为存在量词)
SELECT student.sNo,student.sname
FROM student
WHERE NOT EXISTS
( SELECT *
FROM course
WHERE NOT EXISTS
(SELECT *
FROM sc
WHERE sc.sNo=student.sNo AND sc.cNo=course.cNo )
AND course.dNo=
(SELECT department.dNo
FROM department
WHERE department.dName='信息学院')
)
(21)查询选修了“杨佳伟”同学所选修的全部课程的学生姓名。
select sname
from student
where sno in
(select sno
from sc scx
where not exists
(select *
from sc scy
where scy.sno=
(select sno
from student
where sname='杨佳伟'
)
and not exists
(select *
from sc scz
where scz.sno=scx.sno and
scz.cno=scy.cno
)
)
)
and sname!='杨佳伟'
五、DDL练习
1、创建2张表,信息如下:
图书(编号,书名,作者,ISBN,出版社编号,版本,出版日期)。主码为编号,ISBN唯一。出版社编号为外码,参照出版社编号。
出版社(编号,名称,地址,电话)。主码为编号。
要求:(1)创建表的同时创建约束;
create table press
(Pnumber char(30)primary key, /*Press number 出版社编号 主码*/
pname char(30), /* pname 名称*/
address char(30), /*adress 地址*/
phone char(30) /*phone 电话*/
);
create table book
(bno char(9) primary key, /*bno是编号 主码*/
bname char(20), /*banme是书名*/
author char(20), /*author是作者名*/
ISBN char(30) unique, /*ISBN唯一*/
Pnumber char(30), /*Press number 出版社编号*/
vs char(20), /*version 版本*/
dateofpub char(20) , /*Date of publication出版日期*/
foreign key(Pnumber) references press(Pnumber) /*出版社编号(Pnumber)为外码,参照出版社表的编号(Pnumber)*/
);
(2)删除所创建的表;
drop table book cascade;
drop table press cascade;
(3)重新创建表,在表创建之后增加约束。
create table press
(Pnumber char(30), /*Press number 出版社编号 主码*/
pname char(30), /* pname 名称*/
address char(30), /*adress 地址*/
phone char(30) /*phone 电话*/
);
create table book
(bno char(9) , /*bno是编号 主码*/
bname char(20), /*banme是书名*/
author char(20), /*author是作者名*/
ISBN char(30) , /*ISBN唯一*/
Pnumber char(30), /*Press number 出版社编号*/
vs char(20), /*version 版本*/
dateofpub char(20) /*Date of publication出版日期*/
);
alter table press
add primary key(Pnumber);
alter table book
add primary key(bno),
add unique (ISBN),
add foreign key(Pnumber) references press(Pnumber)
;
2、
(1)分别向两张表中各插入2行数据。
insert
into press(pnumber,pname,address,phone)
values('A1001','A出版社','西安','165985626265');
insert
into press(pnumber,pname,address,phone)
values('B1002','B出版社','北京','165995626265');
insert
into book(bno,bname,author,isbn,pnumber,vs,dateofpub)
values('001','近代史概论','Mr.A','wkosc9844','A1001','第13版','2015.05.20');
insert
into book(bno,bname,author,isbn,pnumber,vs,dateofpub)
values('002','思修概论','Mr.B','ijm9844','B1002','第10版','2010.05.20');
(2)将其中一个出版社地址变更一下。
update press
set address='上海'
where pnumber='A1001';
(3)删除所插入数据。
delete
from book;
delete
from press;
3、
(1)创建一个软件学院所有选修了“离散数学”课程的学生视图,并通过视图插入一行数据。
create view is_s1(sno,sname)
as
select sno,sname,dno
from student
where student.sno in
(
select sno
from sc
where cno in
( select cno
from course
where cname='离散数学'
)
)
and dno in
(
select dno
from department
where dname='软件学院'
)
insert
into is_s1(sno,sname)
values('154612','张三');
(2)创建一个各门课程平均分视图。
create view is_s2(cno,cname,avgscore)
as
select sc.cno,course.cname,avg(score)
from sc,course
where sc.score is not null and
sc.cno=course.cno
group by sc.cno,course.cname
4、创建一张学生平均成绩表s_score(sNo,sName,avgscore),并通过子查询插入所有学生数据。
create table student_score
(
sNo char(6),
sName varchar(20),
avgscore int
);
insert
into student_score
select student.sno,student.sname,avg(sc.score)
from sc,student
where sc.score is not null and
sc.sno=student.sno
group by student.sno,student.sname
DCL
尝试将多条SQL语句组成一个事务执行,体验提交和回滚操作。
student(sNo,sName,sex,age,dNo)
course(cNo,cName,cPNo,credit,dNo)
sc(sNo,cNo,score,recordDate)
Query
一、单表
(1)查询所有年龄大于等于20岁的学生学号、姓名;
select sNo,sNamefrom student
where age>=20
(2)查询所有姓钱的男生学号、姓名、出生年份;
from student
where sName LIKE'钱%' and sex='男'
(3)查询所有学分大于3的课程名称;
select cnamefrom course
where credit>3
(4)查询所有没有被分配到任何学院的学生姓名;
select sNamefrom student
where dno is null
(5)查询所有尚未设置主页的学院名称。
select dnamefrom department
where homepage is null
二、聚集
(1)查询各个学院的平均年龄;
select dNO,avg(age)from student
where age is not null and dNo is not null
group by dNo
(2)查询每个学生选修课程的平均分;
select sno,avg(score)from sc
where score is not null
group by sno
(3)查询各课程的平均分;
select cno,avg(score)from sc
where score is not null
group by cno
(4)查询各学院开设的课程门数;
select dno,count(cno)from course
group by dno
(5)查询各门课程选修人数。
select cno,count(sno)from sc
group by cno
三、多表(1)查询“信息学院”所有学生学号与姓名;
select sno,sname
from student
where dno in
(select dno
from department
where dname='信息学院'
);
(2)查询“软件学院”开设的所有课程号与课程名称;
select cno,cname
from course
where dno in
(select dno
from department
where dname='软件学院'
);
(3)查询与“陈丽”在同一个系的所有学生学号与姓名;
select sno,sname
from student
where dno in
(select dno
from student
where sname='陈丽'
);
(4)查询与“张三”同岁的所有学生学号与姓名;
select sno,sname
from student
where sname!='钱多多'and age in
(select age
from student
where sname='钱多多'
);
(5)查询与“张三”同岁且不与“张三”在同一个系的学生学号与姓名;
select sno,sname
from student
where age in
(select age
from student
where sname='钱多多'
)
and dno not in
(select dno
from student
where sname='钱多多'
)
(6)查询学分大于“离散数学”的所有课程名称;
select cname
from course
where credit >
(select credit
from course
where cname='离散数学'
)
(7)查询选修了课程名为“组合数学”的学生人数;
select count(sno)
from sc
where cno in
(select cno
from course
where cname='组合数学'
)
(8)查询没有选修“离散数学”的学生姓名;
select sname
from student
where sno not in
(select sno
from sc
where cno in
(select cno
from course
where cname='离散数学'
)
)
(9)查询与“算法设计与分析”、“移动计算”学分不同的所有课程名称;
select cname
from course
where credit not in
(select credit
from course
where cname='算法设计与分析'or cname='移动计算'
)
(10)查询平均分大于等于90分的所有课程名称;
select cname
from course
where cno in
(select cno
from sc
group by cno
having avg(score)>=90
)
(11)查询选修了“离散数学”课程的所有学生姓名与成绩;
select sname,score
from student,sc
where student.sno=sc.sno and
cno in
(select cno
from course
where cname='离散数学'
)
(12)查询“王兵”所选修的所有课程名称及成绩;
select cname,score
from sc,course
where sno in
(select sno
from student
where sname='尹江月'
)
and sc.cno=course.cno
(13)查询所有具有不及格课程的学生姓名、课程名与成绩;
select sname,cname,score
from sc,course,student
where sc.score<60 and sc.sno=student.sno
and sc.cno=course.cno
(14)查询选修了“文学院”开设课程的所有学生姓名;
select sname
from student
where sno in
(select sno
from sc,course
where sc.cno=course.cno and
course.dno =
(select dno
from department
where dname='文学院'
)
)
(15)查询“信息学院”所有学生姓名及其所选的“信息学院”开设的课程名称。
select sname,cname
from student,course,sc
where student.sno=sc.sno and sc.cno=course.cno
and student.dno in
(select dno
from department
where dname='信息学院'
)
and course.dno in
(select dno
from department
where dname='信息学院'
)
四、综合
(1)查询所有学生及其选课信息(包括没有选课的学生);
select *
from student ,sc where student.sno=sc.sno
(2)查询“形式语言与自动机”先修课的课程名称;select second.cname
from course first, course second
where first.cpno=second.cno
and first.cname='形式语言与自动机'
(3)查询“形式语言与自动机”间接先修课课程名称;
select third.cname
from course first, course second ,course third
where first.cpno=second.cno and second.cpno=third.cno
and first.cname='形式语言与自动机'
(4)查询先修课为‘编译原理’的课程名称;select first.cname
from course first, course second
where first.cpno=second.cno
and second.cname='编译原理'
(5)查询间接先修课为离散数学的课程名称;
select first.cname
from course first, course second ,course third
where first.cpno=second.cno and second.cpno=third.cno
and third.cname='离散数学'
(6)查询所有没有先修课的课程名称;
select first.cname
from course first
where first.cpno is null
(7)查询所有没选修“形式语言与自动机”课程的学生姓名;
select sname
from student
where sno not in
(
select sno
from sc
where cno in
(
select cno
from course
where cname='形式语言与自动机'
)
)
(8)查询所有选修了“概率论”但没选修其先修课的学生姓名;
select sname
from student
where sno in
(select sno
from sc
where cno in
(select cno
from course
where cname='概率论'
)
)
and sno not in
(select sno
from sc
where cno in
(select cpno
from course
where cname='概率论'
)
)
(9)查询选修课程总学分大于等于28的学生姓名及其选修课程总学分;
select s.sNo,s.sName,v.sumCredit
from student s, (select sNo,SUM(credit)
from sc, course c
where sc.cNo=c.cNo
group by sc.sNo
having SUM(credit)>=28) as v(sNo,sumCredit)
where s.sNo=v.sNo;
(10)查询选修了3门以上课程且成绩都大于85分的学生学号与姓名;
select sno,sname
from student
where sno in
(select sno
from sc
group by sno
having count(*)>3 and min(score)>85)
(11)查询恰好选修了3门课并且都及格的学生姓名;
select sname
from student
where sno in
(select sno
from sc
group by sno
having count(*)=3 and min(score)>60
)
(12)查询人数多于6的学院名称及其学生人数;
select dname,dpt_count
from department ,
(select dno,count(*)
from student
group by dno
having count(*)>6)
as dpt(dpt_dno,dpt_count)
where department.dno=dpt.dpt_dno
(13)查询平均成绩高于王兵的学生姓名;
select sname
from student
where sno in
(select sno
from sc
group by sno
having avg(score)>
(select avg(score)
from sc
group by sno
having sno in
(select sno
from student
where sname='王兵'
)
)
)
and sname<>'王兵'
(14)查询所有选修了离散数学并且选修了编译原理课程的学生姓名;
select sname
from student
where sno in
(select sno
from sc
where cno in
(select cno
from course
where cname='离散数学'
)
)
and sno in
(select sno
from sc
where cno in
(select cno
from course
where cname='编译原理'
)
)
(15)查询软件学院离散数学课程平均分;
select avg(score)
from student,sc
where student.sno=sc.sno
and sc.cno in
(select cno
from course
where cname='离散数学'
)
and student.dno in
(select dno
from department
where dname='软件学院'
)
(16)查询年龄与“软件学院”所有学生年龄都不相同学生姓名及其年龄和学院;
select sname,age,dno
from student
where age <>all
(select age
from student
where dno in
(select dno
from department
where dname='软件学院'
)
and age is not null
)
(17)查询各学院选修同一门课人数大于4的学院、课程及选课人数;
select d.dname,c.cname,v.countOf
from course c,department d,
(select dno,cno,count(s.sno)
from student s, sc
where s.sno=sc.sno
group by dno,cno
having count(sc.sno)>4)
as v(dno,cno,countOf)
where c.cno=v.cno and d.dno=v.dno;
(18)查询仅仅选修了“高等数学”一门课程的学生姓名;(学号、姓名及所在学院名称)
select student.sname,student.sno,department.dname
from student,depatment
where student.sno not in
(
select sno
from sc
where cno in
(
select cno
from course
where cname<>'高等数学'
)
)
and student.sno in
(
select sno
from sc
where cno in
(
select cno
from course
where cname=='高等数学'
)
)
and student.dno=department.dno
(19)查询平均学分积小于70分的学生姓名。
SELECT student.sName,sub.sumcre
FROM student,
(SELECT sc.sNo AS ssno,
SUM(sc.score * course.credit)/SUM(course.credit) AS sumcre
FROM sc,course
WHERE course.cNo=sc.cNo AND sc.score IS NOT NULL
GROUP BY sc.sNo
HAVING SUM(sc.score * course.credit)/SUM(course.credit)<70
)
AS sub(ssno,sumcre)
WHERE sub.ssno=student.sNo;
(20)查询选修了“信息学院”开设全部课程的学生姓名。
(没有一门信息学院开的课他是不选修的 双重否定 全称量词转为存在量词)
SELECT student.sNo,student.sname
FROM student
WHERE NOT EXISTS
( SELECT *
FROM course
WHERE NOT EXISTS
(SELECT *
FROM sc
WHERE sc.sNo=student.sNo AND sc.cNo=course.cNo )
AND course.dNo=
(SELECT department.dNo
FROM department
WHERE department.dName='信息学院')
)
(21)查询选修了“杨佳伟”同学所选修的全部课程的学生姓名。
select sname
from student
where sno in
(select sno
from sc scx
where not exists
(select *
from sc scy
where scy.sno=
(select sno
from student
where sname='杨佳伟'
)
and not exists
(select *
from sc scz
where scz.sno=scx.sno and
scz.cno=scy.cno
)
)
)
and sname!='杨佳伟'
五、DDL练习
1、创建2张表,信息如下:
图书(编号,书名,作者,ISBN,出版社编号,版本,出版日期)。主码为编号,ISBN唯一。出版社编号为外码,参照出版社编号。
出版社(编号,名称,地址,电话)。主码为编号。
要求:(1)创建表的同时创建约束;
create table press
(Pnumber char(30)primary key, /*Press number 出版社编号 主码*/
pname char(30), /* pname 名称*/
address char(30), /*adress 地址*/
phone char(30) /*phone 电话*/
);
create table book
(bno char(9) primary key, /*bno是编号 主码*/
bname char(20), /*banme是书名*/
author char(20), /*author是作者名*/
ISBN char(30) unique, /*ISBN唯一*/
Pnumber char(30), /*Press number 出版社编号*/
vs char(20), /*version 版本*/
dateofpub char(20) , /*Date of publication出版日期*/
foreign key(Pnumber) references press(Pnumber) /*出版社编号(Pnumber)为外码,参照出版社表的编号(Pnumber)*/
);
(2)删除所创建的表;
drop table book cascade;
drop table press cascade;
(3)重新创建表,在表创建之后增加约束。
create table press
(Pnumber char(30), /*Press number 出版社编号 主码*/
pname char(30), /* pname 名称*/
address char(30), /*adress 地址*/
phone char(30) /*phone 电话*/
);
create table book
(bno char(9) , /*bno是编号 主码*/
bname char(20), /*banme是书名*/
author char(20), /*author是作者名*/
ISBN char(30) , /*ISBN唯一*/
Pnumber char(30), /*Press number 出版社编号*/
vs char(20), /*version 版本*/
dateofpub char(20) /*Date of publication出版日期*/
);
alter table press
add primary key(Pnumber);
alter table book
add primary key(bno),
add unique (ISBN),
add foreign key(Pnumber) references press(Pnumber)
;
2、
(1)分别向两张表中各插入2行数据。
insert
into press(pnumber,pname,address,phone)
values('A1001','A出版社','西安','165985626265');
insert
into press(pnumber,pname,address,phone)
values('B1002','B出版社','北京','165995626265');
insert
into book(bno,bname,author,isbn,pnumber,vs,dateofpub)
values('001','近代史概论','Mr.A','wkosc9844','A1001','第13版','2015.05.20');
insert
into book(bno,bname,author,isbn,pnumber,vs,dateofpub)
values('002','思修概论','Mr.B','ijm9844','B1002','第10版','2010.05.20');
(2)将其中一个出版社地址变更一下。
update press
set address='上海'
where pnumber='A1001';
(3)删除所插入数据。
delete
from book;
delete
from press;
3、
(1)创建一个软件学院所有选修了“离散数学”课程的学生视图,并通过视图插入一行数据。
create view is_s1(sno,sname)
as
select sno,sname,dno
from student
where student.sno in
(
select sno
from sc
where cno in
( select cno
from course
where cname='离散数学'
)
)
and dno in
(
select dno
from department
where dname='软件学院'
)
insert
into is_s1(sno,sname)
values('154612','张三');
(2)创建一个各门课程平均分视图。
create view is_s2(cno,cname,avgscore)
as
select sc.cno,course.cname,avg(score)
from sc,course
where sc.score is not null and
sc.cno=course.cno
group by sc.cno,course.cname
4、创建一张学生平均成绩表s_score(sNo,sName,avgscore),并通过子查询插入所有学生数据。
create table student_score
(
sNo char(6),
sName varchar(20),
avgscore int
);
insert
into student_score
select student.sno,student.sname,avg(sc.score)
from sc,student
where sc.score is not null and
sc.sno=student.sno
group by student.sno,student.sname
DCL
尝试将多条SQL语句组成一个事务执行,体验提交和回滚操作。