create table timeslots (
staff_id int,
date date,
time_from time,
time_to time,
free boolean default true
);
CREATE EXTENSION btree_gist;
create table timeslots (
staff_id int,
slot_time tsrange,
free boolean default true,
EXCLUDE USING GIST (staff_id WITH =, slot_time WITH &&)
);
create table users (
id integer primary key autoincrement,
name text,
email text
);
create table journal (
user_id integer references users(id),
event_date timestamp
/*
остальные поля журнала
*/
);
SELECT *
FROM tbl
ORDER BY ...
LIMIT 40, 10
SELECT *
FROM tbl
ORDER BY ...
LIMIT 10 OFFSET 40
select *
from blocks
join (
select min(b.id) id
from blocks b
join links l on b.link_id = l.id
where l.id = 1
) fb on fb.id = blocks.id;
select products.id, products.name, jsonb_agg(url)
from products
left join products_photos on products.id = product_id
group by products.id, products.name
|----|---------|----------------------------------|
| id | name | jsonb_agg |
|----|---------|----------------------------------|
| 1 | Товар 1 | ["example1.png", "example2.png"] |
if (in_array($person, $arr['Ученик'])) {
echo "$person is Ученик";
} elseif (in_array($person, $arr['Учитель'])) {
echo "$person is Учитель";
}
foreach ($arr as $position => $names) {
if (in_array($person, $names)) {
echo "$person is $position";
}
}
create table users (
id serial primary key,
balance numeric(9, 2)
);
create table user_cards (
id serial,
user_id int references users(id),
filename text
);
CREATE TABLE species (
species_id INTEGER PRIMARY KEY,
type_id INTEGER,
species_name VARCHAR(255) NOT NULL,
species_amount INTEGER,
date_start DATE,
species_status ENUM ('active', 'absent', 'fairy') NOT NULL DEFAULT 'active'
);
CREATE TABLE species_statuses (
id INTEGER PRIMARY KEY,
name VARCHAR(255) NOT NULL
);
INSERT INTO species_statuses VALUES (1, 'active'), (2, 'absent'), (3, 'fairy');
CREATE TABLE species (
id INTEGER PRIMARY KEY,
type_id INTEGER,
name VARCHAR(255) NOT NULL,
amount INTEGER,
date_start DATE,
status_id INTEGER DEFAULT 1,
FOREIGN KEY (status_id) REFERENCES species_statuses(id)
);
create table customers (
id serial PRIMARY KEY,
name text
);
create table orders (
id serial PRIMARY KEY,
customer_id int
);
alter table orders ADD FOREIGN KEY (customer_id) REFERENCES customers(id) ;
у них будут отдельные БДэто не правильно! Должна быть общая база с таблицами отелей, комнат и бронирования
create table hotels (
id serial primary key,
name text
-- other fields
);
create table rooms (
id serial primary key,
hotel_id int references hotels(id),
description text
-- other fields
);
create table bookings (
id serial primary key,
room_id int references rooms(id),
check_in date,
check_out date,
status smallint
-- other fields
);
-- get free rooms
select *
from rooms
join hotels on hotels.id = rooms.hotel_id
where not exists (
select true from bookings
where
room.id = bookings.room_id and
'requested_check_in' between check_in and check_out and
'requested_check_out' between check_in and check_out
) and (
-- other filters
)
CREATE INDEX email_ix ON Staff(email);
CREATE INDEX jobtitle_id_ix ON Staff(jobtitle_id);
SELECT Jobtitles.name, salary
FROM Jobtitles
JOIN Staff ON Staff.jobtitle_id = Jobtitles.jobtitle_id
ORDER BY salary DESC
FETCH FIRST ROW WITH TIES;
UPDATE winka SET losse = losse + 1 WHERE user_id = 1;
UPDATE winka SET win = win + 1 WHERE user_id = 1;