SELECT city.name
FROM city
WHERE EXISTS (
SELECT city FROM ad_cars WHERE ad_cars.city = city.name
);select
o.id, o.name, o.price,
group_concat(case when attrib_name = 'цвет' then attrib_value end) color,
group_concat(case when attrib_name = 'размер' then attrib_value end) size,
group_concat(case when attrib_name = 'дата' then attrib_value end) date
from orders o
left join order_attributes oa on o.id = oa.order_id
group by o.id, o.name, o.price;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"] |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`select 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
;SELECT * ....
EXCEPT
SELECT * ....;CREATE PROCEDURE [dbo].[addTask]
@definition varchar(1000),
@userEmail varchar(30)
AS
BEGIN
IF (@userEmail is not null)
BEGIN
INSERT INTO Задача(Описание) VALUES (@definition)
INSERT INTO Реализация_задачи (ID_работника, ID_задачи)
SELECT ID_работника, SCOPE_IDENTITY()
FROM Работник WHERE Электронная_почта = @userEmail
END
END
GO
EXEC addTask @definition = 'New task', @userEmail = 'Employee@mail.com'
GOSELECT EMPLOYER.EMP_NAME, DEPARTMENT.DEP_NAME, POSITION, SALARY
FROM SALARY
JOIN EMPLOYER ON EMPLOYER.EMP_ID = SALARY.EMP_ID
JOIN DEPARTMENT ON DEPARTMENT.DEP_ID = EMPLOYER.DEP_ID
WHERE PERIOD = 201905;select * from tbl
where id_ref = 99 and delete = 0
order by id_carrier desc
limit 1;delete t.*
from t
left join t t1 on t.a = t1.a and t.id > t1.id
where t1.id is not null;UPDATE accounts
SET
email = COALESCE($2, accounts.email) ,
first_name = COALESCE($3, accounts.first_name) ,
last_name = COALESCE($4, accounts.last_name)
WHERE id = $1
RETURNING *select
req_id, req_text,
recipient.name as recipient_name,
sender.name as sender_name
from request
join users as recipient on request.recipient_id = recipient.id
join users as sender on request.sender_id = sender.id;SELECT count(*) from `runs` where `date` >= unix_timestamp(curdate());SELECT count(distinct client) from `runs` where `date` >= unix_timestamp(curdate());SELECT COUNT(*) FROM (
SELECT
`client`, MIN(`date`) `first_run`
FROM `runs`
GROUP BY `client`
HAVING `first_run` >= unix_timestamp(curdate())
) `todays_first_runs`;CREATE SEQUENCE profiles_seq START 1;
CREATE OR REPLACE FUNCTION nextval_rand(regclass)
RETURNS text AS
$func$
BEGIN
EXECUTE format('ALTER SEQUENCE profiles_seq INCREMENT %s', (random() * 100)::int + 1);
RETURN 'Пользователь #' || nextval($1)::text;
END
$func$ LANGUAGE plpgsql SECURITY DEFINER;
create table profiles (
id uuid not null,
username text default nextval_rand('profiles_seq'::regclass),
primary key (id)
);
insert into profiles (id) values
(gen_random_uuid()),
(gen_random_uuid()),
(gen_random_uuid()),
(gen_random_uuid()),
(gen_random_uuid());
select * from profiles;SELECT id
FROM tbl
GROUP BY id
HAVING MIN(discount) = 'PROMO' AND MAX(discount) = 'PROMO'select `order`, item, count(discount)
from store
group by `order`, item
having count(discount) = count(case when discount='PROMO' then 1 end)select *
from a,
lateral (select * from b where b.a_id = a.id order by d desc limit 1) m;