9.1 User Roles and Permissions

Proper user management is the first line of defense in database security. It involves creating users, assigning roles, and granting specific permissions.

Creating Users and Roles

CREATE USER john_doe WITH PASSWORD 'secure_password';
    
    CREATE ROLE read_only;
    
    GRANT read_only TO john_doe;

Granting and Revoking Permissions

GRANT SELECT ON employees TO read_only;
    
    REVOKE INSERT, UPDATE, DELETE ON employees FROM read_only;

Role-Based Access Control (RBAC)

CREATE ROLE hr_manager;
    GRANT SELECT, INSERT, UPDATE ON employees TO hr_manager;
    GRANT SELECT ON payroll TO hr_manager;
    
    CREATE ROLE finance_analyst;
    GRANT SELECT ON financial_reports TO finance_analyst;
    
    CREATE USER jane_smith WITH PASSWORD 'secure_pwd123';
    GRANT hr_manager TO jane_smith;

Principle of Least Privilege

Always grant the minimum necessary permissions to users and roles. Regularly review and audit user permissions to ensure they align with current job responsibilities.

9.2 SQL Injection and Prevention

SQL injection is a technique where malicious SQL statements are inserted into application queries. It's one of the most common web hacking techniques.

Vulnerable Code (Do Not Use)

// This is vulnerable to SQL injection
    string query = "SELECT * FROM users WHERE username = '" + username + "' AND password = '" + password + "'";

Preventing SQL Injection

// Use parameterized queries
    string query = "SELECT * FROM users WHERE username = @Username AND password = @Password";
    command.Parameters.AddWithValue("@Username", username);
    command.Parameters.AddWithValue("@Password", password);

Input Validation and Sanitization

In addition to using parameterized queries, implement strict input validation and sanitization to further protect against SQL injection attempts.

Stored Procedures

Use stored procedures to encapsulate SQL logic and reduce the risk of SQL injection.

CREATE PROCEDURE GetUserByCredentials
        @Username NVARCHAR(50),
        @Password NVARCHAR(50)
    AS
    BEGIN
        SELECT * FROM Users
        WHERE Username = @Username AND Password = @Password
    END
    
    -- Usage
    EXEC GetUserByCredentials @Username = 'john', @Password = 'password123';

9.3 Data Encryption

Encryption is used to protect sensitive data stored in the database. Most modern database systems provide built-in encryption functions.

Transparent Data Encryption (TDE)

TDE encrypts the entire database at rest, including backups and log files.

-- Enable TDE (SQL Server example)
    USE master;
    GO
    CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'StrongPassword123!';
    GO
    CREATE CERTIFICATE TDECert WITH SUBJECT = 'TDE Certificate';
    GO
    USE YourDatabase;
    GO
    CREATE DATABASE ENCRYPTION KEY
    WITH ALGORITHM = AES_256
    ENCRYPTION BY SERVER CERTIFICATE TDECert;
    GO
    ALTER DATABASE YourDatabase
    SET ENCRYPTION ON;
    GO

Column-Level Encryption

Encrypt specific columns containing sensitive data.

-- Create a symmetric key
    CREATE SYMMETRIC KEY SSNKey
    WITH ALGORITHM = AES_256
    ENCRYPTION BY CERTIFICATE TDECert;
    
    -- Encrypt data
    OPEN SYMMETRIC KEY SSNKey
    DECRYPTION BY CERTIFICATE TDECert;
    
    INSERT INTO Employees (Name, EncryptedSSN)
    VALUES ('John Doe', EncryptByKey(Key_GUID('SSNKey'), '123-45-6789'));
    
    -- Decrypt data
    SELECT Name, CONVERT(VARCHAR, DecryptByKey(EncryptedSSN))
    FROM Employees;

Encryption in Transit

Use SSL/TLS to encrypt data in transit between the client and the database server.

9.4 Auditing and Monitoring SQL Activity

Auditing helps track database activities, which is crucial for security and compliance purposes.

Setting Up Auditing (SQL Server Example)

