Test Your Microsoft SQL Server NOT IN Operator Knowledge with This Quiz

Progress

True / False

The NOT IN operator is used to exclude rows that match the values in a specified list.

True False

The NOT IN operator checks if a value is not within a list of values and excludes those rows from the result set.

True / False

The NOT IN operator can be used with subqueries.

True False

The NOT IN operator can indeed be used with subqueries to exclude rows based on the result of another query.

True / False

Using NOT IN with a NULL value in the list will return no rows.

True False

If the list contains a NULL value, the NOT IN operator will not exclude any rows because the comparison with NULL always returns UNKNOWN.

True / False

The NOT IN operator can be used to filter out rows where a column value is within a range of values.

True False

The NOT IN operator is used to filter out rows based on a list of discrete values, not a range. For ranges, BETWEEN is more appropriate.

True / False

The NOT IN operator performs faster than the NOT EXISTS operator in all cases.

True False

The performance of NOT IN versus NOT EXISTS depends on the specific scenario and data. There is no general rule that one is always faster than the other.

True / False

The NOT IN operator can be used with string values.

True False

The NOT IN operator can be used with string values to exclude rows that match any value in a specified list of strings.

True / False

The NOT IN operator can be used to filter out rows with date values.

True False

The NOT IN operator can be used with date values to exclude rows matching any date in a specified list.

True / False

The NOT IN operator is case-sensitive by default in SQL Server.

True False

The case sensitivity of the NOT IN operator depends on the collation settings of the database. By default, SQL Server collation is case-insensitive.

True / False

The NOT IN operator can be combined with other logical operators such as AND and OR.

True False

The NOT IN operator can be used in conjunction with other logical operators to create more complex filtering conditions.

Q10

True / False

The NOT IN operator can only be used with numeric values.

True False

The NOT IN operator can be used with various data types, including numeric, string, and date values.

Q11

True / False

When using the NOT IN operator, it is important to ensure that the subquery does not return any NULL values.

True False

If the subquery used with NOT IN returns NULL values, the NOT IN condition will not exclude any rows because comparisons with NULL always yield UNKNOWN.

Q12

True / False

The NOT IN operator cannot be used with subqueries that return multiple columns.

True False

The NOT IN operator requires the subquery to return a single column of values for comparison.

Q13

True / False

The NOT IN operator can be used in an UPDATE statement to filter rows to be updated.

True False

The NOT IN operator can be used in the WHERE clause of an UPDATE statement to exclude rows that match the values in the specified list.

Q14

True / False

The NOT IN operator cannot be used with JOIN conditions.

True False

The NOT IN operator is typically used in the WHERE clause, but it can be part of a condition in a join if necessary.

Q15

True / False

The NOT IN operator can be used in a DELETE statement to exclude rows that match certain values.

True False

The NOT IN operator can be used in the WHERE clause of a DELETE statement to exclude rows from deletion based on the specified list of values.

Q16

Single Choice

Which of the following queries correctly uses the NOT IN operator to exclude rows with specific IDs?

SQL Code

SELECT *
FROM Employees
WHERE EmployeeID NOT IN (1, 2, 3);

SELECT * FROM Employees WHERE EmployeeID NOT IN (1, 2, 3); SELECT * FROM Employees WHERE EmployeeID != (1, 2, 3); SELECT * FROM Employees WHERE EmployeeID NOT IN (1, 2, 3, NULL);SELECT * FROM Employees WHERE EmployeeID IN (1, 2, 3);

This query correctly uses the NOT IN operator to exclude rows where EmployeeID is 1, 2, or 3.

Q17

Single Choice

How does the NOT IN operator behave when the subquery returns NULL values?

It excludes rows with NULL values. It includes rows with NULL values. It does not exclude any rows.It throws an error.

If the subquery returns NULL values, the NOT IN condition will not exclude any rows, as comparison with NULL returns UNKNOWN.

Q18

Single Choice

Which query will exclude all employees from the 'Sales' department?

SQL Code

SELECT *
FROM Employees
WHERE DepartmentID NOT IN (SELECT DepartmentID
 FROM Departments
 WHERE DepartmentName = 'Sales');

