<1 ~ 10 > Oracle SQL 기초 ~ WHERE 절

<1, 2>

 

SELECT * FROM EMP;
select * from emp;
select * from EMP;
SELECT * FROM emp;  --이 방식을 선택

--SELECT * FROM EMP;
--select * from emp;
--select * from EMP;
--SELECT * FROM emp;  --이 방식을 선택

--테이블 구조 확인
DESC emp;
DESC dept;
DESC professor;

--현재 스키마(현재 계정)에 포함된 모든 테이블 검색
SELECT * FROM tab;

--emp의 구조와 데이터를 확인
DESC emp;
SELECT * FROM emp;
SELECT eno, ename, job FROM emp;
SELECT eno, ename, sal FROM emp;

--테이블의 정보가 아니라 간단한 수식연산
SELECT 2+3 FROM dual;  -- dual은 형식을 맞추기 위한 용어

 

연습문제

--1)student 테이블 구조를 검색해라
DESC student;


--2)course 테이블 구조를 검색해라
DESC course;

--3)professor 테이블 구조를 검색해라
DESC professor;

--4)score 테이블 구조를 검색해라
DESC score;

--5) 모든 학생의 정보를 검색해라
SELECT * FROM student;

--7) 모든 과목의 정보를 검색해라 
SELECT major FROM student;

--8) 기말고사 시험점수를 검색해라
DESC score;
SELECT * FROM score;


--9) 학생들의 학과와 학년을 검색해라 
DESC student;
SELECT major, syear FROM student;

--10) 각 과목의 이름과 학점을 검색해라 :: 실제업무에서는 학생들이 동명이인이 있을 수도 있고..그래서 학번을 같이 출력해주는게 센스
SELECT * FROM student;

--11) 모든 교수의 직위를 검색해라
DESC professor;
SELECT * FROM professor;
SELECT orders FROM professor;
SELECT pname, orders FROM professor;

 

 

<3, 4> 별명을 이용한 검색

--별명을 이용한 검색

-- 1) 각 사원의 이름과 담당업무를 검색해서 결과를 보고한다 ;중복가능성 ->이름같이 출력
SELECT eno, ename, job FROM emp; --개발할 시에는 ㅇㅋ

SELECT eno 사번, ename 이름, job 업무
  FROM emp; -- 보고서에서는 이렇게 하는게 좋음. 보기에, 수정시에 좋음
  
-- 2) 각 사원의 급여와 1년간 수급하는 급여를 검색하라.(보너스 제외)
SELECT  eno 사번, ename 이름, sal*12 "연간 급여" 
FROM emp; --employeee

-- 3) 사원의 연봉을 검색한다(연봉 == sal*12 + comm) comm:보너스
SELECT eno 사번, ename 이름, sal 급여, comm 보너스, sal*12+comm 연봉
FROM emp;

DB에서 null은 '알 수 없다' or '정해지지 않았다'
null 을 통계조작(의도적인 오류)으로 많이 사용한다.
null 은 둘 중에 하나로 정해야 한다.
  1) null -> 0으로 치환
  2) null -> null을 포함한 컬럼을 계산대상에서 제외 


-- 4)  사원의 연봉을 검색한다(연봉 == sal*12 + comm) comm:보너스
--      null을 포함한 컬럼을 계산대상에서 제외 
--      NVL함수
SELECT eno 사번, ename 이름, sal 급여, comm 보너스, sal*12+NVL(comm, 0) 연봉

FROM emp;

 

연습문제

-- 1) 각 학생의 평점을 검색하라(별명을 사용)
SELECT  sno 학번, avr 평점 
FROM student;

-- 2) 각 과목의 학점수를 검색하라(별명을 사용)
SELECT * FROM course;

SELECT cname 과목, st_num 학점
FROM course;

-- 3) 각 교수의 지위를 검색하라(별명을 사용)
SELECT * FROM prfessor;

SELECT pno 사번, pname 이름, orders 지위
FROM professor;


-- 4) 급여를 10%인상했을 때 연간 지급되는 급여를 검색하라(별명을 사용)
SELECT * FROM emp;
SELECT eno 사번, ename 이름, ((sal+(sal*0.1)+NVL(comm,0))*12) "인상 연간 급여" 
--SELECT eno 사번, ename 이름, ((sal*1.1+comm)*12) "인상 연간 급여" 
                              --1.1 ==110%
FROM emp;

