Setting Up Our Database for the Course Project

In this course, you will take on the role of a data analyst working for a company. Your task is to analyze and manage a database for the company, named Green Cycles. In this lesson, we will set up the database, load the necessary data, and execute our first SQL query.

Step 1: Open pgAdmin

1. Search for pgAdmin in your system and open it.
2. Maximize the window for better visibility.

Step 2: Create a New Database

1. In the pgAdmin interface, expand the latest PostgreSQL version under Servers.
2. Locate the Databases section, right-click on it, and select Create Database.
3. Name the database Green Cycles.
4. Click Save.

Now, your database is created! 🎉

Step 3: Check the Schema and Tables

1. Expand Schemas under the Green Cycles database.
2. You will see a default schema named public.
3. Expand Tables—currently, there are no tables in the database.

Step 4: Load Data into the Database

To add data, we will execute a SQL script file that contains all necessary commands to create tables and insert records.

Steps to Load the Data:

1. Download the SQL file provided in the course materials.
2. Extract the file from the downloaded folder.
3. Locate the SQL file inside the extracted folder.
4. Copy the folder path where the SQL file is located.

Step 5: Execute the SQL File in pgAdmin

1. In pgAdmin, open the Query Tool:
   • Right-click on Schemas → public.
   • Select Query Tool.
2. Click on the Open File icon.
3. Paste the folder path where the SQL file is located.
4. Select the SQL file and click Open.
5. Click on the Play button (▶) or press F5 to execute the script.

Once executed, you should see a message: Query returned successfully.

Step 6: Refresh the Database

1. Right-click on Schemas → public and select Refresh.
2. Expand Tables, and you will now see multiple tables created in the database.

Step 7: Run Your First SQL Query

Now, let’s run a simple query to see if our data was loaded correctly.

1. Open the Query Tool again.

2. Delete any existing text.

3. Enter the following SQL command:

   SELECT * FROM public.actor;
   

4. Press F5 or click the Play button (▶).

You should now see a table displaying the actor data from the public schema. 🎉

Next Steps

Now that our database is set up, we will introduce the course project in the next session. You will begin analyzing and managing the data for Green Cycles using SQL queries. 🚀