Test Your PostgreSQL Database Platforms Knowledge with This Quiz

Progress

True / False

PostgreSQL is an open-source relational database management system (RDBMS).

True False

PostgreSQL is an open-source RDBMS known for its robustness and compliance with SQL standards.

True / False

In PostgreSQL, databases are not case-sensitive by default.

True False

PostgreSQL treats identifiers (like table and column names) as case-insensitive by default unless they are quoted.

True / False

PostgreSQL does not support JSON data types.

True False

PostgreSQL has strong support for JSON data types, allowing for efficient storage and querying of JSON data.

True / False

In PostgreSQL, the VACUUM command is used to reclaim storage and optimize database performance

True False

The VACUUM command in PostgreSQL helps to reclaim storage occupied by dead tuples and optimize performance

True / False

PostgreSQL supports full-text search capabilities natively.

True False

PostgreSQL includes built-in full-text search capabilities, allowing users to perform complex text searches within their databases.

True / False

In PostgreSQL, the SERIAL data type automatically generates unique integer values.

True False

The SERIAL data type in PostgreSQL is used to create auto-incrementing integer columns, commonly used for primary keys.

True / False

PostgreSQL supports multi-version concurrency control (MVCC) to handle concurrent transactions.

True False

PostgreSQL uses multi-version concurrency control (MVCC) to manage concurrent transactions efficiently without locking the database.

True / False

In PostgreSQL, a foreign data wrapper (FDW) is used to query external data sources as if they were regular PostgreSQL tables.

True False

Foreign Data Wrappers (FDWs) in PostgreSQL allow users to access and query external data sources as if they were regular tables in PostgreSQL.

True / False

PostgreSQL's pg_dump utility can only be used to back up entire databases, not specific tables.

True False

The pg_dump utility in PostgreSQL can be used to back up entire databases or specific tables

Q10

True / False

In PostgreSQL, the RECURSIVE keyword is used in a WITH clause to create recursive Common Table Expressions (CTEs).

True False

The RECURSIVE keyword in a WITH clause in PostgreSQL is used to create recursive Common Table Expressions (CTEs), which allow a query to reference itself.

Q11

Single Choice

Which MySQL command is used to check the current database platform version?

SELECT DATABASE() SHOW VARIABLES LIKE '%version%' SHOW PLATFORMSSELECT VERSION()

The SELECT VERSION() command is used to retrieve the version of the MySQL database platform currently in use.

Q12

Single Choice

Which of the following platforms is MySQL primarily used on?

Desktop applications Mobile apps Web serversCloud storage

MySQL is widely used on web servers as the database platform for web applications.

Q13

Single Choice

What MySQL command can be used to show all available databases in the current platform?

SHOW TABLES SHOW SCHEMAS SHOW DATABASESSHOW ALL

The SHOW DATABASES command lists all databases available on the current MySQL platform

Q14

Single Choice

How do you connect to a MySQL database on a remote platform using the MySQL command-line client?

mysql -u root -h remote_host -P 3306 mysql -u root -h localhost -P 3306 mysql -u root -h localhostmysql -u root -h remote_host

The -h option specifies the remote host, and -P specifies the port (3306 is the default MySQL port). This command connects to the MySQL database on a remote platform.

Q15

Single Choice

Which storage engine in MySQL is best suited for transactional databases on enterprise platforms?

MyISAM InnoDB MEMORYCSV

InnoDB is a storage engine in MySQL that supports ACID-compliant transactions, making it ideal for enterprise-level database platforms.

Q16

Single Choice

Given the SQL code below, what will be the output if the MySQL platform is configured to allow multiple database connections?

SQL Code

SELECT @@max_connections;

The maximum number of simultaneous connections The current database platform name The total number of databasesThe list of all users

The @@max_connections system variable returns the maximum number of simultaneous connections that the MySQL database platform allows.

Q17

Single Choice

Which MySQL feature allows data to be distributed across multiple platforms for better performance and reliability?

Sharding Replication ClusteringPartitioning

MySQL Replication allows data from one database to be copied to others across different platforms, providing high availability and better performance

Q18

Single Choice

Consider the following SQL code. What platform-related MySQL feature is being configured?

SQL Code

SET GLOBAL innodb_buffer_pool_size = 8G;

Platform user management Memory allocation for InnoDB storage Platform-level transaction controlPlatform backup configuration

The innodb_buffer_pool_size parameter defines the amount of memory allocated for the InnoDB buffer pool on the MySQL platform

