SQL权限控制习题

SQL触发器：删除学生数据

#创建触发器，删除学生数据时，将删除的数据备份到数据表student_bf，以备误删除时进行数据恢复。

1、student（学生表）：

SNO学号CHAR(7)

SNAME姓名CHAR(10)

SSEX性别CHAR(2)

SAGE年龄SMALLINT

SDEPT所在系 VARCHAR(20)

2、course（课程表）

CNO课程号CHAR(10)

CNAME课程名VARCHAR(20)

CCREDIT学分SMALLINT

SEMSTER学期SMALLINT

PERIOD学时SMALLINT

3、sc（选课表）

SNO 学号CHAR(7)

CNO 课程号CHAR(10)

GRADE 成绩 SMALLINT

[注意：SQL表名请用小写]

delimiter $
CREATE TRIGGER TRG_DELE 
BEFORE DELETE ON student
for each row 
begin 
    insert into student_bf
    select * from student
    where sno=old.sno;
end $
delimiter;

SQL触发器：创建成绩表插入触发器

#创建触发器，插入学生成绩时，限制必须在0-100之间。如果不在0-100之间，设置为0.

1、student（学生表）：

SNO学号CHAR(7)

SNAME姓名CHAR(10)

SSEX性别CHAR(2)

SAGE年龄SMALLINT

SDEPT所在系 VARCHAR(20)

2、course（课程表）

CNO课程号CHAR(10)

CNAME课程名VARCHAR(20)

CCREDIT学分SMALLINT

SEMSTER学期SMALLINT

PERIOD学时SMALLINT

3、sc（选课表）

SNO 学号CHAR(7)

CNO 课程号CHAR(10)

GRADE 成绩 SMALLINT

[注意：SQL表名请用小写]

delimiter $
create trigger ss
before insert on sc
for each row
begin
    if new.grade<0 or new.grade>100 then
        set new.grade=0;
    end if;
end $
delimiter;

SQL存储过程：建立存储过程，查询课程的成绩表

#建立存储过程show_grade：根据学号，课程名参数，返回学生的成绩。

学生数据库db_student包括三个数据表student（学生表）、course（课程表）和sc（选课表）。表结构如下：

1、student（学生表）：

SNO学号CHAR(7)

SNAME姓名CHAR(10)

SSEX性别CHAR(2)

SAGE年龄SMALLINT

SDEPT所在系 VARCHAR(20)

2、course（课程表）

CNO课程号CHAR(10)

CNAME课程名VARCHAR(20)

CCREDIT学分SMALLINT

SEMSTER学期SMALLINT

PERIOD学时SMALLINT

3、sc（选课表）

SNO 学号CHAR(7)

CNO 课程号CHAR(10)

GRADE 成绩 SMALLINT

[注意：SQL表名请用小写]

delimiter $
create procedure show_grade(in ssno char(7),in scname varchar(20),out gg smallint)
begin
    select grade from sc,course
    where sno=ssno and sc.cno = course.cno and cname=scname
    into gg;
end $
DELIMITER;

存储过程：返回学生总数，男生人数，女生人数

#存储过程 tj_rs：根据系名，统计该系学生总数，男生人数，女生人数。

[注意：SQL表名请用小写]

学生数据库db_student包括三个数据表student（学生表）、course（课程表）和sc（选课表）。表结构如下：

1、student（学生表）：

SNO学号CHAR(7)

SNAME姓名CHAR(10)

SSEX性别CHAR(2)

SAGE年龄SMALLINT

SDEPT所在系 VARCHAR(20)

2、course（课程表）

CNO课程号CHAR(10)

CNAME课程名VARCHAR(20)

CCREDIT学分SMALLINT

SEMSTER学期SMALLINT

PERIOD学时SMALLINT

3、sc（选课表）

SNO 学号CHAR(7)

CNO 课程号CHAR(10)

GRADE 绩 SMALLINT

delimiter $$
create procedure tj_rs(
in SDEPT_0 VARCHAR(20),out total int,out gender_M int,out gender_F int)
begin
    select count(*) into total
    from student
    where SDEPT_0= SDEPT;
    select count(*) into gender_M 
    from student
    where SDEPT_0=SDEPT and SSEX='男';
    select count(*) into gender_F
    from student
    where SDEPT_0=SDEPT and SSEX='女';
end
$$

创建触发器，当更改商品价格(price列)时，记录价格

创建触发器，当更改商品product表的价格(price列)时，要将老的价格记录在一个名为price_log的表中。

相关表结构：

1、商品表：product
2、商品价格记录表：price_log

delimiter $
CREATE TRIGGER BEFORE_update_price
BEFORE UPDATE
ON product
FOR EACH ROW
BEGIN
    if(old.price<>new.price) THEN
        INSERT price_log(product_id,price)
        VALUES(old.product_id,old.price);
    END IF;
END $
delimiter ;

SQL存储过程：建立存储过程，查询学生的平均分

#建立存储过程show_grade：根据学号，返回学生的平均成绩(保留整数)。

学生数据库db_student包括三个数据表student（学生表）、course（课程表）和sc（选课表）。表结构如下：

1、student（学生表）：

SNO学号CHAR(7)

SNAME姓名CHAR(10)

SSEX性别CHAR(2)

SAGE年龄SMALLINT

SDEPT所在系 VARCHAR(20)

2、course（课程表）

CNO课程号CHAR(10)

CNAME课程名VARCHAR(20)

CCREDIT学分SMALLINT

SEMSTER学期SMALLINT

PERIOD学时SMALLINT

3、sc（选课表）

SNO 学号CHAR(7)

CNO 课程号CHAR(10)

GRADE 成绩 SMALLINT

[注意：SQL表名请用小写]

CREATE PROCEDURE show_grade (
  IN SNO1 CHAR(7),
  OUT GRADE1 SMALLINT
) 
SELECT AVG(GRADE) INTO GRADE1 FROM sc
WHERE SNO = SNO1 
GROUP BY SNO;

SQL触发器：插入新员工时，同步更新部门表相应人数

创建触发器，插入新员工时，同步更新部门表相应人数。

相关表结构：

1、员工表：employee

2、部门表：department

delimiter $$
create TRIGGER t1
AFTER INSERT on  employee
FOR EACH ROW
BEGIN
  update department
    set emp_num = coalesce(emp_num,0)+1 where dept_id = new.dept_id;
END $$
delimiter;

存储过程：根据部门编号，返回该部门的员工总数，男员工数，女员工数

创建存储过程tj_rs：根据部门编号(int)，返回该部门的员工总数，男员工数，女员工数(注意参数顺序必须与题目一致)。

相关表结构：

1、员工表：employee

2、部门表：department

delimiter $$
create procedure tj_rs(in deptid int,out count1 int,out count2 int,out count3 int)
begin
   select count(*)into count1 from employee where dept_id=deptid;
   select count(*) into count2 from employee where  dept_id=deptid and gender='男';
   select count(*) into count3 from employee where  dept_id=deptid and gender='女';
end
$$

SQL存储过程：查询订单

#创建存储过程：total_order(IN orderid INT , OUT totals NUMERIC(9,2), OUT cid INT)

要求：根据输入的订单编号(order_id)，返回订单总金额(total_money),顾客编号(customer_id)。

订单表：orders

delimiter $
create procedure total_order(in orderid int,out totals numeric(9,2),out cid int)
begin
    select total_money,customer_id from orders 
    where order_id=orderid
    into totals,cid;
end $
delimiter ;