Diploma in Databases and T-SQL – Second Assessment Answers

Complete this assessment to review your understanding of the following modules:

-Introducing Transact-SQL

-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

-T-SQL

– 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.

Ans: derived

2. Which of these commands clears the entire table? Choose one.

CLEAR TABLE

TRUNCATE TABLE

DELETE TABLE

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.

PIVOT

OUTER APPLY

CROSS APPLY

UNION

4. ________ data is rotating data from a rows-based orientation to a column-based orientation. Fill in the blank.

Ans: pivoting

5. In SQL Server Error messages are in ______________. Drag the correct answer into the box.

sys.info

sys.alerts

sys.errors

sys.messages

6. Temporary tables are stored in _____ until you end your session with the database. Drag the correct answer into the box.

Databases

System Views

Views

tempdb

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.

True

False

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.

FROM

WHERE

12. Whenever you run a SELECT statement its results are stored on a _______ table. Drag the correct answer into the box.

Virtual

Temporary

System

Window

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.

TRY CATCH

RAISERROR

EXECUTE

14. _______ functions are applied to a window, or set of rows, including ranking, offset, aggregate and distribution functions. Fill in the blank.

Ans: window

15. Which type of join creates Cartesian products on purpose? Choose one.

Inner join

Outer join

Self join

Cross join

16. _______ conversion allows compatible data types to be automatically converted. Fill in the blank.

Ans: implicit

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.

True

False

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.

Ans: aggregate

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.

IF

IF ELSE

DO WHILE

WHILE

21. GROUPING SETS subclause builds on the ________ clause. Drag the correct answer into the box.

SELECT

GROUP BY

HAVING

UPDATE

22. When using UPDATE, you must specify with a _________ clause or all rows will be affected. Fill in the blank.

Ans: where

23. Most __________ are self- contained and have no connection with the outer query other than passing its results. Fill in the blank.

Ans: subqueries

24. _____ returns a result set of distinct rows combined from all statements removing duplicates during query processing. Fill in the blank.

Ans: union

25. The __ predicate determines whether a specified value matchs any value in a subquery or list. Fill in the blank.

Ans: in

26. When using the DELETE statement, you must use the _____ clause or all rows will be deleted. Fill in the blank.

Ans: where

27. The WHERE clause is processed before GROUP BY, the _________ clause is processed after GROUP BY. Fill in the blank.

Ans: having

28. If you want to generate identity for two different tables, with neither having the same identifier you use _________. Fill in the blank.

Ans: sequences

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.

Table Variables

Views

Temporary tables

Derived tables

30. True or False: When unpivoting data you will get back all the data you pivoted, even columns not on the pivoted table.

True

False

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.

Ans: primary

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.

True

False

33. A TRY ________ block involves trying a bit of code that may cause an error. Fill in the blank.

Ans: catch

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.

SELECT NEXT

INSERT VALUES

INSERT SELECT

SELECT INTO

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.

Apple Linux

Windows

Microsoft

Apple

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.

SET

ORDER BY

HAVING

WHERE

38. ____ represents a missing or unknown value. Fill in the blank.

Ans: null

39. True or False: The AND predicate combine two Boolean expressions and returns TRUE if either is TRUE.

True

False

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.

Ans: while

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.

Ans: merge

43. The __________ function returns the item at the specified index from a list of values. Fill in the blank.

Ans: choose

44. The _____________ clause provides a search condition that each group must satisfy. Fill in the blank.

Ans: having

45. ___ allows you to limit the number or percentage of rows returned by a query. Fill in the blank.

Ans: top

46. ______ is the core statement for querying tables to get data out of the database. Type the correct answer in the textbox.

Ans: select