Q19

Single Choice

On a MySQL platform configured with multiple databases, how can a specific platform’s schema be exported using a command-line tool?

mysqlbackup --database=dbname mysqldump --database=dbname mysqlbackup --export-schema=dbnamemysqldump --schema=dbname

The mysqldump command is used to export a specific database schema from the MySQL platform. The --database option specifies the database to be exported

Q20

Single Choice

Which MySQL platform feature can be configured for automated database failover and recovery across different geographical regions?

MySQL Cluster MySQL Fabric MySQL BackupMySQL Workbench

MySQL Fabric is a management framework that supports automated high availability, sharding, and database failover across multiple platforms and geographical regions, making it suitable for enterprise-grade applications.

Q21

Multiple Choice

Which of the following SQL commands correctly creates a database in PostgreSQL, a key component of a database platform?

SQL Code

CREATE DATABASE app_db
WITH OWNER = app_owner
ENCODING = 'UTF8'
LC_COLLATE = 'en_US.UTF-8'
LC_CTYPE = 'en_US.UTF-8'
TEMPLATE = template0;

CREATE DATABASE app_db WITH OWNER = app_owner ENCODING = 'UTF8' LC_COLLATE = 'en_US.UTF-8' LC_CTYPE = 'en_US.UTF-8' TEMPLATE = template0; CREATE DATABASE app_db; CREATE DATABASE app_db WITH OWNER = app_owner;CREATE DATABASE app_db ENCODING = 'UTF8';

This query creates a database named 'app_db' with specified owner, encoding, and locale settings, which are essential when setting up a database platform.

Q22

Multiple Choice

Which SQL statement in PostgreSQL is used to create a role for managing access to the database platform?

SQL Code

CREATE ROLE app_admin
WITH LOGIN PASSWORD 'secure_password'
CREATEDB CREATEROLE;

CREATE ROLE app_admin WITH LOGIN PASSWORD 'secure_password' CREATEDB CREATEROLE; CREATE ROLE app_user WITH LOGIN PASSWORD 'user_password' NOSUPERUSER; CREATE ROLE app_readonly WITH LOGIN PASSWORD 'readonly_password' NOCREATEDB;CREATE ROLE app_dev WITH LOGIN PASSWORD 'dev_password' CREATEDB;

This command creates a role named 'app_admin' with permissions to log in, create databases, and create other roles, which is crucial for managing access in a database platform.

Q23

Multiple Choice

Identify the SQL query that grants specific privileges to a role on a PostgreSQL database platform.

SQL Code

GRANT SELECT, INSERT, UPDATE
ON ALL TABLES IN SCHEMA public
TO app_user;

GRANT SELECT, INSERT, UPDATE ON ALL TABLES IN SCHEMA public TO app_user; GRANT ALL PRIVILEGES ON DATABASE app_db TO app_admin; GRANT USAGE ON SCHEMA public TO app_user;GRANT SELECT, INSERT ON ALL TABLES IN SCHEMA public TO app_readonly;

This query grants the 'app_user' role the ability to SELECT, INSERT, and UPDATE data in all tables within the 'public' schema, managing access control on the database platform.

Q24

Multiple Choice

Which SQL command in PostgreSQL is correctly used to create a schema as part of organizing the database platform?

SQL Code

CREATE SCHEMA app_data
AUTHORIZATION app_owner;

CREATE SCHEMA app_data AUTHORIZATION app_owner; CREATE SCHEMA app_data; CREATE SCHEMA AUTHORIZATION app_owner;CREATE SCHEMA app_owner;

This command creates a schema named 'app_data' and assigns its ownership to 'app_owner', which is an important step in organizing the database platform's structure.

Q25

Multiple Choice

Select the correct SQL statement that transfers ownership of a PostgreSQL table to another role within the database platform.

SQL Code

ALTER TABLE public.users
OWNER TO app_admin;

ALTER TABLE public.users OWNER TO app_admin; ALTER TABLE public.orders OWNER TO app_user; ALTER TABLE public.products OWNER TO app_owner;ALTER TABLE public.logs OWNER TO app_dev;

This query transfers the ownership of the 'users' table from its current owner to the 'app_admin' role, an essential function for managing database platform resources.

Q26

Multiple Choice

Identify the correct SQL command that creates a composite index on multiple columns within a table on a PostgreSQL database platform.

SQL Code

CREATE INDEX idx_user_order
ON orders (user_id, order_date);

