LESSON

Lesson 1: Understanding Core Database Concepts

Multiple Choice

1. What kind of filename extension do Microsoft SQL Server transaction log files have?

a) .mdf

b) .ldf

c) .ndf

d) .log

Answer: b

Difficulty: Easy

Section Reference: Understanding Database Concepts

Explanation: Microsoft SQL Server transaction log files use an .ldf extension and don’t contain any objects such as tables or views. Transaction log files can be used to perform a point-in-time restore or rollback.

2. What kind of data structure improves the speed of data retrieval?

a) sort

b) filter

c) index

d) transaction log

Answer: c

Difficulty: Easy

Section Reference: Understanding Database Concepts

Explanation: In a database containing thousands of records with many fields per record, a fast computer would take some time to search through a table to retrieve data. A database index is a data structure that improves the speed of data retrieval operations on a table. The disadvantage of using an index is that indexes need to be created and updated, which takes processing and takes up disk space.

3. What is a collection of programs that enables you to enter, organize, and select data from a database?

a) flat program

b) spreadsheet group

c) DAT application

d) DBMS

Answer: d

Difficulty: Easy

Section Reference: Understanding Database Concepts

Explanation: Most users do not access databases directly. Instead, they use a database management system (DBMS) to access the databases indirectly. DBMS is a collection of programs that enables you to enter, organize, and select data in a database.

4. What is a simple database that uses two-dimensional tables consisting of rows and columns?

a) flat type database

b) hierarchical database

c) relational database

d) tabled database

Answer: a

Difficulty: Easy

Section Reference: Understanding Flat Type Databases

Explanation: A flat type database is very simplistic in design. They are most commonly used in plain text formats, as their purpose is to hold one record per line, making the access performance and queries very quick. An example of this type of database would be what you would find in a .txt or .ini file. Flat type databases are considered flat because they are two-dimensional tables consisting of rows and columns.

5. Which type of database has multiple tables with parent/child relationships and a child that can have more than one parent?

a) flat-file database

b) hierarchical database

c) relational database

d) tabled database

Answer: c

Difficulty: Medium

Section Reference: Understanding Relational Databases

Explanation: A relational database is similar to a hierarchical database in that data is stored in tables, and any new information is automatically added into the table without the need to reorganize the table itself. Different from hierarchical database, a table in a relational database can have multiple parents.

6. What allows a SQL database to be accessed by multiple users at the same time?

a) database server

b) workstation

c) collector

d) computer located in the center of the building

Answer: a

Difficulty: Easy

Section Reference: Understanding Relational Database Concepts

Explanation: Databases are often found on database servers so that they can be accessed by multiple users and so they can provide a high-level of performance. A popular database server runs Microsoft SQL Server. Database servers are entirely optimized to serve only the purposes of the database itself, usually using advanced hardware to allow for the high-processing necessities of the database server.

7. What are the limitations or rules placed on a file or column to ensure that data is considered valid for a database?

a) checker

b) validator

c) scanner

d) constraint

Answer: d

Difficulty: Medium

Section Reference: Introducing Language Elements

Explanation: Constraints are limitations or rules placed on a field or column to ensure that invalid data is not entered. For example, if you want someone to input a person’s age, the data typed in can be only a positive number. A person cannot have a negative age.

8. What is the primary console used to configure databases for Microsoft SQL Server?

a) DBS

b) SSMS

c) SQL Configuration Tool

d) SQL ODBC Emulator

Answer: b

Difficulty: Medium

Section Reference: Using the SQL Server Management Studio (SSMS) Interface

Explanation: When you install Microsoft SQL Server, you also install the SQL Server Management Studio (SSMS), which is the primary tool to manage the server and its databases via a graphical interface.

9. Which of the following is a language element that allows you to use core statements such as INSERT, UPDATE, and DELETE to manipulate data in any SQL Server table?

a) DML

b) DDE

c) DDL

d) DAT

Answer: a

Difficulty: Hard

Section Reference: Understanding Data Manipulation Language (DML)

Explanation: Data Manipulation Language (DML) allows you to use the core statements INSERT, UPDATE, DELETE, and MERGE to manipulate data in any SQL Server table.

10. Which of the following is a subset of T-SQL that deals with creating database objects such as tables and stored procedures?

a) DML

b) DDE

c) DDL

d) DAT

Answer: c

Difficulty: Hard

Section Reference: Understanding Data Definition Language (DDL)

Explanation: Data definition language (DDL) statements form part of the T-SQL portion of SQL Server and can be used to create database objects such as tables and views. The user interface employed to create these underlying DDL statements will be through the SQL Server Management Studio user interface.

11. What kind of statements are USE, CREATE, ALTER, and DROP?

a) DML

b) DDE

c) DDL

d) DAT

Answer: c

Difficulty: Hard

Section Reference: Using DDL Statements

Explanation: A DDL script statement task can always be completed through the SQL Server Management Studio user interface, but not all the options you may want to use with the DDL script can be accomplished through the user interface. You must, therefore, be familiar with the DDL statements USE, CREATE, ALTER, and DROP for objects to create and manage tables, user-defined data types, views, triggers, functions, and stored procedures.

12. What statement would you use to delete a table from a SQL database?

a) DELETE