SELECT * FROM Employees WHERE DepartmentID IN (SELECT DepartmentID FROM Departments WHERE DepartmentName = 'Sales'); SELECT * FROM Employees WHERE DepartmentID NOT IN (SELECT DepartmentID FROM Departments WHERE DepartmentName = 'Sales'); SELECT * FROM Employees WHERE DepartmentID = (SELECT DepartmentID FROM Departments WHERE DepartmentName = 'Sales');SELECT * FROM Employees WHERE DepartmentName != 'Sales';

The subquery retrieves the DepartmentID for 'Sales' and the NOT IN operator excludes employees from that department.

Q19

Single Choice

What will be the result of using NOT IN with an empty subquery?

It will return no rows. It will return an error. It will return all rows.It will return NULL.

When the subquery is empty, the NOT IN operator returns all rows because there are no values to exclude.

Q20

Single Choice

Which query uses the NOT IN operator to exclude products from certain categories?

SQL Code

SELECT ProductName
FROM Products
WHERE CategoryID NOT IN (1, 2, 3);

SELECT ProductName FROM Products WHERE CategoryID IN (1, 2, 3); SELECT ProductName FROM Products WHERE CategoryID NOT IN (1, 2, 3); SELECT ProductName FROM Products WHERE CategoryID != (1, 2, 3);SELECT ProductName FROM Products WHERE CategoryID IS NOT (1, 2, 3);

This query excludes products from categories with IDs 1, 2, and 3 using the NOT IN operator.

Q21

Single Choice

What is the effect of using NOT IN with a list containing NULL?

It excludes rows with NULL values. It includes rows with NULL values. It does not exclude any rows.It throws an error.

Using NOT IN with a list containing NULL will result in the condition not excluding any rows, as comparison with NULL returns UNKNOWN.

Q22

Single Choice

Which of the following queries uses NOT IN to exclude rows based on a subquery?

SQL Code

SELECT EmployeeName
FROM Employees
WHERE EmployeeID NOT IN (SELECT EmployeeID
 FROM TerminatedEmployees);

SELECT EmployeeName FROM Employees WHERE EmployeeID NOT IN (SELECT EmployeeID FROM TerminatedEmployees); SELECT EmployeeName FROM Employees WHERE EmployeeID IN (SELECT EmployeeID FROM TerminatedEmployees); SELECT EmployeeName FROM Employees WHERE EmployeeID != (SELECT EmployeeID FROM TerminatedEmployees);SELECT EmployeeName FROM Employees WHERE EmployeeID IS NOT IN (SELECT EmployeeID FROM TerminatedEmployees);

This query uses a subquery to retrieve EmployeeIDs from the TerminatedEmployees table and excludes those from the Employees table.

Q23

Single Choice

How does the NOT IN operator affect query performance when dealing with large datasets?

It improves performance. It has no effect on performance. It can degrade performance.It always results in faster queries.

The NOT IN operator can affect performance negatively, especially with large datasets, because it requires a full table scan and comparison with each value in the list or subquery.

Q24

Single Choice

Which query demonstrates a correct use of NOT IN to filter out specific order statuses?

SQL Code

SELECT OrderID, OrderDate
FROM Orders
WHERE OrderStatus NOT IN ('Cancelled', 'Returned');

SELECT OrderID, OrderDate FROM Orders WHERE OrderStatus NOT IN ('Cancelled', 'Returned'); SELECT OrderID, OrderDate FROM Orders WHERE OrderStatus != ('Cancelled', 'Returned'); SELECT OrderID, OrderDate FROM Orders WHERE OrderStatus IS NOT ('Cancelled', 'Returned');SELECT OrderID, OrderDate FROM Orders WHERE OrderStatus NOT ('Cancelled', 'Returned');

This query uses the NOT IN operator to exclude orders with statuses 'Cancelled' and 'Returned'.

Q25

Single Choice

Which statement about the NOT IN operator is accurate?

