PostgreSQL Scenario Based MCQs

Basic Level

Introduction to PostgreSQL

1. You are trying to connect to a PostgreSQL database from a command-line client but receive an error message about the host not being reachable. Which of the following is the most likely reason?
   • A) Incorrect SQL query syntax
   • B) Incorrect database name
   • C) Database server is not running
   • D) Invalid data type for the query
2. You need to create a new database in PostgreSQL. Which command should you use?
   • A) CREATE DATABASE <dbname>;
   • B) CREATE NEW DATABASE <dbname>;
   • C) CREATE DATABASE FROM <dbname>;
   • D) DATABASE CREATE <dbname>;

Getting Started with PostgreSQL

3. You successfully connect to PostgreSQL using psql but receive an error when running a SELECT query. What might be the issue?
   • A) The table does not exist in the database
   • B) The database is corrupted
   • C) You lack the necessary permissions
   • D) The query syntax is incorrect
4. You run a query to retrieve all rows from a table but no results are returned. What should you check first?
   • A) If the table contains any data
   • B) If the query is spelled correctly
   • C) If there is a network issue
   • D) If the database is in read-only mode

Data Types and Constraints

5. You want to store a large block of text in a table and need to ensure that no data is truncated. Which data type should you use for the column?
   • A) VARCHAR(255)
   • B) TEXT
   • C) CHAR(255)
   • D) BYTEA
6. You need to enforce that a column in a table only accepts non-negative numbers. Which constraint should be used?
   • A) CHECK (column >= 0)
   • B) NOT NULL
   • C) UNIQUE
   • D) DEFAULT 0

Basic SQL Operations

7. You need to update a specific record in the table where the user_id is 10, setting the status to ‘active’. Which query would achieve this?
   • A) UPDATE users SET status = ‘active’ WHERE user_id = 10;
   • B) UPDATE users SET status = ‘active’;
   • C) MODIFY users SET status = ‘active’ WHERE user_id = 10;
   • D) ALTER TABLE users SET status = ‘active’ WHERE user_id = 10;
8. You have a table where users’ email addresses must be unique. What will happen if you attempt to insert a duplicate email address?
   • A) The insert will succeed, and a duplicate entry will be allowed
   • B) The insert will fail with a “unique violation” error
   • C) The email will be automatically corrected
   • D) The record will be inserted with a NULL email value
9. You need to remove all records from a table without deleting the table structure. Which SQL statement would you use?
   • A) DROP TABLE table_name;
   • B) DELETE FROM table_name;
   • C) REMOVE ALL FROM table_name;
   • D) TRUNCATE TABLE table_name;

Indexes and Performance Basics

10. You notice that a specific query involving a large dataset is running slowly. What should you check first to improve performance?
    • A) Add more columns to the SELECT statement
    • B) Create an index on the columns involved in the WHERE clause
    • C) Reduce the size of the dataset by deleting records
    • D) Change the database engine to a faster one
11. You need to create an index on a table column to speed up queries that frequently filter by this column. Which SQL command would you use?
    • A) CREATE INDEX idx_name ON table_name (column_name);
    • B) ALTER TABLE table_name CREATE INDEX (column_name);
    • C) INDEX table_name (column_name);
    • D) CREATE UNIQUE INDEX idx_name ON table_name (column_name);

Transactions and Concurrency Control

12. You are updating records in a table but notice that other users are seeing inconsistent data. What should you implement to avoid this issue?
    • A) Increase the database’s disk space
    • B) Implement locking and isolation levels
    • C) Use a more powerful server
    • D) Increase the number of database connections
13. You run a BEGIN TRANSACTION, make some updates, but then decide not to save the changes. Which command should you use to discard the changes?
    • A) ROLLBACK;
    • B) COMMIT;
    • C) SAVEPOINT;
    • D) END;

---

Intermediate Level

Advanced Joins and Subqueries

14. You need to fetch users who have made a purchase in the last month. You want to return the user’s name and the product they bought. Which SQL query structure would be most effective for this?
    • A) JOIN between users and purchases with a subquery filtering by date
    • B) Nested SELECT with aggregate functions for each user
    • C) UNION query combining both tables
    • D) Multiple subqueries for each table in the SELECT statement
15. You want to list customers who have never placed an order. Which SQL approach would you take?
    • A) Use a LEFT JOIN with a WHERE clause filtering for NULL order IDs
    • B) Use a RIGHT JOIN with a condition for customers with orders
    • C) Use a subquery to find customers with zero orders
    • D) Use a FULL OUTER JOIN with a COUNT on order records

Views and Materialized Views

16. You want to create a read-only view of your sales data for reporting purposes. Which is the best approach?
    • A) Create a view with SELECT queries
    • B) Use a materialized view for faster query performance
    • C) Create a normal table and allow SELECT access
    • D) Use a database trigger to automatically refresh the data
17. You need to improve performance by caching complex query results for faster subsequent access. What is the best option?
    • A) Use a normal view
    • B) Use a materialized view with periodic refresh
    • C) Create a separate table for cached results
    • D) Use an index on the base table

