Test Your PostgreSQL INSERT Command Knowledge with This Quiz

Progress

True / False

In PostgreSQL, the INSERT statement is used to add new rows to an existing table.

True False

The INSERT statement in PostgreSQL is used to add new rows of data into an existing table.

True / False

The syntax INSERT INTO table_name VALUES (value1, value2, ...); is used to insert a new row in PostgreSQL.

True False

This is the correct syntax for inserting a new row into a table in PostgreSQL.

True / False

You can insert multiple rows into a PostgreSQL table with a single INSERT INTO statement.

True False

PostgreSQL allows inserting multiple rows using a single INSERT INTO statement by separating the values with commas.

True / False

In PostgreSQL, you must always specify the column names when using the INSERT INTO statement.

True False

Specifying column names is optional if you provide values for all columns in the correct order.

True / False

The RETURNING clause in PostgreSQL can be used with INSERT INTO to return the values of specified columns after the insertion.

True False

The RETURNING clause allows you to return the values of specified columns after an INSERT operation.

True / False

The ON CONFLICT clause in PostgreSQL allows you to handle conflicts that arise when inserting data.

True False

The ON CONFLICT clause can be used to specify alternative actions to take when a conflict occurs during an INSERT.

True / False

In PostgreSQL, the INSERT INTO ... SELECT statement can be used to insert data into a table based on a query.

True False

The INSERT INTO ... SELECT statement allows you to insert data into a table based on the results of a query.

True / False

You cannot use a CTE (Common Table Expression) with an INSERT statement in PostgreSQL.

True False

You can use a CTE (Common Table Expression) with an INSERT statement in PostgreSQL to organize complex queries.

True / False

The INSERT command in PostgreSQL supports the WITH clause for executing multiple INSERT operations in a single statement.

True False

The WITH clause can be used to execute multiple INSERT operations in a single statement.

Q10

True / False

In PostgreSQL, the INSERT statement does not support conditional inserts using the WHERE clause.

True False

The INSERT statement itself does not support the WHERE clause directly for conditional inserts. However, you can achieve conditional inserts by using subqueries or CASE statements within the VALUES clause.

Q11

Single Choice

Which of the following is the correct syntax to insert a new row into a table named employees with columns id, name, and salary in PostgreSQL?

INSERT INTO employees VALUES ('1', 'John Doe', 50000); INSERT INTO employees (id, name, salary) VALUES (1, 'John Doe', 50000); INSERT INTO employees VALUES (id, name, salary) (1, 'John Doe', 50000);INSERT INTO employees (id, name, salary) VALUES ('1', 'John Doe', '50000');

The correct syntax for the INSERT statement in PostgreSQL includes specifying the columns and their corresponding values using the VALUES clause.

Q12

Single Choice

What will happen if you omit a column in the INSERT statement in PostgreSQL that does not have a default value or allows NULLs?

The row will be inserted with the omitted column filled with a NULL value. The row will not be inserted, and PostgreSQL will throw an error. The row will be inserted with the omitted column filled with a default value.The row will be inserted, and the omitted column will be left empty.

PostgreSQL will throw an error if you omit a column in the INSERT statement that does not have a default value or allows NULLs.

Q13

Single Choice

How do you insert multiple rows into a PostgreSQL table using a single INSERT statement?

INSERT INTO employees (id, name, salary) VALUES (1, 'John Doe', 50000), (2, 'Jane Doe', 60000); INSERT INTO employees (id, name, salary) MULTIPLE VALUES (1, 'John Doe', 50000), (2, 'Jane Doe', 60000); INSERT INTO employees (id, name, salary) VALUES (1, 'John Doe', 50000); INSERT INTO employees (id, name, salary) VALUES (2, 'Jane Doe', 60000);INSERT INTO employees (id, name, salary) ADD ROW (1, 'John Doe', 50000), (2, 'Jane Doe', 60000);

PostgreSQL allows the insertion of multiple rows in a single INSERT statement by separating the value sets with commas

Q14

Single Choice

What is the correct way to insert data into a table and return the inserted rows in PostgreSQL?

INSERT INTO employees (id, name, salary) VALUES (1, 'John Doe', 50000) RETURNING id, name, salary; INSERT INTO employees (id, name, salary) VALUES (1, 'John Doe', 50000) RETURNING id, name, salary; INSERT INTO employees (id, name, salary) VALUES (1, 'John Doe', 50000) RETURN id, name, salary;INSERT INTO employees (id, name, salary) VALUES (1, 'John Doe', 50000) RETURN id, name, salary;

