Skip to main content

SQL and Database Queries

Learning Objectives​

By the end of this section, you will be able to:

  • Understand what SQL is and why it's the standard database language
  • Write basic SQL queries to retrieve information from databases
  • Create and modify database tables and structures
  • Insert, update, and delete data safely and efficiently
  • Join multiple tables to get comprehensive business information
  • Create useful business reports using SQL queries
  • Apply best practices for database security and data integrity

Introduction: Speaking the Language of Databases​

Imagine you're in a foreign country and need to ask for directions, order food, or conduct business. You'd need to learn the local language to communicate effectively. Similarly, to work with databases, you need to learn SQL (Structured Query Language) - the universal language that databases understand.

SQL is like having a conversation with a very organized, precise librarian who can instantly find any information you need, but only if you ask in exactly the right way. In this section, we'll learn how to "speak database" fluently.

What is SQL?​

Simple Definition​

SQL (Structured Query Language) is a standardized language for communicating with relational databases. It allows you to ask questions, add new information, modify existing data, and organize database structures.

Real-World Analogy: Restaurant Orders​

Think of SQL like placing orders at a restaurant:

The Restaurant Setup:

  • Menu = Database tables (Customers, Orders, Products)
  • Waiter = Database management system
  • Kitchen = Database engine that processes your requests
  • Your order = SQL query
  • Your meal = Query results

How it works:

  1. You tell the waiter exactly what you want: "I want chicken biryani, medium spice, with raita on the side"
  2. The waiter writes down your specific request
  3. The kitchen prepares exactly what you ordered
  4. You get back precisely what you asked for

Similarly, with SQL:

  1. You write a specific query: "Show me all customers from Mumbai who bought more than ₹10,000 worth of products"
  2. The database processes your exact request
  3. You get back precisely the data you asked for

Why SQL is Important for MSP Professionals​

Client Database Management:

  • Extract reports for business clients
  • Troubleshoot application database issues
  • Migrate data between systems
  • Set up automated reporting systems

Career Advancement:

  • SQL skills are required for most IT positions
  • Database knowledge increases salary potential by 15-25%
  • Essential for cloud computing and business intelligence roles

Basic SQL Query Structure​

The Four Essential SQL Operations (CRUD)​

CRUD stands for Create, Read, Update, Delete - the four basic operations you can perform on database data.

Real-World Business Scenario: Managing customer information for "TechMart Electronics"

1. CREATE - Adding New Information​

Business Scenario: New customer walks into TechMart and wants to make a purchase. First, we need to add them to our customer database.

SQL Command: INSERT

INSERT INTO Customers (customer_id, first_name, last_name, email, phone, city)
VALUES (1001, 'Priya', 'Sharma', 'priya.sharma@email.com', '9876543210', 'Mumbai');

What this means in plain English: "Add a new customer record with ID 1001, name Priya Sharma, email priya.sharma@email.com, phone 9876543210, from Mumbai"

Business Impact: Customer can now make purchases, receive promotional emails, and be tracked for loyalty programs.

2. READ - Retrieving Information​

Business Scenario: Manager wants to see all customers from Mumbai to plan a local marketing campaign.

SQL Command: SELECT

SELECT first_name, last_name, email, phone
FROM Customers
WHERE city = 'Mumbai';

What this means in plain English: "Show me the name, email, and phone number of all customers who live in Mumbai"

Results:

first_name | last_name | email | phone
-----------|-----------|-------|-------
Priya      | Sharma    | priya.sharma@email.com | 9876543210
Rajesh     | Patel     | rajesh.patel@email.com | 9765432101
Anita      | Gupta     | anita.gupta@email.com  | 9654321012

Business Impact: Marketing team can target Mumbai customers with local promotions and offers.

3. UPDATE - Modifying Existing Information​

Business Scenario: Customer Priya Sharma moved to a new address and calls to update her information.

SQL Command: UPDATE

UPDATE Customers
SET city = 'Pune', phone = '9876543999'
WHERE customer_id = 1001;

