Test Your PostgreSQL SQL Knowledge with This Quiz

Progress

True / False

SQL stands for Structured Query Language.

True False

SQL is an acronym for Structured Query Language, which is used for managing and manipulating relational databases.

True / False

SQL is a programming language designed for querying and modifying databases.

True False

SQL is specifically designed for querying, updating, and managing data in relational database management systems (RDBMS), including PostgreSQL.

True / False

SQL cannot be used to delete data from a database.

True False

SQL includes the DELETE statement, which is used to remove records from a table in a database.

True / False

SQL commands are divided into categories such as DDL, DML, DCL, and TCL.

True False

SQL commands are categorized into Data Definition Language (DDL), Data Manipulation Language (DML), Data Control Language (DCL), and Transaction Control Language (TCL) to organize their different functions.

True / False

In PostgreSQL, SQL is used to create and manage tables.

True False

SQL in PostgreSQL can be used to create, alter, and drop tables, as well as define their structure using DDL commands like CREATE TABLE and ALTER TABLE.

True / False

SQL is case-sensitive in PostgreSQL, meaning that SELECT and select are treated differently.

True False

SQL is generally case-insensitive in PostgreSQL, so SELECT and select are treated the same. However, identifiers like table and column names are case-sensitive unless quoted.

True / False

SQL in PostgreSQL can be used to perform complex transactions that include multiple statements executed as a single unit of work.

True False

PostgreSQL supports transactions, allowing multiple SQL statements to be executed as a single unit using BEGIN, COMMIT, and ROLLBACK.

True / False

SQL functions in PostgreSQL can include control-of-flow statements such as IF and LOOP.

True False

SQL functions in PostgreSQL support procedural extensions (PL/pgSQL) that include control-of-flow statements like IF, LOOP, WHILE, etc.

True / False

SQL in PostgreSQL does not support recursive queries.

True False

PostgreSQL supports recursive queries using Common Table Expressions (CTEs) with the WITH RECURSIVE clause, allowing queries to reference their own output.

Q10

True / False

In PostgreSQL, the EXPLAIN statement in SQL is used to display the execution plan of a query.

True False

The EXPLAIN statement in PostgreSQL is used to show the execution plan of a query, providing insights into how the database will execute the query and helping with performance optimization.

Q11

Single Choice

Which SQL command is used to retrieve data from a MySQL database?

INSERT SELECT UPDATEDELETE

The SELECT statement is used to query the database and retrieve data from one or more tables.

Q12

Single Choice

What does the DISTINCT keyword do in a MySQL SELECT statement?

Removes duplicate rows from the result set Sorts the result set Groups the result setFilters the result set based on a condition

The DISTINCT keyword is used to eliminate duplicate rows in the result set, returning only unique values.

Q13

Single Choice

Which of the following SQL statements is used to update data in a MySQL table?

ALTER UPDATE MODIFYCHANGE

The UPDATE statement is used to modify existing data in a table.

Q14

Single Choice

Which of the following SQL queries will return the total number of rows in the employees table?

SELECT COUNT(*) FROM employees; SELECT SUM(*) FROM employees; SELECT MAX(*) FROM employees;SELECT AVG(*) FROM employees;

The COUNT(*) function returns the total number of rows in a table, including those with NULL values.

Q15

Single Choice

How do you select all records from the customers table where the city column is either 'New York' or 'Los Angeles'?

SELECT * FROM customers WHERE city IN ('New York', 'Los Angeles'); SELECT * FROM customers WHERE city = 'New York' AND 'Los Angeles'; SELECT * FROM customers WHERE city IS 'New York' OR 'Los Angeles';SELECT * FROM customers WHERE city LIKE 'New York, Los Angeles';

The IN operator allows you to specify multiple values in a WHERE clause, making it easy to match any of them.

Q16

Single Choice

What is the correct SQL syntax to create a MySQL table named orders with an id column that auto-increments?

