数据库作业

DML嵌套查询

学生信息表（STU）

  SNO、SNM、SEX、BIRTHDAY、NATNM、 CLSNO、GRPNO

学习情况表（STUDY）

  学号SNO、课程编号CNO、考试成绩SCORE

课程表（数据字典COURSE）

  课程编号CNO、课程名称CNM，任课教师TNM ，先修课程FCNO

1、查询与姓名为“张三”在同一个组的学生信息

2、查询参加了全部课程考试的学生学号及姓名（思路：没有一门课程是没有选修的）

3、删除平均分以下的成绩

1、/*（30分）in如果写成=也算对，或者扣1分*/
select * from stu where grpno in(select grpno from stu where snm='张三'); 

2、/*40分*/
①select sno,snm from stu A
    where not exists(
      select * from course B where not exists(
        select * from study C where C.cno=B.cno and C.sno=A.sno
      )
    );
② /*sno没写前缀扣5分；count(cno) 写成count(*)也可以；*/
select stu.sno,snm from stu,study 
    where stu.sno=study.sno
    group by stu.sno having count(cno)>=(select count(cno) from course);

3、30分
delete from study where score<(select avg(score) from study)

存储过程-加判断

编写存储过程p_study，向表study(sno,cno,score)中插入数据，输入参数为学号、课程号、成绩，要求对输入数据进行合理性检查。

DELIMITER @@

CREATE procedure p_study(_sno CHAR(10),_cno CHAR(3),_score DECIMAL(4,0))
lable_exit: BEGIN
  if not exists(select * from stu where sno=_sno) then
    leave lable_exit; #学号不存在
  end if;
  
  if not exists(select * from course where sno=_cno) then
    leave lable_exit; #课程号不存在
  end if;
  
  if exists(select * from study where sno=_cno and cno=_cno) then
    leave lable_exit; #相应的成绩已经存在
  end if;
  
  INSERT INTO study(sno,cno,score) VALUES(_sno,_cno,_score);
END@@

DELIMITER ;

触发器练习 

DROP TRIGGER IF EXISTS t_ins_sc;
DELIMITER @@
CREATE TRIGGER t_ins_sc
BEFORE INSERT
ON sc
FOR EACH ROW
BEGIN
    IF new.score<0 THEN /*如果判断了但是没有达到取消插入的效果，扣30分*/
      signal SQLSTATE '45000' SET message_text='分数不能小于0';
     END IF;
    INSERT INTO log_sc(who,v_new,op_data) VALUES(USER(),new.grade,NOW());
    /*或者after触发器，判断后用如下处理：
    DELETE FROM sc WHERE sno=new.sno AND cno=new.cno;    */
END@@

约束练习

 

1、创建2个表：20分/个，主、外键错误或遗漏，扣5分/个，如果字段名笔误扣1-2分/个
create table dept1
(dno decimal(3) not null primary key,
 name varchar(10),
 loc varchar(20)
);      
create table emp1(
 eno decimal(4) not null,
 name varchar(10) unique,
 salary decimal(6,2),
 dno decimal(3) references dept1(dno) on delete cascade
);
2、增加约束
alter table dept1 add constraint un_dept1_name unique(name);
alter table emp1 add constraint pk_emp1 primary key(eno);
alter table emp1 add constraint chk_salary 
            check(salary between 2000 and 5000);
说明：后2个合并为一条语句也可以。

关系代数

③Select SNAME,SDEPT, CNAME from T,C,SC,S where T.TNO=C.TNO AND C.CNO=SC.CNO AND SC.SNO=S.SNO AND TNAME=‘刘’

④Select SNAME,SDEPT,SCORE from S,SC,C WHERE S.S NO=SC.SNO AND SC.CNO=C.CNO AND CNMAE=‘高等数学’ and score>=90

⑤查询没选VB课程的学生的姓名和所在系（40分）

πsname,sdept (S) - πsname,sdept(σCname=‘VB’ (C∞SC∞S))

⑥查询选了C01和C02的课程的学生的学号？（关系代数表达式及SQL语句，60分）

学生：S(SNO,SNAME ,AGE,SEX,SDEPT)

课程：C (CNO,CNAME,CREDIT,SEMESTER,TNO)

选课：SC(SNO,CNO,SCORE)

教师：T(TNO,TNAME,TITLE)

⑤select sname,sdept from STU where sno not in

    (select sno from sc where cno=(select cno from c where c.cname=‘VB’)

    ) 或者：

  select sname,sdept from STU where sno not in

    (select sno from sc,c where sc.cno=c.cno and c.cname=‘VB’)

评分说明：任意一种都可以，用in的扣5分，没有用in或not in的扣15分；SQL语句基本语法混乱的视情况，得分不超过10分

⑥π1 (σ1=4∧2=‘C001'∧5=‘C002' (SC×SC))

Select distinct Sno from SC A,SC B WHERE A.SNO=B.SNO AND A.CNO=‘C01’AND B.CNO=‘C02’

查询软件工程系选了第1学期开设的全部课程的学生的学号和姓名。写出关系代数表达式和SQL语句

πSNO,SNAME,CNO (σSDEPT=‘软件工程系’ (S))∞(SC) ÷ πCNO(σSEMETER=1(C))

Select sno,sname from s A

Where sdept=’软件工程系’ and not exists

  (select cno from course B where semester=1 and not exists

    (select * from sc C where C.SNO=A.SNO and C.CNO=B.CNO)

  )