Setting Up Postgres Database and Tables for SQL Practice

Databases Postgres
,
1

Are you eager to dive into the world of SQL but unsure where to start? Look no further! In this blog post, we’ll guide you through the process of setting up a Postgres database and tables, providing you with the perfect environment to practice your SQL skills.

Embedded Video

In this tutorial video, we walk you through the step-by-step process of setting up a Postgres database and tables for SQL practice. Learn how to create a new database and user, set up required tables, and register the server on pgAdmin. Perfect for beginners looking to enhance their SQL skills!

Pre-requisites

Before we begin, make sure you have the latest version of Postgres and pgAdmin installed on your system. If you haven’t already installed them, check out our comprehensive guide on how to install and manage PostgreSQL on Windows and Mac.

Step 1: Setting up the Database and User To start, we need to create a new database and user in Postgres. Open your preferred terminal or command prompt and execute the following commands:

CREATE USER itversity_retail_user WITH ENCRYPTED PASSWORD '*******'; 
CREATE DATABASE itversity_retail_db OWNER itversity_retail_user;

Step 2: Setting up the Required Tables Now, let’s create the necessary tables for our SQL practice. We’ll create a table called sales with specific columns to work with. Here’s how you can do it:

plaintextCopy code

CREATE TABLE sales (
    sale_id INT PRIMARY KEY, -- Unique as well as not null 
    sale_rep_id INT, 
    sale_amount FLOAT, 
    commission_pct INT
);

Step 3: Registering the Server on pgAdmin After setting up the database and tables, it’s time to manage them conveniently using pgAdmin. Follow these steps to register the server:

  1. Delete any existing server from pgAdmin (if applicable).
  2. Register a new server using the credentials for the itversity_retail_db database and itversity_retail_user user.

Conclusion

Congratulations! You’ve successfully set up a Postgres database and tables for SQL practice. With this foundation in place, you can now start honing your SQL skills with confidence. Practice running queries, manipulating data, and exploring the power of SQL in a safe and controlled environment.

Support

If you encounter any issues or have questions about setting up your database, don’t hesitate to ask for support. Simply reply to this topic on our community forum, and our knowledgeable community members will be happy to assist you.

Happy querying!