|
|
|
|
|
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')
|
|
|
|
|
|
|
|
|
|
|
|
-- <EFBFBD>ߺ<EFBFBD> <EFBFBD><EFBFBD><EFBFBD><EFBFBD> <EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>
|
|
|
|
|
|
(SELECT * FROM EMPLOYEE WHERE DNO = 10)
|
|
|
|
|
|
UNION ALL
|
|
|
|
|
|
(SELECT * FROM EMPLOYEE WHERE JOB = 'chief')
|
|
|
|
|
|
|
|
|
|
|
|
-- <EFBFBD>ܺ<EFBFBD> <EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD> (<EFBFBD>պ<EFBFBD> ȣȯ<EFBFBD><EFBFBD>[: <EFBFBD>Ӽ<EFBFBD> <EFBFBD><EFBFBD><EFBFBD><EFBFBD> <EFBFBD><EFBFBD> Ÿ<EFBFBD><EFBFBD> <EFBFBD><EFBFBD>ġ] <EFBFBD><EFBFBD><EFBFBD><EFBFBD>ġ <EFBFBD><EFBFBD> <EFBFBD><EFBFBD>)
|
|
|
|
|
|
(SELECT ENO, ENAME, DNO, NULL FROM EMPLOYEE)
|
|
|
|
|
|
UNION
|
|
|
|
|
|
(SELECT NULL, NULL, DNO, DNAME FROM DEPARTMENT)
|
|
|
|
|
|
|
|
|
|
|
|
-- <EFBFBD><EFBFBD><EFBFBD><EFBFBD> <EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>
|
|
|
|
|
|
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 <EFBFBD><EFBFBD><EFBFBD><EFBFBD> <EFBFBD><EFBFBD> <EFBFBD><EFBFBD><EFBFBD><EFBFBD> UNION <EFBFBD>ؼ<EFBFBD> <EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>
|
|
|
|
|
|
|
|
|
|
|
|
|
