PostgreSQL Database: Comprehensive Learning Quizzes for Further Education

Question 1

Which command is used to retrieve data from a table in PostgreSQL?

Accepted Answer

SELECT

Answer

FETCH

Answer

GET

Answer

RETRIEVE

Question 2

What data type would be most appropriate for storing a product's price in PostgreSQL?

Accepted Answer

NUMERIC

Answer

VARCHAR

Answer

INTEGER

Question 3

In PostgreSQL, what is a 'view'?

Accepted Answer

A virtual table based on a stored query.

Answer

A type of index for faster data retrieval.

Answer

A physical table storing actual data.

Answer

A security mechanism to restrict user access.

Question 4

Which command is used to modify existing data in a table?

Accepted Answer

UPDATE

Answer

MODIFY

Answer

ALTER

Answer

CHANGE

Question 5

What is a foreign key used for in PostgreSQL?

Accepted Answer

To establish a link between columns in different tables.

Answer

To uniquely identify each row in a table.

Answer

To define the data type of a column.

Question 6

What is the role of the PostgreSQL server process ('postgres')?

Accepted Answer

It manages the database cluster and handles client connections.

Answer

It executes SQL queries and returns results to the client.

Answer

It writes data changes to the log file for recovery purposes.

Question 7

What is the purpose of using an index in PostgreSQL?

Accepted Answer

To speed up data retrieval by providing a search path.

Answer

To enforce data integrity and prevent duplicate values.

Answer

To define relationships between different tables.

Question 8

What command is used to create a new database in PostgreSQL?

Accepted Answer

CREATE DATABASE

Answer

NEW DATABASE

Answer

INIT DATABASE

Answer

MAKE DATABASE

Question 9

What command is used to create a new database in PostgreSQL?

Accepted Answer

CREATE DATABASE

Answer

INSERT INTO

Answer

SELECT

Answer

CREATE TABLE

Question 10

Which of these statements is true about a primary key constraint in PostgreSQL?

Accepted Answer

It enforces uniqueness for each row in a table.

Answer

It is used to store large binary data.

Answer

It defines the default value for a column.

Answer

It allows duplicate values in a column.

Question 11

You want to retrieve data from a table called 'users' where the 'age' column is greater than 30. What SQL query would you use?

Accepted Answer

SELECT * FROM users WHERE age > 30;

Answer

SELECT * FROM users WHERE age = 30;

Answer

SELECT * FROM users ORDER BY age > 30;

Answer

SELECT age FROM users WHERE age > 30;

Question 12

What is the purpose of the 'ORDER BY' clause in a SELECT statement?

Accepted Answer

To sort the retrieved data in a specific order.

Answer

To filter data based on conditions.

Answer

To create a new table.

Answer

To delete data from a table.

Question 13

Which PostgreSQL command is used to modify the structure of an existing table?

Accepted Answer

ALTER TABLE

Answer

CREATE TABLE

Answer

UPDATE

Answer

DELETE

Question 14

What is the difference between 'TRUNCATE TABLE' and 'DELETE FROM' in PostgreSQL?

Accepted Answer

TRUNCATE TABLE removes all rows without logging, while DELETE FROM logs each row deletion.

Answer

They both perform the same function.

Answer

TRUNCATE TABLE deletes a table, while DELETE FROM removes rows.

Question 15

How can you ensure data consistency and integrity in PostgreSQL?

Accepted Answer

By using constraints, triggers, and transactions.

Answer

By using only foreign keys.

Answer

By backing up the database regularly.

Answer

By using only primary keys.

Question 16

What is a stored procedure in PostgreSQL?

Accepted Answer

A pre-compiled SQL code block that can be executed multiple times.

Answer

A trigger that automatically executes after a specific event.

Answer

A temporary table used for storing data.

Question 17

Which of the following is NOT a benefit of using PostgreSQL?

Accepted Answer

It is a proprietary database system.

Answer

It offers high performance and scalability.

Answer

It is open-source and free to use.

Answer

It supports a wide range of data types.

Question 18

Which of the following is NOT a data type supported by PostgreSQL?

Accepted Answer

Bit

Answer

Date

Answer

Text

Answer

Boolean

Question 19

What is the command used to create a new table in PostgreSQL?

Accepted Answer

CREATE TABLE

Answer

BUILD TABLE

Answer

MAKE TABLE

Question 20

Which of the following would be the most appropriate constraint to enforce uniqueness of a column in a PostgreSQL table?

Accepted Answer

UNIQUE

Answer

PRIMARY KEY

Answer

NOT NULL

Answer

FOREIGN KEY

Question 21

What is the purpose of the ORDER BY clause in a PostgreSQL query?

Accepted Answer

To sort the results of the query

Answer

To filter the results of the query

Answer

To group the results of the query

Answer

To limit the number of results returned

Question 22

Which of the following is a commonly used identifier for PostgreSQL roles?

Accepted Answer

postgres

Answer

admin

Answer

root

Answer

user

Question 23

What is the default port number for PostgreSQL connections?

Accepted Answer

5432

Answer

3306

Answer

8080

Answer

443

Question 24

Which of the following is NOT a benefit of using PostgreSQL for data warehousing?

Accepted Answer

High concurrency

Answer

