오라클로 배우는 데이터베이스 개론과 실습 3장 연습문제

최우정·2022년 6월 30일
0
post-thumbnail

SELECT FROM BOOK;
SELECT
FROM CUSTOMER;
SELECT * FROM ORDERS;

-- 연습문제 1-1) 도서번호가 1인 도서의 이름
SELECT BOOKNAME FROM BOOK WHERE BOOKID = 1;

-- 연습문제 1-2) 가격이 20,000원 이상인 도서의 이름
SELECT BOOKNAME FROM BOOK WHERE PRICE >=20000;

-- 연습문제 1-3) 박지성의 총구매액
SELECT SUM(SALEPRICE) FROM ORDERS WHERE CUSTID = 1;

-- 연습문제 1-4) 박지성이 구매한 도서의 수
SELECT COUNT(SALEPRICE) FROM ORDERS WHERE CUSTID = 1;

-- 연습문제 1-5) 박지성이 구매한 도서의 출판사 수
SELECT COUNT(PUBLISHER)
FROM BOOK, ORDERS
WHERE BOOK.BOOKID = ORDERS.BOOKID
AND ORDERS.CUSTID = 1;

-- 연습문제 1-6) 박지성이 구매한 도서의 이름, 가격, 정가와 판매가격의 차이
SELECT BOOKNAME, PRICE, PRICE-SALEPRICE
FROM BOOK, ORDERS, CUSTOMER
WHERE BOOK.BOOKID = ORDERS.BOOKID
AND ORDERS.CUSTID = CUSTOMER.CUSTID
AND CUSTOMER.NAME = '박지성';

-- 연습문제 1-7) 박지성이 구매하지 않은 도서의 이름
SELECT DISTINCT BOOK.BOOKNAME
FROM BOOK, ORDERS, CUSTOMER
WHERE BOOK.BOOKID = ORDERS.BOOKID
AND CUSTOMER.CUSTID = ORDERS.CUSTID
AND CUSTOMER.NAME != '박지성';

-- 연습문제 2-1) 마당서점 도서의 총수
SELECT COUNT(*) FROM BOOK;

-- 연습문제 2-2) 마당서점에 도서를 출고하는 출판사의 총수
SELECT COUNT(DISTINCT PUBLISHER) FROM BOOK;

-- 연습문제 2-3) 모든 고객의 이름, 주소
SELECT NAME, ADDRESS FROM CUSTOMER;

-- 연습문제 2-4) 2020년 7월 4일~7월 7일 사이에 주문받은 도서의 주문번호
SELECT ORDERID
FROM ORDERS
WHERE ORDERDATE BETWEEN '20.07.04' AND '20.07.07';

-- 연습문제 2-5) 2020년 7월 4일~7월 7일 사이에 주문받은 도서를 제외한 도서의 주문번호
SELECT ORDERID
FROM ORDERS
WHERE ORDERDATE NOT BETWEEN '20.07.04' AND '20.07.07';

-- 연습문제 2-6) 성이 '김'씨인 고객의 이름과 주소
SELECT NAME, ADDRESS
FROM CUSTOMER
WHERE NAME LIKE '김%';

-- 연습문제 2-7) 성이 '김'씨이고 이름이 '아'로 끝나는 고객의 이름과 주소
SELECT NAME, ADDRESS
FROM CUSTOMER
WHERE NAME LIKE '김%아';

-- 연습문제 2-8) 주문하지 않은 고객의 이름(부속질의 사용)
SELECT NAME
FROM CUSTOMER
WHERE CUSTID NOT IN (SELECT CUSTID
FROM ORDERS);

-- 연습문제 2-9) 주문 금액의 총액과 주문의 평균 금액
SELECT SUM(SALEPRICE), AVG(SALEPRICE)
FROM ORDERS;

-- 연습문제 2-10) 고객의 이름과 고객별 구매액
SELECT CUSTOMER.NAME, SUM(ORDERS.SALEPRICE)
FROM CUSTOMER, ORDERS
WHERE CUSTOMER.CUSTID = ORDERS.CUSTID
GROUP BY CUSTOMER.NAME;

