SELECT C.CONTINENT, MAX(C.POPULATION) AS MAXPOPULATION FROM COUNTRY C
WHERE POPULATION NOT IN (0)
GROUP BY C.CONTINENT

SELECT C.NAME, C.CONTINENT, C.POPULATION FROM COUNTRY C
JOIN
(
SELECT C.CONTINENT, MAX(C.POPULATION) AS MAXPOPULATION FROM COUNTRY C
WHERE POPULATION NOT IN (0)
GROUP BY C.CONTINENT HAVING C.CONTINENT LIKE "%AMERICA%"
)J ON J.MAXPOPULATION = C.POPULATION AND J.CONTINENT = C.CONTINENT

SELECT M.MOVIE_NM, M.OPEN_DT, M.SHOW_TM FROM MOVIE M
WHERE M.SHOW_TM > ANY
(
SELECT M.SHOW_TM FROM MOVIE M
JOIN (SELECT * FROM STARS_IN SI WHERE SI.PEOPLE_NM = '신세경') J
ON J.MOVIE_CD = M.MOVIE_CD
)
AND M.REP_GENRE_NM = '드라마' AND YEAR(M.OPEN_DT) >= 2019

SELECT MP.SEX, COUNT(*) FROM MOVIE_PEOPLE MP
JOIN
(
SELECT * FROM STARS_IN SI
WHERE MOVIE_CD = (SELECT M.MOVIE_CD FROM MOVIE M WHERE M.MOVIE_NM = '마녀')
) J
ON MP.PEOPLE_CD = J.PEOPLE_CD
GROUP BY MP.SEX

JOIN
(SELECT * FROM MOVIE) J1
ON J1.MOVIE_CD = WL.MOVIE_CD
JOIN
(SELECT DM.ID, DM.REG_DATE FROM D_DIVER.MEMBER DM) J2
ON J2.ID = WL.ID
GROUP BY WL.ID
ORDER BY J2.REG_DATE DESC

SELECT J.MOVIE_NM, J.VIEWS, COUNT(J.MOVIE_CD), J.PRDT_DATE FROM STARS_IN SI
JOIN
(
SELECT * FROM MOVIE M
WHERE M.VIEWS BETWEEN 10 AND 20
) J
ON J.MOVIE_CD = SI.MOVIE_CD
GROUP BY J.MOVIE_CD
ORDER BY J.PRDT_DATE DESC

SELECT FT.TITLE, FT.DESCRIPTION, J.인기도 FROM FILM_TEXT FT
JOIN
(
SELECT FC.film_ID,
(CASE
WHEN FC.CATEGORY_ID < 10 THEN '인기장르'
ELSE '비주류 장르'
END) AS '인기도'
FROM FILM_CATEGORY FC
)J ON FT.FILM_ID = J.FILM_ID
WHERE FT.TITLE LIKE "Z%"
ORDER BY FT.TITLE

SELECT P.CUSTOMER_ID, SUM(P.AMOUNT), P.LAST_UPDATE FROM PAYMENT P
GROUP BY P.CUSTOMER_ID HAVING SUM(P.AMOUNT) < 60

SELECT M.MOVIE_NM, J.PEOPLE_NM, '남자' FROM MOVIE M
JOIN
(
SELECT * FROM STARS_IN SI
WHERE PEOPLE_CD IN (SELECT MP.PEOPLE_CD FROM MOVIE_PEOPLE MP WHERE MP.SEX = '남자')
) J
ON J.MOVIE_CD = M.movie_cd
WHERE M.REP_NATION_NM = '한국'
ORDER BY J.PEOPLE_NM

SELECT MP.PEOPLE_CD, MP.PEOPLE_NM FROM MOVIE_PEOPLE MP
JOIN
(
SELECT * FROM MOVIE_PEOPLE MP
GROUP BY MP.PEOPLE_NM
HAVING COUNT(MP.PEOPLE_NM) > 1
) J ON MP.PEOPLE_NM = J.PEOPLE_NM
ORDER BY MP.PEOPLE_NM

SELECT DISTINCT J.COMPANY_NM FROM STARS_IN SI
JOIN
(SELECT * FROM companies_in CI WHERE CI.company_Part_Nm = '배급사') J
ON J.MOVIE_CD = SI.MOVIE_CD
WHERE SI.PEOPLE_NM = '마동석' OR SI.PEOPLE_NM = '김태훈'
ORDER BY J.COMPANY_NM

SELECT SI.PEOPLE_NM, COUNT(SI.PEOPLE_NM) AS '출연횟수' FROM STARS_IN SI
JOIN
(SELECT * FROM MOVIE_PEOPLE MP WHERE MP.SEX = '남자') J
ON J.PEOPLE_CD = SI.PEOPLE_CD
GROUP BY SI.PEOPLE_NM
ORDER BY 출연횟수 DESC

