

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.
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.
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.
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.
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');
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.
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 players
WHERE 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 players
WHERE 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 players
WHERE 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 players
WHERE id > 3
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 players
SET 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 players
WHERE 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.
Create your free account to unlock your custom reading experience.