SQL/ORACLE
SQL HR 예제 문제 풀이 1
mukom
2022. 11. 4. 18:40
사원의 정보는 EMPLOYEES 테이블에 존재하기 때문에 FROM 절에서는 해당 테이블을 작성한다.
'모든'에 해당하는 키워드는 * 이다.
장소와 관련한 테이블은 LOCATIONS 이며, 그 중에서도 도시만 출력하기 때문에 CITY 컬럼명을 작성하였다.
사원에 관련하기 때문에 대상이 되는 테이블은 EMPLOYEES,
모든 정보를 요구하고 있기 때문에 출력 대상 컬럼은 * 이 된다.
그 중에서도 조건을 걸고 있기 때문에 WHERE 절을 작성하게 되는데,
이름의 첫 글자가 M 으로 시작하는 사원을 찿아야 하기 때문에 LIKE 함수를 사용하였다.
글자의 수는 상관없기 때문에 M 이라는 글자 뒤에 그 무엇이 와도 괜찮다는 표현으로 % 를 작성한다.
위의 문제와 비슷하지만 조건이 달라졌다.
이번에는 첫 글자가 아닌 두 번째 글자가 소문자 a 인 경우를 찾아내는 문제이다.
a 의 앞에 다른 글자가 와야 한다는 표현을 해줘야 하기 때문에 언더바_ 를 작성하였고,
이후 글자 수는 마찬가지로 상관없기 때문에 % 추가하였다.
도시는 LOCATIONS 테이블에 있다.
정렬에 관련한 것은 ORDER BY 절에서 작성해야 하고, CITY 를 정렬 기준으로 잡았다.
정렬은 기본적으로 오름차순이기 때문에 별도의 키워드 사용이 필요없지만,
만약 내림차순으로 출력하고 싶다면 DESC 를 작성해주면 된다.
부서명은 DEPARTMETNS 테이블에 있는 컬럼이다.
이를 정렬하기 위해서는 위와 마찬가지로 ORDER BY 절이 필요하고
내림차순을 위하여 DESC 키워드를 사용하였다.
위의 쿼리에서는 DEPARTMENT_NAME 이 아닌 숫자 1을 사용하고 있는데,
쿼리는 ORDER BY 보다 SELECT 가 먼저 실행되기 때문에 ORDER BY 에서는 컬럼에 대해 번호로 호명이 가능해 진다.
만약 DEPARTMENT_NAME 이 두 번째에 있는 컬럼이었으면 숫자 2로 사용할 수 있다는 것이다.
사원에 관련한 정보이기 때문에 대상이 되는 테이블은 EMPLOYEES
모든 정보이기 때문에 *
연봉이 7000 이상이라는 조건이 있기 때문에 WHERE 을 추가하고 SALARY >= 7000
마지막으로 출력된 정보를 연봉순으로 정렬해야 하기 때문에 ORDER BY 절을 추가한다.
여기에서 숫자를 사용하지 않은 이유는 SELECT 절에서 * 를 사용하면 숫자로 처리할 수 없기 때문이다.
사원들에 대한 정보이기 때문에 대상 테이블은 EMPLOYEES
모든 정보를 조회해야 하기 때문에 *
그 중에서도 인센티브를 받는 사원만 출력해야 하기 때문에 해당 컬럼의 데이터가 NULL 이 아닌 경우를 출력한다.
NULL 값은 = 연산으로는 출력할 수 없기 때문에 반드시 IS NOT | IS NULL 연산을 사용해야 한다.
= NULL 로 하게 되면 NULL = NULL 상태일 때에 값이 UNKNOWN 이 되어 마치 FALSE 인 것처럼 처리된다.
사원에 관련한 정보이기 때문에 FROM 절에는 EMPLOYEES.
모든 정보가 아닌 요구하는 컬럼이 있기 때문에 사원번호, 이름, 부서번호를 SELECT 절에 작성하였는데,
추가적으로 출력된 결과의 조건 충족을 확인하기 위하여 HIRE_DATE 를 추가하였다.
조건은 입사일인데, 입사일의 데이터 타입은 DATE 타입이고,
내가 작성하는 정보는 문자이기 때문에 TO_DATE 함수를 사용하여 DATE 타입으로 변환해 준다.
위의 문제와 유사하지만 이번에는 연도를 특정한 문제이다.
여러 방식의 풀이가 있겠지만 여기서는 기존의 데이터를 TO_CHAR 함수를 적용하여 연도만 추출하고
이를 문자열 2006 과 일치하는지의 여부를 따졌다.
글자 수와 관련한 함수는 LENGTH 함수가 존재한다.
LENGTH 함수의 인자를 FIRST_NAME 을 넣어
해당 컬럼의 데이터가 5글자 이상인지 확인하는 쿼리를 작성하였다.
~별로 해석되는 부분은 GROUP BY 절을 사용해야 한다.
또한 부서 번호별 사원의 수를 조회하는 것은 COUNT 집계 함수를 활용할 수 있는데,
해당 조건을 만족하는 모든 데이터를 세기 때문에 * 를 사용할 수 있다.
위와 마찬가지로 직무 아이디별 이라는 조건이 있기 때문에 GROUP BY 절을 추가하였다.
또한 평균을 구하는 집계 함수 AVG 를 활용하여 평균 연봉을 구할 수 있도록 하였다.
조건절을 통해 상사가 없는 경우를 IS NOT NULL 을 통해 제외하여 출력하였다.
반대의 경우에 IS NULL 을 활용하여 상사가 없는 사원을 출력할 수도 있다.
조회 대상이 되는 컬럼 중에는 부서명이 포함되어 있는데
이는 EMPLOYEES 테이블에는 없는 데이터이고 DEPARTMENTS 테이블에 있는 데이터이다.
때문에 해당 문제는 JOIN 을 통하여 해결해야 한다.
두 테이블의 같은 DEPARTMENT_ID 일 경우 해당되는 DEPARTMENT_NAME 을 출력하도록 하는 것이다.
이를 위하여 USING 절에서 DEPARTMENT_ID 를 추가하였다.
이번 문제 또한 위의 문제처럼 JOIN 을 활용해야 하는 문제이다.
부서명과 도시명을 각각 DEPARTMENTS 테이블과 LOCATIONS 테이블에 존재한다.
서로 다른 테이블에 존재하는 정보를 함께 보려면 공통된 정보를 통해 추출해야 하고
이 두 테이블의 공통 컬럼은 LOCATION_ID 이다.
이번에는 세 개의 테이블을 통한 정보 추출 문제이다.
JOIN 는 USING 과 한 세트로 한 테이블 당 한 세트씩 작성을 해주면 어렵지 않게 위와 같은 형태의 질의 결과를 확인할 수 있다.
이번 문제는 위의 문제에서 JOBS 테이블을 추가해주면 된다.
추출하고자 하는 데이터가 범위 값이 아닌 특정한 값일 때에는 IN 함수를 활용할 수 있다.
IN 함수 괄호에 특정한 값을 작성하게 되면 왼쪽에 작성된 컬럼의
데이터와 일치 여부를 따져 질의 결과를 보여준다.
이 문제는 살짝 복잡하기 때문에 순서대로 생각하는 것이 중요하다.
먼저 SELECT 절에는 평균 연봉과 부서명을 작성한다.
FROM 절에서는 대상이 되는 테이블을 작성해야 하는데, 사원의 연봉은 사원 테이블에 있고 부서명은 부서 테이블에 존재하기 때문에 JOIN 을 활용하였다.
그 다음 조건인 4인 미만의 사원이 근무하는 부서를 찾기 위해서
'부서별 4인 미만의 사원' 으로 GROUP BY 절 을 작성하였다.
위의 문제를 통해 쉽게 적용할 수 있는 문제이다.
두 테이블을 결합하여 결합된 데이터 중 부서명을 추출하고,
그 중에서도 부서의 인원수가 6명 미만인 곳을 출력하도록 하였다.
JOIN 에서의 조건을 더 붙이기 위하여 USING 대신 ON 절을 사용하였다.
이때 ON 절에서 DEPARTMENT_ID 가 서로의 테이블에서 일치하는지에 대해 조건을 꼭 써줘야 하는데,
이를 명시하지 않을 경우 DEPARTMENT_ID 가 60 이 아닌 IT 부서까지 출력되기 때문이다.
이 문제를 해결하기 위해서는 테이블 간의 관계를 알아야 한다.
문제에서 원하는 것은 대륙에 따른 도시를 구해야 하기 때문에 대륙에 관련한 테이블과 도시와 관련한 테이블이 필요하다.
대륙에 관련한 테이블은 REGIONS 이고, 도시와 관련한 테이블은 LOCATIONS 이다.
이들은 직접적인 관련이 없는 테이블이기 때문에 중간을 잇고 있는 COUNTRIES 가 필요해진다.
때문에 JOIN 문을 활용하여 이 세 테이블을 합쳤고,
대륙명 별로 출력하기 위하여 GROUP BY 절을 통하여 출력을 하였다.
위의 문제와 비슷한 문제로 도시명은 LOCATIONS 테이블에 존재하고,
부서 관련은 부서 테이블에 존재한다.
때문에 이들을 JOIN 으로 묶어 도시명 별로 추리기 위하여 GROUP BY 절을 활용하였다.