Test Your PostgreSQL Database Table Knowledge with This Quiz

Progress

True / False

PostgreSQL tables can have primary keys, which uniquely identify each row in the table.

True False

A primary key is a field in a table which uniquely identifies each row/record in that table. PostgreSQL supports primary keys, ensuring that no duplicate values exist and that each value is unique.

True / False

In PostgreSQL, the CHAR and VARCHAR data types are used to store numeric values.

True False

The CHAR and VARCHAR data types are used to store character strings, not numeric values. Numeric values are stored using data types like INTEGER, NUMERIC, etc.

True / False

PostgreSQL supports JSON data types for storing JSON-formatted data.

True False

PostgreSQL supports JSON and JSONB data types, allowing you to store JSON-formatted data in tables and perform operations on it.

True / False

You can create an index on a PostgreSQL table to improve query performance.

True False

Indexes in PostgreSQL can be created on tables to improve the speed of data retrieval operations. They allow the database to find rows more efficiently.

True / False

In PostgreSQL, the FOREIGN KEY constraint enforces a link between the data in two tables.

True False

A FOREIGN KEY in PostgreSQL is a key used to link two tables together, ensuring that the value in the child table corresponds to an actual value in the parent table.

True / False

In PostgreSQL, you cannot alter the structure of an existing table to add or drop columns.

True False

PostgreSQL allows you to modify the structure of existing tables using the ALTER TABLE command to add, drop, or modify columns.

True / False

PostgreSQL supports partitioned tables, which allow you to divide large tables into smaller, more manageable pieces.

True False

Partitioning in PostgreSQL allows a table to be divided into smaller pieces, improving manageability and potentially query performance, especially for large datasets.

True / False

In PostgreSQL, CTID can be used to uniquely identify rows in a table for the duration of a transaction.

True False

CTID is a system column in PostgreSQL that uniquely identifies a row within its table during a specific transaction. However, it can change after a VACUUM operation or if the row is updated.

True / False

PostgreSQL does not support inheritance in tables, where a child table can inherit columns from a parent table.

True False

PostgreSQL supports table inheritance, allowing a child table to inherit columns and constraints from a parent table, providing a way to model hierarchical data.

Q10

True / False

In PostgreSQL, the SERIAL data type can be used to automatically generate unique identifiers for table columns.

True False

The SERIAL data type in PostgreSQL is used to create unique identifiers by automatically generating a sequence of numbers, often used for primary keys.

Q11

Single Choice

Which MySQL statement is used to create a new database table?

SQL Code

_____ table_name (
 column1 datatype,
 column2 datatype,
 column3 datatype
);

ADD CREATE INSERTUPDATE

The CREATE statement is used in MySQL to create a new database table. The other options are related to different operations, like adding data (INSERT), updating data (UPDATE), or adding columns (ADD).

Q12

Single Choice

What is the correct syntax to create a table named Employees with ID as an integer primary key in MySQL?

SQL Code

CREATE TABLE Employees (
 ID INT _____ PRIMARY KEY,
 Name VARCHAR(50)
);

AUTO_INCREMENT UNIQUE INDEXAUTO_INSERT

AUTO_INCREMENT is used in MySQL to automatically generate a unique value for the ID column whenever a new row is inserted into the table. It is typically used for primary keys.

Q13

Single Choice

Which MySQL clause is used to remove a table from the database?

DROP TABLE DELETE TABLE REMOVE TABLECLEAR TABLE

The DROP TABLE clause in MySQL is used to remove an entire table from the database, including all its data and structure. The other options are invalid or not used in MySQL.

Q14

Single Choice

What is the purpose of the IF NOT EXISTS clause when creating a table in MySQL?

SQL Code

CREATE TABLE IF NOT EXISTS Employees (
 ID INT AUTO_INCREMENT PRIMARY KEY,
 Name VARCHAR(50)
);

To create the table only if it doesn't already exist. To ignore the table creation if the table already exists To create the table and replace any existing table with the same name.To check for existing data before creating the table

The IF NOT EXISTS clause in MySQL is used to avoid an error by ensuring that a table is only created if it does not already exist in the database.

Q15

Single Choice

How would you modify an existing table named Employees to add a new column Email with a VARCHAR(100) data type?

SQL Code

ALTER TABLE Employees _____ Email VARCHAR(100);

ADD COLUMN MODIFY COLUMN ALTER COLUMNINSERT COLUMN

The ADD COLUMN clause in the ALTER TABLE statement is used to add a new column to an existing table in MySQL.

Q16

Single Choice

Which of the following MySQL statements can be used to rename an existing table from OldTable to NewTable?

