Ch-4 Intermediate SQL

-- inner join (Only matching tuples)
select * from course inner join prereq on course.course_id = prereq.course_id;

-- left outer join (Keeps all tuples from the left relation)
select * from course natural left outer join prereq;

-- right outer join (Keeps all tuples from the right relation)
select count(*) from course natural right outer join prereq where prereq_id is null;

-- full outer join (Keeps all tuples from both relations)
select * from course full outer join prereq using (course_id);

date '2005-7-27'
time '09:00:30.75'
timestamp '2005-7-27 09:00:30.75'
interval '1' day

-- User-defined types
create type Dollars as numeric (12,2) final;

-- Domains with constraints
create domain person_name char(20) not null;

create domain degree_level varchar(10)
    constraint degree_level_test -- the name of constraints
    check (value in ('Bachelors', 'Masters', 'Doctorate'));

create table student(
	student_id int primary key,
	name varchar(50),
	email varchar(100) unique, -- 单列唯一约束互不影响
	phone char(11) unique,
	registration_date date
);

create table enrollment(
	enrollment_id int primary key,
	student_id int not null,
	course_id int not null,
	enroll_date date,
	foreign key (student_id) references student(student_id),
	foreign key (course_id) references course(course_id),
	unique (student_id,course_id) -- 组合唯一约束（笛卡尔积）
);

create table course (
    course_id char(5) primary key,
    dept_name varchar(20),
    foreign key (dept_name) references department
        on delete cascade -- 当主表字段删除时执行 cascade
        on update cascade -- 当主表字段更新时执行 cascade
);

create table person(
	name char(10) primary key,
	mother char(10),
	father char(10),
	foreign key (father) references person,
	foreign key (mother) references person
);

-- Creating an assertion
create assertion credits_earned_constraint check
(not exists 
    (select ID from student
     where tot_cred <> (
         select sum(credits) from takes natural join course
         where student.ID=takes.ID and grade is not null and grade<>'F')
    )
);

-- view definition
create view v as <query expression>

create view faculty as
    select ID, name, dept_name
    from instructor;
    
-- Querying the view
select name from faculty where dept_name = 'Biology';

insert into faculty values ('30765', 'Green', 'Music');
-- Must be translated by the system to:
insert into instructor values ('30765', 'Green', 'Music', null);

create materialized view departments_total_salary(dept_name, total_salary) as
    select dept_name, sum (salary)
    from instructor
    group by dept_name;

select dept_name
from departments_total_salary
where total_salary>(select avg(total_salary) from departments_total_salary);

create table student (
    ID varchar (5) primary key,
    name varchar (20) not null
);

create index studentID_index on student(ID);

-- Query executed by using the index
select * from student where ID = '12345';

SET AUTOCOMMIT=0;

UPDATE account SET balance=balance -100 WHERE ano='1001';
UPDATE account SET balance=balance+100 WHERE ano='1002';
COMMIT;

UPDATE account SET balance=balance -200 WHERE ano='1003';
UPDATE account SET balance=balance+200 WHERE ano='1004'; 
COMMIT;

UPDATE account SET balance=balance+balance*2.5%; -- 长事务
COMMIT;

-- Grant statement
-- user list can be a user-id, public or a role
grant <privilege list> on <relation name or view name> to <user list>

-- Revoke statement
-- <privilege list> may be all to reovke all privileges
revoke <privilege list> on <relation name or view name> from <user list>

-- Granting privileges
grant select on instructor to U1, U2, U3;
grant select on department to public;
grant update (budget) on department to U1, U2;

-- Revoking privileges
revoke select on branch from U1, U2, U3;

-- Transfer of privileges
grant select on department to Amit with grant option;
revoke select on department from Amit cascade;
revoke select on department from Amit restrict;
revoke grant option for select on department from Amit;

create role instructor;
grant select on takes to instructor;
grant instructor to Amit;

-- Chain of roles
create role teaching_assistant;
grant teaching_assistant to instructor; -- instructor inherits assistant's privileges