Oracle/ SELECT절 복습

--SELECT 절의 다양한 활용
--1. 급여가 10% 인상될 경우 각 사원의 연봉을 검색한다
SELECT eno, ename, sal*1.1
FROM emp;

--2. 1년동안 지급되는 급여와 10% 인상되어 1년 동안 지급되는 급여간에 차액을 검색한다
SELECT eno, ename, ((sal*12*1.1) +NVL(comm, 0))-((sal*12) +NVL(comm, 0)) 급여차액
FROM emp;

--3. 각 학생들의 평균평점과 4.5만점으로 환산한 경우 각각 평점의 상승폭은 얼마인지 검색한다
--   (별명을 이용)
SELECT sno, sname, avr 평균평점, (avr/8*9) 환산, (avr/8*9)-avr 상승폭
FROM student;



--연결 연산자와 중복제거
--1. '__학생의 4.5 환산 평점은 __입니다.'형태로 학생의 환산 평점을 출력한다
SELECT sname||' 학생의 4.5 환산 평점은 '|| avr/8*9 ||' 입니다.'
FROM student;

--2. '__과목의 담당 교수 번호는 __입니다' 형태로 과목의 정보를 출력한다
SELECT cname||' 과목의 담당 교수번호는 '|| pno ||' 입니다.'
FROM course;

--3. 학교에 개설된 과목들은 몇 학점짜리인지 검색한다
SELECT cname, st_num
FROM course
ORDER BY st_num;

--4. 학생들이 수강중인 과목의 과목번호를 검색한다
SELECT DISTINCT cno
FROM score
ORDER BY cno;



--정렬과 묶음 검색
--1. 각 부서별로 사원의 연봉을 검색한다
SELECT * 
FROM emp
ORDER BY dno;

--2. 급여가 10% 인상된 경우 부서별로 각 사원의 연봉을 연봉순으로 검색한다
SELECT eno, ename, sal*1.1 인상급여, dno
FROM emp
ORDER BY dno;

--3. 보너스가 100% 인상된 경우 업무별로 각 사원의 연봉을 연봉순으로 검색한다
SELECT eno, ename, ((sal*12)+(NVL(comm, 0)*2)) 연봉
FROM emp
ORDER BY 연봉 DESC;

--4. 학과별 학년별로 학생의 정보를 성적순으로 입력한다
SELECT major, syear, sname, avr
FROM student
ORDER BY major, syear, avr DESC;

--5. 4.5점으로 환산된 평점을 이용 학과별로 학생의 정보를 성적순으로 입력한다
SELECT major, sno, sname, (avr/8*9) 환산평점
FROM student
ORDER BY major, 환산평점 DESC;



--WHERE 절을 이용한 조건 검색
--1. 학과 및 학년별로 학점이 2.0 미만인 학생을 낮은 성적순으로 검색한다
SELECT major, syear, sno, sname, avr
FROM student
WHERE avr <2
ORDER BY avr;

--2. 학과별로 부임일이 1998년 이전인 교수의 정보를 부임일 순으로 검색한다
SELECT * 
FROM professor
WHERE hiredate < '1999/01/01'
ORDER BY hiredate;

--3. 담당교수가 정해지지 않은 과목을 학점순으로 검색한다
SELECT * 
FROM course
WHERE pno IS NULL
ORDER BY st_num;

--4. 보너스가 급여의 10% 이상인 사원을 검색한다
SELECT * 
FROM emp
WHERE comm > (sal/10);



--관계 연산자와 LIKE 연산자
--1. 화학과 학생중에 성이 '사마'인 학생을 검색한다
SELECT * 
FROM student
WHERE sname LIKE '사마%';

--2. 화학과 학생중 4.5 환산 평점이 3.5이상인 학생을 검색한다
SELECT major, sno, sname, (avr/8*9) 환산평점
FROM student
WHERE (avr/8*9) > 3.5
AND major = '화학';

--3. 화학과 이외 학과 학생의 평점을 각 학과 및 학년별로 검색한다
SELECT major, syear, sno, sname, avr
FROM student
WHERE major !='화학'
ORDER BY major, syear;

--4. 교수가 정해진 과목중에 화학관련 과목을 검색한다
--   (이름에 '화학'이란 문자열이 있는 과목)
SELECT * 
FROM course
WHERE cname LIKE '%화학%'
AND pno IS NOT NULL;

--5. 2000년 이후 부임한 교수 중에 직위가 부교수인 교수를 검색한다
SELECT * 
FROM professor
WHERE orders = '부교수'
AND hiredate > '1999/12/31';



