DAY.02

/*
	p.151 중복제거 distinct 
	distinct는 보려고하는 컬럼에서 조합했을 때 중복이 되는
	경우 중복제거가 된다.
	
	예약어(파란색 키워드): 약속된 명령어
	PK(Primary Key, 기본키, 노란색 키 모양), 단일키/복합키
	
*/
 

-- 443,308 row
SELECT * FROM titles;

-- 443,308 row
SELECT DISTINCT * FROM titles;

-- 443,308 row
SELECT title FROM titles;

-- 7 row
SELECT DISTINCT title FROM titles;

-- 443,308 row
SELECT title, from_date FROM titles;

-- 34,823 row
SELECT DISTINCT title, from_date FROM titles;


-- WHERE 절
-- 비교 연산자, 관계 연산자 
-- 동등 Equal
SELECT * FROM salaries WHERE salary = 69722;

-- 초과
SELECT * FROM salaries WHERE salary > 69722;

-- 이상
SELECT * FROM salaries WHERE salary >= 69722;

-- 이하
SELECT * FROM salaries WHERE salary <= 69722;

-- 미만
SELECT * FROM salaries WHERE salary < 69722;

-- 범위 BETWEEN 이상 AND 이하
SELECT * FROM salaries 
 WHERE salary BETWEEN 50000 AND 70000;

SELECT * FROM salaries 
 WHERE salary >= 50000 
   AND salary <= 70000; 

-- 연봉이 69722, 70698 인 자료를 찾고싶다.
SELECT * FROM salaries WHERE salary IN (69722, 70698);

SELECT * FROM salaries 
 WHERE salary = 69722
    OR salary = 70698;

SELECT * FROM salaries WHERE salary NOT IN (69722, 70698);

SELECT * FROM salaries WHERE salary != 69722;
SELECT * FROM salaries WHERE salary <> 69722;

-- LIKE 
SELECT * FROM employees WHERE first_name LIKE 'Sumant';
SELECT * FROM employees WHERE first_name = 'Sumant';

SELECT * FROM employees WHERE first_name LIKE 'Su%';
SELECT * FROM employees WHERE first_name LIKE '%li';
SELECT * FROM employees WHERE first_name LIKE '_ani%';
SELECT * FROM employees WHERE first_name LIKE '__niy%';


-- 질문. 이름에 asa 를 포함하고 있는 사람 찾고 싶다. 
SELECT * FROM employees WHERE first_name LIKE '%asa%';

-- NULL
SELECT * FROM titles WHERE to_date IS NULL;
SELECT * FROM titles WHERE to_date <=> NULL;

SELECT * FROM titles WHERE to_date IS NOT NULL;


SELECT *, salary = 69722
FROM salaries; 

SELECT TRUE AND TRUE, TRUE AND FALSE, FALSE AND FALSE
   , TRUE AND TRUE AND FALSE
   , TRUE AND TRUE AND TRUE;
   
SELECT *, emp_no = 10001, salary >= 70000, salary <= 80000
FROM salaries
WHERE emp_no = 10001;   

SELECT *, emp_no = 10001, salary >= 70000, salary <= 80000
FROM salaries
WHERE emp_no = 10001
AND salary >= 70000
AND salary <= 80000;

-- order by: 정렬 (오름차순(ASC, 생략가능), 내림차순(DESC))
SELECT *
FROM employees
ORDER BY first_name DESC;

SELECT *
FROM employees
ORDER BY first_name, gender DESC;


SELECT *
FROM employees
WHERE emp_no = 10000;

-- 집계함수와 group by
-- count() 집계함수는 튜플 수 (row count), NULL은 카운팅 제외
-- sum() 집계함수는 모든값 더한다.
SELECT COUNT(salary), SUM(salary), AVG(salary)
     , SUM(salary) / COUNT(salary)
     , MAX(salary), MIN(salary)
FROM salaries; 


SELECT emp_no, COUNT(salary), SUM(salary), AVG(salary)
     , SUM(salary) / COUNT(salary)
     , MAX(salary), MIN(salary)
FROM salaries
GROUP BY emp_no; 

SELECT emp_no
FROM salaries
GROUP BY emp_no;
 

SELECT distinct emp_no
FROM salaries;

-- 사원번호가 10,005인 사원의 평균 연봉을 표시해 주세요.
SELECT emp_no, AVG(salary)
FROM salaries
WHERE emp_no = 10005;

SELECT emp_no, AVG(salary)
FROM salaries
WHERE emp_no = 10005
GROUP BY emp_no;

-- 사원번호가 10005, 10006인 각 사원의 평균 연봉을 표시해 주세요.

SELECT emp_no, AVG(salary)
FROM salaries
WHERE emp_no BETWEEN 10005 AND 10006
GROUP BY emp_no;


-- HAVING: GROUP BY의 조건문
-- 사원의 평균 연봉이 80,000 이상인 사람들을 찾고 싶다.

SELECT emp_no, AVG(salary)
FROM salaries
GROUP BY emp_no
HAVING AVG(salary) >= 80000;

/*
	직원의  출생년도별  직원 수를 알고싶다. 
*/

SELECT YEAR('2020-10-12');

SELECT *, YEAR(birth_date)
FROM employees;

SELECT YEAR(birth_date), COUNT(1)
FROM employees
GROUP BY YEAR(birth_date);



SELECT *
FROM titles
WHERE emp_no = 10001
AND title = 'Senior Engineer'
AND from_date = '1986-06-26';


INSERT INTO titles
(emp_no, title, from_date, to_date)
VALUES
(10001, 'Senior Engineer', '1986-06-28', NULL);







SELECT COUNT(1), COUNT(emp_no), COUNT(to_date) FROM titles;