SQL Challenge - Game of Thrones

_got_challenge.md

Game of Thrones Challenge

Load the two query files into a PostgreSQL client.

Load CSV both files into the created tables

Perform the required queries. Fill in the query language below and the result (if applicable)

Questions

1. Show all rows from the battles table
   

SELECT * FROM battles;

2. Show all rows from the deaths table

SELECT * FROM battles;

3. What is the maximum death year from the deaths table?

4. From the deaths table, group by allegiances. Find out the count of each allegiance. The result should look something like this:
   

5. Order your result from question 4 by allegiance alphabetically.

The result should look something like this:

6. From the battles table, find the count of battles per region. (Hint, another group by is needed.) The result should look like this:
   

7. From your result in 6, order by count in descending order. Result should look like this:

8. Find the attacker_commander that appears most in the battles data.

9. Find the attacker_commander that appears least in the battles data.

10. Find the average defender size in the battles data.

11. Find the average attacker size in the battles data.

12. From the battles table, group by attacker_1. Show the count of attacker_1 sub-groups.

Result should look something like this:

13. Order the result from Question 12 by ascending order of count.

14. Using aliases, and your query from Question 13, rename count and attacker_1 in the SELECT line to num_attackers, and attacker. Additionally, only show counts greater than 3.

Result should look like this:

battles.csv

battles.sql

CREATE TABLE battles (
    name character varying(52) NOT NULL,
    year integer NOT NULL,
    battle_number integer PRIMARY KEY,
    attacker_king character varying(24),
    defender_king character varying(24),
    attacker_1 character varying(27) NOT NULL,
    attacker_2 character varying(9),
    attacker_3 character varying(7),
    attacker_4 character varying(6),
    defender_1 character varying(16),
    defender_2 character varying(9),
    defender_3 character varying(30),
    defender_4 character varying(30),
    attacker_outcome character varying(4),
    battle_type character varying(14),
    major_death integer,
    major_capture integer,
    attacker_size integer,
    defender_size integer,
    attacker_commander character varying(95),
    defender_commander character varying(109),
    summer integer,
    location character varying(36),
    region character varying(15) NOT NULL
);

deaths.csv

deaths.sql

CREATE TABLE deaths (
    name character varying(33),
    allegiances character varying(15),
    death_year integer,
    book_of_death integer,
    death_chapter integer,
    book_intro_chapter integer,
    gender integer,
    nobility integer,
    got integer,
    cok integer,
    sos integer,
    ffc integer,
    dwd integer
);