SQL 문법연습 걷기반 2
키워드
order by 에 논리연산은 안되지만 집계함수는 사용가능하다
where에 alias 는 사용불가지만 함수사용은 가능하다
order by 에는 alias 사용이 가능하다.
datediff(value1 , value 2) 기간을 구해주는 함수
date_sub(기준일 interval 값 값의속성year, month..) 과거의 날짜를 계산해주는 함수.
timestempdiff 윤년까지 고려한 정확한 연도차이(나이)계산
함수값을 select문에 활용하자 서브쿼리 쓸필요없다 . 안정적이고 가독성좋다. 함수값은 where 에도 사용이 가능하다 .alias는 where문에 사용불가.
round 소수값 반올림
flor 소수값 버림 2.8 > 2
ceil 소수첫째자리에서 올림 2.1 > 3
21
select name, rank() over(order by rating desc) op.gg
from lol_users
22
select name, rating, rank() over(order by join_date desc) as "new_user"
from lol_users
아래는 정답코드 그냥 정렬방식에 date를 넣고 order by limit구문을 사용하는게 더 간편
SELECT name
FROM lol_users
ORDER BY join_date DESC LIMIT 1;
윈도우 함수 안에는 limit 사용이 불가능하다.
23
select name, rank() over(partition by region order by rating desc) as "region_ranking"
from lol_users
SELECT id, name, region, rating, join_date
FROM lol_users
ORDER BY region, rating DESC;
order by 에 정렬기준을 두가지 사용도 가능하다.
order by 에 region, rating desc 와 같이 두가지 정렬기준이 있는경우. 우선순위에 따라 차례로 정렬을 수행한다.
region별로 정렬된후 그안에서 rating이 높은 사용자부터 배치된다
24
select region, region_rating
from
(
select region, avg(rating) as region_rating
from lol_users
group by 1
) a
SELECT region, AVG(rating) AS avg_rating
FROM lol_users
GROUP BY region;
정답과 차이
결과값은 같지만 아래가 더 안정적이다.
서브쿼리 자주쓰면 복잡도가 높아지고 성능이 떨어진다
최적화와 가독성을 위해 두번째 쿼리가 더 적합하다.
함수값을 select문에 활용하자 서브쿼리 쓸필요없다 . 안정적이고 가독성좋다.
25
select feedback_date, satisfaction_score
from lol_feedbacks
order by satisfaction_score desc
26
select user_name, max(feedback_date)
from lol_feedback
group 1
27 만족도 점수가 5점인 피드백의 수
select count(satisfaction_score)
from lol_feedbacks
where satisfaction_score = 5
SELECT COUNT(*)
FROM lol_feedbacks
WHERE satisfaction_score = 5;
28
select count(user_name)
from lol_feedbacks
order by user_name desc limit 3;
SELECT user_name, COUNT(*) AS feedback_count
FROM lol_feedbacks
GROUP BY user_name
ORDER BY feedback_count DESC LIMIT 3;
그냥 유저이름같은거 카운팅하면 되는거니까 그룹바이 안해도 되는거 아닌가
아니다 내답은 그냥 이름 전부다 카운트한다. 기준이 없다.
group by 내 답은 전체피드백수를 센다. 유저별로 세기 위해서는 group by를 추가해야한다
29
select feedback_date, avg(satisfaction_score) as avg
from lol_feedbacks
group by feedback_date
order by avg desc limit 1 ;
alias 는 select 절에서 정의된후 사용함으로 당연히 where 절에서 사용이 불가능하다. order by 는 alias 사용 가능
order by 에도 집계함수 사용 가능하다.
30
select name
from doctors
where major = '성형외과';
31
select major, count(1) as doctor_count
from doctors
group by 1;
32
select count(1) as senior_doctor
from doctors
where curdate()-hire_date >= (365*5)
SELECT COUNT(*) AS num_of_doctors
FROM doctors
WHERE hire_date <= DATE_SUB(CURDATE(), INTERVAL 5 YEAR);
현재날짜에서 5년 이전날짜를 계산하고 이를 기준으로 조건설정
date_sub함수
지정한 날짜에 대해 특정 기간(일, 월, 연도)을 빼는(substract)작업을 수행한다. 과거의 날짜를 계산하는 함수
date_sub(기준일, interval 뺄기간의숫자값 뺄기간의 단위)
date_sub ( curdate(), interval 5 year);
두 번째 쿼리가 더 명확하고 읽기 쉬운 표현이다.
DATE_SUB를 사용하면 날짜 계산이 정확하며, 데이터베이스의 날짜 처리 방식과 완전히 호환된다첫 번째 쿼리는 데이터베이스에 따라 숫자 기반 계산 방식에 오류가 생길 가능성이 있을 수 있다.둘 중 선택은 문제 없지만, 더 안정적이고 가독성이 좋은 두 번째 쿼리를 사용하는 것이 권장된다
33.
select doctor, date_sub(curdate(), interval hire_date day) as period_of_employment
form doctors
date_sub함수는 빼는연산이다. 뺀 정수값만큼 과거의 날짜를 돌려주는 함수.
기간은 datediff함수 알수차이를 계산하는 함수
SELECT name, DATEDIFF(CURDATE(), hire_date) AS working_days
FROM doctors;
date_sub(curdate(), interval hire_date day) 2020년7월20일을 뺄수는 없다.
DATEDIFF(CURDATE(), hire_date)
34
select gender, count(1)
from patients
group by gender;
35 나이가 40이상인 환자의 수
select count(age)
from
(
select name, (curdate()-birth_date)/365 as age
from patients
) a
where age >=40;
메인쿼리from 서브쿼리 구조일 때 메인쿼리는 서브쿼리의 컬럼만 쓸수있나? 아니면 서브쿼리의 결과컬럼이 아닌 원래테이블도 참조할 수 있나?
메인쿼리가 서브쿼리를 from절로 사용할 때는 메인쿼리는 서브쿼리의 결과로 생성된 가상테이블만 참조할수있다.
round 소수값 반올림
flor 소수값 버림 2.8 > 2
ceil 소수첫째자리에서 올림 2.1 > 3
TIMESTAMPDIFF(YEAR, birth_date, CURDATE())를 사용하여 현재 날짜(CURDATE())와 birth_date 간의 정확한 연도 차이(나이)를 계산 TIMESTAMPDIFF함수는 윤년까지 고려하므로 더 정확한 나이 계산이 가능하다
36.방문안한지 1년이상된 환자
select name, datediff(curdate(), last_visit_date) as days
from patients
where datediff(curdate(), last_visit_date) >=365
query 문에서 where 절에 alias 는 못쓰지만 select의 컬럼(함수포함)을 사용하는것은 가능하다. 함수호출결과는 직접사용 가능하다
37 1980년대 환자의 수
별칭은 숫자시작안됨.
select count(substr(birth_date, 1,3) = '198') as _1980
from patients
substr은 논리연산자 true or false 값을 반환하는데 count는 논리값을 셀 수 없다.
select count(birth_date)
from patients
where substr(birth_date,1, 3) = '198';
가장 올바른 쿼리
select substr(birth_date,1,3), count(1)
from patients
where substr(birth_date, 1, 3)= '198'
group by 1;
두번째쿼리와 동일하다. group by 로 계산하느냐, count안에 넣느냐 차이.
select substr, count(1)
from patients
where substr(birth_date, 1, 3) = '198'
group by 1;
함수 자체를 카운트한다? 문법적으로 유효하지않다. 첫번째 쿼리와 유사.
38 존재하고있는 총부서의 수
select count(name)
from departmnets;
39 모든직원과 그들이 속한 부서의 이름
select e.name, d.name
from employees e left join departments d on e.employees = d.id
select e.name, d.name
from employees e inner join departments d on e.employees = d.id
답은 inner join null값이 없는것들로만 묶여야해서?
모든직원과 그들의 부서 and 이니까 inner join ? 부서없는직원은 조회가 안될텐데
40. 기술팀 부서에 속한 직원들의 이름.
select d.name, e.name
from emplyees e inner joint departments d on e.department_id = d.id;
where d.name='기술팀'
SELEC e.name
FROM employees e INNER JOIN departments d ON e.department_id = d.id
WHERE d.name = '기술팀';
41.부서별로 직원 수를 계산하는 쿼리를 작성해주세요
select d.name, count(1) as employee_count
from employees e inner join departments d on e.department_id = d.id;
group by 1
SELECT d.name, COUNT(e.id) AS employee_count
FROM departments d LEFT JOIN employees e ON d.id = e.department_id
GROUP BY d.id;
부서가 같은 e .id를센다?
42. 직원이 없는 부서의 이름을 찾는 쿼리를 작성해주세요
select d.name
from departments d left join employess e on d.id=e.department_id
where e.id is null;
SELECT d.name
FROM departments d LEFT JOIN employees e ON d.id = e.department_id
WHERE e.id IS NULL;
43. 마케팅팀 부서에만 속한 직원들의 이름
select d.id, e. name
from employees e inner join departments d on e.department_id = d.id
where d.id = '마케팅팀'
SELECT e.name
FROM employees e INNER JOIN departments d ON e.department_id = d.id
WHERE d.name = '마케팅팀';
44.모든 주문의 주문ID와 주문된 상품의 이름
select p.id, o.id,
from orders o full outer join products p on o.priduct_id = p.id;
45. 총매줄(price*quantity)의합이 가장높은 상품의 id와 해당상품의 총매출
select p.id, sum (p.price*o.qunatatiy) as total_sales
from products p inner join orders o on p.id = o.product_id
group by 1
order by p.id desc limit 1
SELECT p.id, SUM(p.price * o.quantity) AS total_sales
FROM products p INNER JOIN orders o ON p.id = o.product_id
GROUP BY p.id
ORDER BY total_sales DESC LIMIT 1;
.
id 와 총매출 계산 값을 서브쿼리로 짜고 메인쿼리에서 차트세우려고했다.
그냥 집계함수를 select 에 포함.
46. 각상품 id별로 판매된 총수량을 계산하는 쿼리
select p.id, sum(o.quantity) as sum_quantity
from products p inner join orders o on p.id = o.product_id
group by 1;
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, order_date
from orders o inner join products p on o.priduct_id = p.id;
where order_date >= '2023-03-03
48. 가장많이 판매된 상품의 이름
select p.naem, sum(o.quantity)
from products p inner join orders o on p.id=o.product_id
gropu by 1;
order by sum(o.quantity) desc limit1;
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;
49. 각상품 id별로 평균 주문수량
select p.id, avg(o.quantity) as avg_quantity
from products p inner join orders o on p.id = o.orders
group by 1
SELECT p.id, AVG(o.quantity) AS average_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 orders o leftjoin products p on o.priduct_id = p.id
where p.id is null;
order_ id 값이 null이어야 한다.
select p.id, p.name
from products p left join orders o on p.id=o.product_id
where o.id is null;
SELECT p.id, p.name
FROM products p LEFT JOIN orders o ON p.id = o.product_id
WHERE o.id IS NULL;
무엇을 기준으로 삼느냐. 주문이 null값이어야 한다