SQL Chapter5

Create database SQLTest4

Create table Student (
	SNO CHAR(15),
	SNAME CHAR(15),
	SSex CHAR(5),
	Sdept CHAR(5)
)

Create table Course (
	CNO INT,
	CNAME CHAR(8),
	Cpno INT,
	Ccredit INT
)

Create table SC (
	Sno Char(15),
	Cno int,
	Grade int
)


-----------------------------------------


Create View SC_View
As 
Select Sno,Cno,Grade 
From SC
Where Cno = 1
And Cast(DATENAME(hh,GETDATE()) As int) Between 9 And 17
And DATENAME(WEEKDAY, GETDATE()) in ('星期一','星期二','星期三','星期四','星期五')
With Check Option;

Create View IS_Student1
As 
Select Sno,Sname,Ssex,Sdept
From Student
Where Sdept = 'IS'
With Check Option;

Create View IS_Student2
As 
Select Sno,Sname,Ssex,Sdept
From Student
Where Sdept = 'IS'


select * from is_student1
select * from is_student2

insert into IS_Student1
values('2012215121','李勇','男','CS');

insert into IS_Student2
values('2012215121','李勇','男','CS');

drop user 张勇
drop login zhangyong
create login zhangyong with password='123';
create user 张勇 for login zhangyong;
create login litianming with password='123';
create user 李天明 for login litianming;


Grant Insert
On Student
To 张勇
With Grant Option

Grant Update(Cpno),Select
On Course
To 李天明

--把对表S的INSERT权限授予用户张勇，并允许他再将此权限授予其他用户。
--把查询SPJ表和修改QTY属性的权限授给用户李天明。


--把对表S的INSERT权限授予用户张勇，并允许他再将此权限授予其他用户。
--把查询SPJ表和修改QTY属性的权限授给用户李天明。