Database can be a scary word. It’s often considered large and complicated, but they can work wonders for keeping your application neat and in order. For more on the differences between the two main types of databases, here is a primer that may be of assistance.
As for what you’re about to read, I’d strongly recommend following along with PSequel, a GUI on Mac that allows you to see the data you are working with. Basketball, like many sports, is filled with data that makes it more interesting. A stat line is essential a chunk of data. Russell Westbrook might as well be a statistician with his triple-double prowess. And what better way to prepare for the upcoming basketball season than to prepare some databases based on player attributes.
One more bit of exposition. PostgreSQL is one of the top programs for relational databases. While there are other good ones such as MySQL and SQLite, I have found PostgreSQL to be most favorable since it’s open-sourced and has a pretty great online community.
And now, it’s gametime.
A little database dancing to start things off
Create a Database
Creating a database is simple. First things first, make sure you’re server is running on the command line by running:
psql
Now that you’re logged in, creating a database is as easy as:
create database (name of database);
Once it’s created, you could see a list of all of your databases with the \l command. To quit from the command line, you can use \q.
Enter PSequel
Type in your database and let’s connect!
PSequel is a GUI that makes viewing your data a little easier. I created a database called basketball. Now let’s connect and create some tables.
Table Creation
Once you’ve connected, you’re free to create tables that will organize all of your data. With ‘query’ selected, we can create a table like this:
From above, we can see that I am creating a table called ‘players.’ Inside this table, there will be four rows: an ID (mostly to keep track of how many people I am adding), a first name, a last name, and a team. You’ll notice there is a datatype listed after these, which lets the database know what kind of data to expect. Integer is a number. Varchar stands for a ‘variable-length character’ string. The number following this in parenthesis is the maximum number of characters. For more on what kind of datatypes you can use in PostgreSQL, check out this nifty list. So the team listed will only be able to hold three characters. Once this is done, run the query and refresh the page (bottom left).
The table, although empty, can now be found in the ‘content’ tab. But we can’t work with an empty table. Let’s add to it.
All About Insert
With your brand new empty table, head back over to query and insert the following:
INSERT INTO players (id, first_name, last_name, team) VALUES (1, 'Steph', 'Curry', 'GSW');
Steph Curry loves dancing for data
Here, I am inserting records into my ‘players’ table. The first set of parenthesis, while not mandatory, tells the database which columns the data will go into. You can put the columns in whatever order you want here, as long as it corresponds to the second parenthesis group. For example, PSequel would not allow it if I tried to put an integer into the first name field. It’s also important that strings (or varchars) are put into quotes.
Easy enough? Alright, let’s add some more data and see how to manipulate it.
Selecting Your All Stars
We have some data to work with
From the image provided, now we can select and manipulate the players. Quite a squad we have so far. Head back into query and run the following line:
SELECT * FROM players
From here, you can see everyone. But what if you only wanted to see players from the Golden State Warriors? Next, run the following lines:
SELECT * FROM playersWHERE team = 'GSW'
This ‘where’ keyword is especially important when we want to update or delete items from the database. Other keywords can be concatenated to these lines to show even more data.
SELECT * FROM playersWHERE team = 'GSW'OR team = 'NY'
The statement above will show you anyone from either Golden State or New York. Notice the ‘or’ statement.
SELECT * FROM playersWHERE team = 'GSW'AND first_name = 'Steph'
This statement would return just Steph Curry, since while Klay Thompson does share the same team (GSW), his first name does not match.
You may also run greater than, less than, or equal to statements for integers. Take the following example and notice that the first two IDs are not returned.
SELECT * FROM playersWHERE id > 3
Updating and Deleting
Let’s now change something in our database. We’ve heard some speculation about Lebron James heading to the Lakers after his contract is up. How can we reflect this in our data:
UPDATE playersSET team = 'LAL'WHERE last_name = 'James'
The first line specifies the table you are changing. The second line specifies what we are setting. The third line tells us that we only want to change the data that has a last name of James. Like the earlier examples, we could have added more parameters, as well as ‘and’ or ‘or’ statements.
Notice that Lebron James now plays for the Los Angeles Lakers. By clicking on the ID column header, we could once again order the players by their IDs (or any other header for that matter.
Deleting is done in a similar manner, but by using the word ‘delete’ instead of update. And once again, we can add as many parameters as we want. The command below wipes Lebron James from the list.
DELETE playersWHERE last_name = 'James'AND team = 'LAL'
But he probably wouldn’t like that.
Thanks for reading. Feel free to contact me to chat code or basketball here. Till next time.