Hello, Habr!
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.
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.
SQL (ˈɛsˈkjuˈɛl; 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.
You can read about SQL from various .
This article does not aim to teach you SQL from scratch.
So, let's get started.
We will use the well-known in Oracle with its tables ():
Note that we will only be looking at tasks related to SELECT. There are no tasks for DML and DDL here.
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.
Restricting and Sorting Data
Table Employees. Get a list with information about all employees
Solution
SELECT * FROM employees
Table Employees. Get a list of all employees with the name 'David'
Solution
SELECT *
FROM employees
WHERE first_name = 'David';
Table Employees. Get a list of all employees with job_id equal to 'IT_PROG'
Solution
SELECT *
FROM employees
WHERE job_id = 'IT_PROG'
Table Employees. Get a list of all employees from department 50 (department_id) with a salary greater than 4000
Solution
SELECT *
FROM employees
WHERE department_id = 50 AND salary > 4000;
Table Employees. Get a list of all employees from department 20 and department 30 (department_id)
Solution
SELECT *
FROM employees
WHERE department_id = 20 OR department_id = 30;
Table Employees. Get a list of all employees whose last letter in their name is 'a'
Solution
SELECT *
FROM employees
WHERE first_name LIKE '%a';
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)
Solution
SELECT *
FROM employees
WHERE (department_id = 50 OR department_id = 80)
AND commission_pct IS NOT NULL;
Table Employees. Get a list of all employees whose names contain at least 2 letters 'n'
Solution
SELECT *
FROM employees
WHERE first_name LIKE '%n%n%';
Table Employees. Get a list of all employees whose name length is greater than 4 letters
Solution
SELECT *
FROM employees
WHERE first_name LIKE '%_____%';
Table Employees. Get a list of all employees whose salary is between 8000 and 9000 (inclusive)
Solution
SELECT *
FROM employees
WHERE salary BETWEEN 8000 AND 9000;
Employees Table. Retrieve a list of all employees whose name contains the character '%'
Solution
SELECT *
FROM employees
WHERE first_name LIKE '%%%' ESCAPE '';
Employees Table. Retrieve a list of all manager IDs
Solution
SELECT DISTINCT manager_id
FROM employees
WHERE manager_id IS NOT NULL;
Employees Table. Retrieve a list of employees with their positions in the format: Donald(sh_clerk)
Solution
SELECT first_name || '(' || LOWER (job_id) || ')' employee FROM employees;
Using Single-Row Functions to Customize Output
Employees Table. Retrieve a list of all employees whose name length is greater than 10 characters
Solution
SELECT *
FROM employees
WHERE LENGTH (first_name) > 10;
Employees Table. Retrieve a list of all employees whose name contains the letter 'b' (case insensitive)
Solution
SELECT *
FROM employees
WHERE INSTR (LOWER (first_name), 'b') > 0;
Employees Table. Retrieve a list of all employees whose name contains at least 2 letters 'a'
Solution
SELECT *
FROM employees
WHERE INSTR (LOWER (first_name),'a',1,2) > 0;
Employees Table. Retrieve a list of all employees whose salary is a multiple of 1000
Solution
SELECT *
FROM employees
WHERE MOD (salary, 1000) = 0;
Employees Table. Retrieve the first 3-digit number of the employee's phone number if their number is in the format XXX.XXX.XXXX
Solution
SELECT phone_number, SUBSTR (phone_number, 1, 3) new_phone_number
FROM employees
WHERE phone_number LIKE '___.___.____';
Departments Table. Retrieve the first word from the department name for those with more than one word in the title
Solution
SELECT department_name,
SUBSTR (department_name, 1, INSTR (department_name, ' ')-1)
first_word
FROM departments
WHERE INSTR (department_name, ' ') > 0;
Employees Table. Retrieve employee names without the first and last letter of the name
Solution
SELECT first_name, SUBSTR (first_name, 2, LENGTH (first_name) - 2) new_name
FROM employees;
Employees Table. Retrieve a list of all employees whose last letter in the name is 'm' and the name length is greater than 5
Solution
SELECT *
FROM employees
WHERE SUBSTR (first_name, -1) = 'm' AND LENGTH(first_name) > 5;
Dual Table. Retrieve the date of the next Friday
Solution
SELECT NEXT_DAY (SYSDATE, 'FRIDAY') next_friday FROM DUAL;
Employees Table. Retrieve a list of all employees who have worked in the company for more than 17 years
Solution
SELECT *
FROM employees
WHERE MONTHS_BETWEEN (SYSDATE, hire_date) / 12 > 17;
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
Solution
SELECT *
FROM employees
WHERE MOD (SUBSTR (phone_number, -1), 2) != 0
AND INSTR (phone_number,'.',1,3) = 0;
Employees Table. Retrieve a list of all employees whose job_id value after the '_' has at least 3 characters but is not equal to 'CLERK'
Solution
SELECT *
FROM employees
WHERE LENGTH (SUBSTR (job_id, INSTR (job_id, '_') + 1)) > 3
AND SUBSTR (job_id, INSTR (job_id, '_') + 1) != 'CLERK';
Table Employees. Retrieve a list of all employees replacing all '.' in the PHONE_NUMBER value with '-'
Solution
SELECT phone_number, REPLACE (phone_number, '.', '-') new_phone_number
FROM employees;
Using Conversion Functions and Conditional Expressions
Table Employees. Retrieve a list of all employees who started on the first day of any month
Solution
SELECT *
FROM employees
WHERE TO_CHAR (hire_date, 'DD') = '01';
Table Employees. Retrieve a list of all employees who started working in 2008
Solution
SELECT *
FROM employees
WHERE TO_CHAR (hire_date, 'YYYY') = '2008';
Table DUAL. Show tomorrow's date in the format: Tomorrow is Second day of January
Solution
SELECT TO_CHAR (SYSDATE, 'fm""Tomorrow is ""Ddspth ""day of"" Month') info
FROM DUAL;
Table Employees. Retrieve a list of all employees and their hire dates in the format: 21st of June, 2007
Solution
SELECT first_name, TO_CHAR (hire_date, 'fmddth ""of"" Month, YYYY') hire_date
FROM employees;
Table Employees. Retrieve a list of employees with salary increases of 20%. Show salary with a dollar sign
Solution
SELECT first_name, TO_CHAR (salary + salary * 0.20, 'fm$999,999.00') new_salary
FROM employees;
Table Employees. Retrieve a list of all employees who started in February 2007.
Solution
SELECT *
FROM employees
WHERE hire_date BETWEEN TO_DATE ('01.02.2007', 'DD.MM.YYYY')
AND LAST_DAY (TO_DATE ('01.02.2007', 'DD.MM.YYYY'));
SELECT *
FROM employees
WHERE to_char(hire_date,'MM.YYYY') = '02.2007';
Table DUAL. Output the current date, + second, + minute, + hour, + day, + month, + year
Solution
SELECT SYSDATE now,
SYSDATE + 1 / (24 * 60 * 60) plus_second,
SYSDATE + 1 / (24 * 60) plus_minute,
SYSDATE + 1 / 24 plus_hour,
SYSDATE + 1 plus_day,
ADD_MONTHS (SYSDATE, 1) plus_month,
ADD_MONTHS (SYSDATE, 12) plus_year
FROM DUAL;
Table Employees. Retrieve a list of all employees with total salaries (salary + commission_pct(%)) in the format: $24,000.00
Solution
SELECT first_name, salary, TO_CHAR (salary + salary * NVL (commission_pct, 0), 'fm$99,999.00') full_salary
FROM employees;
Table Employees. Retrieve a list of all employees and information on the existence of salary bonuses (Yes/No)
Solution
SELECT first_name, commission_pct, NVL2 (commission_pct, 'Yes', 'No') has_bonus
FROM employees;
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
Solution
SELECT first_name,
salary,
CASE
WHEN salary = 5000 AND salary < 10000 THEN 'Normal'
ELSE 'High'
END salary_level
FROM employees;
Table Countries. For each country, show the region it belongs to: 1-Europe, 2-America, 3-Asia, 4-Africa (without Join)
Solution
SELECT country_name country,
DECODE (region_id,
1, 'Europe',
2, 'America',
3, 'Asia',
4, 'Africa',
'Unknown')
region
FROM countries;
SELECT country_name
country,
CASE region_id
WHEN 1 THEN 'Europe'
WHEN 2 THEN 'America'
WHEN 3 THEN 'Asia'
WHEN 4 THEN 'Africa'
ELSE 'Unknown'
END
region
FROM countries;
Reporting Aggregated Data Using the Group Functions
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)
Solution
SELECT department_id,
MIN (salary) min_salary,
MAX (salary) max_salary,
MIN (hire_date) min_hire_date,
MAX (hire_date) max_hire_Date,
COUNT (*) count
FROM employees
GROUP BY department_id
order by count(*) desc;
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
Solution
SELECT SUBSTR (first_name, 1, 1) first_char, COUNT (*)
FROM employees
GROUP BY SUBSTR (first_name, 1, 1)
HAVING COUNT (*) > 1
ORDER BY 2 DESC;
Table Employees. How many employees work in the same department and earn the same salary?
Solution
SELECT department_id, salary, COUNT (*)
FROM employees
GROUP BY department_id, salary
HAVING COUNT (*) > 1;
Table Employees. Get a report on how many employees were hired each day of the week. Sort by count
Solution
SELECT TO_CHAR (hire_Date, 'Day') day, COUNT (*)
FROM employees
GROUP BY TO_CHAR (hire_Date, 'Day')
ORDER BY 2 DESC;
Table Employees. Get a report on how many employees were hired each year. Sort by count
Solution
SELECT TO_CHAR (hire_date, 'YYYY') year, COUNT (*)
FROM employees
GROUP BY TO_CHAR (hire_date, 'YYYY');
Table Employees. Get the number of departments that have employees
Solution
SELECT COUNT (COUNT (*)) department_count
FROM employees
WHERE department_id IS NOT NULL
GROUP BY department_id;
Table Employees. Get a list of department_id where more than 30 employees work
Solution
SELECT department_id
FROM employees
GROUP BY department_id
HAVING COUNT (*) > 30;
Table Employees. Get a list of department_id and the rounded average salary of employees in each department.
Solution
SELECT department_id, ROUND (AVG (salary)) avg_salary
FROM employees
GROUP BY department_id;
Table Countries. Get a list of region_id where the sum of all characters in country_name is greater than 60
Solution
SELECT region_id
FROM countries
GROUP BY region_id
HAVING SUM (LENGTH (country_name)) > 60;
Table Employees. Get a list of department_id where employees have multiple (>1) job_id
Solution
SELECT department_id
FROM employees
GROUP BY department_id
HAVING COUNT (DISTINCT job_id) > 1;
Employees Table. Retrieve the list of manager_ids who have more than 5 subordinates and the total salary of their subordinates exceeds 50000
Solution
SELECT manager_id
FROM employees
GROUP BY manager_id
HAVING COUNT (*) > 5 AND SUM (salary) > 50000;
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)
Solution
SELECT manager_id, AVG (salary) avg_salary
FROM employees
WHERE commission_pct IS NULL
GROUP BY manager_id
HAVING AVG (salary) BETWEEN 6000 AND 9000;
Employees Table. Retrieve the maximum salary from all employees whose job_id ends with the word ‘CLERK’
Solution
SELECT MAX (salary) max_salary
FROM employees
WHERE job_id LIKE '%CLERK';
SELECT MAX (salary) max_salary
FROM employees
WHERE SUBSTR (job_id, -5) = 'CLERK';
Employees Table. Retrieve the maximum salary among all average salaries by department
Solution
SELECT MAX (AVG (salary))
FROM employees
GROUP BY department_id;
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
Solution
SELECT LENGTH (first_name), COUNT (*)
FROM employees
GROUP BY LENGTH (first_name)
HAVING LENGTH (first_name) > 5 AND COUNT (*) > 20
ORDER BY LENGTH (first_name);
SELECT LENGTH (first_name), COUNT (*)
FROM employees
WHERE LENGTH (first_name) > 5
GROUP BY LENGTH (first_name)
HAVING COUNT (*) > 20
ORDER BY LENGTH (first_name);
Displaying Data from Multiple Tables Using Joins
Employees, Departments, Locations, Countries, Regions Table. Retrieve the list of regions and the number of employees in each region
Solution
SELECT region_name, COUNT (*)
FROM employees e
JOIN departments d ON (e.department_id = d.department_id)
JOIN locations l ON (d.location_id = l.location_id)
JOIN countries c ON (l.country_id = c.country_id)
JOIN regions r ON (c.region_id = r.region_id)
GROUP BY region_name;
Employees, Departments, Locations, Countries, Regions Table. Retrieve detailed information about each employee:
First_name, Last_name, Department, Job, Street, Country, Region
Solution
SELECT First_name,
Last_name,
Department_name,
Job_id,
street_address,
Country_name,
Region_name
FROM employees e
JOIN departments d ON (e.department_id = d.department_id)
JOIN locations l ON (d.location_id = l.location_id)
JOIN countries c ON (l.country_id = c.country_id)
JOIN regions r ON (c.region_id = r.region_id);
Employees Table. Show all managers who have more than 6 employees under their supervision
Solution
SELECT man.first_name, COUNT (*)
FROM employees emp JOIN employees man ON (emp.manager_id = man.employee_id)
GROUP BY man.first_name
HAVING COUNT (*) > 6;
Employees Table. Show all employees who do not report to anyone
Solution
SELECT emp.first_name
FROM employees emp
LEFT JOIN employees man ON (emp.manager_id = man.employee_id)
WHERE man.FIRST_NAME IS NULL;
SELECT first_name
FROM employees
WHERE manager_id IS NULL;
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)
Example:
first_name | status
Jennifer | Left the company at 31 of December, 2006
Clara | Currently Working
Solution
SELECT first_name,
NVL2 (
end_date,
TO_CHAR (end_date, 'fm""Left the company at"" DD ""of"" Month, YYYY'),
'Currently Working')
status
FROM employees e LEFT JOIN job_history j ON (e.employee_id = j.employee_id);
Tables Employees, Departments, Locations, Countries, Regions. Retrieve a list of employees who live in Europe (region_name)
Solution
SELECT first_name
FROM employees
JOIN departments USING (department_id)
JOIN locations USING (location_id)
JOIN countries USING (country_id)
JOIN regions USING (region_id)
WHERE region_name = 'Europe';
SELECT first_name
FROM employees e
JOIN departments d ON (e.department_id = d.department_id)
JOIN locations l ON (d.location_id = l.location_id)
JOIN countries c ON (l.country_id = c.country_id)
JOIN regions r ON (c.region_id = r.region_id)
WHERE region_name = 'Europe';
Tables Employees, Departments. Show all departments that have more than 30 employees
Solution
SELECT department_name, COUNT (*)
FROM employees e JOIN departments d ON (e.department_id = d.department_id)
GROUP BY department_name
HAVING COUNT (*) > 30;
Tables Employees, Departments. Show all employees who are not part of any department
Solution
SELECT first_name
FROM employees e
LEFT JOIN departments d ON (e.department_id = d.department_id)
WHERE d.department_name IS NULL;
SELECT first_name
FROM employees
WHERE department_id IS NULL;
Tables Employees, Departments. Show all departments with no employees
Solution
SELECT department_name
FROM employees e
RIGHT JOIN departments d ON (e.department_id = d.department_id)
WHERE first_name IS NULL;
Tables Employees. Show all employees who have no subordinates
Solution
SELECT man.first_name
FROM employees emp
RIGHT JOIN employees man ON (emp.manager_id = man.employee_id)
WHERE emp.FIRST_NAME IS NULL;
Tables Employees, Jobs, Departments. Show employees in the format: First_name, Job_title, Department_name.
Example:
First_name | Job_title | Department_name
Donald | Shipping | Clerk Shipping
Solution
SELECT first_name, job_title, department_name
FROM employees e
JOIN jobs j ON (e.job_id = j.job_id)
JOIN departments d ON (d.department_id = e.department_id);
Tables Employees. Get a list of employees whose managers were hired in 2005, but these employees themselves were hired before 2005
Solution
SELECT emp.*
FROM employees emp JOIN employees man ON (emp.manager_id = man.employee_id)
WHERE TO_CHAR (man.hire_date, 'YYYY') = '2005'
AND emp.hire_date < TO_DATE ('01012005', 'DDMMYYYY');
Employees Table. Retrieve a list of employees whose managers were hired in January of any year and whose job_title length exceeds 15 characters.
Solution
SELECT emp.*
FROM employees emp
JOIN employees man ON (emp.manager_id = man.employee_id)
JOIN jobs j ON (emp.job_id = j.job_id)
WHERE TO_CHAR(man.hire_date, 'MM') = '01' AND LENGTH(j.job_title) > 15;
Using Subqueries to Solve Queries
Employees Table. Retrieve a list of employees with the longest first names.
Solution
SELECT *
FROM employees
WHERE LENGTH(first_name) =
(SELECT MAX(LENGTH(first_name)) FROM employees);
Employees Table. Retrieve a list of employees with a salary greater than the average salary of all employees.
Solution
SELECT *
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);
Employees, Departments, Locations Table. Retrieve the city where employees earn the least in total.
Solution
SELECT city
FROM employees e
JOIN departments d ON (e.department_id = d.department_id)
JOIN locations l ON (d.location_id = l.location_id)
GROUP BY city
HAVING SUM(salary) =
(SELECT MIN(SUM(salary))
FROM employees e
JOIN departments d ON (e.department_id = d.department_id)
JOIN locations l ON (d.location_id = l.location_id)
GROUP BY city);
Employees Table. Retrieve a list of employees whose manager earns more than 15000.
Solution
SELECT *
FROM employees
WHERE manager_id IN (SELECT employee_id
FROM employees
WHERE salary > 15000)
Tables Employees, Departments. Show all departments with no employees
Solution
SELECT *
FROM departments
WHERE department_id NOT IN (SELECT department_id
FROM employees
WHERE department_id IS NOT NULL);
Employees Table. Show all employees who are not managers
Solution
SELECT *
FROM employees
WHERE employee_id NOT IN (SELECT manager_id
FROM employees
WHERE manager_id IS NOT NULL)
Employees Table. Show all managers who have more than 6 employees under their supervision
Solution
SELECT *
FROM employees e
WHERE (SELECT COUNT(*)
FROM employees
WHERE manager_id = e.employee_id) > 6;
Employees, Departments Table. Show employees who work in the IT department
Solution
SELECT *
FROM employees
WHERE department_id = (SELECT department_id
FROM departments
WHERE department_name = 'IT');
Tables Employees, Jobs, Departments. Show employees in the format: First_name, Job_title, Department_name.
Example:
First_name | Job_title | Department_name
Donald | Shipping | Clerk Shipping
Solution
SELECT first_name,
(SELECT job_title
FROM jobs
WHERE job_id = e.job_id)
job_title,
(SELECT department_name
FROM departments
WHERE department_id = e.department_id)
department_name
FROM employees e;
Tables Employees. Get a list of employees whose managers were hired in 2005, but these employees themselves were hired before 2005
Solution
SELECT *
FROM employees
WHERE manager_id IN (SELECT employee_id
FROM employees
WHERE TO_CHAR(hire_date, 'YYYY') = '2005')
AND hire_date < TO_DATE('01012005', 'DDMMYYYY');
Employees Table. Retrieve a list of employees whose managers were hired in January of any year and whose job_title length exceeds 15 characters.
Solution
SELECT *
FROM employees e
WHERE manager_id IN (SELECT employee_id
FROM employees
WHERE TO_CHAR(hire_date, 'MM') = '01')
AND (SELECT LENGTH(job_title)
FROM jobs
WHERE job_id = e.job_id) > 15;
That's all for now.
I hope the tasks were interesting and engaging.
I will add to this list of tasks as much as possible.
I would also appreciate any comments and suggestions.
P.S.: If anyone thinks of an interesting task involving SELECT, please write in the comments, and I’ll add it to the list.
Thank you.
Source: habr.com
