Cyber Security Specialist | Quantum Cryptography Researcher

SQL Data Languages

Abdul Wahab Junaid
SQL Data Languages

SQL is organized into four distinct sublanguages, each serving a critical role in database management. Understanding these components is essential for any database professional.

1. Data Definition Language (DDL) – The Database Architect

DDL forms the structural foundation of your database. These commands define, modify, and remove database objects while maintaining referential integrity.

Core DDL Commands with Professional Examples

CREATE: Building Database Structures

SQL
-- Creating a normalized customer database schema
CREATE SCHEMA ecommerce;

CREATE TABLE ecommerce.customers (
    customer_id INT IDENTITY(1,1) PRIMARY KEY,
    first_name NVARCHAR(50) NOT NULL,
    last_name NVARCHAR(50) NOT NULL,
    email NVARCHAR(100) UNIQUE CHECK (email LIKE '%@%.%'),
    date_registered DATETIME DEFAULT GETDATE(),
    credit_limit DECIMAL(10,2) CONSTRAINT chk_credit CHECK (credit_limit >= 0)
);

CREATE TABLE ecommerce.orders (
    order_id INT IDENTITY(1000,1) PRIMARY KEY,
    customer_id INT NOT NULL REFERENCES ecommerce.customers(customer_id),
    order_date DATETIME DEFAULT GETDATE(),
    status VARCHAR(20) DEFAULT 'Pending' 
        CHECK (status IN ('Pending', 'Shipped', 'Delivered', 'Cancelled')),
    total_amount DECIMAL(12,2) NOT NULL,
    INDEX idx_order_date (order_date)
);

ALTER: Evolving Database Structures

SQL
-- Adding a loyalty program to existing customers
ALTER TABLE ecommerce.customers
ADD loyalty_points INT DEFAULT 0,
    loyalty_tier VARCHAR(15) DEFAULT 'Standard'
        CHECK (loyalty_tier IN ('Standard', 'Silver', 'Gold', 'Platinum'));

-- Modifying column to support international phone numbers
ALTER TABLE ecommerce.customers
ALTER COLUMN phone VARCHAR(20);

-- Adding a foreign key after table creation
ALTER TABLE ecommerce.orders
ADD CONSTRAINT fk_customer
FOREIGN KEY (customer_id) REFERENCES ecommerce.customers(customer_id)
ON DELETE CASCADE;

DROP vs. TRUNCATE: Data Cleanup

SQL
-- Remove test data while keeping structure
TRUNCATE TABLE staging.import_data;

-- Completely remove obsolete table
DROP TABLE legacy.product_catalog;

Professional Considerations:

2. Data Manipulation Language (DML) – The Data Workhorse

DML handles the day-to-day data operations that power applications and reports.

Advanced DML Operations

Complex INSERT Operations

SQL
-- Inserting from a SELECT (data migration)
INSERT INTO production.products (product_id, name, price)
SELECT item_id, item_name, base_price * 1.1 -- Apply 10% price increase
FROM legacy.inventory
WHERE discontinued_flag = 0;

-- Bulk insert from CSV
BULK INSERT sales.transactions
FROM '/data/import/2023_q4_transactions.csv'
WITH (
    FIELDTERMINATOR = ',',
    ROWTERMINATOR = '\n',
    FIRSTROW = 2,
    TABLOCK
);

Sophisticated UPDATE Patterns

SQL
-- Conditional update with JOIN
UPDATE p
SET p.price = p.price * 0.9, -- 10% discount
    p.last_updated = GETDATE()
FROM production.products p
JOIN sales.inventory i ON p.product_id = i.product_id
WHERE i.quantity > 100 
AND i.warehouse_id = 5;

-- Update using window functions
UPDATE emp
SET department_rank = r.rank
FROM (
    SELECT employee_id, 
           RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) as rank
    FROM hr.employees
) r
WHERE emp.employee_id = r.employee_id;

DELETE with Precision

SQL
-- Archiving before deletion
INSERT INTO audit.deleted_orders
SELECT *, GETDATE(), SYSTEM_USER
FROM sales.orders
WHERE order_date  DATEADD(year, -7, GETDATE());

-- Deleting with referential integrity
DELETE FROM marketing.newsletter_subscribers
WHERE last_engagement_date  DATEADD(year, -2, GETDATE())
AND subscriber_id NOT IN (
    SELECT DISTINCT customer_id 
    FROM sales.orders
);

Analytical SELECT Queries

SQL
-- Common Table Expression (CTE) for hierarchical data
WITH EmployeeHierarchy AS (
    -- Anchor member
    SELECT employee_id, name, manager_id, 1 AS level
    FROM hr.employees
    WHERE manager_id IS NULL

    UNION ALL

    -- Recursive member
    SELECT e.employee_id, e.name, e.manager_id, eh.level + 1
    FROM hr.employees e
    JOIN EmployeeHierarchy eh ON e.manager_id = eh.employee_id
)
SELECT * FROM EmployeeHierarchy
ORDER BY level, name;