-- 연습문제 2-11) 고객의 이름과 고객이 구매한 도서 목록
SELECT CUSTOMER.NAME, BOOK.BOOKNAME
FROM BOOK, CUSTOMER, ORDERS
WHERE BOOK.BOOKID = ORDERS.BOOKID
AND CUSTOMER.CUSTID = ORDERS.CUSTID;

-- 연습문제 2-12) 도서의 가격(Book 테이블)과 판매가격(Orders 테이블)의 차이가 가장 많은 주문
SELECT ORDERS.*
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);

-- 연습문제 2-13) 도서의 판매액 평균보다 자신의 구매액 평균이 더 높은 고객의 이름
SELECT NAME
FROM CUSTOMER, ORDERS
WHERE CUSTOMER.CUSTID = ORDERS.CUSTID
GROUP BY NAME HAVING AVG(SALEPRICE) > (SELECT AVG(ORDERS.SALEPRICE)
FROM ORDERS);

-- 연습문제 3-1) 박지성이 구매한 도서의 출판사와 같은 출판사에서 도서를 구매한 고객의 이름
SELECT NAME
FROM CUSTOMER, ORDERS, BOOK
WHERE CUSTOMER.CUSTID = ORDERS.CUSTID
AND ORDERS.BOOKID = BOOK.BOOKID
AND NAME NOT LIKE '박지성'
AND PUBLISHER
IN (SELECT BOOK.PUBLISHER
FROM CUSTOMER, ORDERS, BOOK
WHERE CUSTOMER.CUSTID = ORDERS.CUSTID
AND ORDERS.BOOKID = BOOK.BOOKID
AND NAME LIKE '박지성');

-- 연습문제 3-2) 두 개 이상의 서로 다른 출판사에서 도서를 구매한 고객의 이름
SELECT NAME
FROM CUSTOMER C1
WHERE 2 >=
(SELECT COUNT(DISTINCT PUBLISHER)
FROM CUSTOMER, ORDERS, BOOK
WHERE CUSTOMER.CUSTID = ORDERS.CUSTID
AND ORDERS.BOOKID = BOOK.BOOKID
AND (NAME LIKE C1.NAME));

-- 연습문제 3-3) 전체 고객의 30% 이상이 구매한 도서

-- 연습문제 4-1) 새로운 도서 ('스포츠 세계', '대한미디어', 10000원)이 마당서점에 입고되었다.
--삽입이 안 될 경우 필요한 데이터가 더 있는지 찾아보시오
INSERT INTO BOOK(BOOKNAME, PUBLISHER, PRICE)
VALUES('스포츠 세계', '대한미디어', '10000원');

-- 연습문제 4-2) '삼성당'에서 출판한 도서를 삭제하시오.
DELETE FROM BOOK WHERE PUBLISHER='삼성당';
SELECT * FROM BOOK;

-- 연습문제 4-3) '이상미디어'에서 출판한 도서를 삭제하시오, 삭제가 안 되면 원인을 생각해 보시오.
DELETE FROM BOOK WHERE PUBLISHER='이상미디어';
-- 다른 테이블에서 PUBLISHER를 참조하고 있어서 무결성 제약조건이 위배되었다.

-- 연습문제 4-4) 출판사 '대한미디어'를 '대한출판사'로 이름을 바꾸시오
UPDATE BOOK SET PUBLISHER='대한출판사' WHERE PUBLISHER='대한미디어';

-- 연습문제 4-5) (테이블 생성) 출판사에 대한 정보를 저장하는 테이블 Bookcompany(name, address,
--begin)를 생성하고자 한다. name은 기본키이며 VARCHAR(20), address는 VARCHAR(20),
--begin은 DATE 타입으로 선언하여 생성하시오.
CREATE TABLE Bookcompany (
name VARCHAR(20) PRIMARY KEY,
address VARCHAR(20),
begin DATE);

-- 연습문제 4-6) (테이블 수정) Bookcompany 테이블에 인터넷 주소를 저장하는 webaddress 속성을
-- VARCHAR(30)으로 추가하시오
ALTER TABLE Bookcompany
ADD webaddress VARCHAR(30);

