SQL for Beginners – Part 2: Setup, Create Tables, Insert & Manage Your Data

1. Now That You Know the Basics...

In the previous blog, we understood what DBMS, RDBMS, and SQL are, along with why they matter. Now it's time to get hands-on:

✅ Set up the software

✅ Create a database and tables

✅ Load data

✅ Write your first SQL queries

Let’s start by setting up the tools!

2. SQL Software You Can Use

You’ll need two things:

1. A Database Engine – where the data is stored and managed

2. A GUI Client (optional) – for visual interaction, if you don’t want to type everything in a terminal

   
   

Common Setups:

Engine	GUI Tool	Beginner Friendly?	OS Support
PostgreSQL	PgAdmin	✅ Yes	Windows, macOS, Linux
MySQL	MySQL Workbench	✅ Yes	Windows, macOS, Linux
SQLite	DB Browser	✅ Yes	All platforms
All DBs	DBeaver	✅ Universal	All platforms

3. Installing PostgreSQL + PgAdmin (Recommended for Beginners)

1. Go to: https://www.postgresql.org/download

2. Choose your OS and follow the installer

3. During setup, it will install both:

   • PostgreSQL Server (database engine)

   • PgAdmin (GUI interface)

4. Set a master password and note it down

5. Open PgAdmin, create a new server connection, and you’re ready!

   

Ready to Get Practical? Let's Start Using SQL in PgAdmin!

🎯 Writing real SQL queries and

🔧 Interacting with actual databases

To do this, we’ll use a tool called PgAdmin — a graphical interface that lets you write SQL, create tables, and manage data visually without using the command line.

Think of PgAdmin as your workbench where you’ll:

• Create databases and tables 🧱

• Load real-world data 📥

• Run SQL queries like SELECT, WHERE, INSERT, UPDATE, etc. 💬

  
  

Before we run any queries, we must first make sure we have:

1. Installed the right software ✅

2. Set up a database ✅

3. Added or imported some data ✅

Let’s go step-by-step, starting with two ways to create your first table.

Creating a Table Using SQL (No Preloaded Data)

This is the best way to understand table structure and get comfortable with writing SQL from scratch.

📁 Step 1: Create a New Database

In PgAdmin:

1. Open PgAdmin

2. In the left sidebar, right-click on Database

3. Select Create → Database

• Give it a name (e.g., SchoolDB)

  

5. Click save ✅

Your blank database is now ready!

Step 2: Create a Table

Let’s say we want to store student records like ID, name, age, and class.

In PgAdmin:

1. Click on your new database (SchoolDB)

2. Open Query Tool

Now let’s build our first table using the CREATE TABLE command — this is like designing the blueprint of a spreadsheet where your data will live.

We’ll create a table called Students that stores basic details: ID, name, age, and class.

Here’s the complete command first:

CREATE TABLE Students (
  StudentID INT PRIMARY KEY,
  Name VARCHAR(100),
  Age INT,
  Class VARCHAR(10)
);

Let’s understand it line by line 👇

