数据库第三章作业

基本表：

$S (S N O, S N A M E, A G E, S E X)$

$S C (S N O, C N O, G R A D E)$

$C (C N O, C N A M E, T E A C H E R)$

问题一：使用SQL的查询语句表达下列查询：

检索Liu老师所教授课程的课程号和课程名

select CNO, CNAME
from C
where TEACHER = 'Liu'

检索年龄大于23岁的男学生的学号和姓名

select SNO, SNAME
from S
where AGE > 23 and SEX = '男';

检索学号为S3学生所学课程的课程名与任课教师名

select CNAME, TEACHER
from C, SC
where C.CNO = SC.CNO and SC.SNO = 'S3';

检索至少选修Liu老师所授课程中一门课程的女同学的姓名

select distinct NAME
from S, SC, C
where S.SNO = SC.SNO and SC.CON = C.CON and C.TEACHER = 'Liu' and S.SEX = '女';

检索Wang同学不学的课程的课程号

(select distinct CNO
 from S, SC
 where S.SNO = SC.SNO and S.SNAME = 'Wang')
except 
(select distinct CNO
 from S, SC, C
 where S.SNO = SC.SNO and SC.CON = C.CON and C.TEACHER = 'Wang';)

检索至少选修两门课程的学生学号

select distinct SNO
from SC as X
where SNO in (select SNO
     		  from SC as Y
     		  where X.SNO = Y.SNO and X.CNO != Y.CNO);

检索全部学生都选修的课程的课程号和课程名

select CNO,CNAME
from C
where not exists ( (select SNO 
     				from S)
    				except
    			   (select distinct SNO
    				from SC
    				where SC.CNO = C.CNO) );

检索选修课程包含Liu老师所授课程的学生学号

select SNO
from S
where exists (select SNO 
              from SC, C
              where SC.CNO = C.CNO and SC.SNO = S.SNO and C.TEACHER = 'Liu');

问题二：试用SQL查询语句表达下列对三个基本表S、SC、C的查询：

在表C中统计开设课程的教师人数

select count(distinct TEACHER)
from SC;

求选修C4课程的女学生的平均年龄

select avg(AGE)
from S, SC, C
where S.SNO = SC.SNO and SC.CNO = C.CNO and C.CNAME = 'C4' and S.SEX = '女';

求Shu老师所授课程的每门课程的平均成绩

select CNO, avg(GRADE)
from SC, C
where SC.CNO = C.CNO and C.TEACHER = 'Shu'
group by C.CNO;

统计每个学生选修课程的门数（超过5门的学生才统计）。要求输出学生学号和选修门数，查询结构按门数降序排列，若门数相同，按学号升序排列
```
select SNO, count(distinct CNO)
from SC
group by CNO
having count(*) > 5
order by count(*) desc, SNO asc;
```

检索学号比Liu同学大，而年龄比他小的学生的姓名

select SNAME
from S as X, S as Y
where Y.SNAM = 'Liu' and X.SNO > Y.SNO and X.AGE < Y.AGE;

在表SC中检索成绩为空值的学生的学号和课程号
```
select SNO, CNO
from SC
where GRADE is null;
```

检索姓名以L打头的所有学生的姓名和年龄

select SNAME, AGE
from S
where S.name like 'L%';

求年龄大于男同学平均年龄的女同学的姓名和年龄

select SNAME, AGE
from S
where S.SEX = '女' and S.AGE > (select avg(Y.AGE)
                                from S as Y
                                where Y.SEX = '男');

求年龄大于所有女同学年龄的男同学的姓名和年龄

select SNAME, AGE
from S
where S.SEX = '男' and S.AGE > all (select avg(Y.AGE)
                                    from S as Y
                                    where Y.SEX = '女');

问题三：试用SQL更新语句表达对数据库中关系S、SC、C的更新操作：

往关系C中插一个课程元组（‘C8’，‘VC++’，‘BAO’）
```
insert into C
	values ('C8', 'VC++', 'BAO');
```

检索所授每门课程平均成绩大于80分的教师姓名，并把检索到的值送往另一个已存在的表FACULTY（TNAME）

insert into FACULTY(TNAME)
	select distinct TEACHER
	from SC as X natural join C
	where not exists
		(select CNO
		 from SC as Y
		 where X.CNO = Y.CNO and X.SNO = Y.SNO and avg(GRADE) <= 80
		 group by Y.SNO);

在SC中删除尚无成绩的选课元组
```
delete from SC
where SC.GRADE in null;
```

把选修LIU老师课程的女同学选课元组全部删去

