Relational Databases Explained: Structure, Queries, and Benefits (2024 Guide) -

Advanced SQL techniques enable sophisticated data analysis. Subqueries nest queries within others, executed first with results used by outer queries (e.g., finding payments below average). Derived tables create temporary tables within queries using subqueries in FROM clause, enabling complex calculations to be reused. The WITH clause (CTE) improves readability by defining temporary tables separately before main queries. Window functions perform calculations across related rows without collapsing them. RANK() assigns ranks within groups defined by PARTITION BY, with ORDER BY determining ranking order. Window functions can be filtered using WHERE clauses on calculated values. These techniques enable complex analysis like ranking actors alphabetically within each last name group, extracting top performers per category, and performing comparative analysis across datasets.

This section covers three advanced SQL techniques demonstrated through practical examples: (1) Using EXISTS with subqueries to find records present in multiple subsets (e.g., museums open on both Sunday and Monday); (2) Transforming text-based time values to timestamp format using TO_TIMESTAMP with format strings, then calculating durations by subtraction; (3) Ranking results using RANK() window functions with ORDER BY to identify maximum values, then filtering with WHERE rank = 1. These techniques enable complex analytical queries that go beyond basic data retrieval.

This comprehensive section covers advanced SQL query techniques essential for data analysis. Subqueries are nested queries with two types: correlated (references outer query columns, executes per outer row) and non-correlated (executes independently). Subqueries can use aggregate functions like AVG() for comparisons and the IN operator for filtering against multiple values. JOINs combine tables using conditions like: SELECT * FROM table1 JOIN table2 ON table1.id = table2.id. INNER JOIN returns matching records, while other JOIN types return different combinations. Window functions perform calculations across related rows without self-joins: RANK() assigns ranks with gaps for ties, DENSE_RANK() without gaps, ROW_NUMBER() assigns unique sequential integers, PARTITION BY divides data into groups, LAG() accesses previous row data, LEAD() accesses next row data, and NTILE(n) divides rows into n equal buckets. These techniques enable complex data analysis, ranking, pagination, and trend analysis.

Advanced SQL concepts include subqueries (single row, multiple row, correlated) for complex data retrieval. Window functions (RANK, DENSE_RANK, LEAD, LAG) perform calculations across related rows without collapsing results. DELETE is DML (rollable), while TRUNCATE is DDL (not rollable). ACID properties ensure reliable transactions: Atomicity (all-or-nothing), Consistency (integrity rules), Isolation (concurrent transaction handling), Durability (committed changes persist). Database normalization reduces redundancy through normal forms. SQL handles NULL values using IS NULL/IS NOT NULL and NULLIF functions. Self-join joins a table with itself for hierarchical relationships.

Advanced SQL techniques enable complex data analysis across multiple tables. JOINs combine tables: INNER JOIN returns matching rows only, LEFT JOIN returns all left rows with matching right data (NULL for non-matches), RIGHT JOIN reverses LEFT JOIN, FULL JOIN returns all rows regardless of matches. LEFT JOIN with WHERE IS NULL (LEFT ANTI JOIN) finds rows in left table with no right table matches. Subqueries execute first, returning values for outer queries, but cannot use aggregate functions directly. CTEs (Common Table Expressions) with WITH clause improve readability by breaking complex queries into named steps, enabling multiple references and recursive logic. Window functions perform calculations across related rows without collapsing them: SUM(column) OVER (PARTITION BY column ORDER BY column) calculates running totals. ROW_NUMBER() OVER (PARTITION BY column ORDER BY column DESC) assigns unique sequential numbers within partitions for top-N analysis. LAG(column, 1) retrieves previous row values for period-over-period comparisons. Window functions enable running totals, moving averages, and row-based comparisons essential for time-series analysis.

Indexes enable fast data retrieval without scanning entire tables, organized as B-trees dividing data into ranges. However, indexes slow down writes because the database must maintain them during every insert, update, or delete. Query optimizers analyze queries and choose between table scans and index usage based on table size, index availability, and query selectivity. Partitioning divides large tables into smaller pieces for parallel processing. Effective performance tuning requires understanding how data is stored, how queries are executed, and balancing read and write performance.