CREATE INDEX idx_user_order ON orders (user_id, order_date); CREATE INDEX idx_order_date ON orders (order_date); CREATE INDEX idx_product_price ON products (price, product_name);CREATE INDEX idx_user_email ON users (email);

This query creates a composite index on the 'user_id' and 'order_date' columns in the 'orders' table, optimizing query performance on a PostgreSQL database platform.

Q27

Multiple Choice

Which SQL command correctly configures replication settings for a PostgreSQL database platform?

SQL Code

ALTER SYSTEM SET wal_level = replica;
ALTER SYSTEM SET max_wal_senders = 10;
SELECT pg_reload_conf();

ALTER SYSTEM SET wal_level = replica; ALTER SYSTEM SET max_wal_senders = 10; SELECT pg_reload_conf(); ALTER SYSTEM SET wal_level = hot_standby; ALTER SYSTEM SET max_wal_senders = 5; SELECT pg_reload_conf(); ALTER SYSTEM SET wal_level = archive; ALTER SYSTEM SET max_wal_senders = 8; SELECT pg_reload_conf();ALTER SYSTEM SET wal_level = logical; ALTER SYSTEM SET max_wal_senders = 20; SELECT pg_reload_conf();

This sequence of commands sets the WAL level to 'replica' and configures the maximum number of WAL senders to 10, which are necessary settings for enabling replication on a PostgreSQL database platform.

Q28

Multiple Choice

Choose the correct SQL query that sets up a foreign data wrapper in PostgreSQL to interact with an external database platform.

SQL Code

CREATE EXTENSION postgres_fdw;
CREATE SERVER foreign_server
FOREIGN DATA WRAPPER postgres_fdw
OPTIONS (host 'foreign_host', dbname 'foreign_db', port '5432');

CREATE EXTENSION postgres_fdw; CREATE SERVER foreign_server FOREIGN DATA WRAPPER postgres_fdw OPTIONS (host 'foreign_host', dbname 'foreign_db', port '5432'); CREATE SERVER foreign_server FOREIGN DATA WRAPPER mysql_fdw OPTIONS (host 'foreign_host', dbname 'foreign_db', port '3306'); CREATE SERVER external_server FOREIGN DATA WRAPPER oracle_fdw OPTIONS (host 'oracle_host', dbname 'oracle_db', port '1521');CREATE EXTENSION mysql_fdw; CREATE SERVER foreign_server FOREIGN DATA WRAPPER mysql_fdw OPTIONS (host 'foreign_host', dbname 'foreign_db', port '3306');

This sequence of commands installs the 'postgres_fdw' extension, creates a server connection named 'foreign_server' to an external PostgreSQL instance, allowing interaction between different database platforms.

Q29

Multiple Choice

Which SQL command in PostgreSQL sets up a publication for logical replication on a database platform?

SQL Code

CREATE PUBLICATION app_pub
FOR TABLE users, orders;

CREATE PUBLICATION app_pub FOR TABLE users, orders; CREATE PUBLICATION app_pub FOR ALL TABLES; CREATE PUBLICATION app_pub FOR TABLE users;CREATE PUBLICATION app_pub FOR TABLE orders, products;

This query creates a publication named 'app_pub' for logical replication, allowing the 'users' and 'orders' tables to be replicated to other databases or platforms.

Q30

Multiple Choice

Identify the SQL command that correctly configures a PostgreSQL server for connection pooling, an essential feature for database platforms.

SQL Code

ALTER SYSTEM SET max_connections = 100;
ALTER SYSTEM SET shared_buffers = '128MB';
SELECT pg_reload_conf();

ALTER SYSTEM SET max_connections = 100; ALTER SYSTEM SET shared_buffers = '128MB'; SELECT pg_reload_conf(); ALTER SYSTEM SET max_connections = 200; ALTER SYSTEM SET shared_buffers = '256MB'; SELECT pg_reload_conf(); ALTER SYSTEM SET max_connections = 150; ALTER SYSTEM SET shared_buffers = '512MB'; SELECT pg_reload_conf();ALTER SYSTEM SET max_connections = 50; ALTER SYSTEM SET shared_buffers = '64MB'; SELECT pg_reload_conf();

This sequence of commands configures the PostgreSQL server to allow up to 100 connections and sets the shared buffer size to 128MB, which are important settings for enabling efficient connection pooling on a database platform.

PostgreSQL Database Quiz Questions