Test Your PostgreSQL NOT IN Operator Knowledge with This Quiz

Progress

True / False

In PostgreSQL, the NOT IN keyword is used to filter rows where a column's value does not match any value in a specified list.

True False

The NOT IN keyword filters rows where the column's value is not included in the specified list of values.

True / False

In PostgreSQL, the NOT IN keyword can only be used with numeric values.

True False

The NOT IN keyword can be used with numeric, text, date, and other data types.

True / False

In PostgreSQL, using NOT IN with an empty list will always return all rows from the table.

True False

An empty list with NOT IN logically means no values are excluded, so all rows are returned.

True / False

In PostgreSQL, the NOT IN keyword is case-sensitive when used with text values.

True False

PostgreSQL’s NOT IN keyword performs case-sensitive comparisons for text values, similar to other SQL comparisons.

True / False

In PostgreSQL, the NOT IN keyword can be used with subqueries to specify a dynamic list of values.

True False

The NOT IN keyword can be used with subqueries, allowing for dynamic value lists based on the result of the subquery.

True / False

In PostgreSQL, using NOT IN with a subquery that returns NULL values will exclude rows where the column's value is NULL.

True False

If a subquery used with NOT IN returns NULL, the condition will not match any rows because NULL comparisons yield unknown results.

True / False

In PostgreSQL, using the NOT IN keyword with a large list of values can impact query performance.

True False

A large list of values in a NOT IN clause can degrade query performance, especially if the list is not indexed or optimized.

True / False

In PostgreSQL, the NOT IN keyword can be used within a JOIN condition.

True False

The NOT IN keyword can be used within a JOIN condition to exclude rows based on a list of values.

True / False

In PostgreSQL, the NOT IN keyword can be combined with other logical operators like AND and OR.

True False

The NOT IN keyword can be combined with logical operators to create complex filtering conditions.

Q10

True / False

In PostgreSQL, using NOT IN with a subquery that includes NULL will ensure the query returns only non-null values.

True False

Using NOT IN with a subquery that includes NULL will cause the entire NOT IN condition to fail for all rows because NULL comparisons are treated as unknown, which results in no rows being returned.

Q11

Single Choice

Which of the following SQL statements will return all employees not working in departments 10, 20, or 30?

SQL Code

SELECT employee_id, employee_name
FROM employees
WHERE department_id NOT IN (10, 20, 30);

Only employees from departments 10, 20, or 30 Employees not from departments 10, 20, or 30 All employeesNone of the above

The NOT IN operator filters out the employees working in departments 10, 20, or 30, returning only those who are in other departments.

Q12

Single Choice

What will the following PostgreSQL query return?

SQL Code

SELECT product_name
FROM products
WHERE category_id NOT IN (1, 2, 3);

Products in categories 1, 2, or 3 Products not in categories 1, 2, or 3 Products in categories other than 1, 2, or 3None of the above

The NOT IN operator ensures that only products from categories other than 1, 2, or 3 are selected.

Q13

Single Choice

In PostgreSQL, which statement correctly finds orders that are not from customers 1001, 1002, and 1003?

SQL Code

SELECT order_id
FROM orders
WHERE customer_id NOT IN (1001, 1002, 1003);

Orders from customers 1001, 1002, and 1003 Orders not from customers 1001, 1002, and 1003 Orders from all customersNone of the above

The query uses NOT IN to exclude orders from the specified customer IDs.

Q14

Single Choice

What is the output of the following query in PostgreSQL if the table contains NULL values in the region_id column?

SQL Code

SELECT region_name
FROM regions
WHERE region_id NOT IN (1, 2, 3);

Regions with IDs 1, 2, or 3 Regions with IDs other than 1, 2, or 3, excluding NULLs Regions with IDs other than 1, 2, or 3, including NULLsNo rows returned if there are NULLs

The NOT IN operator will return no rows if the column being checked contains NULLs because NULLs are not comparable using NOT IN.

Q15

Single Choice

Given the following SQL statement, what does it return?

SQL Code

SELECT employee_id
FROM employees
WHERE salary NOT IN (5000, 7000, 10000);

Employees with salary 5000, 7000, or 10000 Employees with salary other than 5000, 7000, or 10000 All employeesNone of the above

The query selects employees whose salary is not equal to 5000, 7000, or 10000.

Q16

Single Choice

What will the following PostgreSQL query return if the supplier_id column has duplicate values?

SQL Code

SELECT supplier_name
FROM suppliers
WHERE supplier_id NOT IN (101, 102, 103);

Suppliers with IDs 101, 102, or 103 Suppliers not with IDs 101, 102, or 103 All suppliersNone of the above

The NOT IN operator filters out rows where supplier_id is 101, 102, or 103, regardless of duplicates.

Q17

Single Choice

How does PostgreSQL handle a query using NOT IN when the subquery returns NULL?

SQL Code

SELECT customer_id
FROM customers
WHERE customer_id NOT IN (SELECT customer_id FROM orders WHERE order_date IS NULL);

