Module 6: Diploma in Databases and T-SQL – First Assessment Answers

Completing this assessment is used 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

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. What does DDL stand for? Choose one.

Data Definition Language

Data Diagnosis Language

Data Directing Language

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

*

&

%

#

3. 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’

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

Ans: foreign

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

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

Foreign key

Primary key

Unique key

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

Can use nondeterministic functions

Cannot JOIN stored procedures

Can be called from within another SQL statement

Can JOIN user-defined functions

Cannot be used to modify SQL Server configuration

8. Traditional databases are organized by Rows and Columns stored in tables which are stored in the database files, Columns are often referred to as fields, rows are also referred to as____________. Fill in the blank.

Ans: records

9. The SELECT statement is use to retrieve data from one or more tables in the database it can include the ________ clause to define the conditions used to determine what data will be returned. Fill in the blank.

Ans: where

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

True

False

11. _________ functions assist with the summarization of large volumes of data. Drag the correct answer into the box.

Predicate

Join

Aggregate

Union

12. DBMS stands for what? Choose one.

Database Manipulation System

Database Master System

Database Management System

13. What does DML stand for? Choose one.

Data Method Language

Data Manipulation Language

Data Management Language

14. GUI stands for what? Choose one.

Graphical User Interface

Graphical User Interaction

Graphical User Incorporation

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

Third

Second

16. The three components of security are Securables, Permissions and __________. Fill in the blank.

Ans: principals

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

Ans: count

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

Ans: normalization

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

Ans: virtual

20. _____ clause allows you to combine the rows returned from multiple SELECT statements into a single result set. Fill in the blank.

Ans: union

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

camelcase

pascalcase

uppercamelcase

underscore

22. ________ data type conversions occurs when the SQL Server expression evaluator automatically converts data from one data type to another to complete an operation. Fill in the blank.

Ans: implicit

23. True or False – Primary key links your tables in the database together .

True

False

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

CROSS

INNER

INNER OUTER

25. True or False : All database users are automatically members of the private database role.

True

False

26. A money data type uses how many bytes in storage? Choose one.

16 bytes

8 bytes

1 byte

32 bytes

27. You can add a new row to a table using the ______ statement. Fill in the blank.

Ans: insert

28. SQL Server includes several fixed database roles. Match the role to its description

Ans:

Add, delete, or change data  -> db_datawriter

Read all data from all user tables   ->  db_datareader

Perform all configuration activities   ->  db_owner

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

30. Permissions to an object can be managed by using certain commands. Select those that are the actual commands. Choose two.

RETRACT

EXCLUDE

REVOKE

DENY

REMOVE

31. True or False: Accessing the data base is on a different level to accessing the Server.

True

False

32. SQL stands for what? Choose one.

Software Query Language

Structured Query Language

Secure Query Language

System Query Language

33. Match the form of normalization with its description.

Ans:

Eliminate data not dependant on the key  ->  Third normal form

Eliminate redundant data  ->  Second normal form

No repeating groups  -> First normal form

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

Full

Incremental

Differential

Local

35. 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: where

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

#

&

%

*

37. The _______ predicate specifies an inclusive range to test. Fill in the blank.

Ans: between

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

True

False

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

40. Which of these entities are created in an SQL server when using the CREATE statement? Choose three.

Procedure

Querys

Columns

Tables

Views

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

Ans: bulk

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

True

False

43. Match the term with its correct function.

Ans:

Specifies and inclusive range to test  ->  BETWEEN

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

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

Reverses the result of a search condition, to specifically what you don’t want returned   ->  NOT

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

ntext

Varchar

Unicode

Float

xml

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

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

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

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

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

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

48. True or False : A Database server hosts DBMS system and one or more instances of SQL Server.

True

False

49. True or False. Disregarding the 4th and 5th forms of normalization rules may result in a less than perfect database design but shouldn’t affect functionality.

True

False