Chapter3
연습문제 01
book table
order table
customer table
--1.도서번호가 1인 도서의 이름
SELECT bookname
FROM book
WHERE bookid=1;
--2.가격이 20,000원 이상인 도서의 이름
SELECT bookname
FROM book
WHERE price >=20000;
--3.박지성의 총 구매액
SELECT name, SUM(saleprice)AS 총구매액
FROM customer,orders
WHERE customer.custid=orders.custid AND customer.name LIKE'%박지성%'
GROUP BY customer.name;
--4.박지성이 구매한 도서의 수
SELECT name, COUNT(*) AS 도서의수
FROM customer,orders
WHERE customer.custid=orders.custid AND customer.name LIKE'%박지성%'
GROUP BY customer.name;
--5.박지성이 구매한 도서의 출판사 수
SELECT customer.name, COUNT(*) AS 출판사의수
FROM customer,orders
WHERE customer.custid=orders.custid AND customer.name LIKE'%박지성%'
GROUP BY customer.name;
--6.박지성이 구매한 도서의 이름 ,가격 , 정가와 판매가격의 차이
SELECT customer.name,book.bookname,book.price,orders.saleprice ,SUM(book.price-orders.saleprice) AS 차이
FROM customer,orders,book
WHERE customer.custid=orders.custid AND customer.name LIKE'%박지성%' AND orders.bookid=book.bookid
GROUP BY book.publisher, customer.name, book.bookname, book.price, orders.saleprice;
--7.박지성이 구매하지 않은 도서의 이름
SELECT DISTINCT book.bookname
FROM customer,orders,book
WHERE customer.custid=orders.custid AND customer.custid !=1 AND orders.bookid=book.bookid
GROUP BY customer.name, book.bookname;
연습문제 02
--1.마당서점 도서의 총수
SELECT COUNT(*) AS 총수
FROM book;
--2.마당서점에 도서를 출고하는 출판사의 총수
SELECT COUNT(*) AS 출판사의총수
FROM book,orders
WHERE book.bookid=orders.bookid;
--3.모든 고객의 이름 ,주소
SELECT name,address
FROM customer;
--4.2020년 7월 4일 -7월 7일 사이에 주문받은 도서의 주문번호
SELECT custid
FROM orders
WHERE orderdate BETWEEN '2020-07-04' AND '2020-07-07';
--5.2020년 7월 4일 -7월 7일 사이에 주문받지 않은 도서의 주문번호
SELECT custid
FROM orders
WHERE not orderdate BETWEEN '2020-07-04' AND '2020-07-07';
--6.성이 김씨인 고객의 이름과 주소
SELECT name,address
FROM customer
WHERE name LIKE'%김%';
--7.성이 김 씨이고 아 로 끝나는 고객의 이름과 주소
SELECT name,address
FROM customer
WHERE name LIKE'김%아';
--8.주문하지 않은 고객의 이름(부속질의 사용)
SELECT name
FROM customer
WHERE name NOT IN(
SELECT customer.name
FROM customer,orders
WHERE orders.custid=customer.custid);
--9.주문금액의 총액과 주문의 평균금액
SELECT SUM(price) AS 총액, AVG(PRICE) AS 평균
FROM ORDERS,BOOK
WHERE BOOK.BOOKID = ORDERS.BOOKID;
SELECT sum(orders.SALEPRICE) AS 총액, avg(orders.SALEPRICE)AS 평균
FROM ORDERS;
--10.고객의 이름과 고객별 구매액
SELECT customer.name, sum(orders.SALEPRICE)
FROM ORDERS,customer
WHERE orders.custid=customer.custid
GROUP BY customer.name
ORDER BY name;
--11.고객의 이름과 고객이 구매한 도서목록
SELECT customer.name, book.bookname
FROM ORDERS,customer,book
WHERE orders.custid=customer.custid AND book.bookid=orders.bookid
GROUP BY customer.name, book.bookname
ORDER BY name;
--12.도서의 가격 과 판매가격의 차이가 가장 많은 주문
SELECT *
FROM book, ORDERS
WHERE book.BOOKID = orders.BOOKID
AND book.PRICE-orders.SALEPRICE = (
SELECT max(book.PRICE-orders.SALEPRICE)
FROM book, ORDERS
WHERE book.BOOKID = orders.BOOKID
);
--13 도서의 판매액 평균보다 자신의 구매액 평균이 더 높은 고객 이름
SELECT customer.NAME
FROM CUSTOMER, ORDERS
WHERE CUSTOMER.CUSTID = ORDERS.CUSTID
GROUP BY customer.NAME
HAVING avg(orders.SALEPRICE) > (
SELECT avg(orders.SALEPRICE)
FROM ORDERS
);
--3-1 박지성이 구매한 도서의 출판사와 같은 출판사에서 도서를 구매한 고객의 이름
SELECT DISTINCT name
FROM customer,book,orders
WHERE name NOT LIKE '박지성' AND book.bookid=orders.bookid AND customer.custid=orders.custid AND book.publisher IN
(
SELECT DISTINCT publisher
FROM book,orders,customer
WHERE customer.custid=orders.custid AND book.bookid=orders.bookid AND customer.name ='박지성');
--3-2 두개이상의 서로다른 출판사에서 도서를 구매한 고객의 이름 (박지성,장미란,김연아 세명 나와야함 )
SELECT DISTINCT name,book.publisher,COUNT(*)
FROM customer,book,orders
WHERE customer.custid=orders.custid AND book.bookid=orders.bookid
group by book.publisher, name;
select NAME
from CUSTOMER s1
where 2>= (select count(distinct publisher) from customer t1, orders t2, book t3
where t1.custid = t2.custid
and t2.bookid = t3.bookid
and t1.name = s1.name);
SELECT DISTINCT CUSTOMER.NAME
FROM CUSTOMER, ORDERS, BOOK
WHERE CUSTOMER.CUSTID =ORDERS.CUSTID
AND BOOK.BOOKID = ORDERS.BOOKID
GROUP BY CUSTOMER.NAME
HAVING COUNT(DISTINCT PUBLISHER) >= 2;
--3-3전체 고객의 30%이상이 구매한 도서
SELECT * FROM book;
--04-01새로운 도서삽입 ('스포츠세계',대한미디어,10000원)이 입고 되었다. -->(BOOKID (PRIMARY KEY)값 부족으로 생성안됨)
--INSERT INTO BOOK(BOOKID,BOOKNAME,PRICE,PUBLISHER)VALUES(11,'스포츠세계',10000,'대한미디어')
--04-02 삼성당에서 출판한 도서를 삭제하시오
DELETE FROM BOOK
WHERE book.publisher='삼성당';
--0403이상미디어에서 출판한 도서를 삭제하세요
--삭제 안됨 :무결성 제약조건이 위배 -자식레코드 발견
DELETE FROM BOOK
WHERE book.publisher='이상미디어';
--04-04 출판사 대한미디어를 대한출판사로 이름을 바꾸시오
UPDATE BOOK
SET publisher='대한출판사'
WHERE publisher='대한미디어';
--0405
CREATE TABLE Bookcompany(
name VARCHAR(20) PRIMARY KEY,
address VARCHAR(20),
begin DATE);
--0406
ALTER TABLE Bookcompany ADD webaddress VARCHAR(30);
--4-7
INSERT INTO Bookcompany(name,address,begin,webaddress)
VALUES('한빛아카데미','서울시 AKVHRN','1993-01-01','http://hanbit.co.kr');
SELECT * FROM bookcompany;
--5-01
SELECT *
FROM Customer c1
WHERE NOT EXISTS(
SELECT *
FROM ORDERS c2
WHERE c1.custid=c2.custid);
--05-02
SELECT *
FROM Customer c1
WHERE EXISTS(
SELECT *
FROM ORDERS c2
WHERE c1.custid=c2.custid);
--06 테이블 생성
CREATE TABLE 과제(
학년 NUMBER,
동아리 VARCHAR(10),
학생수 NUMBER,
과제수 NUMBER);
SELECT *
FROM 과제;
--06학생이 10명이상 가입한 동아리와 그 동아리에 총 과제수 (A:45/B:60)
SELECT 동아리, SUM(과제수)
FROM 과제
WHERE 동아리 IN
(SELECT 동아리
FROM 과제
WHERE 학생수>10)
group by 동아리;
--07
SELECT DISTINCT 출판사명
FROM 도서
WHERE 10 < (SELECT COUNT(도서번호)
FROM 도서
WHERE 발행연도>2000);
--9 직원이 6명 이상인 부서의 부서명과, 그 부서 소속 직원중 급여가 40.000원 이상인 직원의 수를 검색
SELECT 부서명,COUNT(주민번호) AS 직원수
FROM 직원,부서
WHERE 직원.소속부서번호=부서.부서번호
AND 5< (SELECT COUNT(주민번호)
FROM 직원,부서
WHERE 직원.소속부서번호=부서.부서번호)
AND 급여>40000
GROUP BY 부서명;
--11.극장
CREATE TABLE 극장(
극장번호 NUMBER,
극장이름 VARCHAR(10),
위치 VARCHAR(10));
CREATE TABLE 상영관(
극장번호 NUMBER,
상영관번호 NUMBER,
영화제목 VARCHAR(10),
가격 NUMBER,
좌석수 NUMBER);
CREATE TABLE 예약(
극장번호 NUMBER,
상영관번호 NUMBER,
고객번호 NUMBER,
좌석번호 NUMBER,
날짜 DATE);
CREATE TABLE 고객(
고객번호 NUMBER,
이름 VARCHAR(10),
주소 VARCHAR(10));
--1.모든 극장의 이름과 위치를 보이시오
SELECT 극장이름,위치
FROM 극장;
--2.잠실에 있는 극장을 보이시오
SELECT 극장이름
FROM 극장
WHERE 위치='잠실';
--3.잠실에 사는 고객의 이름을 오름차순으로 보이시오
SELECT 이름
FROM 고객
WHERE 주소= '잠실';
ORDER BY 이름 DESC;
--4.가격이 8.000원이하인 영화의 극방번호,상영관 번호,영화제목을 보이시오
SELECT 극장번호,상영관번호,영화제목
FROM 영화
WHERE 가격<=8000;
--5.극장위치와 고객의 주소가 같은 고객을 보이시오
SELECT 고객
FROM 고객,극장
WHERE 고객.주소 = 극장.위치;
--1. 극장의 수는 몇개인가
SELECT COUNT(*)
FROM 극장;
--2.상영되는 영화의 평균 가격은 얼마인가
SELECT AVG(가격)
FROM 상영관;
--3.2020년 9월1일에 영화를 관람한 고객의 수는 얼마인가
SELECT COUNT(고객번호)
FROM 예약
WHERE 날짜='20-09-01';
SELECT COUNT(이름)
FROM 예약,고객
WHERE 예약.고객번호=고객.고객번호 AND 날짜='20-09-01';
SELECT *
FROM 예약;
--1.'대한'극장에서 상영된 영화제목을 고르시오
SELECT 상영관.영화제목
FROM 상영관 , 극장
WHERE 극장.극장번호 = 상영관.극장번호 AND 극장이름 ='대한';
--2.대한 극장에서 영화를 본 고객의 이름을 보이시오
SELECT 고객.이름
FROM 극장,예약,고객
WHERE 극장.극장번호=예약.극장번호 AND 예약.고객번호=고객.고객번호 AND 극장이름 ='대한';
--3.대한 극장의 전체 수입을 보이시오
SELECT SUM(가격)
FROM 상영관,극장,예약
WHERE 극장.극장번호 = 상영관.극장번호 AND 예약.상영관번호=상영관.상영관번호 AND 극장이름='대한';
--1.극장별 상영관수를 보이시오
SELECT 극장이름,COUNT(상영관번호)
FROM 극장,상영관
WHERE 극장.극장번호 = 상영관.극장번호
GROUP BY 극장이름;
--2.'잠실'에있는 극장의 상영관을 보이시오
SELECT 상영관번호
FROM 극장 , 상영관
WHERE 극장.극장번호 = 상영관.극장번호 AND 극장.위치='잠실';
--3.202-09-01일의 극장별 평균 관람 고객수를 보이시오
SELECT AVG(COUNT(고객번호))
FROM 예약
WHERE 날짜='20-09-01'
GROUP BY 고객번호;
--4.202-09-01일의 가장 많은 고객이 관람한 영화를 보이시오
SELECT 영화제목
FROM 상영관, 예약
WHERE 상영관.극장번호=예약.극장번호 AND 상영관.상영관번호=예약.상영관번호
AND 날짜 LIKE '2014-09-01'
GROUP BY 예약.극장번호, 예약.상영관번호
HAVING COUNT(*) = (
SELECT MAX(*)FROM 상영관, 예약
WHERE 상영관.극장번호=예약.극장번호
AND 상영관.상영관번호=예약.상영관번호
AND 날짜 LIKE '20-09-01'
GROUP BY 예약.극장번호, 예약.상영관번호);
--12-1 테이블을 생성 값추가
CREATE TABLE Salesperson(
name VARCHAR2(20),
age NUMBER(10),
salary VARCHAR(20),
PRIMARY KEY (name));
DROP TABLE Salesperson;
CREATE TABLE Customers(
name VARCHAR2(20),
city VARCHAR2(20),
industrytype VARCHAR(20),
PRIMARY KEY (name));
CREATE TABLE Orders1(
numbers NUMBER(10),
custname VARCHAR2(20) ,
salesperson VARCHAR2(20),
amount VARCHAR(20),
FOREIGN KEY (custname) REFERENCES Customers(name),
FOREIGN KEY (salesperson) REFERENCES Salesperson(name)
);
SELECT * FROM Orders1;
SELECT * FROM Salesperson;
SELECT * FROM CUSTOMERS;
INSERT INTO Customers(name,city,industrytype) VALUES('길동','화성','학생');
insert into salesperson(name, age, salary) values('Tom', '26', 10000);
insert into salesperson(name, age, salary) values('Roy', '32', 15000);
insert into salesperson(name, age, salary) values('Sally', '24', 9000);
insert into salesperson(name, age, salary) values('Nancy', '29', 7000);
insert into salesperson(name, age, salary) values('Clara', '40', 8500);
insert into customers(name, city, industrytype) values('Mary', 'LA', '개발자');
insert into customers(name, city, industrytype) values('Carrie', 'LA', '요리사');
insert into customers(name, city, industrytype) values('IU', 'KR', '가수');
insert into customers(name, city, industrytype) values('Diane', 'LA', '개발자');
insert into customers(name, city, industrytype) values('Grace', 'DE', '요리사');
insert into customers(name, city, industrytype) values('Nancy', 'MX', '교육자');
insert into customers(name, city, industrytype) values('Frank', 'US', '교육자');
insert into orders1(numbers, custname, salesperson, amount) values('1', 'Mary','Tom', '1000');
insert into orders1(numbers, custname, salesperson, amount) values('2', 'IU','Sally', '2000');
insert into orders1(numbers, custname, salesperson, amount) values('3', 'Diane','Nancy', '3000');
insert into orders1(numbers, custname, salesperson, amount) values('4', 'Grace','Roy', '4000');
insert into orders1(numbers, custname, salesperson, amount) values('5', 'Nancy','Tom', '5000');
insert into orders1(numbers, custname, salesperson, amount) values('6', 'Diane','Sally', '6000');
insert into orders1(numbers, custname, salesperson, amount) values('7', 'Grace','Roy', '7000');
insert into orders1(numbers, custname, salesperson, amount) values('8', 'Nancy','Nancy', '8000');
--12-2 모든 판매원의 이름과 급여를 보이시오. 단 중복행은 제거한다.
SELECT DISTINCT name,salary
FROM Salesperson;
--12-3 나이가 30세 미만인 판매원의 이름
SELECT name
FROM Salesperson
WHERE AGE<30;
--12-4 'S'로 끝나는 도시에 사는 고객의 이름
SELECT NAME
FROM CUSTOMERS
WHERE CITY LIKE '_S%';
--12-5 주문을 한 고객의수를 (서로다른고객)를 구하시오
SELECT COUNT(DISTINCT CUSTNAME)
FROM ORDERS1;
--12-6판매원 각각에 대하여 주문의 수를 계산하시오
SELECT Salesperson.NAME,COUNT(NUMBERS)
FROM SALESPERSON,ORDERS1
WHERE SALESPERSON.NAME=ORDERS1.salesperson
GROUP BY Salesperson.name;
--12-7'la'에 사는 고객으로 부터 주문을 받은 판매원의 이름과 나이를 보이시오(부속질의)
select name, age
from salesperson
where name in (
select salesperson
from orders1
where custname in (
select name
from customerS
where city like 'LA')
);
--12-8'la'에 사는 고객으로 부터 주문을 받은 판매원의 이름과 나이를 보이시오(조인)
SELECT Salesperson.name,Salesperson.age
FROM Salesperson,Orders1,CUSTOMERS
WHERE SALESPERSON.NAME=ORDERS1.salesperson
AND ORDERS1.CUSTNAME = CUSTOMERS.NAME
AND CITY='LA';
--12-9 두번이상 주문을 받은 판매원의 이름
SELECT name
FROM Salesperson s1
WHERE 2<= (
SELECT COUNT(salesperson)
FROM ORDERS1 o1
WHERE s1.name= o1.salesperson
);
--12-10 판매원 tom의 봉급을 45000원으로 변경하는 sql
update salesperson
set salary = 450000
where name = 'TOM';
###13 번
-- 테이블 생성
create table Employee(
empno INT primary key,
name varchar(10),
phoneno varchar(13),
address varchar(20),
sex varchar(20),
position varchar(20),
deptno int not null
);
create table Department
(
deptno int primary key,
deptname varchar(20),
manager varchar(20)
);
create table Project
(
projno int primary key,
projname varchar(20),
deptno int
);
create table Works
(
empno int not null,
projno int not null,
hours_worked int
);
insert into Department values(1, '개발1팀', '재호');
insert into Department values(2, '개발2팀', '하윤');
insert into Department values(3, '서비스1팀', '진우');
insert into Department values(4, '서비스2팀', '하윤');
insert into Project values(1, 'Admin Page Project', 1);
insert into Project values(2, 'Client Page Project', 2);
insert into Project values(3, 'Ad TV', 3);
insert into Project values(4, 'Ad Web', 4);
insert into Employee values(1, '민준', '010-1234-1230', '서울', '남자', 'IT', 1);
insert into Employee values(2, '서준', '010-1234-1231', '부산', '남자', 'IT', 2);
insert into Employee values(3, '예준', '010-1234-1232', '울산', '여자', 'service', 3);
insert into Employee values(4, '도윤', '010-1234-1233', '김포', '남자', 'IT', 1);
insert into Employee values(5, '시우', '010-1234-1234', '서울', '여자', 'service', 4);
insert into Employee values(6, '주원', '010-1234-1235', '부산', '여자', 'IT', 2);
insert into Employee values(7, '하준', '010-1234-1236', '김포', '남자', 'service', 4);
insert into Employee values(8, '지호', '010-1234-1237', '울산', '여자', 'service', 3);
insert into Employee values(9, '지후', '010-1234-1238', '서울', '남자', 'IT', 1);
insert into Employee values(10, '준서', '010-1234-1239', '서울', '여자', 'IT', 2);
insert into Works values(1, 1, 10);
insert into Works values(2, 2, 2);
insert into Works values(3, 3, 40);
insert into Works values(4, 1, 20);
insert into Works values(5, 4, 11);
insert into Works values(6, 2, 5);
insert into Works values(7, 4, 9);
insert into Works values(8, 3, 4);
insert into Works values(9, 1, 4);
insert into Works values(10, 2, 12);
select * from department;
select * from employee;
select * from project;
select * from works;
--13-2모든 사원의 이름
SELECT NAME
FROM employee;
--13-3여자 사원의 이름
SELECT NAME
FROM employee
WHERE SEX='여자';
--13-4팀장의 이름
SELECT MANAGER
FROM department;
--13-5IT부서에서 일하는 사원의 이름과 주소를 보이시오
SELECT NAME ,ADDRESS
FROM employee e
WHERE e.position ='IT';
--13-6'홍길동'팀장 부서에서 일하는 사원의 수를 보이시오
SELECT COUNT(name)
FROM EMPLOYEE e,DEPARTMENT d
WHERE e.deptno=d.deptno and manager='홍길동';
--13-7사원들이 일한 시간수를 부서별, 사원 이름별 오름차순으로 보이시오
SELECT hours_worked, name, deptname
FROM EMPLOYEE e,Works w ,Department d
WHERE e.empno= w.empno and e.deptno=d.deptno
group by hours_worked, name,deptname
order by w.hours_worked asc;
--13-8 2명이상의 사원이 참여한 프로젝트의 번호,이름 ,사원의 수를 보이시오
select projno,projname,COUNT(e1.empno) AS 사원수
from project p,employee e1
where p.deptno=e1.deptno and 2>(select Count(empno)
from employee e2,department d
where e2.deptno=d.deptno and e1.empno=e2.empno)
group by projno, projname;
--13-9 3명이상의 사원이 있는 부서의 사원 이름
select e1.name
from employee e1 ,department d1
where e1.deptno=d1.deptno and 3<=(select count(name)
from department d2,employee e2
where d2.deptno =e2.deptno and d1.deptno=d2.deptno);
select * from dept;
select * from emp;
--14-1 사원의 이름과 업무를 출력하시오
select ename,job
from emp;
--14-2 30번 부서에 근무 하는 모든 사원의 이름과 급여를 출력하시오
select ename,sal
from emp
where deptno='30';
--14-3 사원번호와 이름, 현재 급여, 증가된 급여분(증가액),10%인상된 급여(인상된급여) 를 사원번호 순으로 출력
select empno,ename,sal, comm as 증가액, (sal*0.1)+sal as 인상된급여
from emp;
--14-4 S로 시작하는 모든 사원과 부서번호
select ename,deptno
from emp
where ename LIKE 'S%';
--14-5 모든 사원의 최대 및 최소급여, 합계 및 평균 급여를 구하시오!
SELECT MIN(SAL),MAX(SAL),SUM(SAL),ROUND (AVG(SAL))
FROM EMP;
--14-6 업무이름 과 업무별로 동일한 업무를 하는 사원의 수를 출력하시오
SELECT job,count(empno)
FROM EMP e1
WHERE job in (select job
from EMP e2
where e1.job=e2.job)
group by job;
--14-7사원의 최대 급여와 최소급여의 차액
SELECT MAX(SAL)-MIN(SAL) AS 차액
FROM EMP;
--14-8 30번 부서의 사원수와 사원들 급여의 합계와 평균
select COUNT(empno) AS 사원수,SUM(SAL),AVG(SAL)
from emp
where deptno='30';
--14-9평균급여가 가장높은 부서의 번호를 출력하세요 (10 나와야함)
SELECT deptno
FROM Emp
GROUP BY deptno
HAVING AVG(sal) IN (SELECT MAX(AVG(sal)) FROM Emp GROUP BY deptno);
--14-10세일즈맨을 제외 하고 각 업무별 사원의 총급여가 3,000이상인 각 업무에 대해서 업무명과 각 업무별 평균급여 내림차순
SELECT JOB,AVG(SAL)AS 평균급여
FROM EMP e
WHERE JOB != 'SALESMAN' AND 3000<(SELECT SUM(SAL)
FROM EMP)
GROUP BY e.job
ORDER BY 평균급여 DESC;
--14-11 전체 사원 가운데 직속상관이 있는 사원의 수를 출력하시오
SELECT COUNT(empno)
FROM EMP
WHERE MGR IS NOT NULL;
--14-12 emp 테이블에서 이름 급여 커미션 금액 총액(sal*12+commp)을 구하여 총액이 많은 순으로 출력하시오
select ename,sal,comm,(sal*12+comm) as 총액
from emp
where comm is not null
order by 총액 desc;
--14-13 각 부서별로 같은 업무를 하는 사람의 인원수를 구하여 부서번호, 업무이름,인원수를 출력하시오
select deptno,job,count(empno)
from emp
group by deptno,job;
--14-14 사원이 1도 없는 부서의이름
select d.dname
from Emp e right join Dept d on e.deptno=d.deptno
WHERE e.empno is null;
--14-15 같은 업무를 하는 사람의 수가 4명이상인 업무와 인원수를
select deptno,job,count(empno)
from emp
group by deptno,job
having count(empno) >=4;
--14-16사원 번호가 7400이상 7600이하인 사원의 이름
select ename
from Emp
WHERE empno between 7400 and 7600;
--14-17사원의 이름과 사원의 부서이름을 출력 하세요
select ename,dname
from DEPT d,EMP e
WHERE d.deptno= e.deptno;
--14-18사원의 이름 과 팀장의이름을 출력하세요
select empno.ename,mgr.ename as 팀장
from EMP empno,EMP mgr
where empno.mgr= mgr.empno;
--14-19사원 scott
SELECT e.ENAME
FROM EMP e
WHERE e.SAL > (SELECT SAL
FROM EMP
WHERE ENAME = 'SCOTT');
