PM & AI Chronicles

From Product Thinking to Prompt Engineering – One Tool at a Time

Working With Databases 🧾: Practical Data Tasks 💡: From Queries to Backups ❓📥

A Look Inside Data Tasks: How Databases Add, Change & Retrieve Information 🆕🌱🎣

In the previous article, we looked at SQL commands and basic syntax—the actual language used to talk to a database, retrieve information, and make changes safely and efficiently. 👉 SQL Syntax

Now it’s time for the fun part — actually doing things with your data. In this article, we’ll walk through the everyday tasks that anyone working with a database will eventually perform — things like adding new information, updating mistakes, searching for exactly what you need, connecting tables, and even backing up or cleaning up your database safely.

Everything is explained in plain English, with simple examples, so you can understand not just how to run these commands, but why they exist and when to use them.

By the end of this post, you’ll be able to handle everyday, real-life operations such as adding a student record, finding all employees, updating prices, cleaning up old entries, and backing up your database without fear. This is the part where SQL starts to feel powerful, practical, and genuinely helpful.

Alright — let’s get started with the basics of working with data.

Manipulating Data — INSERT, UPDATE, DELETE 🛠️

Before we start running big fancy queries, let’s begin with the most basic (but most important) things you can do in a database: add something, change something, or remove something.

Think of this as managing entries in a notebook:

  • INSERT → writing a new line
  • UPDATE → correcting or changing a line
  • DELETE → erasing a line

SQL does the same thing — but with tables.

INSERT — Adding New Data

When you use INSERT, you’re simply telling the database: “Hey, add this new record to the table.”

Plain English: “Create a new row with this information.”

Example: Add a new student.

INSERT INTO Students (StudentID, Name, Grade)
VALUES (101, 'Bob Jones', '5');

This adds Bob as a new Grade 5 student.

UPDATE — Changing Existing Data

UPDATE is how you fix mistakes or change information.

Plain English: “Find this row, and change these details.”

Example: Update a product price.

UPDATE Products
SET Price = 19.99
WHERE ProductID = 42;

Only the product with ID 42 gets its price changed.

Important 🚨: Always use a WHERE clause when updating. Otherwise, you will update the entire table. (And yes, every beginner has done this at least once 😅)

DELETE — Removing Data 🗑️

DELETE removes rows from a table.

Plain English: “Find this row and delete it.”

Example: Delete a customer record. 

DELETE FROM Customers
WHERE CustomerID = 15;

The customer with ID 15 is removed from the table.

Important 🚨: Never delete without a WHERE clause, unless you truly mean to wipe out all rows.

⭐ Quick Memory Tip: Think of them like this:

  • INSERT → “Make a new row”
  • UPDATE → “Fix/change this row”
  • DELETE → “Remove this row”

Extracting Data — SELECT Basics 🔍

Now that you know how to add, change, and remove data, it’s time for the part you’ll use every single day — getting information out of the database.

This is where SQL truly shines. Searching, filtering, narrowing down results… all of it starts with one command: SELECT

Think of it as telling the database: “Show me exactly the information I want.”

Let’s walk through this step by step, in small, comfortable pieces.

SELECT — Getting Data From a Table 📊

The simplest SELECT looks like this:

SELECT * FROM Employees;

Plain English: “Show me all columns and all rows from the Employees table.”

But we rarely need everything. So you can choose what you want:

SELECT Name, Salary FROM Employees;

Plain English: “Show me only the Name and Salary columns.”

WHERE — Filtering Data 🔎🌪️

WHERE is your filter. It allows you to ask questions like:

  • “Show employees in HR.”
  • “Show products cheaper than $5.0”
  • “Show orders from last wee.k”

Example:

SELECT *
FROM Employees
WHERE Department = 'HR';

Plain English: “Get only employees who work in HR.”

AND, OR, NOT — Combining Conditions

Once you start filtering, you’ll often combine conditions:

✔ AND: Both conditions must be true.

WHERE Department = 'Sales' AND Salary > 60000

✔ OR: At least one must be true.

WHERE City = 'Dallas' OR City = 'Houston'

✔ NOT: Opposite of the condition

WHERE NOT City = 'Miami'

✔ Use Parentheses for Clarity

WHERE (City = 'Dallas' OR City = 'Houston')
  AND Salary > 50000;