-- Create a server audit
    CREATE SERVER AUDIT MyAudit
    TO FILE (
        FILEPATH = 'C:\SQLAudit\',
        MAXSIZE = 100 MB,
        MAX_ROLLOVER_FILES = 10
    );
    
    -- Create an audit specification
    CREATE SERVER AUDIT SPECIFICATION MyAuditSpec
    FOR SERVER AUDIT MyAudit
    ADD (FAILED_LOGIN_GROUP),
    ADD (SUCCESSFUL_LOGIN_GROUP),
    ADD (DATABASE_CHANGE_GROUP)
    WITH (STATE = ON);
    
    -- Enable the audit
    ALTER SERVER AUDIT MyAudit WITH (STATE = ON);

Querying Audit Logs

SELECT event_time, action_id, succeeded, session_id, server_principal_name
    FROM sys.fn_get_audit_file('C:\SQLAudit\*', NULL, NULL)
    WHERE action_id = 'LGIF' -- Failed logins
    ORDER BY event_time DESC;

Real-time Monitoring

Implement real-time monitoring solutions to detect and alert on suspicious activities.

Log Management

Establish a robust log management system to securely store and analyze audit logs for extended periods.

9.5 Additional Security Best Practices

• Regularly update and patch your database management system
• Implement strong password policies
• Use multi-factor authentication for database access
• Segment your network and use firewalls to control database access
• Regularly backup your database and test restore procedures
• Implement data masking for non-production environments
• Conduct regular security audits and penetration testing
• Educate your team on SQL security best practices

10.1 Normalization (1NF, 2NF, 3NF, BCNF)

Normalization is the process of organizing data to minimize redundancy and dependency. It involves breaking down larger tables into smaller, more manageable ones.

Example: Normalizing a Table

CREATE TABLE orders (
        order_id INT,
        customer_name VARCHAR(100),
        product_name VARCHAR(100),
        quantity INT,
        order_date DATE,
        customer_email VARCHAR(100)
    );

CREATE TABLE customers (
        customer_id INT PRIMARY KEY,
        customer_name VARCHAR(100),
        customer_email VARCHAR(100)
    );
    
    CREATE TABLE products (
        product_id INT PRIMARY KEY,
        product_name VARCHAR(100)
    );
    
    CREATE TABLE orders (
        order_id INT PRIMARY KEY,
        customer_id INT,
        order_date DATE,
        FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
    );
    
    CREATE TABLE order_items (
        order_id INT,
        product_id INT,
        quantity INT,
        PRIMARY KEY (order_id, product_id),
        FOREIGN KEY (order_id) REFERENCES orders(order_id),
        FOREIGN KEY (product_id) REFERENCES products(product_id)
    );

10.2 Denormalization Techniques

Denormalization is the process of adding redundant data to one or more tables to improve read performance. It's often used in data warehousing and reporting systems.

Example: Denormalizing for Reporting

CREATE TABLE order_summary (
        order_id INT PRIMARY KEY,
        customer_name VARCHAR(100),
        total_amount DECIMAL(10, 2),
        order_date DATE,
        -- Denormalized data
        product_count INT,
        last_product_name VARCHAR(100)
    );
    
    -- Populate the denormalized table
    INSERT INTO order_summary (order_id, customer_name, total_amount, order_date, product_count, last_product_name)
    SELECT 
        o.order_id,
        c.customer_name,
        SUM(oi.quantity * p.price) AS total_amount,
        o.order_date,
        COUNT(DISTINCT oi.product_id) AS product_count,
        FIRST_VALUE(p.product_name) OVER (PARTITION BY o.order_id ORDER BY oi.quantity DESC) AS last_product_name
    FROM orders o
    JOIN customers c ON o.customer_id = c.customer_id
    JOIN order_items oi ON o.order_id = oi.order_id
    JOIN products p ON oi.product_id = p.product_id
    GROUP BY o.order_id, c.customer_name, o.order_date;

10.3 Database Schema Design Best Practices

• Use appropriate data types to conserve storage and improve performance
• Implement proper indexing for frequently queried columns
• Use constraints (e.g., NOT NULL, UNIQUE) to enforce data integrity
• Consider future scalability when designing tables and relationships
• Use consistent naming conventions for tables, columns, and other database objects

Example: Implementing Best Practices

