DATABASES & SQL — ZERO TO HERO

2 What is a Database? | SQL from Scratch | PostgreSQL | Cloud SQL | Hands-On Labs

2.1 What is a Database? (Explained Simply)

Imagine you run a shop. You write down every sale in a notebook — customer name, item bought, price, date.
After a few months, finding one specific sale is painful. You'd need to flip through hundreds of pages. A
database is like that notebook but stored on a computer — and it can find any record instantly, handle millions
of entries, and let multiple people use it at the same time.

💡 REAL-LIFE ANALOGY

A DATABASE is like a well-organized filing cabinet.
- Each DRAWER = a Table (e.g. Customers, Orders, Products)
- Each FOLDER = a Row (one customer, one order)
- Each LABEL on the folder = a Column (name, age, city)
SQL is the language you use to open drawers and find exactly what you need.

2.2 Types of Databases

Type What It Means Examples Used For

Data stored in tables with rows & PostgreSQL, MySQL,

Relational (SQL) Orders, Customers, Inventory
columns Cloud SQL

Document Data stored as JSON-like

Firestore, MongoDB User profiles, Product catalogs
(NoSQL) documents

Key-Value Simple key → value pairs, Sessions, Caching,

Redis, Memorystore
(NoSQL) extremely fast Leaderboards

Rows with dynamic columns,

Wide-Column Bigtable, Cassandra IoT sensor data, Time-series
massive scale

Optimized for read-heavy

Analytical (OLAP) BigQuery, Snowflake Data warehouses, Reporting
analytics

Stores relationships between Social networks, Fraud

Graph Neo4j, Spanner
entities detection

2.3 SQL From Scratch — The Language of Data

SQL (Structured Query Language) is the universal language for talking to databases. It's not really a
'programming language' — think of it more like asking questions in plain English but with a specific structure.
Once you learn SQL, you can work with almost any database.

Creating Your First Table — E-Commerce Example

-- In an e-commerce company, we need to track customers and orders.
-- Let's create the tables:
GCP Cloud Data Engineer — Zero to Hero All Industries | Hands-On | Zero to Hero

-- TABLE 1: Customers
CREATE TABLE customers (
customer_id SERIAL PRIMARY KEY, -- Auto-increment unique ID
full_name VARCHAR(100) NOT NULL, -- Name, max 100 chars, required
email VARCHAR(150) UNIQUE, -- Must be unique across all rows
city VARCHAR(50),
created_at TIMESTAMP DEFAULT NOW() -- Auto-set to current time
);

-- TABLE 2: Products
CREATE TABLE products (
product_id SERIAL PRIMARY KEY,
product_name VARCHAR(200) NOT NULL,
category VARCHAR(50),
price DECIMAL(10, 2), -- Up to 10 digits, 2 decimal places
stock_qty INTEGER DEFAULT 0
);

-- TABLE 3: Orders (links customers to products)

CREATE TABLE orders (
order_id SERIAL PRIMARY KEY,
customer_id INTEGER REFERENCES customers(customer_id), -- Foreign Key
product_id INTEGER REFERENCES products(product_id), -- Foreign Key
quantity INTEGER NOT NULL,
total_amount DECIMAL(10, 2),
order_date DATE NOT NULL,
status VARCHAR(20) DEFAULT 'pending'
);

Inserting Data — Healthcare Example

-- Let's add some patients and appointments to a hospital database:

CREATE TABLE patients (

patient_id SERIAL PRIMARY KEY,
full_name VARCHAR(100) NOT NULL,
date_of_birth DATE,
blood_group VARCHAR(5),
phone VARCHAR(15)
);

-- Insert sample data:

INSERT INTO patients (full_name, date_of_birth, blood_group, phone) VALUES
('Ravi Kumar', '1985-03-15', 'O+', '9876543210'),
('Priya Sharma', '1992-07-22', 'A+', '9876543211'),
('Ahmed Khan', '1978-11-30', 'B+', '9876543212'),
('Sunita Reddy', '2000-01-10', 'AB-', '9876543213');

-- Verify:
SELECT * FROM patients;

Querying Data — The SELECT Statement

-- Basic SELECT: Get all columns from orders table
SELECT * FROM orders;

-- Select specific columns (ALWAYS prefer this over * in production)

SELECT order_id, customer_id, total_amount, order_date