The RETURNING clause in PostgreSQL allows you to return the inserted rows immediately after the INSERT statement.

Q15

Single Choice

Which of the following SQL commands will insert a row into the departments table only if the row does not already exist, based on the department_id in PostgreSQL?

INSERT INTO departments (department_id, department_name) VALUES (1, 'HR') ON CONFLICT (department_id) DO NOTHING; INSERT INTO departments (department_id, department_name) VALUES (1, 'HR') ON CONFLICT (department_id) DO UPDATE SET department_name = EXCLUDED.department_name; INSERT INTO departments (department_id, department_name) VALUES (1, 'HR') WHERE NOT EXISTS (SELECT 1 FROM departments WHERE department_id = 1);INSERT INTO departments (department_id, department_name) VALUES (1, 'HR') IF NOT EXISTS;

The ON CONFLICT DO NOTHING clause prevents insertion if the row already exists, based on the specified constraint.

Q16

Single Choice

How can you insert data into a table using a SELECT statement in PostgreSQL?

INSERT INTO employees (id, name, salary) SELECT * FROM temp_employees; INSERT INTO employees (id, name, salary) COPY FROM temp_employees; INSERT INTO employees (id, name, salary) SELECT FROM temp_employees;INSERT INTO employees (id, name, salary) IMPORT temp_employees

PostgreSQL allows you to use a SELECT statement to insert data into a table from another table.

Q17

Single Choice

When using the INSERT INTO ... SELECT statement, which clause can be used to handle conflicts that arise due to duplicate keys in PostgreSQL?

ON CONFLICT ON DUPLICATE KEY ON KEY DUPLICATEON ERROR

The ON CONFLICT clause is used in PostgreSQL to handle conflicts during an insert operation, typically for unique constraints.

Q18

Single Choice

Consider a table products with a unique constraint on the product_code column. Which PostgreSQL statement will insert a new row, or update the existing row if the product_code already exists?

INSERT INTO products (product_code, product_name, price) VALUES ('P001', 'Laptop', 1200) ON CONFLICT (product_code) DO UPDATE SET product_name = EXCLUDED.product_name, price = EXCLUDED.price; INSERT INTO products (product_code, product_name, price) VALUES ('P001', 'Laptop', 1200) ON DUPLICATE KEY UPDATE product_name = VALUES(product_name), price = VALUES(price); INSERT INTO products (product_code, product_name, price) VALUES ('P001', 'Laptop', 1200) ON ERROR UPDATE product_name = EXCLUDED.product_name, price = EXCLUDED.price;INSERT INTO products (product_code, product_name, price) VALUES ('P001', 'Laptop', 1200) ON CONFLICT UPDATE product_name = EXCLUDED.product_name, price = EXCLUDED.price;

The ON CONFLICT clause with DO UPDATE is used to update the existing row if a conflict arises due to a unique constraint.

Q19

Single Choice

How can you insert a row into the orders table, ensuring that the order_id is automatically generated, but also include a custom value for it using PostgreSQL's INSERT command?

INSERT INTO orders (order_id, customer_id, total) VALUES (DEFAULT, 101, 500); INSERT INTO orders (order_id, customer_id, total) VALUES (AUTO, 101, 500); INSERT INTO orders (order_id, customer_id, total) VALUES (NULL, 101, 500);INSERT INTO orders (order_id, customer_id, total) VALUES (SERIAL, 101, 500);

The DEFAULT keyword is used in PostgreSQL to automatically generate a value for the column, such as from a sequence or a default expression.

Q20

Single Choice

In PostgreSQL, which of the following is the correct approach to insert data from one table into another and handle potential conflicts by ignoring the conflict but returning the rows that were successfully inserted?

INSERT INTO target_table SELECT * FROM source_table ON CONFLICT DO NOTHING RETURNING *; INSERT INTO target_table SELECT * FROM source_table ON DUPLICATE KEY DO NOTHING RETURNING *; INSERT INTO target_table SELECT * FROM source_table ON ERROR DO NOTHING RETURNING *;INSERT INTO target_table SELECT * FROM source_table ON DUPLICATE RETURNING *;

The ON CONFLICT DO NOTHING RETURNING * syntax in PostgreSQL allows you to insert data and ignore conflicts while returning the rows that were successfully inserted.

Q21

Multiple Choice

Which SQL command inserts a new row into the 'employees' table with all column values provided?

SQL Code

INSERT INTO employees (employee_id, first_name, last_name, department_id)
VALUES (1, 'John', 'Doe', 101);

