꾸러기

SELECT ...
FROM table
WHERE expr (SELEC ... FROM table WHERE 조건)

- 연산자 기준으로 우측에 괄호를 묶어서 표현한다.
- subquery 안에 order by 사용을 자제할 것

- single-row subqueries (스칼라 서브쿼리)
subquery에서 1개의 결과만 나오므로 비교연산자 사용 가능

- mltiple-row subqueries
subquery에서 여러개의 결과가 나오므로 비교연산자 직접 사용 불가
any, all 등을 사용함

>any : 최소
<any : 최대
>all : 최대
<all : 최소

- 두 테이블의 조인시킬 컬럼의 이름이 같을 경우
SELECT ...
FROM table1 NATURAL JOIN table2

- 조인시킬 컬럼을 지정할 경우
SELECT ...
FROM table1 JOIN table2
USING (조인할 컬럼);

- 조인에 대한 조건을 추가할 경우
SELECT ...
FROM table1 JOIN table2
ON (조건)

- OUTER JOIN은 인덱스를 무시하고 테이블 전체를 검색하므로 비효율적임

- LEFT OUTER JOIN
SELECT ...
FROM table1 LEFT OUTER JOIN table2;

- RIGHT OUTER JOIN
SELECT ...
FROM table1 RIGHT OUTER JOIN table2;

- FULL OUTER JOIN
SELECT ...
FROM table1 FULL OUTER JOIN table2;

- Cartesian Product
SELECT ...
FROM table1 CROSS JOIN table2;

1.
SQL> select s.name, d.dname
from student s, department d
where s.deptno=d.deptno
order by d.dname, s.name

2
SQL> select



- temp 테이블에서 자신의 생일보다 빠른 사람의 인원수 구하기

SQL> select a.emp_id, a.emp_name, a.birth_date, count(b.birth_date)
from temp a, temp b
where b.birth_date(+)<a.birth_date
group by a.emp_id, a.emp_name, a.birth_date
order by count(b.birth_date);

SQL> select s.name "학생이름", d.dname "학과이름", p.name "교수이름"
from student s, department d, professor p
where s.deptn=d.deptno
and s.profno=p.profno

SQL> select a.cust, g.gift
from test13 g, test14 a
where a.point between g.fpoint and g.tpoint;

SQL> select g.gift, count(a.cust)
from test13 g, test14 a
where a.point between g.fpoint and g.toint
broup by g.gift;

SQL> select a.cust, a.point, g.gift
from test13 g, test14a
where g.gift='갈비세트' and a.point >= g.fpoint;

SQL> select a.emp_id "사번", a,emp_name "이름", to_char(a.birth_date,'YYYY-MM-DD') "생년월일",
trunc(month_between(sysdate,a.birth_date)/12) "현재나이",
a.lev "현재직급"
from temp a, emp_level b
where trunc(month_between(sysdate,a.birth_date)/12) between b.from_age and b.to_age
and b.lev="부장";

SQL> select s.name "이름", s.grade "학년", p.name "교수이름", p.position "직급"
from student s, professor p
where s.profno(+)=p.profno;

SQL> select t.emp_id "사번", t.emp_name "이름", t.lev "직급", t.salary "연봉",
e.from_sal "하한범위", e.to_sal "상한범위"
from temp t, emp_level e
where t.lev=e.lev(+)

SQL> select s.studno, s.name, d.dname, d.loc
from student s, department d
where s. deptno=d.deptno and s.studno=10101;

SQL> select s.studno, s.name, s.weight, d.dname, d.loc
from student s, department d
where s. deptno=d.deptno and s.weight>=80;

1.
SQL> select deptno, avg(weight), count(*)
from student
group by deptno
order by avg(weight) desc;

2.
SQL> select max(count(*)), min(count(*))
from student
group by grade;

3.
SQL> select deptno, grade, count(*), max(weight)
from student
group by deptno, grade
having count(*) >= 3;

4.
SQL> select deptno, grade, avg(height)
from student
group by cube(deptno, grade);

5.
SQL> select deptno, grade, avg(weight)
from student
group by rollup(deptno, grade);

6.
SQL> select deptno, count(*)
from professor
group by deptno
having count(*) <=2;

7.
SQL> select max(sal+nvl(comm,0)),
min(sal+nvl(comm,0)),
round(avg(sal+nvl(comm,0)),1)
from professor;

8.
SQL> select deptno, position, avg(sal)
from professor
group by cube(deptno, position);

9.
SQL> select position
case when avg(sal)>=300 then'우수'
else '보통'
end '구분'
from professor
group by position;

1.
SQL> select name, lower(userid)
4.
SQL> select studno, name, deptno,