-- 연습문제 4-7) Bookcompany 테이블에 임의의 투플 name=한빛아카데미, address=서울시 마포구,
-- begin=1993-01-01, webaddress=http://hanbit.co.kr을 삽입하시오.
INSERT INTO Bookcompany (name, address, begin, webaddress)
VALUES ('한빛아카데미', '서울시 마포구', '1993-01-01', 'http://hanbit.co.kr');

-- 연습문제 5-1) 질의의 결과는 무엇인가?
SELECT
FROM CUSTOMER C1
WHERE NOT EXISTS (SELECT

FROM ORDERS C2
WHERE C1.CUSTID=C2.CUSTID);
-- CUSTOMER와 ORDERS를 CUSTID로 동등 조인했을 때
-- 존재하지 않은 CUSTOMER를 출력하시오

-- 연습문제 5-2) NOT을 지우면 질의의 결과는 무엇인가?
-- CUSTOMER와 ORDERS를 CUSTID로 동등 조인했을 때 존재하는 결과들만 출력

-- 연습문제 6) 다음 과제 테이블은 학년별 동아리에 가입한 학생 수와 제출한 학생수를 저장하고 있다.
--'학생이 10명 이상 가입한 동아리에 대하여 동아리와 제출한 총 과제 수를 출력하시오'
--를 수행하기 위한 SQL 문을 작성하시오

CREATE TABLE 과제(
학년 VARCHAR(20),
동아리 VARCHAR(20),
학생수 VARCHAR(20),
과제수 VARCHAR(20)
);

SELECT 동아리, SUM(과제수)
FROM 과제
WHERE 동아리 IN (SELECT 동아리
FROM 과제
WHERE 학생수>=10)
GROUP BY 동아리;

-- 연습문제 7) 도서(도서번호, 도서제목, 출판사명, 발행연도) 테이블에서, 2000년 이후에 10권
-- 이상의 책을 발행한 출판사의 이름을 중복 없이 출력하는 SQL 문으로 옳은 것은?
-- (단, 출판사명이 동일한 출판사는 존재하지 않는 것으로 가정한다. 도서번호는
-- 도서 테이블의 기본키이다.)
SELECT DISTINCT 출판사
FROM 도서
WHERE 발행연도 > 2000
GROUP BY 출판사 HAVING COUNT(도서번호) >=3;

-- 연습문제 9)

-- 연습문제 11-1-1) 모든 극장의 이름과 위치를 보이시오.
SELECT FROM 극장;
SELECT
FROM 상영관;
SELECT FROM 예약;
SELECT
FROM 고객;

SELECT 극장이름, 위치
FROM 극장;

-- 연습문제 11-1-2) '잠실'에 있는 극장을 보이시오.
SELECT 극장이름
FROM 극장
WHERE 위치='잠실';

-- 연습문제 11-1-3) '잠실'에 사는 고객의 이름을 오름차순으로 보이시오.
SELECT 이름
FROM 고객
WHERE 주소='잠실'
ORDER BY 이름;

-- 연습문제 11-1-4) 가격이 8,000원 이하인 영화의 극장번호, 상영관번호, 영화제목을 보이시오.
SELECT 극장번호, 상영관번호, 영화제목
FROM 상영관
WHERE 가격 <=8000;

-- 연습문제 11-1-5) 극장 위치와 고객의 주소가 같은 고객을 보이시오
SELECT DISTINCT 극장.위치 AS 극장위치, 고객.주소 AS 고객주소, 이름
FROM 극장, 고객
WHERE 극장.위치 = 고객.주소;

-- 연습문제 11-2-1) 극장의 수는 몇 개인가?
SELECT COUNT(*)
FROM 극장;

-- 연습문제 11-2-2) 상영되는 영화의 평균 가격은 얼마인가?
SELECT ROUND(AVG(가격))
FROM 상영관;

-- 연습문제 11-2-3) 2020년 9월 1일에 영화를 관람한 고객의 수는 얼마인가?
SELECT COUNT(고객번호)
FROM 예약
WHERE 날짜='2020-09-01';

-- 연습문제 11-3-1) '대한'극장에서 상영된 영화제목을 보이시오.
SELECT 상영관.영화제목
FROM 극장, 상영관
WHERE 극장.극장번호 = 상영관.극장번호
AND 극장이름 = '대한';

