select name, s_name, email
from users
where users.id = 1 and exists (
select 1 from orders where user_id = users.id and product_id = 2
);select distinct name, s_name, email
from users
join orders on orders.user_id = users.id
where users.id = 1 and product_id = 2;select *
from cars
where not exists (
select 1
from rental
where
rental.car = cars.regnum
and CURRENT_DATE() between start_date and end_date
);select *
from cars
where exists (
select 1
from rental
where
rental.car = cars.regnum
and CURRENT_DATE() between start_date and end_date
);select *
from cars
where exists (
select 1
from rental
where
rental.car = cars.regnum
and start_date > CURRENT_DATE()
);create table t (
a int
);
insert into t values (2000000000),(2000000000),(2000000000),(2000000000);
select sum(a) * 99 from t;+==============+
| sum(a) * 99 |
+==============+
| 792000000000 |
+--------------+create table rest_zones (
id int primary key,
description text
);
create table bookings (
id int primary key,
rest_zone_id int,
from_date date,
to_date date,
index(rest_zone_id),
foreign key (rest_zone_id) references rest_zones(id)
);SELECT user_f_5, user_f_88
FROM sb_plugins_1
WHERE user_f_5 <= 299 AND p_active = 1 AND user_f_88 IN ('ready','nonal')
ORDER BY user_f_88 = 'ready' DESC, user_f_5 DESC
LIMIT 15select *
from news
where not exists (
select 1 from user_news
where user_news.news_id = news.id and user_news.user_id = news.user_id
);select
ads.id, ads.title,
json_arrayagg(photo) as photos
from ads
left join photos on ads.id = photos.id_ad
group by ads.id, ads.title
;select
groups.id_group,
groups.name_group,
count(distinct users.id_user) size_group
from groups
join users on users.id_group = groups.id_group
group by groups.id_group, groups.name_group
having count(distinct users.id_user) between 1 and 5
;select * from test
join (
select "hash" from test where "on" order by random() limit 1
) t on t.hash = test.hash;SELECT
jobs.job_title,
AVG(employees.salary) average_salary
FROM
employees
JOIN jobs ON employees.job_id = jobs.job_id
WHERE
jobs.job_title Like '%Manager'
GROUP BY jobs.job_title
HAVING AVG(employees.salary) > 10000;SELECT
ID,
tel_balance1 * (tel1 LIKE "38090%") +
tel_balance2 * (tel2 LIKE "38090%") +
tel_balance3 * (tel3 LIKE "38090%") AS balance_38090
FROM
telephone
WHERE
tel1 LIKE "38090%"
OR tel2 LIKE "38090%"
OR tel3 LIKE "38090%"
;SELECT
`id_user`,
SUM(IF(`balance_history`.`date` >= NOW() - INTERVAL 1 DAY, `sum`, 0)) `sum_24h`,
SUM(`sum`) `sum_1mnth`,
`users`.`name`
FROM `balance_history`
INNER JOIN `users` ON `id_user` = users.id
WHERE `balance_history`.`date` >= NOW() - INTERVAL 1 MONTH AND `type` = 'plus'
GROUP BY `id_user`
ORDER BY `sum` DESC
LIMIT 5;create table genres (
id int primary key auto_increment,
name varchar(255)
);
create table films (
id int primary key auto_increment,
name varchar(255)
);
create table film_genres (
film_id int,
genre_id int
);select g.id, g.name, count(distinct price)
from goods g
join prices p on g.id = p.id_goods
where price in (200, 205) -- prices that we need to search
group by g.id, g.name
having count(distinct price) = 2; -- 2 count different pricesupdate test
set html = replace(
html,
substr(
html,
locate('<nav>', html, 1) + 5,
locate('</nav>', html, locate('<nav>', html, 1))-locate('<nav>', html, 1) - 5
), '');select class
from students
group by class
having count(*)*5 = sum(rang);SELECT
`t1`.*,
GROUP_CONCAT(`t3`.`name` SEPARATOR ', ') AS `role_group`
FROM
`user` AS `t1`
JOIN `user_role` AS `t2`
ON
`t1`.`id` = `t2`.`user_id`
JOIN `access_group` AS `t3`
ON
`t2`.`group_id` = `t3`.`id`
GROUP BY
`t1`.`id`
HAVING SUM(`group_id` IN('20', '24')) = 2
LIMIT 0, 20UPDATE first_table
JOIN second_table ON first_table.id = second_table.id
SET first_table.count = first_table.count + second_table.total_count, second_table.total_count = 0
WHERE second_table.id = 1;