-- PIVOT for cross-tab reports
SELECT *
FROM (
    SELECT DATEPART(YEAR, order_date) AS year,
           DATEPART(QUARTER, order_date) AS quarter,
           total_amount
    FROM sales.orders
) AS SourceTable
PIVOT (
    SUM(total_amount)
    FOR quarter IN ([1], [2], [3], [4])
) AS PivotTable;

3. Data Control Language (DCL) – The Security Gatekeeper

DCL manages who can access what within your database environment.

Professional Security Implementation

Role-Based Access Control

SQL
-- Create application roles
CREATE ROLE reporting_read_only;
CREATE ROLE data_entry_operator;
CREATE ROLE db_administrator;

-- Grant schema-level permissions
GRANT SELECT ON SCHEMA::sales TO reporting_read_only;
GRANT SELECT, INSERT, UPDATE ON SCHEMA::inventory TO data_entry_operator;
GRANT CONTROL ON SCHEMA::production TO db_administrator;

-- Create a user with limited access
CREATE USER etl_user WITH PASSWORD = 'S3cure#Passw0rd';
ALTER ROLE data_entry_operator ADD MEMBER etl_user;

-- Column-level security
GRANT SELECT ON hr.employees(employee_id, first_name, last_name, department) TO reporting_read_only;
DENY SELECT ON hr.employees(salary, ssn, performance_rating) TO reporting_read_only;

Dynamic Data Masking (Advanced Security)

SQL
-- Add masking to sensitive columns
ALTER TABLE customers
ALTER COLUMN credit_card_number ADD MASKED WITH (FUNCTION = 'partial(0,"XXXX-XXXX-XXXX-",4)');

ALTER TABLE employees
ALTER COLUMN salary ADD MASKED WITH (FUNCTION = 'random(1000, 5000)');

-- Grant unmasked access to specific roles
GRANT UNMASK TO payroll_team;

4. Transaction Control Language (TCL) – The Consistency Guardian

TCL ensures data integrity through proper transaction management.

Professional Transaction Patterns

Nested Transactions with Error Handling

SQL
BEGIN TRY
    BEGIN TRANSACTION;

    -- Update inventory
    UPDATE production.inventory
    SET quantity = quantity - @order_quantity
    WHERE product_id = @product_id;

    -- Record transaction
    INSERT INTO sales.transactions (
        product_id, quantity, unit_price, customer_id
    ) VALUES (
        @product_id, @order_quantity, @current_price, @customer_id
    );

    -- Update customer loyalty points
    UPDATE customers
    SET loyalty_points = loyalty_points + @points_earned
    WHERE customer_id = @customer_id;

    -- Log the activity
    INSERT INTO audit.order_activity (
        user_id, action, timestamp
    ) VALUES (
        SYSTEM_USER, 'New order processed', GETDATE()
    );

    COMMIT TRANSACTION;
END TRY
BEGIN CATCH
    IF @@TRANCOUNT > 0
        ROLLBACK TRANSACTION;

    -- Log error details
    INSERT INTO error_log (error_time, error_number, error_message)
    VALUES (GETDATE(), ERROR_NUMBER(), ERROR_MESSAGE());

    -- Re-throw the error for application handling
    THROW;
END CATCH

Savepoints for Complex Operations

SQL
BEGIN TRANSACTION;

-- Phase 1: Data preparation
INSERT INTO staging.import_batch (batch_date) VALUES (GETDATE());
SAVEPOINT after_batch_creation;

-- Phase 2: Data transformation
EXEC transform.cleanse_customer_data;
SAVEPOINT after_data_cleanse;

-- Phase 3: Data validation
IF EXISTS (SELECT 1 FROM staging.invalid_records)
BEGIN
    ROLLBACK TO SAVEPOINT after_batch_creation;
    RAISERROR('Data validation failed', 16, 1);
END

-- Final commit if all succeeds
COMMIT TRANSACTION;

Isolation Level Control

SQL
-- Critical financial report needing repeatable reads
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
BEGIN TRANSACTION;
    -- Get account balance
    SELECT balance FROM accounts WHERE account_id = 1001;

    -- Get recent transactions
    SELECT * FROM transactions 
    WHERE account_id = 1001
    AND transaction_date > DATEADD(day, -7, GETDATE());

    -- This will see a consistent snapshot despite concurrent updates
COMMIT TRANSACTION;

-- Reset to default
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

Professional SQL Development Practices

  1. Script Templates: Always include error handling and transaction control
  2. Batch Operations: Process data in batches to avoid lock contention
  3. Performance Considerations:
  1. Metadata Management:
SQL
-- Document your database objects
EXEC sp_addextendedproperty 
    @name = N'MS_Description', 
    @value = 'Stores customer loyalty program information',
    @level0type = N'SCHEMA', @level0name = 'ecommerce',
    @level1type = N'TABLE', @level1name = 'customers';

By mastering these four SQL sublanguages and applying professional development practices, you’ll be able to design robust, secure, and high-performance database solutions that meet enterprise requirements.

Exit mobile version