-- ÁúÀÇ 3-1 ¸ðµç µµ¼­ÀÇ À̸§°ú °¡°ÝÀ» °Ë»öÇϽÿÀ.
SELECT bookname, price;

SELECT price, bookname
FROM Book;

-- ÁúÀÇ 3-2 ¸ðµç µµ¼­ÀÇ µµ¼­¹øÈ£, µµ¼­À̸§, ÃâÆÇ»ç, °¡°ÝÀ» °Ë»öÇϽÿÀ.
SELECT bookid, bookname, publisher, price
FROM Book;

-- ÁúÀÇ 3-3 µµ¼­ Å×ÀÌºí¿¡ ÀÖ´Â ¸ðµç ÃâÆǻ縦 °Ë»öÇϽÿÀ.
SELECT publisher
FROM Book;

SELECT DISTINCT publisher
FROM Book;

-- ÁúÀÇ 3-4 °¡°ÝÀÌ 20,000¿ø ¹Ì¸¸ÀÎ µµ¼­¸¦ °Ë»öÇϽÿÀ.
SELECT *
FROM Book
WHERE price < 20000;

-- ÁúÀÇ 3-5 °¡°ÝÀÌ 10,000¿ø ÀÌ»ó 20,000 ÀÌÇÏÀÎ µµ¼­¸¦ °Ë»öÇϽÿÀ.
SELECT *
FROM Book
WHERE price BETWEEN 10000 AND 20000;

SELECT *
FROM Book
WHERE price >= 10000 AND price <= 20000;

-- ÁúÀÇ 3-6 ÃâÆǻ簡 ¡®±Â½ºÆ÷Ã÷¡¯ ȤÀº ¡®´ëÇѹ̵ð¾î¡¯ÀÎ µµ¼­¸¦ °Ë»öÇϽÿÀ.
SELECT *
FROM Book
WHERE publisher IN ('±Â½ºÆ÷Ã÷', '´ëÇѹ̵ð¾î');

SELECT *
FROM Book
WHERE publisher NOT IN ('±Â½ºÆ÷Ã÷', '´ëÇѹ̵ð¾î');

-- ÁúÀÇ 3-7 ¡®Ã౸ÀÇ ¿ª»ç¡¯¸¦ Ãâ°£ÇÑ ÃâÆǻ縦 °Ë»öÇϽÿÀ.
SELECT bookname, publisher
FROM Book
WHERE bookname LIKE 'Ã౸ÀÇ ¿ª»ç';

-- ÁúÀÇ 3-8 µµ¼­À̸§¿¡ ¡®Ã౸¡¯°¡ Æ÷ÇÔµÈ ÃâÆǻ縦 °Ë»öÇϽÿÀ.
SELECT bookname, publisher
FROM Book
WHERE bookname LIKE '%Ã౸%';

-- ÁúÀÇ 3-9 µµ¼­À̸§ ¿ÞÂÊ µÎ ¹ø° À§Ä¡¿¡ ¡®±¸¡¯¶ó´Â ¹®ÀÚ¿­À» °®´Â µµ¼­¸¦ ¸ðµÎ °Ë»öÇϽÿÀ.
SELECT *
FROM Book
WHERE bookname LIKE '_±¸%';

-- ÁúÀÇ 3-10 Ã౸¿¡ °üÇÑ µµ¼­ Áß °¡°ÝÀÌ 20,000¿ø ÀÌ»óÀÎ µµ¼­¸¦ °Ë»öÇϽÿÀ.
SELECT *
FROM Book
WHERE bookname LIKE '%Ã౸%' AND price >= 20000;

-- ÁúÀÇ 3-11 ÃâÆǻ簡 ¡®±Â½ºÆ÷Ã÷¡¯ ȤÀº ¡®´ëÇѹ̵ð¾î¡¯ÀÎ µµ¼­¸¦ °Ë»öÇϽÿÀ.
SELECT *
FROM Book
WHERE publisher='±Â½ºÆ÷Ã÷' OR publisher='´ëÇѹ̵ð¾î';

-- ÁúÀÇ 3-12 µµ¼­¸¦ À̸§¼øÀ¸·Î °Ë»öÇϽÿÀ.
SELECT *
FROM Book
ORDER BY bookname;