-- 연습문제 11-3-2) '대한' 극장에서 영화를 본 고객의 이름을 보이시오.
SELECT 고객.이름
FROM 극장, 예약, 고객
WHERE 극장.극장번호 = 예약.극장번호
AND 고객. 고객번호 = 예약.고객번호
AND 극장.극장이름 = '대한';

SELECT 고객.이름
FROM 고객, 예약
WHERE 예약.고객번호 = 고객.고객번호
AND 예약.극장번호 IN (SELECT 극장번호
FROM 극장
WHERE 극장이름 = '대한');

-- 연습문제 11-3-3) '대한' 극장의 전체 수입을 보이시오.
SELECT SUM(가격)
FROM 극장, 상영관
WHERE 극장.극장번호 = 상영관.극장번호
AND 극장이름 = '대한';

SELECT SUM(가격)
FROM 예약, 상영관, 극장
WHERE 예약.극장번호 = 상영관.극장번호
AND 예약.상영관번호 = 상영관.상영관번호
AND 극장.극장번호 = 상영관.극장번호
AND 극장이름 = '대한';

SELECT SUM(가격)
FROM 상영관
WHERE (상영관.극장번호, 상영관.상영관번호) IN (SELECT 예약.극장번호, 예약.상영관번호
FROM 예약, 극장
WHERE 예약.극장번호=극장.극장번호
AND 극장이름='대한');

-- 연습문제 11-4-1) 극장별 상영관 수를 보이시오.
SELECT 극장.극장이름, COUNT(상영관번호)
FROM 극장, 상영관
WHERE 극장.극장번호 = 상영관.극장번호
GROUP BY 극장이름;

SELECT 극장번호, COUNT(*)
FROM 상영관
GROUP BY 극장번호;

-- 연습문제 11-4-2) '잠실'에 있는 극장의 상영관을 보이시오.
SELECT DISTINCT 극장이름, 상영관번호
FROM 극장, 상영관
WHERE 극장.극장번호 = 상영관.극장번호
AND 위치 = '잠실';

-- 연습문제 11-4-3) 2020년 9월 1일의 극장별 평균 관람 고객 수를 보이시오.
SELECT 극장.극장이름, COUNT(*)
FROM 극장, 예약
WHERE 극장.극장번호 = 예약.극장번호
AND 날짜='2020-09-01'
GROUP BY 예약.극장번호, 극장.극장이름;

-- 연습문제 11-4-4) 2020년 9월 1일에 가장 많은 고객이 관람한 영화를 보이시오.
INSERT INTO 예약 VALUES(1,1,3,17,'2020-09-01');
SELECT * FROM 예약;

SELECT 영화제목, COUNT(고객번호)
FROM 상영관, 예약
WHERE 상영관.극장번호 = 예약.극장번호
AND 상영관.상영관번호 = 예약.상영관번호
AND 날짜='2020-09-01'
GROUP BY 영화제목;

SELECT MAX(COUNT(고객번호))
FROM 상영관, 예약
WHERE 상영관.극장번호 = 예약.극장번호
AND 상영관.상영관번호 = 예약.상영관번호
AND 날짜='2020-09-01'
GROUP BY 영화제목;

SELECT
FROM (SELECT 상영관.영화제목, COUNT(
)
FROM 상영관, 예약
WHERE 상영관.극장번호 = 예약.극장번호
AND 상영관.상영관번호 = 예약.상영관번호
AND 예약.날짜 = '2020-09-01'
GROUP BY 상영관.영화제목
ORDER BY COUNT(*) DESC)
WHERE ROWNUM = 1;

-- 연습문제 11-5-1) 각 테이블에 데이터를 삽입하는 INSERT 문을 하나씩 실행시켜 보시오.

-- 연습문제 12-1) 테이블을 생성하는 CREATE 문과 데이터를 삽입하는 INSERT 문을 작성하시오.
-- 테이블의 타입은 임의로 정하고, 데이터는 아래 질의의 결과가 나오도록 삽입한다.

-- SALESPERSON 테이블 생성
CREATE TABLE SALESPERSON (
NAME VARCHAR(20) PRIMARY KEY,
AGE INT,
SALARY VARCHAR(20)
);