Advanced Indexing Techniques

18. You are analyzing slow query performance. After examining the query plan, you see that a full table scan is being performed. What should you do next?
    • A) Create a composite index on the columns used in the WHERE clause
    • B) Add more columns to the SELECT statement
    • C) Drop any existing indexes
    • D) Increase the database’s RAM
19. Which indexing strategy would be ideal for speeding up searches that involve a combination of multiple columns in the WHERE clause?
    • A) B-tree index on a single column
    • B) Hash index on each column
    • C) Composite index covering all relevant columns
    • D) No index, use sequential scans instead
20. You notice that a query is still slow despite having an index on the relevant column. What other factors might affect performance?
    • A) The database server has insufficient memory
    • B) The index is not being used due to poor query structure
    • C) The database schema has too many foreign keys
    • D) All of the above

Functions and Stored Procedures

21. You need to create a reusable block of code to calculate sales tax based on the item price. Which PostgreSQL feature should you use?
    • A) Function
    • B) Trigger
    • C) View
    • D) Stored Procedure
22. You are asked to implement a function that will automatically update the stock of products after each sale. Which PostgreSQL feature would you use for this task?
    • A) Trigger
    • B) Stored Procedure
    • C) View
    • D) Index

Data Import and Export

23. You need to import data from a CSV file into a PostgreSQL table. Which command would you use to load the data?
    • A) COPY table_name FROM ‘file.csv’ WITH CSV;
    • B) IMPORT INTO table_name FROM ‘file.csv’;
    • C) LOAD DATA INFILE ‘file.csv’ INTO table_name;
    • D) INSERT INTO table_name SELECT * FROM ‘file.csv’;
24. You want to export data from PostgreSQL to a CSV file. Which command will allow you to do this?
    • A) COPY table_name TO ‘file.csv’ WITH CSV;
    • B) EXPORT table_name TO ‘file.csv’;
    • C) OUTPUT table_name INTO ‘file.csv’;
    • D) SELECT * FROM table_name INTO OUTFILE ‘file.csv’;

---

Advanced Level

Advanced Query Optimization

25. You are noticing that a query which uses multiple JOIN operations is running slowly. What should you do first to optimize it?
    • A) Use subqueries instead of JOINs
    • B) Add an index on the columns used in JOIN conditions
    • C) Change the query to a UNION ALL query
    • D) Increase the server’s RAM
26. You have a query that involves aggregating data over a large dataset. Which approach would speed up this query?
    • A) Use indexes on the columns involved in aggregation
    • B) Increase the number of tables involved in the query
    • C) Remove the GROUP BY clause
    • D) Use a subquery for each aggregation

Partitioning and Sharding

27. You have a large table that is growing rapidly. What partitioning strategy would help optimize query performance and manageability?
    • A) Partition by range (e.g., date ranges)
    • B) Use sharding across multiple database servers
    • C) Use clustering to store similar data together
    • D) Both A and B
28. You need to distribute large datasets across multiple servers for horizontal scalability. What technique should you use?
    • A) Partitioning
    • B) Sharding
    • C) Indexing
    • D) Clustering

Logical and Physical Replication

29. You need to ensure high availability of your PostgreSQL database. Which type of replication should you set up?
    • A) Logical replication
    • B) Physical replication
    • C) Both logical and physical replication
    • D) No replication is required for high availability
30. A replica database is lagging behind the master due to high transaction volume. What should you check to address this issue?
    • A) Network latency between the master and replica
    • B) CPU and disk performance of the replica server
    • C) Replication configuration settings
    • D) All of the above

---

Answers Table

Qno	Answer
1	B) 5432
2	A) postgres
3	A) The table does not exist in the database
4	A) If the table contains any data
5	B) TEXT
6	A) CHECK (column >= 0)
7	A) UPDATE users SET status = ‘active’ WHERE user_id = 10;
8	B) The insert will fail with a “unique violation” error
9	D) TRUNCATE TABLE table_name;
10	B) Create an index on the columns involved in the WHERE clause
11	A) CREATE INDEX idx_name ON table_name (column_name);
12	B) Implement locking and isolation levels
13	A) ROLLBACK;
14	A) JOIN between users and purchases with a subquery filtering by date
15	A) Use a LEFT JOIN with a WHERE clause filtering for NULL order IDs
16	A) Create a view with SELECT queries
17	B) Use a materialized view with periodic refresh
18	A) Create a composite index on the columns used in the WHERE clause
19	C) Composite index covering all relevant columns
20	D) All of the above
21	A) Function
22	A) Trigger
23	A) COPY table_name FROM ‘file.csv’ WITH CSV;
24	A) COPY table_name TO ‘file.csv’ WITH CSV;
25	B) Add an index on the columns used in JOIN conditions
26	A) Use indexes on the columns involved in aggregation
27	D) Both A and B
28	B) Sharding
29	B) Physical replication
30	D) All of the above