SQL Code

_____ OldTable TO NewTable;

RENAME TABLE ALTER TABLE UPDATE TABLECHANGE TABLE

The RENAME TABLE statement in MySQL is used to change the name of an existing table. The other options either do not exist in MySQL or serve different purposes

Q17

Single Choice

Which statement is used to copy the structure of an existing table named Employees into a new table called EmployeesBackup without copying the data in MySQL?

SQL Code

_____ EmployeesBackup LIKE Employees;

CREATE TABLE CLONE TABLE DUPLICATE TABLECOPY TABLE

The CREATE TABLE ... LIKE statement in MySQL is used to create a new table with the same structure as an existing table, but without copying any of the data.

Q18

Single Choice

How can you ensure that a column EmployeeID in a MySQL table always contains a unique value across all rows?

Set the column as PRIMARY KEY. Set the column as UNIQUE Set the column as AUTO_INCREMENT.All of the above.

Setting a column as PRIMARY KEY, UNIQUE, or AUTO_INCREMENT ensures that the column contains unique values across all rows. In MySQL, AUTO_INCREMENT is often used alongside PRIMARY KEY to automatically generate unique values.

Q19

Single Choice

Which MySQL statement is used to change the data type of an existing column in a table?

SQL Code

ALTER TABLE Employees _____ Name VARCHAR(255);

MODIFY COLUMN ALTER COLUMN CHANGE COLUMNUPDATE COLUMN

The MODIFY COLUMN clause in the ALTER TABLE statement is used to change the data type of an existing column in MySQL.

Q20

Single Choice

In MySQL, how would you drop a column named MiddleName from a table Employees?

SQL Code

_____ Employees DROP COLUMN MiddleName;

ALTER TABLE MODIFY TABLE UPDATE TABLEDELETE COLUMN

The ALTER TABLE statement with the DROP COLUMN clause is used to remove a column from an existing table in MySQL. This operation deletes the column and all its data permanently.

Q21

Multiple Choice

Which of the following SQL queries correctly creates a table with a primary key in PostgreSQL?

SQL Code

CREATE TABLE employees (
 employee_id SERIAL PRIMARY KEY,
 name VARCHAR(100) NOT NULL,
 position VARCHAR(50) NOT NULL,
 salary NUMERIC(10, 2) NOT NULL
);

CREATE TABLE employees ( employee_id SERIAL PRIMARY KEY, name VARCHAR(100) NOT NULL, position VARCHAR(50) NOT NULL, salary NUMERIC(10, 2) NOT NULL ); CREATE TABLE employees ( employee_id INTEGER PRIMARY KEY, name VARCHAR(100), position VARCHAR(50), salary NUMERIC(10, 2) ); CREATE TABLE employees ( employee_id SERIAL, name VARCHAR(100) PRIMARY KEY, position VARCHAR(50), salary NUMERIC(10, 2) );CREATE TABLE employees ( employee_id SERIAL PRIMARY KEY, name VARCHAR(100), position VARCHAR(50), salary NUMERIC(10, 2) );

This query creates an 'employees' table with 'employee_id' as the primary key, which is essential for uniquely identifying each row in the table.

Q22

Multiple Choice

Which SQL command would you use to add a new column to an existing table in PostgreSQL?

SQL Code

ALTER TABLE orders
ADD COLUMN order_status VARCHAR(20) NOT NULL DEFAULT 'pending';

ALTER TABLE orders ADD COLUMN order_status VARCHAR(20) NOT NULL DEFAULT 'pending'; ALTER TABLE orders ADD COLUMN status VARCHAR(20); ALTER TABLE orders ADD order_status VARCHAR(20) NOT NULL DEFAULT 'pending';ALTER TABLE orders ADD COLUMN order_status VARCHAR(20);

This command adds a new column 'order_status' to the 'orders' table with a default value of 'pending'. It’s a common task when modifying the structure of a table.

Q23

Multiple Choice

Identify the SQL statement that correctly deletes a table from a PostgreSQL database.

SQL Code

DROP TABLE IF EXISTS sales;

DROP TABLE IF EXISTS sales; DROP TABLE sales; DELETE TABLE sales;DROP IF EXISTS sales;

This query deletes the 'sales' table if it exists in the database, ensuring that the operation doesn’t fail if the table is already absent.

Q24

Multiple Choice

Which SQL command would you use to update a specific row in a PostgreSQL table?

SQL Code

UPDATE products
SET price = 19.99
WHERE product_id = 101;

