PostgreSQL Online Test

1. What does the "ST_Intersects" function in PostgreSQL's geospatial features determine?

Whether two geometries have any spatial intersection Whether a geometry is valid Whether two geometries are identical Whether a geometry is empty

2. In PostgreSQL, which statement is used to insert data into a table?

INSERT UPDATE DELETE SELECT

3. What does "SQL injection" refer to in the context of PostgreSQL security best practices?

Unauthorized execution of malicious SQL code through unvalidated user input Creating new database tables Optimizing query performance Managing user roles

4. What is the "hstore" extension used for in PostgreSQL's NoSQL support?

To provide key-value storage for semi-structured data To store binary data To manage database indexes To create new databases

5. What is a surrogate key in the context of database design in PostgreSQL?

A system-generated unique identifier for a row The primary key of a table A user-defined key for data retrieval A foreign key linking two tables

6. 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

7. What does the "fact table" represent in a star schema of a data warehouse?

It contains measures and numeric data for analysis It stores user access logs It creates new databases It enforces strong password requirements

8. What is the purpose of PostgreSQL's "GRANT" and "REVOKE" statements in the context of security?

To assign or remove privileges on database objects to/from roles To create new tables To modify user passwords To log user activity

9. 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

10. What is the purpose of "connection pooling" in PostgreSQL performance tuning?

To reuse database connections, reducing connection overhead To store data redundantly for backup To combine multiple databases into a single pool To increase the number of available connections

11. What is "slowly changing dimension" (SCD) in data warehousing with PostgreSQL?

It refers to handling changes in data over time, especially in dimension tables It is used to create new databases It optimizes query performance It enforces strong password requirements

12. In PostgreSQL, what is the purpose of a trigger in a database schema?

To automatically execute a predefined action when certain database events occur To enforce primary key constraints To create views for data retrieval To define relationships between tables

13. 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

14. What is the purpose of the SQL "DELETE" statement in PostgreSQL?

To remove records from a table To add records to a table To create a new table To change the table structure

15. In terms of NoSQL features, what does the "array_agg" function do in PostgreSQL?

It aggregates values into an array for NoSQL-style data storage It creates physical backups It manages user roles and permissions It optimizes query performance

16. What does the "trust" authentication method in PostgreSQL do?

Allows access without requiring a password Enforces strong password requirements Encrypts data during transmission Requires multi-factor authentication

17. Which PostgreSQL extension is used to provide enhanced geospatial functions and data types for advanced GIS applications?

"PostGIS" "pgBouncer" "pg_dump" "psql"

18. Which SQL command is used to install a PostgreSQL extension?

CREATE EXTENSION INSTALL EXTENSION ADD EXTENSION USE EXTENSION

19. Which graphical user interface (GUI) tool is commonly used for managing PostgreSQL databases?

pgAdmin pgBouncer pg_dump psql

20. Which PostgreSQL extension is commonly used for encryption and decryption of data at rest?

"pgcrypto" "pgBouncer" "pg_dump" "pgAdmin"