delete from SC
where SC.SNO in (select distinct SNO
                 from C, S, SC as X
                 where S.SNO = X.SNO and C.CNO = X.CNO and S.SEX = '女' and C.TEACHER = 'LIU');

把MATHS课不及格的成绩全改成60分

update SC
set SC.GRADE = 60
where SC.GRADE < 60 and SC.CNO in (select CNO
                                   from C
                                   where C.CNAME = 'MATHS');

把低于所有课程总平均成绩的男同学成绩提高5%

update SC
set SC.GRADE = SC.GRADE * (1 + 5%)
where SC.SNO in (select SNO 
                 from S
                 where S.SEX = '男')
	and SC.GRADE < (select avg(GRADE)
                  	from SC);

把表SC中修改C4课程的成绩，若成绩小于等于70分时提高5%，若成绩大于70分时提高4%（用两种方法实现：一种方法是用两个UPDATE语句实现；另一种方法是用带CASE操作的一个UPDATA语句实现）

-- methods 1:
update SC
set SC.GRADE = SC.GRADE * (1 + 5%)
where SC.GRADE <= 70;

update SC
set SC.GRADE = SC.GRADE * (1 + 4%)
where SC.GRADE > 70;

-- methods 2:
update SC
set SC.GRADE = case when SC.GRADE <= 70 then SC.GRADE * (1 + 5%)
                    else SC.GRADE * (1 + 4%);

在表SC中，当某个成绩低于全部课程的平均成绩时，提高5%

update SC
set SC.GRADE = SC.GRADE * (1 + 5%)
where SC.GRADE < (select avg(GRADE)
                  from SC);

问题四：设数据库中有如下三个关系用SQL语句写出下列操作

$E M P (E N O, E N A M E, A G E, S E X, E C I T Y)$ 职工表（职工工号，姓名，年龄，性别，籍贯）

$W O R K S (E N O, C N O, S A L A R Y)$ 工作表（职工工号，公司编号，工资）

$C O M P (C N O, C N A M E, C I T Y)$ 公司表（公司编号，公司名称，公司所在城市）

用CREATE TABLE语句创建上述三个表，需指出主键和外键

-- table EMP
create table EMP 
	( ENO	char(15),
      ENAME	varchar(20),
      AGE	numeric(3,0),
      SEX	char(4),
      ECITY	varchar(20),
	primary key (ENO) );

-- table COMP
create table COMP
	( CNO	char(10),
      CNAME	varchar(20),
      CITY	varchar(20),
	primary key (CNO) );

-- table WORKS
create table WORKS
	( ENO		char(15),
      CNO		char(10),
      SALARY	numeric(10,3),
	primary key (ENO, CNO),
	foreign key (ENO) references EMP,
    foreign key (CNO) references COMP );

检索超过35岁的男职工的工号和姓名

select ENO, ENAME
from EMP
where AGE > 35;

假设每个职工只能在一个公司工作，检索工资超过1000元的男性职工的工号和姓名

select ENO, ENAME
from EMP, WORKS
where EMP.ENO = WORKS.ENO and WORKS.SALARY > 1000 and EMP.SEX = '男';

假设每个职工可在多个公司工作，检索（同时）在编号为C4和C8公司兼职的职工的工号和姓名

select distinct ENO, ENAME
from EMP natural join WORKS
where WORKS.CNO in (select X.CNO
                    from WORKS as X, WORKS as Y
                    where X.ENO = Y.ENO and X.CNO = 'C4' and Y.CNO = 'C8');

检索在 “ 联华公司 ” 工作、工资超过1000元的男性职工的工号和姓名

select distinct ENO, ENAME
from EMP natural join WORKS natural join COMP
where EMP.SEX = '男' and WORKS.SALARY > 1000 and COMP.CNAME = '联华公司';

假设每个职工可在多个公司工作，检索每个职工的而兼职公司数目和工资总数，显示$ (ENO,NUM,SUM_SALARY) $分别表示工号、公司数目和工资总数。
```
select ENO, count(CNO) as NUM, sum(SALARY) as SUM_SALARY
from WROKS
group by ENO;
```

工号为E6的职工在多个公司工作，试检索至少在E6职工兼职的所有公司工作的职工工号

-- 注意这样的写***包括E6本人
select distinct X.ENO
from WORKS as X
where not exists (select Y.CNO
                  from WORKS as Y
                  where Y.ENO = 'E6' and not exist (select Z.CNO
                          		   					from WORKS as Z
                                   					where X.ENO = Z.ENO and Z.CNO = Y.CNO) );

检索华联公司中低于本公司平均工资的职工的工号和姓名

