SQL Syntax and Conventions

Duration: 60 minutes

Module 3: Session 3

Learning Objectives

Understand SQL statement structure and syntax rules
Master SQL naming conventions for databases, tables, and columns
Learn SQL keywords and reserved words
Apply formatting and indentation best practices
Write readable and maintainable SQL code
Understand case sensitivity in MySQL

SQL Statement Structure

SQL (Structured Query Language) follows specific syntax rules. Understanding these rules is essential for writing correct and efficient database queries.

💡

Key Syntax Rules

SQL statements can span multiple lines
Statements end with a semicolon (;)
Keywords are NOT case-sensitive (but use UPPERCASE by convention)
Identifiers (table/column names) follow database collation rules
String literals use single quotes ('text')
Comments start with -- or use /* */ for multi-line

SQL Keywords and Reserved Words

Common SQL Keywords

-- Keywords should be UPPERCASE (convention)
SELECT name, email 
FROM users 
WHERE age >= 18 
ORDER BY name ASC;

-- NOT like this (works but poor style):
select name, email from users where age >= 18 order by name asc;

-- Reserved words as identifiers need backticks
CREATE TABLE `order` (  -- 'order' is a reserved word
    `select` INT,       -- 'select' is a reserved word
    product_name VARCHAR(100)
);

Naming Conventions

graph TB subgraph "Good Naming ✅" G1[user_accounts] G2[product_id] G3[created_at] G4[is_active] G5[order_items] end subgraph "Bad Naming ❌" B1[tbl_Users] B2[ProductID] B3[created-date] B4[active?] B5[order items] end style G1 fill:#dcfce7 style G2 fill:#dcfce7 style G3 fill:#dcfce7 style G4 fill:#dcfce7 style G5 fill:#dcfce7 style B1 fill:#fee2e2 style B2 fill:#fee2e2 style B3 fill:#fee2e2 style B4 fill:#fee2e2 style B5 fill:#fee2e2

Naming Convention Best Practices

Element	Convention	Good Examples	Bad Examples
Database	lowercase, underscore	ecommerce_db, wordpress_site	E-Commerce, WordPressSite
Tables	lowercase, underscore, plural	users, order_items, products	User, OrderItems, tbl_products
Columns	lowercase, underscore	first_name, created_at, is_active	FirstName, created-at, isActive
Primary Keys	id or tablename_id	id, user_id, product_id	UserID, pk_user, userid
Foreign Keys	referenced_table_id	user_id, category_id	fk_user, UserRef
Indexes	idx_tablename_columns	idx_users_email, idx_products_sku	email_index, Index1
Constraints	type_description	chk_age_minimum, unique_email	Constraint1, ck1

-- Good naming example
CREATE TABLE order_items (
    order_item_id INT AUTO_INCREMENT PRIMARY KEY,
    order_id INT NOT NULL,
    product_id INT NOT NULL,
    quantity INT NOT NULL DEFAULT 1,
    unit_price DECIMAL(10,2) NOT NULL,
    discount_percent DECIMAL(5,2) DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    FOREIGN KEY fk_order (order_id) REFERENCES orders(order_id),
    FOREIGN KEY fk_product (product_id) REFERENCES products(product_id),
    
    INDEX idx_order_items_order (order_id),
    INDEX idx_order_items_product (product_id),
    
    CONSTRAINT chk_quantity_positive CHECK (quantity > 0),
    CONSTRAINT chk_discount_range CHECK (discount_percent BETWEEN 0 AND 100)
);

SQL Formatting and Indentation

SQL Formatting Rules

-- 1. Keywords on new lines for complex queries
SELECT 
    column1,
    column2
FROM 
    table_name
WHERE 
    condition;

-- 2. Indent JOIN clauses
SELECT 
    o.order_id,
    c.customer_name,
    p.product_name
FROM 
    orders o
    INNER JOIN customers c 
        ON o.customer_id = c.customer_id
    INNER JOIN order_items oi 
        ON o.order_id = oi.order_id
    INNER JOIN products p 
        ON oi.product_id = p.product_id;

