{"id":36671,"date":"2019-10-31T22:13:01","date_gmt":"2019-10-31T19:13:01","guid":{"rendered":"https:\/\/prohoster.info\/blog\/sql-zanimatelnye-zadachki\/"},"modified":"2019-10-31T22:13:01","modified_gmt":"2019-10-31T19:13:01","slug":"sql-zanimatelnye-zadachki","status":"publish","type":"post","link":"https:\/\/prohoster.info\/en\/blog\/news\/sql-zanimatelnye-zadachki","title":{"rendered":"SQL. Engaging tasks","gt_translate_keys":[{"key":"rendered","format":"text"}]},"content":{"rendered":"

Hello, Habr!<\/p>\n

For more than 3 years, I have been teaching SQL in various training centers, and one of my observations is that students grasp and understand SQL better when presented with tasks rather than simply explaining the capabilities and theoretical foundations.<\/p>\n

In this article, I will share my list of tasks that I assign to students as homework and on which we conduct various brainstorming sessions, leading to a deep and clear understanding of SQL.<\/p>\n

\"SQL.
\n
\nSQL (\u02c8\u025bs\u02c8kju\u02c8\u025bl; English: structured query language) is a declarative programming language used for creating, modifying, and managing data in a relational database managed by the corresponding database management system. Learn more\u2026 <\/a><\/noindex><\/p>\n

You can read about SQL from various sources<\/a><\/noindex>.
\nThis article does not aim to teach you SQL from scratch.
\n<\/a><\/noindex>
\nSo, let's get started.<\/p>\n

We will use the well-known HR schema<\/a><\/noindex> in Oracle with its tables (Learn more<\/a><\/noindex>):<\/p>\n

\"SQL.
\nNote that we will only be looking at tasks related to SELECT. There are no tasks for DML and DDL here.<\/p>\n

Since I have already learned to \"somewhat\" port QEMU to JavaScript, this time it was decided to do it wisely and not repeat past mistakes.<\/h2>\n

\nRestricting and Sorting Data<\/b><\/p>\n

Table Employees. Get a list with information about all employees
\nSolution<\/b><\/p>\n

SELECT * FROM employees\n<\/code><\/pre>\n
Table Employees. Get a list of all employees with the name 'David'
\nSolution<\/b><\/p>\n
SELECT *\n  FROM employees\n WHERE first_name = 'David';\n<\/code><\/pre>\n
Table Employees. Get a list of all employees with job_id equal to 'IT_PROG'
\nSolution<\/b><\/p>\n
SELECT *\n  FROM employees\n WHERE job_id = 'IT_PROG'\n<\/code><\/pre>\n
Table Employees. Get a list of all employees from department 50 (department_id) with a salary greater than 4000
\nSolution<\/b><\/p>\n
SELECT *\n  FROM employees\n WHERE department_id = 50 AND salary > 4000;\n<\/code><\/pre>\n
Table Employees. Get a list of all employees from department 20 and department 30 (department_id)
\nSolution<\/b><\/p>\n
SELECT *\n  FROM employees\n WHERE department_id = 20 OR department_id = 30;\n<\/code><\/pre>\n
Table Employees. Get a list of all employees whose name ends with the letter 'a'
\nSolution<\/b><\/p>\n
SELECT *\n  FROM employees\n WHERE first_name LIKE '%a';\n<\/code><\/pre>\n
Table Employees. Get a list of all employees from department 50 and department 80 (department_id) who have a bonus (value in the commission_pct column is not empty)
\nSolution<\/b><\/p>\n
SELECT *\n  FROM employees\n WHERE     (department_id = 50 OR department_id = 80)\n       AND commission_pct IS NOT NULL;\n<\/code><\/pre>\n
Table Employees. Get a list of all employees whose name contains at least 2 letters 'n'
\nSolution<\/b><\/p>\n
SELECT *\n  FROM employees\n WHERE first_name LIKE '%n%n%';\n<\/code><\/pre>\n
Table Employees. Get a list of all employees whose name length is greater than 4 letters
\nSolution<\/b><\/p>\n
SELECT *\n  FROM employees\n WHERE first_name LIKE '%_____%';\n<\/code><\/pre>\n
Table Employees. Get a list of all employees whose salary is between 8000 and 9000 (inclusive)
\nSolution<\/b><\/p>\n
SELECT *\n  FROM employees\n WHERE salary BETWEEN 8000 AND 9000;\n<\/code><\/pre>\n
Table Employees. Get a list of all employees whose name contains the character '%'
\nSolution<\/b><\/p>\n
SELECT *\n  FROM employees\n WHERE first_name LIKE '%%%' ESCAPE '';\n<\/code><\/pre>\n
Employees Table. Retrieve a list of all manager IDs
\nSolution<\/b><\/p>\n
SELECT DISTINCT manager_id\n  FROM employees\n WHERE manager_id IS NOT NULL;\n<\/code><\/pre>\n
Employees Table. Retrieve a list of employees with their positions in the format: Donald(sh_clerk)
\nSolution<\/b><\/p>\n
SELECT first_name || '(' || LOWER (job_id) || ')' employee FROM employees;\n<\/code><\/pre>\n
\nUsing Single-Row Functions to Customize Output<\/b><\/p>\n
Employees Table. Retrieve a list of all employees whose name length is greater than 10 characters
\nSolution<\/b><\/p>\n
SELECT *\n  FROM employees\n WHERE LENGTH (first_name) > 10;\n<\/code><\/pre>\n
Table Employees. Get a list of all employees whose name contains the letter 'b' (case insensitive)
\nSolution<\/b><\/p>\n
SELECT *\n  FROM employees\n WHERE INSTR (LOWER (first_name), 'b') > 0;\n<\/code><\/pre>\n
Table Employees. Get a list of all employees whose name contains at least 2 letters 'a'
\nSolution<\/b><\/p>\n
SELECT *\n  FROM employees\n WHERE INSTR (LOWER (first_name),'a',1,2) > 0;\n<\/code><\/pre>\n
Employees Table. Retrieve a list of all employees whose salary is a multiple of 1000
\nSolution<\/b><\/p>\n
SELECT *\n  FROM employees\n WHERE MOD (salary, 1000) = 0;\n<\/code><\/pre>\n
Employees Table. Retrieve the first 3-digit number of the employee's phone number if their number is in the format XXX.XXX.XXXX
\nSolution<\/b><\/p>\n
SELECT phone_number, SUBSTR (phone_number, 1, 3) new_phone_number\n  FROM employees\n WHERE phone_number LIKE '___.___.____';\n<\/code><\/pre>\n
Departments Table. Retrieve the first word from the department name for those with more than one word in the title
\nSolution<\/b><\/p>\n
SELECT department_name,\n       SUBSTR (department_name, 1, INSTR (department_name, ' ')-1)\n           first_word\n  FROM departments\n WHERE INSTR (department_name, ' ') > 0;\n<\/code><\/pre>\n
Employees Table. Retrieve employee names without the first and last letter of the name
\nSolution<\/b><\/p>\n
SELECT first_name, SUBSTR (first_name, 2, LENGTH (first_name) - 2) new_name\n  FROM employees;\n<\/code><\/pre>\n
Table Employees. Get a list of all employees whose name ends with the letter 'm' and has a length greater than 5
\nSolution<\/b><\/p>\n
SELECT *\n  FROM employees\n WHERE SUBSTR (first_name, -1) = 'm' AND LENGTH(first_name) > 5;\n<\/code><\/pre>\n
Dual Table. Retrieve the date of the next Friday
\nSolution<\/b><\/p>\n
SELECT NEXT_DAY (SYSDATE, 'FRIDAY') next_friday FROM DUAL;\n<\/code><\/pre>\n
Employees Table. Retrieve a list of all employees who have worked in the company for more than 17 years
\nSolution<\/b><\/p>\n
SELECT *\n  FROM employees\n WHERE MONTHS_BETWEEN (SYSDATE, hire_date) \/ 12 > 17;\n<\/code><\/pre>\n
Employees Table. Retrieve a list of all employees whose last digit of the phone number is odd and consists of 3 digits separated by a dot
\nSolution<\/b><\/p>\n
SELECT *\n  FROM employees\n WHERE MOD (SUBSTR (phone_number, -1), 2) != 0\n       AND INSTR (phone_number,'.',1,3) = 0;\n<\/code><\/pre>\n
Table Employees. Get a list of all employees whose job_id value has at least 3 characters after the '_' but this value after '_' is not equal to 'CLERK'
\nSolution<\/b><\/p>\n
SELECT *\n  FROM employees\n WHERE     LENGTH (SUBSTR (job_id, INSTR (job_id, '_') + 1)) > 3\n       AND SUBSTR (job_id, INSTR (job_id, '_') + 1) != 'CLERK';\n<\/code><\/pre>\n
Table Employees. Get a list of all employees replacing all '.' in PHONE_NUMBER with '-'
\nSolution<\/b><\/p>\n
SELECT phone_number, REPLACE (phone_number, '.', '-') new_phone_number\n  FROM employees;\n<\/code><\/pre>\n
\nUsing Conversion Functions and Conditional Expressions<\/b><\/p>\n
Table Employees. Retrieve a list of all employees who started on the first day of any month
\nSolution<\/b><\/p>\n
SELECT *\n  FROM employees\n WHERE TO_CHAR (hire_date, 'DD') = '01';\n<\/code><\/pre>\n
Table Employees. Retrieve a list of all employees who started working in 2008
\nSolution<\/b><\/p>\n
SELECT *\n  FROM employees\n WHERE TO_CHAR (hire_date, 'YYYY') = '2008';\n<\/code><\/pre>\n
Table DUAL. Show tomorrow's date in the format: Tomorrow is Second day of January
\nSolution<\/b><\/p>\n
SELECT TO_CHAR (SYSDATE, 'fm\"\"Tomorrow is \"\"Ddspth \"\"day of\"\" Month') info\n  FROM DUAL;\n<\/code><\/pre>\n
Table Employees. Retrieve a list of all employees and their hire dates in the format: 21st of June, 2007
\nSolution<\/b><\/p>\n
SELECT first_name, TO_CHAR (hire_date, 'fmddth \"\"of\"\" Month, YYYY') hire_date\n  FROM employees;\n<\/code><\/pre>\n
Table Employees. Retrieve a list of employees with salary increases of 20%. Show salary with a dollar sign
\nSolution<\/b><\/p>\n
SELECT first_name, TO_CHAR (salary + salary * 0.20, 'fm$999,999.00') new_salary\n  FROM employees;\n<\/code><\/pre>\n
Table Employees. Retrieve a list of all employees who started in February 2007.
\nSolution<\/b><\/p>\n
SELECT *\n  FROM employees\n WHERE hire_date BETWEEN TO_DATE ('01.02.2007', 'DD.MM.YYYY')\n                     AND LAST_DAY (TO_DATE ('01.02.2007', 'DD.MM.YYYY'));\n\nSELECT *\n  FROM employees\n WHERE to_char(hire_date,'MM.YYYY') = '02.2007'; \n<\/code><\/pre>\n
Table DUAL. Output the current date, + second, + minute, + hour, + day, + month, + year
\nSolution<\/b><\/p>\n
SELECT SYSDATE now,\n       SYSDATE + 1 \/ (24 * 60 * 60) plus_second,\n       SYSDATE + 1 \/ (24 * 60) plus_minute,\n       SYSDATE + 1 \/ 24 plus_hour,\n       SYSDATE + 1 plus_day,\n       ADD_MONTHS (SYSDATE, 1) plus_month,\n       ADD_MONTHS (SYSDATE, 12) plus_year\n  FROM DUAL;\n<\/code><\/pre>\n
Table Employees. Retrieve a list of all employees with total salaries (salary + commission_pct(%)) in the format: $24,000.00
\nSolution<\/b><\/p>\n
SELECT first_name, salary, TO_CHAR (salary + salary * NVL (commission_pct, 0), 'fm$99,999.00') full_salary\n  FROM employees;\n<\/code><\/pre>\n
Table Employees. Retrieve a list of all employees and information on the existence of salary bonuses (Yes\/No)
\nSolution<\/b><\/p>\n
SELECT first_name, commission_pct, NVL2 (commission_pct, 'Yes', 'No') has_bonus\n  FROM employees;\n<\/code><\/pre>\n
Table Employees. Retrieve the salary level of each employee: Less than 5000 is considered Low level, 5000 or more and less than 10000 is considered Normal level, 10000 or more is considered High level
\nSolution<\/b><\/p>\n
SELECT first_name,\n       salary,\n       CASE\n           WHEN salary = 5000 AND salary < 10000 THEN 'Normal'\n           ELSE 'High'\n       END salary_level\n  FROM employees;\n<\/code><\/pre>\n
Table Countries. For each country, show the region it belongs to: 1-Europe, 2-America, 3-Asia, 4-Africa (without Join)
\nSolution<\/b><\/p>\n
SELECT country_name country,\n       DECODE (region_id,\n               1, 'Europe',\n               2, 'America',\n               3, 'Asia',\n               4, 'Africa',\n               'Unknown')\n           region\n  FROM countries;\n\nSELECT country_name\n           country,\n       CASE region_id\n           WHEN 1 THEN 'Europe'\n           WHEN 2 THEN 'America'\n           WHEN 3 THEN 'Asia'\n           WHEN 4 THEN 'Africa'\n           ELSE 'Unknown'\n       END\n           region\n  FROM countries;\n<\/code><\/pre>\n
\nReporting Aggregated Data Using the Group Functions<\/b><\/p>\n
Table Employees. Get a report by department_id with the minimum and maximum salary, earliest and latest hire dates, and the number of employees. Sort by the number of employees (in descending order)
\nSolution<\/b><\/p>\n
  SELECT department_id,\n         MIN (salary) min_salary,\n         MAX (salary) max_salary,\n         MIN (hire_date) min_hire_date,\n         MAX (hire_date) max_hire_Date,\n         COUNT (*) count\n    FROM employees\nGROUP BY department_id\norder by count(*) desc;\n<\/code><\/pre>\n
Table Employees. How many employees have names starting with the same letter? Sort by count. Show only those where the count is greater than 1
\nSolution<\/b><\/p>\n
SELECT SUBSTR (first_name, 1, 1) first_char, COUNT (*)\n    FROM employees\nGROUP BY SUBSTR (first_name, 1, 1)\n  HAVING COUNT (*) > 1\nORDER BY 2 DESC;\n<\/code><\/pre>\n
Table Employees. How many employees work in the same department and earn the same salary?
\nSolution<\/b><\/p>\n
SELECT department_id, salary, COUNT (*)\n    FROM employees\nGROUP BY department_id, salary\n  HAVING COUNT (*) > 1;\n<\/code><\/pre>\n
Table Employees. Get a report on how many employees were hired each day of the week. Sort by count
\nSolution<\/b><\/p>\n
SELECT TO_CHAR (hire_Date, 'Day') day, COUNT (*)\n    FROM employees\nGROUP BY TO_CHAR (hire_Date, 'Day')\nORDER BY 2 DESC;\n<\/code><\/pre>\n
Table Employees. Get a report on how many employees were hired each year. Sort by count
\nSolution<\/b><\/p>\n
SELECT TO_CHAR (hire_date, 'YYYY') year, COUNT (*)\n    FROM employees\nGROUP BY TO_CHAR (hire_date, 'YYYY');\n<\/code><\/pre>\n
Table Employees. Get the number of departments that have employees
\nSolution<\/b><\/p>\n
SELECT COUNT (COUNT (*)) department_count\n    FROM employees\n   WHERE department_id IS NOT NULL\nGROUP BY department_id;\n<\/code><\/pre>\n
Table Employees. Get a list of department_id where more than 30 employees work
\nSolution<\/b><\/p>\n
  SELECT department_id\n    FROM employees\nGROUP BY department_id\n  HAVING COUNT (*) > 30;\n<\/code><\/pre>\n
Table Employees. Get a list of department_id and the rounded average salary of employees in each department. 
\nSolution<\/b><\/p>\n
  SELECT department_id, ROUND (AVG (salary)) avg_salary\n    FROM employees\nGROUP BY department_id;\n<\/code><\/pre>\n
Table Countries. Get a list of region_id where the sum of all characters in country_name is greater than 60
\nSolution<\/b><\/p>\n
  SELECT region_id\n    FROM countries\nGROUP BY region_id\n  HAVING SUM (LENGTH (country_name)) > 60;\n<\/code><\/pre>\n
Table Employees. Get a list of department_id where employees have multiple (>1) job_id
\nSolution<\/b><\/p>\n
  SELECT department_id\n    FROM employees\nGROUP BY department_id\n  HAVING COUNT (DISTINCT job_id) > 1;\n<\/code><\/pre>\n
Employees Table. Retrieve the list of manager_ids who have more than 5 subordinates and the total salary of their subordinates exceeds 50000
\nSolution<\/b><\/p>\n
  SELECT manager_id\n    FROM employees\nGROUP BY manager_id\n  HAVING COUNT (*) > 5 AND SUM (salary) > 50000;\n<\/code><\/pre>\n
Employees Table. Retrieve the list of manager_ids whose average salary of all their subordinates is between 6000 and 9000 and who do not receive bonuses (commission_pct is empty)
\nSolution<\/b><\/p>\n
  SELECT manager_id, AVG (salary) avg_salary\n    FROM employees\n   WHERE commission_pct IS NULL\nGROUP BY manager_id\n  HAVING AVG (salary) BETWEEN 6000 AND 9000;\n<\/code><\/pre>\n
Table Employees. Get the maximum salary from all employees whose job_id ends with the word 'CLERK'
\nSolution<\/b><\/p>\n
SELECT MAX (salary) max_salary\n  FROM employees\n WHERE job_id LIKE '%CLERK';\n\nSELECT MAX (salary) max_salary\n  FROM employees\n WHERE SUBSTR (job_id, -5) = 'CLERK';\n<\/code><\/pre>\n
Employees Table. Retrieve the maximum salary among all average salaries by department
\nSolution<\/b><\/p>\n
  SELECT MAX (AVG (salary))\n    FROM employees\nGROUP BY department_id;\n<\/code><\/pre>\n
Employees Table. Get the number of employees with the same number of letters in their names. Additionally, show only those with names longer than 5 and the number of employees with that name exceeding 20. Sort by name length
\nSolution<\/b><\/p>\n
  SELECT LENGTH (first_name), COUNT (*)\n    FROM employees\nGROUP BY LENGTH (first_name)\n  HAVING LENGTH (first_name) > 5 AND COUNT (*) > 20\nORDER BY LENGTH (first_name);\n\n  SELECT LENGTH (first_name), COUNT (*)\n    FROM employees\n   WHERE LENGTH (first_name) > 5\nGROUP BY LENGTH (first_name)\n  HAVING COUNT (*) > 20\nORDER BY LENGTH (first_name);\n<\/code><\/pre>\n
\nDisplaying Data from Multiple Tables Using Joins<\/b><\/p>\n
Employees, Departments, Locations, Countries, Regions Table. Retrieve the list of regions and the number of employees in each region
\nSolution<\/b><\/p>\n
  SELECT region_name, COUNT (*)\n    FROM employees e\n         JOIN departments d ON (e.department_id = d.department_id)\n         JOIN locations l ON (d.location_id = l.location_id)\n         JOIN countries c ON (l.country_id = c.country_id)\n         JOIN regions r ON (c.region_id = r.region_id)\nGROUP BY region_name;\n<\/code><\/pre>\n
Employees, Departments, Locations, Countries, Regions Table. Retrieve detailed information about each employee:
\nFirst_name, Last_name, Department, Job, Street, Country, Region
\nSolution<\/b><\/p>\n
SELECT First_name,\n       Last_name,\n       Department_name,\n       Job_id,\n       street_address,\n       Country_name,\n       Region_name\n  FROM employees  e\n       JOIN departments d ON (e.department_id = d.department_id)\n       JOIN locations l ON (d.location_id = l.location_id)\n       JOIN countries c ON (l.country_id = c.country_id)\n       JOIN regions r ON (c.region_id = r.region_id);\n<\/code><\/pre>\n
Employees Table. Show all managers who have more than 6 employees under their supervision
\nSolution<\/b><\/p>\n
  SELECT man.first_name, COUNT (*)\n    FROM employees emp JOIN employees man ON (emp.manager_id = man.employee_id)\nGROUP BY man.first_name\n  HAVING COUNT (*) > 6;\n<\/code><\/pre>\n
Employees Table. Show all employees who do not report to anyone
\nSolution<\/b><\/p>\n
SELECT emp.first_name\n  FROM employees emp\n       LEFT JOIN employees man ON (emp.manager_id = man.employee_id)\n WHERE man.FIRST_NAME IS NULL;\n\nSELECT first_name\n  FROM employees\n WHERE manager_id IS NULL;\n<\/code><\/pre>\n
Tables Employees, Job_history. The Employee table stores all employees. The Job_history table stores employees who have left the company. Generate a report of all employees and their status in the company (Currently Employed or Left the company with the exit date)
\nExample:
\nfirst_name | status
\nJennifer | Left the company at 31 of December, 2006
\nClara | Currently Working
\nSolution<\/b><\/p>\n
SELECT first_name,\n       NVL2 (\n           end_date,\n           TO_CHAR (end_date, 'fm\"\"Left the company at\"\" DD \"\"of\"\" Month, YYYY'),\n           'Currently Working')\n           status\n  FROM employees e LEFT JOIN job_history j ON (e.employee_id = j.employee_id);\n<\/code><\/pre>\n
Tables Employees, Departments, Locations, Countries, Regions. Retrieve a list of employees who live in Europe (region_name)
\nSolution<\/b><\/p>\n
 SELECT first_name\n  FROM employees\n       JOIN departments USING (department_id)\n       JOIN locations USING (location_id)\n       JOIN countries USING (country_id)\n       JOIN regions USING (region_id)\n WHERE region_name = 'Europe';\n \n SELECT first_name\n  FROM employees e\n       JOIN departments d ON (e.department_id = d.department_id)\n       JOIN locations l ON (d.location_id = l.location_id)\n       JOIN countries c ON (l.country_id = c.country_id)\n       JOIN regions r ON (c.region_id = r.region_id)\n WHERE region_name = 'Europe';\n<\/code><\/pre>\n
Tables Employees, Departments. Show all departments that have more than 30 employees
\nSolution<\/b><\/p>\n
SELECT department_name, COUNT (*)\n    FROM employees e JOIN departments d ON (e.department_id = d.department_id)\nGROUP BY department_name\n  HAVING COUNT (*) > 30;\n<\/code><\/pre>\n
Tables Employees, Departments. Show all employees who are not part of any department
\nSolution<\/b><\/p>\n
SELECT first_name\n  FROM employees e\n       LEFT JOIN departments d ON (e.department_id = d.department_id)\n WHERE d.department_name IS NULL;\n\nSELECT first_name\n  FROM employees\n WHERE department_id IS NULL;\n<\/code><\/pre>\n
Tables Employees, Departments. Show all departments with no employees
\nSolution<\/b><\/p>\n
SELECT department_name\n  FROM employees e\n       RIGHT JOIN departments d ON (e.department_id = d.department_id)\n WHERE first_name IS NULL;\n<\/code><\/pre>\n
Tables Employees. Show all employees who have no subordinates
\nSolution<\/b><\/p>\n
SELECT man.first_name\n  FROM employees emp\n       RIGHT JOIN employees man ON (emp.manager_id = man.employee_id)\n WHERE emp.FIRST_NAME IS NULL;\n<\/code><\/pre>\n
Tables Employees, Jobs, Departments. Show employees in the format: First_name, Job_title, Department_name.
\nExample:
\nFirst_name | Job_title | Department_name
\nDonald | Shipping | Clerk Shipping
\nSolution<\/b><\/p>\n
SELECT first_name, job_title, department_name\n  FROM employees e\n       JOIN jobs j ON (e.job_id = j.job_id)\n       JOIN departments d ON (d.department_id = e.department_id);\n<\/code><\/pre>\n
Tables Employees. Get a list of employees whose managers were hired in 2005, but these employees themselves were hired before 2005
\nSolution<\/b><\/p>\n
SELECT emp.*\n  FROM employees emp JOIN employees man ON (emp.manager_id = man.employee_id)\n WHERE     TO_CHAR (man.hire_date, 'YYYY') = '2005'\n       AND emp.hire_date < TO_DATE ('01012005', 'DDMMYYYY');\n<\/code><\/pre>\n
Employees Table. Retrieve a list of employees whose managers were hired in January of any year and whose job_title length exceeds 15 characters.
\nSolution<\/b><\/p>\n
SELECT emp.*\n  FROM employees emp\n       JOIN employees man ON (emp.manager_id = man.employee_id)\n       JOIN jobs j ON (emp.job_id = j.job_id)\n WHERE TO_CHAR(man.hire_date, 'MM') = '01' AND LENGTH(j.job_title) > 15;\n<\/code><\/pre>\n
\nUsing Subqueries to Solve Queries<\/b><\/p>\n
Employees Table. Retrieve a list of employees with the longest first names.
\nSolution<\/b><\/p>\n
SELECT *\n  FROM employees\n WHERE LENGTH(first_name) =\n       (SELECT MAX(LENGTH(first_name)) FROM employees);\n<\/code><\/pre>\n
Employees Table. Retrieve a list of employees with a salary greater than the average salary of all employees.
\nSolution<\/b><\/p>\n
SELECT *\n  FROM employees\n WHERE salary > (SELECT AVG(salary) FROM employees);\n<\/code><\/pre>\n
Employees, Departments, Locations Table. Retrieve the city where employees earn the least in total.
\nSolution<\/b><\/p>\n
SELECT city\n    FROM employees e\n         JOIN departments d ON (e.department_id = d.department_id)\n         JOIN locations l ON (d.location_id = l.location_id)\nGROUP BY city\n  HAVING SUM(salary) =\n         (SELECT MIN(SUM(salary))\n              FROM employees e\n                   JOIN departments d ON (e.department_id = d.department_id)\n                   JOIN locations l ON (d.location_id = l.location_id)\n          GROUP BY city);\n<\/code><\/pre>\n
Employees Table. Retrieve a list of employees whose manager earns more than 15000.
\nSolution<\/b><\/p>\n
SELECT *\n  FROM employees\n WHERE manager_id IN (SELECT employee_id\n                        FROM employees\n                       WHERE salary > 15000)\n<\/code><\/pre>\n
Tables Employees, Departments. Show all departments with no employees
\nSolution<\/b><\/p>\n
SELECT *\n  FROM departments\n WHERE department_id NOT IN (SELECT department_id\n                               FROM employees\n                              WHERE department_id IS NOT NULL);\n<\/code><\/pre>\n
Employees Table. Show all employees who are not managers
\nSolution<\/b><\/p>\n
SELECT *\n  FROM employees\n WHERE employee_id NOT IN (SELECT manager_id\n                             FROM employees\n                            WHERE manager_id IS NOT NULL)\n<\/code><\/pre>\n
Employees Table. Show all managers who have more than 6 employees under their supervision
\nSolution<\/b><\/p>\n
SELECT *\n  FROM employees e\n WHERE (SELECT COUNT(*)\n          FROM employees\n         WHERE manager_id = e.employee_id) > 6;\n<\/code><\/pre>\n
Employees, Departments Table. Show employees who work in the IT department
\nSolution<\/b><\/p>\n
SELECT *\n  FROM employees\n WHERE department_id = (SELECT department_id\n                          FROM departments\n                         WHERE department_name = 'IT');\n<\/code><\/pre>\n
Tables Employees, Jobs, Departments. Show employees in the format: First_name, Job_title, Department_name.
\nExample:
\nFirst_name | Job_title | Department_name
\nDonald | Shipping | Clerk Shipping
\nSolution<\/b><\/p>\n
SELECT first_name,\n       (SELECT job_title\n          FROM jobs\n         WHERE job_id = e.job_id)\n           job_title,\n       (SELECT department_name\n          FROM departments\n         WHERE department_id = e.department_id)\n           department_name\n  FROM employees e;\n<\/code><\/pre>\n
Tables Employees. Get a list of employees whose managers were hired in 2005, but these employees themselves were hired before 2005
\nSolution<\/b><\/p>\n
SELECT *\n  FROM employees\n WHERE     manager_id IN (SELECT employee_id\n                            FROM employees\n                           WHERE TO_CHAR(hire_date, 'YYYY') = '2005')\n       AND hire_date < TO_DATE('01012005', 'DDMMYYYY');\n<\/code><\/pre>\n
Employees Table. Retrieve a list of employees whose managers were hired in January of any year and whose job_title length exceeds 15 characters.
\nSolution<\/b><\/p>\n
SELECT *\n  FROM employees e\n WHERE     manager_id IN (SELECT employee_id\n                            FROM employees\n                           WHERE TO_CHAR(hire_date, 'MM') = '01')\n       AND (SELECT LENGTH(job_title)\n              FROM jobs\n             WHERE job_id = e.job_id) > 15;\n<\/code><\/pre>\n
\nThat's all for now.<\/b><\/p>\n
I hope the tasks were interesting and engaging. 
\nI will add to this list of tasks as much as possible.
\nI would also appreciate any comments and suggestions.<\/p>\n
P.S.: If anyone thinks of an interesting task involving SELECT, please write in the comments, and I\u2019ll add it to the list.<\/p>\n
Thank you.
\n
Source: habr.com<\/a><\/p>","protected":false,"gt_translate_keys":[{"key":"rendered","format":"html"}]},"excerpt":{"rendered":"
\u0417\u0434\u0440\u0430\u0432\u0441\u0442\u0432\u0443\u0439, \u0425\u0430\u0431\u0440! \u0412\u043e\u0442 \u0443\u0436\u0435 \u0431\u043e\u043b\u0435\u0435 3-\u0445 \u043b\u0435\u0442 \u044f \u043f\u0440\u0435\u043f\u043e\u0434\u0430\u044e SQL \u0432 \u0440\u0430\u0437\u043d\u044b\u0445 \u0442\u0440\u0435\u043d\u0438\u043d\u0433 \u0446\u0435\u043d\u0442\u0440\u0430\u0445, \u0438 \u043e\u0434\u043d\u0438\u043c \u0438\u0437 \u043c\u043e\u0438\u0445 \u043d\u0430\u0431\u043b\u044e\u0434\u0435\u043d\u0438\u0439 \u044f\u0432\u043b\u044f\u0435\u0442\u0441\u044f \u0442\u043e, \u0447\u0442\u043e \u0441\u0442\u0443\u0434\u0435\u043d\u0442\u044b \u043e\u0441\u0432\u0430\u0438\u0432\u0430\u044e\u0442 \u0438 \u043f\u043e\u043d\u0438\u043c\u0430\u044e\u0442 SQL \u043b\u0443\u0447\u0448\u0435, \u0435\u0441\u043b\u0438 \u0441\u0442\u0430\u0432\u0438\u0442\u044c \u043f\u0435\u0440\u0435\u0434 \u043d\u0438\u043c\u0438 \u0437\u0430\u0434\u0430\u0447\u0443, \u0430 \u043d\u0435 \u043f\u0440\u043e\u0441\u0442\u043e \u0440\u0430\u0441\u0441\u043a\u0430\u0437\u044b\u0432\u0430\u0442\u044c \u043e \u0432\u043e\u0437\u043c\u043e\u0436\u043d\u043e\u0441\u0442\u044f\u0445 \u0438 \u0442\u0435\u043e\u0440\u0435\u0442\u0438\u0447\u0435\u0441\u043a\u0438\u0445 \u043e\u0441\u043d\u043e\u0432\u0430\u0445. \u0412 \u044d\u0442\u043e\u0439 \u0441\u0442\u0430\u0442\u044c\u0435 \u044f \u043f\u043e\u0434\u0435\u043b\u044e\u0441\u044c \u0441 \u0432\u0430\u043c\u0438 \u0441\u0432\u043e\u0438\u043c \u0441\u043f\u0438\u0441\u043a\u043e\u043c \u0437\u0430\u0434\u0430\u0447, \u043a\u043e\u0442\u043e\u0440\u044b\u0435 \u044f \u0434\u0430\u044e […]<\/p>\n","protected":false,"gt_translate_keys":[{"key":"rendered","format":"html"}]},"author":1,"featured_media":27465,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[702],"tags":[],"class_list":["post-36671","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-news"],"aioseo_notices":[],"aioseo_head":"\n\t\t\n\t\n\t\n\t\n\t\n\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\n","aioseo_head_json":{"title":"\ud83e\udd47SQL. Engaging Tasks | ProHoster","description":"Hello, Habr! For over 3 years now, I have been teaching SQL in various training centers, and one of my observations is that students grasp and understand SQL better when presented with a task, rather than just listening.","canonical_url":"https:\/\/prohoster.info\/en\/blog\/news\/sql-zanimatelnye-zadachki","robots":"max-image-preview:large","keywords":"","webmasterTools":{"miscellaneous":""},"schema":null,"og:locale":"en_US","og:site_name":"ProHoster | \u041a\u0443\u043f\u0438\u0442\u044c \u043d\u0430\u0434\u0435\u0436\u043d\u044b\u0439 \u0445\u043e\u0441\u0442\u0438\u043d\u0433 \u0434\u043b\u044f \u0441\u0430\u0439\u0442\u043e\u0432 \u0441 \u0437\u0430\u0449\u0438\u0442\u043e\u0439 \u043e\u0442 DDoS, VPS VDS \u0441\u0435\u0440\u0432\u0435\u0440\u044b","og:type":"article","og:title":"\ud83e\udd47SQL. \u0417\u0430\u043d\u0438\u043c\u0430\u0442\u0435\u043b\u044c\u043d\u044b\u0435 \u0437\u0430\u0434\u0430\u0447\u043a\u0438 | ProHoster","og:description":"\u0417\u0434\u0440\u0430\u0432\u0441\u0442\u0432\u0443\u0439, \u0425\u0430\u0431\u0440! \u0412\u043e\u0442 \u0443\u0436\u0435 \u0431\u043e\u043b\u0435\u0435 3-\u0445 \u043b\u0435\u0442 \u044f \u043f\u0440\u0435\u043f\u043e\u0434\u0430\u044e SQL \u0432 \u0440\u0430\u0437\u043d\u044b\u0445 \u0442\u0440\u0435\u043d\u0438\u043d\u0433 \u0446\u0435\u043d\u0442\u0440\u0430\u0445, \u0438 \u043e\u0434\u043d\u0438\u043c \u0438\u0437 \u043c\u043e\u0438\u0445 \u043d\u0430\u0431\u043b\u044e\u0434\u0435\u043d\u0438\u0439 \u044f\u0432\u043b\u044f\u0435\u0442\u0441\u044f \u0442\u043e, \u0447\u0442\u043e \u0441\u0442\u0443\u0434\u0435\u043d\u0442\u044b \u043e\u0441\u0432\u0430\u0438\u0432\u0430\u044e\u0442 \u0438 \u043f\u043e\u043d\u0438\u043c\u0430\u044e\u0442 SQL \u043b\u0443\u0447\u0448\u0435, \u0435\u0441\u043b\u0438 \u0441\u0442\u0430\u0432\u0438\u0442\u044c \u043f\u0435\u0440\u0435\u0434 \u043d\u0438\u043c\u0438 \u0437\u0430\u0434\u0430\u0447\u0443, \u0430 \u043d\u0435 \u043f\u0440\u043e\u0441\u0442\u043e.","og:url":"https:\/\/prohoster.info\/en\/blog\/news\/sql-zanimatelnye-zadachki","og:image":"https:\/\/prohoster.info\/wp-content\/uploads\/2021\/11\/logo-350.jpg","og:image:secure_url":"https:\/\/prohoster.info\/wp-content\/uploads\/2021\/11\/logo-350.jpg","og:image:width":350,"og:image:height":350,"article:published_time":"2019-10-31T19:13:01+00:00","article:modified_time":"2019-10-31T19:13:01+00:00","article:publisher":"https:\/\/www.facebook.com\/prohoster","article:author":"https:\/\/www.facebook.com\/prohoster"},"aioseo_meta_data":{"post_id":"36671","title":null,"description":null,"keywords":null,"keyphrases":null,"primary_term":null,"canonical_url":null,"og_title":null,"og_description":null,"og_object_type":"default","og_image_type":"default","og_image_url":null,"og_image_width":null,"og_image_height":null,"og_image_custom_url":null,"og_image_custom_fields":null,"og_video":null,"og_custom_url":null,"og_article_section":null,"og_article_tags":null,"twitter_use_og":false,"twitter_card":"default","twitter_image_type":"default","twitter_image_url":null,"twitter_image_custom_url":null,"twitter_image_custom_fields":null,"twitter_title":null,"twitter_description":null,"schema":{"blockGraphs":[],"customGraphs":[],"default":{"data":{"Article":[],"Course":[],"Dataset":[],"FAQPage":[],"Movie":[],"Person":[],"Product":[],"ProductReview":[],"Car":[],"Recipe":[],"Service":[],"SoftwareApplication":[],"WebPage":[]},"graphName":"","isEnabled":true},"graphs":[]},"schema_type":null,"schema_type_options":null,"pillar_content":false,"robots_default":true,"robots_noindex":false,"robots_noarchive":false,"robots_nosnippet":false,"robots_nofollow":false,"robots_noimageindex":false,"robots_noodp":false,"robots_notranslate":false,"robots_max_snippet":null,"robots_max_videopreview":null,"robots_max_imagepreview":"large","priority":null,"frequency":null,"local_seo":null,"seo_analyzer_scan_date":"2026-01-22 04:21:19","breadcrumb_settings":null,"limit_modified_date":false,"reviewed_by":null,"ai":null,"created":"2021-03-01 01:40:23","updated":"2026-01-22 04:21:19","focus_keyword":null,"additional_keywords":null,"truseo_locale":null},"gt_translate_keys":[{"key":"link","format":"url"}],"_links":{"self":[{"href":"https:\/\/prohoster.info\/en\/wp-json\/wp\/v2\/posts\/36671","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/prohoster.info\/en\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/prohoster.info\/en\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/prohoster.info\/en\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/prohoster.info\/en\/wp-json\/wp\/v2\/comments?post=36671"}],"version-history":[{"count":0,"href":"https:\/\/prohoster.info\/en\/wp-json\/wp\/v2\/posts\/36671\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/prohoster.info\/en\/wp-json\/wp\/v2\/media\/27465"}],"wp:attachment":[{"href":"https:\/\/prohoster.info\/en\/wp-json\/wp\/v2\/media?parent=36671"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/prohoster.info\/en\/wp-json\/wp\/v2\/categories?post=36671"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/prohoster.info\/en\/wp-json\/wp\/v2\/tags?post=36671"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}