SQL 퀘스트 걷기반 10)
38.현재 존재하고 있는 총 부서의 수를 구하는 쿼리
select count(distinct department_id) from employees;
39.모든 직원과 그들이 속한 부서의 이름을 나열하는 쿼리
select e.name, d.name from employees e left join departments d on e.department_id=d.id;
40.'기술팀' 부서에 속한 직원들의 이름을 나열하는 쿼리
select d.name,e.name from employees e left join departments d on e.department_id=d.id where d.name='기술팀';
41.부서별로 직원 수를 계산하는 쿼리
select d.name, count(e.name) '부서별 직원 수' from employees e left join departments d on e.department_id=d.id group by d.name;
42.직원이 없는 부서의 이름을 찾는 쿼리
select d.name '직원이 없는 부서' from employees e right join departments d on e.department_id=d.id where e.name is null;
43.'마케팅팀' 부서에만 속한 직원들의 이름을 나열하는 쿼리
select d.name, e.name from employees e left join departments d on e.department_id=d.id where d.name='마케팅팀';
걷기반 마지막문제
44.모든 주문의 주문 ID와 주문된 상품의 이름을 나열하는 쿼리
select * from products p inner join orders o on p.id=o.product_id;
45.총 매출(price * quantity의 합)이 가장 높은 상품의 ID와 해당 상품의 총 매출을 가져오는 쿼리
select p.id '매출이 가장 높은 상품 ID' , sum(p.price*o.quantity) '총 매출' from products p inner join orders o on p.id=o.product_id group by p.id order by '총 매출' desc limit 1;
:select에서 컬럼명을 지정하면 order 에서 호출 가능
46.각 상품 ID별로 판매된 총 수량(quantity)을 계산하는 쿼리
select p.id, p.name, o.quantity from products p inner join orders o on p.id=o.product_id;
->SELECT p.id, SUM(o.quantity) AS total_quantity FROM products p INNER JOIN orders o ON p.id = o.product_id GROUP BY p.id;
:문제 이해 못함..
47.2023년 3월 3일 이후에 주문된 모든 상품의 이름을 나열하는 쿼리
select p.name from products p inner join orders o on p.id=o.product_id
where o.order_date>'2023-03-03';
48.가장 많이 판매된 상품의 이름을 찾는 쿼리
select p.name from products p inner join orders o on p.id=o.product_id
order by quantity desc limit 1;
->SELECT p.name, SUM(o.quantity) AS total_quantity FROM products p
INNER JOIN orders o ON p.id = o.product_id GROUP BY p.id ORDER BY total_quantity DESC LIMIT 1;
:select 문에서 미리 group by-sum()함수로 계산한 후 order by 절에서 내림차순 정렬.
49.각 상품 ID별로 평균 주문 수량을 계산하는 쿼리
select avg(o.quantity) from products p inner join orders o on p.id=o.product_id
group by p.id;
50.판매되지 않은 상품의 ID와 이름을 찾는 쿼리
select p.id, p.name from products p inner join orders o on p.id=o.product_id
where o.quantity=0
->where o.id is null 이 더 정확
SQL 달리기반
lv1
select count(*) 'name_cnt' from user where name like '김%';
->SELECT count(distinct(user_id)) as name_cnt
FROM users where substr(name,1,1) = '김'
:count할 때 distinct 주의..
lv2
select date_format(created_at,"%Y-%m-%d") 'created_at', round(avg(point)) 'average_points' from point_users pu group by user_id;
->SELECT DATE(p.created_at) AS created_at,
ROUND(AVG(p.point)) AS average_points
FROM point_users p GROUP BY DATE(p.created_at);
:문제를 제대로 이해 못했다. date함수가 더 간편.
lv3
select u.user_id, u.email, if(p.point is null,'0',p.point) 'point' from users u left join point_users p on u.user_id =p.user_id order by p.point desc;
->SELECT u.user_id, u.email,COALESCE(p.point,0) as point
FROM users u
left JOIN point_users p ON u.user_id = p.user_id
order by p.point desc;
<COALESCE(컬럼 1, 컬럼2,컬럼3...)>
null 값 처리 함수.컬럼1이 null이 아니면 1, 1이 null인 경우 2를 반환, 컬럼2가 null인 경우 3을 반환
lv4
1.select c.CustomerName, count(*) 'OrderCount', sum(o.TotalAmount) 'TotalSpent' from customers c left join orders o on c.CustomerID=o.CustomerID
group by c.CustomerName;
->
SELECT
c.CustomerName,
COUNT(o.OrderID) AS OrderCount,
COALESCE(SUM(o.TotalAmount), 0) AS TotalSpent
FROM
Customers c
LEFT JOIN
Orders o ON c.CustomerID = o.CustomerID
GROUP BY
c.CustomerName;
2.select Country,max(Total) from (select c.Country 'Country' ,c.CustomerName 'CustomerName' ,sum(o.TotalAmount) 'Total' from customers c left join orders o on c.CustomerID=o.CustomerID
group by 1,2 order by Total desc)s group by Country;
->
SELECT
c.Country,
c.CustomerName AS Top_Customer,
SUM(o.TotalAmount) AS Top_Spent
FROM
Customers c
JOIN
Orders o ON c.CustomerID = o.CustomerID
GROUP BY
c.Country, c.CustomerName
HAVING
SUM(o.TotalAmount) = (
SELECT
MAX(SumSpent)
FROM
(SELECT
SUM(o2.TotalAmount) AS SumSpent
FROM
Customers c2
JOIN
Orders o2 ON c2.CustomerID = o2.CustomerID
WHERE
c2.Country = c.Country
GROUP BY
c2.CustomerID) AS Subquery
);
아직 2번 푸는 중..