-- ORDER_1 테이블 생성
CREATE TABLE ORDER_1 (
NUMBERS INT,
CUSTNAME VARCHAR(20),
SALESPERSON VARCHAR(20),
AMOUNT VARCHAR(20));

-- CUSTOMER_1 테이블 생성
CREATE TABLE CUSTOMER_1 (
NAME VARCHAR(20) PRIMARY KEY,
CITY VARCHAR(20),
INDUSTRYTYPE VARCHAR(20));

-- ORDER_1 테이블 외래키 설정
ALTER TABLE ORDER_1
ADD FOREIGN KEY (CUSTNAME)
REFERENCES CUSTOMER_1 (NAME);

ALTER TABLE ORDER_1
ADD FOREIGN KEY (SALESPERSON)
REFERENCES SALESPERSON (NAME);

-- 데이터 삽입 : 판매원
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 customer_1(name, city, industrytype) values('Mary', 'LA', '개발자');
insert into customer_1(name, city, industrytype) values('Carrie', 'LA', '요리사');
insert into customer_1(name, city, industrytype) values('IU', 'KR', '가수');
insert into customer_1(name, city, industrytype) values('Diane', 'LA', '개발자');
insert into customer_1(name, city, industrytype) values('Grace', 'DE', '요리사');
insert into customer_1(name, city, industrytype) values('Nancy', 'MX', '교육자');
insert into customer_1(name, city, industrytype) values('Frank', 'US', '교육자');

-- 데이터 삽입 : 주문
insert into order_1(numbers, custname, salesperson, amount) values('1', 'Mary','Tom', '1000');
insert into order_1(numbers, custname, salesperson, amount) values('2', 'IU','Sally', '2000');
insert into order_1(numbers, custname, salesperson, amount) values('3', 'Diane','Nancy', '3000');
insert into order_1(numbers, custname, salesperson, amount) values('4', 'Grace','Roy', '4000');
insert into order_1(numbers, custname, salesperson, amount) values('5', 'Nancy','Tom', '5000');
insert into order_1(numbers, custname, salesperson, amount) values('6', 'Diane','Sally', '6000');
insert into order_1(numbers, custname, salesperson, amount) values('7', 'Grace','Roy', '7000');
insert into order_1(numbers, custname, salesperson, amount) values('8', 'Nancy','Nancy', '8000');

select from salesperson;
select
from order_1;
select * from customer_1;

-- 연습문제 12-2) 모든 판매원의 이름과 급여를 보이시오. 단, 중복 행은 제거한다.
SELECT DISTINCT NAME, SALARY
FROM SALESPERSON;

-- 연습문제 12-3) 나이가 30세 미만인 판매원의 이름을 보이시오.
SELECT NAME
FROM SALESPERSON
WHERE AGE < 30;

-- 연습문제 12-4) 'S'로 끝나는 도시에 사는 고객의 이름을 보이시오
SELECT NAME
FROM CUSTOMER_1
WHERE CITY LIKE '%S';

-- 연습문제 12-5) 주문을 한 고객의 수(서로 다른 고객만)를 구하시오.
SELECT COUNT(DISTINCT CUSTNAME)
FROM ORDER_1;

SELECT COUNT(CUSTNAME) AS "주문을 한 고객의 수"
FROM (SELECT DISTINCT CUSTNAME FROM ORDER_1);

-- 연습문제 12-6) 판매원 각각에 대하여 주문의 수를 계산하시오.
SELECT SALESPERSON, COUNT(NUMBERS)
FROM ORDER_1
GROUP BY SALESPERSON;

-- 연습문제 12-7) 'LA'에 사는 고객으로부터 주문을 받은 판매원의 이름과 나이를 보이시오(부속질의를 사용)
SELECT NAME, AGE
FROM SALESPERSON
WHERE NAME IN (SELECT SALESPERSON
FROM ORDER_1
WHERE CUSTNAME IN (SELECT NAME
FROM CUSTOMER_1
WHERE CITY = 'LA'));

-- 연습문제 12-8) 'LA'에 사는 고객으로 부터 주문을 받은 판매원의 이름과 나이를 보이시오(조인을 사용)
SELECT S.NAME, S.AGE
FROM SALESPERSON S, ORDER_1 O, CUSTOMER_1 C
WHERE S.NAME = O.SALESPERSON
AND O.CUSTNAME = C.NAME
AND C.CITY = 'LA';