Returns all customer_ids Returns customer_ids with non-NULL order dates Returns an empty setNone of the above

The NOT IN operator will return an empty set if the subquery returns NULL because NULLs make the entire comparison unknown.

Q18

Single Choice

What is the effect of using NOT IN in a correlated subquery in PostgreSQL?

SQL Code

SELECT employee_id
FROM employees e
WHERE department_id NOT IN (
 SELECT department_id
 FROM departments d
 WHERE e.location_id = d.location_id
);

Selects employees in departments from different locations Selects employees in departments with matching locations Selects employees not in departments with matching locationsNone of the above

The query checks if the department_id of an employee's department is not present in departments located in the same location as the employee.

Q19

Single Choice

In PostgreSQL, what will the following SQL query return if there are no matching product_ids in the sales table?

SQL Code

SELECT product_name
FROM products
WHERE product_id NOT IN (SELECT product_id FROM sales);

All products Products with no sales No productsNone of the above

The NOT IN operator filters out products that have sales, returning only those products with no matching product_id in the sales table.

Q20

Single Choice

Which of the following PostgreSQL queries correctly finds customers who have made no purchases in the last year?

SQL Code

SELECT customer_id
FROM customers
WHERE customer_id NOT IN (
 SELECT customer_id
 FROM orders
 WHERE order_date >= CURRENT_DATE - INTERVAL '1 year'
);

Customers who made purchases in the last year Customers who made no purchases in the last year All customersNone of the above

The query uses the NOT IN operator to exclude customers who have made purchases in the last year, returning only those who have not made any purchases during this period.

Q21

Multiple Choice

Identify the SQL scripts that correctly use the NOT IN operator to exclude specific numeric values in PostgreSQL.

SELECT * FROM employees WHERE department_id NOT IN (4, 5, 6); SELECT * FROM employees WHERE department_id <> 4 AND department_id <> 5 AND department_id <> 6; SELECT * FROM employees WHERE department_id NOT IN ('4', '5', '6');SELECT * FROM employees WHERE department_id NOT IN (4 AND 5 AND 6);

The NOT IN operator is used to exclude specific values from the result set. The correct scripts will demonstrate how to use the NOT IN operator with numeric values in PostgreSQL.

Q22

Multiple Choice

Select the SQL scripts that correctly use the NOT IN operator with string values in PostgreSQL.

SELECT * FROM customers WHERE state NOT IN ('CA', 'NY', 'TX'); SELECT * FROM customers WHERE state <> 'CA' AND state <> 'NY' AND state <> 'TX'; SELECT * FROM customers WHERE state NOT IN (CA, NY, TX);SELECT * FROM customers WHERE NOT state IN ('CA', 'NY', 'TX');

The NOT IN operator can be used to exclude specific string values from the result set. The correct scripts will demonstrate how to use the NOT IN operator with string values in PostgreSQL.

Q23

Multiple Choice

Determine the SQL scripts that correctly use the NOT IN operator with subqueries in PostgreSQL.

SELECT * FROM employees WHERE department_id NOT IN (SELECT department_id FROM departments WHERE location_id = 10); SELECT * FROM employees WHERE department_id NOT IN (SELECT department_id FROM departments WHERE location_id <> 10); SELECT * FROM employees WHERE department_id <> ALL (SELECT department_id FROM departments WHERE location_id = 10);SELECT * FROM employees WHERE department_id NOT IN (SELECT department_id FROM departments WHERE location_id = 10) AND department_id IS NOT NULL;

The NOT IN operator can be used with subqueries to exclude results based on the results of another query. The correct scripts will demonstrate how to use the NOT IN operator with subqueries in PostgreSQL.

Q24

Multiple Choice

Identify the SQL scripts that correctly use the NOT IN operator with JOINs in PostgreSQL.

SELECT * FROM orders WHERE customer_id NOT IN (SELECT customer_id FROM customers); SELECT * FROM orders WHERE NOT EXISTS (SELECT 1 FROM customers WHERE customers.customer_id = orders.customer_id); SELECT * FROM orders o LEFT JOIN customers c ON o.customer_id = c.customer_id WHERE c.customer_id IS NULL;SELECT * FROM orders WHERE customer_id IN (SELECT customer_id FROM customers);

The NOT IN operator can be used with JOINs to exclude results from multiple tables. The correct scripts will demonstrate how to use the NOT IN operator with JOINs in PostgreSQL.

Q25

Multiple Choice

Select the SQL scripts that correctly use the NOT IN operator with multiple columns in PostgreSQL.

SELECT * FROM products WHERE (category_id, supplier_id) NOT IN (SELECT category_id, supplier_id FROM suppliers); SELECT * FROM products WHERE category_id NOT IN (SELECT category_id FROM suppliers) AND supplier_id NOT IN (SELECT supplier_id FROM suppliers); SELECT * FROM products WHERE (category_id, supplier_id) <> ALL (SELECT category_id, supplier_id FROM suppliers);SELECT * FROM products WHERE (category_id, supplier_id) NOT IN (SELECT category_id, supplier_id FROM suppliers WHERE category_id IS NOT NULL);

