Download Transact SQL Certificate Practice Exam: Querying Data and more Exams Technology in PDF only on Docsity!
Practice Exam
Question 1. Which clause must appear immediately after the SELECT list in a basic Transact‑SQL query? A) FROM B) WHERE C) GROUP BY D) ORDER BY Answer: A Explanation: The correct order of clauses is SELECT → FROM → WHERE → GROUP BY → HAVING → ORDER BY. Question 2. In a WHERE clause, which operator is used to test for a range of values inclusive of the boundaries? A) BETWEEN B) IN C) LIKE D) = Answer: A Explanation: BETWEEN checks if a value lies between two others, including the end points. Question 3. Which keyword removes duplicate rows from the result set? A) DISTINCT B) UNIQUE C) GROUP BY D) HAVING Answer: A
Practice Exam
Explanation: DISTINCT eliminates duplicate rows; UNIQUE is not a T‑SQL keyword for this purpose. Question 4. How does SQL Server treat NULL in a three‑valued logic expression? A) As FALSE B) As TRUE C) As UNKNOWN D) As ZERO Answer: C Explanation: Any logical operation involving NULL yields UNKNOWN, which is treated as FALSE in WHERE filtering. Question 5. Which set operator returns all rows from both queries, including duplicates? A) UNION ALL B) UNION C) INTERSECT D) EXCEPT Answer: A Explanation: UNION ALL does not eliminate duplicates, whereas UNION does. Question 6. Which join type returns only rows that have matching values in both tables? A) INNER JOIN B) LEFT OUTER JOIN C) RIGHT OUTER JOIN D) FULL OUTER JOIN Answer: A
Practice Exam
Answer: A Explanation: TOP n returns the first n rows; FETCH NEXT is used with OFFSET for paging. Question 10. Which clause would you use to filter groups after aggregation? A) HAVING B) WHERE C) ON D) GROUP BY Answer: A Explanation: HAVING filters aggregated results; WHERE filters rows before aggregation. Question 11. Which scalar function returns the current date and time? A) GETDATE() B) SYSDATETIME() C) CURRENT_TIMESTAMP D) All of the above Answer: D Explanation: All three return the current date/time; SYSDATETIME has higher precision. Question 12. Which aggregate function counts all rows, including those with NULL in the column being counted? A) COUNT(*) B) COUNT(column) C) SUM(column) D) AVG(column)
Practice Exam
Answer: A Explanation: COUNT(*) counts every row; COUNT(column) ignores NULLs. Question 13. Which ranking function assigns the same rank to ties and leaves gaps in the sequence? A) RANK() B) DENSE_RANK() C) ROW_NUMBER() D) NTILE() Answer: A Explanation: RANK() gives identical rank to ties and skips subsequent numbers. Question 14. Which analytic function returns the value of a column from the preceding row within the same partition? A) LAG() B) LEAD() C) FIRST_VALUE() D) LAST_VALUE() Answer: A Explanation: LAG(column, offset) accesses a previous row’s value. Question 15. Which clause is required when using a window function to define the order of rows? A) ORDER BY within OVER() B) GROUP BY C) HAVING
Practice Exam
C) DROP TABLE
D) MERGE
Answer: A Explanation: DELETE logs each row; TRUNCATE is minimally logged. Question 19. Which statement merges data from a source table into a target table, performing INSERT, UPDATE, or DELETE as needed? A) MERGE B) UPDATE … FROM … C) INSERT … SELECT … D) UPSERT Answer: A Explanation: MERGE implements conditional insert, update, or delete in one statement. Question 20. Which clause captures rows affected by a DML statement into a table variable? A) OUTPUT INTO B) RETURNING INTO C) INSERTED INTO D) SELECT INTO Answer: A Explanation: OUTPUT … INTO stores the affected rows. Question 21. Which subquery type can be used in the SELECT list to return a scalar value per row? A) Scalar subquery
Practice Exam
B) Correlated subquery C) Multi‑row subquery D) EXISTS subquery Answer: A Explanation: A scalar subquery returns a single value, suitable for the SELECT list. Question 22. What is the primary difference between CROSS APPLY and OUTER APPLY? A) OUTER APPLY returns rows from the left side even when the right side returns no rows. B) CROSS APPLY returns rows from the right side only. C) Both behave identically. D) CROSS APPLY can be used only with table‑valued functions. Answer: A Explanation: OUTER APPLY preserves left rows like a LEFT OUTER JOIN; CROSS APPLY behaves like an INNER JOIN. Question 23. Which keyword introduces a Common Table Expression? A) WITH B) CTE C) BEGIN D) DECLARE Answer: A Explanation: WITH starts a CTE definition. Question 24. Which of the following is true about a recursive CTE? A) It must contain an anchor member and a recursive member separated by UNION ALL.
Practice Exam
Question 27. Which operator produces a result set that contains one row for each distinct value of the pivot column, turning row values into column headers? A) PIVOT B) UNPIVOT C) CROSS APPLY D) GROUP BY Answer: A Explanation: PIVOT rotates rows into columns. Question 28. In a PIVOT operation, what happens to NULL values that become column headers? A) They appear as NULL in the result set. B) They are omitted from the output. C) They are replaced with 0 automatically. D) The query fails. Answer: A Explanation: NULL column headers are allowed and display as NULL. Question 29. Which function is used to transform JSON text into a relational rowset? A) OPENJSON B) JSON_VALUE C) JSON_QUERY D) FOR JSON PATH Answer: A Explanation: OPENJSON parses JSON and returns rows and columns.
Practice Exam
Question 30. Which T‑SQL clause generates JSON output from a result set? A) FOR JSON PATH B) FOR XML AUTO C) SELECT JSON D) JSON_OUTPUT() Answer: A Explanation: FOR JSON PATH (or AUTO) formats query results as JSON. Question 31. Which system‑versioned temporal table clause retrieves data as of a specific point in time? A) FOR SYSTEM_TIME AS OF … B) FROM … AT TIME … C) AS OF … D) SYSTEM_TIME BETWEEN … Answer: A Explanation: FOR SYSTEM_TIME AS OF returns the row version valid at that moment. Question 32. Which XML method extracts a scalar value from an XML column? A) .value() B) .query() C) .nodes() D) .exist() Answer: A Explanation: .value() returns a single value from an XML instance.
Practice Exam
Question 36. In a DML trigger, which pseudo‑tables contain the rows affected by the operation? A) inserted and deleted B) new and old C) added and removed D) source and target Answer: A Explanation: inserted holds new rows; deleted holds old rows. Question 37. Which transaction isolation level allows dirty reads? A) READ UNCOMMITTED B) READ COMMITTED C) REPEATABLE READ D) SERIALIZABLE Answer: A Explanation: READ UNCOMMITTED does not place shared locks, permitting dirty reads. Question 38. Which T‑SQL construct is used for structured error handling? A) TRY…CATCH B) IFERROR C) ON ERROR D) THROW ONLY Answer: A Explanation: TRY…CATCH captures errors and allows handling logic.
Practice Exam
Question 39. Which function raises a custom error and aborts the batch? A) THROW B) RAISERROR with severity 20+ C) PRINT D) RAISEERROR (non‑existent) Answer: A Explanation: THROW generates an exception; RAISERROR can also, but THROW is the modern, always aborting method. Question 40. Which error function returns the message text of the caught error? A) ERROR_MESSAGE() B) ERROR_NUMBER() C) ERROR_STATE() D) ERROR_LINE() Answer: A Explanation: ERROR_MESSAGE() provides the textual description. Question 41. Which clause is mandatory when using the OFFSET‑FETCH pagination feature? A) ORDER BY B) GROUP BY C) HAVING D) UNION Answer: A Explanation: OFFSET requires a deterministic order, supplied by ORDER BY.
Practice Exam
Question 45. Which join type is equivalent to an INNER JOIN when the ON condition is always true? A) CROSS JOIN B) LEFT OUTER JOIN C) FULL OUTER JOIN D) SELF JOIN Answer: A Explanation: CROSS JOIN returns all combinations; with a true ON, INNER JOIN behaves the same. Question 46. Which clause can be used to rename a column in the result set without altering the underlying table? A) AS alias B) RENAME TO C) CHANGE alias D) SET alias Answer: A Explanation: The AS keyword assigns an alias to the column in the output. Question 47. Which built‑in function calculates the number of days between two dates? A) DATEDIFF(day, startdate, enddate) B) DATEADD(day, startdate, enddate) C) DATEPART(day, enddate) - DATEPART(day, startdate) D) DAY(enddate) - DAY(startdate) Answer: A Explanation: DATEDIFF returns the integer count of datepart boundaries crossed.
Practice Exam
Question 48. Which T‑SQL statement is used to remove a column from a table? A) ALTER TABLE dbo.Table DROP COLUMN ColumnName; B) DELETE COLUMN ColumnName FROM dbo.Table; C) ALTER TABLE dbo.Table REMOVE ColumnName; D) DROP COLUMN ColumnName FROM dbo.Table; Answer: A Explanation: ALTER TABLE … DROP COLUMN removes the specified column. Question 49. Which function returns the current session’s user name? A) SUSER_SNAME() B) USER_NAME() C) CURRENT_USER D) SESSION_USER Answer: C (or D also returns the same; but CURRENT_USER is the direct T‑SQL keyword) Explanation: CURRENT_USER returns the name of the user context for the session. Question 50. Which keyword is used to create a temporary table that persists for the duration of the session? A) CREATE TABLE #Temp (…); B) CREATE TEMP TABLE #Temp (…); C) DECLARE TABLE #Temp (…); D) MAKE TABLE #Temp (…); Answer: A Explanation: Prefixing the table name with # creates a local temporary table.
Practice Exam
Question 54. Which statement correctly creates a view that selects only active customers? A) CREATE VIEW dbo.ActiveCustomers AS SELECT * FROM dbo.Customers WHERE IsActive = 1; B) CREATE VIEW dbo.ActiveCustomers SELECT * FROM dbo.Customers WHERE IsActive = 1; C) CREATE VIEW dbo.ActiveCustomers AS SELECT * FROM dbo.Customers IF IsActive = 1; D) CREATE VIEW dbo.ActiveCustomers FROM dbo.Customers WHERE IsActive = 1; Answer: A Explanation: Proper syntax includes AS and the SELECT statement. Question 55. Which operator is used to test for the existence of rows returned by a subquery? A) EXISTS B) IN C) ANY D) SOME Answer: A Explanation: EXISTS returns TRUE if the subquery yields at least one row. Question 56. Which clause can be used to limit the number of rows returned by a SELECT statement without using TOP? A) OFFSET … FETCH NEXT … B) LIMIT … C) ROWCOUNT … D) SAMPLE … Answer: A Explanation: OFFSET‑FETCH provides paging and row limiting.
Practice Exam
Question 57. Which function returns the absolute value of a numeric expression? A) ABS() B) POWER() C) SQRT() D) LOG() Answer: A Explanation: ABS returns the magnitude without sign. Question 58. Which statement removes all rows from a table while resetting identity seed? A) TRUNCATE TABLE dbo.Table; B) DELETE FROM dbo.Table; C) DROP TABLE dbo.Table; D) ALTER TABLE dbo.Table RESEED; Answer: A Explanation: TRUNCATE removes rows and resets identity; DELETE does not reset identity. Question 59. Which keyword can be used to return the number of rows affected by the last statement? A) @@ROWCOUNT B) ROWCOUNT() C) @@ERROR D) @@IDENTITY Answer: A Explanation: @@ROWCOUNT holds the count of rows affected by the most recent DML.
