PostgreSQL – Yoshua F. Reece

chugging along, learning sql

May 21, 2025 yreece

Learning SQL and databases has been fun so far. It is opening me up to new concepts, and I feel like I’m gaining a foundational understanding of information science. I find myself thinking about the information in other areas of my life and applying the concepts I have learned. For example, I made a database schema for my online English class.

As I go along through the ‘Design Databases with PostgreSQL’ course on CodeCademy, I have little moments of victory when I write a query correctly by myself. This was my most recent case of that:

SELECT book.title AS book_title, author.name AS author_name, book.description AS book_description
FROM book, author, books_authors
WHERE book.isbn = books_authors.book_isbn AND author.email = books_authors.author_email;

SELECT author.name AS author_name, author.email AS author_email, book.title AS book_title
FROM author, book, books_authors
WHERE author.email = books_authors.author_email AND book.isbn = books_authors.book_isbn;

These queries connect two tables together using a junction table. The first query shows for each book the corresponding authors. The second query shows for each author the corresponding books. This exercise is to demonstrate a many-to-many relationship.

Additionally, claude.ai pointed out that the same result can be reached using a different approach. This shows the same query using JOIN:

SELECT book.title AS book_title, 
       author.name AS author_name, 
       book.description AS book_description
FROM book
JOIN books_authors ON book.isbn = books_authors.book_isbn
JOIN author ON author.email = books_authors.author_email;

Exploring a baseball database

May 17, 2025 yreece

As part of the CodeCademy course ‘Design Databases With PostgreSQL’, I am getting oriented with databases by exploring this baseball database created by Sean Lahman.

I opted to use psql and the command line interface for this project, as opposed to the GUI client.

The project from CodeCademy was to do various analyses on the data by creating sql queries. I found that it was pretty much above my sql skill level to create the queries on my own, so I ended up re-typing each query from the solutions file. I was able to read and understand the logic, though for joins involving multiple conditions the logic was difficult to wrap my head around. Also, I’m not super familiar with baseball.

After completing the assignment, I asked Claude.ai to create a SQL query to show the trend in average salaries for baseball players. I used the output to make this chart in Google sheets.

Setting up PostgreSQL server, client and command line tools

May 12, 2025 yreece

I downloaded postgresql and postbird. Now I can create and manage databases locally.

I practiced creating tables, adding columns and rows with various data types, backfilling data, querying the data and adding constraints. I practiced doing this using both the Postbird GUI and psql CLI.

postgres=# select * from films;
 id |           name            | release_date | category  | stars
----+---------------------------+--------------+-----------+-------
  1 | Bugs Life                 | 1998-11-14   | animation |     3
  2 | Matilda                   | 1996-07-28   | comedy    |     4
  3 | Return of the Jedi        | 1983-05-25   | sci-fi    |     2
  4 | Shrek                     | 2001-03-23   | animation |     5
  5 | The Count of Monte-Cristo | 2024-04-03   | action    |     3
  6 | Napoleon Dynamite         | 2004-04-19   | comedy    |     5
(6 rows)

CodeCademy course: Design Databases With PostgreSQL

May 12, 2025 yreece

This was the first project in a course that I am starting. In it, I learned some foundational database concepts and how to query, create, alter database information using SQL. Here are my first ever SQL queries:

CREATE TABLE friends (
  id INTEGER, 
  name TEXT, 
  birthday DATE
);

INSERT INTO friends (id, name, birthday) 
VALUES (1, 'Ororo Munroe', '1940-05-30');

INSERT INTO friends (id, name, birthday) 
VALUES (2, 'Didi', '2024-06-16');

INSERT INTO friends (id, name, birthday) 
VALUES (3, 'Luffy', '1999-05-05');

INSERT INTO friends (id, name, birthday) 
VALUES (4, 'water god', '1225-06-16');

UPDATE friends
SET name = 'Storm'
WHERE id = 1;

ALTER TABLE friends 
ADD email TEXT;

UPDATE friends
SET email = 'storm@lessonslides.com'
WHERE id = 1;

UPDATE friends
SET email = 'didi@lessonslides.com'
WHERE id = 2;

UPDATE friends
SET email = 'luffy@lessonslides.com'
WHERE id = 3;

UPDATE friends
SET email = 'watergod@lessonslides.com'
WHERE id = 4;

DELETE FROM friends
WHERE id = 1;

SELECT * 
FROM friends;