-- 5) 현재 학생의 평균 평점은 4.0만점이다. 이를 4.5만점으로 환산해서 검색하라(별명을 사용)
SELECT * FROM st_score;
--100% 4.0기준일때  x 1.125
SELECT sname 이름, avr*1.125 평점
FROM student 
ORDER BY avr DESC;

 

 

<5, 6> 연결연산자

--1) 사원의 이름을 급여 또는 업무와 함께 검색하시오.
SELECT ename||sal
from emp;

SELECT ename||' ' ||sal
from emp;

SELECT ename||' ' ||sal "이름 급여"
from emp;

--2) 연결 연산자 사용시 주의할 것
--SELECT eno || ' ' || ename ||' ' ||sal*12+NVL(comm, 0) 사원연봉
--FROM emp;
                              -- 문자열에 12를 곱해서 연산이 안된것
SELECT eno || ' ' || ename ||' ' ||(sal*12+NVL(comm, 0)) 사원연봉
FROM emp; 

-- 3) 중복제거
-- 직원들의 업무는 어떤 것이 있는지 검색한다(업무의 종류를 검색)
SELECT job 업무
FROM emp;

SELECT DISTINCT job 업무 --distinct 중복제거 역할
FROM emp;

 

연습문제

--1) '__학과인 __학생의 현재 평점은 __입니다' 형태로 학생의 정보를 출력하라
--SELECT major ||'학과인 ', sname||' 학생의 현재 평점은 ', avr||'입니다.'
--SELECT major ||'학과인 ' 학과, sname||' 학생의 현재 평점은 ' 이름, avr||'입니다.' 평균
SELECT major ||'학과인 ' ||sname||' 학생의 현재 평점은 ' ||avr||'입니다.' "현재 평점"
FROM student;


--2) '__과목은 __학점 과목입니다.' 형태로 과목의 정보를 출력하라
SELECT * FROM course;
SELECT cname ||' 과목은 '||st_num||'학점 과목입니다'
FROM course;


--3) '__교수는 __학과 소속입니다.' 형태로 교수의 정보를 출력하라
SELECT pname ||' 교수는 '|| section ||'학과 소속입니다.'
FROM professor;

--4) 학교에는 어떤 학과가 있는지 검색한다(학생 테이블 기반으로 검색한다)
SELECT DISTINCT major
FROM student;

--5) 학교에는 어떤 학과가 있는지 검색한다(교수 테이블 기반으로 검색한다)
SELECT * FROM professor;

SELECT DISTINCT section
FROM professor;


--6) 교수의 지위는 어떤 것들이 있는지 검색한다
SELECT DISTINCT orders
FROM professor;

 

 

<7, 8> 정렬과 묶음 검색

[정렬]

--1) 이름을 급여순으로 검색하세요
SELECT eno 사번, ename 이름, sal 급여
FROM emp;

SELECT eno 사번, ename 이름, sal 급여
FROM emp
ORDER BY ASC sal; --오름차순 : 점점 올라간다 : 작은값부터: default값이 오름차순이라 안 써도 됨

SELECT eno 사번, ename 이름, sal 급여
FROM emp
ORDER BY sal DESC; --오름차순 : 점점 올라간다 : 작은값부터: default값이 오름차순이라 안 써도 됨

-- 2) 사원의 사번과 이름을 연봉순으로 검색하세요
SELECT eno 사번, ename 이름, sal*12+NVL(comm, 0) 연봉
FROM emp
ORDER BY 연봉 DESC;

SELECT eno 사번, ename 이름, sal*12+NVL(comm, 0) 연봉
FROM emp
ORDER BY 3 DESC; --3번째 칸의 콜론을 기준으로 정렬하라.


[정렬을 이용한 묶음 검색]
-- 3)업무별로 사원의 연봉을 검색한다.  어떤 테이블에서 가져올지부터 생각 -> 절 나눔
SELECT job 업무, eno 사번, ename 이름, sal 급여 --업무별로 정렬했으니 업무를 젤 앞으로 내는게 좋음
FROM emp
ORDER BY 업무;

-- 4) 각 부서별로 사원의 급여를 검색 , 단 급여를 많이 받는 사람부터 검색
DESC emp;

SELECT dno 부서번호, eno 사번, ename 이름, sal 급여
FROM emp
ORDER BY 부서번호 ASC, 급여 DESC; --1 부서별 2 급여

SELECT dno 부서번호, eno 사번, ename 이름, sal 급여
FROM emp
ORDER BY 부서번호, 급여 DESC; --asc는 보통 생략

