insert into таблица [(атрибуты)]
values (значения) [, ещё строки]

insert into таблица [(атрибуты)]
запрос

insert into Courses (Name, Lecturer) values
('БД', 'Корнеев Г.'),
('ППО', 'Киракозов Ю.')

insert into Points
select SId, CId, 0 from Students, Courses

update таблица set значения [where условие]

атрибут = выражение

update Students set GroupId = '4537'
where LastName = 'Иванов'

update Students set
PassNo = '1234', PassSeries = '567890'
where SId = 1

update Points set points = points + 1 where CId in
(select CId from Courses where Name = 'СУБД')

delete from таблица [where условие]

truncate таблица -- DDL

delete from Points where CId =
in (select CId from Courses where Name = 'СУБД')

truncate Points

merge into таблица [[as] имя1] 
using данные [[as] имя2] 
on условие
when matched [and ...] then действие1
when not matched [and ...] then действие2

merge into Points
using (select * from Courses c, Students s) r
on Points.CId = r.CId and Points.SId = r.SId
when matched then update set points = points + 1
when not matched then insert (SId, CId, points)
    values (r.SId, r.CId, 1)

Date

create type Name as object (
    FirstName varchar(100),
    LastName varchar(100)
)

create type Points as object
(points as int between 0 and 100)

Enroll Date not null

SName Name

CoursePoints Points

primary key (SId, CId)

unique (PassNo, PassSeries)

foreign key (SId) references Students(SId)

check (Points between 0 and 100)

SId in (select SId from Students)

ExpulsionDate not null or
not exists (select * from Points p
where p.SId = SId, p.CId = CId and Point < 60)

create assertion NonEmptyGroup check (not exists
    (select * from Group g where not exists
        (select * from Students s where s.GId = g.GId)))

foreign key (столбцы)
references таблица [(столбцы)]
[on delete действие]
[on update действие]

foreign key (CId) references Courses
on delete cascade on update cascade

foreign key (SId) references Students
on delete restrict on update cascade

create trigger имя on объект
{before | after | instead of} {insert | update | delete}
[объявление ссылок]
[for each {row | statement}]
действие

referencing {new|old} {row|table} имя

create trigger on Course after delete
referencing old row c for each row
delete from points p where p.CId = c.CId

create trigger on Points after insert, update
referencing new row p
for each row set p.time = now()

create view имя(столбцы) as запрос

drop view имя

create view AveragePoints(SId, AvgPoints) as
select SId, avg(Points) from Points

create view StudentCourse(FN, LN, N, P) as
select s.FirstName, s.LastName, c.Name, p.Points
from Students s natural join Points p natural join
Courses c

• Если кортеж удовлетворяет предикату $R_1$ ⇒ вставить в $R_1$
• Если кортеж удовлетворяет предикату $R_2$ ⇒ вставить в $R_2$

create materialized view имя
[
    refresh [{fast|complete}] [on {demand|commit}]
    [start with время] [next время]
]
as запрос

refresh materialized view имя

create materialized view AveragePoints
refresh next dateadd(day, now(), 1)
as select SId, avg(Points) from Points group by SId

create user имя [password 'пароль']

alter user имя [password 'пароль']

drop user имя

create user kgeorgiy;

alter user kgeorgiy password '***';

drop user kgeorgiy;

create group имя

alter group имя {add|drop} user пользователь

drop group имя

create group lecturers;

alter group lecturers add user kgeorgiy;

drop group lecturers;

grant {select|insert|update|delete|create
         |execute|trigger|usage|…|all priviledges}
on {table|database|view|procedure|function|…} имя
to {пользователь|group группа|public}
[with grant option]

grant all priviledges on Students 
  to group Deans with grant option;

grant select on Students to public;

revoke [grant option for]
{select|insert|update|delete|create
   |execute|trigger|usage|…|all priviledges}
on {table|database|view|procedure|function|…} имя
from {пользователь|group группа|public}
[cascade|restrict]

revoke insert on Students 
    from group Deans cascade;

revoke update on Students from public;

alter {table|schema|database|…}
owner to пользователь

create view FITPStudents as
select * from Students
where GId in
(select GId from Group where FId = 'ИТиП')

grant all priviledges on FITPStudents to FITPDean;

create view PublicStudents as
select SId, FirstName, LastName
   /* No passport info, etc */
from Students

grant select on PublicStudents to public;

create view StudentPoints as
select SId, avg(points)
from Students natural join Marks

grant select on StudentPoints to public;