SELECT & Data Retrieval: Real-World

Every database interaction starts with SELECT. Used to generate reports, populate dashboards, export data, validate records, and power every application that reads from a database. A typical web application makes 100-1000 SELECT queries per second. At scale, SELECT * on a 30-column table transfers 10x more data than selecting 3 needed columns - costing thousands in bandwidth and latency.

E-Commerce Product Catalog API

Building product listing API with proper column selection:

PRODUCTS (product_id, sku, name, description, price, cost, inventory, created_at, updated_at)

Specific column selection: (1) Hides sensitive data (cost) from public API (2) Reduces bandwidth (3) Enables index-only scans (4) Clear API contract

API endpoint: GET /products (public)

SELECT product_id, name, description, price, inventory

FROM products

WHERE inventory > 0

ORDER BY created_at DESC

LIMIT 20;

Admin dashboard: Include cost and margins

SELECT product_id, name, price, cost,

(price - cost) AS profit,

ROUND((price - cost) / price * 100, 2) AS margin_percent

FROM products

ORDER BY margin_percent DESC;

All

User Dashboard with Calculated Fields

Employee dashboard showing calculated compensation:

EMPLOYEES (employee_id, first_name, last_name, salary, bonus, commission, hire_date)

Calculated fields in SELECT: (1) Business logic in database (2) Consistent calculations (3) Reduced application code (4) Easy to audit

Employee compensation summary

SELECT employee_id,

CONCAT(first_name, ' ', last_name) AS full_name,

salary AS base_salary,

COALESCE(bonus, 0) AS bonus,

COALESCE(commission, 0) AS commission,

salary + COALESCE(bonus, 0) + COALESCE(commission, 0) AS total_comp,

YEAR(CURRENT_DATE) - YEAR(hire_date) AS years_employed

FROM employees

ORDER BY total_comp DESC;

All

Analytics Dashboard with DISTINCT

Reporting unique values for filters and dropdowns:

ORDERS (order_id, customer_id, status, country, created_at)

DISTINCT for analytics: (1) Populate filter dropdowns (2) Count unique values (3) Remove duplicates for reporting (4) Data quality checks

Get unique countries for filter dropdown

SELECT DISTINCT country

FROM orders