What this means in plain English: "Find the customer with ID 1001 (Priya Sharma) and change her city to Pune and phone number to 9876543999"

Business Impact: Customer receives deliveries at correct address and can be reached at new phone number.

4. DELETE - Removing Information​

Business Scenario: Customer requests account deletion due to privacy concerns.

SQL Command: DELETE

DELETE FROM Customers
WHERE customer_id = 1001;

What this means in plain English: "Remove the customer record for customer ID 1001 from the database"

⚠️ Important Safety Note: DELETE operations are permanent! Always backup data and double-check your WHERE clause before executing.

Creating Database Tables​

Understanding Table Structure​

Business Scenario: TechMart needs to set up a new database to track customers, products, and orders.

Creating the Customers Table​

CREATE TABLE Customers (
    customer_id INT PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL,
    phone VARCHAR(15),
    city VARCHAR(50),
    registration_date DATE DEFAULT CURRENT_DATE
);

Breaking down each part:

Field Definitions:

  • customer_id INT PRIMARY KEY: Unique number for each customer (like Aadhaar number)
  • first_name VARCHAR(50) NOT NULL: Text field, maximum 50 characters, required
  • email VARCHAR(100) UNIQUE NOT NULL: Must be unique across all customers, required
  • phone VARCHAR(15): Optional field (customer might not provide phone)
  • registration_date DATE DEFAULT CURRENT_DATE: Automatically set to today's date

Data Types Explained:

  • INT: Whole numbers (1, 2, 1001, -5)
  • VARCHAR(n): Variable-length text up to n characters
  • DATE: Dates in YYYY-MM-DD format (2024-03-15)
  • DECIMAL(10,2): Numbers with decimals (for prices: 1299.99)
  • BOOLEAN: True/false values

Constraints Explained:

  • PRIMARY KEY: Unique identifier, no duplicates allowed
  • NOT NULL: Field must have a value, cannot be empty
  • UNIQUE: No two records can have the same value
  • DEFAULT: Automatic value if none provided

Products Table:

CREATE TABLE Products (
    product_id INT PRIMARY KEY,
    product_name VARCHAR(100) NOT NULL,
    category VARCHAR(50),
    price DECIMAL(10,2) NOT NULL,
    stock_quantity INT DEFAULT 0,
    supplier_id INT
);

Orders Table:

CREATE TABLE Orders (
    order_id INT PRIMARY KEY,
    customer_id INT,
    order_date DATE DEFAULT CURRENT_DATE,
    total_amount DECIMAL(10,2),
    status VARCHAR(20) DEFAULT 'Pending',
    FOREIGN KEY (customer_id) REFERENCES Customers(customer_id)
);

Foreign Key Relationship: The customer_id in Orders table must match an existing customer_id in Customers table. This ensures data integrity - you can't create an order for a non-existent customer.

Basic SQL Queries for Business Reporting​

Single Table Queries​

Business Questions and SQL Solutions:

Question 1: "How many customers do we have in each city?"

SELECT city, COUNT(*) as customer_count
FROM Customers
GROUP BY city
ORDER BY customer_count DESC;

Results:

city      | customer_count
----------|---------------
Mumbai    | 45
Delhi     | 38
Bangalore | 32
Pune      | 28

Business Insight: Mumbai has the most customers - consider opening a second branch there.

Question 2: "Which products are running low on stock (less than 10 units)?"

SELECT product_name, stock_quantity, price
FROM Products
WHERE stock_quantity `<` 10
ORDER BY stock_quantity ASC;

Results:

product_name | stock_quantity | price
-------------|----------------|--------
iPhone 15    | 3              | 79999
MacBook Pro  | 5              | 199999
Samsung TV   | 8              | 65000

Business Action: Reorder these products immediately to avoid stockouts.

Question 3: "What's our average order value by month?"

SELECT 
    MONTH(order_date) as month,
    YEAR(order_date) as year,
    AVG(total_amount) as average_order_value,
    COUNT(*) as number_of_orders
FROM Orders
WHERE order_date >= '2024-01-01'
GROUP BY YEAR(order_date), MONTH(order_date)
ORDER BY year, month;

