본 게시글은 오라클로 배우는 데이터베이스 개론과 실습 2판 - 연습문제 3장 풀이입니다. 제가 이 책을 공부하면서 풀었던 풀이의 흔적이므로 정답이 아닐 수 있습니다. 혹여나 틀린 부분이 있어 오류를 댓글로 지적해주신다면 감사하겠습니다.
1. 마당서점의 고객이 요구하는 다음 질문에 대해 SQL 문을 작성하시오.
(1) 도서번호가 1인 도서의 이름
SELECT bookname
FROM Book
WHERE bookid = 1;(2) 가격이 20,000원 이상인 도서의 이름
SELECT bookname
FROM Book
WHERE price >= 20000;(3) 박지성의 총 구매액
SELECT SUM(saleprice)
FROM Customer, Orders
WHERE Customer.custid = Orders.custid
AND Customer.name LIKE '박지성';(4) 박지성이 구매한 도서의 수
SELECT COUNT(*)
FROM Customer, Orders
WHERE Customer.custid = Orders.custid
AND Customer.name LIKE '박지성';(5) 박지성이 구매한 도서의 출판사 수
SELECT COUNT(DISTINCT publisher)
FROM Customer, Orders, Book
WHERE Customer.custid = Orders.custid
AND Orders.bookid = Book.bookid
AND Customer.name LIKE '박지성';(6) 박지성이 구매한 도서의 이름, 가격, 정가와 판매가격의 차이
SELECT bookname, price, price - saleprice
FROM Customer, Orders, Book
WHERE Customer.custid = Orders.custid
AND Orders.bookid = Book.bookid
AND Customer.name LIKE '박지성';(7) 박지성이 구매하지 않은 도서의 이름
SELECT bookname
FROM Book b1
WHERE NOT EXISTS (
SELECT 1 FROM Orders o1
WHERE o1.bookid = b1.bookid
AND o1.custid = (
SELECT custid
FROM Customer
WHERE name LIKE '박지성'
)
);2. 마당서점의 운영자와 경영자가 요구하는 다음 질문에 대해 SQL 문을 작성하시오.
(1) 마당서점 도서의 총 개수
SELECT COUNT(*)
FROM Book;(2) 마당서점에 도서를 출고하는 출판사의 총 개수
SELECT COUNT(DISTINCT publisher)
FROM Book;(3) 모든 고객의 이름, 주소
SELECT name, address
FROM Customer;(4) 2014년 7월 4일~7월 7일 사이에 주문받은 도서의 주문번호
SELECT *
FROM Orders
WHERE orderdate BETWEEN '20140704' AND '20140707';(5) 2014년 7월 4일~7월 7일 사이에 주문받은 도서를 제외한 도서의 주문번호
SELECT *
FROM Orders
WHERE orderdate NOT BETWEEN '20140704' AND '20140707';(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 custname
FROM Orders
);(9) 주문 금액의 총액과 주문의 평균 금액
SELECT SUM(saleprice), AVG(saleprice)
FROM Orders;(10) 고객의 이름과 고객별 구매액
SELECT name, SUM(saleprice)
FROM Orders, Customer
WHERE Orders.custid = Customer.custid
GROUP BY name;(11) 고객의 이름과 고객이 구매한 도서 목록
SELECT name, bookname
FROM Book, Orders, Customer
WHERE Book.bookid = Orders.bookid AND Orders.custid = Customer.custid;(12) 도서의 가격(Book 테이블)과 판매가격(Orders 테이블)의 차이가 가장 많은 주문
SELECT * FROM Book, Orders
WHERE Book.bookid = Orders.bookid
AND price - saleprice = (
SELECT MAX(price - saleprice)
FROM Book, Orders
WHERE Book.bookid = Orders.bookid
);(13) 도서의 판매액 평균보다 자신의 구매액 평균이 더 높은 고객의 이름
SELECT name, AVG(saleprice)
FROM Customer, Orders
WHERE Customer.custid = Orders.custid
GROUP BY name
HAVING AVG(saleprice) > (
SELECT AVG(saleprice)
FROM Orders
);3. 마당서점에서 다음의 심화된 질문에 대해 SQL 문을 작성하시오.
(1) 박지성이 구매한 도서의 출판사와 같은 출판사에서 도서를 구매한 고객의 이름
SELECT publisher
FROM Customer, Orders, Book
WHERE Customer.custid=Orders.custid
AND Orders.bookid=Book.bookid
AND name LIKE '박지성';(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) 전체 고객의 30% 이상이 구매한 도서
SELECT bookname
FROM Book b1
WHERE (
(
SELECT COUNT(Book.bookid)
FROM Book, Orders
WHERE Book.bookid=Orders.bookid
AND Book.bookid=b1.bookid
) >= 0.3 * (
SELECT COUNT(*)
FROM Customer
)
);4. 다음 질의에 대해 DML 문을 작성하시오.
(1) 새로운 도서 ('스포츠 세계', '대한미디어', 10000원)이 마당서점에 입고되었다. 삽입이 안 될 경우 필요한 데이터가 더 있는지 찾아보시오.
INSERT INTO BOOK VALUES(11, '스포츠 세계', '대한미디어', 10000);(2) '삼성당'에서 출판한 도서를 삭제하시오.
DELETE FROM Book WHERE publisher LIKE '삼성당';(3) '이상미디어'에서 출판한 도서를 삭제하시오. 삭제가 안 될 경우 원인을 생각해보시오.
DELETE FROM Book WHERE publisher LIKE '이상미디어';
(4) 출판사 '대한미디어'를 '대한출판사'로 이름을 바꾸시오.
UPDATE Book SET publisher='대한출판사' WHERE publisher LIKE '대한미디어';(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);(6) (테이블 수정) Bookcompany 테이블에 인터넷 주소를 저장하는 webaddress 속성을 VARCHAR(30)으로 추가하시오.
ALTER TABLE Bookcompany ADD webaddress VARCHAR(30);(7) Bookcompany 테이블에 임의의 투플 name=한빛아카데미, address=서울시 마포구, begin=1993-01-01, webaddress=http://hanbit.co.kr를 삽입하시오.
INSERT INTO Bookcompany VALUES ('한빛아카데미', '서울시 마포구', '1993-01-01', '<http://hanbit.co.kr>');5. 다음 EXISTS 질의의 결과를 보이시오.
SELECT *
FROM Customer c1
WHERE NOT EXISTS (SELECT *
FROM Orders c2
WHERE c1.custid=c2.custid);(1) 질의의 결과는 무엇인가?
(2) NOT을 지우면 질의의 결과는 무엇인가?
6. 다음 과제 테이블은 학년별 동아리에 가입한 학생 수와 제출한 과제 수를 저장하고 있다. ‘학생이 10명 이상 가입한 동아리 대하여 동아리와 제출한 총 과제 수를 출력하시오’를 수행하기 위한 SQL문을 작성하시오.
| 학년 | 동아리 | 학생 수 | 과제 수 |
|---|---|---|---|
| 1 | A | 20 | 20 |
| 1 | B | 10 | 20 |
| 1 | C | 5 | 8 |
| 2 | A | 15 | 10 |
| 2 | B | 15 | 20 |
| 3 | A | 5 | 15 |
| 3 | B | 12 | 20 |
| 3 | C | 8 | 15 |
SELECT 학년, 동아리, SUM(학생 수) AS 총_학생_수, SUM(과제 수) AS 총_과제_수
FROM 과제
GROUP BY 학년, 동아리
HAVING SUM(학생 수) >= 10;7. 도서(도서번호, 도서제목, 출판사명, 발행연도) 테이블에서, 2000년 이후에 10권 이상의 책을 발행한 출판사의 이름을 중복 없이 출력하는 SQL 문으로 옳은 것은? (단, 출판사명이 동일한 출판사는 존재하지 않는 것으로 가정한다. 도서번호는 도서 테이블의 기본키이다.)
SELECT DISTINCT 출판사명
FROM 도서
WHERE 발행연도 >= 2000
GROUP BY 출판사명
HAVING COUNT(도서번호) >= 10;R
| 1 | a | 10 |
|---|---|---|
| 1 | a | 11 |
| 1 | a | 25 |
| 2 | b | 22 |
| 3 | b | 21 |
| 5 | c | 17 |
S
| 1 | p | x |
|---|---|---|
| 1 | p | y |
| 2 | q | y |
| 4 | r | w |
| 6 | s | z |
(SELECT DISTINCT A FROM R)
UNION ALL
(SELECT A FROM S)SQL문을 수행한 후 생성되는 투플(tuple)의 총 개수: 8개
9. 회사 데이터베이스에서 직원이 6명 이상인 부서의 부서명과 그 부서 소속 직원 중 급여가 40,000 이상인 직원의 수를 검색하는 SQL 질의를 작성하시오. (단, 모든 부서에서 급여가 40,000 이상인 직원이 1명 이상 있다고 가정한다.)
SELECT B.부서명, COUNT(*) AS 급여_40만_이상_직원_수
FROM 부서 B
JOIN 직원 E ON B.부서번호 = E.소속부서번호
WHERE E.급여 >= 40000
GROUP BY B.부서명
HAVING COUNT(*) >= 6;10. 다음 '사원' 테이블을 생성하는 SQL 문에서 부서의 속성값을 '인사', '연구', '영업'으로 제한하고자 한다. ㄱ, ㄴ에 들어갈 내용으로 옳은 것은?
CREATE TABLE 사원
(사번 NUMBER NOT NULL,
이름 CHAR(10),
직급 CHAR(10),
부서 CHAR(10) ㄱ(부서 ㄴ('인사', '연구', '영업')));ㄱ: CHECK
ㄴ: IN
11. [극장 데이터베이스] 다음은 4개의 지점을 가진 극장 데이터베이스다. 밑줄 친 속성은 기본키이다. 테이블의 구조를 만들고 데이터를 입력한 후 다음 질의에 대한 SQL 문을 작성하시오.
(1) 단순 질의
① 모든 극장의 이름과 위치를 보이시오.
SELECT 극장이름, 위치
FROM 극장;② '잠실'에 있는 극장을 보이시오.
SELECT *
FROM 극장
WHERE 위치 LIKE '잠실';③ '잠실'에 사는 고객의 이름을 오름차순으로 보이시오.
SELECT 고객번호, 이름, 주소
FROM 고객
WHERE 주소 LIKE '잠실'
ORDER BY 이름;④ 가격이 8,000원 이하인 영화의 극장번호, 상영관번호, 영화제목을 보이시오.
SELECT 극장번호, 상영관번호, 영화제목
FROM 상영관
WHERE 가격 <= 8000;⑤ 극장 위치와 고객의 주소가 같은 고객들을 보이시오.
SELECT 고객.이름, 극장.위치
FROM 고객, 극장
WHERE 고객.주소 LIKE 극장.위치;(2) 집계질의
① 극장의 수는 몇 개인가?
SELECT COUNT(극장번호)
FROM 극장;② 상영되는 영화의 평균 가격은 얼마인가?
SELECT AVG(가격)
FROM 상영관;③2014년 9월 1일에 영화를 관람한 고객의 수는 얼마인가?
SELECT COUNT(이름)
FROM 고객, 예약
WHERE 예약.고객번호=고객.고객번호
AND 날짜 LIKE '2014-09-01';(3) 부속질의와 조인
① '대한' 극장에서 상영된 영화제목을 보이시오.
SELECT 영화제목
FROM 극장, 상영관
WHERE 극장.극장번호=상영관.극장번호
AND 극장이름 LIKE '대한';② '대한' 극장에서 영화를 본 고객의 이름을 보이시오.
SELECT 고객.이름
FROM 극장, 예약, 고객
WHERE 극장.극장번호=예약.극장번호
AND 예약.고객번호=고객.고객번호
AND 극장이름 LIKE '대한';③ '대한' 극장의 전체 수입을 보이시오.
SELECT SUM(가격)
FROM 극장, 상영관, 예약
WHERE 극장.극장번호=상영관.극장번호
AND 상영관.극장번호=예약.극장번호
AND 상영간.상영관번호=예약.상영관번호;(4) 그룹질의
① 극장별 상영관 수를 보이시오.
SELECT 극장번호, COUNT(*)
FROM 상영관
GROUP BY 극장번호;② '잠실'에 있는 극장의 상영관을 보이시오.
SELECT *
FROM 극장, 상영관
WHERE 극장.극장번호=상영관.극장번호
AND 위치 LIKE '잠실';③ 201년 9월 1일에 극장별 평균 관람 고객의 수를 보이시오.
SELECT 극장번호, COUNT(*)
FROM 예약 WHERE 날짜
LIKE '2014-09-01'
GROUP BY 극장번호;④ 2014년 9월 1일에 가장 많은 고객이 관람한 영화를 보이시오.
SELECT 영화제목 FROM 상영관, 예약
WHERE 상영관.극장번호=예약.극장번호
AND 상영관.상영관번호=예약.상영관번호
AND 날짜 LIKE '2014-09-01'
GROUP BY 예약.극장번호, 예약.상영관번호
HAVING COUNT(*) = (SELECT MAX(*)
FROM 상영관, 예약
WHERE 상영관.극장번호=예약.극장번호
AND 상영관.상영관번호=예약.상영관번호
AND 날짜 LIKE '2014-09-01'
GROUP BY 예약.극장번호, 예약.상영관번호
);(5) DML
① 각 테이블에 데이터를 삽입하는 INSERT 문들을 하나씩 보이시오.
INSERT INTO 극장(극장번호, 극장이름, 위치)
VALUES (1, 'CGV', 'Gangnam');
INSERT INTO 상영관(극장번호, 상영관번호, 영화제목, 가격, 좌석수)
VALUES (2, 1, 'HarryPotter', 13000, 130);
INSERT INTO 예약(극장번호, 상영관번호, 고객번호, 좌석번호, 날짜)
VALUES (2, 1, 2037, 77, '2018-11-21');
INSERT INTO 고객(고객번호, 이름, 주소)
VALUES (2037, 'IanKim', 'Gangnam');② 영화의 가격을 10% 인상하시오.
UPDATE 상영관 SET 가격 = 가격 * 1.1;- [판매원 데이터베이스] 다음 릴레이션을 보고 물음에 답하시오. Salesperson은 판매원, Order는 주문, Customer는 고객을 나타낸다. 밑줄 친 속성은 기본키이고 custname과 salesperson은 각각 Customer.name과 Salesperson.name을 참조하는 외래키이다.
Salesperson(name, age, salary)
Order(number, custname, salesperson, amount)
Custoner(name, city, industrytype)(1) 테이블을 생성하는 CREATE문과 데이터를 삽입하는 INSERT문을 작성하시오. 테이블의 데이터 타입은 임의로 정하고, 데이터는 아래 질의의 결과가 나오도록 삽입한다.
(2) 모든 판매원의 이름과 급여를 보이시오. 단, 중복 행은 제거한다.
(3) 나이가 30세 미만인 판매원의 이름을 보이시오.
(4) ‘S’로 끝나는 도시에 사는 고객의 이름을 보이시오.
(5) 주문을 한 고객의 수(서로 다른 고객만)를 구하시오.
(6) 판매원 각각에 대하여 주문의 수를 계산하시오.
(7) ‘LA’에 사는 고객으로부터 주문을 받은 판매원의 이름과 나이를 보이시오(부속질의를 사용).
(8) ‘LA’에 사는 고객으로부터 주문을 받은 판매원의 이름과 나이를 보이시오(조인을 사용).
(9) 두 번 이상 주문을 받은 판매원의 이름을 보이시오.
(10) 판매원 ‘TOM’의 봉급을 45,000원으로 변경하는 SQL문을 작성하시오.
1. 테이블 생성 및 데이터 삽입
CREATE TABLE Salesperson (
name VARCHAR(50) PRIMARY KEY,
age INT,
salary INT
);
CREATE TABLE Order (
number INT,
custname VARCHAR(50),
salesperson VARCHAR(50),
amount INT,
PRIMARY KEY (custname, salesperson),
FOREIGN KEY (custname) REFERENCES Customer(name),
FOREIGN KEY (salesperson) REFERENCES Salesperson(name)
);
CREATE TABLE Customer (
name VARCHAR(50) PRIMARY KEY,
city VARCHAR(50),
industrytype VARCHAR(50)
);
INSERT INTO Salesperson VALUES ('Alice', 28, 30000);
INSERT INTO Salesperson VALUES ('Bob', 35, 40000);
INSERT INTO Customer VALUES ('Cust1', 'LA', 'Tech');
INSERT INTO Customer VALUES ('Cust2', 'New York', 'Finance');
INSERT INTO Order VALUES (1, 'Cust1', 'Alice', 2000);
INSERT INTO Order VALUES (2, 'Cust2', 'Bob', 1500);2. 모든 판매원의 이름과 급여
SELECT DISTINCT name, salary FROM Salesperson;
3. 나이가 30세 미만인 판매원의 이름
SELECT name FROM Salesperson WHERE age < 30;
4. ‘S’로 끝나는 도시에 사는 고객의 이름
SELECT name FROM Customer WHERE city LIKE '%S';
5. 주문을 한 고객의 수
SELECT COUNT(DISTINCT custname) FROM Order;
6. 판매원별 주문 수
SELECT salesperson, COUNT(*) FROM Order GROUP BY salesperson;
7. ‘LA’에 사는 고객으로부터 주문을 받은 판매원 (부속질의 사용)
SELECT name, age FROM Salesperson
WHERE name IN (SELECT salesperson FROM Order WHERE custname IN (SELECT name FROM Customer WHERE city = 'LA'));
8. ‘LA’에 사는 고객으로부터 주문을 받은 판매원 (조인 사용)
SELECT S.name, S.age FROM Salesperson S
JOIN Order O ON S.name = O.salesperson
JOIN Customer C ON O.custname = C.name
WHERE C.city = 'LA';
9. 두 번 이상 주문을 받은 판매원의 이름
SELECT salesperson FROM Order GROUP BY salesperson HAVING COUNT(*) > 1;
10. 판매원 ‘TOM’의 봉급 변경
UPDATE Salesperson SET salary = 45000 WHERE name = 'TOM';
- [기업 프로젝트 데이터베이스] 다음 릴레이션을 보고 물음에 답하시오. Employee는 사원, Department는 부서, Project는 프로젝트, Works는 사원이 프로젝트에 참여한 내용을 나타낸다. 한 사원이 여러 프로젝트에서 일하거나 한 프로젝트에 여러 사원이 일할 수 있다. hours-worked 속성은 각 사원이 각 프로젝트에서 일한 시간을 나타낸다. 밑줄 친 속성은 기본키이다.
Employee(empno, name, phoneno, address, sex, position, deptno)
Department(deptno, deptname, manager)
Project(projno, projname, deptno)
Works(empno, projno, hours-worked)(1) 테이블을 생성하는 CREATE문과 데이터를 삽입하는 INSERT문을 작성하시오. 테이블의 데이터 타입은 임의로 정하고, 데이터는 아래 질의의 결과가 나오도록 삽입한다.
(2) 모든 사원의 이름을 보이시오.
(3) 여자 사원의 이름을 보이시오.
(4) 팀장(manager)의 이름을 보이시오.
(5) ‘IT’ 부서에서 일하는 사원의 이름과 주소를 보이시오.
(6) ‘홍길동’ 팀장(manager) 부서에서 일하는 사원의 수를 보이시오.
(7) 사원들이 일한 시간 수를 부서별, 사원 이름별 오름차순으로 보이시오.
(8) 2명 이상의 사원이 참여한 프로젝트의 번호, 이름, 사원의 수를 보이시오.
1. 테이블 생성 및 데이터 삽입
CREATE TABLE Employee (
empno INT PRIMARY KEY,
name VARCHAR(50),
phoneno VARCHAR(20),
address VARCHAR(100),
sex CHAR(1),
position VARCHAR(50),
deptno INT
);
CREATE TABLE Department (
deptno INT PRIMARY KEY,
deptname VARCHAR(50),
manager INT
);
CREATE TABLE Project (
projno INT PRIMARY KEY,
projname VARCHAR(50),
deptno INT
);
CREATE TABLE Works (
empno INT,
projno INT,
hours_worked INT,
PRIMARY KEY (empno, projno),
FOREIGN KEY (empno) REFERENCES Employee(empno),
FOREIGN KEY (projno) REFERENCES Project(projno)
);
INSERT INTO Department VALUES (101, 'IT', 1);
INSERT INTO Employee VALUES (1, '홍길동', '010-1234-5678', '서울', 'M', '팀장', 101);
INSERT INTO Employee VALUES (2, '김철수', '010-2345-6789', '부산', 'M', '엔지니어', 101);
INSERT INTO Project VALUES (1001, '프로젝트A', 101);
INSERT INTO Project VALUES (1002, '프로젝트B', 101);
INSERT INTO Works VALUES (1, 1001, 30);
INSERT INTO Works VALUES (2, 1001, 20);
INSERT INTO Works VALUES (1, 1002, 15);
INSERT INTO Works VALUES (2, 1002, 25);2. 모든 사원의 이름
SELECT name FROM Employee;
3. 여자 사원의 이름
SELECT name FROM Employee WHERE sex = 'F';
4. 팀장(manager)의 이름
SELECT E.name FROM Employee E JOIN Department D ON E.empno = D.manager;
5. 'IT' 부서에서 일하는 사원의 이름과 주소
SELECT E.name, E.address FROM Employee E JOIN Department D ON E.deptno = D.deptno WHERE D.deptname = 'IT';
6. '홍길동' 팀장 부서에서 일하는 사원의 수
SELECT COUNT(*) FROM Employee E
JOIN Department D ON E.deptno = D.deptno
WHERE D.manager = (SELECT empno FROM Employee WHERE name = '홍길동');
7. 사원들이 일한 시간 수 (부서별, 사원 이름별 오름차순)
SELECT E.deptno, E.name, SUM(W.hours_worked) FROM Employee E
JOIN Works W ON E.empno = W.empno
GROUP BY E.deptno, E.name
ORDER BY E.deptno ASC, E.name ASC;
8. 2명 이상의 사원이 참여한 프로젝트
SELECT P.projno, P.projname, COUNT(*) AS 사원수
FROM Project P JOIN Works W ON P.projno = W.projno
GROUP BY P.projno, P.projname
HAVING COUNT(*) > 1;
- [사원 데이터베이스] Dept는 부서(Department) 테이블로 deptno(부서번호), dname(부서이름), loc(위치, location)으로 구성되어 있다. Emp는 사원(Employee) 테이블로 empno(사원번호), ename(사원이름), job(업무), mgr(팀장번호, manager), hiredate(고용날짜), sal(급여, salary), comm(커미션금액, commission), deptno(부서번호)로 구성되어 있다. 밑줄 친 속성은 기본키이고 Emp의 deptno는 Dept의 deptno를 참조하는 외래키이다. 사원 데이터베이스는 demo_scott, sql 스크립트를 실행하여 설치하도록 한다.
Dept(deptno Number(2), dname VARCHAR2(14), loc VARCHAR2(13))
Emp(empno Number(4), ename VARCHAR2(10), job VARCHAR2(9), mgr NUMBER(4), hiredate DATE, sal NUMBER(7,2), comm NUMBER(7,2), deptno NUMBER(2))(1) 사원의 이름과 업무를 출력하시오. 단, 사원의 이름은 ‘사원이름’, 업무는 ‘사원업무’ 머리글이 나오도록 출력한다.
(2) 30번 부서에 근무하는 모든 사원의 이름과 급여를 출력하시오.
(3) 사원번호와 이름, 현재 급여, 증가된 급여분(열 이름은 ‘증가액’), 10% 인상된 급여(열 이름은 ‘인상된 급여’)를 사원번호 순으로 출력하시오.
(4) ‘S’로 시작하는 모든 사원과 부서번호를 출력하시오.
(5) 모든 사원의 최대 및 최소 급여, 합계 및 평균 급여를 출력하시오. 열 이름은 각각 MAX, MIN, SUM, AVG로 한다. 단, 소수점 이하는 반올림하여 정수로 출력한다.
(6) 업무 이름과 업무별로 동일한 업무를 하는 사원의 수를 출력하시오. 열 이름은 각각 ‘업무’와 ‘업무별 사원수’로 한다.
(7) 사원의 최대 급여와 최소 급여의 차액을 출력하시오.
(8) 30번 부서의 사원 수와 사원들 급여의 합계와 평균을 출력하시오.
(9) 평균 급여가 가장 높은 부서의 번호를 출력하시오.
(10) 세일즈맨(SALESMAN)을 제외하고, 각 업무별 사원의 총급여가 3,000 이상인 각 업무에 대해서, 업무명과 각 업무별 평균 급여를 출력하시오. 단 평균 급여의 내림차순으로 출력한다.
(11) 전체 사원 가운데 직속상관이 있는 사원의 수를 출력하시오.
(12) Emp 테이블에서 이름, 급여, 커미션(comm) 금액, 총액(sal*12+comm)을 구하여 총액이 많은 순서대로 출력하시오. 단, 커미션이 NULL 인 사람은 제외한다.
(13) 각 부서별로 같은 업무를 하는 사람의 인원수를 구하여 부서번호, 업무 이름, 인원수를 출력하시오.
(14) 사원이 1명도 없는 부서의 이름을 출력하시오.
(15) 같은 업무를 하는 사람의 수가 4명 이상인 업무와 인원수를 출력하시오.
(16) 사원번호가 7400 이상 7600 이하인 사원의 이름을 출력하시오.
(17) 사원의 이름과 사원의 부서이름을 출력하시오.
(18) 사원의 이름과 팀장(mgr)의 이름을 출력하시오.
(19) 사원 SCOTT보다 급여를 많이 받는 사람의 이름을 출력하시오.
(20) 사원 SCOTT이 일하는 부서번호 혹은 DALLAS에 있는 부서번호를 출력하시오.
1. 사원의 이름과 업무
SELECT ename AS "사원이름", job AS "사원업무" FROM Emp;
2. 30번 부서의 사원 이름과 급여
SELECT ename, sal FROM Emp WHERE deptno = 30;
3. 사원별 현재 급여, 증가액, 인상된 급여
SELECT empno, ename, sal, sal * 0.1 AS "증가액", sal * 1.1 AS "인상된 급여" FROM Emp ORDER BY empno;
4. ‘S’로 시작하는 사원과 부서번호
SELECT ename, deptno FROM Emp WHERE ename LIKE 'S%';
5. 모든 사원의 급여 통계
SELECT ROUND(MAX(sal)) AS "MAX", ROUND(MIN(sal)) AS "MIN", ROUND(SUM(sal)) AS "SUM", ROUND(AVG(sal)) AS "AVG" FROM Emp;
6. 업무별 사원 수
SELECT job AS "업무", COUNT(*) AS "업무별 사원수" FROM Emp GROUP BY job;
7. 최대 급여와 최소 급여의 차액
SELECT MAX(sal) - MIN(sal) FROM Emp;
8. 30번 부서의 사원 수, 급여 합계와 평균
SELECT COUNT(*), SUM(sal), AVG(sal) FROM Emp WHERE deptno = 30;
9. 평균 급여가 가장 높은 부서 번호
SELECT deptno FROM Emp GROUP BY deptno ORDER BY AVG(sal) DESC FETCH FIRST 1 ROW ONLY;
10. 세일즈맨을 제외한 업무별 평균 급여
SELECT job, AVG(sal) FROM Emp WHERE job != 'SALESMAN' GROUP BY job HAVING SUM(sal) >= 3000 ORDER BY AVG(sal) DESC;
11. 직속 상관이 있는 사원의 수
SELECT COUNT(*) FROM Emp WHERE mgr IS NOT NULL;
12. 이름, 급여, 커미션, 총액(순서대로)
SELECT ename, sal, comm, (sal * 12 + comm) AS total FROM Emp WHERE comm IS NOT NULL ORDER BY total DESC;
13. 부서별, 업무별 인원수
SELECT deptno, job, COUNT(*) FROM Emp GROUP BY deptno, job;
14. 사원이 없는 부서 이름
SELECT dname FROM Dept WHERE deptno NOT IN (SELECT deptno FROM Emp);
15. 같은 업무를 하는 사람이 4명 이상인 업무
SELECT job, COUNT(*) FROM Emp GROUP BY job HAVING COUNT(*) >= 4;
16. 사원번호 7400 이상 7600 이하인 사원 이름
SELECT ename FROM Emp WHERE empno BETWEEN 7400 AND 7600;
17. 사원 이름과 부서 이름
SELECT E.ename, D.dname FROM Emp E JOIN Dept D ON E.deptno = D.deptno;
18. 사원 이름과 팀장 이름
SELECT E1.ename, E2.ename AS "mgr_name" FROM Emp E1 JOIN Emp E2 ON E1.mgr = E2.empno;
19. SCOTT보다 급여를 많이 받는 사람의 이름
SELECT ename FROM Emp WHERE sal > (SELECT sal FROM Emp WHERE ename = 'SCOTT');
20. SCOTT이 일하는 부서 번호 또는 DALLAS에 있는 부서 번호
SELECT deptno FROM Dept WHERE deptno = (SELECT deptno FROM Emp WHERE ename = 'SCOTT') OR loc = 'DALLAS';
'CSE > 데이터베이스 (database)' 카테고리의 다른 글
| 오라클로 배우는 데이터베이스 개론과 실습 2판 - 연습문제 5장 (0) | 2023.12.17 |
|---|---|
| 오라클로 배우는 데이터베이스 개론과 실습 2판 - 마당서점 데이터베이스 구축 (0) | 2023.12.16 |
| 오라클로 배우는 데이터베이스 개론과 실습 2판 - 연습문제 4장 (0) | 2023.09.11 |
| 오라클로 배우는 데이터베이스 개론과 실습 2판 - 연습문제 2장 (2023. 10. 26 수정) (2) | 2023.09.11 |
| 오라클로 배우는 데이터베이스 개론과 실습 2판 - 연습문제 1장 (2) | 2023.09.09 |
컴퓨터 전공 관련, 프론트엔드 개발 지식들을 공유합니다. React, Javascript를 다룰 줄 알며 요즘에는 Typescript에도 관심이 생겨 공부하고 있습니다. 서로 소통하면서 프로젝트 하는 것을 즐기며 많은 대외활동으로 개발 능력과 소프트 스킬을 다듬어나가고 있습니다.
포스팅이 좋았다면 "좋아요❤️" 또는 "구독👍🏻" 해주세요!