GCP PDE | 6 Sigma | Docker + GCP Hands-On Page 2 E-Commerce | Healthcare | Finance
GCP Cloud Data Engineer — Zero to Hero All Industries | Hands-On | Zero to Hero

FROM orders;

-- WHERE: Filter rows (like saying 'only show me orders above ■1000')
SELECT * FROM orders
WHERE total_amount > 1000
AND status = 'completed';

-- Finance example: Find transactions above ■50,000

SELECT transaction_id, account_number, amount, transaction_date
FROM transactions
WHERE amount > 50000
AND transaction_type = 'debit'
ORDER BY transaction_date DESC; -- Newest first

-- LIMIT: Get only first 10 rows (avoid returning millions)

SELECT * FROM orders LIMIT 10;

Aggregations — Summarizing Data

-- COUNT: How many orders do we have?
SELECT COUNT(*) AS total_orders FROM orders;

-- SUM, AVG, MIN, MAX

SELECT
COUNT(*) AS total_orders,
SUM(total_amount) AS total_revenue,
AVG(total_amount) AS avg_order_value,
MIN(total_amount) AS smallest_order,
MAX(total_amount) AS largest_order
FROM orders
WHERE status = 'completed';

-- GROUP BY: Revenue by city (e-commerce example)

SELECT
[Link],
COUNT(o.order_id) AS num_orders,
SUM(o.total_amount) AS total_revenue,
ROUND(AVG(o.total_amount), 2) AS avg_order
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id
WHERE [Link] = 'completed'
GROUP BY [Link]
HAVING SUM(o.total_amount) > 10000 -- Only cities with >10k revenue
ORDER BY total_revenue DESC;

JOINs — Combining Tables (Most Important Concept!)

💡 REAL-LIFE ANALOGY

A JOIN is like matching entries from two lists.
Imagine: List A = Customer Names. List B = Their Orders.
INNER JOIN = Only customers who HAVE orders
LEFT JOIN = ALL customers, even those with NO orders (nulls for missing orders)
RIGHT JOIN = ALL orders, even if customer data is missing
FULL JOIN = Everything from both lists, matched where possible

GCP PDE | 6 Sigma | Docker + GCP Hands-On Page 3 E-Commerce | Healthcare | Finance
GCP Cloud Data Engineer — Zero to Hero All Industries | Hands-On | Zero to Hero

-- INNER JOIN: Get orders with customer names and product details
SELECT
o.order_id,
c.full_name AS customer_name,
p.product_name,
[Link],
o.total_amount,
o.order_date
FROM orders o
INNER JOIN customers c ON o.customer_id = c.customer_id
INNER JOIN products p ON o.product_id = p.product_id
WHERE o.order_date >= '2024-01-01'
ORDER BY o.order_date DESC;

-- LEFT JOIN: Find customers who have NEVER placed an order

SELECT
c.customer_id,
c.full_name,
[Link],
o.order_id -- This will be NULL if no orders
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
WHERE o.order_id IS NULL; -- Filter: only customers with no orders

Advanced SQL — Window Functions (Game Changer!)

Window functions let you do calculations ACROSS rows without losing individual row details. This is one of the
most powerful SQL features a data engineer must know.
-- RANK customers by total spending (e-commerce)
SELECT
c.full_name,
[Link],
SUM(o.total_amount) AS total_spent,
RANK() OVER (ORDER BY SUM(o.total_amount) DESC) AS spending_rank,
RANK() OVER (PARTITION BY [Link] ORDER BY SUM(o.total_amount) DESC) AS city_rank
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id
GROUP BY c.full_name, [Link];

-- Running total of daily revenue (finance)

SELECT
transaction_date,
daily_revenue,
SUM(daily_revenue) OVER (ORDER BY transaction_date) AS running_total,
LAG(daily_revenue, 1) OVER (ORDER BY transaction_date) AS prev_day,
ROUND(100.0 * (daily_revenue - LAG(daily_revenue,1)
OVER (ORDER BY transaction_date))
/ NULLIF(LAG(daily_revenue,1) OVER (ORDER BY transaction_date),0), 2)
AS day_over_day_pct
FROM daily_revenue_summary;

-- Find each patient's most recent appointment (healthcare)

