PostgreSQL Quiz - MCQ - Multiple Choice Questions

Welcome to the PostgreSQL Quiz for beginners! Whether you're testing your knowledge or looking to learn something new, this quiz has you covered. We will be covering the fundamental concepts of PostgreSQL through 40+ multiple-choice questions. Let's dive in!

1. What is PostgreSQL?

A. A NoSQL database

B. A relational database management system (RDBMS)

C. A programming language

D. A web server

Answer:

B. A relational database management system (RDBMS)

Explanation:

PostgreSQL is an open-source RDBMS known for its extensibility and standards compliance.

2. Which of the following is a key feature of PostgreSQL?

A. Support for JSON

B. Exclusively uses the MyISAM storage engine

C. Proprietary software

D. Limited to small datasets

Answer:

A. Support for JSON

Explanation:

PostgreSQL offers extensive support for JSON, allowing for a mix of structured and semi-structured data storage.

3. Which command-line utility is used for interactive PostgreSQL operations?

A. pgadmin

B. psql

C. pgsql

D. adminsql

Answer:

B. psql

Explanation:

psql is the interactive terminal for working with PostgreSQL.

4. Which SQL command is used to create a new PostgreSQL database?

A. NEW DATABASE

B. ADD DATABASE

C. CREATE DATABASE

D. DATABASE CREATE

Answer:

C. CREATE DATABASE

Explanation:

The command CREATE DATABASE is used to create a new PostgreSQL database.

5. How do you add a comment in SQL in PostgreSQL?

A. /* Comment Here */

B. // Comment Here

C. -- Comment Here

D. # Comment Here

Answer:

C. -- Comment Here

Explanation:

In PostgreSQL, the double dash -- is used to add a single-line comment.

6. Which data type in PostgreSQL can be used to store binary data?

A. CHAR

B. BINARY

C. BYTEA

D. DATA_BYTE

Answer:

C. BYTEA

Explanation:

The BYTEA type is used to store binary data or "byte arrays".

7. Which command in PostgreSQL is used to list all the available databases?

A. SHOW DATABASES;

B. \ld

C. \l

D. DISPLAY DATABASES;

Answer:

C. \l

Explanation:

In the psql interface, the command \l lists all available databases.

8. Which keyword is used in PostgreSQL to define a primary key constraint?

A. PRIMARY

B. KEY

C. UNIQUE

D. PRIMARY KEY

Answer:

D. PRIMARY KEY

Explanation:

The PRIMARY KEY constraint uniquely identifies each record in a table.

9. Which of the following PostgreSQL functions can be used to obtain the current date and time?

A. GET_TIMESTAMP()

B. CURRENT_TIMESTAMP

C. NOW_DATE_TIME()

D. FETCH_DATETIME()

Answer:

B. CURRENT_TIMESTAMP

Explanation:

In PostgreSQL, CURRENT_TIMESTAMP returns the current date and time.

10. What is the maximum length of a table name in PostgreSQL?

A. 32 characters

B. 64 characters

C. 128 characters

D. 256 characters

Answer:

B. 64 characters

Explanation:

By default, PostgreSQL has a maximum identifier length of 64 characters.

11. Which PostgreSQL function returns the number of characters in a string?

A. STRLEN()

B. LENGTH()

C. CHAR_COUNT()

D. NUMCHAR()

Answer:

B. LENGTH()

Explanation:

The LENGTH() function in PostgreSQL is used to get the number of characters in a string.

12. What does the SERIAL keyword in PostgreSQL do?

A. Creates a series of numbers

B. Creates an auto-increment integer column

C. Serializes a table for export

D. Links tables in a series

Answer:

B. Creates an auto-increment integer column

Explanation:

SERIAL is used to define auto-incrementing integer columns in PostgreSQL.

13. In which language is PostgreSQL written?

A. Python

B. Java

C. C

D. Ruby

Answer:

C. C

Explanation:

PostgreSQL is primarily written in the C programming language.

14. Which of the following is NOT a valid backup option for PostgreSQL?

A. pg_dump

B. pg_restore

C. pg_backup

D. pg_basebackup

Answer:

C. pg_backup

Explanation:

While pg_dump, pg_restore, and pg_basebackup are valid PostgreSQL backup tools, pg_backup is not.

15. Which command can be used to see the query plan for a statement without executing it?

A. EXPLAIN

B. DESCRIBE

C. QUERY PLAN

D. INSPECT

Answer:

A. EXPLAIN

Explanation:

The EXPLAIN command in PostgreSQL displays the execution plan of a SQL statement without running it.

16. Which PostgreSQL feature allows for partitioning tables?

A. Table Slicing

B. Table Mapping

C. Table Segmentation

D. Table Inheritance

Answer:

D. Table Inheritance

Explanation:

Table inheritance in PostgreSQL can be used as a way to achieve table partitioning.

17. How do you retrieve the version of the PostgreSQL server you're connected to?

A. SHOW VERSION();

B. SELECT version();

C. RETRIEVE VERSION;

D. SELECT @version;

Answer:

B. SELECT version();

Explanation:

The version() function returns the version of the PostgreSQL server.

18. Which command-line utility is used for administrative tasks like creating, deleting, and maintaining PostgreSQL databases?

A. pgadmin

B. pgsql

C. psql

D. createdb and dropdb

Answer:

D. createdb and dropdb

Explanation:

createdb and dropdb are command-line utilities for creating and deleting PostgreSQL databases, respectively.

19. Which datatype is used in PostgreSQL to store IPv4 and IPv6 addresses?

A. IP

B. NET_ADDR

C. INET

D. NETWORK

Answer:

C. INET

Explanation:

The INET type in PostgreSQL is used to store both IPv4 and IPv6 addresses.

20. Which command can be used to list all the tables in the current PostgreSQL database?

A. \tables

B. \l

C. \dt

D. LIST TABLES;

Answer:

C. \dt

Explanation:

In the psql interface, the command \dt lists all the tables in the current database.

21. Which of the following is NOT a locking mechanism in PostgreSQL?

A. Advisory Locks

B. Tuple Locks

C. Transaction Locks

D. Segment Locks

Answer:

D. Segment Locks

Explanation:

PostgreSQL does not have a "Segment Locks" mechanism.

22. PostgreSQL is often referred to as an ORDBMS. What does the "OR" stand for?

A. Operational Relational

B. Object-Relational

C. Organized Record

D. Open-Resource

Answer:

B. Object-Relational

Explanation:

PostgreSQL is often termed as an Object-Relational Database Management System (ORDBMS) because it supports both relational and object-oriented database features.

23. How would you retrieve unique values from a column named "names" in a table called "users"?

A. SELECT DISTINCT(names) FROM users;

B. UNIQUE SELECT names FROM users;

C. SELECT names UNIQUE FROM users;

D. GET DISTINCT names FROM users;

Answer:

A. SELECT DISTINCT(names) FROM users;

Explanation:

The DISTINCT keyword is used to retrieve unique values from a column in PostgreSQL.

24. Which function would you use to obtain the current user name in PostgreSQL?

A. GET_USER();

B. CURRENT_USER();

C. USER_NAME();

D. CURRENT_USER;

Answer:

D. CURRENT_USER;

Explanation:

The CURRENT_USER keyword in PostgreSQL returns the name of the current user.

25. Which command will allow you to switch to a different database named 'testdb' in the psql interface?

A. USE testdb;

B. \c testdb;

C. SWITCH testdb;

D. SELECT DATABASE testdb;

Answer:

B. \c testdb;

Explanation:

In the psql interface, the command \c followed by the database name allows you to switch to a different database.

26. What is the primary role of the WAL in PostgreSQL?

A. Web Access Layer

B. Write Ahead Logging

