kakao Cloud school 2기 D+31

1. DML 연습문제 - 1

<my answer>

select count(distinct publisher) as 개수
from Book, Customer, Orders
where Customer.custid = Orders.custid and Book.bookid = Orders.bookid and Customer.name = '박지성';

select Book.bookname, Book.price, (Book.price - Orders.saleprice) as 차이
from Book, Customer, Orders
where Customer.custid = Orders.custid and Book.bookid = Orders.bookid and Customer.name = '박지성';

select distinct Book.bookname
from Book, Customer, Orders
where Customer.custid = Orders.custid and Book.bookid = Orders.bookid and Customer.custid !='박지성';

select name
from Customer
where custid not in (select custid from Orders);
select sum(saleprice) AS 총금액, avg(saleprice) AS 평균금액
from Orders;

SELECT DISTINCT Customer.name, sum(saleprice)
FROM Customer, Orders
WHERE Customer.custid = Orders.custid group BY name;

select distinct Customer.name, Book.bookname
from Customer, Book, Orders
where Customer.custid = Orders.custid and Book.bookid = Orders.bookid;

select orderid from Orders, Book where Book.bookid = Orders.bookid and (Book.price -
Orders.saleprice) = (select max(Book.price - Orders.saleprice)
from Book, Orders
where Book.bookid = Orders.bookid) ;

select name from Orders,Customer where Customer.custid = Orders.custid group by Orders.custid
having avg(saleprice) > (select avg(saleprice) from Orders);

<other answer>

-- 박지성이 구매한 도서의 출판사수.
SELECT COUNT(DISTINCT publisher) AS 출판사수
FROM Customer,Orders,Book
WHERE Customer.custid = Orders.custid
AND Orders.bookid = Book.bookid
AND Customer.name = '박지성';

-- 박지성이 구매한 도서의 이름,정가,정가와 판매가격의 차이.
SELECT Book.bookname, Book.price, Book.price-Orders.saleprice
AS 할인금액
FROM Customer,Orders,Book
WHERE Customer.custid = Orders.custid
AND Orders.bookid = Book.bookid AND Customer.name = '박지성';

-- 박지성이 구매하지 않은 도서의 이름.
SELECT bookname FROM Book
WHERE bookname NOT IN
(SELECT Book.bookname FROM Book,Orders,Customer
WHERE Customer.custid = Orders.custid
AND Orders.bookid = Book.bookid AND Customer.name='박지성');

-- 주문하지 않은 고객의 이름(부속질의 사용)
SELECT name FROM Customer
WHERE custid NOT IN(SELECT custid FROM Orders);
-- 주문금액의 총액과 주문의 평균 금액.
SELECT SUM(saleprice) AS 주문총액, AVG(saleprice) AS 평균금액 FROM Orders;

-- 고객의 이름과 고객별 구매액
SELECT Customer.name, SUM(saleprice) AS 고객별구매액
FROM Customer,Orders
WHERE Customer.custid = Orders.custid
GROUP BY Customer.name;

-- 도서의 가격(Book)과 판매가격(Orders)의 차이가 가장 많은 주문
-- 1.차이가 가장 많이는 주문의 가격.
SELECT MAX(price-saleprice) FROM Book,Orders
WHERE Orders.bookid = Book.Bookid;

-- 위에서구한 MAX(price-saleprice)과 price-saleprice이 일치하는 케이스.
SELECT Orders.orderid FROM Orders,Book
WHERE Orders.bookid = Book.bookid
AND price-saleprice = 
(SELECT MAX(price-saleprice) FROM Book,Orders
WHERE Orders.bookid = Book.Bookid);

-- 도서의 판매액 평균보다 자신의 구매액 평균이 더 높은 고객의 이름.
-- 1.도서의 판마액 평균.
-- SELECT AVG(saleprice) FROM Orders
-- 2.자신의 구매액 평균(=GROUP BY로 평균을 구해야함)이 더 높은
SELECT Customer.name FROM Orders,Customer
WHERE Customer.custid = Orders.custid
GROUP BY Orders.custid
HAVING AVG(saleprice) > (SELECT AVG(saleprice) FROM Orders);

