Population Census

SELECT SUM(CITY.POPULATION)
FROM CITY JOIN COUNTRY ON CITY.COUNTRYCODE = COUNTRY.CODE
WHERE COUNTRY.CONTINENT = "Asia"

그냥 JOIN은 INNER JOIN이다. 조건을 만족하는 열들끼리 붙인다

African Cities

SELECT CITY.NAME
FROM CITY JOIN COUNTRY ON CITY.CountryCode = COUNTRY.Code
WHERE COUNTRY.CONTINENT = 'Africa'

Average Population of Each Continent

select country.continent, floor(AVG(city.population))
from city join country on CITY.CountryCode = COUNTRY.Code
group by country.continent

floor 함수

The Report

SELECT IF(GRADE < 8, NULL, NAME), GRADE, MARKS
FROM STUDENTS JOIN GRADES
WHERE MARKS BETWEEN MIN_MARK AND MAX_MARK
ORDER BY GRADE DESC, NAME

키 없는 그냥 조인은 카티션 조인?
IF랑 join?

그냥 join하면 각각의 경우에 다 붙는다

Top Competitors

select h.hacker_id, h.name
from submissions s
inner join challenges c
on s.challenge_id = c.challenge_id
inner join difficulty d
on c.difficulty_level = d.difficulty_level 
inner join hackers h
on s.hacker_id = h.hacker_id
where s.score = d.score and c.difficulty_level = d.difficulty_level
group by h.hacker_id, h.name
having count(s.hacker_id) > 1
order by count(s.hacker_id) desc, s.hacker_id asc

Ollivander's Inventory

select w.id, p.age, w.coins_needed, w.power 
from Wands as w join Wands_Property as p on (w.code = p.code) 
where p.is_evil = 0 and w.coins_needed = (select min(coins_needed) 
                                          from Wands as w1 join Wands_Property as p1 on (w1.code = p1.code) 
                                          where w1.power = w.power and p1.age = p.age) 
order by w.power desc, p.age desc

Challenges

. Write a query to print the hacker_id, name, and the total number of challenges created by each student. Sort your results by the total number of challenges in descending order. If more than one student created the same number of challenges, then sort the result by hacker_id. If more than one student created the same number of challenges and the count is less than the maximum number of challenges created, then exclude those students from the result.

/* these are the columns we want to output */
select c.hacker_id, h.name ,count(c.hacker_id) as c_count

/ this is the join we want to output them from /
from Hackers as h
inner join Challenges as c on c.hacker_id = h.hacker_id

/ after they have been grouped by hacker /
group by c.hacker_id

/ but we want to be selective about which hackers we output /
/ having is required (instead of where) for filtering on groups /
having

/* output anyone with a count that is equal to... */
c_count = 
    /* the max count that anyone has */
    (SELECT MAX(temp1.cnt)
    from (SELECT COUNT(hacker_id) as cnt
         from Challenges
         group by hacker_id
         order by hacker_id) temp1)

/* or anyone who's count is in... */
or c_count in 
    /* the set of counts... */
    (select t.cnt
     from (select count(*) as cnt 
           from challenges
           group by hacker_id) t
     /* who's group of counts... */
     group by t.cnt
     /* has only one element */
     having count(t.cnt) = 1)

/ finally, the order the rows should be output /
order by c_count DESC, c.hacker_id

/ ;) /
;

``` sql
/* these are the columns we want to output */
select c.hacker_id, h.name ,count(c.hacker_id) as c_count

/* this is the join we want to output them from */
from Hackers as h
    inner join Challenges as c on c.hacker_id = h.hacker_id

/* after they have been grouped by hacker */
group by c.hacker_id, h.name

/* but we want to be selective about which hackers we output */
/* having is required (instead of where) for filtering on groups */
having 

/* output anyone with a count that is equal to... */
c_count = 
    /* the max count that anyone has */
    (SELECT MAX(temp1.cnt)
    from (SELECT COUNT(hacker_id) as cnt
         from Challenges
         group by hacker_id
         order by hacker_id) temp1)

/* or anyone who's count is in... */
or c_count in 
    /* the set of counts... */
    (select t.cnt
     from (select count(*) as cnt 
           from challenges
           group by hacker_id) t
     /* who's group of counts... */
     group by t.cnt
     /* has only one element */
     having count(t.cnt) = 1)

/* finally, the order the rows should be output */
order by c_count DESC, c.hacker_id

/* ;) */
;

Contest Leaderboard

select h.hacker_id, h.name, sum(score) as t_score
from hackers as h inner join (select hacker_id, max(score) as score
							 from submissions
                             group by challenge_id, hacker_id) as max_score
                             on h.hacker_id = max_score.hacker_id
group by h.hacker_id, name
having t_score > 0
order by t_score desc, h.hacker_id asc;

select h.hacker_id, name, sum(score) as total_score
from
hackers as h inner join
/* find max_score*/
(select hacker_id,  max(score) as score from submissions group by challenge_id, hacker_id) max_score

on h.hacker_id=max_score.hacker_id
group by h.hacker_id, name

/* don't accept hackers with total_score=0 */
having total_score > 0

/* finally order as required */
order by total_score desc, h.hacker_id
;