数据库原理实战-高级SQL实战

• 练习1:定义两个变量，用于分别获取名字为Green的员工的城市和街道，并显示变量值。

#定义两个变量，用于分别获取名字为Green的员工的城市和街道，并显示变量值。
set @green_street;
set @green_city;
select city, street into @green_city,@green_street
from employee
where employee_name = 'Green';
select @green_street,@green_city;

• 练习2:给定员工姓名，统计该员工所在公司的平均工资。

首先是通过员工姓名找到公司,而姓名->公司的这个关系是在works的,因此先要查询works

delimiter$$
create PROCEDURE emp_avg_sla(IN emp_name varchar(20),out emp_sal decimal(8,2))
begin
	select avg(salary) into emp_sal
	from works
	where employee_name = emp_name;
end$$
delimiter ;

show tables;

set @emp_name = 'CbingQuan';
set @emp_sal := 0;
call emp_avg_sla(@emp_name,@emp_sal);
select @emp_sal;

• 练习3:调用已经创建的存储过程，显示Green所在公司的平均工资。

set @emp_name = 'Green';
set @emp_sal := 0;
call emp_avg_sla(@emp_name,@emp_sal);
select @emp_sal;

• 练习4:设计一个存储过程，给低于等于平均工资的员工增加工资5%，给高于平均工资的员工增加10%。

drop procedure update_salary;
create procedure update_salary()
begin
    declare temp_name varchar(20);
    declare temp_salary decimal(8,2);
    declare avg_salary decimal(8,2);
    declare count int default 0;
    declare total_count int ;
    declare cur cursor for select employee_name,salary from works;
    select avg(salary) into avg_salary from works;
    select count(*) into total_count from works;
    open cur;
    while `count` < total_count do
        fetch cur into temp_name,temp_salary;
        if(temp_salary <= avg_salary) then
            update works set salary = salary *1.05;
        else
            update works set salary = salary * 1.10;
        end if;
        set count = count +1;
    end while;
    close cur;
end;
call update_salary();
select * from works;

• 练习5. 编写university数据库中的触发器，在对takes表的插入操作前检查，使grade的值必须是‘A’-‘F’中的之一，如果不是则把grade设置为null。

create trigger check_grade before insert on takes
    for each row
    begin
        if new.grade not in ('A','B','C','D','E','F') then
            set new.grade = null;
        end if;
    end;

• 练习6: 创建一个变量@check_cred=true，编写university数据库中的触发器，检查对student表的更新操作，如果tot_cred属性值不等于该学生所有及格课程的学分之和，则强制设置为正确的学分总和，并将@check_cred设置为false。

这道题涉及的查询有:

tot_cred,要通过查询某一个学生的全部学分之和,并且记录

#查询这位同学选修的所有课程
select course_id from takes where takes.ID = NEW.ID and takes.grade not in ('F');
#计算总和
select sum(credits) into @tot_cred_takes
from course
where course_id in
(select course_id
 from takes
 where takes.ID = NEW.ID and takes.grade is not null and takes.grade not in ('F'));

通过takes表查询这个学生所有及格的课程学分总和tot_cred_takes

select tot_cred into @tot_cred_select
      from student
      where student.ID = NEW.ID;

set @check_cred = true;
create trigger check_cred_trigger before update on student
    for each row
    begin
        #查询所有及格课程的学分
        set @tot_cred_takes := 0;
        set @tot_cred_select := 0;
        #计算总和
        select sum(credits) into @tot_cred_takes
        from course
        where course_id in
        (select course_id
         from takes
         where takes.ID = NEW.ID and takes.grade is not null and takes.grade not in ('F'));
        #查询总和
        select tot_cred into @tot_cred_select
        from student
        where student.ID = NEW.ID;
        if @tot_cred_select <> @tot_cred_takes then
            set NEW.tot_cred = @tot_cred_takes;
            set @check_cred = false;
        end if;
    end;