Plain English: “From Dallas or Houston, plus salary above $50,000.”

LIKE — Pattern Matching

LIKE is super useful when you only know part of the text.

  • % = any number of characters
  • _ = one character

Examples:

WHERE Name LIKE 'A%'        -- starts with A
WHERE Name LIKE '%son'      -- ends with son
WHERE Email LIKE '%gmail%'  -- contains gmail

BETWEEN — Range Filtering

BETWEEN: Perfect for numbers or dates:

WHERE Price BETWEEN 10 AND 50;

Plain English: “Price from 10 to 50, including both.”

IS NULL — Missing Values

NULL means “no value.”

WHERE PhoneNumber IS NULL;

Plain English: “Show rows where phone number is missing.”

You can also check the opposite:

WHERE PhoneNumber IS NOT NULL;

⭐ A Quick Recap

When extracting data:

  • SELECT → choose what you want
  • WHERE → filter results
  • AND/OR/NOT → combine conditions
  • LIKE → match patterns
  • BETWEEN → select ranges
  • IS NULL → find missing values

These are the core tools for every query you’ll ever write.

Sorting & Deduplication — ORDER BY & DISTINCT 📊

Once you know how to extract data, the next natural step is learning how to organize it. Databases store information, but you decide the order in which it should be displayed — and whether duplicates should appear or not. This topic covers two everyday tools:

  • ORDER BY → sort your results
  • DISTINCT → remove duplicate values

Let’s look at both with easy examples.

ORDER BY — Sorting Your Results

By default, SQL does not guarantee any particular order. If you want your results arranged alphabetically, by date, by salary, etc., you must specify it.

✔ Sort A → Z (Ascending)

SELECT Name, Salary
FROM Employees
ORDER BY Name ASC;

Plain English: “Show employees sorted by name, from A to Z.”

✔ Sort Z → A (Descending)

SELECT Name, Salary
FROM Employees
ORDER BY Name DESC;

✔ Sort by numbers (low to high)

ORDER BY Price ASC;

✔ Sort by latest dates first

ORDER BY OrderDate DESC;

✔ Sort by multiple columns
ORDER BY Department ASC, Salary DESC;

Plain English: “First group by department, then within each department show the highest salaries first.”

DISTINCT — Removing Duplicates

If a column contains repeated values (like cities, departments, or categories), DISTINCT fetches each unique value once.

✔ Example: Get a list of unique departments

SELECT DISTINCT Department
FROM Employees;

Plain English: “Show each department only once.”

✔ DISTINCT on multiple columns

SELECT DISTINCT City, State
FROM Customers;

This gives you unique city–state combinations.

Quick Memory Tip ⭐

  • ORDER BY → sorts your results
  • DISTINCT → removes duplicates

These two commands help you keep your data neat, clean, and easy to read.

Grouping & Calculations — GROUP BY, HAVING & Aggregate Functions 📈

So far, you’ve learned how to fetch individual rows, sort them, and remove duplicates. Now it’s time for one of the most powerful features in SQL: summarizing data.

This is how you answer real-world questions like:

  • How many employees are in each department?
  • What is the total sales for each month?
  • What’s the average salary in each team?

To do this, SQL gives us two powerful tools:

  • Aggregate functions → perform calculations
  • GROUP BY → group rows before calculating
  • HAVING → filter groups (not individual rows)

Let’s walk through this clearly and slowly.

Aggregate Functions — COUNT, SUM, AVG, MIN, MAX 🔢 📈

Aggregate functions work on multiple rows at once

✔ COUNT() — how many?

SELECT COUNT(*) 
FROM Employees;

Plain English: “How many employees do we have?”

✔ SUM() — total of values

SELECT SUM(Salary)
FROM Employees;

✔ AVG() — average value

SELECT AVG(Salary)
FROM Employees;

✔ MIN() — smallest value

SELECT MIN(Price)
FROM Products;

✔ MAX() — largest value

SELECT MAX(Price)
FROM Products;

These become powerful when combined with GROUP BY.

GROUP BY — Grouping Rows Before Calculations 🗫

GROUP BY lets you group rows that share something in common → like department, city, category, or month.

✔ Example: Count employees in each department

SELECT Department, COUNT(*) AS TotalEmployees
FROM Employees
GROUP BY Department;

