Module 15: Diploma in Databases and T-SQL – Final Assessment Answers

Posted on by

Complete this assessment to review your understanding of the following modules

  • -Introducing Database Concepts
  • -Relational Concepts
  • -Creating Databases and Database Objects
  • -Using DML Statements
  • -SQL Server Administration Fundamentals
  • -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. ______ functions operate on elements from a single row as inputs and return a single value. Fill in the blank.

Ans: scalar

2. Temporary tables are used to hold temporary results. They are created with a ___ prefix. Drag the correct answer into the box.

*

%

#

@

Ans: #

3. What data type provides support for international characters such as the Chinese or Russian alphabet? Choose one.

Float

Varchar

Unicode

ntext

xml

4. True or False: You can specify your order of query results to be ascending or descending using the ASC or DESC. The default order is the order the data in your table is in.

True

False

5. Match the term with its correct function.

Combines two Boolean expressions and returns TRUE if either is TRUE -> OR

Combines two Boolean expressions and returns TRUE one when both are TRUE -> AND

Specifies and inclusive range to test -> BETWEEN

6. Database are stored on database servers which are dedicated ________ or virtual servers that host the database files. Fill in the blank.

Ans: physical

7. ______ queries return only distinct rows that appear in the first table but not the second table. Fill in the blank.

Ans: except

8. For the _______ form of normalization if the contents of a group of fields apply to more that a single record, ensure those fields are in the table. Drag the correct answer into the box.

Fourth

First

Second

Third

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

True

False

10. There are MIN, MAX, AVG and ______ aggregate functions. Fill in the blank.

Ans: count

11. True or False – Unique constraint allows you to enforce uniqueness in columns other than the primary key.

True

False

12. DBMS stands for what? Choose one.

Database Manipulation System

Database Master System

Database Management System

14. A ___________ backup contains all the data that has change since the last backup of the same type. Drag the correct answer into the box.

Synthetic

Local

Differential

Incremental

Full

15. GUI stands for what? Choose one.

Graphical User Interface

Graphical User Interaction

Graphical User Incorporation

16. A ___________ is a group of task that together form a single unit of work. Fill in the blank.

Ans: transaction

17. Which of these are true about user-defined functions? Choose three.

Can JOIN user-defined functions

Cannot JOIN stored procedures

Can be called from within another SQL statement

Can use nondeterministic functions

Cannot be used to modify SQL Server configuration

18. Which of these is a column or combination of columns that are used to establish a link between data in two tables. ? Choose one.

Unique key

Primary key

Foreign key

19. Which SELECT statement will return the ids and names of the employees that work in engineering and design? Choose one.

SELECT First Name Last Name Id Num Address FROM Employees WHERE JobTitle = ‘Engineer’ or JobTitle = ‘Designer’

SELECT FirstName, LastName, IdNum FROM Employees WHERE JobTitle = ‘Engineer’ or JobTitle = ‘Designer’

SELECT * FROM Employees WHERE JobTitle = ‘Engineer’ and JobTitle = ‘Designer’

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.

WHILE

IF ELSE

IF

DO WHILE

21. Match the data types with their category.

Other -> xml

string -> varchar

Exact numeric -> int

Approximate numeric -> float

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

Ans: where

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

DELETE TABLE

CLEAR TABLE

TRUNCATE TABLE

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

Self join

Outer join

Inner join

Cross join

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

26. While _________ are not essential for your code to work as they do not affect it, they are important for describing your code. Fill in the blank.

Ans: comments

27. Your primary key on one table can be a _______ key on another table. Fill in the blank.

Ans: foreign

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

Ans: implicit

29. A primary key must be unique but sometimes it may not be possible to make it unique using one column, so you use multiple columns instead. This is called a ___________ key.”Fill in the blank.

Ans: composite

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

SELECT

HAVING

GROUP BY

UPDATE

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

Ans: top

32. True or False – Foreign key constraint allows you to establish a link between data in tow tables, but you can only use a single column.

True

False

33. A view is a ________ table consisting of different columns form one or more tables. Fill in the blank.

Ans: virtual

34. The _______ user account is included in every database and is used by any user who accesses the database but does not have a user account within the database. Type the correct answer into the text box. Fill in the blank.

Ans: guest

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

Ans: having

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

Ans: sequences

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

Ans: where

38. When using the UPDATE and DELETE statements if you don’t put in a ___________ clause it causes all the information in the table to be updated or deleted. Fill in the blank.

Ans: bulk

39. True or False – A primary key can contain a null value.

True

False

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

Ans: pivoting

41. What does DML stand for? Choose one.

Data Management Language

Data Manipulation Language

Data Method Language

42. Naming conventions for your code and objects is considered good practice one of the most common types is _________ where the first letter of the identifier and the first letter or every subsequent concatenated word is capitalized. Drag the correct answer into the box.

Uppercamelcase

camelcase

pascalcase

underscore

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

WHERE

FROM

44. The first normal form means the data is in an entity format which means which of the following conditions have been met? Choose three.

Ensure repeating groups have individual tables

Create separate table for each set of related data

Eliminate repeating groups in individual tables

Identify each set of related data with primary key

Identify each set of related data with foreign key

45. The ______ clause allows you to combine related data from multiple tables as one result set. Type the correct answer into the text box.

Ans: join

46. _____ JOINS return all rows from the left table with all rows from the right table. Drag the correct answer into the box.

INNER

CROSS

INNER OUTER

47. Which of these apply when creating a derived table?

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

The table must not be referred to multiple times in the same query

48. Which of these is an example of RDBMS? Choose one.

MS Excel

MS Access

MS Outlook

49. What character do you use for commenting a single line of code? Choose one.

*/

//

—

/*

Ans: —

50. Match the form of descriptions to the terms.

An organized collection of data, typically stored in electronic format. -> Database

A collection of applications that allows users and other programs to capture and analyze data by providing you with the -> Relational Database Management System

A software system designed to allow the definition, creation, querying and updating of data stored in relational database -> Database Management System

51. _________ functions would include COUNT, AVG, MAX, MIN, SUM. Fill in the blank.

Ans: aggregate

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

Ans: null

53. ______________ is the process of organizing data in a database that include establishing relationships between the tables. Fill in the blank.

Ans: normalisation

54. When using the SELECT statement to retrieve all columns what symbol do you use instead of manually typing out all fields to return all? Choose one.

*

#

&

%

Ans: *

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

Ans: in

56. In the three-tiered approach to accessing content in a database, the last tier for accessing content is called __________. Fill in the blank.

Ans: permissions

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

Ans: choose

58. SQL stands for what? Choose one.

Software Query Language

Secure Query Language

Structured Query Language

System Query Language

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

Ans: window

60. What does CTE stand for? Choose one.

Command Table Extensions

Command Table Expressions

Common Table Expressions

Common Table Extensions

61. Select the DELETE statement that will delete products that have taken longer than 20 hours to make, but have also sold nothing. Choose one

DELETE FROM productProduction WHERE productionTime > 20 AND Sales =0;

DELETE FROM productProduction WHERE productionTime > 20 OR Sales =0;

62. 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 SELECT

SELECT INTO

INSERT VALUES

63. A _______ INSERT can be used to import a data file into a table with a user-specified format. Fill in the blank.

Ans: bulk

64. When using the LIKE predicate what is the symbol for wildcards? Choose one.

*

%

&

#

Ans: %

65. A database user is a database level security principal that must be mapped to a _____ at the server level in order for the user to connect to the database. Fill in the blank.

Ans: login

66. True or False: An Outer Join keeps all the items from the inner table and then filters what does not match on the outer table.

True

False

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

Ans: catch