Sql Examples

Uses GROUP BY ROLLUP and GROUPING to generate hierarchical summary rows with subtotals and grand totals for multi-dimensional analysis.

Uses CASE expressions in SELECT, ORDER BY, and aggregate contexts to implement conditional logic, categorization, and dynamic sorting in queries.

Uses COALESCE, NULLIF, NVL, and GREATEST/LEAST to handle NULL values gracefully in calculations, display formatting, and conditional logic.

Uses Common Table Expressions (CTEs) to break complex queries into named, readable steps for multi-level data transformation and aggregation.

Creates various index types including B-tree, partial, expression, and composite indexes to optimize different query patterns and access paths.

Creates simple and materialized views to abstract complex queries, pre-compute expensive aggregations, and provide simplified data access patterns.

Uses DELETE with subquery conditions to remove specific rows and RETURNING to capture deleted data for audit logging and referential cleanup.

Uses DISTINCT and DISTINCT ON to eliminate duplicate rows and select unique rows based on specific column ordering for data deduplication.

Demonstrates complex WHERE clause filtering with multiple operators including BETWEEN, IN, LIKE, IS NULL, and boolean logic for precise data selection.

Uses GROUP BY with multiple aggregate functions to summarize sales data by category, including count, sum, average, min, and max calculations.

Uses HAVING clause to filter grouped results based on aggregate conditions, combined with WHERE for pre-aggregation row filtering.

Performs INNER JOINs across three tables to combine related data from customers, orders, and products with proper table aliasing and filtering.

Demonstrates INSERT statements with multiple rows, column lists, and ON CONFLICT upsert behavior for inserting or updating data atomically.

Queries and manipulates JSON/JSONB data using operators for extraction, filtering, and modification of nested JSON structures in PostgreSQL.

Uses the MERGE statement to perform conditional INSERT, UPDATE, or DELETE operations based on source-target matching for data synchronization.

Demonstrates ORDER BY with multiple columns, mixed sort directions, NULLS handling, and expression-based sorting for precise result ordering.

Implements keyset pagination using LIMIT and OFFSET for efficient page-by-page result retrieval, with a fallback to cursor-based pagination for large datasets.

Uses a recursive Common Table Expression to traverse hierarchical data structures like org charts, category trees, or bill of materials.

Demonstrates a basic SELECT query with column aliases, literal expressions, and calculated columns for retrieving and shaping data from a table.

Creates a stored procedure with parameter validation, error handling, dynamic SQL, and cursor operations for encapsulating complex business logic.

Demonstrates correlated and non-correlated subqueries in WHERE clauses and SELECT columns to filter and enrich data based on related query results.

Defines a table with comprehensive constraints including primary key, foreign keys, check constraints, unique constraints, and default values.

Demonstrates explicit transaction control with SET TRANSACTION isolation levels, savepoints, and conditional rollback for safe multi-step operations.

Combines result sets using UNION ALL, UNION, INTERSECT, and EXCEPT operators to merge, find common, or find differing rows between queries.

Performs UPDATE with a FROM clause to join additional tables and uses RETURNING to retrieve modified rows for verification and auditing.

Uses window functions like ROW_NUMBER, RANK, LAG, LEAD, and running totals to perform advanced analytics without collapsing result rows.