MySQL로 배우는 데이터베이스 개론과 실습 2판 - 3장 문제 풀이

1.

select bookname from BOOK where bookId =1;

select bookname from BOOK
where price >= 20000;

select sum(salePrice) from Orders
where custId = (select custId from customer where name = '박지성');

select count(*) from Orders
where custid = (select custId from Customer where name = '박지성');

select count(distinct publisher) from BOOK B
join orders O on o.bookId = B.bookId
where custid = (select custId from Customer where name = '박지성');

select bookname, price, abs(price - salePrice) from Orders O
join BOOK B on O.bookId = B.bookId
where custId = (select custid from customer where name = '박지성');

select bookname from Orders O
join BOOK B on O.bookId = B.bookId
where B.bookId not in (select bookid from orders where custid = (select custid from Customer where name = '박지성'))                                                                          where name = '박지성'))
group by bookname;

 

2.

select count(*) from BOOK

select count(distinct publisher) from BOOK;

select name, address from Customer;

select orderId from Orders
where orderdate between '2024-07-04' and '2024-07-07';

select orderId from Orders
where orderdate not between '2024-07-04' and '2024-07-07';

select name, address from Customer
where name like "김%";

select name, address from Customer
where name like "김%이";

select name from Customer
where custid not in (select custid from Orders);

select sum(salePrice), avg(salePrice) from Orders;

select name, sum(salePrice) from Orders O
join Customer C on C.custId = O.custId
group by name;

select name, bookId from Orders O
join Customer C on C.custId = O.custId
group by name, bookid;

select * from Orders
where orderId = (select orderid from Orders o
join BOOK b on o.bookId = b.bookId
order by abs(o.salePrice - b.price) desc
limit 1);

select C.name from orders o
join Customer C on C.custId = O.custId
group by C.name
having avg(o.salePrice) > (select avg(salePrice) from Orders);

 

 

3. 

select name from Customer C
join Orders O on C.custId = O.custId
join BOOK B on B.bookId = O.bookId
where B.publisher
          in (select B1.publisher from Customer C1
        join Orders O1 on C1.custId = O1.custId
        join BOOK B1 on B1.bookId = O1.bookId
        where C1.custId = (select custid from customer where name = '박지성'))
        and C.name != '박지성';

select name from Customer c
join orders o on c.custId = o.custId
join BOOK b on b.bookId = o.bookId
group by c.name
having count(distinct b.publisher) >= 2;

select b.bookId, b.bookname from BOOK b
join Orders O on b.bookId = O.bookId
group by b.bookId
having count(*) >= 0.3 * (select count(*) from Customer);

 

4.

-- 답 : 북 아이디가 필요하다.
insert into book(bookid, bookname, publisher, price) values('북 아이디가 필요하다.','스포츠 세계', '대한미디어', 10000);

delete from book
where publisher = "삼성당";

delete from book
where publisher = "이상미디어";

update book
set publisher = '대한출판사'
where publisher = '대한미디어';

create table Bookcompany(
	name varchar(20) primary key,
    address varchar(20),
    begin date
);

alter table bookcompany add webaddress varchar(30);

insert into bookcompany(name, address, begin, webaddress) 
values('한빛아카데미','서울시 마포구','1993-01-01','<http://hanbit.co.kr>');

 

5.

1. 책 구매를 하지 않은 고객
2. 책을 구매한 고객

6.

DDL (데이터 정의어)

• CREATE: 테이블, 뷰, 인덱스와 같은 데이터베이스 객체를 생성하는 데 사용됩니다.
• ALTER: 기존 데이터베이스 객체의 구조를 수정하는 데 사용됩니다.
• DROP: 데이터베이스 객체를 삭제하는 데 사용됩니다.

DML (데이터 조작어)

• SELECT: 데이터베이스에서 데이터를 검색하는 데 사용됩니다.
• INSERT: 데이터베이스에 새 데이터를 추가하는 데 사용됩니다.
• DELETE: 데이터베이스에서 데이터를 삭제하는 데 사용됩니다.
• UPDATE: 데이터베이스의 기존 데이터를 수정하는 데 사용됩니다.

DCL (데이터 제어어)

• GRANT: 사용자에게 데이터베이스에 대한 액세스 권한을 부여하는 데 사용됩니다.
• REVOKE: 사용자의 데이터베이스 액세스 권한을 취소하는 데 사용됩니다

 

7.

1. select * where A=a2 from R
2. select A, B from R
3. SELECT * from R join S on R.C = S.C

 

8.

 

9.

 

10.

 

11. 

 

12.

 

 

13.

select address, changeday from cust_attr
where custid = 1;

select phone, changeday
from cust_attr
where custid = 1;