C. Write After Load

D. Workload Allocation Logic

Answer:

B. Write Ahead Logging

Explanation:

In PostgreSQL, WAL stands for Write Ahead Logging. It's a method where changes are logged before they are applied to the database, ensuring data integrity and consistency.

27. Which of the following data types would be best for storing monetary values in PostgreSQL?

A. FLOAT

B. INTEGER

C. MONEY

D. DECIMAL

Answer:

C. MONEY

Explanation:

The MONEY type is specifically designed for storing currency amounts in PostgreSQL. It handles currency symbols and formatting.

28. What is the purpose of the VACUUM command in PostgreSQL?

A. To clean and optimize the database

B. To backup the database

C. To restore the database

D. To migrate data

Answer:

A. To clean and optimize the database

Explanation:

The VACUUM command reclaims storage occupied by dead tuples, and optionally, optimizes the database file structures.

29. Which of these is a PostgreSQL tool for creating a physical backup?

A. pg_dump

B. pg_backup

C. pg_basebackup

D. pg_clone

Answer:

C. pg_basebackup

Explanation:

pg_basebackup is used for taking base backups of a running PostgreSQL database cluster.

30. How do you concatenate two columns in PostgreSQL?

A. USING

B. JOIN

C. +

D. ||

Answer:

D. ||

Explanation:

In PostgreSQL, you can concatenate columns using the || operator.

31. Which of these commands is used to remove a table from PostgreSQL?

A. DELETE TABLE

B. DROP TABLE

C. REMOVE TABLE

D. DISCARD TABLE

Answer:

B. DROP TABLE

Explanation:

The DROP TABLE command is used to remove a table from PostgreSQL.

32. What does the CASCADE option do when used with the DROP TABLE command?

A. Drops the table and all associated indexes

B. Drops the table and any dependent objects

C. Recreates the table after dropping

D. Ignores any dependencies and drops the table

Answer:

B. Drops the table and any dependent objects

Explanation:

When using CASCADE with DROP TABLE, it ensures that the table and any dependent objects (like views) are dropped.

33. What is the default port on which PostgreSQL listens?

A. 3306

B. 8080

C. 5432

D. 80

Answer:

C. 5432

Explanation:

By default, PostgreSQL listens on port 5432.

34. Which command in psql will list all the databases?

A. \list

B. \databases

C. \showdb

D. \db

Answer:

A. \list

Explanation:

In the psql interface, the \list or \l command lists all the available databases.

35. How would you describe the structure of a table named "employees" using psql?

A. \d employees

B. DESCRIBE employees;

C. \tableinfo employees

D. \struct employees

Answer:

A. \d employees

Explanation:

In psql, you can use the \d command followed by the table name to describe its structure.

36. If you want to see the list of users and their roles in psql, which command would you use?

A. \users

B. \roles

C. \du

D. \listusers

Answer:

C. \du

Explanation:

The \du command in psql lists all roles (users are also roles) and their attributes.

37. Which psql command would you use to turn on the timing of commands?

A. \timeon

B. \timing

C. \showtime

D. \settime

Answer:

B. \timing

Explanation:

The \timing command in psql can be used to turn the timing of commands on or off.

38. How would you quit out of the psql interface?

A. \exit

B. QUIT;

C. \q

D. LEAVE;

Answer:

C. \q

Explanation:

To quit psql, you simply enter the command \q.

39. Which command is used to display the history of executed SQL commands in psql?

A. \history

B. \h

C. \s

D. \log

Answer:

C. \s

Explanation:

The \s command in psql displays the history of SQL commands that have been executed in the current session.

40. What does the psql command \e do?

A. Exits the psql shell

B. Opens the last SQL command in an editor

C. Executes the SQL command

D. Echoes the SQL command

Answer:

B. Opens the last SQL command in an editor

Explanation:

The \e command in psql opens the last SQL command in a text editor. This is useful for long queries that need editing.