Results:

month | year | average_order_value | number_of_orders
------|------|--------------------|-----------------
1     | 2024 | 15750.50           | 124
2     | 2024 | 18200.75           | 142
3     | 2024 | 21500.25           | 158

Business Insight: Average order value is increasing - customers are buying higher-value items or multiple products.

Joining Multiple Tables​

The Power of Relationships: Most business questions require information from multiple tables.

Business Question: "Show me the complete order details including customer names and product information"

Challenge: Order information is in Orders table, customer names are in Customers table, and we need to connect them.

Solution - SQL JOIN:

SELECT 
    o.order_id,
    o.order_date,
    c.first_name,
    c.last_name,
    c.city,
    o.total_amount,
    o.status
FROM Orders o
JOIN Customers c ON o.customer_id = c.customer_id
WHERE o.order_date >= '2024-01-01'
ORDER BY o.order_date DESC;

Results:

order_id | order_date | first_name | last_name | city    | total_amount | status
---------|------------|------------|-----------|---------|--------------|--------
5001     | 2024-03-15 | Priya      | Sharma    | Mumbai  | 25000        | Delivered
5002     | 2024-03-14 | Rajesh     | Kumar     | Delhi   | 18500        | Processing
5003     | 2024-03-13 | Anita      | Patel     | Pune    | 12000        | Shipped

How JOIN works:

  1. FROM Orders o: Start with Orders table (use 'o' as shorthand)
  2. JOIN Customers c: Connect to Customers table (use 'c' as shorthand)
  3. ON o.customer_id = c.customer_id: Match records where customer IDs are equal
  4. Result: Combined information from both tables

Advanced Business Queries​

Question: "Which customers from Mumbai have spent more than ₹50,000 in the last 6 months, and what did they buy?"

SELECT 
    c.first_name,
    c.last_name,
    c.email,
    SUM(o.total_amount) as total_spent,
    COUNT(o.order_id) as number_of_orders
FROM Customers c
JOIN Orders o ON c.customer_id = o.customer_id
WHERE c.city = 'Mumbai'
    AND o.order_date >= DATE_SUB(CURRENT_DATE, INTERVAL 6 MONTH)
GROUP BY c.customer_id, c.first_name, c.last_name, c.email
HAVING SUM(o.total_amount) > 50000
ORDER BY total_spent DESC;

Breaking down the complex query:

  • JOIN: Connect customers and orders
  • WHERE: Filter for Mumbai customers and recent orders
  • GROUP BY: Group orders by customer
  • HAVING: Filter grouped results (customers who spent > ₹50,000)
  • ORDER BY: Sort by highest spending first

Business Value: Identify VIP customers for special promotions and loyalty programs.

Data Filtering and Searching​

WHERE Clause - The Foundation of Data Filtering​

Basic Comparison Operators:

-- Equal to
SELECT * FROM Products WHERE price = 25000;

-- Not equal to
SELECT * FROM Products WHERE category > 'Electronics';

-- Greater than
SELECT * FROM Orders WHERE total_amount > 15000;

-- Less than or equal to
SELECT * FROM Products WHERE stock_quantity = 10;

-- Between values
SELECT * FROM Orders WHERE order_date BETWEEN '2024-01-01' AND '2024-03-31';

Text Searching with LIKE​

Business Scenario: Customer calls asking about "Samsung" products but doesn't know exact model names.

-- Find all products with "Samsung" in the name
SELECT product_name, price, stock_quantity
FROM Products
WHERE product_name LIKE '%Samsung%';

-- Find customers whose first name starts with 'R'
SELECT first_name, last_name, city
FROM Customers
WHERE first_name LIKE 'R%';

-- Find emails from Gmail accounts
SELECT first_name, last_name, email
FROM Customers
WHERE email LIKE '%@gmail.com';

LIKE Patterns:

  • %Samsung%: Contains "Samsung" anywhere
  • Samsung%: Starts with "Samsung"
  • %Samsung: Ends with "Samsung"
  • _amsung: Single character, then "amsung"

Combining Conditions with AND, OR​