-- 연습문제 12-9) 두 번 이상 주문을 받은 판매원의 이름을 보이시오.
SELECT NAME
FROM SALESPERSON S
WHERE 2 <= (SELECT COUNT(SALESPERSON)
FROM ORDER_1 O
WHERE O.SALESPERSON = S.NAME);

SELECT SALESPERSON
FROM ORDER_1
GROUP BY SALESPERSON HAVING COUNT(*) > = 2;

-- 연습문제 12-10) 판매원 'TOM'의 봉급을 45,000원으로 변경하는 SQL 문을 작성하시오
UPDATE SALESPERSON SET SALARY=45000 WHERE NAME='TOM';

-- 연습문제 13-1) 테이블을 생성하는 CREATE 문과 데이터를 삽입하는 INSERT 문을 작성하시오.
-- 테이블의 데이터 타입은 임의로 정하고, 데이터는 아래 질의의 결과가 나오도록 삽입한다.

-- EMPLOY 테이블 생성
CREATE TABLE EMPLOYEE(
EMPNO INT PRIMARY KEY,
NAME VARCHAR(20),
PHONENO INT,
ADDRESS VARCHAR(20),
SEX VARCHAR(20),
POSITION VARCHAR(20),
DEPTNO INT);

-- DEPARTMENT 테이블 생성
CREATE TABLE DEPARTMENT (
DEPTNO INT PRIMARY KEY,
DEPTNAME VARCHAR(20),
MANAGER VARCHAR(20));

-- PROJECT 테이블 생성
CREATE TABLE PROJECT (
PROJNO INT PRIMARY KEY,
PROJNAME VARCHAR(20),
DEPTNO INT);

-- WORKS 테이블 생성
CREATE TABLE WORKS (
EMPNO INT PRIMARY KEY,
PROJNO INT,
HOURS_WORKED INT);

ALTER TABLE WORKS
ADD FOREIGN KEY (PROJNO)
REFERENCES PROJECT (PROJNO);

-- 데이터 저장
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 d ;
select
from employee e ;
select from project p ;
select
from works w ;

-- 연습문제 13-2) 모든 사원의 이름을 보이시오.
SELECT NAME
FROM EMPLOYEE;

-- 연습문제 13-3) 여자 사원의 이름을 보이시오.
SELECT NAME
FROM EMPLOYEE
WHERE SEX='여자';

-- 연습문제 13-4) 팀장(manager)의 이름을 보이시오.
SELECT MANAGER
FROM DEPARTMENT;

-- 연습문제 13-5) 'IT' 부서에서 일하는 사원의 이름과 주소를 보이시오.
SELECT NAME, ADDRESS
FROM EMPLOYEE
WHERE POSITION = 'IT';

-- 연습문제 13-6) '홍길동' 팀장(manager) 부서에서 일하는 사원의 수를 보이시오.
SELECT COUNT(*)
FROM EMPLOYEE, DEPARTMENT
WHERE EMPLOYEE.DEPTNO = DEPARTMENT.DEPTNO
AND MANAGER = '홍길동';

-- 연습문제 13-7) 사원들이 일한 시간 수를 부서명, 사원 이름별 오름차순으로 보이시오.
SELECT D.DEPTNAME, E.NAME, SUM(W.HOURS_WORKED)
FROM DEPARTMENT D, EMPLOYEE E, WORKS W
WHERE D.DEPTNO = E.DEPTNO
AND E.EMPNO = W.EMPNO
GROUP BY D.DEPTNAME, E.NAME
ORDER BY D.DEPTNAME, E.NAME ASC;

SELECT D.DEPTNAME, SUM(W.HOURS_WORKED)
FROM DEPARTMENT D, WORKS W, EMPLOYEE E
WHERE D.DEPTNO = E.DEPTNO
AND E.EMPNO = W.EMPNO
GROUP BY DEPTNAME;

-- 연습문제 13-8) 2명 이상의 사원이 참여한 프로젝트의 번호, 이름, 사원의 수를 보이시오.
SELECT PROJNO, PROJNAME, COUNT(NAME) AS "사원의 수"
FROM PROJECT P, EMPLOYEE E
WHERE P.DEPTNO = E.DEPTNO
GROUP BY P.PROJNAME, PROJNO, PROJNAME HAVING COUNT(*) >= 2
ORDER BY PROJNO;

