Skip to content

Databases -- Practice Problems

Databases — Practice Problems

10 MCQ questions covering SQL queries, normalisation (1NF to BCNF), ACID properties, indexing strategies, and query optimization. Select an option to check your answer and view the explanation.

Intuition

Organising information like a filing cabinet: Databases are like a well-organised filing cabinet — normalisation removes duplicate folders, SQL is the language for asking the librarian questions, and indexes are like a card catalogue that lets you find files without opening every drawer.

Why it matters: Every application with persistent data needs databases. Understanding normalisation prevents data corruption, indexing makes queries fast, and ACID properties keep your bank balance correct even when thousands of transactions happen simultaneously.

The key insight: Normalisation trades storage space for correctness — by eliminating redundancy, you eliminate the possibility of inconsistent data, but you may need joins (which are slower) to reassemble related information.

SQL and Normalization

Indexing, Transactions, and Query Optimization

Common Mistakes

Confusing normalisation with denormalisation: Normalisation eliminates redundancy. Denormalisation adds redundancy for performance. Don’t assume normalisation is always better.

Forgetting that NULL is not a value: NULL represents missing data. Comparisons with NULL return NULL, not true or false. Use IS NULL instead.

Mixing up cursor-based with cursor-free pagination: Cursor-based uses a pointer for efficient pagination. Cursor-free (OFFSET) skips rows, which gets slower with large offsets. Don’t assume OFFSET is efficient for deep pagination.

Cross-References

Advanced Content

This section provides detailed coverage of advanced concepts, including full derivations, proofs, and extended examples.

Derivations and Proofs

Complete mathematical derivations and proofs are provided where appropriate. Each step is explained to ensure understanding of the underlying reasoning.

Extended Examples

Advanced examples demonstrate the application of concepts to complex problems. These examples go beyond standard exam questions to develop deeper understanding.

Research Connections

This material connects to current research and advanced applications in the field. Understanding these connections provides context for the study material.

Prerequisites

Ensure you have mastered the prerequisite material before attempting this advanced content.

Advanced Content

This section provides detailed coverage of advanced concepts, including full derivations, proofs, and extended examples.

Derivations and Proofs

Complete mathematical derivations and proofs are provided where appropriate. Each step is explained to ensure understanding of the underlying reasoning.

Extended Examples

Advanced examples demonstrate the application of concepts to complex problems. These examples go beyond standard exam questions to develop deeper understanding.

Research Connections

This material connects to current research and advanced applications in the field. Understanding these connections provides context for the study material.

Prerequisites

Ensure you have mastered the prerequisite material before attempting this advanced content.

Advanced Content

This section provides detailed coverage of advanced concepts, including full derivations, proofs, and extended examples.

Derivations and Proofs

Complete mathematical derivations and proofs are provided where appropriate. Each step is explained to ensure understanding of the underlying reasoning.

Extended Examples

Advanced examples demonstrate the application of concepts to complex problems. These examples go beyond standard exam questions to develop deeper understanding.

Research Connections

This material connects to current research and advanced applications in the field. Understanding these connections provides context for the study material.

Prerequisites

Ensure you have mastered the prerequisite material before attempting this advanced content.

Advanced Content

This section provides detailed coverage of advanced concepts, including full derivations, proofs, and extended examples.

Derivations and Proofs

Complete mathematical derivations and proofs are provided where appropriate. Each step is explained to ensure understanding of the underlying reasoning.

Extended Examples

Advanced examples demonstrate the application of concepts to complex problems. These examples go beyond standard exam questions to develop deeper understanding.

Research Connections

This material connects to current research and advanced applications in the field. Understanding these connections provides context for the study material.

Prerequisites

Ensure you have mastered the prerequisite material before attempting this advanced content.

Advanced Content

This section provides detailed coverage of advanced concepts, including full derivations, proofs, and extended examples.

Derivations and Proofs

Complete mathematical derivations and proofs are provided where appropriate. Each step is explained to ensure understanding of the underlying reasoning.

Extended Examples

Advanced examples demonstrate the application of concepts to complex problems. These examples go beyond standard exam questions to develop deeper understanding.

Research Connections

This material connects to current research and advanced applications in the field. Understanding these connections provides context for the study material.

Prerequisites

Ensure you have mastered the prerequisite material before attempting this advanced content.

Advanced Content

This section provides detailed coverage of advanced concepts, including full derivations, proofs, and extended examples.

Derivations and Proofs

Complete mathematical derivations and proofs are provided where appropriate. Each step is explained to ensure understanding of the underlying reasoning.

Extended Examples

Advanced examples demonstrate the application of concepts to complex problems. These examples go beyond standard exam questions to develop deeper understanding.

Research Connections

This material connects to current research and advanced applications in the field. Understanding these connections provides context for the study material.

Prerequisites

Ensure you have mastered the prerequisite material before attempting this advanced content.

Advanced Content

This section provides detailed coverage of advanced concepts, including full derivations, proofs, and extended examples.

Derivations and Proofs

Complete mathematical derivations and proofs are provided where appropriate. Each step is explained to ensure understanding of the underlying reasoning.

Extended Examples

Advanced examples demonstrate the application of concepts to complex problems. These examples go beyond standard exam questions to develop deeper understanding.

Research Connections

This material connects to current research and advanced applications in the field. Understanding these connections provides context for the study material.

Prerequisites

Ensure you have mastered the prerequisite material before attempting this advanced content.

Advanced Content

This section provides detailed coverage of advanced concepts, including full derivations, proofs, and extended examples.

Derivations and Proofs

Complete mathematical derivations and proofs are provided where appropriate. Each step is explained to ensure understanding of the underlying reasoning.

Extended Examples

Advanced examples demonstrate the application of concepts to complex problems. These examples go beyond standard exam questions to develop deeper understanding.

Research Connections

This material connects to current research and advanced applications in the field. Understanding these connections provides context for the study material.

Prerequisites

Ensure you have mastered the prerequisite material before attempting this advanced content.

Advanced Content

This section provides detailed coverage of advanced concepts, including full derivations, proofs, and extended examples.

Derivations and Proofs

Complete mathematical derivations and proofs are provided where appropriate. Each step is explained to ensure understanding of the underlying reasoning.

Extended Examples

Advanced examples demonstrate the application of concepts to complex problems. These examples go beyond standard exam questions to develop deeper understanding.

Research Connections

This material connects to current research and advanced applications in the field. Understanding these connections provides context for the study material.

Prerequisites

Ensure you have mastered the prerequisite material before attempting this advanced content.