--BETWEEN 과 IN 연산자
--1. 평점이 3.0에서 4.0사이의 학생을 학과별로 검색한다
SELECT * 
FROM student
WHERE avr BETWEEN 3 AND 4
ORDER BY major;

--2. 물리, 화학과 학생중 4.5 환산 평점이 3.5에서 4.0사이인 학생을 검색한다
SELECT major, sno, sname, (avr/8*9) 환산평점
FROM student
WHERE (avr/8*9) BETWEEN 3.5 AND 4
AND major IN ('물리', '화학');

--3. 물리, 화학과 학생의 정보를 학년별 성적순으로 검색한다
SELECT * 
FROM student
WHERE major IN ('물리', '화학')
ORDER BY syear, avr DESC;

--4. 물리, 화학과 교수중에 1999년에서 2000년 사이에 부임한 교수의 정보를 직위별로 검색한다
SELECT * 
FROM professor
WHERE hiredate BETWEEN '1999/01/01' AND '2001/01/01'
ORDER BY orders;


   
--등가조인/비등가조인
--1. 김연아의 입사일보다 빨리 부임한 교수의 명단을 검색한다
SELECT *
FROM professor
WHERE TO_DATE(hiredate) < (SELECT TO_DATE(hdate)
                                           FROM emp
                                           WHERE ename= '김연아');

--2. 송강 교수의 과목을 수강하는 학생의 기말고사 점수를 성적 순으로 검색한다
SELECT * 
FROM st_score 
WHERE cno IN(SELECT cno
                      FROM course
                      WHERE pno IN(SELECT pno
                                            FROM professor
                                            WHERE pname ='송강'))
ORDER BY result DESC;                            

--3. 송강 교수가 강의하는 과목에서 평가 점수가 A인 학생의 명단을 과목명과 함께 검색한다
SELECT sno, sname, cname, grade 
FROM st_score 
NATURAL JOIN scgrade
WHERE cno IN(SELECT cno
                      FROM course
                      WHERE pno IN(SELECT pno
                                            FROM professor
                                            WHERE pname ='송강'))
AND result BETWEEN  loscore AND  hiscore
AND grade='A';

--4. 화학과 1학년 학생에게 강의하는 교수의 명단을 검색한다
SELECT * 
FROM professor 
WHERE pno IN(SELECT pno 
                      FROM course
                      WHERE cno IN(SELECT cno 
                                            FROM score
                                            WHERE sno IN(SELECT sno
                                                                  FROM student
                                                                  WHERE syear=1 AND major='화학')));



--조인-자기참조조인,외부조인
--1. 직원중에 자신의 관리자보다 급여가 높은 사람의 급여 정보를 관리자 급여 정보와 같이 검색한다
SELECT e1.eno 사번, e1.ename 사원, e1.sal 급여, e1.mgr 관리자사번,
          e2.eno 관리자사번, e2.ename 관리자, e2.sal 급여
FROM emp e1, emp e2
WHERE e1.mgr=e2.eno
AND e1.sal> e2.sal;

--2. 각 부서의 지역과 부서장을 검색한다
SELECT d.dno, dname, loc, mgr, e.eno, ename
FROM dept d, emp e
WHERE d.dno=e.dno
AND eno=director;



--조인-자연조인과 Using절을 이용한 조인
--1. 송강 교수의 과목을 수강하는 학생의 기말고사 점수를 성적순으로 검색한다
SELECT sno, sname, cname, result
FROM student
JOIN score USING (sno)
JOIN course USING (cno)
JOIN professor USING (pno)
WHERE pname ='송강'
ORDER BY result DESC;

--2. 화학과 1학년 학생에게 강의하는 교수의 명단을 검색한다.
SELECT DISTINCT pname
FROM professor
JOIN course USING (pno)
JOIN score USING (cno)
JOIN student USING (sno)
WHERE major='화학'
AND syear = 1;



--단일행 서브쿼리를 사용하세요
--1. 화학과 1학년 학생중 평점이 평균 이하인 학생을 검색한다
SELECT major, syear, sname, AVG(avr)
FROM student 
GROUP BY major, syear, sname
HAVING AVG(avr) <(SELECT AVG(avr)
                              FROM student)
AND major ='화학' AND syear=1
ORDER BY AVG(avr);

--2. 일반 화학을 수강하는 학생중 성적이 가장 낮은 학생의 이름을 검색한다
--  조인을 전혀 사용하지 않고 서브 쿼리만으로 SQL문을 작성한다
SELECT sno, sname
FROM student
WHERE sno IN(SELECT sno
                      FROM score
                       WHERE result =(SELECT MIN(result)
                                               FROM score
                                               WHERE cno = (SELECT cno
                                                                     FROM course
                                                                     WHERE cname='일반화학' )));