2. DML 연습문제 - 2 & DDL 연습문제

-- 연습문제

-- (1) 박지성이 구매한 도서의 출판사와 같은 출판사에서 도서를 구매한 고객의 이름
<GROUP BY로 투플을 그룹으로 묶은 후 SELECT 절에는 GROUP BY에서 사용한 속성과 집
계함수만 나올 수 있음!!!!!!!!!!!!!!>



-- (2)두 개 이상의 서로 다른 출판사에서 도서를 구매한 고객의 이름
select Customer.name
from Customer, Orders, Book
where Customer.custid = Orders.custid and Book.bookid = Orders.bookid
group by Customer.name
having count(distinct Book.publisher) >= 2;

-- (3) (생략) 전체 고객의 30% 이상이 구매한 도서

select Book.bookname
from Customer, Orders, Book where Book.bookid = Orders.bookid and Customer.custid = Orders.custid 
group by Book.bookname 
having count(Book.bookname) >= (select (count(*) * 0.3) from Customer);


-- 새로운 도서 ('스포츠 세계', '대한미디어', 10000원)이 마당서점에 입고되었다. 삽입이 안될 경우 필요한 데이터가 더 있는지 찾아보자
insert into Book(bookid,bookname, publisher, price) values (11,'스포츠 세계', '대한미디어', 10000);
select * from Book;

-- 삼성당’에서 출판한 도서를 삭제해야 한다.
delete from Book where publisher = '삼성당';
select * from Book;

-- ‘이상미디어’에서 출판한 도서를 삭제해야 한다. 삭제가 안 될 경우 원인을 생각해보자. : 이유 없음.
delete from Book where publisher = '이상미디어';

-- 출판사 ‘대한미디어’가 ‘대한출판사’로 이름을 바꾸었다.
update Book set publisher = '대한출판사' where publisher = '대한미디어';

3. 쉘스크립트 간단한 작성법

[root@mariadb ~]# test="hi"
test에 hi 넣기

[root@mariadb ~]# echo $test
hi
$ 옵션을 줘서 불러오기

[root@mariadb ~]# echo ${test}1234
hi1234
이어붙일 수도 있음

[root@mariadb ~]# date
Wed Dec 14 02:11:15 EST 2022
기본적으로 날짜 정보 제공

[root@mariadb ~]# my_date=$(date)
[root@mariadb ~]# echo $my_date
Wed Dec 14 02:11:45 EST 2022

[root@mariadb ~]# my_date=$(date +%y%m%d)
[root@mariadb ~]# echo $my_date
221214
날짜를 숫자형으로 변환 가능

직전 실행이 성공적으로 적용됐는지 - 0 : 성공
[root@mariadb ~]# echo $?
0

[root@mariadb ~]# ping 8.8.8.8 -c 1
PING 8.8.8.8 (8.8.8.8) 56(84) bytes of data.
64 bytes from 8.8.8.8: icmp_seq=1 ttl=128 time=43.3 ms
--- 8.8.8.8 ping statistics ---
1 packets transmitted, 1 received, 0% packet loss, time 0ms
rtt min/avg/max/mdev = 43.396/43.396/43.396/0.000 ms
성공한 명령어

성공
[root@mariadb ~]# echo $?
0
[root@mariadb ~]# ping asdf.123.c -c 1
ping: asdf.123.c: Name or service not known

실패
[root@mariadb ~]# echo $?
2

---

Redirection (>) : 원하는 값들을 파일에 담아둘 수 있음!!
이전에 실행시켰던 명령어가 정상적으로 실행 됐을시 정상적으로 적용 가능하다

하지만, 실패한 스크립트를 만약에 저장하고 싶다면 -c 옵션에 '2'를 추가해주면 된다.
아래 실습 코드를 보면서 이해하자!

---

[root@mariadb ~]# cat /etc/sysconfig/network-scripts/ifcfg-ens32 > ifcfg.txt
ifcfg-ens32 파일의 내용을 ifcfg.txt 파일로 redirection 