b) DEL

c) DROP

d) REMOVE

Answer: c

Difficulty: Hard

Section Reference: DROP

Explanation: The DROP statement removes an object from the database, but if other objects are dependent on the object you are attempting to remove, this statement will fail and an error will be raised.

13. Which statement can you use to remove rows from a table?

a) SELECT

b) UPDATE

c) DELETE

d) STOP

Answer: c

Difficulty: Medium

Section Reference: Understanding Data Manipulation Language (DML)

Explanation: Data Manipulation Language (DML) is the language element that allows you to use the core statements INSERT, UPDATE, DELETE, and MERGE to manipulate data in any SQL Server tables. The DELETE command removes rows from a table or view.

14. What is the primary means of programing and managing SQL Server?

a) DOS commands

b) sqlcmd scripts

c) XQuery

d) T-SQL

Answer: d

Difficulty: Hard

Section Reference: Using the SQL Server Management Studio (SSMS) Interface

Explanation: Transact-SQL (T-SQL) is the primary means of programming and managing SQL Server. It exposes keywords so that you can create and manage databases and its components and monitor and manage the server itself. When you use SSMS to perform an action or task, you are executing T-SQL commands.

15. What allows you to execute SQL commands in a command-line application?

a) DOS commands

b) sqlcmd scripts

c) XQuery

d) Transact-SQL

Answer: b

Difficulty: Medium

Section Reference: Using the SQL Server Management Studio (SSMS) Interface

Explanation: The sqlcmd command-line application comes with Microsoft SQL Server and exposes SQL Server’s management features. It allows SQL queries to be written and executed from the command prompt. It can also act as a scripting language to create and run a set of SQL statements as a script. Such scripts are stored as a .sql file and are used either for management of databases or to create the database schema during the deployment of a database.

Fill in the Blank

16. A ____________ is an organized collection of data, typically stored in electronic format.

Answer: database (db)

Difficulty: Easy

Section Reference: Understanding Databases Concepts

Explanation: A database (db) is an organized collection of data, typically stored in electronic format. It allows you to input, organize, and retrieve data quickly. Traditional databases are organized by fields, records, and files.

17. Primary data files used on a Microsoft SQL server has an _____ extension

Answer: .mdf

Difficulty: Medium

Section Reference: Understanding Databases Concepts

Explanation: Microsoft SQL Server uses three types of files to store the database. Primary data files, with an .mdf extension, are the first files created in a database and can contain user-defined objects, such as tables and views, as well as system tables that SQL Server requires for keeping track of the database.

Short Answer

18. What statement is used to add or remove columns from a table?

Answer: ALTER

Difficulty: Hard

Section Reference: ALTER

Explanation: The ALTER statement changes an existing object and enables you to add or remove columns from a table. You can also use ALTER to change the definition of a view, a stored procedure, a trigger, or a function.

19. What is the difference between the TRUNCATE and DELETE commands?

Answer: The DELETE statement is used to delete rows from a table, but it does not free the space containing the table. Use the SQL TRUNCATE command to delete all rows from the table and free the space containing the table.

Difficulty: Hard

Section Reference: TRUNCATE and DELETE

Explanation: To delete all rows from the user table, use the following command:

DELETE FROM user;

To delete all rows from the user table while freeing the space, use the following command:

TRUNCATE FROM user;

20. Which statement would you use to change the database context?

Answer: USE

Difficulty: Hard

Section Reference: USE

Explanation: One T-SQL command worth mentioning is the USE command, which changes the database context to the specified database or database snapshot. In other words, when performing commands on a particular database, you most likely have to utilize the USE command just to select the database first.

Lesson 2: Creating Database Objects

Multiple Choice

1. Which attribute specifies the type of data that an object can hold and specifies how many bytes it will take up?

a) specifier

b) data type

c) validator

d) data specific

Answer: b

Difficulty: Easy

Section Reference: Defining Data Types

Explanation: A data type is an attribute that specifies the type of data that an object can hold. It also specifies how many bytes each data type takes up. For example, several data types define only whole numbers, which are good for counting or for identification. Other data types allow decimal numbers and come in handy when storing values dealing with money. Other data types are designed to store strings or multiple characters so that you can define labels, descriptions, and comments. Lastly, you have other miscellaneous data types that can store dates, times, and binary numbers consisting of 0s and 1s or can be used to store digital images.

2. What common data type you would be used to count objects?

a) money

b) integer

c) float

d) double float

Answer: b

Difficulty: Easy

Section Reference: Using Built-in Data Types

Explanation: The int numeric data type is used to store mathematical computations and is used when you do not require a decimal point output. An example of an integer would be: 2 or –2

3. What common data type is used to store decimal numbers such as 3.14 and 7.07?

a) money

b) integer

c) float

d) varchar

Answer: c

Difficulty: Medium

Section Reference: Using Built-in Data Types

Explanation: A float numeric data type is commonly used in the scientific community and is considered an approximate-number data type. This means that not all values within the data type range will be represented exactly. When considering using this numeric data type, note that the float data type ranges are different. For storage of 4 bytes, you would use the range of 1–24, as in float(24), and for 8 bytes, you would use the range of 25–53, as in float(53).

4. Which data type should you use to store text based on English?

a) text

