- Databases
- PostgreSQL
- In the Terminal,
psql
starts a connection to PostgreSQL - Within psql
\l
lists all of your databases\c <your database>
connect to your database\dt
displays all tables within current database\q
quit psql
Use the included files nba_seed.sql
, nba_queries.sql
and nba_season_2011-12.csv
to solve the following problems.
Parts 1 - 4
We're going to work on our Postgresql skills by practicing basic SQL commands with some outdated NBA data.
Let's start by creating an empty PSQL database called nba_db
and then seeding that database with raw information from nba_season_2011_2012.csv
. We're going to accomplish all of this by writing PSQL commands into an SQL script called nba_seed.sql
and then running that script.
- From the homework folder, open
nba_seed.sql
in VSCode - We have completed the following for you:
- Drop a database called
nba_db
if it exists - Create a database called
nba_db
- Connect to
nba_db
- Enter PSQL commands to complete the following objectives:
-
Create a table called
players
with columns forname
age
team
games
points
HINT
What datatypes should each of these columns be?
-
Insert the player:
Anderson Varejao, age: 29, team: CLE, games: 25, points: 271
- Save your
nba_seed.sql
file - Run
nba_seed.sql
by entering the following command into the terminal:
$ psql -f nba_seed.sql
In the nba_seed.sql
file, look at the COPY players statement which is currently commented out - what do you think it does?
Uncomment this COPY command and change the file path for nba_season_2011_2012.csv
in order to get it to run on your machine. The command pwd
in your terminal may help!
Afterwards comment out the single insert player command you created in Part 1, and run the seed file again:
$ psql -f nba_seed.sql
For Parts 3 and 4, work in the nba_queries.sql
file. Run the file to test your answers by using psql -f nba_queries.sql
Some of the prompts may go a little further than what we saw in class, but should be very easy to Google and figure out. Some useful things to look up:
- WHERE
- ORDER BY
- LIMIT
- AVG
- SUM
- 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.
- Name and Points per game (points/games), for the players with the top 20 points per game.
- 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.
Add the following to your nba_queries.sql
file:
-
The team and total points scored from all players on that team (team points) ordered from most team points to least.
-
The age and the average points per game for that age, ordered from oldest to youngest for all ages.
-
The team and the the number of players who score above 12 points per game on that team, ordered from the most number of players to the least number of players.
Homework is due by midnight Today!