CREATE TABLE DRAMA_MOVIE
SELECT M.MOVIE_NM, M.REP_NATION_NM, M.REP_GENRE_NM, M.WATCH_GRADE_NM FROM MOVIE M

SELECT M.REP_GENRE_NM, COUNT(*) FROM MOVIE M
JOIN
(
SELECT * FROM MOVIE M
WHERE YEAR(M.OPEN_DT) > 2014 AND M.SHOW_TM > 120 AND M.REP_NATION_NM = '미국'
) J
ON J.MOVIE_CD = M.MOVIE_CD
GROUP BY M.REP_GENRE_NM
ORDER BY M.REP_GENRE_NM

SELECT * FROM MOVIE M
WHERE M.MOVIE_CD IN (SELECT SI.MOVIE_CD FROM stars_in SI WHERE SI.PEOPLE_NM = '신세경')

SELECT COUNT(*) FROM stars_in SI
WHERE SI.PEOPLE_NM = '신세경'

SELECT C.CODE, C.NAME, J.LANGUAGE, C.GNP,
(CASE WHEN C.GNP > 1000 THEN '잘사네' ELSE '못사네' END) AS 소득수준
FROM COUNTRY C
JOIN (SELECT * FROM COUNTRYLANGUAGE CL WHERE CL.LANGUAGE = 'ENGLISH') J
ON C.CODE = J.COUNTRYCODE

SELECT P.CUSTOMER_ID, J.FIRST_NAME, SUM(P.AMOUNT) FROM PAYMENT P
JOIN (SELECT * FROM CUSTOMER C WHERE C.FIRST_NAME LIKE 'A___') J
ON J.CUSTOMER_ID = P.CUSTOMER_ID
GROUP BY P.CUSTOMER_ID

SELECT J1.REP_ROLE_NM, COUNT(*) FROM STARS_IN SI
JOIN(SELECT * FROM MOVIE_PEOPLE MP WHERE MP.PEOPLE_NM = '강능원') J1
ON J1.PEOPLE_CD = SI.PEOPLE_CD
JOIN(SELECT * FROM STARS_IN SI) J2
ON J2.MOVIE_CD = SI.MOVIE_CD
GROUP BY J1.REP_ROLE_NM

SELECT M.REP_NATION_NM, COUNT(DISTINCT M.MOVIE_NM) FROM MOVIE M
JOIN (SELECT * FROM STARS_IN SI WHERE SI.CAST IS NOT NULL) J
ON M.MOVIE_CD = J.MOVIE_CD
WHERE M.SHOW_TM >= 100
GROUP BY REP_NATION_NM

SELECT customer.first_name, customer.last_name, address.district, address.last_update FROM customer
JOIN address ON customer.address_id = address.address_id
GROUP BY address.district
ORDER BY last_update DESC;

SELECT film.title, film.rating, film.rental_rate FROM film
JOIN film_category ON film.film_id = film_category.film_id
WHERE film_category.category_id IN (SELECT category.category_id FROM category WHERE category.name = 'COMEDY')
ORDER BY film.rental_rate DESC;

SELECT DISTINCT CI.COMPANY_NM FROM COMPANIES_IN CI
JOIN (SELECT * FROM STARS_IN SI WHERE SI.PEOPLE_NM = '유해진') J
ON J.MOVIE_CD = CI.MOVIE_CD
WHERE CI.COMPANY_PART_NM = '배급사'

SELECT M.REP_GENRE_NM, COUNT(*) FROM MOVIE M
JOIN (SELECT * FROM STARS_IN SI WHERE SI.PEOPLE_NM = '이정현') J
ON J.MOVIE_CD = M.MOVIE_CD
GROUP BY M.REP_GENRE_NM

SELECT M.PRDT_DATE, COUNT(*) FROM MOVIE M
JOIN
(
SELECT M.MOVIE_NM, M.PRDT_DATE FROM MOVIE M WHERE M.PRDT_DATE > (YEAR(NOW()) - 10)
)J ON J.MOVIE_NM = M.MOVIE_NM
WHERE M.REP_NATION_NM = '이탈리아' AND M.SHOW_TM > 120
GROUP BY M.PRDT_DATE