b) int

c) float

d) varchar

Answer: d

Difficulty: Medium

Section Reference: Using Built-in Data Types

Explanation: A varchar character string data type is commonly used in databases in which you are supporting English attributes. If you are supporting multiple languages, use the nvarchar data type instead because this will help minimize the issues that come with character conversion.

5. How many bytes does the int data type take up?

a) 1 byte

b) 2 byte

c) 4 byte

d) 8 byte

Answer: c

Difficulty: Hard

Section Reference: Using Built-in Data Types

Explanation: The int data type holds integer data from –2^31(–2,147,483,648) to 2^31–1(2,147,483,647). It takes 4 bytes of data.

6. How many bytes does the money data type take up?

a) 1 byte

b) 2 byte

c) 4 byte

d) 8 byte

Answer: d

Difficulty: Hard

Section Reference: Using Built-in Data Types

Explanation: The money data type holds currency values from –922,337,203,685,477.508 to 922,337,203,685,477.5807. It takes 8 bytes of data.

7. What type of conversion occurs without specifying the actual callout function?

a) implicit

b) explicit

c) casting

d) autocasting

Answer: a

Difficulty: Medium

Section Reference: Understanding Implicit Conversions

Explanation: SQL Server supports implicit conversions, which can occur without specifying the actual callout function (cast or convert). This means that if you are doing calculations with multiple data types, implicit conversion will occur automatically.

8. How many bytes does a single character take up if it is a varchar that supports English and most European languages?

a) 1 byte

b) 2 bytes

c) 4 bytes

d) 8 bytes

Answer: a

Difficulty: Medium

Section Reference: Using Regular Character Strings

Explanation: A regular character uses 1 byte of storage for each character, which allows you to define one of 256 (8 bits are in a byte and 2^8=256) possible characters that accommodate English and some European languages.

9. What kind of character takes up two bytes of storage and can include almost any language including Chinese, Japanese, and Arabic?

a) standard

b) ANSI

c) Unicode

d) Duocode

Answer: c

Difficulty: Medium

Section Reference: Using Regular Character Strings

Explanation: A Unicode character uses 2 bytes of storage per character so that you can represent one of 65,536 (16 bits are in a 2 bytes and 2^16=65,536 characters). The additional character allows it to store characters from just about any language, including Chinese, Japanese, Arabic, and so on.

10. What data type should you use to support Japanese or Arabic text?

a) nvarchar

b) char

c) Unicode

d) varchar

Answer: a

Difficulty: Hard

Section Reference: Understanding Unicode Character Strings

Explanation: The Unicode character strings nchar and nvarchar can be either fixed or variable like their regular character strings. They use the Unicode UCS-2 character set.

11. Which naming conversion would capitalize the first character of each word with no spaces?

a) PascalCase

b) camelCase

c) CapCase

d) FirstCase

Answer: a

Difficulty: Hard

Section Reference: Creating and Using Tables

Explanation: It really doesn’t make a difference how you use upper- and lowercase, as long as you are consistent. Two common naming conventions are PascalCase and camelCase. Examples of PascalCase include such names as OrderDetails or CustomerAddresses.

12. What is a virtual table consisting of different columns from one or more tables?

a) index

b) view

c) vtable

d) vlookup

Answer: b

Difficulty: Easy

Section Reference: Creating Views

Explanation: A view is simply a virtual table consisting of different columns from one or more tables. Unlike a table, a view is stored in the database as a query object; therefore, a view is an object that obtains its data from one or more tables.

13. What are previously written SQL statements that have been stored within a database?

a) data statements

b) views

c) DDL statements

d) stored procedures

Answer: d

Difficulty: Hard

Section Reference: Creating Stored Procedures

Explanation: A stored procedure is a previously written SQL statement that has been “stored” or saved into the database. Creating a stored procedure will save you time when running the same query over and over again; you can then execute the stored procedure from within the database’s command environment.

14. What command is used to execute a stored procedure?

a) go

b) use

c) start

d) exec

Answer: d

Difficulty: Easy

Section Reference: Creating Stored Procedures

Explanation: An example of executing a stored procedure is as follows:

exec usp_displayallusers

The name of the stored procedure is usp_displayallusers, and exec tells SQL Server to execute the code in the usp_displayallusers stored procedure.

15. What is a type of attack in which malicious code is inserted into strings that are passed to the SQL server instance?

a) SQL infection

b) SQL takeover

c) SQL injection

d) SQL force

Answer: c

Difficulty: Medium

Section Reference: Understanding SQL Injections

Explanation: A SQL injection is an attack in which malicious code is inserted into strings that are later passed to instances of SQL Server waiting for parsing and execution. Any procedure that constructs SQL statements should be reviewed continually for injection vulnerabilities, because SQL Server will execute all syntactically valid queries from any source.

16. What is the set of rules that determine how data is sorted and compared?

a) sort rule

b) filter

c) constraint

d) collation

Answer: d

Difficulty: Medium

Section Reference: Using Built-in Data Types

Explanation: Collation refers to a set of rules that determine how data is sorted and compared. By default, SQL Server has predefined collation precedence. If you want to override how data is sorted, you must use a collation clause.

17. What type of data type would you use to store true/false or yes/no answers?

a) int

