Slava Rozhnev

select d.DEPARTMENT_ID, d.DEPARTMENT_NAME, COUNT(e.EMPLOYEE_ID)
from departments d
left join employees e on d.DEPARTMENT_ID = e.DEPARTMENT_ID
group by d.DEPARTMENT_ID, d.DEPARTMENT_NAME;

SQL online editor

SELECT p.*, category_titles, filter_titles
FROM products as p
LEFT JOIN (
  	SELECT product_id, json_arrayagg(c.title) category_titles from  product_category as pc
	JOIN categories as c ON pc.category_id=c.id
  	GROUP BY product_id
) pc ON  p.id=pc.product_id
JOIN (
  	SELECT product_id, json_arrayagg(f.title) filter_titles 
  	FROM product_filter as pf
	JOIN filters as f ON pf.filter_id=f.id
  	GROUP BY product_id
) pf ON p.id=pf.product_id
WHERE p.id='2'

SQL online editor

$q = mysqli_prepare($connection, "INSERT INTO `user_value` (`user_name`, `user_pass`) VALUES (?, ?)");
$q->bind_param("ss", $user_names, $user_pass);
$q->execute();

run php code online

SELECT "conversations".id 
FROM  "conversations"
JOIN "usersConversations" "uC1" ON "conversations".id = "uC1".conversation_id AND "uC1".user_id = 1
JOIN "usersConversations" "uC2" ON "conversations".id = "uC2".conversation_id AND "uC2".user_id = 2
WHERE  "conversations"."isPrivate" = true 
ORDER BY "conversations".id DESC 
LIMIT 1;

SELECT "conversations".id 
FROM  "conversations"
WHERE  "conversations"."isPrivate" = true
AND EXISTS (SELECT 1 FROM  "usersConversations" WHERE "conversations".id = "usersConversations".conversation_id AND "usersConversations".user_id = 1) 
AND EXISTS (SELECT 1 FROM  "usersConversations" WHERE "conversations".id = "usersConversations".conversation_id AND "usersConversations".user_id = 2) 
ORDER BY "conversations".id DESC 
LIMIT 1;

SELECT 
  p.* 
FROM 
  products as p 
WHERE 
  EXISTS (
    SELECT 1 FROM product_filter pf WHERE pf.filter_id = 1 AND pf.product_id = p.id
  ) 
  AND EXISTS (
	SELECT 1 FROM product_filter pf WHERE pf.filter_id = 2 AND pf.product_id = p.id
  );

online sql editor

В соответствии с наставлениями Akina :

with order_files as (
	select 
  		*, 
  		row_number() over (partition by filename order by version desc, created_at desc) rn
  	from files 
  	where user_id = 1
) select id, filename, user_id, version, created_at
from order_files where rn = 1
;

online sql editor

SELECT * 
FROM `articles` 
LEFT JOIN `categories` ON articles.id_categorie = categories.id_categorie;

SELECT * 
FROM `articles` 
LEFT JOIN `categories` USING (id_categorie);

WITH d AS (
SELECT 
	*,
	row_number() over (partition by key order by created desc) rn
FROM move_history
 ) SELECT * 
 FROM d 
 WHERE status = 'COMPLETED' 
 AND rn = 1
 ORDER BY id;

run SQL online

SELECT userid
FROM tbl 
GROUP BY userid
HAVING COUNT(userid) = 1;

SQL online

SELECT DISTINCT
    company_name 
FROM cabs 
WHERE NOT (company_name LIKE ‘%Chicago%’ OR  company_name  LIKE  ‘%taxi%’ )
LIMIT 10;

SELECT `payment`.* 
FROM `payment` 
WHERE EXISTS (
    SELECT 1 FROM  `payment_deposit` WHERE  `payment`.`id` = `payment_deposit`.`payment_id` 
)
ORDER BY `id` DESC 
LIMIT 20

Это можно сделать более чем одним методом.
Ниже вы можете найти 2 возможных решения

select * from clc
 where not exists (select user_id from stat where stat.user_id = clc.user_id);


select clc.* 
from clc
left join stat on stat.user_id = clc.user_id
where stat.user_id is null;

MySQL online test

Согласен с Кирилл Пальчевский , что следует привести данные к нормальной форме, но если нужно то:

SELECT 
	`tokens`.* 
FROM `tokens` 
ORDER BY REPLACE(SUBSTRING_INDEX(s_short,';',3), SUBSTRING_INDEX(s_short,';',2), '') DESC;

Или компактный вариант предложенный Akina

SELECT 
  `tokens`.* 
FROM `tokens` 
ORDER BY SUBSTRING_INDEX(SUBSTRING_INDEX(s_short,';',3),';',-1) DESC;

MySQL SUBSTRING_INDEX test

Я бы предложил следующую структуру таблиц:

CREATE TABLE employees (
	id SERIAL PRIMARY KEY,
	manager_id INT REFERENCES employees(id),
	full_name VARCHAR,
	address VARCHAR,
	phone VARCHAR,
	hired_at TIMESTAMP,
	fired_at TIMESTAMP
);

CREATE TABLE order_states (
	id SERIAL PRIMARY KEY,
	state VARCHAR
);

CREATE TABLE orders (
	id SERIAL PRIMARY KEY,
	customer VARCHAR,
	address VARCHAR,
	courier_id INT REFERENCES employees(id),
	price NUMERIC
);

CREATE TABLE trace (
	id BIGSERIAL PRIMARY KEY,
	state_id INT REFERENCES order_states(id),
	order_id INT REFERENCES orders(id),
	updated_at TIMESTAMP
);

CREATE INDEX idx_employees_manager_id ON employees(manager_id);

CREATE INDEX idx_orders_courier_id ON orders(courier_id);

CREATE INDEX idx_trace_state_id_order_id ON trace(state_id, order_id);

SQLize online

select 
	row.user_id, count(*) matches_row_count
from users user_row
join users row on 
	(user_row.a_id = row.a_id) and 
	(user_row.b_id = row.b_id) and 
	(user_row.id != row.user_id)
where user_row.user_id = 1
group by row.user_id;

Test query online