문제풀이

1번 문제

1.select name, position from sparta_employees;

2.select distinct position from sparta_employees;

3.select salary from sparta_employees where salary between 40000 and 60000;

4.select hire_date from sparta_employees where hire_date < ‘2023-01-01’;

2번 문제

1.select product_name, price from products;

2.select * from products where product_name like “%프로%”;

3.select * from products where product_name like “갤%”;

4.select sum(price) from products;

3번 문제

1.select id from orders where amount >= 2;

2.select * from orders where amount >= 2 and order_date > ‘2023-11-02’;

3.select * from orders where amount < 3 and shipping_fee > 15000;

4.select * from orders order by shipping_fee desc;

4번 문제

1.select name, track from sparta_students;

2.select * from sparta_students where track != “unity”;

3.select * from sparta_students where enrollment_year = 2021 or enrollment_year = 2023;

4.select enrollment_year from sparta_students where track = “Node.js” and grade = “A”;

5번 문제

1.select name from team_projects where aws_cost >= 40000;

2.select * from team_projects where year(start_date) = 2022;

3.select * from team_projects where end_date < date(now);

4.select datediff(end_date, start_date) from team_projects;

6번 문제

1.select name, rating, rank() over(order by rating desc) from lol_users;

2.select * from lol_users where join_date = (select max(join_date) from lol_users);

3.select region, rating,name from lol_users order by region,rating desc;

4.select region, avg(rating) name from lol_users group by region;

7번 문제

1.select * from lol_feedbacks order by satisfaction_score desc;

2.select user_name, max(feedback_date) from lol_feedbacks group by user_name;

3.select count(*) from lol_feedbacks where satisfaction_score = 5;

4.select user_name, count(user_name) from lol_feedbacks group by user_name order by count(user_name) desc, user_name limit 3;

5.select feedback_date, avg(satisfaction_score) from lol_feedbacks group by feedback_date order by avg(satisfaction_score) desc limit 1;

8번 문제

1.select name from doctors where major = “성형외과”;

2.select major, count() from doctors group by major;

3.select count() from doctors where timestampdiff(year, hire_date, curdate()) >= 5;

4.select name, timestampdiff(day, hire_date, curdate()) from doctors;

9번 문제

1.select gender, count(*) from patients group by gender;

2.select name, birth_date,to_days(birth_date), round((to_days(now()) - to_days(birth_date))/365) as agefrom patientswhere floor((to_days(now()) - to_days(birth_date))/365) >= 40;

3.select * from patients where floor((to_days(now()) - to_days(last_visit_date))/365) >= 1;

4.select count(*) from patients where substr(birth_date,1,4) like “%198%”;

10번 문제

1. 현재 있는 총 부서의 수를 구하는 쿼리

= select count(name) from departments;

2. 각 직원들이 어느 부서에 소속되어 있는지 나열

- select e.name, d.name

from employees e

inner join departments d

on e.department_id = d.id;

3. 기술팀 부서에 속한 사람들 나열

select e.name

from employees e

inner join departments d

on e.department_id = d.id

where d.name = “기술팀”;

4. 각 부서 별로 사람이 얼마나 되는지 나열

select d.name, count(e.name)

from employees e

inner join departments d

on e.department_id = d.id

group by d.name;

5. 직원이 없는 부서의 이름을 나열하기

select sol.dep_name

from

(

select d.name as dep_name, count(e.name) as emp_count

from departments d

left join employees e

on d.id = e.department_id

group by d.name

) as sol

where sol.emp_count = 0;

6 마케팅 팀 부서에만 속한 사원 이름 나열하기

select e.name as emp_name from departments d inner join employees e on d.id = e.department_id where d.name not in (select d.name from departments where d.name != “마케팅팀”);