select distinct ENO, ENAME
from WORKS natural join COMP
where COMP.CNAME = '联华公司' and WORKS.SALARY < 
	(select avg (SALARY)
     from WORKS natural join COMP
     where COMP.CNAME = '联华公司');

在每一公司中为50岁以上职工加薪100元（若职工为多个公司工作，可重复加）

update WORKS
set WORKS.SALARY = WORKS.SALARY + 100
where WORKS.ENO in (select ENO
                    from EMP
                    where EMP.AGE > 50);

在EMP表和WORKS表中删除年龄大于60岁的职工有关元组

-- 注意要先删除WORKS，再删除EMP
delete from WORKS
where WORKS.ENO in (select ENO
                    from EMP
                    where EMP.AGE > 60);
delete from EMP
where EMP.AGE > 60;

问题五：仓库管理数据库中有五个基本表

零件： $P A R T <mtext> </mtext> (P N O, P N A M E, C O L O R, W E I G H T)$

项目： $P R O J E C T <mtext> </mtext> (J N O, J N A M E, D A T E)$

供应商： $S U P P L I E R <mtext> </mtext> (S N O, S N A M E, S A D D R)$

供应： $P_P <mtext> </mtext> (J N O, P N O, T O T A L)$

采购： $P_S <mtext> </mtext> (P N O, S N O, Q U A N T I T Y)$

试用SQL DDL语句定义上述五个基本表，需说明主键和外键

-- table PART
create table PART
	( PNO		char(12),
      PNAME		varchar(20),
      COLOR		varchar(8),
      WEIGHT	numeric(10,3),
	primary key (PNO) );
	
-- table PROJECT
create table PROJECT
	( JNO		char(12),
      JNAME		varchar(20),
      DATE		varchar(100),
    primary key (JNO) );
    
-- table SUPPLIER
create table SUPPLIER
	( SNO		char(12),
      SNAME		varchar(20),
      SADDR		varchar(50),
    primary key (SNO) );
    
-- table P_P
create table P_P
	( JNO		char(12),
      PNO		char(12),
      TOTAL		int
    primary key (JNO, PNO),
    foreign key (JNO) references PROJECT,
    foreign key (PNO) references PART );
    
-- table P_S
create table P_S
	(PNO		char(12),
     SNO		char(12),
     QUANTITY	int,
    primary key (PNO, SNO),
    foreign key (PNO) reference PART,
    foreign key (SNO) reference SUPPLIER );

试将PROJECT、P_P、PART三个基本表的连接定义为一个视图VIEW1，将PART、P_S、SUPPLIER三个基本表的连接定义为一个视图VIEW2

-- create VIEW 1
create view VIEW1 as
	select *
	from PROJECT, P_P, PART
	where PROJECT.JNO = P_P.JNO and PART.PNO = P_P.PNO;

-- create VIEW 2
create view VIEW2 as
	select *
	from PART, P_S, SUPPLIER
	where PART.PNO = P_S.PNO and SUPPLIER.SNO = P_S.SNO;

试在上述两个视图的基础上进行查询操作
- 检索上海的供应商所提供的的零件的编号和名称
```
select PNO, PNAME
from VIEW2
where SEADDR = '上海';
```
- 检索项目J4所用零件的供应商的编号和名称
```
select SNO, SNAME
from VIEW1
where JNO = 'J4';
```

问题六：对于问题一的数据库基本表SC，建立如下视图：

create view S_GRADE (SNO, C_NUM, AVG_GRADE) as 
	select SNO, count(CNO), avg(GRADE)
	from SC
	group by SNO;

试判断下列查询和更新操作是否允许执行，如允许，写出转换到基本表SC上的相应操作

select * 
from S_GRADE;

-- 允许操作，等价于：
select SNO, count(CNO) as C_NUM, avg(GRADE) as AVG_GRADE
from SC
group by SNO;

select SNO, C_NUM 
from S_GREAD 
where AVG_GRADE > 80;

-- 允许操作，等价于：
select SNO, count(CNO) as C_NUM
from SC
group by SNO
having avg(GRADE) > 80;

select SNO, AVG_GRADE
from S_GRADE
where C_NUM > (select C_NUM 
               from S_GRADE
               where SNO = 'S4');

-- 允许操作，等价于：
select X.SNO, avg(GRADE)
from SC as X
group by X.SNO;
having count(X.CNO) > (select count(Y.CNO) 
            		   from SC as Y
               		   where Y.SNO = 'S4');

update S_GRADE 
set SNO = 'S3' 
where SNO = 'S4';

-- 不允许

delete from S_GRADE
where C_NUM > 4

-- 不允许