{"id":7454,"date":"2024-09-24T11:33:29","date_gmt":"2024-09-24T11:33:29","guid":{"rendered":"https:\/\/pohang.eduwp.kr\/?p=7454"},"modified":"2026-04-20T04:31:03","modified_gmt":"2026-04-20T04:31:03","slug":"sakila-db%ec%97%b0%ec%8a%b5","status":"publish","type":"post","link":"https:\/\/pohang.eduwp.kr\/?p=7454","title":{"rendered":"7-1\uc8fc\ucc28 sakila DB\uc5f0\uc2b5(\uc911\uac04\uace0\uc0ac \ub300\ube44)"},"content":{"rendered":"<ol>\n<li><a href=\"https:\/\/pohang.eduwp.kr\/wp-content\/uploads\/2024\/09\/sakila-db.zip\">db\ub2e4\uc6b4\ubc1b\uae30<\/a><\/li>\n<li>\uc124\uce58<\/li>\n<\/ol>\n<p>&nbsp;<\/p>\n<p>\ucd08\uae30 \uba85\ub839\uc5b4<\/p>\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"generic\">use sakila;\r\n\r\nshow tables;\r\n\r\nshow table status;\r\n\r\ndesc film;<\/pre>\n<p>&nbsp;<\/p>\n<p>\uc601\ud654\uac00 \ucd1d \uba87\ud3b8\uc778\uac00\uc694? \ubc30\uc6b0\uc22b\uc790\ub294 \uba87\uba85\uc778\uac00\uc694? \uc601\ud654\uce74\ud14c\uace0\ub9ac\ub294 \uba87\uac1c\uc778\uac00? \ub4f1\ub4f1 \uc22b\uc790\ub97c \uccb4\ud06c\ud558\uae30<\/p>\n<p>select count(*) \uc751\uc6a9\ud558\uae30<\/p>\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"generic\">select count(*) from film;<\/pre>\n<p>&nbsp;<\/p>\n<p>\uc601\ud654\ub4f1\uae09 \uc885\ub958 \ubaa8\ub450 \uc801\uae30<\/p>\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"generic\">select distinct rating from film;<\/pre>\n<p>&nbsp;<\/p>\n<p>\uc601\ud654 \ud14c\uc774\ube14 film\uc5d0\uc11c \uc601\ud654 release \uc5f0\ub3c4 \uc885\ub958 \uc54c\uc544\ub0b4\uae30, \uac01 \uc601\ud654\uc758 release\uc5f0\ub3c4\ub294 release_year \uceec\ub7fc\uc5d0 \uc788\uc74c. (distinct \ud65c\uc6a9)<\/p>\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"generic\">select distinct release_year from film;<\/pre>\n<p>&nbsp;<\/p>\n<p>\uc601\ud654 \ub80c\ud0c8 \ud14c\uc774\ube14\uc5d0\uc11c 10\uac1c \ub370\uc774\ud130\ub9cc \ucd9c\ub825\ud558\uae30, rental \ud14c\uc774\ube14\uc740 dvd\uc758 \ub80c\ud0c8\uc815\ubcf4\uac00 \ub2f4\uaca8\uc788\uc74c<\/p>\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"generic\">select * from rental limit 10;<\/pre>\n<p>&nbsp;<\/p>\n<p>\ub80c\ud0c8 \ud14c\uc774\ube14\uc5d0\uc11c inventory_id\uac00 367\uc778 row \uc804\uccb4 \ucd9c\ub825\ud558\uae30<\/p>\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"generic\">select * from rental where inventory_id = 367;<\/pre>\n<p>&nbsp;<\/p>\n<p>\ub80c\ud0c8 \ud14c\uc774\ube14\uc5d0\uc11c inventory_id\uac00 367, staff_id\uac00 1\uc778\u00a0 row \uc804\uccb4 \ucd9c\ub825\ud558\uae30<\/p>\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"generic\">select * from rental where inventory_id = 367 and staff_id = 1;<\/pre>\n<p>&nbsp;<\/p>\n<p>customer \ud14c\uc774\ube14\uc5d0\uc11c cuntomer \uc218 \uc54c\uc544\ub0b4\uae30<\/p>\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"generic\">select count(*) from customer;<\/pre>\n<p>&nbsp;<\/p>\n<p>paymemt \ud14c\uc774\ube14\uc5d0\uc11c \ub80c\ud0c8\ube44\uc6a9 \ud569\uacc4, \ud3c9\uade0, \ucd5c\ub300\uac12, \ucd5c\uc18c\uac12 \uad6c\ud558\uae30<\/p>\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"generic\">select * from payment;\r\n\r\nselect sum(amount), AVG(amount), max(amount), min(amount) from payment;\r\n\r\n\r\nselect payment_id, (amount), AVG(amount), max(amount), min(amount) from payment GROUP BY payment_id;<\/pre>\n<p>&nbsp;<\/p>\n<p>\uc601\ud654(film table)\uc5d0 \ub9e4\uaca8\uc9c4 \ub4f1\uae09(rating) \uc885\ub958\ub97c \ubaa8\ub450 \ucd9c\ub825\ud558\uc2dc\uc694 (group by \uc774\uc6a9) distinct \uc640 group by\uc758 \ucc28\uc774\uc810 \uc54c\uae30<\/p>\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"generic\">SELECT rating from film GROUP BY rating;<\/pre>\n<p>&nbsp;<\/p>\n<p>\uc601\ud654(film table)\uc5d0 \ub9e4\uaca8\uc9c4 \ub4f1\uae09 (rating) \uc885\ub958\uc5d0 \ub530\ub978 \uc601\ud654 \uac2f\uc218\ub97c \ubaa8\ub450 \ucd9c\ub825\ud558\uc138\uc694 (rating\uac12\uacfc \uac01 rating \uac12\uc5d0 \ub530\ub978 \uc601\ud654 \uac2f\uc218\ub97c \ucd9c\ub825\ud558\uc138\uc694)<\/p>\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"generic\">select rating, count(*) from film GROUP BY rating;<\/pre>\n<p>&nbsp;<\/p>\n<p>\uc601\ud654(film table)\uc5d0 \uc601\ud654\uac00 PG\ub610\ub294 G\ub4f1\uae09\uc758 \uc601\ud654 \uc218\ub97c \uac01 \ub4f1\uae09\ubcc4\ub85c \ucd9c\ub825\ud558\uc138\uc694\u00a0 (rating\uac12\uacfc \uac01 rating \uac12\uc5d0 \ub530\ub978 \uc601\ud654\u00a0 \uac2f\uc218\ub97c \ucd9c\ub825\ud558\uc138\uc694)<\/p>\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"generic\">select rating, count(*) from film where rating = 'PG' or rating ='G' GROUP BY rating;<\/pre>\n<p>&nbsp;<\/p>\n<p>\uc601\ud654(film table)\uc5d0\uc11c \uc601\ud654\uac00 PG \ub610\ub294 G \ub4f1\uae09\uc778 \uc601\ud654 \uc81c\ubaa9\uc744 \ucd9c\ub825\ud558\uc138\uc694<\/p>\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"generic\">select title from film where rating ='PG' or rating='G' group by rating;<\/pre>\n<p>&nbsp;<\/p>\n<p>\uc601\ud654(film table)\uc5d0\uc11c release \uc5f0\ub3c4\uac00 2006 \ub610\ub294 2007 \uc5f0\ub3c4\uc774\uace0, \uc601\ud654\uac00 PG \ub610\ub294 G \ub4f1\uae09\uc778 \uc601\ud654 \uc81c\ubaa9\uacfc \ub4f1\uae09\uc744\u00a0 \ucd9c\ub825\ud558\uc138\uc694<\/p>\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"generic\">select title from film where release_year =2006 or release_year = 2007;\r\n\r\nselect title, rating from film where (release_year =2006 or release_year = 2007) and (rating ='G' or rating='PG');<\/pre>\n<p>&nbsp;<\/p>\n<p>film\ud14c\uc774\ube14\uc5d0\uc11c rating (\ub4f1\uae09)\uc73c\ub85c \uadf8\ub8f9\uc744 \ubb36\uc5b4\uc11c, \uac01 \ub4f1\uae09\ubcc4 \uc601\ud654 \uac2f\uc218 \ucd9c\ub825\ud558\uae30 (\uac01 \ub4f1\uae09\ubcc4 \uac2f\uc218 \ucd9c\ub825\ud558\uae30)<\/p>\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"generic\">select count(*) from film GROUP BY rating;<\/pre>\n<p>&nbsp;<\/p>\n<p>film\ud14c\uc774\ube14\uc5d0\uc11c rating (\ub4f1\uae09)\uc73c\ub85c \uadf8\ub8f9\uc744 \ubb36\uc5b4\uc11c, \uac01 \ub4f1\uae09\ubcc4 \uc601\ud654 \uac2f\uc218 \ucd9c\ub825\ud558\uae30 (\ub4f1\uae09\uacfc \uac01 \ub4f1\uae09\ubcc4 \uac2f\uc218 \ucd9c\ub825\ud558\uae30)<\/p>\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"generic\">select rating, count(*) from film GROUP BY rating;\r\n\r\nselect rating as '\ub4f1\uae09' , count(*) as '\uac2f\uc218' from film GROUP BY rating;<\/pre>\n<p>&nbsp;<\/p>\n<p>&nbsp;<\/p>\n<p>film\ud14c\uc774\ube14\uc5d0\uc11c rating (\ub4f1\uae09)\uc73c\ub85c \uadf8\ub8f9\uc744 \ubb36\uc5b4\uc11c, \uac01 \ub4f1\uae09\ubcc4 \uc601\ud654 \uac2f\uc218\uc640 \uac01 \ub4f1\uae09\ubcc4 \ud3c9\uade0 \ub80c\ud0c8 \ube44\uc6a9 \ucd9c\ub825\ud558\uae30 (\ub4f1\uae09\uacfc \uac01 \ub4f1\uae09\ubcc4 \uac2f\uc218, \uac01 \ub4f1\uae09\ubcc4 \ud3c9\uade0 \ub80c\ud0c8 \ube44\uc6a9 \ucd9c\ub825\ud558\uae30)<\/p>\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"generic\">select rating as '\ub4f1\uae09' , count(*) as '\uac2f\uc218', avg(rental_rate) from film GROUP BY rating;<\/pre>\n<p>&nbsp;<\/p>\n<p>film\ud14c\uc774\ube14\uc5d0\uc11c rating (\ub4f1\uae09)\uc73c\ub85c \uadf8\ub8f9\uc744 \ubb36\uc5b4\uc11c, \uac01 \ub4f1\uae09\ubcc4 \uc601\ud654 \uac2f\uc218\uc640 \uac01 \ub4f1\uae09\ubcc4 \ud3c9\uade0 \ub80c\ud0c8 \ube44\uc6a9 \ucd9c\ub825\ud558\uae30, \ub2e8 \ud3c9\uade0 \ub80c\ud0c8\ube44\uc6a9\uc774 \ub192\uc740 \uc21c\uc73c\ub85c \ucd9c\ub825\ud558\uae30 (\ub4f1\uae09\uacfc \uac01 \ub4f1\uae09\ubcc4 \uac2f\uc218, \uac01 \ub4f1\uae09\ubcc4 \ud3c9\uade0 \ub80c\ud0c8 \ube44\uc6a9 \ucd9c\ub825\ud558\uae30)<\/p>\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"generic\">SELECT rating , COUNT(*) , AVG(rental_rate) FROM film GROUP BY rating ORDER BY avg(rental_rate) DESC;<\/pre>\n<p>&nbsp;<\/p>\n<p>film\ud14c\uc774\ube14\uc5d0\uc11c rating (\ub4f1\uae09)\uc73c\ub85c \uadf8\ub8f9\uc744 \ubb36\uc5b4\uc11c, \uac01 \ub4f1\uae09\ubcc4 \uc601\ud654 \uac2f\uc218\uc640 rating (\ub4f1\uae09), \uac01 \uadf8\ub8f9\ubcc4 \ud3c9\uade0 rental_rate (\ub80c<br \/>\n\ud0c8 \ube44\uc6a9) \ucd9c\ub825\ud558\ub418, \uc601\ud654 \uac2f\uc218\uc640 \ud3c9\uade0 \ub80c\ud0c8 \ube44\uc6a9\uc740 \uac01\uac01 total_films, avg_rental_rate \uc73c\ub85c \ucd9c\ub825\ud558\uace0, avg_rental_rate<br \/>\n\uc774 \ub192\uc740 \uc21c\uc73c\ub85c \ucd9c\ub825\ud558\uc2dc\uc624<\/p>\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"generic\">SELECT rating , COUNT(*) as total_films , AVG(rental_rate) as avg_rental_rate\r\nFROM film\r\nGROUP BY rating\r\nORDER BY AVG(rental_rate) DESC;\r\n\r\n\r\n\r\n\r\nSELECT rating , COUNT(*) as 'total_films' , AVG(rental_rate) as 'avg_rental_rate'\r\nFROM film\r\nGROUP BY rating\r\nORDER BY avg_rental_rate DESC;<\/pre>\n<p>&nbsp;<\/p>\n<p>film\ud14c\uc774\ube14\uc5d0\uc11c rating (\ub4f1\uae09)\uc73c\ub85c \uadf8\ub8f9\uc744 \ubb36\uc5b4\uc11c, \uac01 \ub4f1\uae09\ubcc4 \uc601\ud654 \uac2f\uc218\uc640 rating (\ub4f1\uae09), \uac01 \uadf8\ub8f9\ubcc4 \ud3c9\uade0 rental_rate (\ub80c<br \/>\n\ud0c8 \ube44\uc6a9) \ucd9c\ub825\ud558\ub418, \uc601\ud654 \uac2f\uc218\uc640 \ud3c9\uade0 \ub80c\ud0c8 \ube44\uc6a9\uc740 \uac01\uac01 total_films, avg_rental_rate \uc73c\ub85c \ucd9c\ub825\ud558\uace0, avg_rental_rate<br \/>\n\uc774 \ub192\uc740 \uc21c\uc73c\ub85c \ucd9c\ub825\ud558\uc2dc\uc624 (SQL \uad6c\ubb38\uc744 \ubcf4\uae30 \uc88b\uac8c \uc5ec\ub7ec \uc904\uc5d0 \uac78\uccd0\uc11c \uc368\ubcf4\uae30)<\/p>\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"generic\">SELECT\r\n     rating,\r\n     COUNT(*) AS 'total_films',\r\n     AVG(rental_rate) AS 'avg_rental_rate'\r\nFROM \r\n     film\r\nGROUP BY rating\r\nORDER BY avg_rental_rate DESC;<\/pre>\n<p>&nbsp;<\/p>\n<p>&nbsp;<\/p>\n<p>\uac01 \ub4f1\uae09\ubcc4 \uc601\ud654 \uae38\uc774\uac00 130\ubd84 \uc774\uc0c1\uc778 \uc601\ud654\uc758 \uac2f\uc218\uc640 \ub4f1\uae09\uc744 \ucd9c\ub825\ud574\ubcf4\uc138\uc694<\/p>\n","protected":false},"excerpt":{"rendered":"<p>db\ub2e4\uc6b4\ubc1b\uae30 \uc124\uce58 &nbsp; \ucd08\uae30 \uba85\ub839\uc5b4 use sakila; show tables; show table status; desc film; &nbsp; \uc601\ud654\uac00 \ucd1d \uba87\ud3b8\uc778\uac00\uc694? \ubc30\uc6b0\uc22b\uc790\ub294 \uba87\uba85\uc778\uac00\uc694? \uc601\ud654\uce74\ud14c\uace0\ub9ac\ub294 \uba87\uac1c\uc778\uac00? \ub4f1\ub4f1 \uc22b\uc790\ub97c \uccb4\ud06c\ud558\uae30 select count(*) \uc751\uc6a9\ud558\uae30 select count(*)&#8230;<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[30],"tags":[],"class_list":["post-7454","post","type-post","status-publish","format-standard","category-mysql"],"_links":{"self":[{"href":"https:\/\/pohang.eduwp.kr\/index.php?rest_route=\/wp\/v2\/posts\/7454","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/pohang.eduwp.kr\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/pohang.eduwp.kr\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/pohang.eduwp.kr\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/pohang.eduwp.kr\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=7454"}],"version-history":[{"count":6,"href":"https:\/\/pohang.eduwp.kr\/index.php?rest_route=\/wp\/v2\/posts\/7454\/revisions"}],"predecessor-version":[{"id":7511,"href":"https:\/\/pohang.eduwp.kr\/index.php?rest_route=\/wp\/v2\/posts\/7454\/revisions\/7511"}],"wp:attachment":[{"href":"https:\/\/pohang.eduwp.kr\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=7454"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/pohang.eduwp.kr\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=7454"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/pohang.eduwp.kr\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=7454"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}