[root@mariadb ~]# cat ifcfg.txt
TYPE="Ethernet"
PROXY_METHOD="none"
BROWSER_ONLY="no"
BOOTPROTO="none"
DEFROUTE="yes"
IPV4_FAILURE_FATAL="no"
IPV6INIT="yes"
IPV6_AUTOCONF="yes"
IPV6_DEFROUTE="yes"
IPV6_FAILURE_FATAL="no"
IPV6_ADDR_GEN_MODE="stable-privacy"
NAME="ens32"
UUID="1ccd4968-bd21-4cd2-b1cf-9ec1d48eac45"
DEVICE="ens32"
ONBOOT="yes"
IPADDR="211.183.3.33"
PREFIX="24"
GATEWAY="211.183.3.2"
DNS1="8.8.8.8"
IPV6_PRIVACY="no"


[root@mariadb ~]# date > date.txt
[root@mariadb ~]# cat date.txt
Wed Dec 14 02:17:38 EST 2022
날짜를 date.txt 파일에 저장

[root@mariadb ~]# ping asdf.123.c -c 1
ping: asdf.123.c: Name or service not known
[root@mariadb ~]# ping asdf.123.c -c 1 > ping.txt
ping: asdf.123.c: Name or service not known
[root@mariadb ~]# cat ping.txt
실패한 스크립트는 redirection 이 앞서 안된다고 하였지만 -c 1 옵션에 2를 추가해주면 된다고헀다

[root@mariadb ~]# ping asdf.123.c -c 1 2> ping.txt
[root@mariadb ~]# cat ping.txt
ping: asdf.123.c: Name or service not known

[root@mariadb ~]# ls
anaconda-ks.cfg date.txt ifcfg.txt mariadb_repo_setup ping.txt
[root@mariadb ~]# ping 8.8.8.8 -c 1 > ping1.txt
[root@mariadb ~]# cat ping1.txt
PING 8.8.8.8 (8.8.8.8) 56(84) bytes of data.
64 bytes from 8.8.8.8: icmp_seq=1 ttl=128 time=42.6 ms
--- 8.8.8.8 ping statistics ---
1 packets transmitted, 1 received, 0% packet loss, time 0ms
rtt min/avg/max/mdev = 42.643/42.643/42.643/0.000 ms

---

• Data Base 외부에서 내부로 쿼리 날리기 (-e 옵션)

---

<-e 옵션 사용>
[root@mariadb ~]# mysql -u root -p1234 -e "use shopdb;SELECT * FROM Customer;"
+--------+-----------+------------------------+---------------+
| custid | name | address | phone |
+--------+-----------+------------------------+---------------+
| 1 | 박지성 | 영국 맨체스타 | 000-5000-0001 |
| 2 | 김연아 | 대한민국 서울 | 000-6000-0001 |
| 3 | 장미란 | 대한민국 강원도 | 000-7000-0001 |
| 4 | 추신수 | 미국 클리블랜드 | 000-8000-0001 |
| 5 | 박세리 | 대한민국 대전 | NULL |
+--------+-----------+------------------------+---------------+

<-se 옵션 사용 : 가로줄 세로줄 삭제>
[root@mariadb ~]# mysql -u root -p1234 -se "use shopdb;SELECT * FROM Customer;"
custid name addressphone
1 박지성 영국 맨체스타 000-5000-0001
2 김연아 대한민국 서울 000-6000-0001
3 장미란 대한민국 강원도 000-7000-0001
4 추신수 미국 클리블랜드 000-8000-0001
5 박세리 대한민국 대전 NULL

[root@mariadb ~]# mysql -u root -p1234 -se "use shopdb;SELECT address FROM Customer WHERE
custid=1;"
address
영국 맨체스타

<-Bse 옵션 사용 : 순수데이터 출력>
[root@mariadb ~]# mysql -u root -p1234 -Bse "use shopdb;SELECT address FROM Customer WHERE
custid=1;"
영국 맨체스타