You can not select more than 25 topics
Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.
51 lines
1.5 KiB
51 lines
1.5 KiB
USE Study
|
|
GO
|
|
|
|
(SELECT * FROM EMPLOYEE WHERE DNO = 10)
|
|
UNION
|
|
(SELECT * FROM EMPLOYEE WHERE JOB = 'chief')
|
|
|
|
(SELECT * FROM EMPLOYEE WHERE DNO = 10)
|
|
INTERSECT
|
|
(SELECT * FROM EMPLOYEE WHERE JOB = 'chief')
|
|
|
|
(SELECT * FROM EMPLOYEE WHERE DNO = 10)
|
|
EXCEPT
|
|
(SELECT * FROM EMPLOYEE WHERE JOB = 'chief')
|
|
|
|
-- 중복 허용 합집합
|
|
(SELECT * FROM EMPLOYEE WHERE DNO = 10)
|
|
UNION ALL
|
|
(SELECT * FROM EMPLOYEE WHERE JOB = 'chief')
|
|
|
|
-- 외부 합집합 (합병 호환성[: 속성 개수 및 타입 일치] 불일치 할 때)
|
|
(SELECT ENO, ENAME, DNO, NULL FROM EMPLOYEE)
|
|
UNION
|
|
(SELECT NULL, NULL, DNO, DNAME FROM DEPARTMENT)
|
|
|
|
-- 집단 연산자
|
|
SELECT AVG(SALARY) FROM EMPLOYEE
|
|
|
|
SELECT MAX(SALARY) AS MAXSALARY, MIN(SALARY) AS MINSALARY FROM EMPLOYEE
|
|
|
|
SELECT AVG(SALARY) AS AVERAGE, VAR(SALARY) AS VARIANCE, STDEV(SALARY) AS STANDARDDEVIATION FROM EMPLOYEE
|
|
|
|
SELECT * FROM EMPLOYEE
|
|
SELECT COUNT(*) AS NUMROW, COUNT(COMMISSION) FROM EMPLOYEE
|
|
|
|
SELECT * FROM EMPLOYEE
|
|
SELECT COUNT(JOB) AS JOB, COUNT(DISTINCT JOB) AS DISTJOB FROM EMPLOYEE
|
|
|
|
SELECT DNO, AVG(SALARY) AS AVGSAL FROM EMPLOYEE GROUP BY DNO
|
|
|
|
SELECT DNO, AVG(SALARY) AS AVGSAL FROM EMPLOYEE GROUP BY DNO HAVING MAX(SALARY) > 500
|
|
|
|
SELECT DNO, JOB, AVG(SALARY) AS AVGSAL FROM EMPLOYEE GROUP BY DNO, JOB ORDER BY DNO
|
|
|
|
SELECT DNO, JOB, AVG(SALARY) AS AVGSAL FROM EMPLOYEE GROUP BY DNO, JOB WITH ROLLUP
|
|
|
|
SELECT DNO, JOB, AVG(SALARY) AS AVGSAL FROM EMPLOYEE GROUP BY DNO, JOB WITH CUBE
|
|
|
|
SELECT DNO, JOB, AVG(SALARY) AS AVGSAL FROM EMPLOYEE GROUP BY GROUPING SETS (DNO, JOB) -- DNO, JOB 따로 한 것을 UNION 해서 보여줌
|
|
|
|
|
|
|