UPDATE products SET price = 19.99 WHERE product_id = 101; UPDATE products SET price = 19.99; UPDATE products SET price = 19.99 WHERE product_id = 101 AND category = 'Electronics';UPDATE products SET price = 19.99 WHERE category = 'Electronics';

This query updates the price of the product with 'product_id' 101 to 19.99, demonstrating how to modify specific data within a table.

Q25

Multiple Choice

Which of the following SQL queries would you use to retrieve all rows from a PostgreSQL table?

SQL Code

SELECT * FROM customers;

SELECT * FROM customers; SELECT * FROM customers WHERE customer_id > 100; SELECT name, email FROM customers;SELECT * FROM customers ORDER BY name ASC;

This query selects all columns from the 'customers' table, retrieving every row without any conditions.

Q26

Multiple Choice

Choose the correct SQL command to create a table with a foreign key constraint in PostgreSQL.

SQL Code

CREATE TABLE orders (
 order_id SERIAL PRIMARY KEY,
 customer_id INT REFERENCES customers(customer_id),
 order_date DATE NOT NULL
);

CREATE TABLE orders ( order_id SERIAL PRIMARY KEY, customer_id INT REFERENCES customers(customer_id), order_date DATE NOT NULL ); CREATE TABLE orders ( order_id SERIAL PRIMARY KEY, customer_id INT NOT NULL, order_date DATE NOT NULL ); CREATE TABLE orders ( order_id SERIAL PRIMARY KEY, customer_id INT, order_date DATE NOT NULL );CREATE TABLE orders ( order_id SERIAL PRIMARY KEY, customer_id INT, order_date DATE );

This query creates an 'orders' table where 'customer_id' is a foreign key referencing the 'customer_id' column in the 'customers' table, establishing a relationship between the tables.

Q27

Multiple Choice

Which SQL command is used to rename an existing table in PostgreSQL?

SQL Code

ALTER TABLE customers
RENAME TO clients;

ALTER TABLE customers RENAME TO clients; RENAME TABLE customers TO clients; ALTER TABLE customers CHANGE TO clients;ALTER customers RENAME TO clients;

This command renames the 'customers' table to 'clients', a common operation when the table’s purpose or naming conventions change.

Q28

Multiple Choice

Which SQL query correctly inserts data into a table, including a value for an auto-incrementing column in PostgreSQL?

SQL Code

INSERT INTO employees (name, position, salary)
VALUES ('John Doe', 'Manager', 75000.00)
RETURNING employee_id;

INSERT INTO employees (name, position, salary) VALUES ('John Doe', 'Manager', 75000.00) RETURNING employee_id; INSERT INTO employees (employee_id, name, position, salary) VALUES (DEFAULT, 'John Doe', 'Manager', 75000.00); INSERT INTO employees (name, position, salary) VALUES ('John Doe', 'Manager', 75000.00);INSERT INTO employees (employee_id, name, position, salary) VALUES (NULL, 'John Doe', 'Manager', 75000.00);

This query inserts a new row into the 'employees' table and returns the 'employee_id' of the newly inserted record, which is auto-incremented.

Q29

Multiple Choice

Identify the correct SQL command that adds a check constraint to an existing table in PostgreSQL.

SQL Code

ALTER TABLE products
ADD CONSTRAINT chk_price
CHECK (price > 0);

ALTER TABLE products ADD CONSTRAINT chk_price CHECK (price > 0); ALTER TABLE products ADD CHECK (price > 0); ALTER products ADD CONSTRAINT chk_price CHECK (price > 0);ALTER TABLE products ADD CONSTRAINT CHECK (price > 0);

This command adds a check constraint to the 'products' table to ensure that the 'price' column always has a value greater than zero.

Q30

Multiple Choice

Which SQL query correctly creates a table with a composite primary key in PostgreSQL?

SQL Code

CREATE TABLE order_items (
 order_id INT,
 product_id INT,
 quantity INT NOT NULL,
 PRIMARY KEY (order_id, product_id)
);

CREATE TABLE order_items ( order_id INT, product_id INT, quantity INT NOT NULL, PRIMARY KEY (order_id, product_id) ); CREATE TABLE order_items ( order_id INT PRIMARY KEY, product_id INT, quantity INT NOT NULL ); CREATE TABLE order_items ( order_id INT, product_id INT, quantity INT NOT NULL );CREATE TABLE order_items ( order_id INT, product_id INT, quantity INT NOT NULL, UNIQUE (order_id, product_id) );

This query creates an 'order_items' table where the combination of 'order_id' and 'product_id' serves as the composite primary key, ensuring that each pair is unique.

PostgreSQL Database Quiz Questions