oracle 조회

limchard·2023년 10월 29일

Database WHERE console db literal oracle select 금액 날짜 다중 다중 정렬 리터럴 서브쿼리 소수점 시퀀스 연산자 정렬 조건 조회 최대 최소 콘솔

oracle

목록 보기

2/4

오라클에서 기본적으로 테이블(relation)이라 한다
모든 데이터는 레코드라고 합니다.
한줄한줄은 column 이라고 합니다.
숫자는 NUMBER, INT 라고 한다.
문자는 VARCHAR2 라고 한다.
model 은 어떤 구조로 만들어져 있는지, 어떤 key로 어떤 class diagram으로 만들어져있는지 보여준다.
select * from tab; : table에서 전체를 선택합니다.
select * from student; : 선택합니다. 전체에서, 학생 그룹을
select name from student; : 선택합니다. 이름에서, 학생을
*은 전체를 말한다.

조회 (기본)

--emp테이블에서 ename만 조회하시오
select ename from emp;

--emp테이블에서 이름과 직업만 조회하시오
select ename,job from emp;

--emp테이블 전체조회
select * from emp;

--student 테이블 전체조회
select * from student;

--표현식 //하나의 표현식 column이 더생김
select name,'지금 실습중' from student;

--컬럼 별칭 allias출력
select name "이름",grade as "학년", birthday 생일 from student;

--emp에서 사원명, 직업, 입사일자 을 조회하시오
select ename "사원명",job 직업,hiredate as "입사일자" from emp;

--DISTINCT:중복된 값을 제거하고 출력하기
--EMP에서 직업군을 중복하지말고 출력
select DISTINCT job 직업군 from emp;

--emp에서 부서번호를 중복없이 출력
select DISTINCT deptno 부서번호 from emp;

--합성연산자: 컬럼을 붙여서 출력
select name ||grade 학년별학생명 from student;

--professor 전체조회
select * from professor;

--하나의 컬럼으로 출력_조인형 님은 정교수 입니다.
select name ||  '님은' || position || '입니다' "교수별직급" from professor;

--emp 에서 smith님의 job은 clerk 입니다.
select ename || '님의 job은'|| job ||'입니다.'  from emp;

--student 에서 서진수님의 키는 180cm, 몸무게는 72kg 입니다.
select name || '님의 키는'|| height||'cm, 몸무게는'|| weight||'kg 입니다.' from student;

--emp에서  내 직업은 slaesman 이고 연봉은 *** 입니다.
select '내 직업은'||job||'이고 연봉은' || sal||'입니다.' from emp;

정렬

-- 정렬
-- student에서 학생명, 학년, 생일을 학생명의 오름차순으로 조회하시오.
-- from절이 오고, 그다음 order 절이 온다.명
-- from절은 절대 빠질 수 없다. 
-- order by : 오름차순. 
SELECT name "학생명", grade "학년", birthday "생일" from student order by name asc; 
-- 오름차순, asc는 생략 가능
SELECT name "학생명", grade "학년", birthday "생일" from student order by name desc; 
-- 내림차순, desc는 생략 불가능

-- emp에서 사원명, 직업, 급여를 급여가 낮은사람 순으로 조회하세요.
select ename"사원명",job"직업",sal"급여" from emp order by sal desc;
select ename"사원명",job"직업",sal"급여" from emp order by 3 desc; // 3열.. 열로도 조회 가능하다.

--emp 에서 직업군을 중복없이 출력하고 직업군의 오름차순으로 조회하시오.
select distinct job from emp order by job;

산술연산자

--산술연산자
-- emp에서 sal에 특별보너스를 100을 더하여 출력하시오. 
select ename "사원명", sal "기본급", sal+100 "특별보너스" from emp;

조건(where)

--where절 : 조건
--emp에서 10번부서에 근무하는 사람의 사원명, 급여와 부서번호를 출력하세요.
select ename "사원명", sal 급여, deptno 부서번호 from emp where deptno=10; 

--emp 에서 이름이 king인 사람의 사원명, 직업을 조회하시오.
select ename 사원명, job 직업 from emp where ename='KING'; -- 문자 사용시에는 소따옴표 사용, 문자는 대소문자 구별해줘야함.

--emp 에서 급여가 3000 이상인 사람의 사원명 급여를 조회하시오.
select ename 사원명, sal 급여 from emp where sal>=3000;

-- 학생테이블에서 키가 180이상인 학생명과 키를 출력하시오.
select name 학생명, height 키 from student where height>180;

-- 학생테이블에서 키가 160~180 사이인 학생명과 키 출력 (between a and b : a가 작은숫자가 들어가야 한다.)
select name 학생명, height 키 from student where height between 160 and 180 order by height;
-- in(a,b) : a,b만 조회하는 것.
--emp 에서 deptno가 20,30 인 사람들의 사원명과 부서번호를 조회
select ename 사원명, deptno 사원명 from emp where deptno in(20,30);

--null 값만 출력
--emp에서 comm이 null의 인사람의 이름과 보너스를 출력하시오.
select ename  사원명, comm 보너스 from emp where comm is null;

