[무작정 기록] DB 연습문제 (오라클 기준)
primary key
테이블을 만들 때에 다른 레코드와 구별할 수 있는 속성이 필요한데
이것을 "주식별자" 라고 하며 주식별자를 설정하기 위하여 primary key로 한다.
foreign key
두개의 테이블을 관계로 설정 할 때에
자식테이블의 어떠한 속성을 부모의 주식별자를 참조하도록 하기 위하여 사용한다.
<<데이터 조회 하기>>
select 컬럼이름(들)
from 테이블이름(들)
[ where 조건식 ] --> 있을수도 없을수도 있다.
# 중복을 제거하고 싶은 경우 distinct 사용
select distinct 컬럼이름(들) from 테이블이름(들);
<< where 절에 사용할 수 있는 연산자 >>
비교 : >, <, >=, <=, =, <> (<>는 같지 않냐는 뜻)
논리 : and, or (순서대로 논리 곱, 논리 합)
집합 : in, not in
범위 : between a and b
null : is null, is not null --> null(X)
패턴 : like (어떤 패턴을 따르는지 체크해야 할 때 사용)
- 가격이 20000원 이상인 도서의 목록을 출력
select * from book where price >= 20000;
// 비교연산자 사용할때 생각하고 사용하기!
- 출판사가 '굿스포츠'가 아닌 모든 도서목록을 출력
select * from book where publisher <> '굿스포츠';
// <> 같지 않다는 뜻
- 2025년 3월 8일 주문한 도서번호와 고개번호를 출력
select bookid, custid from orders where orderdate = '2025/03/08';
// 년도를 축약해서 조회해도 나오지만, 기본적으로 날짜는 문자열이 아니므로
// 정확하게, 연도를 지정해서 호출하는 방법을 더 추천.
select bookid, custid from orders where orderdate = '25/03/08';
select bookid, custid from orders where orderdate = to_date('2025/03/08','YYYY/MM/DD');
- '이상미디어' 에서 출간하는 도서중에 가격이 2000원 이상인 도서번호, 도서명을 출력
select bookid, bookname from book where price >= 2000 and publisher = '이상미디어';
// 가격과 출판사가 맞는지 확인차, 처음에는 모든 컬럼을 다 선택하여 조회함
select * from book where price >= 2000 and publisher = '이상미디어';
- '이상미디어' 나 '대한미디어' 에서 출간하는 도서의 도서번호, 도서명, 출판사명을 출력
// 비교&논리 연산자
select bookid, bookname, publisher from book
where publisher = '이상미디어' or publisher = '대한미디어';
// 집합 연산자
select bookid, bookname, publisher from book
where publisher in('이상미디어','대한미디어');
// 두개의 조건을 어떻게 묶어야 할지 몰라 모든 연산자 다 써봄
// 작성했을때 코드의 양도 그렇고 집합 연산자가 좀 더 간결한것 같음
- '이상미디어' 도 아니고 '대한미디어' 도 아닌 출판사의 도서번호, 도서명, 출판사명을 출력
// 비교&논리 연산자
select bookid, bookname, publisher from book
where publisher != '이상미디어' and publisher != '대한미디어';
select bookid, bookname, publisher from book
where publisher <> '이상미디어' and publisher <> '대한미디어';
// 집합 연산자
select bookid, bookname, publisher from book
where publisher not in('이상미디어','대한미디어');
// '!=' 연산자는 오라클에서 되긴하지만 비권장 되므로 '<>' 를 사용키로
// 역시.. 집합이 좀 더 간결!
- '이상미디어' 나 '대한미디어' 에서 출간하는 도서중에 가격이 20000원 이상인 도서번호, 도서명, 출판사명을 출력
// 비교&논리 연산자
select bookid, bookname, publisher from book
where publisher = '이상미디어' or publisher = '대한미디어' and price >= 20000;
// 집합 연산자
select bookid, bookname, publisher from book
where publisher in ('이상미디어','대한미디어') and price >= 20000;
- 가격이 10000원 이상 20000원 이하인 도서의 도서번호, 도서명, 출판사명, 가격을 검색
select bookid, bookname, publisher from book
where price >= 10000 and price <= 20000;
select bookid, bookname, publisher from book
where price between 10000 and 20000;
- '대한미디어'나 '이상미디어' 에서 출간하는 도서중에 가격이 10000원 이상 30000원 이하인 도서 정보를 출력
select * from book
where publisher in ('대한미디어','이상미디어')
and price between >= 10000 and price <= 30000;
select * from book
where publisher in ('대한미디어','이상미디어')
and price between 10000 and 30000;
- 전화번호가 null인 고객의 이름, 주소를 출력
select name, address from customer where phone is null;
- 전화번호가 null이 아닌 고객의 정보를 출력
select name, address from customer where phone is not null;
<< like 연산자 >>
문자열의 어떠한 패턴을 만족하는 데이터를 조회할 때 사용(특정한 문자열을 포함한 경우를 조회하고 싶은경우)
% : 모르는(아무글자) 0개 이상의 글자
_(언더바) : 모르는(아무글자) 1개의 글자
- 도서명에 '축구'를 포함하고 있는 모든 도서를 출력
- 도서명이 '축구'로 시작하는 도서의 정보를 출력
- 도서명이 '축구'로 끝나는 도서의 정보를 출력
// 해당 단어 모두 포함
select * from book where bookname like'%축구%';
// 해당 단어로 시작하는 경우
select * from book where bookname like'%축구';
// 해당 단어로 끝나는 경우
select * from book where bookname like'축구%';
- 성씨가 '김'씨인 모든 고객의 정보를 출력
// 해당 단어 모두 포함
select * from customer where bookname like'%김%';
// 해당 단어로 시작하는 경우
select * from customer where bookname like'%김';
// 해당 단어로 끝나는 경우
select * from customer where bookname like'김%';
- 성씨가 '김'씨이고 성을 포함하여 이름이 2글자인 고객의 정보를 출력
select * from customer where name like '김_';
- 성씨가 '김' 씨이고 성을 포함하여 이름이 3글자인 고객의 정보를 출력
select * from customer where name like '김__';
- 성씨가 '김'씨이고 '서울'에 거주하는 고객의 정보를 출력
select * from customer where name like '김%' and ADDRESS like '%서울%';
<< 데이터 조회하기 >>
레코드를 추가하면 순서는 유지 되지 않는다.
조회할 때에 순서를 정해 줄 수 있다.
select 컬럼이름(들)
from 테이블이름(들)
[ where 조건식 ]
[ order by 컬럼(들) ]
- 모든 도서의 정보를 가격순으로 정렬하여 출력
// order by 기본은 asc(오름차순)
select * from book order by price;
// asc 오름차순(낮은 -> 높은)
select * from book order by price asc;
// desc 내림차순(높은 -> 낮은)
select * from book order by price desc;
// order by 는 where 없이 사용할 수 있다.
// order by 는 조건이 여러개 붙는 경우여도 and 없이 사용할 수 있다.
// order by 중간에 띄어쓰기 꼭하기! 자꾸 붙여서 써서 관계 연산자 부적합 오류남..
- 축구관련 도서의 도서명, 가격, 출판사명을 가격이 높은순으로 출력
select bookname, price, publisher from book
where bookname like '%축구%' order by price;
// order by where 절이 끝난 다음에도 단독으로 사용한다
- 도서명의 두번째 글자가 '구' 이고 가격이 5000원과 50000원 사이인 도서의 도서명, 가격, 출판사명을 출력한다.
단, 출판사순으로 출력하고 출판사가 동일할 때에는 가격순으로 출력
// 같은 출판사 일경우 가격 내림차순(desc)
select * from book
where bookname like '_구%' and price between '5000'and'50000'
order by publisher, price desc;
// 같은 출판사 일경우 가격 오름차순(asc)
select * from book
where bookname like '_구%' and price between '5000'and'50000'
order by publisher, price asc;
- 전화번호가 '000-6000-0001' 인 고객의 이름과 주소를 출력
select name, address from customer where phone = '000-6000-0001';
- 고객번호가 1번인 고객의 주문내역을 출력
select * from orders where custid = 1;
- 도서번호 10번이거나 8번이거나 3번인 도서의 주문내역을 최근의 주문일 순서대로 출력
select * from orders
where bookid = 3 or bookid = 8 or bookid = 10
order by orderdate desc;
select * from orders
where bookid in(3,8,10)
order by orderdate desc;
// 한 컬럼에 비교할 조건이 여러개가 있는경우 in을 사용하는게 좀 더 깔끔한것 같음
- '야구' 관련 도서의 도서명, 가격, 출판사를 출력하시오.
단, 가격이 높은순으로 출력하고 가격이 동일할 때에는 도서명순으로 출력
select bookname, price, publisher from book
where bookname like "%야구%"
order by price desc, bookname;
- '2025/03/01' 과 '2025/03/08' 사이에 판매된 판매가격이 20000원 이상인 판매내역을 출력 하시오
단, 최근의 판매내역으부터 출력. 판매일이 동일할 때에는 판매가격이 높은순으로 출력
select * from orders
where orderdate between '2025/03/01' and '2025/03/08'
and saleprice >= 20000
order by orderdate desc, saleprice desc;
- 10번 도서를 구매한 고객의 이름을 출력
// 10번 도서를 구매한 자의 'custid' 정보
select custid from orders where bookid = 10;
// 서브쿼리 방식
select * from customer
where custid in(
select custid from orders where bookid = 10
);
// join방식(옛방식..이라내..)
select name from orders, customer
where orders.custid = customer.custid
and bookid = 10
// join방식(수정본)
select c.name
from customer c
join orders o on c.custid = o.custid
where o.bookid = 10;
// 위와 같이 조회하고자 하는 칼럼이나 조건식이 두개이상의 테이블에 있을 때에
// "join" 을 이용한다
✔ 조인식
두개의 테이블에 공동으로 들어가는 컬럼을 연결하는것
조인할 테이블의 입력 순서는 바뀌어도 문제가 없다.
# ANSI 88 스타일
select 컬럼이름(들)
from 테이블1, 테이블2
where 조인식;
# ANSI 92 스타일(권장)
select 컬럼이름(들)
from 테이블1
join 테이블2 on 조인식;
- 1번 고객이 구매한 도서명을 검색
// 1번 고객이 구매한 오더리스트
select * from orders where custid = 1;
// orders.custid = 1
select bookname from orders, book
where orders.bookid = book.bookid
and orders.custid = 1;
// custid = 1
select bookname from orders, book
where orders.bookid = book.bookid
and custid = 1;
// 권장 수정본
select bookname
from orders o
join book b
on o.bookid = b.bookid
where o.custid = 1;
<< 테이블 이름에 애칭 설정 >>
애칭을 줄때는 as는 생략 할 수 있다.
select * from book b
join orders o
on b.bookid = o.bookid
where custid = 1;
- '장미란' 고객이 주문한 내역에 대하여 고객번호, 주소, 전화, 도서번호, 도서명, 구매일, 구매가격을 출력
단, 최근의 구매일 부터 출력
select o.custid, c.address, c.phone, o.bookid, b.bookname, o.orderdate, o.saleprice
from orders o, customer c, book b
where o.custid = c.custid
and o.bookid = b.bookid
and c.name='장미란'
order by orderdate desc;
// 여러 테이블을 연결할때, 즉 join 하는 그 순간을 어떻게 해야하는지 까먹기 부지기수..ㅠㅠ
// 권장 수정본(join 사용)
select o.custid, c.address, c.phone, o.bookid, b.bookname, o.orderdate, o.saleprice
from orders o
join customer c
on o.custid = c.custid
join book b
on o.bookid = b.bookid
where c.name='장미란'
order by orderdate desc;
// bookid는 orders, book 모두 가지고 있기 때문에 정의를 확실히 하지 않으면
// 정의가 애매하다 라는 에러알람이 뜬다.
// join을 함에 있어서 두개의 테이블에 공통으로 들어가는 칼럼을 조회 할 때에는
// 특정 테이블 이름을 명시해야 한다.
- '이상미디어'나 '대한미디어'의 '축구'나 '야구' 관련 도서중에
가격이 10000원 이상인 도서를 구매한 고객의 고객번호, 고객이름, 도서번호, 도서명, 구매일을 출력
단, 최근의 구매일 순으로 출력하고 동일할 때에는 도서번호순으로 출력
select c.custid, name, b.bookid, bookname, orderdate
from orders o, customer c, book b
where o.custid = c.custid
and o.bookid = b.bookid
and publisher in ('이상미디어', '대한미디어')
and (bookname like '%축구%' or bookname like '%야구%')
and price >= 10000
order by orderdate desc, b.bookid desc;
// and (bookname like '%축구%' or bookname like '%야구%')
// 이 부분을 묶지 않고, and bookname like '%축구%' and bookname like '%야구%'
// 이렇게 적었더니, 값이 하나도 안나옴
// 즉, 축구, 야구 둘다 들어간 책을 찾게된거라 안나오는거임..!
- 종각에 근무하는 직원들의 사원번호, 부서번호, 부서명, 직책, 입사일, 급여를 출력
단, 입사일 순으로 출력하고 동일할 때에는 사원번호 순으로 출력
select eno, e.dno, ename, job, hiredate, salary
from emp e, dept d
where e.dno = d.dno
and dloc = '종각'
order by hiredate, eno;
- 개발팀에 근무하는 직원들의 부서번호, 부서명, 부서위치, 사원번호, 사원명, 입사일을 출력
단, 부서번호 순으로 출력하되 동일할 때에는 사원번호 순으로 출력
select d.dno, dname, dloc, eno, ename, hiredate
from emp e, dept d
where e.dno = d.dno
and dname like '%개발%'
order by d.dno, eno;
- 직책이 '사원' 이거나 '대리'인 모든 직원들의 부서번호, 부서명, 직책, 입사일, 급여를 출력
단, 급여가 높은 순으로 출력하고 동일할 때에는 입사일 순으로 출력
// like
select d.dno, dname, job, hiredate, salary
from emp e, dept d
where e.dno = d.dno
and (job like '%사원%' or job like '%대리%')
order by salary desc, hiredate;
// in
select e.dno,dname,job,hiredate,salary
from dept d, emp e
where e.dno = d.dno
and job in ('사원', '대리')
order by salary desc, hiredate;
// job 컬럼은 롤이 정해져 있으므로, like로 찾는것보단 in으로 찾는게 좀 더 나을것 같다.
- '2023/01/01' 이전에 입사한 직책이 '사원' 인 모든 직원들의 부서번호, 부서명, 사원명, 사원번호, 입사일 을 출력
입사일 순으로 출력하되 동일할 때에는 사원번호 순으로 출력
select e.dno, dname, ename, eno, hiredate
from emp e, dept d
where e.dno = d.dno
and hiredate <= '2023/01/01'
and job = '사원'
order by hiredate, eno;
// hiredate to_date
select e.dno, dname, ename, eno, hiredate
from emp e, dept d
where e.dno = d.dno
and hiredate <= to_date('2023/01/01', 'YYYY/MM/DD')
and job = '사원'
order by hiredate, eno;
<< 집계함수 >>
레코드를 조회활 때에 레코드의 수, 합, 평균등을 구하고자 할 때 사용하는 함수를 말한다.
레코드의 수 count(칼럼이름)
총합 sum(칼럼이름)
평균 avg(칼럼이름)
최고값 max(칼럼이름)
최저값 min(칼럼이름)
※ 집계함수의 결과는 1건 이기 때문에 작성에 유의한다.
- 모든 도서 의 가격을 출력
select price from book;
- 모든 도서의 총합을 출력
// 총 가격
select sum(price) from book;
- 모든 도서의 평균가격을 조회
select avg(price) from book;
- 도서의 최고가격을 조회
select max(price) from book;
- 도서의 최저가격을 조회
select min(price) from book;
- 모든 도서의 수를 조회
select count(*) from book;
// count 시 어떤 칼럼이 괄호 안에 들어가는게 어떤 것이냐에 따라 결과가 달라 질 수 있다.
// count(칼럼이름) : 컬럼의 값이 null이 아닌 레코드의 수
// count(*) : 모든 레코드의 수
- '이상미디어' 에서 출간하는 도서의 평균가격을 출력
select avg(price) from book where publisher = '이상미디어';
- '박지성' 고객이 주문한 총 건수와 총주문금액을 출력
select count(b.bookid), sum(saleprice)
from book b, customer c, orders o
where o.bookid = b.bookid and c.custid = o.custid
and c.name= '박지성';
// 이렇게 작성해도 결과는 동일
select sum(saleprice), count(*)
from customer c, orders o
where c.custid = o.custid
and name='박지성';
// 집합 함수에도 애칭을 줄 수 있다.
select sum(saleprice) sum, count(*) cnt
from customer c, orders o
where c.custid = o.custid
and name='박지성';
- '축구'관련 도서의 총 도서의 수와 평균가격, 최고가격, 최저가격을 출력
select count(*) cnt, avg(price), max(price), min(price)
from book
where bookname like '%축구%';
- '개발1팀' 에 근무하는 총직원수와 평균급여를 출력
select count(*) cnt, avg(salary)
from emp e
join dept d
on e.dno = d.dno
where dname='개발1팀';
- '2025/03/01' 과 '2025/03/19' 사이에 주문한 건수와 총 주문금액을 출력
select count(*) cnt, avg(saleprice)
from orders o
where orderdate between '2025/03/01' and '2025/03/19';
// 이렇게 하면 0으로 나옴..
// orderdate의 포맷이 DATE 타입이기 때문에, 포맷이 불일치 하여 0 을 출력한다.
select count(orderid), sum(saleprice)
from orders o, book b
where o.bookid = b.bookid
and orderdate in ('2025/03/01','2025/03/19');
- 개발팀의 근무하는 직원들의 총급여를 출력
select sum(salary)
from dept d
join emp e
on d.dno = e.dno
where dname like '%개발%';
<< group by의 사용 >>
모든 도서의 평균금액은 한건만 나온다.
만약, 출판사 별로 평균 금액을 출력하고 싶을경우에 group by 를 사용하면
출판사의 수 만큼 건수가 나온다.
group by 절에 나타난 컬럼만이 select 절에 올 수 있다.
집계 함수에서는 오직 한건만 나타낼 수 있으나,
group by 절에 사용된 컬럼만 오로지 집계함수에서 컬럼이름으로 같이 명시 할 수 있다.
이런식으로 !
select publisher, avg(price)
from book
group by publisher;
- 직책별로 직원의 수, 평균 급여액을 출력 하되, 직원수가 높은순으로 출력
select job, count(job), avg(salary) from emp
group by job
order by count(job) desc;
// order by 를 항상 마지막에 작성한다.
// order by 는 최종결과에 대해 정렬을 수행하는 절이므로 항상 뒤에 있어야한다.
// 만약 그렇지 않으면 에러가 난다.
// 'ORA-00933: SQL 명령어가 올바르게 종료되지 않았습니다'
- 부서명별로 직원의 수, 총급여액, 평균급여액 을 출력 하되, 총급여액이 높은 순으로 출력
select dname, count(*) 부서별직원수, count(salary) 총급여액, avg(salary) 평균급여액
from dept d
join emp e
on d.dno = e.dno
group by dname
order by count(salary);
- 개발팀에 근무하는 직원들의 직책별 평균 급여액을 출력 하되, 평균급여액이 높은순으로 출력
select avg(salary)
from dept d
join emp e
on d.dno = e.dno
where dname like '%개발%'
group by dname
order by avg(salary) desc;
// group by 절에 like 조건식을 넣으면 에러가 난다..
// group by 절에는 표현식은 가능하다.
select dname, avg(salary)
from dept d
join emp e
on d.dno = e.dno
group by dname like '%개발%'
order by avg(salary) desc;
- '대한미디어'나 '이상미디어'의 도서명별로 판매건수를 출력 하되, 판매건수가 높은순으로 출력
select bookname, count(bookname)
from book b
join orders o
on b.bookid = o.bookid
where publisher in ('대한미디어' , '이상미디어')
group by bookname
order by count(bookname) desc;
// 출판사도 같이 출력할경우, 2개의 컬럼명을 넣어 주면 된다!
select publisher, bookname, count(bookname)
from book b
join orders o
on b.bookid = o.bookid
where publisher in ('대한미디어' , '이상미디어')
group by publisher, bookname
order by count(bookname) desc;
- '대한미디어'나 '이상미디어', 'Person' 의 도서명별로 판매건수를 출력 하되, 판매건수가 높은순으로 출력
select bookname, count(bookname)
from book b
join orders o
on b.bookid = o.bookid
where publisher in ('대한미디어' , '이상미디어', 'Pearson')
group by bookname
order by count(bookname) desc
;
<< having의 사용 >>
group by 절에 나타난 결과에 대하여 조건식을 주고자 할 때에 사용한다.
- '대한미디어'나 '이상미디어','Person'의 도서명별로 판매건수를 출력
단, 판매건수가 2건 이상인것만 출력하고 판매건수가 높은순으로 출력
- 고객명별로 구매건수와 총구매금액을 출력
단, 총구매금액이 10000원 이상인 고객만 출력하고 총구매금액이 높은 순으로 출력
- '판교' 나 '종각' 에 근무하는 직원들의 부서명별로 근무하는 직원의 수, 총급여액, 평균급여액을 출력
단, 직원의 수가 2명이상인 부서만 출력하고 총 급여액이 높은 순으로 출력
총급여액이 동일할 때에는 평균급여액 순으로 출력
- '축구' 관련 도서에 대하여 도서명별로 판매건수를 출력
단, 판매건수가 높은순으로 출력
- '축구' 관련 도서에 대하여 도서명별로 판매건수를 출력
단, 판매건수가 2건이상인것만 출력하고 판매건수가 높은순으로 출력.
<< self 조인 >>
- 모든 직원의 이름과 관리자 이름을 출력
- '개발'팀에 근무하는 모든 직원들의 사원번호, 사원명, 관리자명, 부서번호, 부서명을 출력
- '판교' 나 '종각'에 근무하고 직책이 '사원'이거나 '대리'인 직원들 중에
입사일이 관리자보다 빠르거나 급여가 관리자보다 많은 직원들의 사원번호, 사원명, 관리자명, 입사일,
관리자의 입사일, 급여, 관리자의 급여를 출력
단, 입사일 순으로 출력하고 동일할 때에는 급여가 높은순으로 출력
- '홍길동' 의 부하직원들의 사원번호, 사원명, 입사일, 급여를 출력
단, 입사일 순으로 출력
- 고객번호별로 주문한 건수를 출력
- 고객이름별로 주문한 건수를 출력
<< outer join의 형식 >>
- 고객이름별로 주문건수를 출력
단, 주문이 없는 고객이름도 출력
- 관리자이름별로 부하직원의 수를 출력
단, 부하직원이 없는 직원의 이름도 출력
- 출판사별로 총주문건수, 총주문금액을 출력
단, 주문내역이 없는 출판사도 출력