INSERT INTO employees (employee_id, first_name, last_name, department_id) VALUES (1, 'John', 'Doe', 101); INSERT INTO employees (first_name, last_name, department_id) VALUES ('John', 'Doe', 101); INSERT INTO employees VALUES (1, 'John', 'Doe', 101);INSERT INTO employees (employee_id, first_name, last_name) VALUES (1, 'John', 'Doe');

This command inserts a new row into the 'employees' table, specifying values for all columns listed in the query.

Q22

Multiple Choice

Which SQL command inserts a new row with only some column values provided, using DEFAULT for others?

SQL Code

INSERT INTO orders (order_id, order_date, customer_id)
VALUES (1, CURRENT_DATE, DEFAULT);

INSERT INTO orders (order_id, order_date, customer_id) VALUES (1, CURRENT_DATE, DEFAULT); INSERT INTO orders (order_id, order_date) VALUES (1, CURRENT_DATE); INSERT INTO orders VALUES (1, CURRENT_DATE, DEFAULT);INSERT INTO orders (order_id, customer_id) VALUES (1, DEFAULT);

This command inserts a new row into the 'orders' table, specifying values for 'order_id' and 'order_date', while using the default value for 'customer_id'.

Q23

Multiple Choice

Which SQL command inserts multiple rows into the 'products' table in a single query?

SQL Code

INSERT INTO products (product_id, product_name, price)
VALUES (1, 'Laptop', 999.99),
 (2, 'Tablet', 499.99);

INSERT INTO products (product_id, product_name, price) VALUES (1, 'Laptop', 999.99), (2, 'Tablet', 499.99); INSERT INTO products (product_id, product_name) VALUES (1, 'Laptop'), (2, 'Tablet'); INSERT INTO products (product_name, price) VALUES ('Laptop', 999.99), ('Tablet', 499.99);INSERT INTO products (product_id, product_name, price) VALUES (1, 'Laptop', 999.99); INSERT INTO products (product_id, product_name, price) VALUES (2, 'Tablet', 499.99);

This command inserts two rows into the 'products' table with a single query, specifying values for 'product_id', 'product_name', and 'price'.

Q24

Multiple Choice

Which SQL command uses the RETURNING clause to get the inserted row's primary key value?

SQL Code

INSERT INTO customers (first_name, last_name)
VALUES ('Jane', 'Doe')
RETURNING customer_id;

INSERT INTO customers (first_name, last_name) VALUES ('Jane', 'Doe') RETURNING customer_id; INSERT INTO customers (first_name, last_name) VALUES ('Jane', 'Doe'); INSERT INTO customers (first_name, last_name) VALUES ('Jane', 'Doe') RETURNING *;INSERT INTO customers (first_name, last_name) VALUES ('Jane', 'Doe') RETURNING last_name;

This command inserts a new row into the 'customers' table and returns the 'customer_id' of the newly inserted row.

Q25

Multiple Choice

Which SQL command uses the ON CONFLICT clause to handle duplicate keys by doing nothing?

SQL Code

INSERT INTO users (user_id, username)
VALUES (1, 'jdoe')
ON CONFLICT (user_id) DO NOTHING;

INSERT INTO users (user_id, username) VALUES (1, 'jdoe') ON CONFLICT (user_id) DO NOTHING; INSERT INTO users (user_id, username) VALUES (1, 'jdoe') ON CONFLICT DO NOTHING; INSERT INTO users (user_id, username) VALUES (1, 'jdoe');INSERT INTO users (user_id, username) VALUES (1, 'jdoe') ON CONFLICT (username) DO NOTHING;

This command attempts to insert a new row into the 'users' table but does nothing if a row with the same 'user_id' already exists.

Q26

Multiple Choice

Which SQL command inserts a new row into a table with a JSONB column and assigns default values to missing fields?

SQL Code

INSERT INTO settings (config_id, config_data)
VALUES (1, '{"theme": "dark"}')
ON CONFLICT (config_id)
DO UPDATE SET config_data = jsonb_set(settings.config_data, '{theme}', '"dark"', true);

INSERT INTO settings (config_id, config_data) VALUES (1, '{"theme": "dark"}') ON CONFLICT (config_id) DO UPDATE SET config_data = jsonb_set(settings.config_data, '{theme}', '"dark"', true); INSERT INTO settings (config_id, config_data) VALUES (1, '{"theme": "dark"}') ON CONFLICT (config_id) DO UPDATE SET config_data = jsonb_set(config_data, '{theme}', '"dark"', true); INSERT INTO settings (config_id, config_data) VALUES (1, '{"theme": "dark"}') ON CONFLICT (config_id) DO UPDATE SET config_data = '{"theme": "dark"}';INSERT INTO settings (config_id, config_data) VALUES (1, '{"theme": "dark"}') ON CONFLICT (config_data) DO NOTHING;