The NOT IN operator includes rows that match any of the values in the specified list. The NOT IN operator excludes rows that match any of the values in the specified list. The NOT IN operator is used to include rows with NULL values.The NOT IN operator is case-sensitive.

The NOT IN operator excludes rows that match any of the values in the specified list.

Q26

Single Choice

Which of the following is a valid use of the NOT IN operator?

SQL Code

SELECT CustomerName
FROM Customers
WHERE Country NOT IN ('USA', 'Canada');

SELECT CustomerName FROM Customers WHERE Country NOT IN ('USA', 'Canada'); SELECT CustomerName FROM Customers WHERE Country IN ('USA', 'Canada'); SELECT CustomerName FROM Customers WHERE Country != ('USA', 'Canada');SELECT CustomerName FROM Customers WHERE Country IS NOT ('USA', 'Canada');

This query uses the NOT IN operator to exclude customers from the USA and Canada.

Q27

Single Choice

Which query excludes products that belong to the 'Electronics' and 'Toys' categories?

SQL Code

SELECT ProductName
FROM Products
WHERE CategoryName NOT IN ('Electronics', 'Toys');

SELECT ProductName FROM Products WHERE CategoryName NOT IN ('Electronics', 'Toys'); SELECT ProductName FROM Products WHERE CategoryName IN ('Electronics', 'Toys'); SELECT ProductName FROM Products WHERE CategoryName != ('Electronics', 'Toys');SELECT ProductName FROM Products WHERE CategoryName IS NOT ('Electronics', 'Toys');

The query correctly uses the NOT IN operator to exclude products from 'Electronics' and 'Toys' categories.

Q28

Single Choice

What does the NOT IN operator do in the following query?

SQL Code

SELECT OrderID
FROM Orders
WHERE CustomerID NOT IN (SELECT CustomerID FROM Customers WHERE Country = 'Germany');

Includes orders from customers in Germany. Excludes orders from customers not in Germany. Excludes orders from customers in Germany.Includes all orders.

The NOT IN operator excludes orders from customers located in Germany.

Q29

Single Choice

Which query uses NOT IN to exclude sales from specific regions?

SQL Code

SELECT SaleID, SaleAmount
FROM Sales
WHERE RegionID NOT IN (1, 2, 3);

SELECT SaleID, SaleAmount FROM Sales WHERE RegionID NOT IN (1, 2, 3); SELECT SaleID, SaleAmount FROM Sales WHERE RegionID IN (1, 2, 3); SELECT SaleID, SaleAmount FROM Sales WHERE RegionID != (1, 2, 3);SELECT SaleID, SaleAmount FROM Sales WHERE RegionID IS NOT (1, 2, 3);

The query uses the NOT IN operator to exclude sales from regions with IDs 1, 2, and 3.

Q30

Single Choice

Which of the following queries excludes employees from the 'HR' and 'IT' departments?

SQL Code

SELECT EmployeeName
FROM Employees
WHERE DepartmentName NOT IN ('HR', 'IT');

SELECT EmployeeName FROM Employees WHERE DepartmentName NOT IN ('HR', 'IT'); SELECT EmployeeName FROM Employees WHERE DepartmentName IN ('HR', 'IT'); SELECT EmployeeName FROM Employees WHERE DepartmentName != ('HR', 'IT');SELECT EmployeeName FROM Employees WHERE DepartmentName IS NOT ('HR', 'IT');

The query correctly uses the NOT IN operator to exclude employees from the 'HR' and 'IT' departments.

Q31

Multiple Choice

Which query will exclude products from the specified categories?

SQL Code

SELECT ProductName
FROM Products
WHERE CategoryID NOT IN (SELECT CategoryID
 FROM Categories
 WHERE CategoryName IN ('Books', 'Movies'));

SELECT ProductName FROM Products WHERE CategoryID NOT IN (SELECT CategoryID FROM Categories WHERE CategoryName IN ('Books', 'Movies')); SELECT ProductName FROM Products WHERE CategoryID IN (SELECT CategoryID FROM Categories WHERE CategoryName IN ('Books', 'Movies')); SELECT ProductName FROM Products WHERE CategoryID != (SELECT CategoryID FROM Categories WHERE CategoryName IN ('Books', 'Movies'));SELECT ProductName FROM Products WHERE CategoryID IS NOT IN (SELECT CategoryID FROM Categories WHERE CategoryName IN ('Books', 'Movies'));