SELECT dno 부서번호, eno 사번, ename 이름, sal 급여
FROM emp
ORDER BY 1, 4 DESC;

 

연습문제

----1) 성적순으로 학생의 이름을 검색하라
SELECT * FROM student;

SELECT sno 학번, sname 이름, avr 성적
FROM student
ORDER BY 성적 DESC;

 --2) 학과별 성적순으로 학생의 정보를 검색하라
SELECT major 학과, avr 성적, sno 학번, sname 이름
FROM student 
ORDER BY 학과, 성적 DESC;

--3) 학년별 성적순으로 학생의 정보를 검색하라
SELECT syear 학년, avr 성적, sno 학번, sname 이름
FROM student
ORDER BY 학년, 성적 DESC;

--4) 학과별 학년별로 학생의 정보를 성적순으로 검색하라
SELECT major 학과, syear 학년, sno 학번, sname 이름
FROM student 
ORDER BY 학과, 학년;

--5) 학점순으로 과목 이름을 검색하라
SELECT * FROM course;

SELECT st_num 학점, cname 과목이름 
FROM course
ORDER BY 학점;

--6) 각 학과별로 교수의 정보를 검색하라
SELECT * FROM professor;

SELECT section 학과, pno 사번, pname 이름
FROM professor
ORDER BY 학과;

--7) 지위별로 교수의 정보를 검색하라
SELECT orders 지위, pno 사번, pname 이름
FROM professor
ORDER BY 지위 ;

--8) 각 학과별로 교수의 정보를 부임일자 순으로 검색하라
SELECT section 학과, hiredate 부임일자, pno 사번, pname 이름
FROM PROFESSOR
ORDER BY 학과, 부임일자;

 

<9, 10> WHERE 절

--[SQL 문의 조건 (WHERE 절)]

-- 1) 사원 중에 급여가 4000이상인 사원의 명단
SELECT eno 사번, ename 이름, sal 급여
FROM emp;

SELECT eno 사번, ename 이름, sal 급여
FROM emp
WHERE sal >= 4000;

--2) 이름이 김연아인 사원을 검색하시오.
SELECT * --모든 정보
FROM emp
WHERE ename='김연아';

--3) 10번 부서를 제외한 사원의 명단
/*
dno는 VARCHAR2(2)인 문자열의 자료형이다.
아래처럼 dno=10을 하면 테이블의 모든 행의 dno 값을 정수로 형변환하여 비교한다.
dno='10'은 자료형이 일치함 -> 단순 비교만 진행함
기업환경에서는 대량의 테이블이 비일비재하기 때문에 int타입으로 그대로 쓴다면 속도가 매우 저하된다. 
때문에 동일한 자료형으로 비교해야 한다.
*/
SELECT * 
FROM emp
WHERE dno=10;  -- <->반대

SELECT * 
FROM emp
WHERE dno!=10;

SELECT * 
FROM emp
WHERE dno!='10'; --문자열로 바껴도 잘 됨

--4) 연봉이 30000이상인 사원의 이름을 검색하시오
SELECT eno 사번, ename 이름, sal*12+NVL(comm, 0) 연봉
FROM emp
WHERE sal*12+NVL(comm, 0) >= 30000 ;

-- 5) 보너스가 600 이하인 사원을 검색하세요
SELECT eno 사번, ename 이름, comm 보너스
FROM emp
WHERE comm <= 600;

SELECT eno 사번, ename 이름, comm 보너스
FROM emp
WHERE NVL(comm, 0) <= 600;    --null값을 어떻게 표기할 것인가

--7) 입사일이 1996년 이후인 사원의 정보를 검색하시오
SELECT *
FROM emp
WHERE hdate > '1995/12/31';

ALTER SESSION SET nls_date_format= 'YYYY/MM/DD';  --date!!

8) null값을 검색
IS NULL : null검색
IS NOT NULL : null아닌거 검색

  보너스 컬럼이 null값인 사원을 검색하세요
  
SELECT *
FROM emp
WHERE comm IS NULL;

연습문제

1) 화학과 학생을 검색하라


2) 평점이 2.0 미만인 학생을 검색하라


3) 관우 학생의 평점을 검색하라


4) 정교수 명단을 검색하라


5) 화학과 소속 교수의 명단을 검색하라


6) 송강 교수의 정보를 검색하라


7) 학년별로 화학과 학생의 성적을 검색하라


8) 2000년 이전에 부임한 교수의 정보를 부임일순으로 검색하라


9) 담당 교수가 없는 과목의 정보를 검색하라