This command inserts a row into the 'settings' table with a JSONB column. If a conflict occurs on 'config_id', it updates the 'theme' field to 'dark'.

Q27

Multiple Choice

Which SQL command inserts a row and returns the inserted row's entire data?

SQL Code

INSERT INTO employees (first_name, last_name, department_id)
VALUES ('Alice', 'Smith', 101)
RETURNING *;

INSERT INTO employees (first_name, last_name, department_id) VALUES ('Alice', 'Smith', 101) RETURNING *; INSERT INTO employees (first_name, last_name, department_id) VALUES ('Alice', 'Smith', 101) RETURNING employee_id; INSERT INTO employees (first_name, last_name) VALUES ('Alice', 'Smith') RETURNING *;INSERT INTO employees (first_name, last_name, department_id) VALUES ('Alice', 'Smith', 101);

This command inserts a new row into the 'employees' table and returns all columns of the newly inserted row.

Q28

Multiple Choice

Which SQL command performs an INSERT with a subquery that selects data from another table?

SQL Code

INSERT INTO sales (product_id, sale_date, quantity)
SELECT product_id, CURRENT_DATE, 1 FROM products WHERE product_name = 'Laptop';

INSERT INTO sales (product_id, sale_date, quantity) SELECT product_id, CURRENT_DATE, 1 FROM products WHERE product_name = 'Laptop'; INSERT INTO sales (product_id, sale_date, quantity) VALUES ((SELECT product_id FROM products WHERE product_name = 'Laptop'), CURRENT_DATE, 1); INSERT INTO sales SELECT product_id, CURRENT_DATE, 1 FROM products WHERE product_name = 'Laptop';INSERT INTO sales (product_id, sale_date, quantity) SELECT product_id, CURRENT_DATE, 1 FROM products;

This command inserts a new row into the 'sales' table by selecting data from the 'products' table, specifically for products named 'Laptop'.

Q29

Multiple Choice

Which SQL command uses the INSERT...ON CONFLICT clause to handle duplicate keys by updating the conflicting row?

SQL Code

INSERT INTO inventory (product_id, stock)
VALUES (1, 100)
ON CONFLICT (product_id)
DO UPDATE SET stock = EXCLUDED.stock + inventory.stock;

INSERT INTO inventory (product_id, stock) VALUES (1, 100) ON CONFLICT (product_id) DO UPDATE SET stock = EXCLUDED.stock + inventory.stock; INSERT INTO inventory (product_id, stock) VALUES (1, 100) ON CONFLICT (product_id) DO NOTHING; INSERT INTO inventory (product_id, stock) VALUES (1, 100) ON CONFLICT (stock) DO UPDATE SET stock = EXCLUDED.stock + inventory.stock;INSERT INTO inventory (product_id, stock) VALUES (1, 100) ON CONFLICT (product_id) DO UPDATE SET stock = stock + inventory.stock;

This command attempts to insert a new row into the 'inventory' table. If a conflict occurs on 'product_id', it updates the stock by adding the existing stock with the new value.

Q30

Multiple Choice

Which SQL command performs an INSERT with data returned from a CTE (Common Table Expression) in PostgreSQL?

SQL Code

WITH new_products AS (
 SELECT product_id, product_name FROM products WHERE product_id > 100
)
INSERT INTO sales (product_id, sale_date)
SELECT product_id, CURRENT_DATE FROM new_products;

WITH new_products AS ( SELECT product_id, product_name FROM products WHERE product_id > 100 ) INSERT INTO sales (product_id, sale_date) SELECT product_id, CURRENT_DATE FROM new_products; WITH new_products AS ( SELECT product_id FROM products WHERE product_id > 100 ) INSERT INTO sales (product_id, sale_date) SELECT product_id, CURRENT_DATE FROM new_products; WITH new_products AS ( SELECT product_id, product_name FROM products WHERE product_id > 100 ) INSERT INTO sales (product_id, sale_date) SELECT product_id, CURRENT_DATE;WITH new_products AS ( SELECT product_id, product_name FROM products WHERE product_id > 100 ) INSERT INTO sales (product_id, sale_date) SELECT product_name, CURRENT_DATE FROM new_products;

This command uses a CTE (new_products) to select data from the 'products' table and then inserts the selected data into the 'sales' table.

PostgreSQL Database Quiz Questions