Plain English: “For each department, show how many employees are in it.”

✔ Example: Total sales per category

SELECT Category, SUM(Amount) AS TotalSales
FROM Orders
GROUP BY Category;

✔ Example: Average salary per role

SELECT Role, AVG(Salary) AS AvgSalary
FROM Employees
GROUP BY Role;

HAVING — Filtering Groups 🌪️

This is where beginners get confused:

  • WHERE filters rows.
  • HAVING filters groups.

Think of it like this:

  • WHERE → “filter before grouping”
  • HAVING → “filter after grouping”

✔ Example: Show only departments with more than 5 employees

SELECT Department, COUNT(*) AS TotalEmployees
FROM Employees
GROUP BY Department
HAVING COUNT(*) > 5;

✔ Example: Show categories with sales above $10,000

SELECT Category, SUM(Amount) AS TotalSales
FROM Orders
GROUP BY Category
HAVING SUM(Amount) > 10000;

⭐ Quick Memory Tip

  • Aggregate functions → do the math
  • GROUP BY → split rows into groups
  • HAVING → filter the final groups

Joining Tables — JOIN Operations 🔗

Up to now, you’ve been working with single tables. But real databases almost always have multiple tables that relate to each other.

Example:

  • A Students table
  • A Classes table
  • A Teachers table
  • A Departments table

The real power of SQL is the ability to combine information from these tables in one view. That’s where JOINs come in.

Think of it like this: “Find matching information in two tables and show them together.”

Let’s break down the most common JOIN types in simple English.

Why JOINS Exist

Imagine you have:

Table 1: Students

  • StudentID
  • Name
  • ClassID

Table 2: Classes

  • ClassID
  • ClassName

If you want: “Show each student with their class name”. You must combine both tables.

Without JOINs, you’d see either:

  • only students (no class names), or
  • only classes (no student names)

JOINs are the bridge 🔗 between them.

INNER JOIN — Only Matches on Both Sides 🖇

Shows rows that match in both tables.

SELECT Students.Name, Classes.ClassName
FROM Students
INNER JOIN Classes
    ON Students.ClassID = Classes.ClassID;

Plain English: “Show each student only if their ClassID exists in the Classes table.”

LEFT JOIN — Keep Everything from the Left Table 🖇️

This one is super useful.

SELECT Students.Name, Classes.ClassName
FROM Students
LEFT JOIN Classes
    ON Students.ClassID = Classes.ClassID;

Plain English: “Show all students — even those who don’t have a class assigned.” Missing matches will appear as NULL.

RIGHT JOIN — Keep Everything from the Right Table 🖇️

Used less often, but worth knowing.

SELECT Students.Name, Classes.ClassName
FROM Students
RIGHT JOIN Classes
    ON Students.ClassID = Classes.ClassID;

Plain English: “Show all classes — even if no students are enrolled.”

FULL OUTER JOIN — Everything From Both Sides ⛓️

Shows all rows from both tables, matching when possible.

SELECT Students.Name, Classes.ClassName
FROM Students
FULL OUTER JOIN Classes
    ON Students.ClassID = Classes.ClassID;

Plain English: “Show every student and every class, matching where possible.”

⭐ Quick Memory Tip

  • INNER JOIN → only matching rows
  • LEFT JOIN → all from left + matches from right
  • RIGHT JOIN → all from right + matches from left
  • FULL JOIN → everything from both

JOINs may look scary at first, but once you think of tables as “puzzle pieces,” it becomes easy.

Working With Files — Importing, Exporting, Backups & Dropping 📥📤

Up to now, we’ve focused on writing queries and interacting with data inside tables. But in the real world, you’ll often need to:

  • bring data into the database,
  • take data out of it,
  • keep a safe copy (backup), and
  • occasionally delete things you no longer need.

Think of this section as your “Database File Management Toolkit.” Everything is explained in a simple, easy-to-follow way for beginners.— no platform-specific instructions.

Importing Data — Bringing Data Into the Database 📥

Importing is how you take external files and turn them into database rows. Common formats you’ll import:

  • CSV files
  • Excel sheets
  • JSON files
  • Exported data from other apps

Plain English: “Take this file and load it into this table.”

Example (generic SQL Server-style):