CREATE TABLE Students (

This tells the database:

“I want to create a new table called Students.”

• CREATE TABLE → A command to create a new table

• Students → The name of the table

  
  

The opening bracket ( begins the list of columns we’re about to define.

🔢 StudentID INT PRIMARY KEY,

This defines the first column in the table:

• StudentID → The column name

• INT → The data type (an integer/whole number)

• PRIMARY KEY → This makes sure every student has:

  • A unique ID

  • A non-null value

🔐 The Primary Key uniquely identifies each row in the table.

🧑‍🎓 Name VARCHAR(100),

This defines the second column:

• Name → The student’s name

• VARCHAR(100) → A string (text) column that can hold up to 100 characters

You can change 100 to a higher number if you want longer names.

🎂 Age INT,

Third column:

• Age → Stores the student's age

• INT → It's a number (like 12, 13, 14)

  
  

🏫 Class VARCHAR(10)

Fourth column:

• Class → Stores the student’s class/section (like "8A", "9B")

• VARCHAR(10) → A short text string (up to 10 characters)

We don’t add a comma , at the end of this line because it’s the last column.

✅ );

The closing bracket and semicolon end the table definition.

That’s it — your table is ready to be created!

What Just Happened?

Once you run this command in PgAdmin’s Query Tool, You’ve now created an empty container in your database. Think of it like a spreadsheet with column headers, but no rows filled in yet.

Next, we’ll look at how to fill this table with data manually (INSERT)

➕ Method 1.1: Inserting Data Manually with INSERT INTO – Step-by-Step

Now that we’ve created a table (Students), it’s time to add data manually using SQL.

🧾 General Syntax of INSERT:

INSERT INTO table_name (column1, column2, ...)
VALUES (value1, value2, ...);

Let’s understand each part step-by-step:

🧩 Step 1: INSERT INTO table_name

This tells the database:

“I want to add a new row of data into this table.”

In our case:

INSERT INTO Students

Means we’re inserting a record into the Students table.

🧩 Step 2: (column1, column2, ...)

This part specifies which columns you’re going to fill.

Example:

(StudentID, Name, Age, Class)

💡 You must list the columns in the same order as the values.

🧩 Step 3: VALUES (value1, value2, ...)

Now you give the actual values to insert into the columns you just listed.

Example:

VALUES (1, 'Rahul', 14, '8A');

Here’s how it maps:

Column	Value
StudentID	1
Name	'Rahul'
Age	14
Class	'8A'

➕ Adding Multiple Rows at Once

You can insert several rows together using comma-separated value sets:

INSERT INTO Students (StudentID, Name, Age, Class)
VALUES
  (2, 'Anita', 13, '7B'),
  (3, 'John', 15, '9C'),
  (4, 'Meena', 13, '8B');

This saves time and avoids writing multiple INSERT statements.

🔍 Check the Data:

After inserting, run:

SELECT * FROM Students;

to see the data now stored in your table

Method 1.2: Inserting Data from Another Existing

For this we need other existing table right . Let’s deep dive into this.

sometimes you may already have a backup file or a text file containing data. Let’s learn how to load it into your database.

🗃️ 1. Loading Data from a .tar File (PgAdmin Backup)

A .tar file is often used for PostgreSQL database backups — it may include:

• Tables

• Data

• Schema

  
  

📤 Steps to Restore a .tar File in PgAdmin:

1. Open PgAdmin

2. Right-click on the new database → Click Restore

3. In the dialog box:

   • Format: Choose Custom or tar

   • Filename: Browse and select your .tar file

4. Click Restore

   
   

✅ The database (including tables and data) will be loaded automatically.

📄 2. Copy-Paste Data from .txt or .csv File

Let’s say you don’t want to import the file but just want to copy and paste data manually.

Example salesdb.txt file:

📥 Steps in PgAdmin:

1. Right-click on the Salesdb table → Click View/Edit Data > All Rows

2. You’ll see an editable spreadsheet view

3. Copy the data from your .txt or .csv file

4. Paste it directly into the rows

5. Click Save (Disk icon)

✅ The data will be added instantly

When to Use Which?

File Type	Use Case	Method
.tar file	You have a PostgreSQL backup	Use Restore in PgAdmin
.csv / .txt	You want to load or copy data rows	Use Import or Edit Rows
Manual copy	You just want to paste values quickly	Use Edit Data > All Rows

Okay, Now That We Loaded Data — Let’s Insert It into SchoolDB from Another Table

You’ve now learned how to:

• Manually create tables

• Insert data

• Import from .csv or .txt

• Load backups from .tar

Now it’s time for something even more powerful:

💡 Inserting data from one table into another — without retyping or reimporting.

Let’s say we’re working inside one database (e.g., SchoolDB) and we already have a table called SalesStaff that looks like this:

EmpID	Name	Age	Department
1	Raj	32	Marketing
2	Asha	29	Sales
3	Vikram	35	Sales

Now you want to move only the Sales department staff into a new table called Staff.

SQL to Insert Data from One Table into Another

INSERT INTO Staff (EmpID, Name, Age, Department)
SELECT 
	EmpID, Name, Age, Department
FROM SalesStaff
WHERE Department = 'Sales';

🔍 Step-by-Step Explanation

🧩 Step 1: INSERT INTO Staff (EmpID, Name, Age, Department)

This tells PostgreSQL:

“I want to insert new data into the Staff table — and I’m filling these specific columns.”

Make sure these column names exist in the target table and match the data types you're selecting.

🧩 Step 2: SELECT EmpID, Name, Age, Department

This is where we pull the data — you're saying:

“Grab these columns from another table.”

🧩 Step 3: FROM SalesStaff

This tells SQL:

“Get the data from the table called SalesStaff.”

Both SalesStaff and Staff must be in the same database (here: SchoolDB).

🧩 Step 4: WHERE Department = 'Sales'

This filters the results so only rows where the department is ‘Sales’ will be copied.

Without it, all employees would be inserted.

✅ Final Result

The Staff table will now contain only the filtered records from SalesStaff:

EmpID	Name	Age	Department
2	Asha	29	Sales
3	Vikram	35	Sales

That’s how you move data around within the same database using just one command.

No manual typing. No copy-paste. Just clean, efficient SQL 💡

Awesome! Now that you've created, loaded, and inserted data — it's time to learn how to modify, delete, and drop data or tables using SQL.

Modifying, Deleting, Truncating, Renaming, Altering & Dropping – Step-by-Step SQL Commands

After inserting data, you’ll often need to:

• Update existing data (like fixing a typo)

• Delete specific rows (like removing old records)

• Truncate all rows while keeping the table structure

• Rename a table or column (for better clarity)

• Alter the structure of the table (add/remove/change columns)

• Drop an entire table (when it’s no longer needed)

Let’s look at how to do each of these operations in a clean, safe way.

1. UPDATE – Modify Existing Data

Used to change data in one or more columns for specific rows.

🧾 Syntax:

UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;

✅ Example:

UPDATE Students
SET Age = 15
WHERE StudentID = 1;

Step-by-Step:

Step	Explanation
UPDATE Students	Tell SQL which table to modify
SET Age = 15	Change the value of the Age column
WHERE StudentID = 1	Only for the row where StudentID is 1

Always use WHERE to avoid updating all rows by mistake!

2. DELETE – Remove Specific Rows

Used to delete one or more records from a table.

🧾 Syntax:

DELETE FROM table_name
WHERE condition;

Example:

DELETE FROM Students
WHERE Class = '8A';

Step-by-Step:

Step	Explanation
DELETE FROM Students	Target the table to delete from
WHERE Class = '8A'	Only delete students in class '8A'

Again, don't forget the WHERE clause — without it, all rows will be deleted.

3. TRUNCATE – Delete All Rows, Keep Table Structure

TRUNCATE removes all rows like DELETE, but it's faster and cannot be rolled back in most systems.

🧾 Syntax:

TRUNCATE TABLE table_name;

Example:

TRUNCATE TABLE Students;

Part	What it Does
TRUNCATE TABLE	Clears the entire table content
Students	Keeps the table structure intact

📝 Use TRUNCATE when you want a clean slate but still need the table structure.

4. DROP – Permanently Remove a Table

Used to completely delete a table and all of its data.

🧾 Syntax:

DROP TABLE table_name;

Example:

DROP TABLE Alumni;

Step-by-Step:

Step	Explanation
DROP TABLE Alumni	Deletes the entire Alumni table forever

This action is permanent — once dropped, the table cannot be recovered.

5. ALTER – Change Table Structure

Use ALTER when you want to add, remove, or change columns in a table.

➕ Add a Column:

ALTER TABLE Students
ADD Email VARCHAR(100);

🗑️ Remove a Column:

ALTER TABLE Students
DROP COLUMN Email;

🧬 Change a Column’s Data Type:

ALTER TABLE Students
ALTER COLUMN Age TYPE SMALLINT;

6. RENAME – Rename Table or Column

Rename a Table:

ALTER TABLE old_table_name
RENAME TO new_table_name;

Example:

ALTER TABLE Students
RENAME TO SchoolStudents;

✏️ Rename a Column:

ALTER TABLE table_name
RENAME COLUMN old_column TO new_column;

Example:

ALTER TABLE SchoolStudents
RENAME COLUMN Name TO FullName;

Difference Between DELETE, TRUNCATE, and DROP

Feature	DELETE	TRUNCATE	DROP
Removes Rows	Yes	Yes (all rows)	Yes (entire table)
Can Filter Rows (WHERE)	Yes	No	No
Keeps Table Structure	Yes	Yes	No (table is removed)
Removes Table Itself	No	No	Yes
Rollback Possible	Yes (in transaction)	Not always	No
Speed	Slower (row by row)	Very Fast (bulk delete)	Very Fast
Use Case	Delete specific rows	Delete all rows, keep structure	Permanently remove the table
Triggers Invoked	Yes	No	No
Transaction Safe	Yes	Usually not	No

• Use DELETE if you want control over which rows to remove.

• Use TRUNCATE if you want a fast clean-up of all data.

• Use DROP if you're done with the table forever.

🏁 Wrapping Up: You're Now in Control of Your Data!

In this chapter, you’ve gone beyond just reading about databases — you actually:

✅ Installed SQL tools and set up PgAdmin

✅ Created databases and tables from scratch

✅ Inserted data manually, from files, and from other tables

✅ Learned powerful SQL commands like UPDATE, DELETE, TRUNCATE, DROP, ALTER, and RENAME

✅ Understood the differences between destructive and safe modifications

You’re no longer just learning SQL — you’re using it like a real-world developer or data analyst! 💻

But we’re just getting started…

🚀 Coming Up Next...

In the next chapter, we’ll dive into querying data — the most powerful part of SQL. You’ll learn:

• How to filter, sort, and search your data using SELECT, WHERE, LIKE, ORDER BY, and more

• How to extract meaningful answers from your database — fast and efficiently

If you found this post helpful, feel free to:

💬 Drop a comment with questions

🧠 Try writing some queries on your own

📎 Bookmark this for future reference

Let’s continue learning — one SQL query at a time! 👩‍💻👨‍💻