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;select
`events`.`name`,
`users`.`name` as `user_name`,
`executors`.`name` as `executor_name`
from `events`
left join `users` `executors` on `events`.`executor_id` = `executors `.`id`
left join `users` on `events`.`user_id` = `users`.`id`<?php
$art = '(#КЛП1483П1)';
echo trim($art, ')(#');
echo PHP_EOL;
preg_match('/^\(#(.+)\)$/', $art, $m);
echo $m[1];
echo PHP_EOL;echo substr($art, 2, -1);
echo PHP_EOL;select 't1', phone from t1 where phone = '987654321'
union all select 't2', phone from t2 where phone = '987654321'
union all select 't3', phone from t3 where phone = '987654321'WITH countries_links AS (
SELECT JSON_OBJECT('name', `name`, 'iso2', `iso2`, 'links', COUNT(`link_id`)) d
FROM `links`
JOIN `countries` ON `countries`.`iso2` = `links`.`country`
GROUP BY `name`, `iso2`
HAVING COUNT(`link_id`) > 0
) SELECT JSON_ARRAYAGG(d) FROM countries_linksselect distinct t.id_user from T as t
where
t.year = 2023 and
not exists (select id_user from T tt where year = 2022 and tt.id_user = t.id_user);-- LEFT JOIN
select distinct t.id_user
from T as t
left join T tt on tt.year = 2022 and tt.id_user = t.id_user
where t.year = 2023 and tt.id_user is null;
-- EXCEPT
select distinct t.id_user from T as t
where t.year = 2023
except
select distinct t.id_user from T as t
where t.year = 2022
;create table users (
id integer primary key autoincrement,
mute_time int not null default 0
);insert into products (product_id, name, variant, price)
select
coalesce (
min(case when name = 'мышь' then product_id end ),
coalesce(min(product_id), 0) + 1
) product_id
,'мышь', 'красная', 150
from products;