The NOT IN operator can be used with multiple columns by combining columns in a subquery. The correct scripts will demonstrate how to use the NOT IN operator with multiple columns in PostgreSQL.

Q26

Multiple Choice

Determine the SQL scripts that correctly use the NOT IN operator with window functions in PostgreSQL.

SELECT employee_id, department_id, RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS rank FROM employees WHERE department_id NOT IN (1, 2, 3); SELECT employee_id, department_id, DENSE_RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS rank FROM employees WHERE department_id NOT IN (1, 2, 3); SELECT employee_id, department_id, ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY salary DESC) AS rank FROM employees WHERE department_id NOT IN (1, 2, 3);SELECT employee_id, department_id, RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS rank FROM employees WHERE department_id NOT IN (4, 5, 6);

The NOT IN operator can be used with window functions to exclude and rank results within specific partitions. The correct scripts will demonstrate how to use the NOT IN operator with window functions in PostgreSQL.

Q27

Multiple Choice

Identify the SQL scripts that correctly use the NOT IN operator with EXISTS in PostgreSQL.

SELECT * FROM employees WHERE employee_id NOT IN (SELECT manager_id FROM managers WHERE EXISTS (SELECT 1 FROM managers WHERE managers.manager_id = employees.employee_id)); SELECT * FROM employees WHERE NOT EXISTS (SELECT 1 FROM managers WHERE managers.manager_id = employees.employee_id); SELECT * FROM employees WHERE employee_id NOT IN (SELECT manager_id FROM managers);SELECT * FROM employees WHERE employee_id = ALL (SELECT manager_id FROM managers WHERE EXISTS (SELECT 1 FROM managers WHERE managers.manager_id = employees.employee_id));

The NOT IN operator can be combined with EXISTS to exclude results based on the existence of rows in a subquery. The correct scripts will demonstrate how to use the NOT IN operator with EXISTS in PostgreSQL.

Q28

Multiple Choice

Determine the SQL scripts that correctly use the NOT IN operator with UNION in PostgreSQL.

SELECT employee_id, department_id FROM employees WHERE department_id NOT IN (10, 20, 30) UNION SELECT contractor_id, department_id FROM contractors WHERE department_id NOT IN (10, 20, 30); SELECT employee_id, department_id FROM employees WHERE department_id <> ALL (10, 20, 30) UNION SELECT contractor_id, department_id FROM contractors WHERE department_id <> ALL (10, 20, 30); SELECT employee_id, department_id FROM employees WHERE department_id NOT IN (10, 20, 30) UNION SELECT contractor_id, department_id FROM contractors WHERE department_id NOT IN (10, 20);SELECT employee_id, department_id FROM employees WHERE department_id NOT IN (10, 20, 30) UNION ALL SELECT contractor_id, department_id FROM contractors WHERE department_id NOT IN (10, 20, 30);

The NOT IN operator can be used with UNION to exclude and combine results from multiple tables. The correct scripts will demonstrate how to use the NOT IN operator with UNION in PostgreSQL.

Q29

Multiple Choice

Identify the SQL scripts that correctly use the NOT IN operator with DISTINCT in PostgreSQL.

SELECT DISTINCT department_id FROM employees WHERE department_id NOT IN (100, 200, 300); SELECT DISTINCT department_id FROM employees WHERE department_id = ALL (100, 200, 300); SELECT department_id FROM (SELECT DISTINCT department_id FROM employees) AS sub WHERE department_id NOT IN (100, 200, 300);SELECT DISTINCT department_id FROM employees WHERE department_id NOT IN ('100', '200', '300');

The NOT IN operator can be used with DISTINCT to exclude unique rows based on a list of values. The correct scripts will demonstrate how to use the NOT IN operator with DISTINCT in PostgreSQL.

Q30

Multiple Choice

Determine the SQL scripts that correctly use the NOT IN operator with LIMIT OFFSET in PostgreSQL.

SELECT * FROM employees WHERE department_id NOT IN (1, 2, 3) ORDER BY hire_date LIMIT 10 OFFSET 0; SELECT * FROM employees WHERE department_id NOT IN (1, 2, 3) ORDER BY hire_date LIMIT 10; SELECT * FROM employees WHERE department_id NOT IN (1, 2, 3) ORDER BY hire_date LIMIT 10 OFFSET 5;SELECT * FROM employees WHERE department_id <> ALL (1, 2, 3) ORDER BY hire_date LIMIT 10 OFFSET 0;

The NOT IN operator can be used with LIMIT OFFSET to exclude and paginate results. The correct scripts will demonstrate how to use the NOT IN operator with LIMIT OFFSET in PostgreSQL.

PostgreSQL Database Quiz Questions