SQL 과제 - 팀별 SQL퀴즈 풀기

4조 성민제, 조은정, 이다은

[문제 준비]

# 계정생성

CREATE USER 'user4'@'%' IDENTIFIED BY '1234'; GRANT ALL ON *.* TO 'user4'@'%' WITH GRANT OPTION;

 

# 데이터베이스 생성

CREATE DATABASE IF NOT EXISTS employeedb4; USE employeedb4;

 

# 부서 테이블 생성

CREATE TABLE DEPT_4            (DEPTNO INT PRIMARY KEY,     DNAME VARCHAR(10),     LOC VARCHAR(10));

 

# 사원 테이블 생성

CREATE TABLE EMP_4            (EMPNO INT PRIMARY KEY,     ENAME VARCHAR(10) NOT NULL,     JOB VARCHAR(5) NOT NULL,     MGR INT NULL,     HIREDATE DATE NOT NULL,     SAL INT NOT NULL,     COMM INT NULL,     DEPTNO INT NOT NULL,     FOREIGN KEY(DEPTNO) REFERENCES DEPT_4(DEPTNO));

 

# 급여 테이블 생성

CREATE TABLE SALGRADE_4            (GRADE INT,     LOSAL INT,     HISAL INT);

[문제 풀이]

문제 1. SQL의 기본 (11문항)

1-1. 사원의 이름과 급여와 입사일자만을 출력하기 (이다은)

SELECT ENAME, SAL, HIREDATE FROM EMP_4;

1-2. 컬럼 이름에 별칭을 지정 (이다은)

SELECT ENAME AS 이름, SAL AS 급여, HIREDATE AS 입사일자 FROM EMP_4;

1-3. 직급이 중복되지 않고 한 번씩 나열하기 (이다은)

SELECT DISTINCT JOB FROM EMP_4;

1-4. 급여가 300 이하인 사원의 사원번호, 사원 이름, 급여를 출력하기 (이다은)

SELECT EMPNO, ENAME, SAL FROM EMP_4 WHERE SAL <= 300;

1-5. 이름이 “오지호”인 사원의 사원번호, 사원명, 급여를 출력 (조은정)

select EMPNO, ENAME ,SAL from EMP_4 where ENAME='오지호';

1-6. 급여가 250이거나 300이거나 500인 사원들의 사원 번호와 사원명과 급여를 검색 (조은정)

select EMPNO, ENAME, SAL from EMP_4 where SAL IN (250,300,500);

1-7. 급여가 250이거나 300이거나 500도 아닌 사원들을 검색 (조은정)

select EMPNO, ENAME, SAL from EMP_4 where SAL not in (250,300,500);

1-8. LIKE 연산자와 와일드 카드를 사용하여 사원들 중에서 이름이 “김”으로 시작하는 사람과 이름 중에 “기”를 포함하는 사원의 사원번호와 사원이름을 출력 (조은정)

select EMPNO,ENAME from EMP_4 where ENAME LIKE '김%' or ENAME LIKE '%기%';

1-9. 상관이 없는 사원(사장이 되겠지요!)을 검색 (성민제)

SELECT * FROM EMP_4 WHERE MGR IS NULL;

1-10. 사원 테이블에서 최근 입사한 직원 순으로 사원번호, 사원명, 직급, 입사일 컬럼만 출력 (성민제)

SELECT EMPNO, ENAME, JOB, HIREDATE FROM EMP_4 order by HIREDATE desc;

1-11. 부서 번호가 빠른 사원부터 출력하되 같은 부서내의 사원을 출력할 경우, 입사한지 가장 오래된 사원부터 출력 (성민제)

SELECT * FROM EMP_4 ORDER BY DEPTNO ASC ,HIREDATE ASC;

문제 2. 그룹 함수 (8문항)

2-1. 모든 사원의 급여 최고액, 최저액, 총액 및 평균 급여를 출력. (평균에 대해서는 정수로 반올림) (이다은)

SELECT MAX(SAL) AS 최고액, MIN(SAL) AS 최적액, SUM(SAL) AS 총액, ROUND(AVG(SAL)) AS 평균 FROM EMP_4;

2-2. 각 담당 업무 유형별로 급여 최고액, 최저액, 총액 및 평균 급여를 출력. (평균에 대해서는 정수로 반올림) (이다은)

SELECT JOB AS 업무, MAX(SAL) AS 최고액, MIN(SAL) AS 최적액, SUM(SAL) AS 총액, ROUND(AVG(SAL)) AS 평균 FROM EMP_4 GROUP BY JOB;

2-3. count(*) 함수를 이용하여, 담당 업무별 사원수를 출력 (이다은)

SELECT JOB AS 업무, COUNT(*) AS 사원수 FROM EMP_4 GROUP BY JOB;

 

2-4. 과장직급의 인원수를 출력 (조은정)

select COUNT(*) FROM EMP_4 WHERE JOB='과장';

2-5. 급여 최고액, 최저액의 차액을 출력 (조은정)

SELECT MAX(SAL)-MIN(SAL) FROM EMP_4;

2-6. 직급별 사원의 최저 급여를 출력 (성민제)

SELECT JOB,MIN(SAL) FROM EMP_4 GROUP BY JOB ORDER BY SAL;

2-7. 각 부서에 대해 부서번호, 사원수, 부서 내의 모든 사원의 평균 급여 출력. (평균 급여는 소수점 둘째 자리로 반올림) (성민제)

SELECT DEPTNO, COUNT(*) 사원수, round(avg(SAL),2) 평균급여  FROM EMP_4 GROUP BY DEPTNO;

 

 

 

2-8. 각 부서에 대해 부서번호 이름, 지역명, 사원수, 부서 내의 모든 사원의 평균 급여를 출력 (평균 급여는 소수점 둘째 자리로 반올림) (성민제)

SELECT EMP_4.DEPTNO, DEPT_4.DNAME, DEPT_4.LOC, COUNT(*) 사원수, round(AVG(SAL),2) 평균급여 FROM EMP_4, DEPT_4 WHERE EMP_4.DEPTNO = DEPT_4.DEPTNO GROUP BY DEPTNO;