Hello, Habr!
For more than 3 years I have been teaching SQL in various training centers, and one of my observations is that students master and understand SQL better if they are given a task, and not just talk about the possibilities and theoretical foundations.
In this article, I will share with you my list of tasks that I give students as homework and on which we conduct various kinds of brainstorms, which leads to a deep and clear understanding of SQL.
SQL (ΛΙsΛkjuΛΙl; eng. structured query language) is a declarative programming language used to create, modify and manage data in a relational database managed by an appropriate database management system.
You can read about SQL from different .
This article is not intended to teach you SQL from scratch.
So, let's go.
We will use the well-known in Oracle with its tables ():
I note that we will consider only tasks on SELECT. There are no tasks on DML and DDL.
Tasks
Restricting and Sorting Data
Employees table. Get a list with information about all employees
Solution
SELECT * FROM employees
Employees table. Get a list of all employees named 'David'
Solution
SELECT *
FROM employees
WHERE first_name = 'David';
Employees table. Get a list of all employees with job_id equal to 'IT_PROG'
Solution
SELECT *
FROM employees
WHERE job_id = 'IT_PROG'
Employees table. Get a list of all employees from the 50th department (department_id) with a salary (salary) greater than 4000
Solution
SELECT *
FROM employees
WHERE department_id = 50 AND salary > 4000;
Employees table. Get a list of all employees from the 20th and from the 30th department (department_id)
Solution
SELECT *
FROM employees
WHERE department_id = 20 OR department_id = 30;
Employees table. Get a list of all employees whose last letter in their name is 'a'
Solution
SELECT *
FROM employees
WHERE first_name LIKE '%a';
Employees table. Get a list of all employees from the 50th and from the 80th department (department_id) who have a bonus (the 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;
Employees table. Get a list of all employees whose name contains at least 2 letters 'n'
Solution
SELECT *
FROM employees
WHERE first_name LIKE '%n%n%';
Employees table. Get a list of all employees whose name is longer than 4 letters
Solution
SELECT *
FROM employees
WHERE first_name LIKE '%_____%';
Employees table. 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. Get a list of all employees whose name contains the symbol '%'
Solution
SELECT *
FROM employees
WHERE first_name LIKE '%%%' ESCAPE '';
Employees table. Get a list of all manager IDs
Solution
SELECT DISTINCT manager_id
FROM employees
WHERE manager_id IS NOT NULL;
Employees table. Get 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. Get a list of all employees whose name is longer than 10 letters
Solution
SELECT *
FROM employees
WHERE LENGTH (first_name) > 10;
Employees table. Get a list of all employees who have the letter 'b' in their name (case insensitive)
Solution
SELECT *
FROM employees
WHERE INSTR (LOWER (first_name), 'b') > 0;
Employees table. Get 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. Get a list of all employees whose salary is a multiple of 1000
Solution
SELECT *
FROM employees
WHERE MOD (salary, 1000) = 0;
Employees table. Get the first 3-digit number of the employee's phone number if his number is in the format Π₯Π₯Π₯.Π₯Π₯Π₯.Π₯Π₯Π₯Π₯
Solution
SELECT phone_number, SUBSTR (phone_number, 1, 3) new_phone_number
FROM employees
WHERE phone_number LIKE '___.___.____';
Departments table. Get the first word from the department name for those with more than one word in the name
Solution
SELECT department_name,
SUBSTR (department_name, 1, INSTR (department_name, ' ')-1)
first_word
FROM departments
WHERE INSTR (department_name, ' ') > 0;
Employees table. Get employee names without the first and last letter in the name
Solution
SELECT first_name, SUBSTR (first_name, 2, LENGTH (first_name) - 2) new_name
FROM employees;
Employees table. Get a list of all employees whose last letter in the name is equal to 'm' and the length of the name is greater than 5
Solution
SELECT *
FROM employees
WHERE SUBSTR (first_name, -1) = 'm' AND LENGTH(first_name)>5;
Dual table. Get the date of next Friday
Solution
SELECT NEXT_DAY (SYSDATE, 'FRIDAY') next_friday FROM DUAL;
Employees table. Get a list of all employees who have been with the company for over 17 years
Solution
SELECT *
FROM employees
WHERE MONTHS_BETWEEN (SYSDATE, hire_date) / 12 > 17;
Employees table. Get a list of all employees whose last digit of the phone number is odd and consists of 3 numbers 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. Get a list of all employees whose job_id value after the '_' sign has at least 3 characters, but this value after the '_' 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';
Employees table. Get a list of all employees by replacing all '.' in the PHONE_NUMBER value on '-'
Solution
SELECT phone_number, REPLACE (phone_number, '.', '-') new_phone_number
FROM employees;
Using Conversion Functions and Conditional Expressions
Employees table. Get a list of all employees who came to work on the first day of the month (any)
Solution
SELECT *
FROM employees
WHERE TO_CHAR (hire_date, 'DD') = '01';
Employees table. Get a list of all employees who came to work in 2008
Solution
SELECT *
FROM employees
WHERE TO_CHAR (hire_date, 'YYYY') = '2008';
DUAL table. 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;
Employees table. Get a list of all employees and their start date in the format: 21st of June, 2007
Solution
SELECT first_name, TO_CHAR (hire_date, 'fmddth ""of"" Month, YYYY') hire_date
FROM employees;
Employees table. Get a list of employees with increased salaries by 20%. Show salary with dollar sign
Solution
SELECT first_name, TO_CHAR (salary + salary * 0.20, 'fm$999,999.00') new_salary
FROM employees;
Employees table. Get a list of all employees who came to work 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';
DUAL table. Export 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;
Employees table. Get a list of all employees with full 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;
Employees table. Get a list of all employees and information about the availability of salary bonuses (Yes/No)
Solution
SELECT first_name, commission_pct, NVL2 (commission_pct, 'Yes', 'No') has_bonus
FROM employees;
Employees table. Get the salary level of each employee: Less than 5000 is considered Low level, Greater than or equal to 5000 and less than 10000 is considered Normal level, Greater than or equal to 10000 is considered High level
Solution
SELECT first_name,
salary,
CASE
WHEN salary < 5000 THEN 'Low'
WHEN salary >= 5000 AND salary < 10000 THEN 'Normal'
ELSE 'High'
END salary_level
FROM employees;
Countries table. For each country, show the region in which it is located: 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
Employees table. Get a report by department_id with minimum and maximum salary, early and late arrival dates and number of employees. Sort by number of employees (desc)
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;
Employees table. How many employees whose names start with the same letter? Sort by quantity. Show only those where the number 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;
Employees table. How many employees work in the same department and receive the same salary?
Solution
SELECT department_id, salary, COUNT (*)
FROM employees
GROUP BY department_id, salary
HAVING COUNT (*) > 1;
Employees table. Get a report of how many employees were hired on each day of the week. Sort by quantity
Solution
SELECT TO_CHAR (hire_Date, 'Day') day, COUNT (*)
FROM employees
GROUP BY TO_CHAR (hire_Date, 'Day')
ORDER BY 2 DESC;
Employees table. Get a report on how many employees were hired by year. Sort by quantity
Solution
SELECT TO_CHAR (hire_date, 'YYYY') year, COUNT (*)
FROM employees
GROUP BY TO_CHAR (hire_date, 'YYYY');
Employees table. 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;
Employees table. Get list of department_id with more than 30 employees
Solution
SELECT department_id
FROM employees
GROUP BY department_id
HAVING COUNT (*) > 30;
Employees table. Get a list of department_ids 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;
Countries table. Get a list of region_id sum of all letters of all country_names in which more than 60
Solution
SELECT region_id
FROM countries
GROUP BY region_id
HAVING SUM (LENGTH (country_name)) > 60;
Employees table. Get a list of department_id in which employees of several (>1) job_id work
Solution
SELECT department_id
FROM employees
GROUP BY department_id
HAVING COUNT (DISTINCT job_id) > 1;
Employees table. Get a list of manager_id whose number of subordinates is greater than 5 and the sum of all salaries of his subordinates is greater than 50000
Solution
SELECT manager_id
FROM employees
GROUP BY manager_id
HAVING COUNT (*) > 5 AND SUM (salary) > 50000;
Employees table. Get a list of manager_id whose average salary of all his subordinates is between 6000 and 9000 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. Get the maximum salary from all employees job_id that 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. Get the maximum salary among all the average salaries for the 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 name. At the same time, show only those whose name is longer than 5 and the number of employees with the same name is more than 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
Table Employees, Departments, Locations, Countries, Regions. Get a 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;
Table Employees, Departments, Locations, Countries, Regions. Get 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
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;
Employees table, Job_history. The Employee table stores all employees. The Job_history table stores employees who left the company. Get a report on all employees and their status in the company (Employed or left the company with the date of departure)
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);
Table Employees, Departments, Locations, Countries, Regions. Get 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';
Table Employees, Departments. Show all departments with 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;
Table Employees, Departments. Show all employees who are not in 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;
Table 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;
Employees table. 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;
Table 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);
Employees table. Get a list of employees whose managers got a job in 2005, but at the same time, these workers themselves got a job 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. Get a list of employees whose managers got a job in the month of January of any year and the length of the job_title of these employees is more than 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. Get a list of employees with the longest name.
Solution
SELECT *
FROM employees
WHERE LENGTH (first_name) =
(SELECT MAX (LENGTH (first_name)) FROM employees);
Employees table. Get 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);
Table Employees, Departments, Locations. Get the city in which 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. Get a list of employees whose manager receives a salary of more than 15000.
Solution
SELECT *
FROM employees
WHERE manager_id IN (SELECT employee_id
FROM employees
WHERE salary > 15000)
Table 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
Solution
SELECT *
FROM employees e
WHERE (SELECT COUNT (*)
FROM employees
WHERE manager_id = e.employee_id) > 6;
Table Employees, Departments. Show employees who work in the IT department
Solution
SELECT *
FROM employees
WHERE department_id = (SELECT department_id
FROM departments
WHERE department_name = 'IT');
Table 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;
Employees table. Get a list of employees whose managers got a job in 2005, but at the same time, these workers themselves got a job 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. Get a list of employees whose managers got a job in the month of January of any year and the length of the job_title of these employees is more than 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 exciting.
I will add to this list as much as possible.
I will also be glad to any comments and suggestions.
PS: If someone comes up with an interesting task on SELECT, write in the comments, I will add it to the list.
Thank you.
Source: habr.com