CREATE TABLE products (
        product_id INT PRIMARY KEY,
        product_name VARCHAR(100) NOT NULL,
        product_code VARCHAR(20) UNIQUE,
        category_id INT,
        price DECIMAL(10, 2) CHECK (price >= 0),
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
        FOREIGN KEY (category_id) REFERENCES categories(category_id)
    );
    
    -- Create an index for frequently queried columns
    CREATE INDEX idx_product_name ON products(product_name);
    CREATE INDEX idx_product_category ON products(category_id);

10.4 Referential Integrity and Foreign Keys

Referential integrity ensures that relationships between tables remain consistent. Foreign keys are used to enforce these relationships.

Example: Implementing Referential Integrity

CREATE TABLE orders (
        order_id INT PRIMARY KEY,
        customer_id INT,
        order_date DATE,
        FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
            ON DELETE RESTRICT
            ON UPDATE CASCADE
    );
    
    CREATE TABLE order_items (
        order_id INT,
        product_id INT,
        quantity INT,
        PRIMARY KEY (order_id, product_id),
        FOREIGN KEY (order_id) REFERENCES orders(order_id)
            ON DELETE CASCADE,
        FOREIGN KEY (product_id) REFERENCES products(product_id)
            ON DELETE RESTRICT
    );

11.1 Partitioning Tables

Partitioning divides large tables into smaller, more manageable parts based on a partition key.

Example: Range Partitioning

CREATE TABLE sales (
        sale_id INT,
        sale_date DATE,
        amount DECIMAL(10, 2)
    )
    PARTITION BY RANGE (YEAR(sale_date)) (
        PARTITION p0 VALUES LESS THAN (2020),
        PARTITION p1 VALUES LESS THAN (2021),
        PARTITION p2 VALUES LESS THAN (2022),
        PARTITION p3 VALUES LESS THAN MAXVALUE
    );

11.2 Sharding Databases

Sharding distributes data across multiple database servers, improving performance and scalability.

Example: Sharding Strategy

-- Shard 1: Customers with IDs 1-1000000
    CREATE TABLE customers_shard1 (
        customer_id INT PRIMARY KEY,
        name VARCHAR(100),
        CHECK (customer_id BETWEEN 1 AND 1000000)
    );
    
    -- Shard 2: Customers with IDs 1000001-2000000
    CREATE TABLE customers_shard2 (
        customer_id INT PRIMARY KEY,
        name VARCHAR(100),
        CHECK (customer_id BETWEEN 1000001 AND 2000000)
    );

11.3 Data Warehousing Concepts

Data warehousing involves storing large amounts of historical data for analysis and reporting.

Example: Star Schema

CREATE TABLE fact_sales (
        sale_id INT PRIMARY KEY,
        date_key INT,
        product_key INT,
        customer_key INT,
        quantity INT,
        amount DECIMAL(10, 2),
        FOREIGN KEY (date_key) REFERENCES dim_date(date_key),
        FOREIGN KEY (product_key) REFERENCES dim_product(product_key),
        FOREIGN KEY (customer_key) REFERENCES dim_customer(customer_key)
    );

11.4 ETL Processes

ETL (Extract, Transform, Load) processes are used to move data from operational systems to data warehouses.

Example: Simple ETL Process

-- Extract
    CREATE TEMPORARY TABLE temp_sales AS
    SELECT * FROM source_system.sales
    WHERE sale_date >= CURRENT_DATE - INTERVAL 1 DAY;
    
    -- Transform
    UPDATE temp_sales
    SET amount = amount * 1.1
    WHERE product_category = 'Electronics';
    
    -- Load
    INSERT INTO data_warehouse.fact_sales
    SELECT * FROM temp_sales;

11.5 Working with Big Data in SQL

Big Data often requires specialized tools and techniques beyond traditional SQL databases.

Example: Using SQL with Hadoop

-- Using Hive SQL to query Hadoop data
    SELECT 
        year, 
        COUNT(*) AS total_sales,
        SUM(amount) AS total_revenue
    FROM sales_data
    WHERE year >= 2020
    GROUP BY year
    HAVING total_revenue > 1000000
    ORDER BY year;

12.1 Understanding NoSQL Databases

NoSQL databases provide flexible schemas and scalability for certain types of data and use cases.

Common NoSQL Database Types