select phone from cust_attr
where custid = 1
order by changeday asc
limit 1;

select phone from cust_attr
where custid = 1 and changeday = '2024-01-01'
order by changeday asc limit 1;

 

14.

select * from orders o
join cart c on o.bookId = c.bookid
where c.custid = 1;

select * from cart
where cart.bookid not in (select bookid from orders where custid = 1) and cart.custid = 1;

select sum(price) from book b
join cart c on b.bookid = c.bookid;

 

15.

1

SELECT 
    theaterName, theaterLocation
FROM
    theater;

2

SELECT 
    *
FROM
    theater
WHERE
    theaterLocation = '잠실';

3

SELECT 
    userName
FROM
    users
WHERE
    userAddress = '잠실'
order by userName asc;

4

SELECT 
    theaterNum, cinemaNum, movieName
FROM
    cinema
WHERE
    price <= 8000;

5

select * from users join theater
on theater.theaterLocation = users.userAddress;

 

(2)

1

select count(*) from theater;

2

SELECT 
    AVG(price)
FROM
    cinema;

3

SELECT 
    COUNT(*) AS '고객 수'
FROM
    reservation,
    users
WHERE
    reservation.userNum = users.userNum
        AND date = '2014-09-01';

 

(3)

1

SELECT 
    movieName
FROM
    theater,
    cinema
WHERE
    theater.theaterName = '대한'
        AND theater.theaterNum = cinema.theaterNum;

2

select distinct(username) from users u
join reservation r on u.userNum = r.userNum
join movie.theater t on r.theaterNum = t.theaterNum
where t.theaterName like '대한';

3

select sum(price) from reservation r
join theater t on r.theaterNum = t.theaterNum
join cinema c on c.cinemaNum = r.cinemaNum
where r.theaterNum = (select theaterNum from theater where theaterName = '대한')
group by r.theaterNum

 

(4)

select theaterNum, count(theaterNum) from cinema
group by theaterNum;

select * from cinema c
join theater t on c.theaterNum = t.theaterNum
where t.theaterLocation = '잠실';

select count(userNum) from reservation
where date = '2024-09-01'
group by theaterNum;

select c.movieName, count(*) from reservation r
join cinema c on r.cinemaNum = c.cinemaNum
where date = '2024-09-01'
group by r.theaterNum, r.cinemaNum
order by count(*) desc limit 1;

 

(5)

UPDATE cinema 
SET 
    price = price + (price * 0.1);

 

16.

-- 테이블 생성 : 판매원
create table Salesperson(
	name varchar(20) primary key,
    age integer,
    salary integer
);

-- 테이블 생성 : 고객
create table customer (
	name varchar(20) primary key,
    city varchar(20),
    industrytype varchar(20)
);