--emp에서 comm이 null 이 아닌사람 이름과 보너스를 출력하시오.
select ename  사원명, comm 보너스 from emp where comm is not null;

-- NVL : null 값을 0 으로 바꾸기 _연산이 목적이다.
select ename 사원명, nvl (comm,0) 보너스 from emp;

연산자

-- Like 연산자
-- 학생테이블에서 김씨만 조회하여 모든열을 출력하시오.
select * from student where name like '김%'; -- 김으로 시작하는..
select * from student where name like '%김'; -- 김으로 끝나는..
select * from student where name like '%김%'; -- 김을 포함하는..
-- Like % , _언더바 이용해서 특정순번에 있는 글을 출력한다. 
-- emp에서 사원명이 3번째 글자가 a인 사람의 사원명 출력
select ename 사원명 from emp where ename like '__A%'; 

-- emp에서 사원명이 2번째글자가 a인 사람 또는 3번째 글자가 a인 사람의 사원명 출력
select ename 사원명 from emp where ename like '_A%' or ename like '__A%';

-- count    
-- 학생테이블 총 몇명?
select count(*) from student; 
select count(name) from student; 
select count(*) cnt from student; 

-- emp에서 job이 salesman, manager인 사람의 사원명, 직업군만 출력하시오. (or,   in 연산자 )
select ename 사원명, job 직업군 from emp where job='SALESMAN' or JOB='MANAGER';
select ename 사원명, job 직업군 from emp where job in('SALESMAN','MANAGER');

--emp에서 급여가 2000~3000인 사원명, 직업군, 급여를 출력하세요 (and , between연산자) 
select ename 사원명, job 직업군, sal 급여 from emp where sal>=2000 and sal<=3000;
select ename 사원명, job 직업군, sal 급여 from emp where sal between 2000 and 3000;

-- Q.student 테이블에서 1학년 학생의 학생명, 키를 출력하시오. (단 키가 작은순..)
select name 학생명, height 키 from student where grade=1 order by height asc ; 

--Q. student 테이블에서 2학년 학생의 학생명 생일 키 몸무게를 출력하시오(단 생일이 빠른 사람순서)
select name 학생명, birthday 생일, height 키, weight 몸무게 from student order by birthday asc;

--Q. professor 테이블에서 교수들의 이름중 조 씨성을 가진 교수의 교수명 직위 입사일자를 출력하시오.
select name 교수명, position 직위, hiredate 입사일자 from professor where name like '조%';

--Q. emp 에서 사원명, 급여, 급여*12, 보너스, 급여 총계를 구하시오.
select ename 사원명, sal 급여, sal*12 "급여*12", comm 보너스 from emp;

--Q. emp에서 입사일자가 1982/01/01 이후에 들어온 사람의 사원명과 입사일자를 조회하시오 (입사일자 순)
select ename 이름, hiredate 입사일자 from emp where hiredate>'82/01/01' order by hiredate;

리터럴

-- 날짜 리터럴 ' ' 반드시 사용
-- emp에서 직급이 clerk이고 입사일이 87/05/23일인 사람의 사원명 입사일 구하기
select ename 사원명, hiredate 입사일 from emp where job='CLERK' and hiredate='87/05/23';

-- EMP에서 매니져이거나 82년 이후 입사자의 사원명 직업군 입사일 출력
SELECT ENAME 사원명, JOB 직업군, hiredate 입사일 from emp where job='MANAGER' or hiredate>'82/01/01';

다중 정렬

-- 다중정렬 (1차 정렬의 값이 같을경우 2차 정렬 기준으로 순서를 정한다)
-- student에서 1학년 학생의 이름과 키와 몸무게 출력(키는 작은사람부터, 몸무게는 많은사람부터)
select name 학생명, height 키, weight 몸무게 from student where grade=1 order by height asc,weight desc;
-- emp에서 급여내림차순 후 다시 이름으로 오름차순_ 사원번호, 사원명, 급여 조회
select empno 사원번호, ename 사원명, sal 급여 from emp order by sal desc,ename asc;

-- union
-- unionAll
select name from student where deptno1=101 union select name from student where deptno2=201; -- 하나라도 일치하는 사람 (중복 제거) 합집합 
select name from student where deptno1=101 union all select name from student where deptno2=201; -- 하나라도 일치하는 사람 (중복 포함) 
select name from student where deptno1=101 intersect select name from student where deptno2=201; -- 둘다 일치하는 사람 , 교집합

-- Q. gogak point가 30만~ 50만인 조건에서 고객명과 포인트를 출력하세요 (포인트 많은순)
select * from gogak;
select gname 고객명, point 포인트 from gogak where point between 300000 and 500000 order by point desc;

-- Q.emp의 comm의 null을 0으로 바꾸어서 직업이 manager인 사람만 이름과 보너스를 출력하시오.
select ename 이름, nvl(comm,0)"보너스" from emp where job='MANAGER'; 

