SQL expressions are fundamental building blocks for data manipulation in SQL queries. They allow you to transform, calculate, and format data directly in your database queries.

Key SQL Expression Types:

• Arithmetic Expressions: Calculate numeric values (+, -, *, /)
• String Expressions: Manipulate text data (CONCAT, SUBSTRING)
• Date/Time Expressions: Handle temporal data (DATEADD, DATEDIFF)
• Logical Expressions: Make decisions (CASE, IF, COALESCE)
• Type Conversion: Convert between data types (CAST, CONVERT)

Common Use Cases:

• Financial calculations (discounts, taxes, totals)
• Data formatting (combining fields, proper case)
• Date manipulation (age calculation, time differences)
• Conditional logic (status flags, categories)
• Data validation (null checks, range tests)

Best Practices:

• Use clear aliases for computed columns
• Break complex expressions into parts
• Consider NULL handling
• Document calculations inline
• Test with edge cases

Example Syntax:

SELECT 
    first_name,
    last_name,
    ROUND(salary * 1.1, 2) AS increased_salary,
    CASE 
        WHEN department = 'Sales' THEN 'Revenue'
        ELSE 'Support'
    END AS division
FROM employees;

Master SQL arithmetic expressions to perform calculations on your data. This is essential for financial analysis, data processing, and reporting.

Business Scenario: You need to calculate discounted prices and profit margins for a product catalog.

Write a query that:

• Retrieves the product name and base price
• Calculates a 10% discount amount
• Shows the final discounted price
• Computes the profit margin (30% of base price)
• Rounds all monetary values to 2 decimals

Requirements:

• Include all products
• Sort by highest profit margin
• Show prices in consistent format
• Handle NULL prices appropriately

Tips:

• Use ROUND() for currency values
• Calculate discount with multiplication
• Consider adding currency symbols
• Document each calculation step

Learn essential SQL string functions for text manipulation and formatting. These functions are crucial for data cleaning, reporting, and standardization.

Business Scenario: Create a customer directory with formatted names and contact information.

Write a query that:

• Extracts the first name (text before space)
• Formats email in uppercase for visibility
• Extracts email domain for categorization
• Counts email length for validation
• Combines name and email for display

Key String Functions:

• SUBSTR: Extract part of text
• UPPER/LOWER: Change text case
• INSTR: Find position of character
• LENGTH: Get text length
• TRIM: Remove extra spaces
• REPLACE: Substitute text

Tips:

• Handle missing spaces in names
• Validate email format
• Consider international characters
• Use clear column aliases

Master SQL date and time functions to analyze temporal data. These functions are essential for reporting, tracking, and time-based analytics.

Business Scenario: Create an order analysis report with various date calculations.

Write a query that shows:

• Order ID and creation timestamp
• Day of week (0-6) for scheduling
• Month name for reporting
• Days elapsed since order
• Recent order flag (last 30 days)
• Processing time in hours

Important Date Functions:

• strftime(): Format dates
• julianday(): Calculate day differences
• date(): Convert to dates
• datetime(): Handle timestamps
• time(): Extract time parts

Format Codes:

• %Y: Year (YYYY)
• %m: Month (01-12)
• %d: Day (01-31)
• %H: Hour (00-23)
• %M: Minute (00-59)
• %w: Day of week (0-6)

Tips:

• Use consistent date formats
• Handle timezone differences
• Consider date range edges
• Validate date calculations

Master SQL conditional logic using CASE expressions and boolean operations. These are crucial for data categorization, business rules, and dynamic reporting.

Business Scenario: Create a product analysis report with dynamic categorization.

Write a query that:

• Analyzes product metrics
• Assigns price categories
• Evaluates stock levels
• Determines reorder priority
• Calculates inventory value

Price Categories:

• Premium: > $100
• Standard: $20-$100
• Budget: < $20

Stock Status Rules:

• Critical: 0-5 items
• Low: 6-15 items
• Optimal: 16-50 items
• Excess: > 50 items

Priority Logic:

• High: Premium + Critical/Low stock
• Medium: Standard + Critical stock
• Low: All others

Tips:

• Use nested CASE expressions
• Consider edge cases
• Add meaningful labels
• Order results logically

Learn to combine multiple SQL functions for complex data transformation. This advanced technique is essential for sophisticated data analysis and reporting.

Business Scenario: Create a comprehensive customer analytics dashboard.

Requirements:

• Format customer names properly
• Mask sensitive email data
• Calculate customer lifetime value
• Analyze order patterns
• Determine customer segments

Write a query showing:

• Customer full name (properly cased)
• Masked email (domain only)
• Days since last order
• Total orders placed
• Average order value
• Customer segment

Advanced Techniques:

• Multiple function nesting
• Complex calculations
• Data type handling
• NULL value management
• Result formatting

Tips:

• Break down complex logic
• Test each component
• Consider performance
• Document assumptions

Master advanced SQL expressions for complex business analytics. This combines multiple SQL concepts for sophisticated data analysis.

Business Scenario: Create a detailed product performance dashboard.

Analytics Requirements:

• Product performance metrics
• Pricing analysis
• Inventory management
• Risk assessment
• Profitability analysis

Write a query showing:

• Basic product details
• Price with tax (15%)
• Profit margin (30%)
• Current stock value
• Stock risk level
• Performance rating

Advanced Concepts:

• Multi-level calculations
• Complex categorization
• Risk assessment
• Performance metrics
• Trend indicators

Best Practices:

• Document complex logic
• Use clear naming
• Consider scalability
• Handle edge cases
• Format output clearly