12.2 SQL vs. NoSQL

Comparing SQL and NoSQL databases helps in choosing the right tool for specific use cases.

Comparison Table

12.3 Using SQL with NoSQL Databases

Some NoSQL databases offer SQL-like query languages or interfaces for easier integration.

Example: SQL-like Queries in MongoDB

// MongoDB query using SQL-like syntax
    db.runCommand({
      find: "customers",
      filter: { age: { $gt: 25 } },
      sort: { name: 1 },
      limit: 10
    })
    
    // Equivalent SQL query
    SELECT * 
    FROM customers
    WHERE age > 25
    ORDER BY name
    LIMIT 10;

12.4 Polyglot Persistence

Polyglot persistence involves using multiple database types to leverage their strengths for different data needs.

Example: Polyglot Persistence Architecture

// User profiles in SQL database
    CREATE TABLE users (
        user_id INT PRIMARY KEY,
        username VARCHAR(50),
        email VARCHAR(100)
    );
    
    // User sessions in Redis (NoSQL key-value store)
    SET session:1234 "{\"user_id\": 1, \"last_access\": \"2023-04-15T10:30:00Z\"}"
    
    // User activity in MongoDB (NoSQL document store)
    db.user_activity.insert({
        user_id: 1,
        activity_type: "login",
        timestamp: ISODate("2023-04-15T10:30:00Z")
    })
    
    // Social connections in Neo4j (NoSQL graph database)
    CREATE (u1:User {id: 1})-[:FOLLOWS]->(u2:User {id: 2})

13.1 Writing Readable and Maintainable SQL Code

SELECT
        u.user_id,
        u.username,
        COUNT(o.order_id) AS total_orders,
        SUM(o.total_amount) AS total_spent
    FROM
        users u
    LEFT JOIN
        orders o ON u.user_id = o.user_id
    WHERE
        u.status = 'active'
    GROUP BY
        u.user_id, u.username
    HAVING
        total_orders > 5
    ORDER BY
        total_spent DESC
    LIMIT 10;

13.2 Avoiding Common SQL Pitfalls

-- Avoid using SELECT *
    SELECT id, name, email FROM users;
    
    -- Use proper indexing
    CREATE INDEX idx_user_email ON users(email);
    
    -- Avoid using NOT IN with subqueries
    SELECT * FROM orders
    WHERE user_id NOT IN (SELECT user_id FROM banned_users);
    
    -- Use NOT EXISTS instead
    SELECT * FROM orders o
    WHERE NOT EXISTS (
        SELECT 1 FROM banned_users b
        WHERE b.user_id = o.user_id
    );

13.3 Performance Tuning Best Practices

-- Use EXPLAIN to analyze query performance
    EXPLAIN SELECT * FROM users WHERE email = 'user@example.com';
    
    -- Use appropriate data types
    CREATE TABLE products (
        id INT,
        name VARCHAR(100),
        price DECIMAL(10, 2)
    );
    
    -- Use JOINs instead of subqueries when possible
    SELECT u.username, o.order_date
    FROM users u
    JOIN orders o ON u.id = o.user_id
    WHERE o.total_amount > 100;

14.1 SQL Client Tools (e.g., MySQL Workbench, pgAdmin)

SQL client tools provide graphical interfaces for database management and querying.

14.2 SQL Extensions (e.g., PL/SQL, T-SQL)

-- PL/SQL example
    CREATE OR REPLACE PROCEDURE update_product_price(
        p_product_id IN NUMBER,
        p_new_price IN NUMBER
    )
    IS
    BEGIN
        UPDATE products
        SET price = p_new_price
        WHERE product_id = p_product_id;
        
        COMMIT;
    EXCEPTION
        WHEN OTHERS THEN
            ROLLBACK;
            RAISE;
    END;

14.3 Using ORM with SQL (e.g., Hibernate, SQLAlchemy)

// Java Hibernate example
    @Entity
    @Table(name = "users")
    public class User {
        @Id
        @GeneratedValue(strategy = GenerationType.IDENTITY)
        private Long id;
    
        @Column(name = "username")
        private String username;
    
        @Column(name = "email")
        private String email;
    
        // Getters and setters
    }