SELECT M.REP_GENRE_NM, COUNT(DISTINCT MOVIE_NM) FROM MOVIE M
JOIN
(
SELECT SI.MOVIE_CD FROM STARS_IN SI
JOIN (SELECT MP.PEOPLE_CD FROM MOVIE_PEOPLE MP WHERE MP.SEX = '남자') J1
ON SI.PEOPLE_CD = J1.PEOPLE_CD
) J2 ON J2.MOVIE_CD = M.MOVIE_CD
WHERE M.WATCH_GRADE_NM = '청소년관람불가' AND M.SHOW_TM < 100 AND M.REP_NATION_NM = '한국'
GROUP BY M.REP_GENRE_NM

SELECT SUM(M.SHOW_TM) FROM MOVIE M
WHERE M.WATCH_GRADE_NM = '청소년관람불가'

SELECT COUNT(*) FROM (SELECT * FROM STARS_IN SI UNION SELECT * FROM MOVIE_PEOPLE MP) P

SELECT M.REP_GENRE_NM, AVG(M.SHOW_TM) FROM MOVIE M
JOIN
(
SELECT SI.MOVIE_CD FROM STARS_IN SI
WHERE SI.PEOPLE_CD IN (SELECT MP.PEOPLE_CD FROM MOVIE_PEOPLE MP WHERE MP.SEX = "남자" AND MP.REP_ROLE_NM = "배우")
) J
ON J.MOVIE_CD = M.MOVIE_CD
GROUP BY M.REP_GENRE_NM

SELECT DISTINCT M.MOVIE_NM, J.COMPANY_NM ,M.SHOW_TM FROM MOVIE M
JOIN (SELECT CI.MOVIE_CD, CI.COMPANY_NM FROM COMPANIES_IN CI) J
ON J.MOVIE_CD = M.MOVIE_CD
WHERE M.SHOW_TM = (SELECT MIN(M.SHOW_TM) AS 'MIN TIME' FROM MOVIE M WHERE REP_GENRE_NM = "애니메이션")

SELECT COUNT(*) FROM STARS_IN SI
JOIN(SELECT * FROM MOVIE M WHERE M.MOVIE_NM LIKE "%고양이%") J
ON J.MOVIE_CD = SI.MOVIE_CD
WHERE YEAR(J.PRDT_DATE) >= 2015

SELECT M.MOVIE_NM, M.REP_GENRE_NM, M.OPEN_DT, M.REP_NATION_NM, M.SHOW_TM FROM MOVIE M
JOIN
(
SELECT MAX(M.SHOW_TM) AS MAX_SHOW_TM, M.REP_GENRE_NM  FROM MOVIE M
WHERE YEAR(M.OPEN_DT) = 2018
GROUP BY M.REP_GENRE_NM
) J
ON J.MAX_SHOW_TM = M.SHOW_TM AND M.REP_GENRE_NM = J.REP_GENRE_NM
ORDER BY M.SHOW_TM DESC

SELECT CI.COMPANY_NM, COUNT(CI.COMPANY_NM) AS COUNT FROM COMPANIES_IN CI
JOIN (SELECT * FROM MOVIE M WHERE YEAR(M.PRDT_DATE) BETWEEN 2016 AND 2018) J
ON CI.MOVIE_CD = J.MOVIE_CD
WHERE CI.COMPANY_PART_NM = "제작사"
GROUP BY CI.COMPANY_NM
HAVING COUNT(*) >= 3
ORDER BY COUNT DESC

SELECT M.MOVIE_NM, M.SHOW_TM, M.REP_NATION_NM FROM MOVIE M
WHERE M.SHOW_TM < (SELECT MIN(M.SHOW_TM) FROM MOVIE M WHERE M.REP_NATION_NM = "독일")

SELECT M.MOVIE_NM, M.SHOW_TM, M.REP_NATION_NM FROM MOVIE M
WHERE M.SHOW_TM > ALL
(
SELECT M.SHOW_TM FROM MOVIE M
JOIN
(SELECT CI.MOVIE_CD FROM COMPANIES_IN CI WHERE CI.COMPANY_NM = '어뮤즈') J
ON J.MOVIE_CD = M.MOVIE_CD
)
ORDER BY M.SHOW_TM DESC

SELECT M.REP_GENRE_NM, SUM(M.SHOW_TM) FROM MOVIE M
GROUP BY M.REP_GENRE_NM
ORDER BY SUM(M.SHOW_TM)

SELECT M.REP_GENRE_NM,
SUM(CASE WHEN YEAR(M.OPEN_DT) BETWEEN 2017 AND 2019 THEN 1 END) AS 'NEW',
SUM(CASE WHEN YEAR(M.OPEN_DT) BETWEEN 2014 AND 2016 THEN 1 END) AS 'NEWOLD',
SUM(CASE WHEN YEAR(M.OPEN_DT) < 2014 THEN 1 END) AS 'OLD'
FROM MOVIE M
GROUP BY M.REP_GENRE_NM