Step 1: Building a Database

A database is where all your data resides. In PostgreSQL, there are two ways to create a database: using the pgAdmin interface or writing an SQL query.

Method 1: Using the pgAdmin Interface

1. Open pgAdmin 4 and log in by entering your password.

2. Navigate to your server and right-click on the Databases option.

3. Select Create > Database and provide a name for your database (e.g., my_database).

4. Click Save, and your database will be created.

Method 2: Using SQL Queries

1. Open the Query Tool in pgAdmin.

2. Enter the following query:

   CREATE DATABASE my_database;

3. Click the Run button or press F5 to execute the query.

Your database is now ready to use.

Step 2: Creating a Table

Tables are where data is organized and stored. Let’s create a table named students with columns for storing student information.

SQL Query to Create a Table

CREATE TABLE students (
   student_id SERIAL PRIMARY KEY,
   first_name VARCHAR(50) NOT NULL,
   last_name VARCHAR(50) NOT NULL,
   gender VARCHAR(5) NOT NULL,
   enrollment_year INT NOT NULL
);

Explanation of the Code

• student_id SERIAL PRIMARY KEY: A unique identifier for each student that auto-increments.

• first_name and last_name: Columns for storing names, with a maximum length of 50 characters each, and cannot be empty.

• gender: A column to store the gender of the student, limited to 5 characters.

• enrollment_year: An integer column for the year of enrollment.

Run this query in the Query Tool to create your table.

Step 3: Inserting Data into the Table

Once the table is created, the next step is to insert data into it. Use the INSERT INTO statement to add rows to your table.

Example Query to Insert Data

INSERT INTO students (first_name, last_name, gender, enrollment_year)
VALUES
   ('Rohan', 'Singh', 'Male', 2025),
   ('Varun', 'Patel', 'Male', 2025),
   ('Neha', 'Verma', 'Female', 2025);

This query inserts three rows into the students table. You can run this query to populate your table with data.

Step 4: Viewing Data with the SELECT Statement

To view the data stored in your table, use the SELECT statement.

View All Data

SELECT * FROM students;

This command retrieves all columns and rows from the table.

View Specific Columns

SELECT first_name, last_name FROM students;

This query retrieves only the first and last names of the students.

Step 5: Altering a Table

Sometimes, you may need to make changes to an existing table. Use the ALTER TABLE statement to modify the structure of your table.

Example: Altering the Gender Column

If you want to increase the character limit of the gender column from 5 to 6 characters:

ALTER TABLE students
ALTER COLUMN gender TYPE VARCHAR(6);

Run this query to update the column definition.

Practice Assignment

Here’s a small assignment for you to practice:

1. Create a database of your choice.

2. Build a table with at least 4-5 columns (e.g., for student or sales data).

3. Insert 5-6 rows of sample data into the table.

4. Use the SELECT statement to view the data.

Feel free to experiment with altering the table or filtering data using SELECT queries.

Conclusion

In this blog, we’ve covered the fundamentals of building a database, creating tables, inserting data, and modifying tables. These are crucial skills for anyone looking to work with SQL for data management or analysis.

Keep practicing these queries, and don’t hesitate to leave a comment if you have any questions. Stay tuned for the next session, where we’ll explore more advanced SQL queries.

Happy Learning!