Diploma in Databases and T-SQL – Second Assessment Answers
Complete this assessment to review your understanding of the following modules:
-Querying Tables with Select
-Querying Multiple Tables with Joins and Using Set Operators
-Using Functions and Aggregating Data
-Working with Subqueries, Table Expressions and Data
-Modifying Data and Using T-SQL in Programming
– Error Handling and Transactions
To effectively use this website to answer your alison questions with ease, checkout this short tutorial I put together to help you in the video below.
1. _______ tables are named query expressions created within an outer SELECT statement. They are virtual tables for simplifying a query. Fill in the blank.
2. Which of these commands clears the entire table? Choose one.
3. __________applies the right table expression to each row in left table, similar to CROSS JOIN but you can correlate data between sources. Drag the correct answer into the box.
4. ________ data is rotating data from a rows-based orientation to a column-based orientation. Fill in the blank.
5. In SQL Server Error messages are in ______________. Drag the correct answer into the box.
6. Temporary tables are stored in _____ until you end your session with the database. Drag the correct answer into the box.
7. Match the data types with their category.
Other -> xml
string -> varchar
Exact numeric -> int
Approximate numeric -> float
8. True or False: An Outer Join keeps all the items from the inner table and then filters out what does not match on the outer table.
9. What does CTE stand for? Choose one.
Command Table Expressions
Common Table Expressions
Common Table Extensions
Command Table Extensions
10. Which of these apply when creating a derived table?
The table must not be referred to multiple times in the same query
The table have the same names for all columns they have in their original tables
The table must use an order by clause
The table must have an alias
11. When using JOINs there are two places you can place your join, either in the WHERE and FROM clauses. Which is the better method? Choose one.
12. Whenever you run a SELECT statement its results are stored on a _______ table. Drag the correct answer into the box.
13. The THROW command is for throwing an explicit error number, message and state, and it is a replacement for_____________. Drag the correct answer into the box.
14. _______ functions are applied to a window, or set of rows, including ranking, offset, aggregate and distribution functions. Fill in the blank.
15. Which type of join creates Cartesian products on purpose? Choose one.
16. _______ conversion allows compatible data types to be automatically converted. Fill in the blank.
17. True or False: A transaction affects the other operations going on in the database while it is running, so all other operations must be shut down while a transaction is running.
18. What character do you use for commenting a single line of code? Choose one.
19. _________ functions would include COUNT, AVG, MAX, MIN, SUM. Fill in the blank.
20. ________ is used to check if a predicate is TRUE to perform a task, otherwise perform a different task. Drag the correct answer into the box.
21. GROUPING SETS subclause builds on the ________ clause. Drag the correct answer into the box.
22. When using UPDATE, you must specify with a _________ clause or all rows will be affected. Fill in the blank.
23. Most __________ are self- contained and have no connection with the outer query other than passing its results. Fill in the blank.
24. _____ returns a result set of distinct rows combined from all statements removing duplicates during query processing. Fill in the blank.
25. The __ predicate determines whether a specified value matchs any value in a subquery or list. Fill in the blank.
26. When using the DELETE statement, you must use the _____ clause or all rows will be deleted. Fill in the blank.
27. The WHERE clause is processed before GROUP BY, the _________ clause is processed after GROUP BY. Fill in the blank.
28. If you want to generate identity for two different tables, with neither having the same identifier you use _________. Fill in the blank.
29. Which of these only lasts for the current batch of commands you’re executing, and as soon as you start a new set its gone? Choose one.
30. True or False: When unpivoting data you will get back all the data you pivoted, even columns not on the pivoted table.
31. When using the SELECT statement to select data from your database without using ORDER BY, your results come out in order of your _______ key. Fill in the blank.
32. True or False: An Outer Join keeps all the items from the inner table and then filters out what does not match on the outer table.
33. A TRY ________ block involves trying a bit of code that may cause an error. Fill in the blank.
34. You can insert the results returned by the query or stored procedure into an existing table using ______ _______. Drag the correct answer into the box.
35. Transact-SQL is __________’s implementation of SQL syntax. It is the same as standard SQL with a few specific twists, to use for their database products. Drag the correct answer into the box.
36. Temporary tables are used to hold temporary results. They are created with a ___ prefix. Drag the correct answer into the box.
37. The SELECT statement has a number of clauses which help refine your query search. Which one sorts the output? Choose one.
38. ____ represents a missing or unknown value. Fill in the blank.
39. True or False: The AND predicate combine two Boolean expressions and returns TRUE if either is TRUE.
40. Match the operator to its description.
Provides shortcut for defining grouping sets in which all possible combinations of grouping sets -> CUBE
Provides shortcut for defining grouping sets with combinations that assume input columns form a hierarchy -> ROLLUP
41. _____ loop enables code to execute in a repeating loop as long as the predicate evaluates to TRUE, ending when no longer true. Fill in the blank.
42. _____ modifies data based on a conditions, when the source matches the target/ no match in the target, when the target has no match in the source. Fill in the blank.
43. The __________ function returns the item at the specified index from a list of values. Fill in the blank.
44. The _____________ clause provides a search condition that each group must satisfy. Fill in the blank.
45. ___ allows you to limit the number or percentage of rows returned by a query. Fill in the blank.
46. ______ is the core statement for querying tables to get data out of the database. Type the correct answer in the textbox.