20201024_21 Review

• 연봉이 1000이상 1500이하 

SELECT *
FROM emp
WHERE sal >= 1000 and sal <=1500;

 

SELECT *
FROM emp
WHERE sal BETWEEN 1000 and 1500;

둘이 똑같은 결과가 나온다

 

 

 

 

 

• 직원 * 부서 , sales 부서에 근무중인 직원

SELECT *
FROM emp e
JOIN dept d
ON e.deptno = d.deptno
WHERE d.dname = 'SALES';

 

 

 

 

 

 

• UNION 

SELECT ename FROM emp;

 

 

 

 

SELECT dname FROM dept;

 

 

 

SELECT ename FROM emp
UNION
SELECT dname FROM dept;

 

 

 

 

 

 

SELECT LPAD(ename, 10, '*') FROM emp;

 

 

 

• HAVING 절

SELECT dname, avg(sal) as avgsal
FROM emp e
JOIN dept d
ON d.deptno = e.deptno
GROUP BY dname;

 

 

 

SELECT dname, avg(sal) as avgsal
FROM emp e
JOIN dept d
ON d.deptno = e.deptno
GROUP BY dname
HAVING avg(sal) >= 2000 ;