[DataBase] 4장

student 테이블

---

professor 테이블

---

department 테이블

---

1. 학생 테이블(student)과 학과 테이블(department) 테이블을 사용하여 학생 이름, 전공 학과번호(deptno1), 1전공 학과 이름을 출력하시오.

SELECT s.name, s.deptno1, d.dname
FROM student s,department d
WHERE s.deptno1=d.deptno;

---

2. James Seo의 지도교수가 속한 학과명을 출력하시오. (student, professor, department테이블 이용)

SELECT d.dname
FROM (student s join professor p on s.profno=p.profno)
    join department d on d.deptno=s.deptno1
WHERE s.name='James Seo';

---

3. EMP2 테이블과 p_grade 테이블을 조회하여 사원들의 이름과 나이, 현재 직급, 예상 직급을 출력하세요. 예상 직급은 나이로 계산하며 해당 나이가 받아야 하는 직급을 의미합니다. 나이는 오늘(sysdate)을 기준으로 하되 trunc로 소수점 이하는 절삭해서 계산하시오. - EMP2와 P_GRADE 생성할 것

SELECT e.name, trunc((sysdate-e.birthday)/365) "나이", e.position, p.position
FROM emp2 e join p_grade p on trunc((sysdate-e.birthday)/365)
between p.s_age and p.e_age;

p_grade 테이블

emp2 테이블

---

---

4. customer 테이블과 gift 테이블을 join하여 고객이 자기 포인트보다 낮은 포인트의 상품 중 한가지를 선택할 수 있다고 할 때 Notebook을 선택할 수 있는 고객명과 포인트, 상품명을 출력하시오.

SELECT c.gname "CUST_NAME", c.point, g.gname "GIFT_NAME"
FROM customer c join gift g on c.point >= g.g_start
WHERE g.gname='Notebook';

customer 테이블

gift 테이블

---

---

5. emp 테이블에서 사원 번호, 사원 이름, 입사일, 자신보다 먼저 입사한 사람 인원수를 출력하시오. 단 자신보다 입사일이 빠른 사람수를 오름차순으로 출력하시오.(Oracle join 구문과 ANSI join 구문으로 각각 SQL을 작성하시오)

SELECT e1.empno, e1.ename, e1.hiredate, COUNT(e2.empno)
FROM emp e1, emp e2
WHERE e1.hiredate > e2.hiredate
GROUP BY e1.empno, e1.ename, e1.hiredate
ORDER BY COUNT(e2.empno) ASC;

--- ⬇️ ANSI join ---

SELECT e1.empno, e1.ename, e1.hiredate, COUNT(e2.empno)
FROM emp e1 join emp e2 on e1.hiredate > e2.hiredate
GROUP BY e1.empno, e1.ename, e1.hiredate
ORDER BY COUNT(e2.empno) ASC;

---

📌 5장

1. 아래와 같은 구조의 일반 테이블을 생성하세요.

단, 보너스는 같이 입력되지 않으면 기본값 0으로 처리함

CREATE TABLE new_emp
(   NO NUMBER(5),
    NAME VARCHAR(20),
    HIREDATE DATE,
    BONUS NUMBER(6,2) default 0
);

---

2. emp 테이블에서 empno, ename, sal, comm 으로 이루어진 new_emp2 테이블을 생성하고 해당 속성 값을 복사하시오.

CREATE TABLE new_emp2
AS 
SELECT empno, ename, sal, comm
FROM emp;

---

3. 위 2번 문제에서 생성한 new_emp2 테이블에 DATE 타입을 가진 BIRTHDAY 컬럼을 추가하는 쿼리를 쓰세요. 단 해당 컬럼이 추가될 때 기본값으로 현재날짜( SYSDATE ) 가 자동으로 입력되도록 하세요.

ALTER TABLE new_emp2
ADD (BIRTHDAY DATE default SYSDATE);

---

4. new_emp2 테이블의 BIRTHDAY 컬럼 이름을 BIRTH 로 변경하는 쿼리를 쓰세요.

ALTER TABLE new_emp2 RENAME COLUMN BIRTHDAY TO BIRTH;

---

5. new_emp2 테이블의 empno 컬럼의 길이를 NUMBER(7) 로 변경하는 쿼리를 쓰세요.

ALTER TABLE new_emp2
MODIFY(empno NUMBER(7));

---