b) char

c) Boolean

d) float

Answer: c

Difficulty: Medium

Section Reference: Using Build-in Data Types

Explanation: Boolean is also known as a bit data type. If you are storing 8 or less bit columns in a table, the columns are then stored as 1 byte; if you are storing 9 to 16 bits, the columns are stored as 2 bytes, and so forth. The Boolean data type converts true and false string values to bit values, with true converted to 1 and false converted to 0.

Fill in the Blank

18. A char string can be up to _______ characters long.

Answer: 8,000

Difficulty: Hard

Section Reference: Understanding Regular Character Strings

Explanation: For the data set char, it is identified as char [(n)] and is a fixed-length, non-Unicode character (in other words, regular character) and has a length of n bytes. The value of n must be between 1 and 8,000 bytes. The other non-Unicode data type, varchar[(n|max)], is a variable-length data set, which can consist of 1 to 8,000 characters.

Short Answer

19. What does VARCHAR(25) represent?

Answer: A character string up to 25 characters long

Difficulty: Hard

Section Reference: Using Regular Character Strings

Explanation: When you use a VAR element, SQL Server preserves space in the row it resides in based on the column’s defined size and not on the actual number of characters found in the character string itself, plus an extra 2 bytes of data are provided for offset data. For example, if you want to specify that a strong supports only a maximum of 25 characters, you would use VARCHAR(25).

20. What two commands will perform implicit conversion?

Answer: Cast and Convert.

Difficulty: Hard

Section Reference: Understanding Implicit Conversions

Cast and convert play an integral partnership with any data type function as they convert an expression of one data type to another. convert was the old style of converting, with cast being used in the same manner.

 

Lesson 3: Manipulating Data

Multiple Choice

1. What command do you use to perform a query in SQL?

a) USE

b) SELECT

c) QUERY

d) CHOSE

Answer: b

Difficulty: b

Section Reference: Using Queries to Select Data

Explanation: The SQL command for retrieving any data from a database is SELECT. Much like any other SQL command, it will read similar to an English statement.

2. What wildcard character retrieves all records with the SELECT command?

a) *

b) %

c) $

d) ?

Answer: a

Difficulty: Easy

Section Reference: Using Queries to Select Data

Explanation: If you want to choose all column names from within a table, use an asterisk (*) in place of where the column name would be identified.

3. What would you use to combine two parameters in a query in SQL?

a) PLUS

b) BOTH

c) COMBINE

d) AND

Answer: d

Difficulty: Medium

Section Reference: Combining Conditions

Explanation: If you need more from a query than simply one set of parameters, you can use an AND clause to combine several conditions in one query statement to satisfy your requirements. For example, if you want to find the employees who work in the Shipping department but also want to find out which of those employees are female, you would use this query:

SELECT first_name, last_name

FROM employees

WHERE department = 'shipping' AND gender = 'F'

4. What clause would you use with the SELECT command that would pull records based on a range?

a) COMBO

b) BETWEEN

c) OR

d) COMBINE

Answer: b

Difficulty: Easy

Section Reference: Using the BETWEEN Clause

Explanation: To help resolve the awkwardness in using the AND clause in a query statement, replace it with a BETWEEN clause instead. This allows you to specify the range to be used in a “between x and y” query format for a much cleaner statement.

5. What keyword used with the SELECT statement is used to not output specified records?

a) OR

b) DISALLOW

c) DISREGARD

d) NOT

Answer: d

Difficulty: Easy

Section Reference: Using the NOT Clause

Explanation: In some instances, you might find it simpler to write your query to search data in terms of what you don’t want in your output. Transact-SQL provides you with a NOT keyword for use in such situations.

6. What would you use to combine columns from two different tables?

a) AND

b) BETWEEN

c) JOIN

d) COMBINE

Answer: c

Difficulty: Medium

Section Reference: Using the UNION Clause

Explanation: The JOIN clause allows you to combine related data from multiple table sources.  JOIN statements are similar in application that both EXCEPT and INTERSECT do in that they return values from two separate table sources.  Based on this knowledge, we can move forward with learning what data can be extracted by using JOIN statements.

7. Which clause used with the SELECT command will return values that you want to exclude from the normal parameters?

a) EXCEPT

b) INTERSECT

c) SEARCH

d) EXCLUDE

Answer: a

Difficulty: Easy

Section Reference: Using the EXCEPT and INTERSECT Clauses

Explanation: The EXCEPT clause returns any of those distinct values from the left query that are not also found on the right query.

8. What clause is used to combine related data from multiple table sources?

a) COMBINE

b) AND

c) JOIN

d) PLUS

Answer: c

Difficulty: Hard

Section Reference: Using the JOIN Clause

Explanation: The JOIN clause allows you to combine related data from multiple table sources. JOIN statements are similar to both EXCEPT and INTERSECT in that they return values from two separate table sources.

9. What statement would you use to add data to a table?

a) ADD

b) INSERT

c) JOIN

d) PRESENT

Answer: b

Difficulty: Medium

Section Reference: Using Queries to Insert Data

Explanation: If you are looking to insert small quantities of data—for instance, adding a few new rows into your database—you can accomplish this by using the graphical interface tool or by using the INSERT statement.

10. What statement would you use to change data in a table?