14.4 SQL in Data Science (e.g., Integration with R, Python)

# Python with SQLAlchemy example
    from sqlalchemy import create_engine
    import pandas as pd
    
    engine = create_engine('postgresql://user:password@localhost:5432/mydatabase')
    
    df = pd.read_sql_query('SELECT * FROM users WHERE age > 30', engine)
    print(df.head())

14.5 SQL and Cloud Databases (e.g., AWS RDS, Azure SQL Database)

-- AWS RDS example
    CREATE TABLE users (
        id INT AUTO_INCREMENT PRIMARY KEY,
        username VARCHAR(50) NOT NULL,
        email VARCHAR(100) UNIQUE NOT NULL,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
    );
    
    -- Azure SQL Database example
    CREATE TABLE products (
        id INT IDENTITY(1,1) PRIMARY KEY,
        name NVARCHAR(100) NOT NULL,
        price DECIMAL(10, 2) NOT NULL
    );

15.1 SQL Certification Paths

• Microsoft SQL Server Certifications
• Oracle MySQL Certifications
• PostgreSQL Certifications
• IBM Db2 Certifications

15.2 Preparing for SQL Certification Exams

• SQL Server Tutorial
• MySQL Tutorial
• PostgreSQL Tutorial

15.3 Career Opportunities in SQL and Database Management

• Database Administrator
• Data Analyst
• Business Intelligence Analyst
• Data Scientist
• Database Developer

16.1 SQL Cheat Sheet

Basic Queries

SELECT name, age FROM users WHERE age > 18;

INSERT INTO users (name, email) VALUES ('John', 'john@example.com');

UPDATE products SET price = 19.99 WHERE id = 1;

DELETE FROM orders WHERE status = 'cancelled';

Joins

SELECT * FROM orders o
        INNER JOIN customers c ON o.customer_id = c.id;

SELECT * FROM employees e
        LEFT JOIN departments d ON e.dept_id = d.id;

SELECT * FROM orders o
        RIGHT JOIN products p ON o.product_id = p.id;

SELECT * FROM students s
        FULL OUTER JOIN classes c ON s.class_id = c.id;

Aggregations and Grouping

SELECT country, COUNT(*) FROM customers GROUP BY country;

SELECT product_id, AVG(price)
        FROM sales
        GROUP BY product_id
        HAVING AVG(price) > 100;

SELECT 
            COUNT(*) as total_orders,
            SUM(amount) as total_sales,
            AVG(amount) as avg_order_value,
            MAX(amount) as largest_order,
            MIN(amount) as smallest_order
        FROM orders;

Subqueries

SELECT name FROM products
        WHERE category_id IN (
            SELECT id FROM categories WHERE name = 'Electronics'
        );

SELECT e.name
        FROM employees e
        WHERE salary > (
            SELECT AVG(salary) FROM employees
            WHERE department_id = e.department_id
        );

Set Operations

SELECT name FROM customers
        UNION
        SELECT name FROM employees;

SELECT product_id FROM order_items
        INTERSECT
        SELECT product_id FROM inventory;

SELECT customer_id FROM all_customers
        EXCEPT
        SELECT customer_id FROM active_customers;

Advanced Concepts

SELECT 
            name,
            department,
            salary,
            RANK() OVER (PARTITION BY department ORDER BY salary DESC) as rank
        FROM employees;

WITH high_value_orders AS (
            SELECT * FROM orders WHERE total_amount > 1000
        )
        SELECT customer_id, COUNT(*) as order_count
        FROM high_value_orders
        GROUP BY customer_id;

SELECT *
        FROM (
            SELECT category, sales_amount
            FROM sales
        ) AS SourceTable
        PIVOT (
            SUM(sales_amount)
            FOR category IN ([Electronics], [Clothing], [Books])
        ) AS PivotTable;

Data Definition Language (DDL)

CREATE TABLE users (
            id INT PRIMARY KEY,
            name VARCHAR(50),
            email VARCHAR(100) UNIQUE,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
        );

ALTER TABLE users
        ADD COLUMN age INT;

CREATE INDEX idx_lastname
        ON employees (last_name);

CREATE VIEW active_users AS
        SELECT * FROM users
        WHERE status = 'active';