Advanced indexing

Answer

Large data handling

Answer

Data compression

Question 25

What is the purpose of the VACUUM command in PostgreSQL?

Accepted Answer

To reclaim unused space and improve performance

Answer

To optimize query performance

Answer

To repair corrupted data

Answer

To create a backup of the database

Question 26

Which of the following is a popular object-relational mapping (ORM) framework for PostgreSQL?

Accepted Answer

Hibernate

Answer

Entity Framework

Answer

NHibernate

Answer

Spring JPA

Question 27

What is the term for a logical grouping of related tables in PostgreSQL?

Accepted Answer

Schema

Answer

Collection

Answer

Database

Answer

Namespace

Question 28

Which of the following is the default port for PostgreSQL connections?

Accepted Answer

5432

Answer

443

Answer

8080

Answer

3306

Question 29

Which data type is used to store dates and times in PostgreSQL?

Accepted Answer

timestamp

Answer

datetime

Answer

date

Answer

time

Question 30

What is the purpose of the "VACUUM" command in PostgreSQL?

Accepted Answer

Reclaims unused space and optimizes performance

Answer

Creates a new database

Answer

Deletes all data from a table

Answer

Reindexes a table

Question 31

Which of the following is a key feature of PostgreSQL's architecture?

Accepted Answer

Multi-user and concurrent access

Answer

Limited transaction support

Answer

Lack of support for stored procedures

Answer

Single-threaded execution

Question 32

What is the syntax to create a new table named "students" with columns "id", "name", and "age" in PostgreSQL?

Accepted Answer

CREATE TABLE students (id SERIAL PRIMARY KEY, name VARCHAR(255), age INTEGER);

Answer

CREATE TABLE students (id INT, name VARCHAR, age INT);

Answer

CREATE TABLE students (id VARCHAR, name VARCHAR, age VARCHAR);

Question 33

Which of the following is a valid way to insert a new record into the "students" table?

Accepted Answer

INSERT INTO students (name, age) VALUES ('John Doe', 21);

Answer

UPDATE students SET name = 'John Doe', age = 21;

Answer

ADD students (name, age) VALUES ('John Doe', 21);

Question 34

Which index type is most suitable for a column that is frequently used for equality comparisons?

Accepted Answer

B-tree index

Answer

Hash index

Answer

Bitmap index

Answer

GIN index

Question 35

What is the purpose of the "EXPLAIN" command in PostgreSQL?

Accepted Answer

Provides information about the execution plan for a query

Answer

Optimizes a query's performance

Answer

Displays the results of a query

Question 36

Which of the following is a benefit of using PostgreSQL stored procedures?

Accepted Answer

Improved performance and code reusability

Answer

Increased data security

Answer

Simplified database administration

Answer

Enhanced user interface capabilities

Question 37

How can you connect to a PostgreSQL database using the command line?

Accepted Answer

psql -h HOST -p PORT -U USER -d DATABASE

Answer

mysql -h HOST -p PORT -u USER -d DATABASE

Answer

redis-cli -h HOST -p PORT -a USER -d DATABASE

Answer

mongo -h HOST -p PORT -u USER -d DATABASE

Question 38

Which data type in PostgreSQL would be most suitable for storing a customer's phone number?

Accepted Answer

VARCHAR

Answer

INTEGER

Answer

TEXT

Answer

TIMESTAMP

Question 39

What command is used to create a new table in PostgreSQL?

Accepted Answer

CREATE TABLE

Answer

SELECT

Answer

UPDATE

Answer

INSERT INTO

Question 40

Which of the following is NOT a valid constraint type in PostgreSQL?

Accepted Answer

UNIQUEKEY

Answer

FOREIGN KEY

Answer

PRIMARY KEY

Answer

CHECK

Question 41

You need to retrieve all customer records sorted by their last name in ascending order. Which SQL clause would you use?

Accepted Answer

ORDER BY

Answer

HAVING

Answer

WHERE

Answer

GROUP BY

Question 42

What is the purpose of the 'WHERE' clause in a SQL query?

Accepted Answer

Filter rows based on specific conditions.

Answer

Sort the result set.

Answer

Group similar rows together.

Answer

Create a new table from existing data.

Question 43

Which PostgreSQL command allows you to modify data within an existing table?

Accepted Answer

UPDATE

Answer

CREATE TABLE

Answer

ALTER TABLE

Question 44

What is the difference between a 'VIEW' and a 'TABLE' in PostgreSQL?

Accepted Answer

A VIEW is a virtual table based on a query, while a TABLE stores actual data.

Answer

A TABLE is faster to access than a VIEW.

Answer

A VIEW can store more data than a TABLE.

Question 45

Which of the following is NOT a built-in function in PostgreSQL?

Accepted Answer

AVERAGE()

Answer

MAX()

Answer

COUNT()

Answer

SUM()

Question 46

You need to create a backup of your PostgreSQL database. Which command should you use?

Accepted Answer

pg_dump

Answer

vacuum

Answer

psql

Answer

pg_restore

Question 47

What is the purpose of the 'JOIN' clause in a SQL query?

Accepted Answer

Combine data from multiple tables based on a common column.

Answer

Sort the result set.

Answer

Filter rows based on specific conditions.