Font size
S
M
L
XL
WorksheetsSWC_Kargil_Week_3_Quiz
Total questions: 100
Worksheet time: 5hrs 40mins
Name
Class
Date
1.
Which of the following is NOT a Date Function in SQL?
a)
DATE()
b)
NOW()
c)
THEN()
d)
DAY()
2.
What will be the output of the following SQL function? SELECT MOD(53, 10);
a)
5
b)
3
c)
10
3.
Which of the following is a characteristic of a relational database?
a)
Hierarchical structure
b)
Key-value pairs
c)
Tabular structure
d)
Document-based storage
4.
What is the purpose of the SQL PRIMARY KEY constraint?
a)
Allow null values
b)
Ensure uniqueness
c)
Link tables
d)
Check conditions
5.
What does the ACID property 'Isolation' ensure in a transaction?
a)
Data is permanently saved
b)
Transactions are processed independently
c)
Data remains consistent
d)
All operations are completed
6.
Which normal form eliminates partial dependencies?
a)
1NF
b)
2NF
c)
3NF
d)
BCNF
7.
What is the output of SELECT COUNT(*) FROM Table WHERE column IS NULL?
a)
Number of rows
b)
Number of non-null values
c)
Number of null values
8.
Which SQL command is used to delete a table’s structure and data?
a)
DELETE
b)
TRUNCATE
c)
DROP
d)
REMOVE
9.
What is a foreign key in a relational database?
a)
A unique identifier
b)
A key linking tables
c)
A non-unique column
d)
A temporary key
10.
Which of the following is NOT a type of SQL JOIN?
a)
INNER JOIN
b)
LEFT JOIN
c)
CROSS JOIN
d)
SIDE JOIN
11.
What is the purpose of a database index?
a)
Enforce constraints
b)
Speed up queries
c)
Store backups
d)
Define relationships
12.
Which SQL function returns the length of a string?
a)
LEN()
b)
SIZE()
c)
LENGTH()
d)
COUNT()
13.
What is a deadlock in DBMS?
a)
Infinite query loop
b)
Transaction waiting indefinitely
c)
System crash
d)
Data corruption
14.
Which of the following ensures no transitive dependencies?
a)
1NF
b)
2NF
c)
3NF
d)
BCNF
15.
What does the SQL command ALTER TABLE do?
a)
Delete data
b)
Modify table structure
c)
Insert data
d)
Create a table
16.
Which SQL operator combines results without duplicates?
a)
UNION
b)
UNION ALL
c)
INTERSECT
d)
EXCEPT
17.
What is a view in DBMS?
a)
Physical table
b)
Virtual table
c)
Backup table
d)
Index table
18.
Which of the following is a NoSQL database?
a)
MySQL
b)
MongoDB
c)
Oracle
d)
PostgreSQL
19.
What does SELECT ROUND(15.678, 2) return?
a)
15.67
b)
15.68
c)
15.7
d)
16
20.
Which SQL clause filters groups after GROUP BY?
a)
WHERE
b)
HAVING
c)
ORDER BY
d)
LIMIT
21.
What is the time complexity of searching in a B+ tree?
a)
O(n)
b)
O(log n)
c)
O(n log n)
d)
O(1)
22.
Which SQL command grants user permissions?
a)
ALLOW
b)
GRANT
c)
PERMIT
d)
AUTHORIZE
23.
Which isolation level prevents dirty reads?
a)
Read Uncommitted
b)
Read Committed
c)
Repeatable Read
d)
Serializable
24.
What is the purpose of denormalization?
a)
Reduce redundancy
b)
Improve query performance
c)
Eliminate anomalies
d)
Enforce constraints
25.
Which SQL command makes transaction changes permanent?
a)
ROLLBACK
b)
COMMIT
c)
SAVE
d)
END
26.
What is a trigger in DBMS?
a)
Manual procedure
b)
Automatic program
c)
Index type
d)
Backup tool
27.
Which of the following is a candidate key?
a)
Non-unique column
b)
Minimal superkey
c)
Foreign key
d)
Null column
28.
What does SELECT UPPER('sql') return?
a)
SQL
b)
sql
c)
Sql
d)
SqL
29.
Given a table with a non-clustered index on a column, what happens when a new row is inserted?
a)
The table is physically reordered
b)
The index is updated
c)
Data is duplicated
d)
No impact on the index
30.
Which isolation level allows dirty reads but prevents non-repeatable reads?
a)
Read Uncommitted
b)
Read Committed
c)
Repeatable Read
d)
Serializable
31.
In a B+ tree index with 100,000 records, what is the approximate number of disk I/O operations for a search?
a)
O(n)
b)
O(log n)
c)
O(n log n)
d)
O(1)
32.
Which of the following violates Boyce-Codd Normal Form (BCNF)?
a)
Partial dependency
b)
Transitive dependency
c)
Non-key determining another non-key
d)
Key determining a non-key
33.
What is the impact of a covering index on a query?
a)
Increases disk I/O
b)
Slows down writes
c)
Satisfies query without accessing table data
d)
Requires a full table scan
34.
Which SQL clause can cause a query to perform a full table scan if misused?
a)
ORDER BY
b)
GROUP BY
c)
WHERE
d)
HAVING
35.
In a transaction with Repeatable Read isolation, which anomaly can still occur?
a)
Dirty reads
b)
Non-repeatable reads
c)
Phantom reads
d)
Lost updates
36.
What is the purpose of the SQL EXPLAIN PLAN command?
a)
Executes a query
b)
Optimizes a query
c)
Shows the query execution plan
d)
Modifies table structure
37.
Which join type is most efficient for retrieving all rows from two large tables with no matching condition?
a)
INNER JOIN
b)
LEFT JOIN
c)
CROSS JOIN
d)
FULL OUTER JOIN
38.
Which of the following can cause a deadlock in a DBMS?
a)
Sequential transaction execution
b)
Exclusive locks held in a cycle
c)
Single-user database access
d)
Low disk space
39.
Which normal form is violated if a table has a composite key and a non-key attribute depends on part of the key?
a)
1NF
b)
2NF
c)
3NF
d)
BCNF
40.
What happens if a transaction is rolled back after a COMMIT?
a)
Changes are undone
b)
Changes remain permanent
c)
Transaction is restarted
d)
Database crashes
41.
Which SQL function can be used to handle NULL values in a calculation?
a)
AVG()
b)
COALESCE()
c)
SUM()
d)
MAX()
42.
What is the primary drawback of denormalization in a database?
a)
Increased query complexity
b)
Higher storage requirements
c)
Slower read performance
d)
Increased join operations
43.
Which of the following ensures serializable transaction execution?
a)
Read Uncommitted
b)
Read Committed
c)
Repeatable Read
d)
Serializable
44.
What is the time complexity of inserting a record into a balanced B+ tree index?
a)
O(n)
b)
O(log n)
c)
O(n log n)
d)
O(1)
45.
Which SQL feature allows reuse of a query result within the same query?
a)
Subquery
b)
Common Table Expression (CTE)
c)
View
d)
Trigger
46.
In a database with frequent updates, which index type is least suitable?
a)
B+ tree
b)
Bitmap index
c)
Hash index
d)
Clustered index
47.
What is the purpose of a database checkpoint?
a)
Optimizes queries
b)
Ensures data consistency during recovery
c)
Indexes data
d)
Manages user permissions
48.
Given a table Employees (emp_id, emp_name, dept_id, salary), which query retrieves the second-highest salary?
a)
SELECT MAX(salary) FROM Employees WHERE salary < (SELECT MAX(salary) FROM Employees);
b)
SELECT salary FROM Employees ORDER BY salary DESC LIMIT 1 OFFSET 1;
c)
SELECT TOP 2 salary FROM Employees ORDER BY salary DESC;
d)
Both 1 and 2 are correct
49.
For tables Orders (order_id, customer_id, order_date) and Customers (customer_id, cust_name), which query lists customers who placed orders in 2023?
a)
SELECT DISTINCT c.cust_name FROM Customers c INNER JOIN Orders o ON c.customer_id = o.customer_id WHERE YEAR(o.order_date) = 2023;
b)
SELECT c.cust_name FROM Customers c JOIN Orders o WHERE YEAR(o.order_date) = 2023;
c)
SELECT c.cust_name FROM Customers c LEFT JOIN Orders o ON c.customer_id = o.customer_id WHERE YEAR(o.order_date) = 2023;
d)
SELECT c.cust_name FROM Customers c CROSS JOIN Orders o WHERE YEAR(o.order_date) = 2023;
50.
Given a table Sales (sale_id, prod_id, quantity, sale_date), which query finds products with total quantity sold > 100 in January 2023?
a)
SELECT prod_id, SUM(quantity) FROM Sales WHERE MONTH(sale_date) = 1 AND YEAR(sale_date) = 2023 GROUP BY prod_id HAVING SUM(quantity) > 100;
b)
SELECT prod_id, SUM(quantity) FROM Sales GROUP BY prod_id WHERE MONTH(sale_date) = 1 AND YEAR(sale_date) = 2023 HAVING SUM(quantity) > 100;
c)
SELECT prod_id, COUNT(quantity) FROM Sales WHERE MONTH(sale_date) = 1 AND YEAR(sale_date) = 2023 GROUP BY prod_id HAVING COUNT(quantity) > 100;
d)
SELECT prod_id, SUM(quantity) FROM Sales WHERE MONTH(sale_date) = 1 AND YEAR(sale_date) = 2023;
51.
For tables Emp (emp_id, emp_name, dept_id, salary) and Dept (dept_id, dept_name), which query finds departments with no employees?
a)
SELECT d.dept_name FROM Dept d LEFT JOIN Emp e ON d.dept_id = e.dept_id WHERE e.emp_id IS NULL;
b)
SELECT d.dept_name FROM Dept d WHERE d.dept_id NOT IN (SELECT dept_id FROM Emp);
c)
SELECT d.dept_name FROM Dept d RIGHT JOIN Emp e ON d.dept_id = e.dept_id WHERE e.emp_id IS NULL;
d)
Both 1 and 2 are correct
52.
Given a table Transactions (trans_id, account_id, amount), which query calculates the running total of amounts per account?
a)
SELECT trans_id, account_id, amount, SUM(amount) OVER (PARTITION BY account_id ORDER BY trans_id) AS running_total FROM Transactions;
b)
SELECT trans_id, account_id, SUM(amount) FROM Transactions GROUP BY account_id;
c)
SELECT trans_id, account_id, amount, SUM(amount) OVER (ORDER BY trans_id) FROM Transactions;
d)
SELECT trans_id, account_id, amount, SUM(amount) FROM Transactions GROUP BY trans_id, account_id;
53.
For a table Products (prod_id, prod_name, price), which query updates prices of products with price < 50 to increase by 20%?
a)
UPDATE Products SET price = price * 1.20 WHERE price < 50;
b)
UPDATE Products SET price = price + 20 WHERE price < 50;
c)
UPDATE Products SET price = price * 0.20 WHERE price < 50;
d)
UPDATE Products WHERE price < 50 SET price = price * 1.20;
54.
Given a table Students (id, name, marks), which query ranks students based on marks in descending order?
a)
SELECT id, name, marks, RANK() OVER (ORDER BY marks DESC) AS rank FROM Students;
b)
SELECT id, name, marks, ROW_NUMBER() OVER (ORDER BY marks) FROM Students;
c)
SELECT id, name, marks, DENSE_RANK() OVER (ORDER BY marks DESC) FROM Students;
d)
Both 1 and 3 are correct
55.
For tables Orders (order_id, customer_id, amount) and Customers (customer_id, cust_name), which query finds customers whose total order amount exceeds 1000?
a)
SELECT c.cust_name, SUM(o.amount) FROM Customers c JOIN Orders o ON c.customer_id = o.customer_id GROUP BY c.cust_name HAVING SUM(o.amount) > 1000;
b)
SELECT c.cust_name, SUM(o.amount) FROM Customers c JOIN Orders o GROUP BY c.customer_id HAVING SUM(o.amount) > 1000;
c)
SELECT c.cust_name FROM Customers c JOIN Orders o ON c.customer_id = o.customer_id WHERE SUM(o.amount) > 1000;
d)
SELECT c.cust_name, SUM(o.amount) FROM Customers c CROSS JOIN Orders o GROUP BY c.cust_name HAVING SUM(o.amount) > 1000;
56.
Given a table Employees (emp_id, emp_name, salary, hire_date), which query finds employees hired in the last 6 months?
a)
SELECT * FROM Employees WHERE hire_date >= DATE_SUB(CURDATE(), INTERVAL 6 MONTH);
b)
SELECT * FROM Employees WHERE hire_date <= DATE_SUB(CURDATE(), INTERVAL 6 MONTH);
c)
SELECT * FROM Employees WHERE hire_date >= DATE_ADD(CURDATE(), INTERVAL 6 MONTH);
d)
SELECT * FROM Employees WHERE DATEDIFF(CURDATE(), hire_date) <= 6;
57.
For a table Logs (log_id, user_id, action, log_time), which query finds users with more than 5 actions in a day?
a)
SELECT user_id, DATE(log_time), COUNT(*) FROM Logs GROUP BY user_id, DATE(log_time) HAVING COUNT(*) > 5;
b)
SELECT user_id, COUNT(*) FROM Logs GROUP BY user_id HAVING COUNT(*) > 5;
c)
SELECT user_id, DATE(log_time), SUM(action) FROM Logs GROUP BY user_id, DATE(log_time) HAVING SUM(action) > 5;
d)
SELECT user_id, COUNT(*) FROM Logs WHERE DATE(log_time) GROUP BY user_id HAVING COUNT(*) > 5;
58.
Which of the following SQL queries correctly retrieves the top 3 customers based on total order amount for the year 2023 from the Orders table
a)
SELECT customer_id, SUM(amount) AS total_amount
FROM Orders
WHERE YEAR(order_date) = 2023
GROUP BY customer_id
ORDER BY total_amount DESC
LIMIT 3;
b)
SELECT customer_id, MAX(amount)
FROM Orders
WHERE YEAR(order_date) = 2023
GROUP BY customer_id
ORDER BY amount DESC
LIMIT 3;
c)
SELECT customer_id, SUM(amount) AS total_amount
FROM Orders
WHERE YEAR(order_date) = 2023
GROUP BY customer_id
ORDER BY total_amount ASC
LIMIT 3;
d)
SELECT c.customer_id, SUM(o.amount) AS total_amount FROM Orders o WHERE YEAR(o.order_date) = 2023 GROUP BY c.customer_id ORDER BY total_amount DESC LIMIT 3;
59.
What is a key difference between a materialized view and a regular view, and when would you prefer a materialized view?
a)
A materialized view stores data physically and is ideal for real-time dashboards where live updates are needed.
b)
A materialized view is faster than a regular view but cannot be refreshed or updated.
c)
A materialized view stores query results physically and is ideal when data doesn't change frequently and performance is critical, like daily sales dashboards.
d)
A regular view stores data permanently, while a materialized view always reflects the latest data.
60.
For a table Orders (order_id, customer_id, order_date, amount), which query identifies the top 2 highest-grossing products by total order amount in 2023?
a)
SELECT product_id, SUM(amount) FROM Orders WHERE YEAR(order_date) = 2023 GROUP BY product_id ORDER BY SUM(amount) DESC LIMIT 2;
b)
SELECT product_id, SUM(amount) FROM Orders GROUP BY product_id HAVING YEAR(order_date) = 2023 ORDER BY SUM(amount) DESC LIMIT 2;
c)
SELECT product_id, SUM(amount) FROM Orders WHERE YEAR(order_date) = 2023 ORDER BY SUM(amount) DESC LIMIT 2;
d)
SELECT product_id, SUM(amount) FROM Orders WHERE YEAR(order_date) = 2023 GROUP BY product_id LIMIT 2;
61.
Which transaction isolation level would you choose for a system like Amazon’s order processing to prevent phantom reads while minimizing performance overhead?
a)
Read Uncommitted
b)
Read Committed
c)
Repeatable Read
d)
Serializable
62.
Given two large tables, Customers (customer_id, cust_name) and Orders (order_id, customer_id, amount), which join type is most efficient for finding customers with no orders?
a)
INNER JOIN
b)
LEFT JOIN
c)
RIGHT JOIN
d)
FULL OUTER JOIN
63.
In Amazon’s data warehouse, why might a query on a large table scan slowly despite having an index?
a)
Index is corrupted
b)
Non-selective index
c)
Too many NULL values
d)
Index is not covering
64.
For a table Transactions (trans_id, account_id, amount, trans_date), which query finds accounts with a total transaction amount exceeding $5000 in the last 7 days, excluding duplicates?
a)
SELECT DISTINCT account_id, SUM(amount) FROM Transactions WHERE trans_date >= DATE_SUB(CURDATE(), INTERVAL 7 DAY) GROUP BY account_id HAVING SUM(amount) > 5000;
b)
SELECT account_id, SUM(amount) FROM Transactions GROUP BY account_id HAVING SUM(amount) > 5000 AND trans_date >= DATE_SUB(CURDATE(), INTERVAL 7 DAY);
c)
SELECT account_id, SUM(amount) FROM Transactions WHERE trans_date >= DATE_SUB(CURDATE(), INTERVAL 7 DAY) GROUP BY account_id;
d)
SELECT account_id, SUM(amount) FROM Transactions WHERE trans_date >= DATE_SUB(CURDATE(), INTERVAL 7 DAY) HAVING SUM(amount) > 5000;
65.
In a high-transaction storage system (e.g., Western Digital), which index type is least suitable for a table with frequent updates and range queries?
a)
B+ tree
b)
Bitmap index
c)
Clustered index
d)
Non-clustered index
66.
Which transaction isolation level should an organisation use for a security logging system to prevent dirty reads while allowing some concurrency?
a)
Read Uncommitted
b)
Read Committed
c)
Repeatable Read
d)
Serializable
67.
Given a table Employees (emp_id, emp_name, dept_id, salary), which query identifies the employee with the highest salary in each department, handling ties?
a)
SELECT emp_id, emp_name, dept_id, salary FROM Employees WHERE (dept_id, salary) IN (SELECT dept_id, MAX(salary) FROM Employees GROUP BY dept_id);
b)
SELECT emp_id, emp_name, dept_id, salary, DENSE_RANK() OVER (PARTITION BY dept_id ORDER BY salary DESC) AS rnk FROM Employees WHERE rnk = 1;
c)
SELECT emp_id, emp_name, dept_id, salary FROM Employees ORDER BY dept_id, salary DESC LIMIT 1;
d)
Both 1 and 2 are correct
68.
What is the primary benefit of using a CTE over a subquery in SQL?
a)
Increases query execution speed
b)
Improves query readability
c)
Reduces database storage
d)
Enables recursive queries
69.
In relational algebra, which operator retrieves rows from two relations that satisfy a condition?
a)
Union
b)
Join
c)
Projection
d)
Selection
70.
Which normal form is violated if a non-key attribute depends on another non-key attribute?
a)
1NF
b)
2NF
c)
3NF
d)
BCNF
71.
What is a key characteristic of a recursive CTE?
a)
It cannot reference itself
b)
It references itself to process hierarchical data
c)
It is limited to one iteration
d)
It requires a GROUP BY clause
72.
Which SQL construct is best for breaking a complex query into reusable parts without creating a permanent object?
a)
View
b)
Nested Query
c)
CTE
d)
Trigger
73.
In relational algebra, what does the Projection operator (π) do?
a)
Filters rows based on a condition
b)
Selects specific columns
c)
Combines rows from two relations
d)
Removes duplicate rows
74.
Which of the following is true for a nested query in SQL?
a)
It cannot be correlated
b)
It is always executed first
c)
It can be used in the WHERE clause
d)
It requires a CTE
75.
What is the purpose of the UNION operator in a recursive CTE’s recursive part?
a)
To filter rows
b)
To combine iterative results
c)
To sort results
d)
To eliminate duplicates
76.
Which normal form requires that every determinant be a candidate key?
a)
1NF
b)
2NF
c)
3NF
d)
BCNF
77.
In relational algebra, what is the result of the Cartesian Product (R × S)?
a)
Rows where conditions match
b)
All possible combinations of rows
c)
Rows unique to R
d)
Rows unique to S
78.
Which of the following SQL queries uses a correlated nested query?
a)
SELECT * FROM Employees WHERE salary > (SELECT AVG(salary) FROM Employees);
b)
SELECT * FROM Employees WHERE dept_id IN (SELECT dept_id FROM Departments);
c)
SELECT * FROM Employees e WHERE EXISTS (SELECT 1 FROM Departments d WHERE d.dept_id = e.dept_id);
d)
SELECT * FROM Employees ORDER BY salary DESC LIMIT 1;
79.
What is the main advantage of using UNION ALL over UNION in a recursive CTE?
a)
Removes duplicates
b)
Faster execution
c)
Sorts results
d)
Allows nested queries
80.
Which of the following is NOT a requirement for First Normal Form (1NF)?
a)
Atomic values
b)
Unique rows
c)
No transitive dependencies
d)
Primary key
81.
In relational algebra, which operator is equivalent to a SQL WHERE clause?
a)
Projection
b)
Selection
c)
Join
d)
Union
82.
What happens if a recursive CTE lacks a termination condition?
a)
Executes once
b)
Returns NULL
c)
Runs indefinitely
d)
Throws an error
83.
Which of the following best describes a CTE’s scope in SQL?
a)
Available across multiple queries
b)
Persists in the database
c)
Available only within the defining query
d)
Stored as a table
84.
In relational algebra, what does the Difference operator (R − S) return?
a)
Rows common to R and S
b)
Rows in R but not in S
c)
Rows in S but not in R
d)
All rows from R and S
85.
Which normal form addresses multi-valued dependencies?
a)
2NF
b)
3NF
c)
4NF
d)
BCNF
86.
What is the output of a nested query used in a SQL IN clause?
a)
A single value
b)
A table
c)
A list of values
d)
A boolean
87.
Which relational algebra operator can be used to rename a relation or its attributes?
a)
Projection
b)
Selection
c)
Rename
d)
Join
88.
Given a table Employees (emp_id, emp_name, dept_id, salary), which CTE query calculates the average salary per department?
a)
WITH AvgSal AS (SELECT dept_id, AVG(salary) AS avg_salary FROM Employees GROUP BY dept_id) SELECT * FROM AvgSal;
b)
WITH AvgSal AS (SELECT AVG(salary) FROM Employees) SELECT * FROM AvgSal;
c)
WITH AvgSal AS (SELECT dept_id, SUM(salary) FROM Employees GROUP BY dept_id) SELECT * FROM AvgSal;
d)
WITH AvgSal AS (SELECT dept_id, salary FROM Employees) SELECT AVG(salary) FROM AvgSal;
89.
For a table Orders (order_id, customer_id, amount), which nested query finds customers whose total order amount exceeds the average total order amount across all customers?
a)
SELECT customer_id FROM Orders GROUP BY customer_id HAVING SUM(amount) > (SELECT AVG(SUM(amount)) FROM Orders GROUP BY customer_id);
b)
SELECT customer_id FROM Orders WHERE amount > (SELECT AVG(amount) FROM Orders);
c)
SELECT customer_id FROM Orders GROUP BY customer_id HAVING SUM(amount) > (SELECT SUM(amount) FROM Orders);
d)
SELECT customer_id FROM Orders GROUP BY customer_id HAVING AVG(amount) > (SELECT AVG(amount) FROM Orders);
90.
Given a table Graph (node1, node2) representing directed edges, which recursive CTE query finds all reachable nodes from node 1?
a)
WITH RECURSIVE Paths AS (SELECT node1 AS start, node2 AS end FROM Graph WHERE node1 = 1 UNION SELECT p.start, g.node2 FROM Paths p JOIN Graph g ON p.end = g.node1) SELECT end FROM Paths;
b)
WITH RECURSIVE Paths AS (SELECT node1, node2 FROM Graph UNION SELECT p.node1, g.node2 FROM Paths p JOIN Graph g ON p.node2 = g.node1) SELECT * FROM Paths;
c)
WITH RECURSIVE Paths AS (SELECT node1 FROM Graph WHERE node1 = 1 UNION SELECT g.node2 FROM Graph g) SELECT * FROM Paths;
d)
WITH RECURSIVE Paths AS (SELECT node1, node2 FROM Graph WHERE node1 = 1 UNION SELECT g.node1, g.node2 FROM Graph g) SELECT node2 FROM Paths;
91.
Given tables Employees (emp_id, emp_name, dept_id, salary) and Departments (dept_id, dept_name), which query uses EXISTS to find employees in departments with dept_id > 10?
a)
SELECT emp_name FROM Employees e WHERE EXISTS (SELECT 1 FROM Departments d WHERE d.dept_id = e.dept_id AND d.dept_id > 10);
b)
SELECT emp_name FROM Employees e WHERE e.dept_id IN (SELECT dept_id FROM Departments WHERE dept_id > 10);
c)
SELECT emp_name FROM Employees e WHERE EXISTS (SELECT dept_id FROM Departments d WHERE d.dept_id > 10);
d)
SELECT emp_name FROM Employees e WHERE e.dept_id > 10;
e)
Both 1 and 2 are correct
92.
Which query using ANY finds employees with a salary greater than at least one employee in dept_id = 5?
a)
SELECT emp_name FROM Employees WHERE salary > ANY (SELECT salary FROM Employees WHERE dept_id = 5);
b)
SELECT emp_name FROM Employees WHERE salary > ALL (SELECT salary FROM Employees WHERE dept_id = 5);
c)
SELECT emp_name FROM Employees WHERE salary IN (SELECT salary FROM Employees WHERE dept_id = 5);
d)
SELECT emp_name FROM Employees WHERE salary > (SELECT MIN(salary) FROM Employees WHERE dept_id = 5);
e)
Both 1 and 4 are correct
93.
What is the key difference between NOT IN and NOT EXISTS in SQL?
a)
NOT IN is faster
b)
NOT EXISTS handles NULLs better
c)
NOT IN is correlated
d)
NOT EXISTS requires a join
e)
NOT IN always returns fewer rows
94.
Which query using ALL identifies products in Products (prod_id, prod_name, price) with a price higher than every product in category_id = 3?
a)
SELECT prod_name FROM Products WHERE price > ALL (SELECT price FROM Products WHERE category_id = 3);
b)
SELECT prod_name FROM Products WHERE price > ANY (SELECT price FROM Products WHERE category_id = 3);
c)
SELECT prod_name FROM Products WHERE price > (SELECT MAX(price) FROM Products WHERE category_id = 3);
d)
SELECT prod_name FROM Products WHERE price NOT IN (SELECT price FROM Products WHERE category_id = 3);
e)
Both 1 and 3 are correct
95.
Given a table Orders (order_id, customer_id, amount), which query uses NOT IN to find customers with no orders?
a)
SELECT customer_id FROM Customers WHERE customer_id NOT IN (SELECT customer_id FROM Orders);
b)
SELECT customer_id FROM Customers c WHERE NOT EXISTS (SELECT 1 FROM Orders o WHERE o.customer_id = c.customer_id);
c)
SELECT customer_id FROM Customers c LEFT JOIN Orders o ON c.customer_id = o.customer_id WHERE o.order_id IS NULL;
d)
Both 1 and 2 are correct
e)
Both 1 and 3 are correct
96.
Which query using EXISTS is most efficient for finding employees who have placed orders in Orders (order_id, emp_id, amount)?
a)
SELECT emp_name FROM Employees e WHERE EXISTS (SELECT 1 FROM Orders o WHERE o.emp_id = e.emp_id);
b)
SELECT emp_name FROM Employees e WHERE e.emp_id IN (SELECT emp_id FROM Orders);
c)
SELECT emp_name FROM Employees e JOIN Orders o ON e.emp_id = o.emp_id;
d)
SELECT emp_name FROM Employees e WHERE e.emp_id = ANY (SELECT emp_id FROM Orders);
e)
Both 1 and 2 are correct
97.
What is the output of SELECT emp_name FROM Employees WHERE salary > ANY (SELECT salary FROM Employees WHERE dept_id = 0) if the subquery returns no rows?
a)
All employees
b)
No employees
c)
Employees with salary > 0
d)
Employees with non-NULL salary
e)
Throws an error
98.
Which query using NOT EXISTS finds departments in Departments (dept_id, dept_name) with no employees in Employees (emp_id, dept_id, salary)?
a)
SELECT dept_name FROM Departments d WHERE NOT EXISTS (SELECT 1 FROM Employees e WHERE e.dept_id = d.dept_id);
b)
SELECT dept_name FROM Departments d WHERE d.dept_id NOT IN (SELECT dept_id FROM Employees);
c)
SELECT dept_name FROM Departments d LEFT JOIN Employees e ON d.dept_id = e.dept_id WHERE e.emp_id IS NULL;
d)
Both 1 and 2 are correct
e)
Both 1 and 3 are correct
99.
Which query using ALL is equivalent to finding employees with a salary less than the minimum salary in dept_id = 10?
a)
SELECT emp_name FROM Employees WHERE salary < ALL (SELECT salary FROM Employees WHERE dept_id = 10);
b)
SELECT emp_name FROM Employees WHERE salary < ANY (SELECT salary FROM Employees WHERE dept_id = 10);
c)
SELECT emp_name FROM Employees WHERE salary < (SELECT MIN(salary) FROM Employees WHERE dept_id = 10);
d)
SELECT emp_name FROM Employees WHERE salary NOT IN (SELECT salary FROM Employees WHERE dept_id = 10);
e)
Both 1 and 3 are correct
100.
Given a table Sales (sale_id, prod_id, quantity), which query using ANY finds products with at least one sale of quantity > 100?
a)
SELECT prod_id FROM Sales WHERE quantity > ANY (SELECT quantity FROM Sales WHERE quantity > 100);
b)
SELECT prod_id FROM Sales WHERE quantity > 100;
c)
SELECT prod_id FROM Sales s WHERE EXISTS (SELECT 1 FROM Sales s2 WHERE s2.prod_id = s.prod_id AND s2.quantity > 100);
d)
Both 2 and 3 are correct
e)
Both 1 and 2 are correct
Reset