a) CHANGE

b) MODIFY

c) INSERT

d) UPDATE

Answer: d

Difficulty: Medium

Section Reference: Using the UPDATE Statement

Explanation: The UPDATE clause allows you to modify the data stored in tables by using data attributes such as the following:

UPDATE <table_name>

SET <attribute> = <value>

WHERE <conditions>

11. What statement is used to delete one or more rows in a table?

a) DELETE

b) REMOVE

c) TRUNCATE

d) CHANGE

Answer: a

Difficulty: Easy

Section Reference: Deleting Data

Explanation: The DELETE statement is used to perform the exact function it states: remove rows from a table or a view. You can use DELETE in several ways, depending on the amount of data you need to have removed. The simplest method is to identify and delete individual rows from within the database using the DELETE command syntax.

12. What statement is used to delete a table from a database?

a) DELETE

b) DROP

c) REMOVE

d) TRUNCATE

Answer: b

Difficulty: Medium

Section Reference: Deleting a Table with DROP TABLE

Explanation: Suppose that you want to delete an entire table because it’s obsolete. The removal of an entire table looks like this:

DROP TABLE <table_name>

13. How can you prevent the accidental loss of an entire table?

a) Use the DELETE TRANS command.

b) Use the LOOPBACK command.

c) Use the SAFEDELETE command.

d) Use referential integrity.

Answer: d

Difficulty: Hard

Section Reference: Using Referential Integrity

Explanation: A failover measure that you can take regarding database tables is using referential integrity practice methods. One of the most common mistakes of database manipulating is the accidental loss of entire tables. The best way to avoid this type of situation in the first place is to ensure that your database is using referential integrity. Referential integrity does not allow deletion of tables, unless they are actually at the end of the relationship.

14. What do you perform when you want to recall specific records from a database?

a) recall

b) virtual extraction

c) reach

d) query

Answer: d

Difficulty: Easy

Section Reference: Using Queries to Select Data

Explanation: You use the SELECT statement to retrieve or extract data from one table, retrieve or extract data by using joins, and combine results by using UNION and INTERSECT. When you retrieve or extract data from a table, you are performing a query.

15. What command would you use to delete a table while leaving the table structure in place for future use?

a) DELETE TABLE

b) TRUNCATE TABLE

c) REMOVE TABLE

d) STOP TABLE

Answer: b

Difficulty: Medium

Section Reference: Truncating a Table with TRUNCATE TABLE

Explanation: The TRUNCATE TABLE statement removes only the data from within the table but leaves the table structure in place for future use.

Short Answer

16. What statement would you use to pull First_Name, Last_Name, and ID_Number from the Users table?

Answer:     SELECT First_Name, Last_Name, ID_Number

FROM Users

Difficulty: Hard

Section Reference: Using Queries to Select Data

Explanation: The SQL command for retrieving any data from a database is SELECT. Much like any other SQL command, it will read similar to an English statement. Composing a SELECT statement is similar to filling in the blanks, such as the following:

SELECT id, name //columns

FROM sysobjects // tables

WHERE type = "jones" //conditions you want to produce results from

This is a simple statement, but it provides the basic understanding of what the SELECT statement does and always follows the same pattern each time you issue a SELECT statement to the database.

17. What statement would you use to pull all records in which the State is CA from the Users table?

Answer:     SELECT *

FROM Users

WHERE State = 'CA'

Difficulty: Hard

Section Reference: Using Queries to Select Data

Explanation: Suppose that you want to provide your boss with a list of employees whose salary range is above $50K. You are interested only in retrieving those employees who fit your boss’s criteria. Here’s how you would do that in SQL:

SELECT first_name, last_name, salary

FROM employees

WHERE salary >= 50,000

18. What statement would you use to pull First_Name and Last_Name from the Users table in which Gender is F and State is CA?

Answer:     SELECT First_Name, Last_Name

FROM Users

WHERE Gender = 'F' AND State = 'CA'

Difficulty: Hard

Section Reference: Combining Conditions

Explanation: You can combine several conditions in one query statement to satisfy your requirements.

19. What statement would you use to list the First_Name and Last_Name from the Users table which Department is not IT?

Answer:     SELECT First_Name, Last_Name

FROM Users

WHERE NOT Department = 'IT'

Difficulty: Hard

Section Reference: Using the NOT Clause

Explanation: In some instances, it is simpler to write your query to search data in terms of what you don’t want in your output. Transact-SQL provides you with a NOT keyword for use in such situations.

20. What statement would you use to delete the Equipment table?

Answer: DROP TABLE Equipment

Difficulty: Hard

Section Reference: Deleting a Table with DROP TABLE

Explanation: Now, maybe you just want to delete the entire table? The removal of an entire table looks like this:

      DROP TABLE <table_name>

 

Lesson 4: Understanding Data Storage

Multiple Choice

1. What do you call the elimination of redundant data?

a) normalization

b) de-duplication

c) loopbacking

d) reduxing

Answer: a

Difficulty: Medium

Section Reference: Normalizing a Database

Explanation: Using normalization techniques, with respect to data storage, came more into play back in the days when data storage cost a great deal more than it does today. Normalization, in a nutshell, is the elimination of redundant data to save space.

