내 오답들 적기
ㅇ25번
SELECT employee_id, hire_date
FROM employees
WHERE department_id IN (SELECT department_id
FROM employees
WHERE last_name = 'Zlotkey'
AND last_name != 'Zlotkey'
맨 마지막 )를 안 닫아서
ERROR at line 5:
ORA-00907: missing right parenthesis
이 에러가 뜸
ㅇ26번
SELECT employee_id, hire_date
FROM employees
WHERE salary > (SELECT AVG(salary)
FROM employees)
혼자 풀때 생각한거
웨어절에 그룹함수 못 쓰니까 섭쿼리 사용하는 거.. 맞나?
ㅇ27번
SELECT employee_id, last_name
FROM employees
WHERE department_id IN (SELECT department_id
FROM employees
WHERE last_name = '%u%')
등호로 해버리면 노로우셀렉티드 뜨겠지.. 이름이 %u% 인 사람은 없으니까...ㅋㅋㅋ
이름 전체 아는 거 아니니까 = 가 아니라 LIKE
ㅇ28번
SELECT e.last_name, e.department_name, d.location_id
FROM employees e, department d
WHERE d.location_id IN (SELECT location_id
FROM locations
WHERE city = 'Seattle')
FROM employees e, department d
*
ERROR at line 2:
ORA-00942: table or view does not exist
테이블 이름이 디파트먼트s s붙여야지
SELECT e.last_name, e.department_name, d.location_id
FROM employees e, departments d
WHERE d.location_id IN (SELECT location_id
FROM locations
WHERE city = 'Seattle')
SQL> /
SELECT e.last_name, e.department_name, d.location_id
*
ERROR at line 1:
ORA-00904: "E"."DEPARTMENT_NAME": invalid identifier
임플로이 테이블에는 디파트먼트네임 없어!
SELECT e.last_name, d.department_name, d.location_id
FROM employees e, departments d
WHERE d.location_id IN (SELECT location_id
FROM locations
WHERE city = 'Seattle')
AND commission_pct IS NULL
/
내가 계속 빼먹고 있는게 조인조건★★★★★
SELECT e.last_name, d.department_name, d.location_id
FROM employees e, departments d
WHERE e.department_id = d.department_id
AND d.location_id IN (SELECT location_id
FROM locations
WHERE city = 'Seattle')
AND e.commission_pct IS NULL
ㅇ29번
SELECT last_name, hire_date
FROM employees
WHERE hire_date > ( SELECT hire_date
FROM employees
WHERE last_name = 'Davies')
ORDER BY hire_date DESC
수업시간에 풀 때는 맞았는데 혼자 풀때는 틀리네..ㅋㅋ
정확히는>ALL 써야해~~ 데이비스가 여러명일 수 있으니
ㅇ30번
SELECT last_name, salary
FROM employees
WHERE manager_id IN ( SELECT employee_id
FROM employees
WHERE last_name = 'King')
ㅇ31번
이 2개의 차이
SELECT employee_id, last_name, salary
FROM employees
WHERE department_id IN ( SELECT department_id
FROM employees
WHERE salary > (SELECT AVG(salary)
FROM employees)
AND last_name LIKE '%u%)
평균보다 봉급이 크면서 이름에~~인 사원들의 부서아번호
이렇게 해야지
SELECT employee_id, last_name, salary
FROM employees
WHERE department_id IN ( SELECT department_id
FROM employees
WHERE salary > (SELECT AVG(salary)
FROM employees))
AND last_name LIKE '%u%
조건1 평균보다 봉급이 큰 부서
조건2 이름에 ~~인 사원들
즉, 이름 조건이 출력되는 행들에 실행돼
반대로 만약 샐러리가 빠지면
조건1 이름~~인 사원들이 있는 부서
조건2 평균보다 봉급이 큰 사람들
