쿼리 연습

//

직원의 이름과 직책을 출력하나, 여성만 조회하여라.
 
SELECT CONCAT(e.first_name, ' ', e.last_name), t.title, e.gender 
FROM (SELECT * FROM employees WHERE gender = 'f') e   
JOIN titles t 
ON e.emp_no = t.emp_no LIMIT 30;
 
 
SELECT emp_no, CONCAT(last_name, ' ', first_name) AS name, title FROM employees NATURAL JOIN titles LIMIT 30;
 
SELECT t.emp_no, CONCAT(e.last_name, ' ', e.first_name) AS name,
t.title
FROM employees e 
JOIN titles t USING(emp_no)
LIMIT 30;
 
SELECT e.emp_no,
CONCAT(e.last_name, ' ', e.first_name) AS name,
 t.title 
FROM employees e
JOIN titles t ON(e.emp_no = t.emp_no)
WHERE e.gender='f'
AND t.title = 'ENgineer'
LIMIT 30;
 
가장 오래 근무한 직원의 부서를 오름차순으로 조회하라.
SELECT CONCAT(e.last_name, ' ', first_name), d.dept_name, e.hire_date
FROM (SELECT dept_name FROM eployees e JOIN dept_emp de ON e.emp_no = de.emp_no) AS s 
JOIN departments d ON s.emp_no = d.emp_name
ORDER BY hire_date;
 
SELECT CONCAT(e.last_name, ' ', e.first_name), e.hire_date
FROM employees e, departments d JOIN dept_emp ON  
 
SELECT CONCAT(e.last_name, ' ', e.first_name), d.dept_name 
FROM employees e, dept_emp de, departments d 
WHERE e.emp_no = de.emp_no AND de.dept_no = d.dept_no
ORDER BY e.hire_date
LIMIT 30;
 
부서별 직원수를 출력 
[부서별][직원수]
 
SELECT d.dept_name FROM employees e JOIN dept_emp de ON e.emp_no = de.emp_no  
JOIN departments d ON d.dept_no = de.dept_no LIMIT 30;
 
개발부서의 평균연봉
[개발부 평균연봉]
 
SELECT AVG(s.salary) 
FROM salaries s, dept_emp de, departments d
WHERE s.emp_no = de.emp_no AND de.dept_no = d.dept_no AND d.dept_no = 'd005'; 
 
개발부서 직원의 연봉 순 조회
[사번][이름][고용일][성별][연봉]
 
SELECT e.emp_no, e.first_name, e.gender, s.salary
FROM employees e, salaries s
WHERE e.emp_no IN (SELECT emp_no FROM dept_emp WHERE dept_no = 'd005') AND e.emp_no = s.emp_no;
 
salaries, employees, dept_emp, departments
 
SELECT e.emp_no, concat(e.first_name, ' ', e.last_name) AS name
FROM employees e, dept_emp de, departments d
WHERE de.emp_no = e.emp_no 
AND de.dept_no = d.dept_no
AND de.dept_no = (SELECT de.dept_no FROM employees e, dept_emp de
WHERE e.emp_no = de.emp_no 
AND concat(e.first_name, ' ', e.last_name) = 'Fai Bale');

//

현재 급여가 50000이상인 직원 이름을 출력하라.
 
SELECT concat(first_name,' ', last_name) AS name
FROM employees 
WHERE emp_no IN (SELECT emp_no
FROM salaries 
WHERE salary >= 50000)
LIMIT 30;
 
SELECT concat(first_name,' ', last_name) AS name
FROM employees 
WHERE emp_no NOT IN (SELECT emp_no
FROM salaries 
WHERE salary < 50000)
LIMIT 30;

//

현재 전체사원의 평균 연봉보다 적은 급여를 받는 사원의 이름, 급여를 나타내세요.
1.전체사원의 평균 연봉
SELECT AVG(salary) FROM SALARIES;
2.현재 전체사원의 평균 연봉보다 적은 급여
 
SELECT CONCAT(e.first_name, ' ', e.last_name), s.salary
FROM employees e, salaries s
WHERE s.salary < (SELECT AVG(salary) FROM SALARIES)
AND to_date='9999-01-01'
LIMIT 30;
 
 
현재 가장적은 평균급여를 받고 있는 직책에 한해서 평균 급여를 구하세요.
 
SELECT AVG(s.salary) 
FROM titles t, salaries s
WHERE t.emp_no = s.emp_no
GROUP BY t.title
ORDER BY 1
LIMIT 1;

//

현재 회사 상황을 반영한 근무부서를 사번, 직원 전체이름, 근무부서 형태로 출력
[사번][직원 전체이름][근무부서]
SELECT e.emp_no, CONCAT(e.first_name, ' ', e.last_name), d.dept_name
FROM employees e, current_dept_emp ce, departments d
WHERE ce.dept_no IN (SELECT ce.dept_no FROM current_dept_emp ce, dept_emp_latest_date le WHERE ce.emp_no = le.emp_no)
AND ce.dept_no = d.dept_no
AND ce.emp_no = e.emp_no
AND ce.to_date='9999-01-01'; 
 
현재 회사에서 지급되고 있는 급여체계를 반영한 결과를 출력 (사번, 전체이름, 연봉 의 형태로 출력..)
[사번][전체이름][연봉]
EMPLOYEES, SALARIES, CURRENT_DEPT_EMP;
SELECT e.emp_no, CONCAT(e.first_name, ' ', e.last_name), s.salary
FROM employees e, salaries s
WHERE e.emp_no IN (SELECT emp_no FROM current_dept_emp WHERE to_date = '9999-01-01')
AND s.emp_no = e.emp_no
LIMIT 30;