2. Which of the following indicates the elimination of repeating groups?

a) 1NF

b) 2NF

c) 3NF

d) 4NF

Answer: a

Difficulty: Hard

Section Reference: What is Normalization?

Explanation: Of the five normalization forms (NFs), the first one, 1NF, is Eliminate Repeating Groups.

3. Which of the following indicates the elimination of redundant data?

a) 1NF

b) 2NF

c) 3NF

d) 4NF

Answer: b

Difficulty: Hard

Section Reference: What is Normalization?

Explanation: The second normalization form, 2NF, is Eliminate Redundant Data,

4. Which of the following is not a condition for 1NF?

a) Table must have no duplicate records.

b) The table must have no multi-valued attributes.

c) There are no primary keys.

d) Entries in the column are of the same data type.

Answer: c

Difficulty: Hard

Section Reference: First Normal Form (1NF) – Eliminate Repeating Groups

Explanation: The first normalized form (1NF) means the data is in an entity format, which means that the following three conditions must be met:

•         The table must have no duplicate records. After you define a primary key for the table, you have met the first normalized form criteria.

•         The table must not have multi-valued attributes—meaning, you can’t combine multiple values that are considered valid for a column in a single column.

•         The entries in the column or attribute are of the same data type.

5. What would enable you to enforce the uniqueness property of columns, other than a primary key within a table?

a) secondary key

b) constant check

c) validator

d) unique constraint key

Answer: d

Difficulty: Easy

Section Reference: Understanding Primary, Foreign and Composite Keys

Explanation: Unique constraint keys enable you to enforce the uniqueness property of columns, other than a primary key within a table. A unique constraint key acts similar to a primary key, but with two important differences:

•         Columns containing a unique key constraint may contain only one row with a NULL value. You cannot have two rows containing a NULL value in the same option because that would violate the unique constraint’s duplicate value error.

•         A table can have multiple unique constraints.

6. What is an attribute or set of attributes that can be used to uniquely identify the row?

a) primary key

b) second key

c) unique constraint key

d) foreign key

Answer: a

Difficulty: Medium

Section Reference: Primary Keys

Explanation: Perhaps the most important concept of designing any database table is that it has a primary key—an attribute or set of attributes that can be used to uniquely identify the row. Every table must have a primary key; without a primary key, it’s not a valid table. By definition, a primary key must be unique and must have a value (which is not null).

7. What key is replicated from the primary key of one table to a secondary table, and then all the key attributes get duplicated from the primary table?

a) primary key

b) second key

c) unique constraint key

d) foreign key

Answer: d

Difficulty: Medium

Section Reference: Foreign Keys

Explanation: When you have two tables relating to one another, one of those tables acts as the primary table and the other acts as the secondary table. To connect the two tables, the primary key is replicated from the primary to secondary table, and then all the key attributes duplicated from the primary table are known as the foreign key. Although this may be referred to, at times, as a parent-child relationship, enforcing the foreign key attribute is actually referred to as referential integrity.

8. What type of index sorts the data stored inside the table?

a) clustered index

b) non-clustered index

c) quick index

d) primary index

Answer: a

Difficulty: Medium

Section Reference: Clustered Indexes

Explanation: A clustered index defines how SQL Server will sort the data stored inside the table. Because that data can be sorted only in one way, it simply is not possible to have two clustered indexes on the same table. Also, a clustered index is a physical construct as opposed to most indexes, which are logical or software-based.

9. What type of index has an index key value that points to a data row, which contains the key value?

a) clustered index

b) nonclustered index

c) quick index

d) primary index

Answer: b

Difficulty: Medium

Section Reference: Non-Clustered Indexes

Explanation: A nonclustered index contains the nonclustered index key values, and each of those keys has a pointer to a data row that contains the key value. This pointer is referred to as a row locator, whose structure depends on whether the data pages are stored in a heap or as a clustered table. This is an important part of a nonclustered index’s function because if it points to a heap, the row locator is a pointer to the row but, in a clustered table, the row locator is then the clustered index key.

10. What type of field would you make an employee ID?

a) standard key

b) foreign key

c) unique constraint key

d) secondary key

Answer: c

Difficulty: Hard

Section Reference: Understanding Primary, Foreign and Composite Keys

Explanation: Because an employee ID should be unique within a company, it would make sense that it would be a unique constraint key.

11. If you define a primary key, which of the following levels have you reached?

a) 1NF

b) 2NF

c) 3NF

d) 4NF

Answer: a

Difficulty: Hard

Section Reference: First Normal Form (1NF) – Eliminate Repeating Groups

Explanation: Of the three conditions required to meet 1NF, as soon as you have defined a primary key for the table, you have met the first normalized form criteria.

12. Which level eliminates columns not dependent on keys?

a) 1NF

b) 2NF

c) 3NF

d) 4NF

Answer: c

Difficulty: Hard

Section Reference: Third Normal Form (3NF) – Eliminate Columns Not Dependent on Keys

Explanation: The third normal form (3NF) checks for transitive dependencies. A transitive dependency is similar to a partial dependency in that they both refer to attributes that don’t fully depend on a primary key. A dependency is considered transient when attribute1 is dependent on attribute2, which is then dependent on the primary key.

13. Which level of is reached by using primary keys on a table within a SQL Server database?