-- 3. Align column definitions in CREATE TABLE
CREATE TABLE employees (
    employee_id    INT           AUTO_INCREMENT PRIMARY KEY,
    first_name     VARCHAR(50)   NOT NULL,
    last_name      VARCHAR(50)   NOT NULL,
    email          VARCHAR(100)  UNIQUE NOT NULL,
    hire_date      DATE          NOT NULL,
    salary         DECIMAL(10,2) CHECK (salary > 0)
);

-- 4. Complex WHERE conditions
SELECT *
FROM orders
WHERE 
    status = 'pending'
    AND (
        total > 1000 
        OR customer_type = 'premium'
    )
    AND order_date >= DATE_SUB(CURDATE(), INTERVAL 30 DAY);

-- 5. Subqueries indentation
SELECT 
    customer_id,
    customer_name,
    (
        SELECT COUNT(*) 
        FROM orders 
        WHERE orders.customer_id = customers.customer_id
    ) AS order_count
FROM customers
WHERE customer_id IN (
    SELECT DISTINCT customer_id
    FROM orders
    WHERE order_date >= '2025-01-01'
);

Comments and Documentation

SQL Comment Styles

-- Single line comment (most common)
SELECT * FROM users; -- Can also go at end of line

/* 
   Multi-line comment
   Useful for longer explanations
   or temporarily disabling code blocks
*/

/*
 * Header comment style
 * Author: John Doe
 * Date: 2025-01-15
 * Purpose: Customer order analysis query
 */

-- Table documentation
CREATE TABLE products (
    product_id INT AUTO_INCREMENT PRIMARY KEY, -- Unique identifier
    sku VARCHAR(50) UNIQUE NOT NULL,          -- Stock Keeping Unit
    name VARCHAR(200) NOT NULL,               -- Display name
    description TEXT,                          -- Marketing description
    cost DECIMAL(10,2),                       -- Our cost (hidden from customers)
    price DECIMAL(10,2) NOT NULL,             -- Selling price
    
    -- Inventory tracking
    stock_quantity INT DEFAULT 0,             -- Current stock level
    reorder_level INT DEFAULT 10,             -- When to reorder
    
    -- Timestamps
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    -- Soft delete functionality
    deleted_at TIMESTAMP NULL,                -- NULL = active, timestamp = deleted
    
    INDEX idx_sku (sku),
    INDEX idx_active (deleted_at)             -- For filtering active products
);

-- Complex query documentation
/* 
 * Query: Monthly Sales Report
 * Description: Calculates total sales, order count, and average order value
 *              grouped by month for the past 12 months
 * Used by: Finance dashboard, monthly reports
 * Performance: ~200ms on production data (as of 2025-01)
 */
SELECT 
    DATE_FORMAT(order_date, '%Y-%m') AS month,
    COUNT(DISTINCT order_id) AS total_orders,
    COUNT(DISTINCT customer_id) AS unique_customers,
    SUM(total_amount) AS total_revenue,
    AVG(total_amount) AS avg_order_value,
    MAX(total_amount) AS largest_order
FROM 
    orders
WHERE 
    order_date >= DATE_SUB(CURDATE(), INTERVAL 12 MONTH)
    AND status = 'completed'
GROUP BY 
    DATE_FORMAT(order_date, '%Y-%m')
ORDER BY 
    month DESC;

Case Sensitivity in MySQL

⚠️

Platform Differences

Case sensitivity in MySQL depends on your operating system:

Windows:Database and table names are NOT case-sensitive
Linux/Mac:Database and table names ARE case-sensitive
Column names:Never case-sensitive
String comparisons:Depend on collation

-- On Windows: These refer to the same table
SELECT * FROM Users;
SELECT * FROM users;
SELECT * FROM USERS;

-- On Linux/Mac: These are DIFFERENT tables
SELECT * FROM Users;  -- Different
SELECT * FROM users;  -- Different
SELECT * FROM USERS;  -- Different

-- Column names are never case-sensitive
SELECT UserName FROM users;  -- Works
SELECT username FROM users;  -- Same column
SELECT USERNAME FROM users;  -- Same column

