PPT 관계대수 연습문제 <- 패드에 손으로 작성
-- 마당서점에서 판매하는 도서 중 8,000원 이하인 도서를 검색하시오
-- 신간도서 안내를 위해 고객의 (이름, 주소, 핸드폰)이 적힌 카탈로그 주소록을 만드시오.
-- 릴레이션 이름은 각각 도서A, 도서B다. 마당서점의 도서를 하나의 릴레이션으로 보이시오
-- 마당서점의 두 지점에서 동일하게 보유하고 있는 도서 목록을 보이시오.
-- 마당서점 A,B 두 지점 중 지점 A에서만 보유하고 있는 도서 목록을 보이시오
-- 고객 릴레이션과 주문 릴레이션의 카티전 프로덕트를 구하시오
-- 고객과 고객의 주문 사항을 모두 보이시오.
-- 고객과 고객의 주문 사항을 모두 보여주되 같은 속성은 한 번만 표시하시오
-- 마당서점의 고객 중 주문 내역이 있는 고객의 고객 정보를 보이시오
-- 마당서점의 도서 중 가격이 8,000원 이하인 도서이름과 출판사를 보이시오
-- 마당서점의 박지성 고객의 거래 내역 중 주문번호, 이름, 가격을 보이시오 (카디션연산, 조인연산 두가지)
쪽지시험 문제
create database company;
use company;
show tables;
show triggers;
create table department(
deptno int not null primary key,
deptname varchar(20) unique,
floor int
);
create table employee(
empno int not null primary key,
empname varchar(20) not null,
title varchar(20) default '사원',
manager int,
salary int,
dno int,
foreign key(dno) references department(deptno) on update cascade on delete cascade
);
INSERT INTO DEPARTMENT VALUES(1, '영업', 8);
INSERT INTO DEPARTMENT VALUES(2, '기획', 10);
INSERT INTO DEPARTMENT VALUES(3, '개발', 9);
INSERT INTO DEPARTMENT VALUES(4, '총무', 7);
INSERT INTO EMPLOYEE VALUES(2106, '김창섭', '대리', 1003, 2500000, 2);
INSERT INTO EMPLOYEE VALUES(3426, '박영권', '과장', 4377, 3000000, 1);
INSERT INTO EMPLOYEE VALUES(3011, '이수민', '부장', 4377, 4000000, 3);
INSERT INTO EMPLOYEE VALUES(1003, '조민희', '과장', 4377, 3000000, 2);
INSERT INTO EMPLOYEE VALUES(3427, '최종철', '사원', 3011, 1500000, 3);
INSERT INTO EMPLOYEE VALUES(1365, '김상원', '사원', 3426, 1500000, 1);
INSERT INTO EMPLOYEE VALUES(4377, '이성래', '이사', NULL, 5000000, 2);
-- 1번 이씨 성 을 가진 사원들의 이름 ,직급, 소속부서명을 검색하라.
select empname, title, deptname from employee, department
where employee.dno = department.deptno and empname like '이%';
select empname, title, deptname from employee, department
where employee.dno = department.deptno and left(empname,1) = '이';
select e.empname, e.title, d.deptname from employee e, department d
where e.dno = d.deptno and substr(e.empname,1,1) = '이';
-- 2번 모든 사원에 대해 부서명 별로 급여의 평균과
-- 급여의 최대 값을 구하되 급여의 평균이 2500000 이상인 부서명, 급여의 평균, 급여의 최대값을 검색하시오.
select d.deptname, avg(e.salary), max(e.salary) from employee e, department d
where e.dno = d.deptno group by d.deptname having avg(e.salary) >= 2500000;
-- 3번 개발부서에 근무하는 사원의 이름과, 부서명, 봉급을 검색하라
select e.empname, d.deptname, e.salary from employee e, department d
where e.dno = d.deptno and d.deptname = '개발';
-- 4번 김창섭 또는 최종철이 속한 부서이고 부서명이 기획부서인 사원명, 부서명을 검색
select empname, deptname from employee e, department d
where e.dno = d.deptno and (empname = '김창섭' or empname = '최종철')
and d.deptname = '기획';
-- 5번 소속된 직원이 한명도 없는 부서의 번호를 검색하라
select d.deptno
from department d
where d.deptno not in (select e.dno from employee e);
-- 6번 최종철과 같은 직급을 가진 모든 사원의 이름과 직급을 검색하라
select e.empname, e.title from employee e
where e.title like (select emp.title from employee emp where emp.empname = '최종철');
-- 7번 모든 사원에 대해서 소속부서의 이름, 사원의 이름, 직급, 급여를 검색하라
-- 부서 이름에대해서 오름차순 부서이름이 같은경우에는 SALARY 에 대해서 내림차순으로 정렬하시오
select d.deptname, e.empname, e.title, e.salary
from employee e, department d where e.dno = d.deptno
order by d.deptname, e.salary desc;
-- 8번 자신이 속한 부서의 사원들의 평균 급여보다 많은 급여를 받는 사원들에 대해서 이름 부서번호 급여를 검색하라
-- 서브쿼리 외부의 릴레이션을 가져와서 참조하는것도 가능하다!
select empname, dno, salary from employee e1
where e1.salary > (select avg(salary) from employee e2 where e1.dno = e2.dno);
-- 9번 부서명별(부서번호 아님)로 급여의 평균을 구하는 뷰 를 작성하시오
-- ( 단 부서별 급여의 평균이 3000000원 이상인 뷰를 정의 하고 필드가 부서명 급여평균으로 정의한다.)
create view avgsalary(부서명,급여평균) as
select d.deptname, avg(e.salary) from department d, employee e
where d.deptno = e.dno
group by d.deptname
having avg(salary) >= 3000000;
-- 10번 사원의 이름과 그 사원의 직속상사 이름을 검색하는 뷰를 작성하시오
create view sawon(사원,직속상사) as
select e1.empname, e2.empname from employee e1, employee e2
where e1.manager = e2.empno;
-- 부서 테이블을 기준으로 소속된 사원이 없어도 사원의 이름은 모두 나오고 소속된 사원이 없는
-- 부서의 이름은 널로 채워지도록 뷰를 작성하고 한명도 소속되지 않은 부서의 이름을 검색
-- 사원이 한명도 소속 되지 않은 부서의 이름과 사원의 이름을 검색
select * from employee;
select * from department;
select empname, deptname from department d
left outer join employee e on e.dno = d.deptno;
create view v5 as
select deptname,empname from department d
left outer join employee e on d.deptno = e.dno;
select deptname, empname, ifnull(empname,'i am null이에요') from v5 where empname is null;
-- 사원의 성씨별로 몇명인지 개수를 출력
select count(*), substr(empname,1,1) from employee
group by substr(empname,1,1);위의 문제 중 8번 - 서브쿼리는 외부의 것도 참조 가능
9주차 문제 정리, 서브쿼리,exists, join
-- 책을 한번도 안산 고객을 가져오기
select * from customer where custid not in (select custid from orders);
select name from customer c
where custid not in (select custid from orders);
-- 책을 주문한 고객의 이름과 주문, 책을 출력
select c.name, o.orderid, b.bookname
from customer c, orders o, book b
where c.custid = o.custid and b.bookid = o.bookid;
-- 가격이 가장 비싼 책의 이름, 가격
select bookname, price from book
where price = (select max(price) from book);
-- 도서테이블의 책가격의 전체평균보다 책 가격이 비싼 책의 이름, 출판사, 가격을 검색
select bookname, publisher, price from book where price > (select avg(price) from book);
-- 책제목에 축구가 있는 출판사와 같은 출판사 책의 이름과 출판사 출력
select bookname, publisher from book
where publisher
in (select publisher from book where bookname like '%축구%');
-- 김연아가 주문한 책의 총합계를 부질의를 이용해서 검색
select sum(saleprice) from orders o
where custid = (select custid from customer where name = '김연아');
-- 고객번호가 3번인 고객과 같은 책을 주문한 고객의 이름, 책 제목
select name, bookname from customer c, orders o, book b
where c.custid = o.custid and b.bookid = o.bookid and
b.bookid in (select bookid from orders where custid = 3);
-- 책을 주문한 고객의 이름과 주소를 검색, exist, in 연산
select name, address from customer where custid in (select custid from orders);
select name, address from customer c
where exists (select * from orders o where c.custid = o.custid)
10주차 내장함수
-- 도서 테이블의 출판사, 책의 가격 평균을 백원단위 반올림
select publisher, round(avg(price),-2) from book group by publisher;
-- 고객 테이블 주소에서 대한민국을 조선으로 변경해서 검색
select replace(address,'대한민국','조선') from customer;
-- 같은 성을 가진 사람이 몇 명이나 되는지 성별, 인원수를 구하기
select left(name,1), count(*) from customer group by left(name,1);
-- 이름, 전화번호가 포함된 고객 구하기, 전화번호가 없으면 연락처 없음
select name,ifnull(phone,'연락처 없음') from customer;
-- 내장함수를 사용해서 도서 목록에 앞에 1번부터 번호를 붙여서 검색 <- 틀림
-- set @a := 0; 형태로 선언 (@a := @a + 1) 형태로 대입
set @temp := 0;
select (@temp := @temp + 1) '순번', bookid, bookname from book;
-- 내장함수를 사용해서 현재 시간 검색하기
select sysdate() from dual;
select date_format(sysdate(), '%Y-%m-%d-%H');
-- 내장함수를 사용해서 대한미디어에서 출판한 도서의 제목과 제목의 문자수, 바이트수를 검색하기
-- 틀림 char_length <- 글자수, length <- 바이트 수
select bookname, char_length(bookname), length(bookname) from book where publisher = '대한미디어';
-- 내장함수를 사용해서 주문 테이블의 고객명별 산 책의 평균을 구하되 백단위에서 잘라 주기
select name, truncate(avg(saleprice),-2)
from orders o, customer c
where o.custid = c.custid
group by name;
-- 고객이 산 책 제목과 가격을 검색하되 고객 이름 앞의 첫 글자만 출력하기
select bookname, saleprice, left(name,1) from book b, orders o, customer c
where c.custid = o.custid and b.bookid = o.bookid;
-- 주문 테이블의 주문한 고객명 주문 날짜보다 한 달 후의 날짜를 검색하기
select name, orderdate, adddate(orderdate, interval 1 month)
from orders o, customer c
where o.custid = c.custid;
10주차 뷰
뷰에서 이론으로 나올만한거
뷰에서 삽입, 삭제하면 원본도 변경된다
with check option을 걸지 않으면 뷰에는 삽입되지 않지만 원본 테이블에 삽입될 수 있다
뷰에 삽입되지 않은 컬럼은 원본 테이블에서는 널이 된다, 당연히 기본키는 널로 둘 수 없다
뷰 단점 1. 기본키나 널을 허용하지 않은 뷰는 삽입,변경이 불가능,
뷰 단점 2. 계산된 뷰나 집합함수로 계산된 뷰는 삽입,변경 안됨
뷰 단점 3. 조인된 테이블의 뷰는 삽입,변경 안됨
10주차 인덱스
create index ix_custname on customer(name);
drop index ix_custname on customer;
-- 인덱스를 사용했기에 fk가 아닌 name으로도 delete 연산이 가능
delete from customer where name = '길동';
인덱스를 만들어두면 원래는 기본키로만 삭제되던 데이터를 기본키가 아닌 컬럼으로도 삭제할 수 있다!
인덱스를 만들때는 어느 테이블 어느 속성에 적용할건지를 명시
create index ix_book on book(bookname);
인덱스를 삭제할 때도 대상 테이블을 on으로 명시해줘야한다!
drop index ix_book on book;
인덱스가 있을때는 아래 쿼리문으로 삭제가 되지만 인덱스가 없으면 아래 쿼리문으로 삭제가 되지 않는다
delete from book where bookname = '데이타베이스';
트리거
트리거 만들때 delimiter // <- 여백 있어야 한다, 없으면 에러발생
만드는 형태를 delimiter 까지 외우기
DB 설계
논리적 설계 규칙 5개
개체 -> 릴레이션
N : M -> 릴레이션으로 해소
1 : N -> FK
1 : 1 -> FK
다중 값을 가지는 속성은 릴레이션으로 변환, erd 상으로는 해당 속성 아래에 세부 속성을 트리형태로 작성
문제 모음
-- 1번 이씨 성 을 가진 사원들의 이름 ,직급, 소속부서명을 검색하라.
-- 2번 모든 사원에 대해 부서명 별로 급여의 평균과
-- 급여의 최대 값을 구하되 급여의 평균이 2500000 이상인 부서명, 급여의 평균, 급여의 최대값을 검색하시오.
-- 3번 개발부서에 근무하는 사원의 이름과, 부서명, 봉급을 검색하라
-- 4번 김창섭 또는 최종철이 속한 부서이고 부서명이 기획부서인 사원명, 부서명을 검색
-- 5번 소속된 직원이 한명도 없는 부서의 번호를 검색하라
-- 6번 최종철과 같은 직급을 가진 모든 사원의 이름과 직급을 검색하라
-- 7번 모든 사원에 대해서 소속부서의 이름, 사원의 이름, 직급, 급여를 검색하라
-- 부서 이름에대해서 오름차순 부서이름이 같은경우에는 SALARY 에 대해서 내림차순으로 정렬하시오
-- 8번 자신이 속한 부서의 사원들의 평균 급여보다 많은 급여를 받는 사원들에 대해서 이름 부서번호 급여를 검색하라
-- 서브쿼리 외부의 릴레이션을 가져와서 참조하는것도 가능하다!
-- 9번 부서명별(부서번호 아님)로 급여의 평균을 구하는 뷰 를 작성하시오
-- ( 단 부서별 급여의 평균이 3000000원 이상인 뷰를 정의 하고 필드가 부서명 급여평균으로 정의한다.)
-- 10번 사원의 이름과 그 사원의 직속상사 이름을 검색하는 뷰를 작성하시오
-- 11 부서 테이블을 기준으로 소속된 사원이 없어도 사원의 이름은 모두 나오고 소속된 사원이 없는
-- 부서의 이름은 널로 채워지도록 뷰를 작성하고 한명도 소속되지 않은 부서의 이름을 검색
-- 사원이 한명도 소속 되지 않은 부서의 이름과 사원의 이름을 검색
-- 12 사원의 성씨별로 몇명인지 개수를 출력
-- 9주차
-- 책을 한번도 안산 고객을 가져오기
-- 책을 주문한 고객의 이름과 주문, 책을 출력
-- 가격이 가장 비싼 책의 이름, 가격
-- 도서테이블의 책가격의 전체평균보다 책 가격이 비싼 책의 이름, 출판사, 가격을 검색
-- 책제목에 축구가 있는 출판사와 같은 출판사 책의 이름과 출판사 출력
-- 김연아가 주문한 책의 총합계를 부질의를 이용해서 검색
-- 고객번호가 3번인 고객과 같은 책을 주문한 고객의 이름, 책 제목
-- 책을 주문한 고객의 이름과 주소를 검색, exist, in 연산
-- 10주차
-- 도서 테이블의 출판사, 책의 가격 평균을 백원단위 반올림
-- 고객 테이블 주소에서 대한민국을 조선으로 변경해서 검색
-- 같은 성을 가진 사람이 몇 명이나 되는지 성별, 인원수를 구하기
-- 이름, 전화번호가 포함된 고객 구하기, 전화번호가 없으면 연락처 없음
-- 내장함수를 사용해서 도서 목록에 앞에 1번부터 번호를 붙여서 검색 <- 틀림
-- 내장함수를 사용해서 현재 시간 검색하기
-- 내장함수를 사용해서 대한미디어에서 출판한 도서의 제목과 제목의 문자수, 바이트수를 검색하기
-- 내장함수를 사용해서 주문 테이블의 고객명별 산 책의 평균을 구하되 백단위에서 잘라 주기
-- 고객이 산 책 제목과 가격을 검색하되 고객 이름 앞의 첫 글자만 출력하기
-- 주문 테이블의 주문한 고객명 주문 날짜보다 한 달 후의 날짜를 검색하기
-- customer 테이블 name 컬럼 대상으로 인덱스만들기