Business Question: "Show customers from Mumbai OR Delhi who have made orders in the last month"

SELECT DISTINCT c.first_name, c.last_name, c.city, c.email
FROM Customers c
JOIN Orders o ON c.customer_id = o.customer_id
WHERE (c.city = 'Mumbai' OR c.city = 'Delhi')
    AND o.order_date >= DATE_SUB(CURRENT_DATE, INTERVAL 1 MONTH);

Logic Breakdown:

  • OR: Customer from Mumbai OR Delhi (either city is acceptable)
  • AND: Must also have recent orders (both conditions must be true)
  • Parentheses: Control order of operations, like in math

Handling NULL Values​

The NULL Challenge: What happens when data is missing?

Example: Some customers didn't provide phone numbers (NULL values)

-- Find customers without phone numbers
SELECT first_name, last_name, email
FROM Customers
WHERE phone IS NULL;

-- Find customers WITH phone numbers
SELECT first_name, last_name, phone
FROM Customers
WHERE phone IS NOT NULL;

-- Replace NULL values with default text
SELECT 
    first_name,
    last_name,
    COALESCE(phone, 'No phone provided') as phone_display
FROM Customers;

Important: Never use = NULL or > NULL - these don't work! Always use IS NULL or IS NOT NULL.

Sorting and Organizing Results​

ORDER BY - Organizing Your Results​

Business Scenarios:

Alphabetical customer list for marketing team:

SELECT first_name, last_name, city, email
FROM Customers
ORDER BY last_name ASC, first_name ASC;

Top-selling products by revenue:

SELECT 
    product_name,
    SUM(quantity * price) as total_revenue,
    COUNT(*) as times_sold
FROM Products p
JOIN Order_Items oi ON p.product_id = oi.product_id
GROUP BY product_name
ORDER BY total_revenue DESC
LIMIT 10;

Recent orders first:

SELECT order_id, customer_id, order_date, total_amount, status
FROM Orders
ORDER BY order_date DESC, total_amount DESC;

LIMIT - Controlling Result Size​

Business Use Cases:

Top 5 customers by total spending:

SELECT 
    c.first_name,
    c.last_name,
    SUM(o.total_amount) as total_spent
FROM Customers c
JOIN Orders o ON c.customer_id = o.customer_id
GROUP BY c.customer_id
ORDER BY total_spent DESC
LIMIT 5;

Most recent 20 orders:

SELECT order_id, order_date, total_amount, status
FROM Orders
ORDER BY order_date DESC
LIMIT 20;

Aggregation Functions - Summarizing Business Data​

COUNT - Counting Records​

Business Questions and Solutions:

-- How many customers do we have?
SELECT COUNT(*) as total_customers FROM Customers;

-- How many customers provided phone numbers?
SELECT COUNT(phone) as customers_with_phones FROM Customers;

-- How many orders were placed each month?
SELECT 
    YEAR(order_date) as year,
    MONTH(order_date) as month,
    COUNT(*) as orders_count
FROM Orders
GROUP BY YEAR(order_date), MONTH(order_date)
ORDER BY year DESC, month DESC;

SUM - Adding Up Values​

-- Total revenue for the year
SELECT SUM(total_amount) as yearly_revenue
FROM Orders
WHERE YEAR(order_date) = 2024;

-- Revenue by city
SELECT 
    c.city,
    SUM(o.total_amount) as city_revenue
FROM Orders o
JOIN Customers c ON o.customer_id = c.customer_id
GROUP BY c.city
ORDER BY city_revenue DESC;

AVG - Calculating Averages​

-- Average order value
SELECT AVG(total_amount) as average_order_value
FROM Orders;

-- Average order value by customer city
SELECT 
    c.city,
    AVG(o.total_amount) as avg_order_value,
    COUNT(o.order_id) as number_of_orders
FROM Orders o
JOIN Customers c ON o.customer_id = c.customer_id
GROUP BY c.city
HAVING COUNT(o.order_id) >= 10  -- Only cities with 10+ orders
ORDER BY avg_order_value DESC;