-- String comparisons (default collation is case-insensitive)
SELECT * FROM users WHERE name = 'john';  -- Matches 'John', 'JOHN', 'john'
SELECT * FROM users WHERE name LIKE 'j%'; -- Matches 'John', 'jane', 'JAMES'

-- Force case-sensitive comparison
SELECT * FROM users WHERE BINARY name = 'John';  -- Only matches 'John' exactly

-- Using case-sensitive collation
CREATE TABLE users_cs (
    username VARCHAR(50) COLLATE utf8mb4_bin,  -- Case-sensitive
    email VARCHAR(100) COLLATE utf8mb4_unicode_ci  -- Case-insensitive
);

Best Practice: Be Consistent

To avoid issues across different platforms:

Always use lowercase for database and table names
Use underscores instead of camelCase
Be consistent with your naming convention
Setlower_case_table_names=1in MySQL config for consistency

Data Types and Literals

SQL Literal Syntax

-- String literals (use single quotes)
SELECT * FROM users WHERE name = 'John Doe';
SELECT * FROM products WHERE description = 'It''s great!';  -- Escape single quote

-- Numeric literals (no quotes)
SELECT * FROM products WHERE price = 29.99;
SELECT * FROM orders WHERE quantity = 5;

-- Date and time literals
SELECT * FROM orders WHERE order_date = '2025-01-15';  -- Date
SELECT * FROM logs WHERE created_at = '2025-01-15 14:30:00';  -- Datetime
SELECT * FROM events WHERE event_time = '14:30:00';  -- Time

-- Boolean literals
SELECT * FROM users WHERE is_active = TRUE;   -- or 1
SELECT * FROM products WHERE discontinued = FALSE;  -- or 0

-- NULL values
SELECT * FROM customers WHERE phone IS NULL;  -- Correct
SELECT * FROM customers WHERE phone = NULL;   -- Wrong! Always returns false

-- Binary literals
SELECT * FROM files WHERE file_hash = 0x48656C6C6F;  -- Hexadecimal
SELECT * FROM settings WHERE flags = b'10101010';    -- Binary

-- Using functions for current values
INSERT INTO logs (message, created_at) 
VALUES ('User login', NOW());  -- Current timestamp

INSERT INTO tasks (title, due_date) 
VALUES ('Complete project', DATE_ADD(CURDATE(), INTERVAL 7 DAY));

SQL Style Guide Summary

Complete SQL Style Guide

Rule	Do ✅	Don't ❌
Keywords	`SELECT, FROM, WHERE`	`select, From, where`
Table Names	`user_accounts, order_items`	`UserAccounts, tbl_Orders`
Column Names	`first_name, created_at`	`FirstName, created-at`
Indentation	4 spaces or 1 tab consistently	Mixed spaces and tabs
Line Length	Max 80-120 characters	Very long single lines
Strings	`'single quotes'`	`"double quotes"`
Aliases	`u.name, o.total`	`users.name, t1.total`
Comments	Explain why, not what	Redundant comments

Practice Exercise

💻

Format This Query

Reformat this poorly written query following all conventions:

select c.Name,c.Email,count(o.OrderID) as TotalOrders,sum(o.Amount) 
from Customers c left join Orders o on c.CustomerID=o.CustomerID 
where c.Status='active' and o.OrderDate>='2025-01-01' group by 
c.CustomerID having count(o.OrderID)>5 order by TotalOrders desc;

💡 Click for Solution

SELECT 
    c.name,
    c.email,
    COUNT(o.order_id) AS total_orders,
    SUM(o.amount) AS total_amount
FROM 
    customers c
    LEFT JOIN orders o 
        ON c.customer_id = o.customer_id
WHERE 
    c.status = 'active' 
    AND o.order_date >= '2025-01-01'
GROUP BY 
    c.customer_id,
    c.name,
    c.email
HAVING 
    COUNT(o.order_id) > 5
ORDER BY 
    total_orders DESC;

PHP WordPress Development

Course Progress