| 일 | 월 | 화 | 수 | 목 | 금 | 토 |
|---|---|---|---|---|---|---|
| 1 | 2 | 3 | 4 | 5 | 6 | 7 |
| 8 | 9 | 10 | 11 | 12 | 13 | 14 |
| 15 | 16 | 17 | 18 | 19 | 20 | 21 |
| 22 | 23 | 24 | 25 | 26 | 27 | 28 |
| 29 | 30 | 31 |
Tags
- OpenAI
- 회귀
- AI
- 지도학습
- LG Aimers 4th
- supervised learning
- 머신러닝
- 분류
- Machine Learning
- 티스토리챌린지
- LLM
- LG Aimers
- 딥러닝
- ChatGPT
- deep learning
- GPT-4
- regression
- PCA
- 오블완
- gpt
- 해커톤
- Classification
- LG
Archives
- Today
- Total
SYDev
[데이터베이스] LAB4-sql_6 본문
경희대학교 이영구 교수님의 데이터베이스 수업 복습용 게시물입니다.
-- 1
SELECT last_name, hire_date
FROM employees
WHERE department_id = (
SELECT department_id
FROM employees
WHERE last_name = 'Zlotkey'
)
AND last_name <> 'Zlotkey'
-- 2
SELECT employee_id, last_name, salary
FROM employees
WHERE salary > (
SELECT AVG(salary)
FROM employees
)
ORDER BY salary
-- 3
SELECT employee_id, last_name
FROM employees
WHERE department_id IN (
SELECT department_id
FROM employees
WHERE last_name LIKE '%u%'
)
-- 4
SELECT last_name, department_id, job_id
FROM employees
WHERE department_id IN (
SELECT department_id
FROM departments
WHERE location_id = 1700
)
-- 5
SELECT last_name, salary
FROM employees
WHERE manager_id IN (
SELECT employee_id
FROM employees
WHERE last_name = 'King'
)
SELECT last_name, salary
FROM employees
WHERE manager_id = (
SELECT employee_id
FROM employees
WHERE last_name = 'King' AND employee_id = 100
)
-- 6
SELECT department_id, last_name, job_id
FROM employees
WHERE department_id = (
SELECT department_id
FROM departments
WHERE department_name = 'Executive'
)
-- 7
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%'
)
-- 8
SELECT employee_id, last_name, salary
FROM employees
WHERE salary > (
SELECT AVG(e.salary)
FROM employees e JOIN departments d ON (e.department_id = d.department_id)
WHERE d.location_id IN (
SELECT location_id
FROM locations
WHERE country_id = 'US'
)
)
SELECT employee_id, last_name, salary
FROM employees
WHERE salary > (
SELECT AVG(salary)
FROM employees e JOIN departments d
ON (e.department_id = d.department_id)
JOIN locations l
ON (d.location_id = l.location_id)
GROUP BY l.country_id
HAVING l.country_id = 'US'
)
-- 9
SELECT employee_id, last_name, salary, department_id
FROM employees
WHERE (department_id, salary) IN (
SELECT department_id , MAX(salary)
FROM employees
GROUP BY department_id
)
ORDER BY department_id
-- 10
SELECT e.employee_id, e.last_name, e.salary, e.department_id
FROM employees e, (
SELECT department_id , MAX(salary) salary
FROM employees
GROUP BY department_id
) ee
WHERE e.department_id = ee.department_id
AND e.salary = ee.salary
ORDER BY e.department_id
-- 11
SELECT d.department_id, d.department_name
FROM departments d
WHERE EXISTS (
SELECT e.department_id
FROM employees e
WHERE d.department_id = e.department_id
)
-- 12
SELECT rownum, employee_id, last_name, salary
FROM (
SELECT *
FROM employees
ORDER BY salary
)
WHERE rownum <= 5
-- 1
SELECT last_name, hire_date
FROM employees
WHERE department_id = (
SELECT department_id
FROM employees
WHERE last_name = 'Zlotkey'
)
AND last_name <> 'Zlotkey'
-- 2
SELECT employee_id, last_name, salary
FROM employees
WHERE salary > (
SELECT AVG(salary)
FROM employees
)
ORDER BY salary
-- 3
SELECT employee_id, last_name
FROM employees
WHERE department_id IN (
SELECT department_id
FROM employees
WHERE last_name LIKE '%u%'
)
-- 4
SELECT last_name, department_id, job_id
FROM employees
WHERE department_id IN (
SELECT department_id
FROM departments
WHERE location_id = 1700
)
-- 5
SELECT last_name, salary
FROM employees
WHERE manager_id IN (
SELECT employee_id
FROM employees
WHERE last_name = 'King'
)
SELECT last_name, salary
FROM employees
WHERE manager_id = (
SELECT employee_id
FROM employees
WHERE last_name = 'King' AND employee_id = 100
)
-- 6
SELECT department_id, last_name, job_id
FROM employees
WHERE department_id = (
SELECT department_id
FROM departments
WHERE department_name = 'Executive'
)
-- 7
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%'
)
-- 8
SELECT employee_id, last_name, salary
FROM employees
WHERE salary > (
SELECT AVG(e.salary)
FROM employees e JOIN departments d ON (e.department_id = d.department_id)
WHERE d.location_id IN (
SELECT location_id
FROM locations
WHERE country_id = 'US'
)
)
SELECT employee_id, last_name, salary
FROM employees
WHERE salary > (
SELECT AVG(salary)
FROM employees e JOIN departments d
ON (e.department_id = d.department_id)
JOIN locations l
ON (d.location_id = l.location_id)
GROUP BY l.country_id
HAVING l.country_id = 'US'
)
-- 9
SELECT employee_id, last_name, salary, department_id
FROM employees
WHERE (department_id, salary) IN (
SELECT department_id , MAX(salary)
FROM employees
GROUP BY department_id
)
ORDER BY department_id
-- 10
SELECT e.employee_id, e.last_name, e.salary, e.department_id
FROM employees e, (
SELECT department_id , MAX(salary) salary
FROM employees
GROUP BY department_id
) ee
WHERE e.department_id = ee.department_id
AND e.salary = ee.salary
ORDER BY e.department_id
-- 11
SELECT d.department_id, d.department_name
FROM departments d
WHERE EXISTS (
SELECT e.department_id
FROM employees e
WHERE d.department_id = e.department_id
)
-- 12
SELECT rownum, employee_id, last_name, salary
FROM (
SELECT *
FROM employees
ORDER BY salary
)
WHERE rownum <= 5
-- 부질의 없이 평균 급여보다 많은 급여를 받는 사원의 번호, 이름, 급여를 표시
SELECT e1.employee_id, e1.last_name, e1.salary
FROM employees e1, employees e2
GROUP BY e1.employee_id, e1.last_name, e1.salary -- 이미 employee_id로 group by를 진행했기 때문에 다른 그룹 attribute를 추가해도 그루핑에 문제가 발생하지 않음
HAVING e1.salary > AVG(e2.salary)'3학년 1학기 전공 > 데이터베이스' 카테고리의 다른 글
| [데이터베이스] 5.1. 데이터베이스 설계의 개요 (0) | 2024.05.02 |
|---|---|
| [데이터베이스] 4장. 관계대수와 SQL - 2 (0) | 2024.04.24 |
| [데이터베이스] 4장. 관계 대수와 SQL (0) | 2024.03.20 |
| [데이터베이스] 2장. 관계 데이터 모델과 제약 조건 (3) | 2024.03.14 |
| [데이터베이스] 1장. 데이터베이스 시스템 (0) | 2024.03.11 |
'3학년 1학기 전공/데이터베이스' Related Articles
more