CREATE TABLE orders (id INT AUTO_INCREMENT PRIMARY KEY); CREATE TABLE orders (id INT PRIMARY AUTO_INCREMENT); CREATE orders TABLE (id INT AUTO_INCREMENT PRIMARY);CREATE orders (id INT AUTO_INCREMENT PRIMARY);

The correct syntax to create a table with an auto-incrementing primary key column is AUTO_INCREMENT PRIMARY KEY.

Q17

Single Choice

Which SQL query would you use to find the names of employees who have the highest salary in the employees table?

SELECT name FROM employees WHERE salary = (SELECT MAX(salary) FROM employees); SELECT name FROM employees WHERE salary = MAX(salary); SELECT name FROM employees WHERE salary > ALL (SELECT salary FROM employees);SELECT name FROM employees WHERE salary = (SELECT MAX(salary) FROM employees) GROUP BY name;

This query uses a subquery to find the maximum salary and then selects the employee(s) who earn that salary.

Q18

Single Choice

What is the correct SQL query to return the average salary of all employees grouped by department in the employees table?

SELECT department_id, AVG(salary) FROM employees GROUP BY department_id; SELECT department_id, AVG(salary) FROM employees HAVING department_id; SELECT department_id, SUM(salary) FROM employees WHERE department_id;SELECT department_id, AVG(salary) FROM employees GROUP department_id;

The GROUP BY clause is used to group rows that have the same values in specified columns into aggregate data such as AVG.

Q19

Single Choice

How do you modify a MySQL table to add a new column age with data type INT to an existing customers table?

ALTER TABLE customers ADD COLUMN age INT; MODIFY TABLE customers ADD age INT; CHANGE TABLE customers ADD COLUMN age INT;ALTER TABLE customers ADD age INT PRIMARY KEY;

The ALTER TABLE statement is used to add, delete, or modify columns in an existing table.

Q20

Single Choice

You need to retrieve the second highest salary from the employees table in MySQL. Which SQL query would you use?

SELECT MAX(salary) FROM employees WHERE salary < (SELECT MAX(salary) FROM employees); SELECT DISTINCT(salary) FROM employees ORDER BY salary DESC LIMIT 1,1; SELECT salary FROM (SELECT salary FROM employees ORDER BY salary DESC LIMIT 2) AS Temp ORDER BY salary LIMIT 1;SELECT salary FROM employees ORDER BY salary LIMIT 1 OFFSET 1;

This query uses a subquery to find the maximum salary that is less than the highest salary, effectively returning the second highest salary.

Q21

Multiple Choice

Which SQL query retrieves all columns for rows where the column 'status' is 'active' in a PostgreSQL table?

SQL Code

SELECT *
FROM employees
WHERE status = 'active';

SELECT * FROM employees WHERE status = 'active'; SELECT * FROM employees WHERE status = 'inactive'; SELECT status FROM employees WHERE status = 'active';SELECT * FROM employees WHERE status != 'active';

This query selects all columns from the 'employees' table where the 'status' column has the value 'active', a common SQL operation for filtering rows.

Q22

Multiple Choice

Which SQL command adds a new column 'date_of_birth' to an existing PostgreSQL table 'customers'?

SQL Code

ALTER TABLE customers
ADD COLUMN date_of_birth DATE;

ALTER TABLE customers ADD COLUMN date_of_birth DATE; ALTER TABLE customers ADD date_of_birth DATE; ALTER customers ADD COLUMN date_of_birth DATE;ALTER TABLE customers ADD COLUMN date_of_birth TIMESTAMP;

This command adds a new column 'date_of_birth' of type DATE to the 'customers' table, which is often required when updating the table's schema.

Q23

Multiple Choice

Which SQL query correctly updates a row's column value in a PostgreSQL table?

SQL Code

UPDATE orders
SET delivery_date = '2024-08-01'
WHERE order_id = 1001;