BULK INSERT Students
FROM 'C:\Data\students.csv'
WITH (FIELDTERMINATOR = ',', ROWTERMINATOR = '\n');

For beginners, it’s best to remember:

  • Import tools usually come with your DBMS (SQL Server Studio, MySQL Workbench, pgAdmin).
  • All of them have “Import Wizard” options that make this easy.

Exporting Data — Saving Data to a File 📤

Exporting is the opposite of importing:

Plain English: “Take rows from a table and save them as a file.”

Use cases:

  • Sharing data with another team
  • Creating reports
  • Moving data to another database
  • Backing up a specific table

Common export formats:

  • CSV
  • Excel
  • JSON
  • SQL script (INSERT statements)

Example (conceptual):

SELECT *
FROM Employees
INTO OUTFILE 'employees.csv'
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n';

Again, GUI tools usually provide simple “Export” buttons.

Backing Up Databases — Protecting Your Data 💽

A backup is simply a safe copy of your database.

Think of it like:

  • Saving your project
  • Creating a restore point
  • Making sure nothing is lost if something breaks

Backups help when:

  • You accidentally delete data
  • A system crashes
  • A deployment goes wrong
  • A table gets corrupted
  • Storage fails

Every DBMS has a built-in backup feature. High-level SQL example:

BACKUP DATABASE SchoolDB
TO DISK = 'C:\Backups\SchoolDB.bak';

Golden Rule: Always backup before performing risky operations like mass updates or dropping tables.

Dropping Tables & Databases — Use With Caution! 🗑️‼️

Dropping is the permanent deletion of a table or entire database.

✔ Drop a table

DROP TABLE Students;

✔ Drop a database

DROP DATABASE SalesDB;

Warning: ⚠️

You cannot undo a DROP. The table/database and all its data disappear.

Always double-check before running it. A safer alternative is:

TRUNCATE TABLE Students;  -- Deletes rows but keeps the table

Or even:

DELETE FROM Students;     -- Removes data but table remains

⭐ Quick Memory Tip

  • Import → bring data in
  • Export → take data out
  • Backup → create a safe copy
  • Drop → permanently remove

Hands-On Practical Examples 🧪

This final section ties everything together with small, real-world SQL tasks. Each example is simple, clear, and shows how the commands you learned actually work in everyday situations.

Example 1: Add a New Student

INSERT INTO Students (StudentID, Name, Grade)
VALUES (201, 'Mia Johnson', '4');

Plain English: Add a new student to the list.

Example 2: Update a Product Price

UPDATE Products
SET Price = 14.99
WHERE ProductID = 10;

Always include a WHERE to avoid updating everything.

Example 3: Delete a Customer

DELETE FROM Customers
WHERE CustomerID = 32;

Removes only the customer with ID 32.

Example 4: Get All Employees

SELECT *
FROM Employees;

Retrieves every column for every employee.

Example 5: Filter Employees by Department

SELECT Name, Department
FROM Employees
WHERE Department = 'Finance';

Shows only employees in Finance.

Example 6: Get Unique Cities

SELECT DISTINCT City
FROM Customers;

Shows each city only once.

Example 7: Sort Employees by Salary (High → Low)

SELECT Name, Salary
FROM Employees
ORDER BY Salary DESC;

Example 8: Count Employees in Each Department

SELECT Department, COUNT(*) AS Total
FROM Employees
GROUP BY Department;

Summarizes data by groups.

Example 9: Departments With More Than 5 Employees

SELECT Department, COUNT(*) AS Total
FROM Employees
GROUP BY Department
HAVING COUNT(*) > 5;

Example 10: Join Students With Their Class Names

SELECT Students.Name, Classes.ClassName
FROM Students
INNER JOIN Classes
    ON Students.ClassID = Classes.ClassID;

Combines two tables into one result.

Wrapping Up 🧭

You’ve now worked through the most useful, everyday SQL tasks — adding data, updating it, searching through it, organizing results, joining tables, and even handling backups and imports. These are the commands people rely on constantly in real projects.

With these skills, you can start answering fundamental questions, cleaning up data, and solving practical problems on your own. The more you try the examples, the more natural SQL will feel.

This article is part of the Database Fundamentals Series — where we break down what databases are, how they store information, and how data is organized, accessed, and used behind the scenes. 👉 — Database Fundamentals: Foundation of Data Storage