SELECT * FROM (
SELECT
patient_id,
appointment_date,
doctor_name,
ROW_NUMBER() OVER (
PARTITION BY patient_id

GCP PDE | 6 Sigma | Docker + GCP Hands-On Page 4 E-Commerce | Healthcare | Finance
GCP Cloud Data Engineer — Zero to Hero All Industries | Hands-On | Zero to Hero

ORDER BY appointment_date DESC

) AS rn
FROM appointments
) ranked
WHERE rn = 1; -- Keep only the most recent per patient

CTEs — Common Table Expressions (Write Readable SQL)

-- CTEs make complex queries readable — like building blocks
-- Real example: Find top 10% customers by revenue

WITH customer_revenue AS (
-- Step 1: Calculate total revenue per customer
SELECT
customer_id,
SUM(total_amount) AS lifetime_value
FROM orders
WHERE status = 'completed'
GROUP BY customer_id
),
ranked_customers AS (
-- Step 2: Rank them
SELECT
customer_id,
lifetime_value,
NTILE(10) OVER (ORDER BY lifetime_value DESC) AS decile
FROM customer_revenue
)
-- Step 3: Get details for top 10% (decile 1)
SELECT
c.full_name,
[Link],
rc.lifetime_value
FROM ranked_customers rc
JOIN customers c ON rc.customer_id = c.customer_id
WHERE [Link] = 1
ORDER BY rc.lifetime_value DESC;

2.4 Hands-On Lab — Build an E-Commerce Database Locally

⚓ HANDS-ON LAB

LAB 2.1 — Full E-Commerce PostgreSQL Setup
Goal: Create a real e-commerce database with sample data and run analytical queries.
Environment: Docker (from Lab 1.1) — PostgreSQL already running at localhost:5432
Time: ~45 minutes
Skills: CREATE TABLE, INSERT, SELECT, JOIN, GROUP BY, Window Functions

Step 1: Create the Schema

-- Connect to PostgreSQL:
-- Option A: Using psql in terminal
psql -h localhost -U dataeng -d sourcedb

