fatdb7

实验 7 — Advanced SQL (Part II)

5. Create a view faculty showing only the ID, name, and department of instructors.

创建视图faculty仅包含 教师的id，名字，和部门

create view faculty as
select ID,name,dept_name
from instructor;

可见它成功创建了

6. Create a view CSinstructors, showing all information about instructors from the Comp. Sci. department.

create view CSInstructors AS
	select
		*
	from instructor
	where dept_name = 'Comp. Sci.';

可见创建了个只包含CS老师的视图，里面包括两位老师

7. Insert appropriate tuple into each of the views faculty and CSinstructors, to see what updates your database allows on views; explain what happens.

insert into faculty 
VALUES ('114', 'Mr.fat', 'Comp. Sci.');

尝试向faculty里插入元组，发现只要主码不重，即可插入成功。

insert into CSInstructors 
VALUES ('114', 'Mr.fat', 'Comp. Sci.','114514');

select * from CSInstructors

试图向试图CSInstructors 插入ID为114的教师，因为键值重复，无法插入

而之前在faculty中插入的两位教师也在CSInstructors中出现，且salary为空值

键值不同的老师可以插入。

8. Grant permission to one of your friends to view all data in your student relation.

先创建一个叫friend的user，然后用grant授予它select student的权限

grant select on student to friend

9. Now grant permission to all users to see all data in your faculty view. Conversely, find a friend who has granted you permission on their faculty view, and execute a select query on that view.

创建了3个用户
用grant和public关键字给所有用户权限

grant select on faculty to PUBLIC;

可见登录了用户a之后，它的左侧有视图faculty，且能对它执行select操作