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属性的权限授给用户李天明。

Insert Into Student Values('95001','李勇','男',20,'CS');
Insert Into Student Values('95002','刘晨','女',19,'IS');
Insert Into Student Values('95003','王敏','女',18,'MA');
Insert Into Student Values('95004','张立','男',19,'IS');

Insert Into Course Values(1,'数据库',5,4);
Insert Into Course Values(2,'数学',NULL,2);
Insert Into Course Values(3,'信息系统',1,4);
Insert Into Course Values(4,'操作系统',6,3);
Insert Into Course Values(5,'数据结构',7,4);
Insert Into Course Values(6,'数据处理',NULL,2);
Insert Into Course Values(7,'PASCAL语言',6,4);

Insert Into SC values('201215121',1,92);
Insert Into SC values('201215121',2,85);
Insert Into SC values('201215121',3,88);
Insert Into SC values('201215122',2,90);
Insert Into SC values('201215122',3,80);

Alter Table Course Alter Column Cname Char(20)