SQL. Tasks from https://habr.com/ru/post/461567/

01.sql

SELECT *
FROM learnsql_employees;

02.sql

SELECT *
FROM learnsql_employees t1
WHERE t1.first_name='David'

03.sql

-- ?

SELECT *
FROM learnsql_employees t1
WHERE t1.job_id="IT_PROG";


SELECT t1.first_name, t1.second_name, t2.title
FROM learnsql_employees t1
JOIN learnsql_jobs t2
ON t1.job_id=t2.id
WHERE t2.title='jobA';

04.sql

SELECT *
FROM learnsql_employees t1
WHERE t1.salary>400 AND t1.department_id = 2;

-- Здесь мб условия местами поменять? Как ведется сравнение записей?

05.sql

SELECT *
FROM learnsql_employees t1
WHERE t1.department_id=1 OR t1.department_id = 2;

06.sql

SELECT *
FROM learnsql_employees t1
WHERE t1.first_name LIKE '%1';

07.sql

SELECT *
FROM learnsql_employees t1
WHERE (t1.department_id=1 OR t1.department_id = 2)  AND t1.commission_pct is not Null;

08.sql

SELECT *
FROM learnsql_employees t1
WHERE t1.first_name LIKE '%n%n%'

09.sql

--(it's no true)
SELECT *
FROM learnsql_employees t1
WHERE lenght(t1.first_name) > 4;

--(it's true)
SELECT *
FROM learnsql_employees t1
WHERE t1.first_name LIKE '%_____%';

10.sql

-- it's my solution
SELECT *
FROM learnsql_employees t1
WHERE 1000 < t1.salary AND t1.salary <= 12000;

-- it's not my solution
SELECT *
FROM learnsql_employees t1
WHERE t1.salary BETWEEN 8000 AND 90000;

11.sql

SELECT *
FROM learnsql_employees t1
WHERE t1.first_name LIKE '%!%%' ESCAPE '!';

12.sql

SELECT DISTINCT t1.manager_id
FROM learnsql_employees t1
WHERE t1.manager_id IS NOT NULL;

-- еще есть ALL после селекта, используетс по умолчанию

13.sql

--  опять же они хотят название работы, а значит нужен джоин как в 3.sql?

-- не знаю как решать

14.sql

--(it's true)
SELECT *
FROM learnsql_employees t1
WHERE length(t1.first_name) > 4;

--(it's NO true)
SELECT *
FROM learnsql_employees t1
WHERE t1.first_name LIKE '%_____%';

15.sql

SELECT *
FROM learnsql_employees t1
WHERE LOWER(t1.first_name) LIKE '%b%';


-- author's solution
SELECT *
  FROM employees
 WHERE INSTR (LOWER (first_name), 'b') > 0;
 
 --https://docs.microsoft.com/ru-ru/office/vba/language/reference/user-interface-help/instr-function

16.sql

SELECT *
FROM learnsql_employees t1
WHERE INSTR(LOWER(t1.first_name),'a',1,2) > 0;

-- Error! function instr(text, unknown, integer, integer) does not exist

17.sql

SELECT *
FROM learnsql_employees t1
WHERE t1.salary % 1000 = 0;

-- author's solution
SELECT *
FROM learnsql_employees t1
WHERE MOD(t1.salary, 1000) = 0;

18.sql

-- https://docs.microsoft.com/ru-ru/sql/t-sql/functions/string-functions-transact-sql?view=sql-server-ver15
-- какие алгоритмы лежат в основе сравнения строк?

SELECT *, SUBSTR(t1.phone_number, 1, 3)
FROM learnsql_employees t1
WHERE t1.phone_number LIKE '___.___.____';

19.sql

-- error: function instr(character varying, unknown) does not exist

SELECT *, SUBSTR(t1.name, 1, INSTR(t1.name, ' '))
FROM learnsql_departments t1
WHERE t1.name LIKE '% %';

20.sql

SELECT *, SUBSTR(t1.first_name, 2, length(t1.first_name)-2)
FROM learnsql_employees t1
;

21.sql

SELECT *
FROM learnsql_employees t1
WHERE LENGTH(t1.first_name) < 5 AND INSTR(t1.first_name, 'm') = LENGTH(t1.first_name)-2;
--вновь аналогичная ошибка
-- а еще это решение сломается если в имени будет более дву m

SELECT *
FROM learnsql_employees t1
WHERE LENGTH(t1.first_name) < 7 AND SUBSTR(t1.first_name, LENGTH(t1.first_name), 1) = 'd';

22.sql

-- https://webcodius.ru/sql/sql-funkcii-daty-i-vremeni.html

-- solution on PostgreSQL https://stackoverflow.com/questions/54584082/how-to-convert-oracle-next-day-function-to-postgres-sql ))

23.sql

-- https://pgcookbook.ru/programming/date_and_time.html

SELECT *
FROM learnsql_employees t1
WHERE (now()::date - t1.hire_date)/356>17;

24.sql

-- не понял

25.sql

-- error. again. function instr(character varying, unknown) does not exist

SELECT t1.first_name, t1.second_name, t2.title
FROM learnsql_employees t1
JOIN learnsql_jobs t2
ON t1.job_id=t2.id
WHERE LENGTH(SUBSTR(t2.title, INSTR(t2.title, '_')+1, -1)) > 3 
AND SUBSTR(t2.title, INSTR(t2.title, '_')+1, -1) != 'CLERK';

26.sql

SELECT t1.*, REPLACE(t1.phone_number, '.', '-')
FROM learnsql_employees t1;

27.sql

SELECT t1.*
FROM learnsql_employees t1
WHERE date_part('day', t1.hire_date)=1;

28.sql

SELECT t1.*
FROM learnsql_employees t1
WHERE date_part('year', t1.hire_date)=2000;


SELECT t1.*
FROM learnsql_employees t1
WHERE to_char(t1.hire_date, 'YYYY')='2000';

29.sql

-- ! https://www.postgresql.org/docs/current/functions-datetime.html

-- output: Tommorow is	30.0	day of	9.0
SELECT 'Tommorow is ', 
 date_part('day', now()::timestamp+'1 day'::interval), 
 ' day of ', 
 date_part('month', now()::timestamp+'1 day'::interval);
 
 
 --output: Tommorow is	wednesday	day of	september
 SELECT  'Tommorow is ', 
to_char(now()::timestamp+'1 day'::interval, 'day'), 
 ' day of ', 
to_char(now()::timestamp+'1 day'::interval, 'month');

--Tommorow is	30.0	day of	september
SELECT  'Tommorow is ', 
date_part('day', now()::timestamp+'1 day'::interval), 
 ' day of ', 
to_char(now()::timestamp+'1 day'::interval, 'month');

30.sql

SELECT t1.first_name, t1.second_name, date_part('day', t1.hire_date),'st of ', to_char(t1.hire_date::timestamp, 'month'), ' ', to_char(t1.hire_date, 'YYYY') 
FROM learnsql_employees t1;


-- to_char() https://oracleplsql.ru/to_char-postgresql.html
SELECT t1.first_name, t1.second_name, to_char(t1.hire_date, 'DDth of Month, YYYY') 
FROM learnsql_employees t1;

31.sql

SELECT t1.first_name, t1.second_name, to_char(coalesce(t1.salary, 0)*1.2, '9999999L')
FROM learnsql_employees t1;

32.sql

SELECT t1.first_name, t1.second_name, t1.hire_date
FROM learnsql_employees t1
WHERE  date_part('month', t1.hire_date) = 5 AND date_part('year', t1.hire_date) = 2020;

33.sql

SELECT now(),
       now()::timestamp+'1 second'::interval,
       now()::timestamp+'1 minute'::interval,
       now()::timestamp+'1 hour'::interval,
       now()::timestamp+'1 day'::interval,
       now()::timestamp+'1 month'::interval,
       now()::timestamp+'1 year'::interval
;

34.sql

SELECT t1.first_name, t1.second_name, to_char(coalesce(t1.salary, 0)+coalesce(t1.commission_pct, 0)*coalesce(t1.salary, 0)/100, 'L99,999.00')
FROM learnsql_employees t1;

35.sql

SELECT t1.first_name, t1.second_name, 
CASE 
WHEN t1.commission_pct IS NOT NULL THEN 'YES'
ELSE 'NO'
END
FROM learnsql_employees t1;

36.sql

SELECT t1.first_name, t1.second_name, t1.salary,
CASE
  WHEN t1.salary < 5000 THEN 'low'
  WHEN t1.salary >= 5000 AND t1.salary < 10000 THEN 'normal'
  WHEN t1.salary >= 10000 THEN 'high'
  ELSE 'unknown'
END
FROM learnsql_employees t1;

37.sql

SELECT t1.*,
CASE
  WHEN t1.region_id = 1 THEN 'Europe'
  WHEN t1.region_id = 2 THEN 'America'
  WHEN t1.region_id = 3 THEN 'Asia'
  WHEN t1.region_id = 4 THEN 'Africa'
  ELSE 'unknown'
END
FROM learnsql_countries t1;

38.sql

SELECT t1.department_id,
  count(*),
  MIN(t1.salary),
  MAX(t1.salary),
  MIN(t1.hire_date),
  MAX(t1.hire_date)
FROM learnsql_employees t1
GROUP BY department_id
ORDER BY count(*) DESC;

39.sql

SELECT *
FROM (
SELECT 
  SUBSTR(t1.first_name, 1, 1) AS a,
  COUNT(*) AS cnt
FROM learnsql_employees t1
GROUP BY a
ORDER BY count(*) DESC
) AS t2
WHERE t2.cnt > 1;

-- Сохраняется ли сортировка? Или ее лучше делать самой последней?


-- Почему having cnt > 1 не работает? 
--Зачем понадобилось вводить having когда есть where
SELECT 
  SUBSTR(t1.first_name, 1, 1) AS a,
  COUNT(*) AS cnt
FROM learnsql_employees t1
GROUP BY a
HAVING COUNT(*) > 1
ORDER BY count(*) DESC
;

40.sql

SELECT 
  t1.department_id AS a,
  t1.salary AS b,
  COUNT(*) AS cnt
FROM learnsql_employees t1
GROUP BY a, b
ORDER BY cnt DESC
;

41.sql

SELECT 
  to_char(t1.hire_date, 'day') AS a,
  COUNT(*) as cnt
FROM learnsql_employees t1
GROUP BY a
ORDER BY cnt DESC
;

42.sql

SELECT 
  to_char(t1.hire_date, 'YYYY') AS a,
  COUNT(*) as cnt
FROM learnsql_employees t1
GROUP BY a
ORDER BY cnt DESC
;

43.sql

SELECT count(*)
FROM (
SELECT 
  t1.department_id as a
FROM learnsql_employees t1
WHERE t1.department_id is not null
GROUP BY a
) as t2;

44.sql

SELECT 
  t1.department_id as a,
  count(*) as cnt
FROM learnsql_employees t1
WHERE t1.department_id is not null
GROUP BY a
HAVING count(*) > 1
;

45.sql

SELECT 
  t1.department_id as a,
  TRUNC(coalesce(AVG(t1.salary), 0), 0) as s
FROM learnsql_employees t1
GROUP BY a
;

46.sql

SELECT 
  t1.region_id as a
FROM learnsql_countries t1
GROUP BY a
HAVING SUM(LENGTH(t1.name)) > 10
;

47.sql

SELECT 
  t2.a as c,
  count(*)
FROM (
SELECT 
  t1.department_id as a,
  t1.job_id as b
FROM learnsql_employees t1
GROUP BY a, b
) as t2
GROUP BY c
HAVING count(*) > 1
;


--authors solution
SELECT 
  t1.department_id as a
FROM learnsql_employees t1
GROUP BY a
HAVING COUNT(DISTINCT t1.job_id)>1
;

48.sql

SELECT 
  t1.manager_id as a
FROM learnsql_employees t1
GROUP BY a
HAVING COUNT(*)>5 AND SUM(t1.salary)>5000
;

49.sql

SELECT 
  t1.manager_id as a
FROM learnsql_employees t1
WHERE t1.commission_pct IS NULL
GROUP BY a
HAVING COUNT(*)>5 AND AVG(t1.salary) BETWEEN 600 and 900
;

50.sql

SELECT 
  MAX(t3.salary) as m
FROM 
(SELECT t1.*, t2.title
FROM learnsql_employees t1
JOIN learnsql_jobs t2
ON t1.job_id=t2.id) t3
WHERE t3.title LIKE '%CLERK'
;

51.sql

SELECT 
  t1.department_id as a,
  ROUND(AVG(t1.salary)) as s
FROM learnsql_employees t1
GROUP BY a
ORDER BY s
LIMIT 1
;

--authors solution. Не работает. В PostgreSQL нельзя применять одновременно на одно поле две агрегатные функции?
SELECT 
  MAX(AVG(t1.salary)) as s
FROM learnsql_employees t1
GROUP BY t1.department_id
;

52.sql

SELECT 
  t1.first_name,
  COUNT(*)
FROM learnsql_employees t1
WHERE LENGTH(t1.first_name)>5
GROUP BY t1.first_name
HAVING COUNT(*) > 10
;

53.sql

SELECT
  t6.name AS a,
  COUNT(*)
FROM (
  learnsql_employees t2
  JOIN learnsql_departments t3 ON t2.department_id = t3.id
  JOIN learnsql_locations t4 ON t3.location_id = t4.id
  JOIN learnsql_countries t5 ON t4.country_id = t5.id
  JOIN learnsql_regions t6 ON t5.region_id = t6.id
)
GROUP BY a
;

54.sql

SELECT
  t2.first_name, 
  t2.second_name, 
  t3.name,
  t7.title, 
  t4.street_address, 
  t5.name, 
  t6.name
FROM (
  learnsql_employees t2
  JOIN learnsql_departments t3 ON t2.department_id = t3.id
  JOIN learnsql_locations t4 ON t3.location_id = t4.id
  JOIN learnsql_countries t5 ON t4.country_id = t5.id
  JOIN learnsql_regions t6 ON t5.region_id = t6.id
  JOIN learnsql_jobs t7 ON t2.job_id = t7.id
)

;

55.sql

SELECT
  t2.first_name as a

FROM (
  learnsql_employees t1
  JOIN learnsql_employees t2 ON t1.manager_id = t2.id
)
GROUP BY a
HAVING COUNT(*) > 1
;

56.sql

SELECT t1.* 
FROM learnsql_employees t1
WHERE t1.manager_id IS NULL
;

58.sql

SELECT
  t2.first_name, 
  t2.second_name,  
  t6.name
FROM (
  learnsql_employees t2
  JOIN learnsql_departments t3 ON t2.department_id = t3.id
  JOIN learnsql_locations t4 ON t3.location_id = t4.id
  JOIN learnsql_countries t5 ON t4.country_id = t5.id
  JOIN learnsql_regions t6 ON t5.region_id = t6.id
)
WHERE t6.name = 'regionA'
;

--А можно делать join по двум и более полям?

59.sql

SELECT
  t3.name as a,
  COUNT(*)
FROM (
  learnsql_employees t2
  JOIN learnsql_departments t3 ON t2.department_id=t3.id
)
GROUP BY a
HAVING COUNT(*) > 1
;

60.sql

SELECT t1.* 
FROM learnsql_employees t1
WHERE t1.department_id IS NULL
;

--уже второй раз автор предлагает решение простой задачи через джоин. В это есть какой-то сакральный смысл, помимо учебного?

61.sql

SELECT t2.name a
FROM (
  learnsql_departments t2
  left JOIN learnsql_employees t1 ON t1.department_id=t2.id
)
WHERE t1.department_id is null
GROUP BY a
;

62.sql

SELECT t2.first_name
FROM (
  learnsql_employees t2
  LEFT JOIN learnsql_employees t1 ON t1.manager_id=t2.id
)
where t1.manager_id is null ;

63.sql

SELECT
  t2.first_name, 
  t3.name,
  t4.title
FROM (
  learnsql_employees t2
  JOIN learnsql_departments t3 ON t2.department_id = t3.id
  JOIN learnsql_jobs t4 ON t2.job_id = t4.id
)

;

64.sql

SELECT t1.first_name
FROM (
  learnsql_employees t1
  LEFT JOIN learnsql_employees t2 ON t1.manager_id=t2.id
)
where to_char(t2.hire_date, 'YYYY')='2020' and to_char(t1.hire_date, 'YYYY')<'2020';

--влияет ли порядок условий на скорость работы?

65.sql

SELECT t1.first_name
FROM (
  learnsql_employees t1
  LEFT JOIN learnsql_employees t2 ON t1.manager_id=t2.id
  LEFT JOIN learnsql_jobs t3 ON t1.job_id=t3.id
)
where to_char(t2.hire_date, 'MM')='09' and length(t3.title) > 1
;

66.sql

-- Вообще этот запрос получает только одно длинное имя, а их может быть несколько одинаковых. Поэтому надо еще один селект с условием сравнения длины имен
SELECT t1.first_name a, LENGTH(t1.first_name) b
FROM learnsql_employees t1
ORDER BY b DESC
LIMIT 1
;

67.sql

SELECT t2.first_name, t2.salary
FROM learnsql_employees t2
WHERE t2.salary > (
  SELECT AVG(salary)
  FROM learnsql_employees t1
  )

;

68.sql

Таблица Employees, Departments, Locations. Получить город в котором сотрудники в сумме зарабатывают меньше всех.

69.sql

SELECT t1.first_name
FROM (
  learnsql_employees t1
  LEFT JOIN learnsql_employees t2 ON t1.manager_id=t2.id
)
where t2.salary>3000
;

-- авторское решение. Насколько затратна операция джоиН?
SELECT *
  FROM employees
 WHERE manager_id IN (SELECT employee_id
                        FROM employees
                       WHERE salary > 15000)

70.sql

-- а чего "WHERE b" не работает?

SELECT *
FROM learnsql_departments t1
WHERE t1.id NOT IN (SELECT t2.department_id b
                    FROM learnsql_employees t2
                    WHERE t2.department_id IS NOT NULL)
;

71.sql

SELECT *
FROM learnsql_employees t1
WHERE t1.id NOT IN (SELECT t2.manager_id b
                    FROM learnsql_employees t2
                    WHERE t2.manager_id IS NOT NULL)
;

72.sql

-- взял авторское решение. Как этот запрос выполняется? Для каждой записи из таблици т1 делаем селект?
SELECT *
FROM learnsql_employees t1
WHERE (SELECT COUNT(*)
                    FROM learnsql_employees t2
                    WHERE t2.manager_id=t1.id)>0
;

73.sql

SELECT *
FROM learnsql_employees t1
WHERE t1.department_id=(SELECT id
                        FROM learnsql_departments t2
                        WHERE t2.name='departmentA')
;

74.sql

SELECT t1.first_name, 
  (SELECT t2.title
   FROM learnsql_jobs t2
   WHERE t2.id=t1.job_id),
  (SELECT t3.name
   FROM learnsql_departments t3
   WHERE t3.id=t1.department_id)
FROM learnsql_employees t1
;

75.sql

SELECT t1.*
FROM learnsql_employees t1
WHERE t1.manager_id IN (SELECT t2.id
                        FROM learnsql_employees t2
                        WHERE TO_CHAR(t2.hire_date, 'YYYY')='2020')
     AND TO_CHAR(t1.hire_date, 'YYYY')<'2020'
;

76.sql

SELECT t1.*
FROM learnsql_employees t1
WHERE t1.manager_id IN (SELECT t2.id
                        FROM learnsql_employees t2
                        WHERE TO_CHAR(t2.hire_date, 'MM')='09')
     AND (SELECT LENGTH(t3.title)
          FROM learnsql_jobs t3
          WHERE t3.id=t1.job_id)>1
;

create_table.sql

;