Loading and Querying the Database
Explore how to load the Chinook database into PostgreSQL using pgloader, log in with psql, and execute queries to retrieve genre counts, artist details, albums by artist, and top-N artists by genre. Learn to run SQL files, set variables, and efficiently perform complex queries using lateral joins to manage and analyze music data.
Chinook database
We’ll be doing four tasks in this section.
- Import the
Chinookdatabase in PostgreSQL using pgloader. First, we need to start the PostgreSQL service by using the following command:
Use the following command to run the upcoming commands as the PostgreSQL superuser.
Note: The
Chinookdatabase is already created for you.
- Log in to the database. To use the psql, type the following command in the terminal:
Use the following command to log in to the Chinook database:
- Now, that the dataset is loaded, list the relations in the
Chinookdatabase using the following command:
- The next task is to see the details of any table in the
Chinookdatabase. We’ll see the details of thetracktable using the following command:
Click “Click to connect…” in the following terminal to start.
Tip: Press the up and down arrow keys to move between the rows and
qto ...