UPDATE orders SET delivery_date = '2024-08-01' WHERE order_id = 1001; UPDATE orders SET delivery_date = '2024-08-01' WHERE order_id = 1000; UPDATE orders SET delivery_date = '2024-08-01';UPDATE orders SET delivery_date = '2024-08-01' WHERE order_id > 1000;

This query updates the 'delivery_date' column to '2024-08-01' for the row where 'order_id' equals 1001, demonstrating how to modify specific data within a table.

Q24

Multiple Choice

Which SQL command is used to remove a specific row from a PostgreSQL table?

SQL Code

DELETE FROM customers
WHERE customer_id = 123;

DELETE FROM customers WHERE customer_id = 123; DELETE FROM customers WHERE customer_id > 100; DELETE customer_id = 123 FROM customers;DELETE FROM customers;

This command deletes a row from the 'customers' table where the 'customer_id' equals 123, commonly used to manage and clean up data.

Q25

Multiple Choice

Which SQL query retrieves only distinct values from the 'department' column in a PostgreSQL table?

SQL Code

SELECT DISTINCT department
FROM employees;

SELECT DISTINCT department FROM employees; SELECT department FROM employees GROUP BY department; SELECT department FROM employees;SELECT DISTINCT department, COUNT(*) FROM employees;

This query selects distinct values from the 'department' column in the 'employees' table, which is useful for identifying unique entries.

Q26

Multiple Choice

Which SQL command adds a NOT NULL constraint to an existing column in a PostgreSQL table?

SQL Code

ALTER TABLE employees
ALTER COLUMN email SET NOT NULL;

ALTER TABLE employees ALTER COLUMN email SET NOT NULL; ALTER TABLE employees ADD CONSTRAINT email_not_null CHECK (email IS NOT NULL); ALTER TABLE employees MODIFY COLUMN email SET NOT NULL;ALTER TABLE employees ALTER email SET NOT NULL;

This command adds a NOT NULL constraint to the 'email' column in the 'employees' table, ensuring that this column cannot have NULL values.

Q27

Multiple Choice

Which SQL command renames an existing column in a PostgreSQL table?

SQL Code

ALTER TABLE customers
RENAME COLUMN full_name TO name;

ALTER TABLE customers RENAME COLUMN full_name TO name; ALTER TABLE customers CHANGE COLUMN full_name TO name; ALTER TABLE customers MODIFY COLUMN full_name TO name;ALTER customers RENAME full_name TO name;

This command renames the 'full_name' column to 'name' in the 'customers' table, a common task when modifying the structure of a table.

Q28

Multiple Choice

Which SQL query retrieves rows where a column has NULL values in PostgreSQL?

SQL Code

SELECT * FROM orders
WHERE delivery_date IS NULL;

SELECT * FROM orders WHERE delivery_date IS NULL; SELECT * FROM orders WHERE delivery_date = NULL; SELECT * FROM orders WHERE delivery_date IS NOT NULL;SELECT * FROM orders WHERE delivery_date != NULL;

This query selects all rows from the 'orders' table where the 'delivery_date' column has NULL values, which is useful for identifying incomplete data.

Q29

Multiple Choice

Which SQL command adds a new check constraint to a PostgreSQL table to ensure a column's value is positive?

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 TABLE products ADD CONSTRAINT CHECK (price > 0);ALTER products ADD CONSTRAINT chk_price CHECK (price > 0);

This command adds a check constraint to the 'products' table, ensuring that the 'price' column always contains positive values.

Q30

Multiple Choice

Which SQL query retrieves the total number of rows in a PostgreSQL table?

SQL Code

SELECT COUNT(*)
FROM products;

SELECT COUNT(*) FROM products; SELECT COUNT(product_id) FROM products; SELECT COUNT(*) FROM products WHERE price > 0;SELECT * FROM products;

This query counts and returns the total number of rows in the 'products' table, which is useful for understanding the size of the table.

PostgreSQL Database Quiz Questions