The subquery retrieves CategoryIDs for 'Books' and 'Movies' and the NOT IN operator excludes products from those categories.

Q32

Multiple Choice

Which query correctly excludes employees from terminated employee IDs?

SQL Code

SELECT EmployeeName
FROM Employees
WHERE EmployeeID NOT IN (SELECT EmployeeID
 FROM TerminatedEmployees);

The subquery retrieves EmployeeIDs from TerminatedEmployees and the NOT IN operator excludes those employees from the Employees table.

Q33

Multiple Choice

Which query uses the NOT IN operator to filter out customers from specific countries?

SQL Code

SELECT CustomerName
FROM Customers
WHERE Country NOT IN ('Germany', 'France', 'Japan');

SELECT CustomerName FROM Customers WHERE Country NOT IN ('Germany', 'France', 'Japan'); SELECT CustomerName FROM Customers WHERE Country IN ('Germany', 'France', 'Japan'); SELECT CustomerName FROM Customers WHERE Country != ('Germany', 'France', 'Japan');SELECT CustomerName FROM Customers WHERE Country IS NOT ('Germany', 'France', 'Japan');

The query uses the NOT IN operator to exclude customers from Germany, France, and Japan.

Q34

Multiple Choice

Which query uses the NOT IN operator to exclude orders placed by certain customers?

SQL Code

SELECT OrderID, OrderDate
FROM Orders
WHERE CustomerID NOT IN (SELECT CustomerID
 FROM Customers
 WHERE Country = 'USA');

SELECT OrderID, OrderDate FROM Orders WHERE CustomerID NOT IN (SELECT CustomerID FROM Customers WHERE Country = 'USA'); SELECT OrderID, OrderDate FROM Orders WHERE CustomerID IN (SELECT CustomerID FROM Customers WHERE Country = 'USA'); SELECT OrderID, OrderDate FROM Orders WHERE CustomerID != (SELECT CustomerID FROM Customers WHERE Country = 'USA');SELECT OrderID, OrderDate FROM Orders WHERE CustomerID IS NOT IN (SELECT CustomerID FROM Customers WHERE Country = 'USA');

The query uses a subquery to retrieve CustomerIDs from the USA and the NOT IN operator to exclude orders placed by those customers.

Q35

Multiple Choice

Which query excludes employees with IDs found in the subquery?

SQL Code

SELECT EmployeeName
FROM Employees
WHERE EmployeeID NOT IN (SELECT EmployeeID
 FROM RetiredEmployees);

SELECT EmployeeName FROM Employees WHERE EmployeeID NOT IN (SELECT EmployeeID FROM RetiredEmployees); SELECT EmployeeName FROM Employees WHERE EmployeeID IN (SELECT EmployeeID FROM RetiredEmployees); SELECT EmployeeName FROM Employees WHERE EmployeeID != (SELECT EmployeeID FROM RetiredEmployees);SELECT EmployeeName FROM Employees WHERE EmployeeID IS NOT IN (SELECT EmployeeID FROM RetiredEmployees);

The subquery retrieves EmployeeIDs from RetiredEmployees and the NOT IN operator excludes those employees from the Employees table.

Q36

Multiple Choice

Which query uses NOT IN to filter out products from specific suppliers?

SQL Code

SELECT ProductName
FROM Products
WHERE SupplierID NOT IN (SELECT SupplierID
 FROM Suppliers
 WHERE Country = 'China');

SELECT ProductName FROM Products WHERE SupplierID NOT IN (SELECT SupplierID FROM Suppliers WHERE Country = 'China'); SELECT ProductName FROM Products WHERE SupplierID IN (SELECT SupplierID FROM Suppliers WHERE Country = 'China'); SELECT ProductName FROM Products WHERE SupplierID != (SELECT SupplierID FROM Suppliers WHERE Country = 'China');SELECT ProductName FROM Products WHERE SupplierID IS NOT IN (SELECT SupplierID FROM Suppliers WHERE Country = 'China');