MIN and MAX - Finding Extremes​

-- Highest and lowest order values
SELECT 
    MIN(total_amount) as lowest_order,
    MAX(total_amount) as highest_order,
    AVG(total_amount) as average_order
FROM Orders;

-- First and last customer registration dates
SELECT 
    MIN(registration_date) as first_customer,
    MAX(registration_date) as latest_customer,
    COUNT(*) as total_customers
FROM Customers;

Business Intelligence Queries​

Customer Segmentation Analysis​

Business Goal: Identify different types of customers for targeted marketing.

SELECT 
    CASE 
        WHEN total_spent >= 100000 THEN 'VIP'
        WHEN total_spent >= 50000 THEN 'Premium'
        WHEN total_spent >= 20000 THEN 'Regular'
        ELSE 'Occasional'
    END as customer_segment,
    COUNT(*) as customer_count,
    AVG(total_spent) as avg_spending,
    AVG(order_count) as avg_orders
FROM (
    SELECT 
        c.customer_id,
        c.first_name,
        c.last_name,
        SUM(o.total_amount) as total_spent,
        COUNT(o.order_id) as order_count
    FROM Customers c
    LEFT JOIN Orders o ON c.customer_id = o.customer_id
    GROUP BY c.customer_id
) customer_summary
GROUP BY customer_segment
ORDER BY avg_spending DESC;

Results Analysis:

customer_segment | customer_count | avg_spending | avg_orders
-----------------|----------------|--------------|------------
VIP              | 12             | 150000       | 15.2
Premium          | 45             | 67500        | 8.1
Regular          | 156            | 32000        | 4.3
Occasional       | 287            | 8500         | 1.8

Business Actions:

  • VIP customers: Personal account manager, exclusive previews
  • Premium customers: Loyalty rewards, early access to sales
  • Regular customers: Newsletter, seasonal promotions
  • Occasional customers: Re-engagement campaigns, special offers

Sales Performance Analysis​

Monthly Sales Trend:

SELECT 
    DATE_FORMAT(order_date, '%Y-%m') as month,
    COUNT(*) as orders_count,
    SUM(total_amount) as revenue,
    AVG(total_amount) as avg_order_value,
    COUNT(DISTINCT customer_id) as unique_customers
FROM Orders
WHERE order_date >= '2024-01-01'
GROUP BY DATE_FORMAT(order_date, '%Y-%m')
ORDER BY month;

Product Performance Analysis:

SELECT 
    p.category,
    p.product_name,
    COUNT(oi.product_id) as times_sold,
    SUM(oi.quantity) as total_quantity,
    SUM(oi.quantity * oi.price) as total_revenue,
    AVG(oi.price) as avg_selling_price
FROM Products p
JOIN Order_Items oi ON p.product_id = oi.product_id
JOIN Orders o ON oi.order_id = o.order_id
WHERE o.order_date >= DATE_SUB(CURRENT_DATE, INTERVAL 3 MONTH)
GROUP BY p.category, p.product_name
ORDER BY total_revenue DESC;

Common SQL Mistakes and How to Avoid Them​

1. The Dangerous DELETE Without WHERE​

Wrong (Deletes ALL customers!):

DELETE FROM Customers;

Correct (Delete specific customer):

DELETE FROM Customers WHERE customer_id = 1001;

Best Practice: Always test with SELECT first:

-- First, check what you're about to delete
SELECT * FROM Customers WHERE customer_id = 1001;
-- If results look correct, then delete
DELETE FROM Customers WHERE customer_id = 1001;

2. Forgetting to Use Aggregate Functions with GROUP BY​

Wrong (Will cause error):

SELECT city, first_name, COUNT(*)
FROM Customers
GROUP BY city;

Correct (Either group by all non-aggregate columns):

SELECT city, COUNT(*) as customer_count
FROM Customers
GROUP BY city;

Or aggregate the other columns:

SELECT city, COUNT(*) as customer_count, GROUP_CONCAT(first_name) as names
FROM Customers
GROUP BY city;

3. Case Sensitivity in Text Comparisons​