a) 1NF

b) 2NF

c) 3NF

d) 4NF

Answer: a

Difficulty: Hard

Section Reference: First Normal Form (1NF) – Eliminate Repeating Groups

Explanation: After you define a primary key for the table, you have met the first normalized form criteria.

14. What structures are used when storing keys for a clustered index?

a) root tree

b) reverse root tree

c) B-tree

d) alphabetical

Answer: c

Difficulty: Medium

Section Reference: Clustered Index

Explanation: An index is an on-disk (or stored) structure associated entirely with a table or a view that aids in the speed of data retrieval. To build this index, a series of keys are built from one or more columns in each row within the table or a view. These keys are then stored in a structure called a B-tree, which enables SQL Server to then find the row(s) associated with those defined values much more quickly and efficiently.

15. Which type of index uses pointers that point to row location?

a) clustered index

b) nonclustered index

c) quick index

d) primary index

Answer: b

Difficulty: Medium

Section Reference: Non-Clustered Indexes

Explanation: A nonclustered index contains the nonclustered index key values, and each of those keys has a pointer to a data row that contains the key value. This pointer is referred to as a row locator, whose structure depends on whether the data pages are stored in a heap or as a clustered table.

Fill in the Blank

16. How many non-clustered indexes can you have?

Answer: Unlimited

Difficulty: Medium

Section Reference: Foreign Keys

Explanation: Because the non-clustered indexes uses points to sort the data without actually storing the stored table, you can have as many non-clustered indexes as you want.

17. By definition, a __________________ must be unique and must have a value (which is not null).

Answer: primary key

Difficulty: Medium

Section Reference: Foreign Keys

Explanation: Every table must have a primary key; without a primary key, it’s not a valid table. By definition, a primary key must be unique and must have a value (which is not null).

18. A _________________________ occurs when you define more than one column as your primary key.

Answer: composite primary key

Difficulty: Hard

Section Reference: Composite Primary Keys

Explanation: A composite primary key occurs when you define more than one column as your primary key and although many database administrators do not use them or are not aware of them, they play an integral part in the designing of a good, solid data model.

Short Answer

19. How many clustered indexes can you have for a table?

Answer: one

Difficulty: Medium

Section Reference: Clustered Indexes

Explanation: When you begin looking at implementing indexes, remember that each table can have only one clustered index that defines how SQL Server will sort the data stored inside the table. Because that data can only be sorted in one way, having two clustered indexes on the same table is simply not possible. Also note that the clustered index is a physical construct as opposed to most indexes, which are logical, or software-based.

20. What are the two types of indexes?

Answer: clustered and nonclustered indexes

Difficulty: Medium

Section Reference: Understanding Clustered and Non-Clustered Indexes

Explanation: As a database administrator, you need to understand what the two types of indexes (clustered and nonclustered) do, and what the role of these indexes are within a database environment.

 

Lesson 5: Administrating a Database

Multiple Choice

1. What is used to grant a user account access to a database?

a) permission

b) right

c) password

d) encryption key

Answer: a

Difficulty: Easy

Section Reference: Securing Databases

Explanation: A permission is used to grant an entity, such as a user, access to an object, such as another user or a database. The security model within SQL Server is very complex and requires great thought put into applying user roles and their different permissions.

2. What role can you give to a user who gives full access to the SQL server and databases?

a) root

b) system

c) sysadmin

d) local

Answer: c

Difficulty: Medium

Section Reference: Server-Level Security

Explanation: If you add a user to the sysadmin role, that user will now have full access to every server function, database, and object for that server. With that full access, a user can then grant permission to other users against other server securables. You must ensure that this very powerful user role is not granted to the wrong user login, because that user can perform various system-level actions such as granting access to a database and adding in his or her network login ID to be mapped to a specific database user ID.

3. What role is automatically granted to all users?

a) sysadmin

b) root

c) public

d) standard

Answer: c

Difficulty: Medium

Section Reference: Database-Level Security

Explanation: All users are automatically a member of the public standard database role. User-defined roles are custom roles that serve as groups. These roles can be granted permission to a database object, and users can be assigned to a database user-defined role.

4. What permission would you assign that will remove the permission previously assigned?

a) grant

b) denied

c) revoke

d) reset

Answer: c

Difficulty: Easy

Section Reference: Database-level Security

Explanation: Each object’s permission is assigned through either granting, revoking, or denying user login permissions. The revoke permission removes the permission assigned, regardless of whether the permission is a deny permission or whether it’s a grant permission.

5. What is the act of establishing or confirming a user or system identity?

a) authentication

b) authorization

c) auditing

d) logging

Answer: a

Difficulty: Easy

Section Reference: Windows Authentication

Explanation: Authentication is the act of establishing or confirming a user or system identity. Windows Authentication mode is superior to mixed mode because users don’t need to learn yet another password and because it leverages the security design of the network.

6. What fixed role allows you to create logins for the server?

a) dbcreator

b) diskadmin

c) securityadmin

d) serveradmin

Answer: c

Difficulty: Medium

Section Reference: Fixed Server Roles

Explanation: Securityadmin can manage the logins for the server.

7. If you are using mixed mode, what account is the build-in SQL administrator account?

a) administrator

