PostgreSQL MCQ Questions

Repeatable Read isolation ensures transactions see consistent snapshot throughout transaction. It prevents non-repeatable reads and phantom reads through MVCC. Serialization errors can occur requiring retry logic. Understanding Repeatable Read enables stronger consistency requirements.

Partitioning divides large tables into smaller pieces. Methods include range, list, and hash partitioning. Partitioning improves performance and maintenance. Understanding partitioning enables scaling large tables.

Declarative partitioning uses PARTITION BY clause defining partitioning strategy. Partitions are created as child tables. Understanding declarative partitioning simplifies partition management.

False. With table inheritance in PostgreSQL, child tables do NOT inherit primary key, foreign key, or unique constraints from parent tables. Children inherit columns and check constraints but constraints requiring indexes aren't inherited. This design prevents constraint conflicts across inheritance hierarchies. Understanding inheritance limitations ensures proper constraint definition in child tables.

JSONB operators enable efficient JSON querying including containment (@>), existence (?), and path extraction (->). GIN indexes optimize JSONB queries. Understanding JSONB enables document database patterns.

Persistent objects store class instances in databases using SAP persistence service. They enable object-oriented data persistence. Understanding persistent objects enables OO database programming.

Advisory locks are application-level locks managed by PostgreSQL but interpreted by application. They enable coordinating across sessions without locking database objects. Understanding advisory locks enables custom locking strategies.

OData services provide RESTful access to SAP data. Gateway framework exposes OData services. Understanding OData enables modern API development.

Constraints enforce data integrity including PRIMARY KEY, FOREIGN KEY, UNIQUE, CHECK, NOT NULL, and EXCLUSION. They prevent invalid data entry. Understanding constraints ensures data quality and business rule enforcement.

Common Table Expressions (CTEs) define temporary result sets using WITH. They improve query readability and enable recursion. CTEs make complex queries maintainable. Understanding CTEs enables building complex queries clearly.

NOT NULL constraints prevent NULL values in columns. They ensure required data presence. Understanding NOT NULL enforces data completeness.

EXCEPT returns rows in first query not in second. It finds differences between result sets. Understanding EXCEPT enables identifying unique rows in one dataset.

The query has a subquery that can return multiple values but uses = operator which expects single value. The correct operator should be IN when subquery returns multiple rows. The query should be: SELECT * FROM Orders WHERE OrderID IN (SELECT OrderID FROM OrderItems WHERE...). Understanding scalar vs. set operators prevents SQL errors.

Stored procedures are functions that can commit/rollback transactions. They use CALL statement. Procedures enable transaction control within routines. Understanding procedures enables complex transaction logic.

Table maintenance generator (SE54) creates maintenance dialogs for tables and views. It generates screens and function modules automatically. Understanding TMG enables quick maintenance dialog creation.

Table controls display and edit internal table data in screens. They provide grid-like interfaces. Understanding table controls enables tabular data editing.

ANALYZE collects statistics about table contents used by query planner. Accurate statistics enable optimal query plans. ANALYZE runs automatically with autovacuum but can be run manually. Understanding ANALYZE ensures efficient query execution through proper statistics.

INNER JOIN returns only matching rows from both tables based on join condition. It's most common join type. Non-matching rows are excluded. Understanding INNER JOIN enables combining related data from multiple tables.

#N/A

ALE (Application Link Enabling) distributes applications and data across SAP systems using IDOCs and BAPIs. It enables distributed landscapes. Understanding ALE enables system integration.

Hash indexes support only equality comparisons using = operator. They're smaller than B-trees for certain data but historically had limitations including lack of WAL logging. Modern PostgreSQL versions improved hash indexes but B-trees remain preferred for most cases. Understanding hash index limitations guides appropriate use.

Triggers execute functions automatically on INSERT, UPDATE, DELETE, or TRUNCATE. They can be BEFORE, AFTER, or INSTEAD OF. Triggers enable auditing, validation, and cascading changes. Understanding triggers enables reactive database logic.

CDS (Core Data Services) define semantic data models using DDL. CDS views enable advanced data modeling. Understanding CDS enables modern ABAP development on HANA.

Logical replication replicates specific tables/changes selectively. It enables database upgrades and data distribution. Understanding logical replication enables flexible replication scenarios.

CLUSTER physically reorders table data according to index order, improving sequential access performance. It requires exclusive lock and isn't maintained automatically. CLUSTER is expensive but benefits range scans. Understanding cluster enables optimizing specific access patterns.

Database concepts include normalization, indexing, transactions, and optimization. Understanding these fundamentals enables designing efficient, reliable database systems. Proper database design considers performance, integrity, scalability, and maintainability requirements. Each concept serves specific purposes in ensuring database quality and effectiveness.

Covering indexes include all columns needed by queries, enabling index-only scans without accessing table data. They're created using INCLUDE clause for non-key columns. Covering indexes dramatically improve query performance by avoiding table access. Understanding covering indexes enables maximum query optimization.

The correct order of SELECT statement clauses is: 1-SELECT 2-FROM 3-WHERE 4-GROUP BY 5-HAVING 6-ORDER BY. This order represents logical processing flow: select columns, from tables, filter rows, group results, filter groups, order output. Understanding clause order prevents syntax errors and ensures proper query construction.