Базы данных
Временны́е данные
date 'yyyy-mm-dd'
time 'hh:mm:ss.zzz'
datetime 'yyyy-mm-dd hh:mm:ss.zzz'
cast (days.fraction as datetime)
current_date, current_time, current_timestamp(n)
interval 'PyYmMdDThHmMsS'
cast(days.fraction as interval)
Событие | Дата | Статус |
---|---|---|
Записался в вуз | 15.07 | Абитуриент |
Принёс документы | 28.07 | Абитуриент с документами |
Приказ о зачислении | 06.08 | Поступивший |
Выдача документов | 01.09 | Первокурсник |
Событие | Год | Высота |
---|---|---|
Геодезические измерения | 1856 | 8840 |
Топографические измерения | 1950 | 8848 |
Китайские измерения | 1975 | 8848.13 |
Измерение по GPS | 1998 | 8850 |
Повторные китайские измерения | 2005 | 8844.43 |
Непальские измерения | 2019 | > 8844 |
Совместные измерения | 2020 | 8848.86 |
Событие | Факт | Документы |
---|---|---|
Родился ребёнок | Рождение | — |
Регистрация рождения | — | Свидетельство о рождении |
Изготовление паспорта | — | Паспорт |
Наступило XX лет | Начало действия | — |
create table entrants ( id int not null primary key, firstName varchar(100) not null, lastName varchar(100) not null, status varchar(100), statusTimestamp timestamp )
create table Students ( id int not null primary key, firstName varchar(100) not null, lastName varchar(100) not null, ) create table StudentGroups ( studentId int not null references students(id), groupId int not null references groups(id), bg date not null, nd date not null )
check (not exists ( select * from StudentGroups s1 inner join StudentGroups s2 on s1.studentId = s2.studentId where greatest(s1.bg, s2.bg) < least(s1.nd, s2.nd) ))
StudentGroups g1 inner join StudentGroups g2 on g1.groupId = g2.groupId using g1 (bg, nd) and g2 (bg, nd)
select g1.*, g2.*, greatest(g1.bg, g2.bg) as bg, least(g1.nd, g2.nd) as nd from StudentGroups g1 inner join StudentGroups g2 on g1.groupId = g2.groupId and greatest(g1.bg, g2.bg) < least(g1.nd, g2.nd)
t1 inner join t2 on condition using t1 (t1.bg, t1.nd) and t2 (t2.bg, t2.nd)
select t1.*, t2.*, greatest(t1.bg, t2.bg) as bg, least(t1.nd, t2.nd) as nd from t1 inner join t2 on condition and greatest(t1.bg, t2.bg) < least(t1.nd, t2.nd)
select t1.*, greatest(t1.bg, t2.bg) as bg, least(t1.nd, t2.nd) as nd from t1 inner join t2 on greatest(t1.bg, t2.bg) < least(t1.nd, t2.nd) union all select t2.*, greatest(t1.bg, t2.bg) as bg, least(t1.nd, t2.nd) as nd from t1 inner join t2 on t1.value <> t2.value and greatest(t1.bg, t2.bg) < least(t1.nd, t2.nd)
select * from StudentGroups where nd = inf
select * from StudentGroups where bg <= :time and :time < nd
select * from StudentGroups
select sum(cast(nd - bg as float) * price) / sum(cast(nd - bg as float)) from prices
insert into StudentGroups(studentId, groupId, bg, nd) values (:studentId, :groupId, current_date, inf)
update StudentGroups set nd = current_date where studentId = :studentId and nd = inf insert into StudentGroups(studentId, groupId, bg, nd) values (:studentId, :groupId, current_date, inf)
update StudentGroups set nd = current_date where studentId = :studentId and nd = inf
update StudentGroups set nd = :bg where studentId = :sid and bg < :bg and :bg < nd
update StudentGroups set :bg = :nd where studentId = :sid and bg < :nd and :nd < bg
delete from StudentGroups where studentId = :sid and :bg <= bg and nd <= :nd
insert into StudentGroups(studentId, groupId, bg, nd) values (:sid, :groupId, :bg, :nd)
insert into StudentGroups select sid, groupId, :nd, nd from StudentGroups where studentId = :sid and bg < :bg and :nd < nd
create table students ( id int not null primary key, firstName varchar(100) not null, lastName varchar(100) not null, ) create table StudentGroups ( studentId int not null references students(id), groupId int not null references groups(id), when date, primary key (studentId, groupId, when) )
insert into StudentGroups(studentId, groupId, when) values (:studentId, :groupId, current_date)