Introduction to SQL

Context:

-- SQL is based on a mathematical model 
-- proposed in a famous 1970 paper at IBM

Task: Who proposed the relational model that SQL is based on? Name the person and the year.

Hint: The paper was titled "A Relational Model of Data for Large Shared Data Banks".

-- Answer: Dr. Edgar F. Codd at IBM in 1970
-- His paper "A Relational Model of Data for Large 
-- Shared Data Banks" founded relational database theory

Given:

-- Table: employees
-- Columns: name, department, salary

Task: Write a query that ranks employees by salary (highest first). What SQL version introduced window functions?

Hint: Use RANK() OVER (ORDER BY ...) syntax.

-- SQL:2003 introduced window functions
SELECT name, salary,
       RANK() OVER (ORDER BY salary DESC) as rank
FROM employees;

Context:

-- In the late 1970s, a company saw the potential
-- of the relational model and created the first
-- commercial SQL database system

Task: What was the first commercially available SQL database and in what year was it released?

Hint: This company is still one of the leading enterprise database vendors today.

-- Answer: Oracle (1979)
-- Relational Software Inc. (now Oracle Corporation) 
-- released the first commercial SQL-based RDBMS

-- Today Oracle is still one of the leading 
-- enterprise database systems

Given:

-- Table: customers
-- Column: data (JSON type)
-- Sample data: {"name": "John", "email": "john@example.com", "status": "active"}

Task: Write a query to extract the name and email from the JSON data column for active customers. SQL:2016 introduced JSON support.

Hint: PostgreSQL uses ->> operator, MySQL uses JSON_EXTRACT().

-- PostgreSQL example extracting JSON data
SELECT 
    data->>'name' as customer_name,
    data->>'email' as email
FROM customers
WHERE data->>'status' = 'active';

-- MySQL example
SELECT 
    JSON_EXTRACT(data, '$.name') as customer_name
FROM customers;

Requirements:

-- Table: users
-- id: integer, primary key
-- name: text up to 100 characters, required
-- email: text up to 255 characters, must be unique

Task: Write SQL to create a users table with id, name, and email columns with the constraints specified above.

Hint: Use INT, VARCHAR, PRIMARY KEY, NOT NULL, and UNIQUE constraints.

CREATE TABLE users (
    id INT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(255) UNIQUE
);

Given:

-- Existing table: users (id INT PRIMARY KEY, ...)
-- New table: orders
-- Columns: order_id, user_id, order_date, total

Task: Create an orders table with a foreign key that references the users table's id column.

Hint: Use FOREIGN KEY (column) REFERENCES table(column) syntax.

CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    user_id INT,
    order_date DATE,
    total DECIMAL(10, 2),
    FOREIGN KEY (user_id) REFERENCES users(id)
);

Requirements:

-- Table: products
-- product_id: auto-incrementing primary key
-- name: required, up to 200 characters
-- price: required, decimal with 2 decimal places
-- stock: integer, defaults to 0

Task: Create a products table where product_id automatically increments for each new row. Show syntax for both MySQL and PostgreSQL.

Hint: MySQL uses AUTO_INCREMENT, PostgreSQL uses SERIAL.

-- MySQL syntax
CREATE TABLE products (
    product_id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(200) NOT NULL,
    price DECIMAL(10, 2) NOT NULL,
    stock INT DEFAULT 0
);

-- PostgreSQL syntax
CREATE TABLE products (
    product_id SERIAL PRIMARY KEY,
    name VARCHAR(200) NOT NULL,
    price DECIMAL(10, 2) NOT NULL,
    stock INT DEFAULT 0
);

Scenario:

-- Two users are accessing the same bank account
-- User A: Reading balance
-- User B: Updating balance
-- Problem: User A might see inconsistent data

Task: Which ACID property ensures that concurrent transactions see a consistent database state? Write SQL to set the highest isolation level.

Hint: The property starts with "I" and controls visibility between transactions.

-- Answer: Isolation
-- Isolation ensures concurrent transactions don't 
-- interfere with each other

