数据库第三章作业

基本表:

S ( S N O , S N A M E , A G E , S E X ) S(SNO,SNAME,AGE,SEX) S(SNO,SNAME,AGE,SEX)

S C ( S N O , C N O , G R A D E ) SC(SNO,CNO,GRADE) SC(SNO,CNO,GRADE)

C ( C N O , C N A M E , T E A C H E R ) C(CNO,CNAME,TEACHER) C(CNO,CNAME,TEACHER)

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

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

    select CNO, CNAME
    from C
    where TEACHER = 'Liu'
    
  2. 检索年龄大于23岁的男学生的学号和姓名

    select SNO, SNAME
    from S
    where AGE > 23 and SEX = '男';
    
  3. 检索学号为S3学生所学课程的课程名与任课教师名

    select CNAME, TEACHER
    from C, SC
    where C.CNO = SC.CNO and SC.SNO = 'S3';
    
  4. 检索至少选修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 = '女';
    
  5. 检索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';)
    
  6. 检索至少选修两门课程的学生学号

    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);
    
  7. 检索全部学生都选修的课程的课程号和课程名

    select CNO,CNAME
    from C
    where not exists ( (select SNO 
         				from S)
        				except
        			   (select distinct SNO
        				from SC
        				where SC.CNO = C.CNO) );
    
  8. 检索选修课程包含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的查询:

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

    select count(distinct TEACHER)
    from SC;
    
  2. 求选修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 = '女';
    
  3. 求Shu老师所授课程的每门课程的平均成绩

    select CNO, avg(GRADE)
    from SC, C
    where SC.CNO = C.CNO and C.TEACHER = 'Shu'
    group by C.CNO;
    
  4. 统计每个学生选修课程的门数(超过5门的学生才统计)。要求输出学生学号和选修门数,查询结构按门数降序排列,若门数相同,按学号升序排列

    select SNO, count(distinct CNO)
    from SC
    group by CNO
    having count(*) > 5
    order by count(*) desc, SNO asc;
    
  5. 检索学号比Liu同学大, 而年龄比他小的学生的姓名

    select SNAME
    from S as X, S as Y
    where Y.SNAM = 'Liu' and X.SNO > Y.SNO and X.AGE < Y.AGE;
    
  6. 在表SC中检索成绩为空值的学生的学号和课程号

    select SNO, CNO
    from SC
    where GRADE is null;
    
  7. 检索姓名以L打头的所有学生的姓名和年龄

    select SNAME, AGE
    from S
    where S.name like 'L%';
    
  8. 求年龄大于男同学平均年龄的女同学的姓名和年龄

    select SNAME, AGE
    from S
    where S.SEX = '女' and S.AGE > (select avg(Y.AGE)
                                    from S as Y
                                    where Y.SEX = '男');
    
  9. 求年龄大于所有女同学年龄的男同学的姓名和年龄

    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的更新操作:

  1. 往关系C中插一个课程元组(‘C8’,‘VC++’,‘BAO’)

    insert into C
    	values ('C8', 'VC++', 'BAO');
    
  2. 检索所授每门课程平均成绩大于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);
    
  3. 在SC中删除尚无成绩的选课元组

    delete from SC
    where SC.GRADE in null;
    
  4. 把选修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');
    
  5. 把MATHS课不及格的成绩全改成60分

    update SC
    set SC.GRADE = 60
    where SC.GRADE < 60 and SC.CNO in (select CNO
                                       from C
                                       where C.CNAME = 'MATHS');
    
  6. 把低于所有课程总平均成绩的男同学成绩提高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);
    
  7. 把表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%);
    
  8. 在表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 ) EMP(ENO,ENAME,AGE,SEX,ECITY) EMP(ENO,ENAME,AGE,SEX,ECITY) 职工表(职工工号,姓名,年龄,性别,籍贯)

W O R K S ( E N O , C N O , S A L A R Y ) WORKS(ENO,CNO,SALARY) WORKS(ENO,CNO,SALARY) 工作表(职工工号,公司编号,工资)

C O M P ( C N O , C N A M E , C I T Y ) COMP(CNO,CNAME,CITY) COMP(CNO,CNAME,CITY)公司表(公司编号,公司名称,公司所在城市)

  1. 用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 );
    
  2. 检索超过35岁的男职工的工号和姓名

    select ENO, ENAME
    from EMP
    where AGE > 35;
    
  3. 假设每个职工只能在一个公司工作,检索工资超过1000元的男性职工的工号和姓名

    select ENO, ENAME
    from EMP, WORKS
    where EMP.ENO = WORKS.ENO and WORKS.SALARY > 1000 and EMP.SEX = '男';
    
  4. 假设每个职工可在多个公司工作,检索(同时)在编号为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');
    
  5. 检索在 “ 联华公司 ” 工作、工资超过1000元的男性职工的工号和姓名

    select distinct ENO, ENAME
    from EMP natural join WORKS natural join COMP
    where EMP.SEX = '男' and WORKS.SALARY > 1000 and COMP.CNAME = '联华公司';
    
  6. 假设每个职工可在多个公司工作,检索每个职工的而兼职公司数目和工资总数,显示$ (ENO,NUM,SUM_SALARY) $分别表示工号、公司数目和工资总数。

    select ENO, count(CNO) as NUM, sum(SALARY) as SUM_SALARY
    from WROKS
    group by ENO;
    
  7. 工号为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) );
    
  8. 检索华联公司中低于本公司平均工资的职工的工号和姓名

    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 = '联华公司');
    
  9. 在每一公司中为50岁以上职工加薪100元(若职工为多个公司工作,可重复加)

    update WORKS
    set WORKS.SALARY = WORKS.SALARY + 100
    where WORKS.ENO in (select ENO
                        from EMP
                        where EMP.AGE > 50);
    
  10. 在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 ) PART\ (PNO,PNAME,COLOR,WEIGHT) PART (PNO,PNAME,COLOR,WEIGHT)

项目: P R O J E C T <mtext>   </mtext> ( J N O , J N A M E , D A T E ) PROJECT\ (JNO,JNAME,DATE) PROJECT (JNO,JNAME,DATE)

供应商: S U P P L I E R <mtext>   </mtext> ( S N O , S N A M E , S A D D R ) SUPPLIER\ (SNO,SNAME,SADDR) SUPPLIER (SNO,SNAME,SADDR)

供应: P _ P <mtext>   </mtext> ( J N O , P N O , T O T A L ) P\_P\ (JNO,PNO,TOTAL) P_P (JNO,PNO,TOTAL)

采购: P _ S <mtext>   </mtext> ( P N O , S N O , Q U A N T I T Y ) P\_S\ (PNO,SNO,QUANTITY) P_S (PNO,SNO,QUANTITY)

  1. 试用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 );
    
  2. 试将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;
    
  3. 试在上述两个视图的基础上进行查询操作

    • 检索上海的供应商所提供的的零件的编号和名称

      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上的相应操作

  1. select * 
    from S_GRADE;
    
    -- 允许操作,等价于:
    select SNO, count(CNO) as C_NUM, avg(GRADE) as AVG_GRADE
    from SC
    group by SNO;
    
  2. 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;
    
  3. 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');
    
  4. update S_GRADE 
    set SNO = 'S3' 
    where SNO = 'S4';
    
    -- 不允许
    
  5. delete from S_GRADE
    where C_NUM > 4
    
    -- 不允许