b) sysadmin

c) root

d) sa

Answer: d

Difficulty: Medium

Section Reference: SQL Authentication

Explanation: Associated with SQL authentication is sa, the built-in SQL administrator account associated with SQL authentication. Because it is less secure than Windows logins, avoiding mixed mode is recommended; however, it is available for backward compatibility.

8. Which type of backup backs up all changes since the last backup?

a) full backup

b) differential backup

c) incremental backup

d) copy backup

Answer: c

Difficulty: Easy

Section Reference: Database Backups

Explanation: An incremental backup is based on the last backup of data. An incremental backup contains only the data that has changed since the last full or incremental backup. Incremental backups are the smallest and fastest to create over the full and differential backups. However, at restore time, the full backup is restored first, followed by each incremental backup following the full backup.

9. Which type of backup backs up only the data in the primary filegroup and specified files or filegroups?

a) full backup

b) differential backup

c) partial backup

d) incremental backup

Answer: c

Difficulty: Easy

Section Reference: Partial Backup

Explanation: A partial backup backs up all the full data in the primary filegroup, every read/write filegroup, and any optionally specified read-only files or filegroups. A partial backup of a read-only database contains only the primary filegroup.

10. What fixed server role allows you to create, alter, and drop disk files?

a) bulkadmin

b) dbcreator

c) processadmin

d) diskadmin

Answer: d

Difficulty: Easy

Section Reference: Fixed Server Roles

Explanation: Diskadmin can create, alter, and drop disk files.

11. Which service account should you use if you need to have network access?

a) local user account

b) local system account

c) domain user account

d) guest account

Answer: c

Difficulty: Easy

Section Reference: SQL Server Service Account

Explanation: The domain user account is the recommended login account because SQL Server can use the Windows account specifically created for the SQL Server. You can then grant administrator rights to the SQL Server account for which you can then interact with other servers on the network.

12. What fixed database role allows you to have full permission to a database?

a) sa

b) db_owner

c) db_datareader

d) db_accessadmin

Answer: b

Difficulty: Easy

Section Reference: Fixed Database Roles

Explanation: A db_owner is a special role that has all permissions in the database. This role includes all the capabilities of the other roles. It is different from the dbo user role. This is not the database-level equivalent of the server sysadmin role; an object-level deny will override membership in this role.

13. What fixed database role would you use to manage database-level security roles and permissions?

a) sysadmin

b) db_securityadmin

c) db_accessadmin

d) db_datareader

Answer: b

Difficulty: Medium

Section Reference: Fixed Database Roles

Explanation: The db_securityadmin role can manage database-level security (roles and permissions).

14. What command would you use to grant object permissions?

a) GRANT

b) USE

c) RUNAS

d) ALLOW

Answer: a

Difficulty: Easy

Section Reference: Granting Object Permissions with Transact-SQL Statements

Explanation: The Transact-SQL GRANT statement grants permissions on a table, view, table-valued function, stored procedure, extended stored procedure, scalar function, aggregate function, service queue, or synonym. The following statement provides the grant permission to an object for a specific user and her role:

GRANT Permission, Permission

ON Object

TO User/role, User/role

WITH GRANT OPTION

15. If you need to restore a database after an incremental backup, what do you need to do for a restore?

a) Restore the full backup and the first incremental backup.

b) Restore the full backup and the last incremental backup.

c) Restore the full backup and all the incremental backups since the last full backup.

d) Restore the last incremental backup.

Answer: c

Difficulty: Medium

Section Reference: Database Backups

Explanation: At restore time, the full backup is restored first, followed by each incremental backup following the full backup.

Fill in the Blank

16. A ____________________ restores the database in stages.

Answer: piecemeal restore

Difficulty: Hard

Section Reference: Database Restores

Explanation: A piecemeal restore restores the database in stages, beginning with the primary filegroup and one or more secondary filegroups.

Short Answer

17. Which fixed server roles can create, alter, and drop databases?

Answer: Dbcreator

Difficulty: Hard

Section Reference: Fixed Server Role

Explanation: The Dbcreator role can create, alter, drop, and restore databases.

18. What command is used to create a user called server\user1?

Answer: CREATE USER 'server\user1'

Difficulty: Medium

Section Reference: Granting Access to the Database

Explanation: The command to grant database access to a user must be issued from within the database to which the user is to be granted access. The first parameter is the server login, and the second is the optional database username:

USE Family

CREATE USER 'XPS\Lauren' , 'LRN'

19. In mixed mode, what two types of logins are allowed?

Answer: Windows logins and SQL logins

Difficulty: Hard

Section Reference: SQL Authentication

Explanation: SQL servers also support mixed mode, which allows you to connect to the SQL server using Windows authentication or SQL Server authentication. A SQL Server login account and related passwords are defined on the SQL server and are not related to Active Directory or Windows accounts.

20. What command would you use to remove a user from a SQL server?

Answer: DROP LOGIN

Difficulty: Hard

Section Reference: Removing a Windows Login

Explanation: To create a login using Transact-SQL syntax so that you can add a Windows user or group, run the CREATE LOGIN command. Be sure to use the full Windows username, including the domain name, of the user you are trying to add, as follows:

CREATE LOGIN 'XPS\Joe'