PostgreSQL Online Test

1. What is a "superuser" in PostgreSQL, and what privileges does it have?

A user with all privileges, including the ability to perform administrative tasks A user with limited access to specific tables A user with read-only access to the database A user with no privileges

2. What is the primary purpose of a data warehouse in the context of PostgreSQL?

To store and consolidate historical data for analysis and reporting To create new databases To optimize query performance To manage user roles

3. What is a "star schema" in the context of data warehousing with PostgreSQL?

A schema design where fact tables are connected to dimension tables A schema for encrypting data at rest A schema for creating new databases A schema for optimizing query performance

4. Which data type is used to store a single character in PostgreSQL?

CHAR STRING CHAR(1) CHARACTER

5. What does the "FULL OUTER JOIN" do in a PostgreSQL query?

Retrieves all records where there is a match in either the left or right table Retrieves records that exist in both the left and right tables Retrieves records that exist only in the left table Retrieves records that exist only in the right table

6. What is the purpose of the "CASCADE" option in foreign key constraints in PostgreSQL?

To automatically delete or update related rows in child tables To create a new table To enforce data type constraints To limit access to specific users

7. What is the primary NoSQL feature introduced in PostgreSQL to handle semi-structured data?

JSON and JSONB data types BLOB data type XML data type Point data type

8. What is "streaming replication" in PostgreSQL?

A method of replicating data by streaming changes from the primary to the standby server A method for optimizing query performance A method for creating new databases A method for taking database backups

9. What is the purpose of the "uuid-ossp" extension in PostgreSQL?

To generate universally unique identifiers (UUIDs) To create user accounts To optimize query performance To manage access control

10. What is an "auto-vacuum" in PostgreSQL, and how does it relate to performance tuning?

A process that automatically reclaims storage and optimizes table maintenance A type of index that automatically reorders data A feature that generates SQL code for optimization A background process for sorting data

11. In PostgreSQL, what is the significance of "autovacuum" for managing large datasets?

It automatically reclaims storage space and optimizes table maintenance It creates new indexes It manages database connections It enforces SSL/TLS encryption

12. What is "asynchronous replication" in PostgreSQL?

A replication method where data changes are not synchronized with the primary server immediately A method for creating read-only replicas A method for taking database backups A method for optimizing query performance

13. Which SQL language is used in PostgreSQL?

Transact-SQL PL/SQL PL/pgSQL SQL Server Transact-SQL

14. What is a recursive common table expression (RCTE) in PostgreSQL used for?

To handle hierarchical data and recursive queries To create temporary tables To join multiple tables To define primary keys

15. What is the purpose of the "EXISTS" operator in a PostgreSQL query?

To check if a subquery returns any rows To filter rows with specific criteria To join multiple tables To order the result set

16. What does "security hardening" entail in PostgreSQL security best practices?

Implementing additional security measures and configurations to protect the database Creating new databases Optimizing query performance Managing user roles

17. What is the primary purpose of a database schema in PostgreSQL?

To group database objects into logical containers To store data in tables To perform backups and restores To create database users

18. Which geospatial format is supported by the "WKT" abbreviation in PostgreSQL's geospatial features?

Well-Known Text Web Key Token Web Knowledge Transfer Web Kinetic Technology

19. What is the purpose of the "LATERAL" keyword in a PostgreSQL query?

To reference columns from the preceding tables in a subquery To order the result set To create subqueries To limit the number of rows returned

20. What does the "pg_trgm" extension provide in PostgreSQL for managing large datasets?

Text similarity and full-text search capabilities Data encryption Role-based access control Multi-factor authentication