--Q.professor 103번 학과 교수들의 이름과 급여,보너스,연봉을 출력하시오.
select name 이름, pay 급여, bonus 보너스, pay+bonus "연봉" from professor where deptno=103;

그룹함수

-- SQL 그룹함수(count, sum, avg)
-- count(*) : null값 포함, count(컬럼) : null값 제외.

select count(*), count(hpage) from professor;
select count(bonus) from professor;
select count(bonus),sum(bonus) from professor;
select avg(bonus) from professor;

최대, 최소, 소수점 표현

-- max, min
select max(sal),min(sal) from emp;
select max(hiredate),min(hiredate) from emp;

-- 소수점
-- emp에서 sal 평균
select round(avg(sal),1) from emp; -- 소숫점 한자리
select round(avg(sal),2) from emp; -- 소숫점 두자리
select round(avg(sal),0) from emp; -- 반올림
select round(avg(sal),-1) from emp; -- 10 단위
select round(avg(sal),-2) from emp; -- 100 단위

콘솔창 출력(날짜, 금액, to_char 포함)

-- 콘솔창에 출력 (from dual)
-- 현재 날짜를 콘솔에 출력 
select sysdate from dual;

-- 내일 날짜를 콘솔에 출력.
select sysdate+1 from dual;

-- to_char : 날짜 및 수치 데이터를 문자로 변환하기위한 함수
-- 날짜에서 년도만 추출
select to_char(sysdate,'year') from dual; -- 영어로 나온다. twenty twenty-three
select to_char(sysdate,'yyyy') from dual; -- 숫자로 나온다. 2023
select to_char(sysdate,'month') from dual; -- 7월
select to_char(sysdate,'mm') from dual; -- 07
select to_char(sysdate,'dd') from dual; --06

select to_char(sysdate,'yyyy-mm-dd') from dual; -- 2023-07-06
select to_char(sysdate,'yyyy-mm-dd hh-mi-ss') from dual; 
select to_char(sysdate,'yyyy-mm-dd hh24-mi-ss') from dual; 

-- to_char 숫자에도 적용 가능하다.
select to_char(1237892312,'999,999,999')from dual; -- 천단위 구분기호. 오른쪽 999의 갯수가 최대 자릿수를 결정한다. 결국 999와 ','의 조합으로 자릿수를 표현하는것 같다.

--emp
select empno 사원번호, ename 사원명, to_char(hiredate,'yyyy') 년도 from emp;

--emp에서 사원번호, 사원명, 급여출력(급여는 천단위 구분기호로 표현)
select empno 사원번호, ename 사원명,  to_char(sal,'L999,999') 급여 from emp; -- L 붙히면 원화 표시 나옴

서브쿼리

-- 서브쿼리
-- 쿼리안에 또다른 쿼리 담김(KING 보다 급여를 많이 받는 사람은?) 2가지 질문..
-- 서브쿼리가 먼저 수행되어 그 결과를 메인쿼리에 전달해주고, 그 값을 받아 메인쿼리가 실행된다.
-- 서브쿼리 안에는 order by 절을 못사용한다. 

-- emp에서 JAMES보다 급여많이 받는 사람의 사원명 급여를 구해보자.
select ename 이름, sal 급여 from emp where ename='JAMES';
select ename 사원명, sal 급여 from emp where sal>(select sal  from emp where ename='JAMES');
 
 -- emp에서 평균급여보다 많이 받는 사람의 사원번호, 사원명, 급여를 나타내시오.
 select empno 사원번호, ename 사원명, sal 급여 from emp where sal>(select avg(sal) from emp);
 
 -- emp에서 이름이 ward인 사람의 MGR과 같은 값을 가진 사람의 목록
 select * from emp where mgr=(select mgr from emp where ename='WARD');
 
 --emp에서 평균급여보다 많이받는 사람 수?
 select count(*) from emp where sal>(select avg(sal)  from emp);

-- 서브쿼리 문제
-- Q1. emp에서 ADAMS와 같은 직업군을 가진 사람의 이름 연봉 직업군 출력
select ename 이름, sal 연봉, job 직업군 from emp where job=(select job from emp where ename='ADAMS');

--Q2. emp에서 SCOTT와 같은 연봉을 받는 사람의 이름과 연봉 출력
select ename 이름, sal 연봉 from emp where sal=(select sal from emp where ename='SCOTT');

--Q3. student에서 제1전공이 일지매 학생과 동일한 학생들의 학생명 제1전공 출력
select name 학생명, deptno1 제1전공 from student where deptno1=(select deptno1 from student where name='일지매');

--Q4. studetn에서 제1전공이 101인 학과의 평균몸무게보다 몸무게가 많은 학생들의 이름과 몸무게 출력
select name 학생명, weight 몸무게 from student where weight>(select avg(weight) from student where deptno1=101);

--Q5. professor에서 입사일이 김영조 교수보다 나중에 입사한 사람의 이름과 입사일 학과번호 출력
select name 이름, hiredate 입사일, deptno 학과번호 from professor where hiredate>(select hiredate from professor where name='김영조');