课综合练习题答案

第三章综合练习设有三个关系：SC(sno,cno,grade)试用SQL语句表示下列查询语句3、查询学号为S3学生所学课程的课程名与任课教师名S(sno,sname,sex,age)

4、查询至少选修LIU老师所授课程中一门课程的女学生姓名C(cno,cname,teacher)

5、查询WANG同学不学的课程的课程号6、查询至少选修两门课的学生学号7、查询全部学生都选修的课程的课程号与课程名课程的学生学号。1、查询LIU老师所授课程的课程号和课程名2、查询年龄大于23岁的男8、查询选修课程包含LIU老师所授全部学生的学号和姓名An

Introduction

to

Database

System第三章综合练习设有三个关系：SC(sno,cno,grade)10、求LIU老师所授课程的每门课程的平均成绩S(sno,sname,sex,age)

11、检索姓名以L打头的所有学生的姓名和年龄。C(cno,cname,teacher)

12、求年龄大于所有女同学年龄的男学生姓名和年龄。13、往关系C中插一个课程元组（’C8’,’VC++’,’BAO’）14、把选修LIU老师课程的女同学选课元组15、把低于所有课程总平均成绩的男同学成绩提高5%.试用SQL语句表示下列查询语句9、统计每门课程的学生选修人数（超过10人的课程才统计）。

全部删去。要求显示课程号和人数，查询结果按人数降序排列，若人数相同，按课程号升序排列。An

Introduction

to

Database

System综合练习答案S(sno,sname,sex,age)SC(sno,cno,grade)C(cno,cname,teacher)1、查询LIU老师所授课程的课程号和课程名涉及到的表：

C(cno,cname,teacher)Select

cno,cnamefrom

Cwhere

teacher=‘LIU’An

Introduction

to

Database

System综合练习答案S(sno,sname,sex,age)SC(sno,cno,grade)C(cno,cname,teacher)2、查询年龄大于23岁的男学生的学号和姓名涉及到的表：S(sno,sname,sex,age)方法一：一般的查询Select

sno,snamefrom

Swhere

age>23

and

sex=‘M’An

Introduction

to

Database

System综合练习答案S(sno,sname,sex,age)SC(sno,cno,grade)C(cno,cname,teacher)2、查询年龄大于23岁的男学生的学号和姓名涉及到的表：S(sno,sname,sex,age)方法二：用IN嵌套查询Select

sno,sname

from

Swhere

age>23

and

sno

in(select

sno

from

s

where

sex=‘男’)An

Introduction

to

Database

SystemAn

Introduction

to

Database

System综合练习答案S(sno,sname,sex,age)SC(sno,cno,grade)C(cno,cname,teacher)and

sy.sno=sx.sno)2、查询年龄大于23岁的男学生的学号和姓名涉及到的表：S(sno,sname,sex,age)方法三：用EXISTS嵌套查询Select

sx.sno,

sx.sname

from

s

sxwhere

sx.age>23

and

