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 = 'M3437'
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 условие]

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

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 is not null or
not exists (select * from Points p
where p.SId = SId and p.CId = CId and Point < 60)

create assertion NonEmptyGroup check (not exists
    (select * from Groups 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 CascadeCourses
on Courses after delete
referencing old row c for each row
delete from points p where p.CId = c.CId

create trigger AuditPoints
on Points after insert, update
referencing new row p
for each row set p.UpdateTime = current_timestamp()

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

drop view имя

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

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, current_timestamp(), 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 privileges}
on {table|database|view|procedure|function|…} имя
to {пользователь|group группа|public}
[with grant option]

grant all privileges 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 privileges}
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 privileges 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;