-- Example: Setting transaction isolation level
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

-- SQL Server example
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

Context:

-- Databases can be categorized as:
-- SQL (Relational): Structured tables with relationships
-- NoSQL (Non-Relational): Flexible schemas, various types

Task: Name two examples of SQL databases and two NoSQL databases. Also identify what type of NoSQL each is (Document, Key-Value, Graph, etc.).

Hint: Think of popular open-source and cloud databases.

-- SQL Databases (Relational):
-- 1. MySQL
-- 2. PostgreSQL
-- Others: SQL Server, Oracle, SQLite

-- NoSQL Databases (Non-Relational):
-- 1. MongoDB (Document)
-- 2. Redis (Key-Value)
-- Others: Cassandra, Neo4j, DynamoDB

Scenario:

-- Application: Online Banking System
-- Requirements:
-- - Money transfers between accounts
-- - Transaction history and reporting
-- - Regulatory compliance and auditing
-- - Multiple concurrent users

Task: Would you use SQL or NoSQL for this banking application? List at least 3 reasons for your choice.

Hint: Consider ACID properties and data integrity requirements.

-- Answer: SQL Database (e.g., PostgreSQL, Oracle)

-- Reasons:
-- 1. ACID compliance ensures transaction integrity
-- 2. Money transfers need atomicity (all or nothing)
-- 3. Complex queries for reports and auditing
-- 4. Strict schema prevents data corruption
-- 5. Regulatory compliance requirements

Scenario:

-- Application: Mobile Note-Taking App
-- Requirements:
-- - Works offline
-- - Minimal setup and configuration
-- - Small memory footprint
-- - Built-in to mobile platforms

Task: Which database would you choose for a mobile app that needs local storage? Provide at least 3 reasons.

Hint: Think about what comes built into Android and iOS.

-- Answer: SQLite

-- Reasons:
-- 1. Lightweight (serverless, file-based)
-- 2. Zero configuration needed
-- 3. Built into Android and iOS
-- 4. Small memory footprint
-- 5. Works offline

Given:

-- Table: users
-- Column: data (JSONB type)
-- Sample: {"name": "Alice", "active": true, "role": "admin"}

Task: Which SQL database is known for the best JSON support? Write a query to select the name where active is true.

Hint: This database uses JSONB (binary JSON) for better performance.

-- Answer: PostgreSQL

-- PostgreSQL has native JSONB type with indexing:
SELECT data->>'name' as name
FROM users
WHERE data @> '{"active": true}';

-- JSONB is binary JSON with better performance

Scenario:

-- Application: Enterprise HR System
-- Tech Stack: .NET Core, C#, Azure Cloud
-- Requirements:
-- - Integration with Azure services
-- - Business Intelligence and reporting
-- - Enterprise security and compliance

Task: What database would be best for a .NET enterprise application on Azure? List 3 reasons.

Hint: Consider Microsoft's ecosystem and cloud offerings.

-- Answer: SQL Server (or Azure SQL Database)

-- Reasons:
-- 1. Native integration with .NET and C#
-- 2. Azure SQL Database for cloud deployment
-- 3. Enterprise features (BI, reporting)
-- 4. Strong security and compliance
-- 5. Microsoft ecosystem integration

Given:

-- Connection details:
-- Host: localhost
-- Database: mydb
-- Username: admin
-- Password: secret
-- MySQL Port: 3306
-- PostgreSQL Port: 5432

Task: Write connection strings for both MySQL and PostgreSQL databases using the details above. Also show how to connect using Python's psycopg2.

Hint: Connection strings follow the format: protocol://user:pass@host:port/database

-- MySQL connection string format:
-- mysql://admin:secret@localhost:3306/mydb

-- PostgreSQL connection string format:
-- postgresql://admin:secret@localhost:5432/mydb

-- Python example with PostgreSQL:
-- import psycopg2
-- conn = psycopg2.connect(
--     host="localhost",
--     database="mydb",
--     user="admin",
--     password="secret"
-- )