exists(select

*

from

ssywhere

sy.sex=‘男’综合练习答案S(sno,sname,sex,age)SC(sno,cno,grade)C(cno,cname,teacher)2、查询年龄大于23岁的男学生的学号和姓名涉及到的表： 涉及到的表：Sx(sno,sname,sex,age)

Sy(sno,sname,sex,age方法四：自连接Select

sx.sno,

sx.snamefrom

s

sx,

s

xywhere

sx.sno=sy.sno

andsx.age>23

and

sy.sex=‘男’An

Introduction

to

Database

SystemAn

Introduction

to

Database

System综合练习答案S(sno,sname,sex,age)SC(sno,cno,grade)C(cno,cname,teacher)where

sex=‘男’2、查询年龄大于23岁的男学生的学号和姓名涉及到的表：S(sno,sname,sex,age)方法五：集合查询Select

sno,sname

from

Swhere

age>23IntersectSelect

sno,sname

from

S综合练习答案S(sno,sname,sex,age)SC(sno,cno,grade)C(cno,cname,teacher)3、查询学号为S3学生所学课程的课程名与任课教师名涉及到的表：SC(sno,cno,grade)C(cno,cname,teacher)方法一：连接查询Select

cname

,teacherfrom

SC,Cwhere

SC.cno=C.cno

and

sno=‘S3’An

Introduction

to

Database

SystemAn

Introduction

to

Database

System综合练习答案S(sno,sname,sex,age)SC(sno,cno,grade)C(cno,cname,teacher)where

sno=‘S3’

)3、查询学号为S3学生所学课程的课程名与任课教师名涉及到的表：SC(sno,cno,grade)C(cno,cname,teacher)方法二：IN嵌套查询Select

cname

,teacher

from

Cwhere

cno

in(

select

cno

from

SC综合练习答案S(sno,sname,sex,age)SC(sno,cno,grade)C(cno,cname,teacher)3、查询学号为S3学生所学课程的课程名与任课教师名涉及到的表：SC(sno,cno,grade)C(cno,cname,teacher)方法三：EXIST嵌套查询Select

cname

,teacher

from

Cwhere

exists(select

*

from

SCwhere

sno=‘S3’

and

SCA.n

Icntnrodouc=tiCoCn

.tocDantaoboas)e

System综合练习答案S(sno,sname,sex,age)SC(sno,cno,grade)C(cno,cname,teacher)4、查询至少选修LIU老师所授课程中一门课程的女学生姓名涉及到全部的表：S，SC，C方法一：连接查询Select

sname

from

S,SC,Cwhere

S.sno=SC.sno

and

SC.cno=C.cnoand

sex=‘F’and

teacher=‘LIU’An

Introduction

to

Database

System综合练习答案S(sno,sname,sex,age)SC(sno,cno,grade)C(cno,cname,teacher)4、查询至少选修LIU老师所授课程中一门课程的女学生姓名涉及到全部的表：S，SC，C方法二：IN嵌套查询Select

sname

from

Swhere

sex=‘F’

and

sno

in(select

sno

from

SCwhere

cno

in(select

cno

from

Cwhere

teacher=‘LIU’))An

Introduction

to

Database

System综合练习答案S(sno,sname,sex,age)SC(sno,cno,grade)C(cno,cname,teacher)4、查询至少选修LIU老师所授课程中一门课程的女学生姓名涉及到全部的表：S，SC，C方法三：EXISTS嵌套查询Select

sname

from

Swhere

sex=‘F’

and

exists(select

*

from

Cwhere

teacher=‘LIU’

and

exists(select

*

from

SCwhere

SC.sno=S.snoand

SC.cno=C.cno

))

An

Introduction

to

Database

System综合练习答案S(sno,sname,sex,age)SC(sno,cno,grade)C(cno,cname,teacher)5、查询WANG同学不学的课程的课程号涉及到全部的表：S，SC，C方法一：NOT

EXISTS嵌套查询Select

cno

from

Cwhere

not

exists(select

*

from

S,SCwhere

S.sno=SC.snoand

SC.cno=C.cnoand

sname=‘WAAnNIGntr’oduc)t)ion

to

Database

System综合练习答案S(sno,sname,sex,age)SC(sno,cno,grade)C(cno,cname,teacher)5、查询WANG同学不学的课程的课程号涉及到全部的表：S，SC，C方法二：集合查询Select

cno

from

CExceptSelect

distinct

cno

from

S,

SCwhere

S.sno=SC.snoand

sname=‘WANG’An

Introduction

to

Database

System综合练习答案S(sno,sname,sex,age)SC(sno,cno,grade)C(cno,cname,teacher)6、查询至少选修两门课的学生学号涉及到的表：SCSelect

snofrom

SCgroup

by

sno having

count(*)>=2An

Introduction

to

Database

System综合练习答案S(sno,sname,sex,age)SC(sno,cno,grade)C(cno,cname,teacher)7、查询全部学生都选修的课程的课程号与课程名涉及到全部的表：S，SC，CSelect

cno,cnamefrom

Cwhere

not

exists(select

*

from

Swhere

not

exists(select

*

from

SCwhere

sno=S.sno

and

cno=C.cno))An

Introduction

to

Database

System综合练习答案S(sno,sname,sex,age)SC(sno,cno,grade)C(cno,cname,teacher)8、查询选修课程包含LIU老师所授全部课程的学生学号涉及到的表：SC，CSelect

distinct

snofrom

SC

as

Xwhere

not

exists(select

*

from

Cwhere

teacher=‘LIU’and

not

exists(select

*

from

SC

as

Ywhere

Y.sno=X.snoand

Y.cno=C.cAnn

oIn)tr)oduction

to

Database

System综合练习答案S(sno,sname,sex,age)SC(sno,cno,grade)C(cno,cname,teacher)9、统计每门课程的学生选修人数（超过10人的课程才统计）。要求显示课程号和人数，查询结果按人数降序排列，若人数相同，按课程号升序排列涉及到的表：SCSelect

cno,count(sno)from

SCgroup

by

cno

having

count(*)>10order

by

2

desc,1An

Introduction

to

Database

System综合练习答案S(sno,sname,sex,age)SC(sno,cno,grade)C(cno,cname,teacher)10、求LIU老师所授课程的每门课程的平均成绩涉及到的表：SC，CSelect

C.cno,avg(grade)from

SC,Cwhere

SC.cno=C.cnoand

teacher=‘LIU’group

by

C.cnoAn

Introduction

to

Database

System综合练习答案S(sno,sname,sex,age)SC(sno,cno,grade)C(cno,cname,teacher)11、检索姓名以L打头的所有学生的姓名和年龄涉及到的表：SSelect

sname,agefrom

Swhere

sname

like

‘L%’An

Introduction

to

Database

System综合练习答案S(sno,sname,sex,age)SC(sno,cno,grade)C(cno,cname,teacher)12、求年龄大于所有女同学年龄的男学生姓名和年龄涉及到的表：S方法一：>allSelect

sname,agefrom

Swhere

sex=‘M’and

age>all

(select

agefrom

Swhere

sex=‘AnFInt’rodu)c)tion

to

Database

System综合练习答案S(sno,sname,sex,age)SC(sno,cno,grade)C(cno,cname,teacher)12、求年龄大于所有女同学年龄的男学生姓名和年龄涉及到的表：S方法二：>maxSelect

sname,agefrom

Swhere

sex=‘M’and

age>

(select

max(age)from

Swhere

sex=‘AnFInt’rodu)c)tion

to

Database

S