gistfile1.md

##nba sql

Part 1

Fire up psql and create a database called nba_db.

You will find the schema for a table called players in the file players.sql. The schema includes an id, name, age, team, games, and points.

Run the players.sql file for your nba_db by typingpsql -d nba_db -f players.sql into your terminal. This will create the players table in your nba_db. Alternatively, you can open 'psql' and paste in the contents of players.sql.

Part 2

If you haven't already, run gem install pg to install the pg gem. The pg gem allows us to run SQL in a Ruby file.

Run the load_data.rb Ruby program ONCE to populate the database. It reads in the CSV information from the data.csv textfile and populates your database. The data is structured like the following:name,age,team,games,points

note: games is games played during the season and pointsis total points scored over the course of the season.

addendum: We are aware that this data set is outdated. (-_-)

Part 3

Figure out the appropriate SQL commands to find out the following, and keep track of them in the file called sql_commands.txt

###Select all columns

1. All columns for all players from the New York Knicks (NYK)

• All columns for all players from the Indiana Packers (IND) who are under 26 years old
• All columns for all players, ordered from least points scored to most points scored

Get average of certain rows

We did not teach you how to do this explicitly. Here is a hint:

SELECT AVG(COLUMN_NAME) FROM TABLENAME;

1. The average age for all players.

• The average age for all players on the Oklahoma City Thunder (OKC)
• The average age for all players who played more than 40 games

###Challenge These will not be easy.

1. Name and Points per game (points/games), for the players with the top 20 points per game

• The team and total points scored from all players on that team (team points), ordered from most team points to least
• Team and the the number of players who score above 12 points per game on that team, ordered from most to least
• Age and the average points per game for that age, ordered from youngest to oldest

---

##superhero sql

Prompt

Design a Ruby program that manages a database of superheros. The database should be backed by SQL to persist the data.

Directions

• Don't use objects
• Don't write tests

Menu Spec

Your program should have a menu that allows you to perform the following CRUD actions:

• (C) Add a Super Hero
• (R) List all Super Heroes
• (D) Remove a Super Hero

###Superhero Spec

A Super Hero has the following attributes:

• superhero_name - varchar
• alter_ego - varchar
• has_cape? - boolean
• power - varchar
• arch_nemesis

Part 1

Create a super heroes database in psql with:

CREATE DATABASE superheroes_db;

Part 2

Create a superheroes table. It should have the columns indicated in the above spec. Refer to players.sql in the nba_sql lab if you are unsure of how you might do this.

Part 3

Populate your database from superheroes.csv. Look at the ruby program load_data.rb from the nba_sql lab for insights as to how we might do this.

Part 4

Forget that this csv file ever existed. We do not need to read from it, or write to it, ever again.

Part 5

Write a command line program that allows you to:

• (C) Add a Super Hero
• (R) List all Super Heroes
• (D) Remove a Super Hero

You will be using puts and gets and chomp (oh my!) to get user input.

Then you will use the 'pg' gem to connect to the database and execute the relevant SQL statement.

some things to consider:

• What is returned when you execute sql statement via pg?
• How will you represent these data in the command line?

---