-- ÁúÀÇ 3-13 µµ¼­¸¦ °¡°Ý¼øÀ¸·Î °Ë»öÇÏ°í, °¡°ÝÀÌ °°À¸¸é À̸§¼øÀ¸·Î °Ë»öÇϽÿÀ.
SELECT *
FROM Book
ORDER BY price, bookname;

-- ÁúÀÇ 3-14 µµ¼­¸¦ °¡°ÝÀÇ ³»¸²Â÷¼øÀ¸·Î °Ë»öÇϽÿÀ. ¸¸¾à °¡°ÝÀÌ °°´Ù¸é ÃâÆÇ»çÀÇ ¿À¸§Â÷¼øÀ¸·Î °Ë»öÇÑ´Ù.
SELECT *
FROM Book
ORDER BY price DESC, publisher ASC;

-- ÁúÀÇ 3-15 °í°´ÀÌ ÁÖ¹®ÇÑ µµ¼­ÀÇ ÃÑ ÆǸž×À» ±¸ÇϽÿÀ.
SELECT SUM(saleprice)
FROM Orders;

SELECT SUM(saleprice) AS ÃѸÅÃâ
FROM Orders;

-- ÁúÀÇ 3-16 2¹ø ±è¿¬¾Æ °í°´ÀÌ ÁÖ¹®ÇÑ µµ¼­ÀÇ ÃÑ ÆǸž×À» ±¸ÇϽÿÀ.
SELECT SUM(saleprice) AS ÃѸÅÃâ
FROM Orders
WHERE custid=2;

-- ÁúÀÇ 3-17 °í°´ÀÌ ÁÖ¹®ÇÑ µµ¼­ÀÇ ÃÑ ÆǸž×, Æò±Õ°ª, ÃÖÀú°¡, ÃÖ°í°¡¸¦ ±¸ÇϽÿÀ.
SELECT SUM(saleprice) AS Total,
AVG(saleprice) AS Average,
MIN(saleprice) AS Minimum,
MAX(saleprice) AS Maximum
FROM Orders;

-- ÁúÀÇ 3-18 ¸¶´ç¼­Á¡ÀÇ µµ¼­ ÆǸŠ°Ç¼ö¸¦ ±¸ÇϽÿÀ.
SELECT COUNT(*)
FROM Orders;

-- ÁúÀÇ 3-19 °í°´º°·Î ÁÖ¹®ÇÑ µµ¼­ÀÇ ÃÑ ¼ö·®°ú ÃÑ ÆǸž×À» ±¸ÇϽÿÀ.
SELECT custid, COUNT(*) AS µµ¼­¼ö·®, SUM(saleprice) AS ÃѾ×
FROM Orders
GROUP BY custid;

-- ÁúÀÇ 3-20 °¡°ÝÀÌ 8,000¿ø ÀÌ»óÀÎ µµ¼­¸¦ ±¸¸ÅÇÑ °í°´¿¡ ´ëÇÏ¿© °í°´º° ÁÖ¹® µµ¼­ÀÇ ÃÑ ¼ö·®À» ±¸ÇϽÿÀ. ´Ü, 2±Ç
ÀÌ»ó ±¸¸ÅÇÑ °í°´¸¸ ±¸ÇÑ´Ù.
SELECT custid, COUNT(*) AS µµ¼­¼ö·®
FROM Orders
WHERE saleprice >= 8000
GROUP BY custid
HAVING count(*) >= 2;

-- ÁúÀÇ 3-21 °í°´°ú °í°´ÀÇ ÁÖ¹®¿¡ °üÇÑ µ¥ÀÌÅ͸¦ ¸ðµÎ º¸À̽ÿÀ.
SELECT *
FROM Customer, Orders
WHERE Customer.custid =Orders.custid;

-- ÁúÀÇ 3-22 °í°´°ú °í°´ÀÇ ÁÖ¹®¿¡ °üÇÑ µ¥ÀÌÅ͸¦ °í°´º°·Î Á¤·ÄÇÏ¿© º¸À̽ÿÀ.
SELECT *
FROM Customer, Orders
WHERE Customer.custid =Orders.custid
ORDER BY Customer.custid;

-- ÁúÀÇ 3-23 °í°´ÀÇ À̸§°ú °í°´ÀÌ ÁÖ¹®ÇÑ µµ¼­ÀÇ °¡°ÝÀ» °Ë»öÇϽÿÀ.
SELECT name, saleprice
FROM Customer, Orders
WHERE Customer.custid =Orders.custid;