The query uses a subquery to retrieve SupplierIDs from China and the NOT IN operator to exclude products from those suppliers.

Q37

Multiple Choice

Which query excludes students with IDs found in the subquery?

SQL Code

SELECT StudentName
FROM Students
WHERE StudentID NOT IN (SELECT StudentID
 FROM GraduatedStudents);

SELECT StudentName FROM Students WHERE StudentID NOT IN (SELECT StudentID FROM GraduatedStudents); SELECT StudentName FROM Students WHERE StudentID IN (SELECT StudentID FROM GraduatedStudents); SELECT StudentName FROM Students WHERE StudentID != (SELECT StudentID FROM GraduatedStudents);SELECT StudentName FROM Students WHERE StudentID IS NOT IN (SELECT StudentID FROM GraduatedStudents);

The subquery retrieves StudentIDs from GraduatedStudents and the NOT IN operator excludes those students from the Students table.

Q38

Multiple Choice

Which query uses NOT IN to exclude products from suppliers in specific countries?

SQL Code

SELECT ProductName
FROM Products
WHERE SupplierID NOT IN (SELECT SupplierID
 FROM Suppliers
 WHERE Country IN ('USA', 'UK'));

SELECT ProductName FROM Products WHERE SupplierID NOT IN (SELECT SupplierID FROM Suppliers WHERE Country IN ('USA', 'UK')); SELECT ProductName FROM Products WHERE SupplierID IN (SELECT SupplierID FROM Suppliers WHERE Country IN ('USA', 'UK')); SELECT ProductName FROM Products WHERE SupplierID != (SELECT SupplierID FROM Suppliers WHERE Country IN ('USA', 'UK'));SELECT ProductName FROM Products WHERE SupplierID IS NOT IN (SELECT SupplierID FROM Suppliers WHERE Country IN ('USA', 'UK'));

The query uses a subquery to retrieve SupplierIDs from the USA and UK, and the NOT IN operator to exclude products from those suppliers.

Q39

Multiple Choice

Which query uses the NOT IN operator to exclude specific products?

SQL Code

SELECT ProductName
FROM Products
WHERE ProductID NOT IN (SELECT ProductID
 FROM DiscontinuedProducts);

SELECT ProductName FROM Products WHERE ProductID NOT IN (SELECT ProductID FROM DiscontinuedProducts); SELECT ProductName FROM Products WHERE ProductID IN (SELECT ProductID FROM DiscontinuedProducts); SELECT ProductName FROM Products WHERE ProductID != (SELECT ProductID FROM DiscontinuedProducts);SELECT ProductName FROM Products WHERE ProductID IS NOT IN (SELECT ProductID FROM DiscontinuedProducts);

The subquery retrieves ProductIDs from DiscontinuedProducts and the NOT IN operator excludes those products from the Products table.

Q40

Multiple Choice

Which query uses NOT IN to filter out customers from specific cities?

SQL Code

SELECT CustomerName
FROM Customers
WHERE City NOT IN ('New York', 'Los Angeles');

SELECT CustomerName FROM Customers WHERE City NOT IN ('New York', 'Los Angeles'); SELECT CustomerName FROM Customers WHERE City IN ('New York', 'Los Angeles'); SELECT CustomerName FROM Customers WHERE City != ('New York', 'Los Angeles');SELECT CustomerName FROM Customers WHERE City IS NOT ('New York', 'Los Angeles');

The query uses the NOT IN operator to exclude customers from New York and Los Angeles.

Q41

Multiple Choice

Which query uses the NOT IN operator to exclude specific orders?

SQL Code

SELECT OrderID, OrderDate
FROM Orders
WHERE OrderID NOT IN (SELECT OrderID
 FROM CancelledOrders);

