create database jwgl
create table Student(
Sno char(9) primary key,
Sname char(20),
Ssex char(2),
Sage smallint,
Sdept char(6)
);
create table Course(
Cno char(4) primary key,
Cname char(40),
Cpno char(4),
Ccredit smallint,
foreign key (Cpno) references Course(Cno)
);
create table SC(
Sno char(9),
Cno char(4),
Grade smallint,
primary key(Sno,Cno),
foreign key (Sno) references Student(Sno),
foreign key (Cno) references Course(Cno)
)

insert into Student(Sno,Sname,Ssex,Sage,Sdept)
values('201215121','李勇','男',20,'CS')

insert into Student(Sno,Sname,Ssex,Sage,Sdept)
values('201215122','刘晨','女',19,'CS')

insert into Student(Sno,Sname,Ssex,Sage,Sdept)
values('201215123','王敏','女',18,'MA')

insert into Student(Sno,Sname,Ssex,Sage,Sdept)
values('201215125','张立','男',19,'IS')

alter table Student alter column Sdept char(20)

select * from Student

insert into Course(Cno,Cname,Cpno,Ccredit)
values('1','数据库','5',4)

insert into Course(Cno,Cname,Cpno,Ccredit)
values('2','数学',null,2)

insert into Course(Cno,Cname,Cpno,Ccredit)
values('3','信息系统','1',4)

insert into Course(Cno,Cname,Cpno,Ccredit)
values('4','操作系统','6',3)

insert into Course(Cno,Cname,Cpno,Ccredit)
values('5','数据结构','7',4)

insert into Course(Cno,Cname,Cpno,Ccredit)
values('6','数据处理',null,2)

insert into Course(Cno,Cname,Cpno,Ccredit)
values('7','Pascal语言','6',4)

select * from Course

insert into SC(Sno,Cno,Grade)
values('201215121','1',92)

insert into SC(Sno,Cno,Grade)
values('201215121','2',85)

insert into SC(Sno,Cno,Grade)
values('201215121','3',88)

insert into SC(Sno,Cno,Grade)
values('201215122','2',90)

insert into SC(Sno,Cno,Grade)
values('201215122','3',80)

select * from SC

alter table Student alter column Sage int;
alter table Course add unique (Cname);

GRANT SELECT
ON TABLE Student
TO U1;

GRANT ALL PRIVILEGES
ON TABLE Student,Course
TO U2,U3;

GRANT SELECT
ON TABLE SC
TO PUBLIC;

GRANT UPDATE(Sno),SELECT
ON TABLE Student
TO U4;

GRANT INSERT
ON TABLE SC
TO U5
WITH GRANT OPTION;

GRANT INSERT
ON TABLE SC
TO U6
WITH GRANT OPTION;

GRANT INSERT
ON TABLE SC
TO U7;

REVOKE UPDATE(Sno)
ON TABLE Student
FROM U4;

REVOKE SELECT
ON TABLE SC
FROM PUBLIC;

REVOKE INSERT
ON TABLE SC
FROM U5 CASCADE;

CREATE ROLE R1;
GRANT SELECT,UPDATE,INSERT
ON TABLE SC
TO R1;
GRANT R1
TO 王平,张明;
REVOKE R1
FROM 王平;

GRANT DELETE
ON TABLE Student
TO R1;

REVOKE SELECT
ON TABLE Student
FROM R1;

CREATE VIEW CS_Student
AS
SELECT*
FROM Student
WHERE Sdept='CS';
GRANT SELECT
ON CS_Student
TO 王平;
GRANT ALL PRIVILEGES
ON CS_Studsent
TO 张明;

AUDIT ALTER,UPDATE
ON SC;

NOAUDIT ALTER UPDATE
ON SC;