- 학생 테이블의 전화번호 칼럼에서 지역번호를 출력하여라.

SQL> select name, sbustr(tel,1,inst(tel,')')-1) tel_loc from student;
SQL> select name, rtrim(substr(tel,1,3),')') tel_loc from student;


- 학생 테이블에서 userid에서 2번째 a의 위치가 5번째인학생의 name, userid를 출력하세요.

SQL> select name, userid from student where instr(userid,'a',1,2)=5;
instr(userid,'a',1,2) ; userid에서 'a'의 1번째 위치에서 2번째로 위치한 곳에 자리수를 반환하시오.


- tdept 테이블의 dept_name 컬럼을 아래처럼 출력되게 하세요.

12경영지원
123456재무
123456총무
12기술지원
123H/W지원

SQL> select lpad(dept_name,10,'1234567890') from tdept; => 123...에서 채우다가 dept_name으로 채움


- tdept 테이블의 dept_name 컬럼을 아래처럼 출력되게 하세요.

경영지원90
재무567890
기술지원90

SQL> select rpad(dept_name,10,substr('1234567890',lengthb(dept_name))+1) from tdept;


- student 테이블의 name 컬럼에서 성 부분을 # 처리하고 이름만 보이게 출력하세요.

SQL> select replace(name,subst(name,1,1),'#') from student;


- student 테이블의 idnum 컬럼에서 주민번호 뒷 7자리를 *로 보이게 출력하세요.

SQL> select replace(idnum,substr(idnum,7,7),'*******') from student;


- 날짜, 시간를 문자로 변환하여 출력하세요.

SQL> select to_char(sysdate, 'yyyy-mm-dd:hh24:mi:ss') from dual;


- 102번 학과 학생들에 대해 주민번호 7번째 문자가 1인 경우 '남자', 2인 경우 '여자'로 변환하여 출력하세요.

SQL> select name, idnum, decode(substr(idnum,7,1),1,'남자',2,'여자') gender from student where deptno=102;


- 학생 테이블에서 키의 범위에 따라 'A', 'B', 'C', 'D' 4개의 등급으로 나누어 출력하세요.
키가 180~190이면 'A', 170~179이면 'B', 160~169이면 'C', 160미만이면 'D'로 출력하세요.

SQL> select name, height,

- 시작하기 (실습환경 만들기)
$ sqlplus /nolog
SQL> conn / as sysdba;
SQL> startup;
SQL> alter user scott identified by tiger account unlock;
SQL> conn scott/tiger;
SQL> @/home/oracle/exam.sql


- 종료하기
SQL> conn / as sysdba;
SQL> shutdown immediate;
SQL> exit

- 연결연산자 ||


- 문제: 학생 테이블에서 성에 ㅇ과 ㅅ이 포함된 학생의 이름만을 출력할 경우
select name frm student
where name between '비%' and '자%'
order by 1;


- '=null'이 안되고 'is null'이 되는 이유
null은 미지값으로 산술, 비교 등의 연산이 불가능함
모르는 값과는 비교할 수 없으므로, null=null이란 비교연산이 불가능함


- 연산자 우선순위
1. 비교연산자, SQL연산자 (BETWEEN, IN, LIKE, IS NULL)
2. NOT
3. AND
4. OR


- 집합연산 UNION, UNION ALL
합집합을 만드는 테이블의 컬럼 숫자가 같아야 한다.


- 묵시적인 데이터 타입의 변환
숫자와 문자를 연산할 경우, 문자는 숫자로 전환된다. (단 숫자로 변환 가능한 문자의 경우에만 가능함)
(숫자는 우측정렬, 문자는 좌측정렬)
예) 1(숫자) + 1(문자) = 2   /   1(숫자) + a(문자) => 오류

- 테이블의 구조 확인하기
SQL> desc 테이블명


- 접속한 유저명 확인
SQL> show user;


- 접속한 유저명으로 프롬프트 변경
SQL> set sqlprompt "_USER>";


- scott으로 접속 (실습)
SQL> conn scott/tiger


- 페이지 크기 설정하기
SQL> set pagesize "크기(숫자)"


- 에디터를 불러와 잘못된 명령을 수정할 경우
SQL> ed


- 명령을 재실행할 경우 slash를 활용
SQL> /


- 영문외의 문자를 값으로 입력할 때에는 ' (홑따옴표)로 묵는다.


- % ; 임의의 길이에 대한 와일드 문자로 *와 같음


- _ ; 임의의 한 글자에 대한 와일드 문자로 ?와 같음


- 컬럼의 이름은 30자까지 사용할 수 있음