-- 연습문제 13-9) 3명 이상의 사원이 있는 부서의 사원 이름을 보이시오.
SELECT DEPTNAME, NAME
FROM EMPLOYEE E, DEPARTMENT D
WHERE E.DEPTNO = D.DEPTNO
AND E.DEPTNO IN ( SELECT DEPTNO
FROM EMPLOYEE
GROUP BY DEPTNO HAVING COUNT(DEPTNO) > = 3)
ORDER BY DEPTNAME;

-- 연습문제 14 데이터 저장

SELECT FROM DEPT;
SELECT
FROM EMP;

-- 연습문제 14-1) 사원의 이름과 업무를 출력하시오. 단 사원의 이름은 '사원이름', 업무는
-- '사원업무' 머리글이 나오도록 출력한다.

SELECT ENAME AS 사원이름, JOB AS 사원업무
FROM EMP;

-- 연습문제 14-2) 30번 부서에 근무하는 모든 사원의 이름과 급여를 출력하시오.
SELECT ENAME, SAL
FROM EMP
WHERE DEPTNO = 30;

-- 연습문제 14-3) 사원번호와 이름, 현재 급여, 증가된 급여분(열 이름은 '증가액'),
-- 10% 인상된 급여(열 이름은 '인상된 급여')를 사원번호순으로 출력하시오.
SELECT EMPNO, ENAME, SAL, COMM AS 증가액, (SAL+NVL(COMM,0)+SAL*0.1) AS "인상된 급여"
FROM EMP
ORDER BY EMPNO;

SELECT EMPNO, ENAME, SAL, SAL0.1 AS 증가액, ROUND(SAL+(SAL0.1)) AS "인상된 급여"
FROM EMP
ORDER BY EMPNO;

-- 연습문제 14-4) 'S'로 시작하는 모든 사원과 부서번호를 출력하시오.
SELECT ENAME, DEPTNO
FROM EMP
WHERE ENAME LIKE 'S%';

-- 연습문제 14-5) 모든 사원의 최대 및 최소 급여, 합계 및 평균 급여를 출력하시오.
-- 열 이름은 각각 MAX, MIN, SUM, AVG로 한다.
-- 단, 소수점 이하는 반올림하여 정수로 출력한다.
SELECT MAX(SAL) AS MAX, MIN(SAL) AS MIN, SUM(SAL) AS SUM, ROUND(AVG(SAL)) AS AVG
FROM EMP;

-- 연습문제 14-6) 업무 이름과 업무별로 동일한 업무를 하는 사원의 수를 출력하시오.
-- 열 이름은 각각 '업무'와 '업무별 사원수'로 한다.
SELECT JOB AS 업무, COUNT(*) AS "업무별 사원수"
FROM EMP
GROUP BY JOB;

-- 연습문제 14-7) 사원의 최대 급여와 최소 급여의 차액을 출력하시오.
SELECT MAX(SAL) - MIN(SAL) AS "최대 급여와 최소 급여의 차액"
FROM EMP;

-- 연습문제 14-8) 30번 부서의 사원 수와 사원들 급여의 합계와 평균을 출력하시오.
SELECT COUNT(*), SUM(SAL), ROUND(AVG(SAL))
FROM EMP
WHERE DEPTNO = 30;

-- 연습문제 14-9) 평균 급여가 가장 높은 부서의 번호를 출력하시오.
SELECT *
FROM (SELECT DEPTNO, ROUND(AVG(SAL))
FROM EMP
GROUP BY DEPTNO
ORDER BY ROUND(AVG(SAL)) DESC)
WHERE ROWNUM = 1;

-- 연습문제 14-10) 세일즈맨(SALESMAN)을 제외하고, 각 업무별 사원의 총급여가 3,000 이상인
-- 각 업무에 대해서, 업무명과 각 업무별 평균 급여를 출력하시오.
-- 단, 평균 급여의 내림차순으로 출력한다.
SELECT JOB, ROUND(AVG(SAL))
FROM EMP
WHERE (SELECT SUM(SAL)
FROM EMP)> =3000
AND JOB != 'SALESMAN'
GROUP BY JOB
ORDER BY ROUND(AVG(SAL)) DESC;

