MySQL 연습문제1

use world;
SELECT Continent
FROM country
GROUP BY Continent;

use sakila;
select country.country, COUNT(country.country_id)
from customer, address, city, country
where customer.address_id = address.address_id AND address.city_id = city.city_id AND city.country_id = country.country_id AND country.country = "India"
GROUP BY country.country_id

use world;
select fcity.name, fcity.population
from (select city.name, city.countrycode, city.population from city where city.population > 1000000) as fcity, country
WHERE fcity.countrycode = country.code AND country.name = "south korea"
ORDER BY fcity.population DESC;

use world;
select name, countrycode, population
from city
where population BETWEEN 8000000 and 10000000
ORDER BY population DESC;

use world;
SELECT code, CONCAT(name, "(", IndepYear, ")") as NameIndep, continent, Population
FROM country
where IndepYear BETWEEN 1940 AND 1950
ORDER BY IndepYear ASC

use world;
select countrycode, language, percentage
from countrylanguage
where (Language = "english" OR Language = "korean" OR Language = "spanish") AND percentage >= 95
ORDER BY percentage DESC

use world;
select code, name, continent, GovernmentForm, Population
from country
WHERE code LIKE "A%" and GovernmentForm LIKE "%Republic%"

use sakila;
select first_name, count(first_name) as count
from actor
where first_name = "DAN"
GROUP BY first_name

use sakila;
select film_id, title, description
from film_text
where title LIKE "%ICE%" and description LIKE "%Drama%"

use sakila;
select title, description, category, length, price
from film_list
where (price BETWEEN 1 AND 4) AND (length >= 180) AND (category != "Sci-Fi" OR category != "Animation" )