-- 테이블 생성 : 주문
create table orders (
	number integer,
    custname varchar(20),
    saesperson varchar(20),
    amount integer,
    foreign key (custname) references customer(name),
    foreign key (saesperson) 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 customer(name, city, industrytype) values('Mary', 'LA', '개발자');
insert into customer(name, city, industrytype) values('Carrie', 'LA', '요리사');

-- 데이터 삽입 : 주문
insert into orders(number, custname, saesperson, amount) values('1', 'Mary','Tom', '1000');
insert into orders(number, custname, saesperson, amount) values('2', 'IU','Sally', '2000');

select name, age from salesperson
group by name;

select name
from salesperson
where age < 30;

select name
from customer
where city like "%S";

select count(distinct custname)
from orders;

select count(salesperson)
from orders
group by salesperson;

select name, age from salesperson s
where name in (select salesperson from Orders where custname in
                                             (select name from customer where city like '%LA%'));

select S.name, S.age from orders O
join salesperson S on O.salesperson = S.name
join customer C on C.name = O.custname
where C.city like 'LA';

select name from salesperson S
join Orders o on S.name = o.salesperson
group by salesperson
having count(custname) >= 2;

10

update salesperson
set salary = 45000
where name = 'TOM';

 

 

17

create table Employee
(
	empno int auto_increment 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
);

-- 외래키 설정
alter table Employee add constraint foreign key(deptno) references Department(deptno) on delete cascade on update cascade;
alter table Project add constraint foreign key(deptno) references Department(deptno) on delete cascade on update cascade;
alter table Works add constraint foreign key(empno) references Employee(empno) on delete cascade on update cascade;
alter table Works add constraint foreign key(projno) references Project(projno) on delete cascade on update cascade;

-- 데이터 저장
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(null, '민준', '010-1234-1230', '서울', '남자', 'IT', 1);
insert into Employee values(null, '서준', '010-1234-1231', '부산', '남자', 'IT', 2);
insert into Employee values(null, '예준', '010-1234-1232', '울산', '여자', 'service', 3);
insert into Employee values(null, '도윤', '010-1234-1233', '김포', '남자', 'IT', 1);
insert into Employee values(null, '시우', '010-1234-1234', '서울', '여자', 'service', 4);
insert into Employee values(null, '주원', '010-1234-1235', '부산', '여자', 'IT', 2);
insert into Employee values(null, '하준', '010-1234-1236', '김포', '남자', 'service', 4);
insert into Employee values(null, '지호', '010-1234-1237', '울산', '여자', 'service', 3);
insert into Employee values(null, '지후', '010-1234-1238', '서울', '남자', 'IT', 1);
insert into Employee values(null, '준서', '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);

-- (2) 모든 사원의 이름을 보이시오.
select name from employee;

-- (3) 여자 사원의 이름을 보이시오.
select name from employee where sex = '여자';

-- (4) 팀장(manager)의 이름을 보이시오.
select manager from department;

-- (5) 'IT' 부서에서 일하는 사원의 이름과 주소를 보이시오.
select name, address
from employee
where `position` = 'IT';

-- (6) '홍길동' 팀장(manager) 부서에서 일하는 사원의 수를보이시오.
select count(*) from Employee
where deptno = (select deptno from Department where manager =' 홍길동');

-- (7) 사원들이 일한 시간 수를 부서별, 사원 이름별 오름차순으로 보이시오.
select w.empno, projno, w.hours_worked from Works w
join Employee e on w.empno = e.empno
order by w.empno asc, projno asc;

-- (8) 2명 이상의 사원이 참여한 프로젝트의 번호, 이름, 사원의 수를 나타내시오.
select projno, projname, count(deptno) from Project p
group by projno
having count(deptno) >= 2;

--(9) 3명 이사의 사원이 있는 부서의 사원 이름을 나타내시오. 

select deptno, name from Employee where deptno in (
select D.deptno from Employee E
join Department D on E.deptno = D.deptno
group by D.deptno
having count(*) >= 3);

18.

SELECT * FROM DEPT D ; -- 부서
SELECT * FROM EMP E ; -- 사원

-- (1) 사원의 이름과 직위를 출력하시오. 단, 
-- 사원이 이름은 '사원이름', 직위는 '사원직위' 머리글이 나오도록 출력한다.
SELECT E.ENAME '사원이름', D.DNAME '사원직위'
FROM EMP E, DEPT D 
WHERE E.DEPTNO = D.DEPTNO;

-- (2) 30번 부서에 근무하는 모든 사원의 이름과 급여를 출력하시오.
SELECT E.ENAME '사원이름', E.SAL 
FROM EMP E, DEPT D 
WHERE E.DEPTNO = D.DEPTNO
AND D.DEPTNO = 30;

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

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

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

-- (6) 업무 이름과 업무별로 동일한 업무를 하는 사원의 수를 출력하시오.
-- 열 이름은 각각 '업무'와 '업무별 사원수'로 한다.
SELECT D.DNAME '업무', COUNT(*) '업무별 사원수' 
FROM EMP E, DEPT D 
WHERE E.DEPTNO = D.DEPTNO
GROUP BY D.DNAME; 

-- (7) 사원의 최대 급여와 최소 급여의 차액을 출력하시오.
SELECT (MAX(SAL) - MIN(SAL)) '차액' 
FROM EMP;

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

-- (9) 평균급여가 가장 높은 부서의 번호를 출력하시오.
SELECT DEPTNO DNO, ROUND(AVG(SAL)) AVGSAL
FROM EMP
GROUP BY DEPTNO
ORDER BY AVGSAL DESC 
LIMIT 1;

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

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

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

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

-- (14) 사원이 한 명도 없는 부서의 이름을 출력하시오.
SELECT D.DNAME 
FROM EMP E RIGHT JOIN DEPT D 
ON E.DEPTNO = D.DEPTNO 
WHERE E.EMPNO IS NULL;

-- (15) 같은 업무를 하는 사람의 수가 네 명 이상인 업무와 인원 수를 출력하시오.
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, DEPT D 
WHERE E.DEPTNO = D.DEPTNO ;

-- (18) 사원의 이름과 팀장(MGR)의 이름을 출력하시오. ? 팀장 이름 테이블이 없다.

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

-- (20)번 
SELECT deptno FROM DEPT D, EMP E
WHERE D.deptno = E.deptno and E.ename = 'SCOTT'
UNION
SELECT deptno FROM DEPT D, EMP E
WHERE D.deptno = E.deptno and E.ename = 'DALLAS';