SELECT OrderID, OrderDate FROM Orders WHERE OrderID NOT IN (SELECT OrderID FROM CancelledOrders); SELECT OrderID, OrderDate FROM Orders WHERE OrderID IN (SELECT OrderID FROM CancelledOrders); SELECT OrderID, OrderDate FROM Orders WHERE OrderID != (SELECT OrderID FROM CancelledOrders);SELECT OrderID, OrderDate FROM Orders WHERE OrderID IS NOT IN (SELECT OrderID FROM CancelledOrders);

The subquery retrieves OrderIDs from CancelledOrders and the NOT IN operator excludes those orders from the Orders table.

Q42

Multiple Choice

Which query uses NOT IN to filter out students from specific grades?

SQL Code

SELECT StudentName
FROM Students
WHERE Grade NOT IN ('A', 'B');

SELECT StudentName FROM Students WHERE Grade NOT IN ('A', 'B'); SELECT StudentName FROM Students WHERE Grade IN ('A', 'B'); SELECT StudentName FROM Students WHERE Grade != ('A', 'B');SELECT StudentName FROM Students WHERE Grade IS NOT ('A', 'B');

The query uses the NOT IN operator to exclude students with grades 'A' and 'B'.

Q43

Multiple Choice

Which query uses the NOT IN operator to exclude orders from specific customers?

SQL Code

SELECT OrderID, OrderDate
FROM Orders
WHERE CustomerID NOT IN (SELECT CustomerID
 FROM PreferredCustomers);

SELECT OrderID, OrderDate FROM Orders WHERE CustomerID NOT IN (SELECT CustomerID FROM PreferredCustomers); SELECT OrderID, OrderDate FROM Orders WHERE CustomerID IN (SELECT CustomerID FROM PreferredCustomers); SELECT OrderID, OrderDate FROM Orders WHERE CustomerID != (SELECT CustomerID FROM PreferredCustomers);SELECT OrderID, OrderDate FROM Orders WHERE CustomerID IS NOT IN (SELECT CustomerID FROM PreferredCustomers);

The subquery retrieves CustomerIDs from PreferredCustomers and the NOT IN operator excludes orders from those customers.

Q44

Multiple Choice

Which query excludes suppliers with IDs found in the subquery?

SQL Code

SELECT SupplierName
FROM Suppliers
WHERE SupplierID NOT IN (SELECT SupplierID
 FROM BannedSuppliers);

SELECT SupplierName FROM Suppliers WHERE SupplierID NOT IN (SELECT SupplierID FROM BannedSuppliers); SELECT SupplierName FROM Suppliers WHERE SupplierID IN (SELECT SupplierID FROM BannedSuppliers); SELECT SupplierName FROM Suppliers WHERE SupplierID != (SELECT SupplierID FROM BannedSuppliers);SELECT SupplierName FROM Suppliers WHERE SupplierID IS NOT IN (SELECT SupplierID FROM BannedSuppliers);

The subquery retrieves SupplierIDs from BannedSuppliers and the NOT IN operator excludes those suppliers from the Suppliers table.

Q45

Multiple Choice

Which query uses the NOT IN operator to exclude employees from specific departments?

SQL Code

SELECT EmployeeName
FROM Employees
WHERE DepartmentID NOT IN (SELECT DepartmentID
 FROM Departments
 WHERE DepartmentName IN ('Sales', 'Marketing'));

SELECT EmployeeName FROM Employees WHERE DepartmentID NOT IN (SELECT DepartmentID FROM Departments WHERE DepartmentName IN ('Sales', 'Marketing')); SELECT EmployeeName FROM Employees WHERE DepartmentID IN (SELECT DepartmentID FROM Departments WHERE DepartmentName IN ('Sales', 'Marketing')); SELECT EmployeeName FROM Employees WHERE DepartmentID != (SELECT DepartmentID FROM Departments WHERE DepartmentName IN ('Sales', 'Marketing'));SELECT EmployeeName FROM Employees WHERE DepartmentID IS NOT IN (SELECT DepartmentID FROM Departments WHERE DepartmentName IN ('Sales', 'Marketing'));

The subquery retrieves DepartmentIDs for 'Sales' and 'Marketing' and the NOT IN operator excludes employees from those departments.

Microsoft SQL Server Database Quiz Questions