Performance Tuning optimizes system resource utilization through CPU, Memory, I/O, and Application tuning. Database tuning focuses on Hit Ratios and Wait Events. The Optimizer generates SQL execution plans: RBO uses rules, while CBO uses statistics for better decisions. Key prerequisites include setting optimizer_mode and collecting statistics via ANALYZE TABLE. Statistics management commands include DELETE STATISTICS and DBMS_STATS.GATHER_SCHEMA_STATS. Execution plans are roadmaps for SQL execution, with indexes defined in DBA_INDEXES and DBA_IND_COLUMNS. Cursor sharing is enabled via CURSOR_SHARING = FORCE, and long-running sessions are monitored through V$SESSION_LONGOPS.

Performance tuning focuses on aligning query execution time with business tolerance thresholds rather than absolute speed metrics. There is no universal rule for performance tuning—interviewers assess problem-solving approaches rather than memorized solutions. The first diagnostic step is always executing an explain plan to understand execution paths including full table scans, index scans, and range scans. Regularly checking last analyze dates ensures statistics remain current, with recomputation needed when dates exceed two weeks. Critical to performance stability is creating indexes on all foreign keys, as databases automatically index primary keys but not foreign keys. Omitting foreign key indexes introduces deadlock risks. Query execution follows a three-stage process: parsing, optimization, and execution. For five-table joins, 120 join orders exist with multiple access methods, creating approximately 3 million possible execution paths. The database pragmatically evaluates only the top 2,000 best guesses rather than exhaustively analyzing all options, which can result in suboptimal plan selection.

Performance engineers identify bottlenecks through AWR reports for Oracle, Performance Schema for MySQL, PG_stat_statements for PostgreSQL, and SQL Server Profiler. Query simplification involves breaking complex queries into smaller ones using subqueries or CTEs. Indexing strategy development involves monitoring query performance, running EXPLAIN commands, and identifying columns frequently used in WHERE, JOIN, ORDER BY, and GROUP BY clauses. Index creation syntax is CREATE INDEX index_name ON table_name (column_name). Indexes should be created on frequently queried columns, JOIN condition columns, and ORDER BY/GROUP BY columns. Indexes require maintenance every 2-3 months as usage patterns change. Over-indexing slows write operations and increases storage.

The query optimizer relies on statistics (gathered by ANALYZE) to make decisions. Without statistics, it may choose suboptimal plans like bitmap heap scans when sequential scans would be better. Parameters like maintenance_work_mem affect index creation speed. effective_cache_size should be set to 25-50% of actual memory. work_mem affects bitmap index check costs. Table clustering reorganizes data to match index order, reducing random I/O for large tables.

SQL databases provide strong consistency, structured schemas, and powerful query capabilities, ensuring data integrity but creating challenges for horizontal scaling and schema modifications. NoSQL databases sacrifice some consistency and query capabilities to gain horizontal scalability and schema flexibility. The choice between them depends on whether the application needs rock-solid transactions and complex queries (SQL) or requires massive scaling with evolving data models (NoSQL).

SQL databases scale vertically by adding CPU, RAM, and storage to a single server up to hardware limits. NoSQL databases scale horizontally by adding more nodes to a cluster, distributing data across multiple machines. SQL databases require predefined schemas with fixed columns and data types, making schema changes difficult. NoSQL databases have dynamic schemas allowing documents to have different fields and data types within the same collection, providing flexibility for rapidly changing requirements.

SQL databases excel at handling complex relationships, schema changes, and data integrity through normalization and foreign keys, while NoSQL databases offer better schema flexibility, locality, and horizontal scalability for self-containing data; the optimal choice depends on specific application requirements, data relationships, and scalability needs rather than a universal superiority of one model over the other.

SQL databases are relational, table-based systems that enforce strict schema requirements, ensuring data consistency through ACID compliance (Atomicity, Consistency, Isolation, Durability), making them ideal for structured data and complex queries but challenging to scale horizontally. NoSQL databases, including document, key-value, graph, and wide-column stores, offer greater flexibility for unstructured or semi-structured data, enabling easier horizontal scaling through sharding across distributed clusters, though they typically sacrifice strong consistency for availability and partition tolerance, resulting in eventual consistency where updates may take time to propagate across all replicas.

With relational databases, the schema is fixed with rigid data types - if you try to put a Boolean into a column defined to only take integers, the attempt gets rejected. This strictness can be good but comes with downfalls and can be very time-consuming when implementing changes. With NoSQL databases, the schema is flexible - there doesn't have to be any real fixed structure to the data. It can be stored in a flexible schema where this approach can make development easier due to the high level of flexibility.