-- Option B: In pgAdmin ([Link]

GCP PDE | 6 Sigma | Docker + GCP Hands-On Page 5 E-Commerce | Healthcare | Finance
GCP Cloud Data Engineer — Zero to Hero All Industries | Hands-On | Zero to Hero

-- Right-click on sourcedb → Query Tool → paste SQL

-- Create all tables:

CREATE SCHEMA IF NOT EXISTS ecommerce;

CREATE TABLE [Link] (

customer_id SERIAL PRIMARY KEY,
full_name VARCHAR(100) NOT NULL,
email VARCHAR(150) UNIQUE NOT NULL,
city VARCHAR(50),
state VARCHAR(50),
segment VARCHAR(20) DEFAULT 'regular', -- 'premium', 'vip'
created_at TIMESTAMP DEFAULT NOW()
);

CREATE TABLE [Link] (

product_id SERIAL PRIMARY KEY,
product_name VARCHAR(200) NOT NULL,
category VARCHAR(50),
sub_category VARCHAR(50),
price DECIMAL(10,2) NOT NULL,
cost DECIMAL(10,2),
stock_qty INTEGER DEFAULT 100
);

CREATE TABLE [Link] (

order_id SERIAL PRIMARY KEY,
customer_id INTEGER REFERENCES [Link](customer_id),
order_date DATE NOT NULL,
status VARCHAR(20) DEFAULT 'pending',
payment_mode VARCHAR(20),
total_amount DECIMAL(10,2)
);

CREATE TABLE ecommerce.order_items (

item_id SERIAL PRIMARY KEY,
order_id INTEGER REFERENCES [Link](order_id),
product_id INTEGER REFERENCES [Link](product_id),
quantity INTEGER NOT NULL,
unit_price DECIMAL(10,2),
discount_pct DECIMAL(5,2) DEFAULT 0
);

Step 2: Load Sample Data with Python

# save as: load_ecommerce_data.py
import psycopg2
import random
from datetime import date, timedelta
from faker import Faker # pip install faker

fake = Faker('en_IN') # Indian locale for realistic data

conn = [Link](
host='localhost', port=5432,
database='sourcedb', user='dataeng', password='dataeng123'
)
cur = [Link]()

GCP PDE | 6 Sigma | Docker + GCP Hands-On Page 6 E-Commerce | Healthcare | Finance
GCP Cloud Data Engineer — Zero to Hero All Industries | Hands-On | Zero to Hero

# Insert 500 customers

cities = ['Mumbai','Delhi','Bangalore','Hyderabad','Chennai','Pune','Kolkata']
segments = ['regular','premium','vip']
for _ in range(500):
[Link](
'''INSERT INTO [Link] (full_name,email,city,state,segment)
VALUES (%s,%s,%s,%s,%s)''',
([Link](), [Link](), [Link](cities),
[Link](), [Link](segments))
)

# Insert 100 products

categories = [('Electronics','Mobile'),('Electronics','Laptop'),
('Clothing','Shirts'),('Clothing','Shoes'),
('Books','Fiction'),('Books','Technical')]
for i in range(100):
cat, sub = [Link](categories)
price = round([Link](199, 89999), 2)
[Link](
'''INSERT INTO [Link]
(product_name,category,sub_category,price,cost,stock_qty)
VALUES (%s,%s,%s,%s,%s,%s)''',
(f'{sub} Model {i+1}', cat, sub, price,
round(price*0.6,2), [Link](10,500))
)

[Link]()
print('Sample data loaded! 500 customers + 100 products')

# Run: python3 load_ecommerce_data.py

Step 3: Run Analytical Queries

-- Query 1: Top 5 cities by revenue
SELECT [Link], COUNT(o.order_id) AS orders,
SUM(o.total_amount) AS revenue
FROM [Link] o
JOIN [Link] c USING (customer_id)
GROUP BY [Link] ORDER BY revenue DESC LIMIT 5;

-- Query 2: Monthly revenue trend

SELECT DATE_TRUNC('month', order_date) AS month,
COUNT(*) AS orders,
SUM(total_amount) AS revenue
FROM [Link] WHERE status='completed'
GROUP BY 1 ORDER BY 1;

-- Query 3: Best selling product categories

SELECT [Link], p.sub_category,
SUM([Link]) AS units_sold,
SUM([Link] * oi.unit_price) AS revenue
FROM ecommerce.order_items oi
JOIN [Link] p USING (product_id)
GROUP BY 1,2 ORDER BY revenue DESC;

GCP PDE | 6 Sigma | Docker + GCP Hands-On Page 7 E-Commerce | Healthcare | Finance
GCP Cloud Data Engineer — Zero to Hero All Industries | Hands-On | Zero to Hero

✓ TIP
PRACTICE CHALLENGE: Try to write these queries yourself before looking at the answer:
1. Find customers who placed more than 5 orders in the last 3 months
2. Find products that have never been ordered
3. Calculate the month-over-month revenue growth percentage
4. Find the top 3 products in each category by revenue
HINT for #4: Use RANK() OVER (PARTITION BY category ORDER BY revenue DESC)

2.5 GCP Cloud SQL — PostgreSQL in the Cloud

Cloud SQL is Google's fully managed relational database service. It's exactly like PostgreSQL (or MySQL/SQL
Server) but Google handles backups, security patches, replication, and scaling for you. Use it when you need a
traditional database in the cloud.
# Create a Cloud SQL instance using gcloud:
gcloud sql instances create ecommerce-db \
--database-version=POSTGRES_15 \
--region=us-central1 \
--tier=db-f1-micro \ # Smallest tier — use for learning
--storage-size=10GB \
--backup-start-time=02:00

# Create database and user:

gcloud sql databases create ecommerce --instance=ecommerce-db
gcloud sql users create dataeng --instance=ecommerce-db --password=dataeng123

# Connect from your laptop (requires Cloud SQL Proxy):

# Download: [Link]
./cloud-sql-proxy PROJECT:REGION:ecommerce-db

# Then connect as usual:

psql -h [Link] -U dataeng -d ecommerce

★ EXAM TIP

GCP PDE EXAM: Key Cloud SQL facts to remember:
- Cloud SQL supports: PostgreSQL, MySQL, SQL Server
- Use Cloud Spanner when you need GLOBAL scale + strong consistency
- Cloud SQL max storage: 64TB. Max connections: 4000 (PostgreSQL)
- Always use Private IP for Cloud SQL in production (not Public IP)
- Cloud SQL has automatic backups, point-in-time recovery, failover replicas

GCP PDE | 6 Sigma | Docker + GCP Hands-On Page 8 E-Commerce | Healthcare | Finance