Ch-4 SQL 进阶

select *
from course inner join prereq
    on course.course_id = prereq.course_id;

select *
from course natural join prereq;

select *
from course full outer join prereq using (course_id);

select *
from course natural left outer join prereq;

select count(*)
from course natural right outer join prereq
where prereq_id is null;

select *
from course natural full outer join prereq;

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

create type Dollars as numeric(12, 2) final;

create table department (
    dept_name varchar(20),
    building varchar(15),
    budget Dollars
);

create domain person_name char(20) not null;

create domain degree_level varchar(10)
    constraint degree_level_test
    check (value in ('Bachelors', 'Masters', 'Doctorate'));

create table section (
    course_id varchar(8),
    sec_id varchar(8),
    semester varchar(6),
    year numeric(4, 0),
    building varchar(15),
    room_number varchar(7),
    time_slot_id varchar(4),
    primary key (course_id, sec_id, semester, year),
    check (semester in ('Fall', 'Winter', 'Spring', 'Summer'))
);

create table enrollment (
    enrollment_id int primary key,
    student_id int not null,
    course_id int not null,
    enroll_date date,
    unique (student_id, course_id)
);

create table course (
    course_id char(5) primary key,
    title varchar(20),
    dept_name varchar(20),
    foreign key (dept_name) references department
        on delete cascade
        on update cascade
);

check (time_slot_id in (
    select time_slot_id
    from time_slot
))

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'
        )
    ));

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

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

select name
from faculty
where dept_name = 'Biology';

create view physics_fall_2009 as
    select course.course_id, sec_id, building, room_number
    from course, section
    where course.course_id = section.course_id
      and course.dept_name = 'Physics'
      and section.semester = 'Fall'
      and section.year = 2009;

create view physics_fall_2009_watson as
    select course_id, room_number
    from physics_fall_2009
    where building = 'Watson';

select course_id, room_number
from physics_fall_2009
where building = 'Watson';

select course_id, room_number
from (
    select course.course_id, building, room_number
    from course, section
    where course.course_id = section.course_id
      and course.dept_name = 'Physics'
      and section.semester = 'Fall'
      and section.year = 2009
)
where building = 'Watson';

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

insert into faculty values ('30765', 'Green', 'Music');

insert into instructor
values ('30765', 'Green', 'Music', null);

create view instructor_info as
    select ID, name, building
    from instructor, department
    where instructor.dept_name = department.dept_name;

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 view S(a, b, c) as
    select a, b, c
    from S1 natural join S2;

select *
from S
where a = 1;

create table student (
    ID varchar(5),
    name varchar(20) not null,
    dept_name varchar(20),
    tot_cred numeric(3, 0) default 0,
    primary key (ID)
);

create index studentID_index on student(ID);

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;

create view geo_instructor as
    select *
    from instructor
    where dept_name = 'Geology';

grant select on geo_instructor to geo_staff;

grant <privilege list>
on <relation name or view name>
to <user list>;

revoke <privilege list>
on <relation name or view name>
from <user list>;

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

revoke select on branch from U1, U2, U3;

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;

create role teaching_assistant;
grant teaching_assistant to instructor;

grant references (dept_name)
on department
to Mariano;