-- ÁúÀÇ 3-24 °í°´º°·Î ÁÖ¹®ÇÑ ¸ðµç µµ¼­ÀÇ ÃÑ ÆǸž×À» ±¸ÇÏ°í, °í°´º°·Î Á¤·ÄÇϽÿÀ.
SELECT name, SUM(saleprice)
FROM Customer, Orders
WHERE Customer.custid =Orders.custid
GROUP BY Customer.name
ORDER BY Customer.name;

-- ÁúÀÇ 3-25 °í°´ÀÇ À̸§°ú °í°´ÀÌ ÁÖ¹®ÇÑ µµ¼­ÀÇ À̸§À» ±¸ÇϽÿÀ.
SELECT Customer.name, book.bookname
FROM Customer, Orders, Book
WHERE Customer.custid =Orders.custid AND Orders.bookid =Book.bookid;

-- ÁúÀÇ 3-26 °¡°ÝÀÌ 20,000¿øÀÎ µµ¼­¸¦ ÁÖ¹®ÇÑ °í°´ÀÇ À̸§°ú µµ¼­ÀÇ À̸§À» ±¸ÇϽÿÀ.
SELECT Customer.name, book.bookname
FROM Customer, Orders, Book
WHERE Customer.custid =Orders.custid AND Orders.bookid =Book.bookid
		AND Orders.saleprice =20000;
		
-- ÁúÀÇ 3-27 µµ¼­¸¦ ±¸¸ÅÇÏÁö ¾ÊÀº °í°´À» Æ÷ÇÔÇÏ¿© °í°´ÀÇ À̸§°ú °í°´ÀÌ ÁÖ¹®ÇÑ µµ¼­ÀÇ °¡°ÝÀ» ±¸ÇϽÿÀ.
SELECT Customer.name, saleprice
FROM Customer LEFT OUTER JOIN Orders ON Customer.custid =Orders.custid;	

-- ÁúÀÇ 3-28 °¡Àå ºñ½Ñ µµ¼­ÀÇ À̸§Àº ¹«¾ùÀΰ¡?
SELECT bookname
FROM Book
WHERE price = (SELECT MAX(price) 
	        FROM Book);
	        
-- ÁúÀÇ 3-29 µµ¼­¸¦ ±¸¸ÅÇÑ ÀûÀÌ ÀÖ´Â °í°´ÀÇ À̸§À» °Ë»öÇϽÿÀ.
SELECT name
FROM Customer
WHERE custid IN (SELECT custid
		 FROM Orders);	        

ÁúÀÇ 3-30 ´ëÇѹ̵ð¾î¿¡¼­ ÃâÆÇÇÑ µµ¼­¸¦ ±¸¸ÅÇÑ °í°´ÀÇ À̸§À» º¸À̽ÿÀ.
SELECT name
FROM Customer
WHERE custid IN (SELECT custid
		FROM Orders
		WHERE bookid IN (SELECT bookid
				FROM Book
				WHERE publisher='´ëÇѹ̵ð¾î'));
				
ÁúÀÇ 3-31 ÃâÆǻ纰·Î ÃâÆÇ»çÀÇ Æò±Õ µµ¼­ °¡°Ýº¸´Ù ºñ½Ñ µµ¼­¸¦ ±¸ÇϽÿÀ.
SELECT b1.bookname
FROM Book b1
WHERE b1.price > (SELECT avg(b2.price)
		  FROM Book b2
		  WHERE b2.publisher=b1.publisher);	
		  
-- ÁúÀÇ 3-32 µµ¼­¸¦ ÁÖ¹®ÇÏÁö ¾ÊÀº °í°´ÀÇ À̸§À» º¸À̽ÿÀ.
SELECT name
FROM Customer
EXCEPT
SELECT name
FROM Customer
WHERE custid IN (SELECT custid
		FROM Orders);	
		
-- ÁúÀÇ 3-33 ÁÖ¹®ÀÌ ÀÖ´Â °í°´ÀÇ À̸§°ú ÁÖ¼Ò¸¦ º¸À̽ÿÀ.
SELECT name, address
FROM Customer cs
WHERE EXISTS (SELECT *
		FROM Orders od
		WHERE cs.custid =od.custid);