anitaokoh

#Converting back to a dictionary
top10_dict = dict(toplist)
top10_dict

anitaokoh

# To isloate just the words and puting them into a list
uniqueword10 = list(top10_dict.keys())
uniqueword10

anitaokoh

#To visualize the words and values
import matplotlib.pyplot as plt
import numpy as np
%matplotlib inline

so_axis = np.arange(len(uniqueword10))
plt.xticks(so_axis, uniqueword10)
plt.bar(so_axis, top10_dict.values())
plt.show()

anitaokoh

/* Marvel Heroes and Villains
 Based on the website http://marvel.wikia.com/Main_Page
 with popularity data from http://observationdeck.io9.com/something-i-found-marvel-character-popularity-poll-cb-1568108064 
 and power grid data from http://marvel.wikia.com/Power_Grid#Power
 Collected by: https://www.khanacademy.org/profile/Mentrasto/
 */

CREATE TABLE marvels (ID INTEGER PRIMARY KEY,
    name TEXT,
    popularity INTEGER,

anitaokoh

/*
Sales from an online furniture store
Collected by: https://www.khanacademy.org/profile/charlesb2000/
*/
CREATE TABLE sales(
  ID INTEGER  NOT NULL PRIMARY KEY
, transaction_date TEXT
, product TEXT
, price INTEGER
, payment_type TEXT

anitaokoh

/*
Winston's Donut logs
This table of logs shows how many donuts Winston eats at each year of his life,
plus any particular reason to explain his eating habits.
Collected by: https://www.khanacademy.org/profile/mhogwarts/
 */
CREATE TABLE winstons_donut_logs (
	id TEXT PRIMARY KEY,
    status TEXT,
    years_old INTEGER,

anitaokoh

/* To get an overview of the data */

SELECT *
FROM marvels
LIMIT 5;

anitaokoh

/* First, calculate the averages of the abilities and score each character based on if they are above the averages or not */
WITH power_average AS (SELECT Name, CASE
                                      WHEN intelligence > ( SELECT  AVG(intelligence)  FROM  marvels) THEN 1
                                      ELSE 0
                                     END AS intelligence,
                                    CASE
                                        WHEN Strength > ( SELECT  AVG(Strength)  FROM  marvels) THEN 1
                                        ELSE 0
                                    END AS Strength,
                                    CASE

anitaokoh

/* BONUS */
/* Calculate the BMI of each character and categorize them based on obese, healthy etc */

SELECT CASE
         WHEN height_m * weight_kg > 30 then 'Obese'
         WHEN height_m * weight_kg > 25 then 'Overweight'
         WHEN height_m * weight_kg then 'Healthy'
         ELSE 'thin'
       END as category, count(*) AS total

anitaokoh

SELECT Name, CASE
                  WHEN intelligence > ( SELECT  AVG(intelligence)  FROM  marvels) THEN 1
                  ELSE 0
              END AS intelligence,
              CASE
                   WHEN Strength > ( SELECT  AVG(Strength)  FROM  marvels) THEN 1
                   ELSE 0
              END AS Strength,
              CASE
                   WHEN Speed > ( SELECT  AVG(Speed )  FROM  marvels) THEN 1