Problem: Searching for 'mumbai' won't find 'Mumbai'

Solution:

-- Convert to lowercase for comparison
SELECT * FROM Customers WHERE LOWER(city) = LOWER('mumbai');

-- Or use case-insensitive LIKE
SELECT * FROM Customers WHERE city LIKE '%mumbai%';

4. Not Handling NULL Values Properly​

Wrong (Returns no results even if there are NULL values):

SELECT * FROM Customers WHERE phone = NULL;

Correct:

SELECT * FROM Customers WHERE phone IS NULL;

SQL Best Practices for Business Environments​

1. Always Backup Before Modifications​

Before any UPDATE or DELETE:

-- Create backup table
CREATE TABLE Customers_Backup_20240315 AS 
SELECT * FROM Customers;

-- Then perform your changes
UPDATE Customers SET city = 'New Delhi' WHERE city = 'Delhi';

Scenario: Customer places order - need to update both Orders and inventory

BEGIN TRANSACTION;

-- Insert the order
INSERT INTO Orders (customer_id, total_amount, status)
VALUES (1001, 25000, 'Confirmed');

-- Update product inventory
UPDATE Products 
SET stock_quantity = stock_quantity - 1
WHERE product_id = 3001;

-- If everything looks good, commit the changes
COMMIT;
-- If there are problems, rollback: ROLLBACK;

3. Use Meaningful Table and Column Names​

Good naming:

CREATE TABLE Customer_Orders (
    order_id INT PRIMARY KEY,
    customer_id INT,
    order_date DATE,
    total_amount_inr DECIMAL(10,2)
);

Poor naming:

CREATE TABLE tbl1 (
    id INT,
    cust INT,
    dt DATE,
    amt DECIMAL(10,2)
);

4. Comment Complex Queries​

-- Monthly revenue report for management dashboard
-- Shows revenue trends and customer acquisition metrics
SELECT 
    DATE_FORMAT(o.order_date, '%Y-%m') as month,
    SUM(o.total_amount) as monthly_revenue,
    COUNT(DISTINCT o.customer_id) as active_customers,
    -- Calculate average order value for the month
    AVG(o.total_amount) as avg_order_value
FROM Orders o
WHERE o.order_date >= DATE_SUB(CURRENT_DATE, INTERVAL 12 MONTH)
    AND o.status = 'Completed'  -- Only count completed orders
GROUP BY DATE_FORMAT(o.order_date, '%Y-%m')
ORDER BY month DESC;

Performance Considerations​

1. Use Indexes for Faster Queries​

Problem: Query taking 10 seconds to find customer by email

Solution: Create index on frequently searched columns

CREATE INDEX idx_customer_email ON Customers(email);
CREATE INDEX idx_order_date ON Orders(order_date);
CREATE INDEX idx_customer_city ON Customers(city);

Impact: Query time drops from 10 seconds to 0.1 seconds

2. Limit Results When Possible​

Instead of:

SELECT * FROM Orders ORDER BY order_date DESC;

Use:

SELECT * FROM Orders ORDER BY order_date DESC LIMIT 100;

3. Be Specific in SELECT Statements​

Instead of:

SELECT * FROM Customers;

Use:

SELECT customer_id, first_name, last_name, email FROM Customers;

Benefits: Faster queries, less network traffic, more secure

Key Takeaways​

  • SQL is the standard language for communicating with relational databases
  • The four basic operations (CRUD) handle most database interactions: CREATE (INSERT), READ (SELECT), UPDATE, DELETE
  • JOIN operations combine data from multiple tables to answer complex business questions
  • WHERE clauses filter data, ORDER BY organizes results, and aggregate functions summarize information
  • Good SQL practices include backing up data, using transactions, meaningful naming, and proper error handling
  • Database performance improves with indexes, limited result sets, and specific column selection
  • SQL skills are essential for MSP professionals working with client business systems and reporting requirements

What's Next?​

In the next section, we'll explore database administration tasks including backup and recovery, user management, and security implementation. You'll learn how to maintain databases in production environments and ensure data integrity and availability for business-critical applications.