MySQL Select 쿼리 문제&문제 풀이

MySQL 쿼리 연습 문제

아래의 사이트에서 DDL 과 DML 을 다운로드 받을 수 있다.

https://www.w3resource.com/mysql-exercises/

해당 사이트의 introduction 에서 아래의 문구가 적힌 링크를 클릭해서 다운로드 할 수 있다.

You may download the structure and data of the database used here

레벨은 그리 높지 않아 초보자 혹은 쿼리문을 오랫동안 하지 않아 다시 빠르게 훑고 싶을 때 해보면 좋은데, 좀 쉬운 감이 있다.

use test_query;

-- 1. Write a query to display the names (first_name, last_name) using alias name “First Name", "Last Name
-- Sample table: employees
select first_name as "First Name", last_name as "Last Name" from employees;

-- 2. Write a query to get unique department ID from employee table.
-- Sample table: employees
select distinct(department_id) from employees;

-- 3. Write a query to get all employee details from the employee table order by first name, descending.
-- Sample table: employees
select * from employees order by first_name desc;

-- 4. Write a query to get the names (first_name, last_name), salary, PF of all the employees (PF is calculated as 15% of salary).
-- -- Sample table: employees
select first_name, last_name, salary,  (salary*0.15) as PF from employees;

-- 5. Write a query to get the employee ID, names (first_name, last_name), salary in ascending order of salary.
-- Sample table: employees
select employee_id, concat(first_name, " " ,last_name) as names, salary from employees order by salary;

-- 6. Write a query to get the total salaries payable to employees.
-- Sample table: employees
select sum(salary) from employees;

-- 7. Write a query to get the maximum and minimum salary from employees table.
-- Sample table: employees
select max(salary) max_salary, min(salary) min_salary from employees;

-- 8. Write a query to get the average salary and number of employees in the employees table.
-- Sample table: employees
select count(*) total_employee, avg(salary) avg_salary from employees;

-- 9. Write a query to get the number of employees working with the company.
-- Sample table: employees
select count(*) from employees;

-- 10. Write a query to get the number of jobs available in the employees table.
-- Sample table: employees
select count(distinct(job_id)) from employees;

-- 11. Write a query get all first name from employees table in upper case.
-- Sample table: employees
select upper(first_name) from employees;

-- 12. Write a query to get the first 3 characters of first name from employees table.
-- Sample table: employees
select substring(first_name, 1, 3) from employees;

-- SUBSTR(str,pos,len) : pos 0 번째 없음. 1번째부터임. 지정한 자릿수에서 지정한 길이 만큼의 문자열 가져오기.
-- str : 원본 문자열
-- pos : 시작 위치 값 (1부터 시작함)
-- len : 가져올 길이 값

#계속 업데이트 예정