MCQS
Section 1: JOINs & Set Operations (1–10)
1. Which join returns all records when there is a match in either left or right table?
A) INNER JOIN
B) FULL OUTER JOIN
C) LEFT JOIN
D) CROSS JOIN
✅ Answer: B
2. What is the result of this query if table A has 3 rows and B has 2 rows?
sql
CopyEdit
SELECT * FROM A CROSS JOIN B;
A) 3 rows
B) 2 rows
C) 6 rows
D) Error
✅ Answer: C
3. Which JOIN will return unmatched rows from the left table as NULL in right columns?
A) LEFT JOIN
B) INNER JOIN
C) RIGHT JOIN
D) SELF JOIN
✅ Answer: A
4. Consider:
sql
CopyEdit
SELECT * FROM Employees e
FULL OUTER JOIN Departments d ON e.DeptID = d.DeptID;
What happens if Employees has unmatched DeptID?
A) Error
B) Null values in Department columns
C) Query fails
D) Skips unmatched
✅ Answer: B
5. Which join is best for hierarchical data?
A) CROSS JOIN
B) LEFT JOIN
C) SELF JOIN
D) FULL JOIN
✅ Answer: C
6. Which clause must follow a GROUP BY?
A) JOIN
B) ORDER BY
C) HAVING
D) WHERE
✅ Answer: C
7. Which keyword is needed to combine results from two SELECTs including duplicates?
A) JOIN
B) UNION
C) UNION ALL
D) INTERSECT
✅ Answer: C
8. Which join would you use to find employees without departments?
A) INNER JOIN
B) LEFT JOIN with WHERE d.DeptID IS NULL
C) FULL JOIN
D) RIGHT JOIN
✅ Answer: B
9. What does this return?
sql
CopyEdit
SELECT * FROM Employees e
JOIN Departments d ON e.DeptID = d.DeptID
WHERE d.DeptID IS NULL;
A) All employees
B) All departments
C) No rows
D) Only unmatched
✅ Answer: C
10. What does ON 1=1 simulate in a JOIN?
A) Condition
B) Error
C) CROSS JOIN
D) Filter
✅ Answer: C
🔹 Section 2: CAST, CONVERT, Error Handling (11–20)
11. What does this return?
sql
CopyEdit
SELECT CAST('2024-04-10' AS DATETIME);
A) 2024/04/10 00:00:00
B) Error
C) NULL
D) Syntax error
✅ Answer: A
12. Which function is safer for unpredictable string conversion?
A) CAST
B) CONVERT
C) TRY_CAST
D) PARSE
✅ Answer: C
13. What is the output?
sql
CopyEdit
SELECT TRY_CAST('abc' AS INT);
A) 0
B) 1
C) NULL
D) Error
✅ Answer: C
14. What will happen?
sql
CopyEdit
SELECT CONVERT(DATE, '2021-15-01');
A) Returns NULL
B) Returns a valid date
C) Error
D) 2021-01-15
✅ Answer: C
15. What is the result of dividing by zero using TRY...CATCH?
sql
CopyEdit
BEGIN TRY
SELECT 5 / 0;
END TRY
BEGIN CATCH
SELECT 'Error Occurred';
END CATCH
A) Query fails
B) 0
C) Error Occurred
D) NULL
✅ Answer: C
16. What is the result of CONVERT(VARCHAR, GETDATE(), 120)?
A) yyyy-mm-dd hh:mi:ss
B) mm/dd/yyyy
C) Error
D) NULL
✅ Answer: A
17. What does TRY_CONVERT(INT, '123abc') return?
A) Error
B) 123
C) 0
D) NULL
✅ Answer: D
18. Which is best for avoiding failure in a batch script?
A) CAST
B) TRY_CAST
C) PARSE
D) CONVERT
✅ Answer: B
19. Which error function shows the last error number?
A) ERROR_MESSAGE()
B) @@ERROR
C) ERROR_NUMBER()
D) ERROR_STATE()
✅ Answer: C
20. Where is BEGIN TRY...END CATCH block used most commonly?
A) Joins
B) UDFs
C) Transactions
D) Triggers
✅ Answer: C
🔹 Section 3: Procedures, Transactions, Triggers (21–30)
21. What is used to create a stored procedure?
A) CREATE PROC
B) CREATE PROCEDURE
C) NEW PROCEDURE
D) DECLARE PROCEDURE
✅ Answer: B
22. What happens if you forget AS in a procedure?
A) Nothing
B) Warning
C) Syntax Error
D) Skipped
✅ Answer: C
23. Which command starts a transaction?
A) START TRANSACTION
B) BEGIN TRAN
C) TRANSACTION START
D) INIT TRAN
✅ Answer: B
24. Which function checks for errors in transactions?
A) ERROR_CODE()
B) ERROR_MSG()
C) @@ERROR
D) NULL
✅ Answer: C
25. What happens after COMMIT?
A) Transaction closes
B) Can rollback
C) Table deleted
D) Index resets
✅ Answer: A
26. Which trigger runs after an insert?
A) INSTEAD OF
B) AFTER
C) BEFORE
D) DEFAULT
✅ Answer: B
27. Can a trigger insert into another table?
A) No
B) Yes
C) Only in MySQL
D) With cursor
✅ Answer: B
28. Which pseudo-table stores inserted values?
A) Inserted
B) Input
C) Action
D) Source
✅ Answer: A
29. Can a trigger call a procedure?
A) No
B) Yes
C) Only on DELETE
D) Only on SELECT
✅ Answer: B
30. Where is a trigger stored?
A) sys.triggers
B) sys.tables
C) sys.columns
D) sys.procedures
✅ Answer: A
🔹 Section 4: Index, UDFs, CTEs (31–40)
31. What is the correct syntax to create a non-clustered index?
sql
CopyEdit
CREATE NONCLUSTERED INDEX idx_col ON MyTable(ColumnName);
A) Correct
B) Wrong
C) Needs PRIMARY
D) Needs CONSTRAINT
✅ Answer: A
32. What happens when you drop an index?
A) Table deleted
B) Performance drops
C) Query fails
D) Index renames
✅ Answer: B
33. Which type of function returns a value?
A) Table-valued
B) Scalar-valued
C) Inline
D) Trigger
✅ Answer: B
34. Which is true for UDFs in SQL Server?
A) Cannot use TRY...CATCH
B) Can return table or scalar
C) Cannot be used in SELECT
D) Only one per DB
✅ Answer: B
35. What is the keyword for a CTE?
A) DEFINE
B) DECLARE
C) WITH
D) CTE
✅ Answer: C
36. What does this return?
sql
CopyEdit
WITH Top5 AS (
SELECT TOP 5 * FROM Employees ORDER BY Salary DESC
)
SELECT * FROM Top5;
A) 5 lowest salaries
B) Error
C) 5 highest salaries
D) NULL
✅ Answer: C
37. Which CTE is recursive?
sql
CopyEdit
WITH CTE AS (
SELECT ...
UNION ALL
SELECT ...
)
A) Recursive
B) Subquery
C) Inline
D) Scalar
✅ Answer: A
38. Can UDFs use INSERT/DELETE?
A) Yes
B) No
C) Only INSERT
D) With permission
✅ Answer: B
39. Can you index a computed column?
A) No
B) Yes
C) Only if deterministic
D) Only if clustered
✅ Answer: C
40. Which view helps check all indexes?
A) sys.indexes
B) sys.views
C) sys.tables
D) sys.columns
✅ Answer: A
🔹 Section 5: Output-Based & Query Logic (41–50)
41. What is the result?
sql
CopyEdit
SELECT 10 / 3;
A) 3.333
B) 3
C) Error
D) NULL
✅ Answer: B
42. What is the result of:
sql
CopyEdit
SELECT COUNT(*) FROM Orders WHERE TotalAmount > 1000;
If Orders has 3 records: 2500, 900, 1100
A) 2
B) 3
C) 1
D) 0
✅ Answer: A
43. What does this do?
sql
CopyEdit
SELECT * FROM Employees WHERE Salary BETWEEN 50000 AND 80000;
A) Range filter
B) Joins
C) Error
D) Updates salary
✅ Answer: A
44. What will this return?
sql
CopyEdit
SELECT COALESCE(NULL, NULL, 'Hello', 'World');
A) NULL
B) Hello
C) World
D) Error
✅ Answer: B
45. What does this return?
sql
CopyEdit
SELECT DATEDIFF(DAY, '2024-01-01', '2024-01-10');
A) 9
B) 10
C) 11
D) 8
✅ Answer: A
46. Which function returns only date (no time)?
A) GETDATE()
B) CURRENT_TIMESTAMP
C) CONVERT(DATE, GETDATE())
D) SYSUTCDATETIME()
✅ Answer: C
47. Which function returns number of characters in string?
A) CHAR()
B) LENGTH()
C) LEN()
D) COUNT()
✅ Answer: C
48. Which function converts to uppercase?
A) CAPITAL()
B) UPPER()
C) TOUPPER()
D) CAPS()
✅ Answer: B
49. Which command will delete all data but keep structure?
A) DROP
B) DELETE
C) TRUNCATE
D) REMOVE
✅ Answer: C
50. Which clause is used to filter grouped data?
A) WHERE
B) JOIN
C) HAVING
D) ORDER BY
✅ Answer: C