-- 연습문제 14-11) 전체 사원 가운데 직속상관이 있는 사원의 수를 출력하시오.
SELECT COUNT(*)
FROM EMP
WHERE MGR IS NOT NULL;

-- 연습문제 14-12) EMP 테이블에서 이름, 급여, 커미션(COMM) 금액, 총액(SAL12+COMM)을
-- 구하여 총액이 많은 순서대로 출력하시오. 단, 커미션이 NULL인 사람은 제외한다.
SELECT ENAME AS 이름, SAL AS 급여, COMM AS "커미션 금액", (SAL
12+COMM) AS 총액
FROM EMP
WHERE COMM IS NOT NULL;

-- 연습문제 14-13) 각 부서별로 같은 업무를 하는 사람의 인원수를 구하여 부서번호, 업무 이름
-- 인원수를 출력하시오
SELECT DEPTNO, JOB, COUNT(*)
FROM EMP
GROUP BY DEPTNO, JOB
ORDER BY DEPTNO;

-- 연습문제 14-14) 사원이 1명도 없는 부서의 이름을 출력하시오.
SELECT DNAME
FROM (SELECT D.DNAME, E.DEPTNO
FROM DEPT D FULL OUTER JOIN EMP E ON D.DEPTNO = E.DEPTNO)
WHERE DEPTNO IS NULL;

SELECT DEPT.DNAME
FROM EMP RIGHT JOIN DEPT ON EMP.DEPTNO = DEPT.DEPTNO
WHERE EMP.EMPNO IS NULL;

-- 연습문제 14-15) 같은 업무를 하는 사람의 수가 4명 이상인 업무와 인원수를 출력하시오.
SELECT JOB, COUNT()
FROM EMP
GROUP BY JOB HAVING COUNT(
) >=4;

-- 연습문제 14-16) 사원번호가 7400 이상 7600 이하인 사원의 이름 수를 출력하시오.
SELECT ENAME
FROM EMP
WHERE EMPNO BETWEEN 7400 AND 7600;

-- 연습문제 14-17) 사원의 이름과 사원의 부서이름을 출력하시오.
SELECT E.ENAME, D.DNAME
FROM EMP E, DEPT D
WHERE E.DEPTNO = D.DEPTNO;

-- 연습문제 14-18) 사원의 이름과 팀장(MGR)의 이름을 출력하시오.
SELECT E.ENAME AS 사원, S.ENAME AS 팀장
FROM EMP E, (SELECT DISTINCT EMPNO, ENAME
FROM EMP A, (SELECT MGR
FROM EMP
WHERE MGR IN (SELECT EMPNO
FROM EMP)) B
WHERE A.EMPNO = B.MGR
ORDER BY EMPNO) S
WHERE E.MGR = S.EMPNO
ORDER BY 사원;

SELECT A.ENAME AS 사원, B.ENAME AS 팀장
FROM EMP A, EMP B
WHERE A.MGR = B.EMPNO
ORDER BY 사원;

-- 연습문제 14-19) 사원 SCOTT보다 급여를 많이 받는 사람의 이름을 출력하시오.
SELECT ENAME
FROM EMP
WHERE SAL > (SELECT SAL
FROM EMP
WHERE ENAME = 'SCOTT');

-- 연습문제 14-20) 사원 SCOTT이 일하는 부서번호 혹은 DALLAS에 있는 부서번호를 출력하시오.
SELECT DISTINCT DEPTNO
FROM EMP
WHERE ENAME = 'SCOTT' OR DEPTNO = (SELECT DEPTNO
FROM DEPT
WHERE LOC ='DALLAS');

profile
비전공자 Java, JavaScript, Html, Css, C++ 공부중

1개의 댓글

comment-user-thumbnail
2023년 4월 20일

왜 틀린거 달아요?
킹밧네용
경찰에 신고하도롯 하겠스빈다.
참고로 저희 아버지는 대한민구 경찰 1등 이빈다.
반박시 어쩔티비
저희 누나 대학교 6학년임 조심하셈

답글 달기