Understanding Core Database Concepts, Creating Database Objects, Understanding Data Storage, Creating Database Objects

55. What data type can store a time that includes hours, minutes, and seconds, but no fractional seconds, and can also minimize storage requirements? A. time(3) B. smalldatetime C. timestamp D. time

D. time

How are blank columns stored in the database?

A blank column is stored as a NULL in the database.

What is a query?

A query is an inquiry that retrieves the needed information from a database.

93. Which of the following ensures entity integrity? A. A PRIMARY KEY constraint B. A CHECK constraint C. A FOREIGN KEY constraint D. A DEFAULT definition

A. A PRIMARY KEY constraint

109. Which database object should you create to optimize how the underlying data is physically accessed by queries? A. An index B. A function C. A trigger D. A stored procedure

A. An index

107. You have a database table named TransactionHistory that contains millions of rows. The table has a primary key named TrxID. Users frequently use a query that includes the region and status in the WHERE clause condition. You need to optimize performance of this query. What should you do? A. Create a composite nonclustered index on region and status. B. Create two nonclustered indexes, one on region and one on status. C. Create two clustered indexes, one on region and one on status. D. Create a composite clustered index on region and status.

A. Create a composite nonclustered index on region and status.

98. A database includes the table shown in the exhibit. Each teacher is associated with exactly one campus. The address in the table is the address for the campus where the teacher teaches. What change would you need to make to normalize the database to the third normal form (3NF)? A. Create a separate table for campus address information. Use the Campus as the primary key for the table. Create a foreign key that relates the Campus column in the Teachers table to the primary key of the new table. B. Create a separate table for campus address information. Use the Campus as the primary key. Add a TeacherID column to the table and relate it to the TeacherID column in the Teachers table. C. Combine the FirstName and LastName columns into a single column. D. Combine the Campus, Address, City, State, Country, and PostalCode columns into a single column.

A. Create a separate table for campus address information. Use the Campus as the primary key for the table. Create a foreign key that relates the Campus column in the Teachers table to the primary key of the new table.

96. The requirement that each field value in a table is associated with only one row, is an example of normalizing a database to which form? A. Second normal form B. Third normal form C. First normal form D. Fourth normal form

A. Second normal form

97. The EmployeeDependents table includes the following columns: EmployeeID Spouse Child1 Child2 Child3 Which statement correctly identifies the normalization level of this table? A. The table is not normalized B. The table is normalized to the third normal form. C. The table is normalized to the first normal form. D. The table is normalized to the second normal form.

A. The table is not normalized

94. You have created a Employee table that contains an EmpID column as its primary key defined with the following column definition: EmpID int IDENTITY(1,1) PRIMARY KEY You need to create a foreign key with the same column name in the SalaryHistory table that references the EmpID in the Employee table. What must you ensure? A. The two EmpID columns have a compatible data type. B. The SalaryHistory table does not contain any rows. C. The EmpID column in the SalaryHistory table has a CHECK constraint. D. The EmpID column in the SalaryHistory table has the IDENTITY property.

A. The two EmpID columns have a compatible data type.

108. A covering index: A. includes all the columns in a query's select list. B. represents the physical order of the underlying data. C. can only be created on a table's primary key. D. includes a WHERE clause condition.

A. includes all the columns in a query's select list.

56. What data type can store an integer between 0 and 255 and minimize the required storage? A. tinyint B. int C. smallint D. bit

A. tinyint

How do the ALTER statement differ from the UPDATE statement?

ALTER statement changes an object's definition while the UPDATE statement changes the data in a table.

What are atomic transactions?

An atomic transaction is a database transaction that can either succeed or fail. If DDL statements like INSERT, UPDATE, DELETE and/or MERGE violate at least one primary key or a constraint, the transaction will fail.

43. You have an Events table that was defined with the statement shown in the exhibit. Which statement would successfully create a view on the Events table without generating any errors? A. CREATE VIEW AS SELECT * FROM Events WHERE Location=6 B. CREATE VIEW LocalEvents AS SELECT * FROM Events WHERE Location=6 WITH CHECK OPTION C. CREATE VIEW LocalEvents SELECT ID, Description, Location FROM Events WHERE Location = 3 WITH CHECK OPTION D. CREATE VIEW LocalEvents WITH SCHEMABINDING AS SELECT * FROM Events WHERE Location=3 WITH CHECK OPTION

B. CREATE VIEW LocalEvents AS SELECT * FROM Events WHERE Location=6 WITH CHECK OPTION

46. You are creating a stored procedure that accepts a UserID of type int and retrieves a value that is the concatenated FirstName and LastName values for the user. Which parameter list should you use? A. UserID int B. @UserID int, @FullName varchar(40) OUTPUT C. UserID int, FullName varchar(40) OUTPUT D. @UserID int

B. @UserID int, @FullName varchar(40) OUTPUT

91. Which statement correctly describes limitations on primary key and foreign key constraints? A. A table can only have one primary key constraint and one foreign key constraint. B. A table can have one primary key constraint and multiple foreign key constraints. C. A table can have either a primary key constraint or a foreign key constraint, but cannot have both. D. A table can have multiple primary key constraints and multiple foreign key constraints.

B. A table can have one primary key constraint and multiple foreign key constraints.

41. You have a Contracts table in your database. You need to allow users to easily write queries against the table, but only be able to query specific columns. What should you create? A. A DDL trigger B. A view C. A function D. A stored procedure

B. A view

103. Which type of index defines the physical sort order for database data? A. Full-text index B. Clustered index C. Spatial index D. Nonclustered index

B. Clustered index

105. Which of the following are good candidates for key columns in a nonclustered index? A. Columns frequently modified by UPDATE statements B. Columns frequently used as query join conditions C. Columns of varchar(max) or varbinary(max) data types D. Columns frequently used in the SELECT clause of a query

B. Columns frequently used as query join conditions

Why is it not recommended to have foreign keys as null values?

If the foreign key is left as NULL it may result impossible to verify its constraints. In other words, leaving a foreign key as NULL results in a loss of a guarantee of integrity of the data.

Name all the Data Manipulation Language statements.

SELECT, INSERT, UPDATE, DELETE, MERGE

The two categories of database objects

Storage, and programmability.

What are some programmability objects?

Stored procedures, Triggers, and Functions

What are two objects that belong to the storage category?

Table and view.

Table

a collection of data organized into rows and columns.

Database Management System (DBMS)

a collection of programs that enables you to enter, organize, and select data in a database.

What is a self-reference?

a foreign key constraint that reference columns in the same table.

Database (db)

a group of organized data usually stored on an electronic device.

Hierarchical database

a parent table can have multiple children tables, but a child table cannot have more than one parent table.

Flat-type database

a two-dimensional table consisting of rows and columns that hold one record per line.

INSERT

adds one or more new rows into a table or a view.

SELECT

allows the user to read/view desired columns from one or more tables.

Unique constraint

allows to the administrator to identify which column should not have duplicate values.

ALTER

changes an already existing object.

UPDATE

changes the already existing data in a table or a view.

USE

changes the database context.

SQLCMD

command-line application in Microsoft SQL Server that allows the user to write and execute SQL queries from the command prompt.

CREATE

creates a database object (table, view, stored procedure, login, user, etc.).

Relational database

data is stored in tables and new information can be added without the need to reorganize the table itself. These tables can also have multiple parent tables.

Index

data structure that allows a faster retrieval of information from a database.

Not NULL constraint

does not allow the user to leave a certain column as blank.

What are the advantages of indexes?

indexes improve the speed of data retrieval from a database.

Default constraint

inserts a default value into a column.

Data Manipulation Language (DML)

language element that allows the user to manipulate data in SQL Server tables.

Constraints

limitations that do not allow the user to input data considered as invalid into the database.

Check constraint

limits the type of data that a user can input into a database.

57. What data type can store between one and five characters of alphanumeric data and supports multiple languages with minimal storage space requirements? A. CHAR(5) B. VARCHAR(1,5) C. VARCHAR(5) D. NVARCHAR(5)

D. NVARCHAR(5)

99. What is a characteristic of a database that is normalized to the first normal form? A. Tables contain only columns that are dependent on the table's primary key. B. All data is stored in a single table. C. All tables are related using foreign keys. D. Tables do not contain multiple columns that represent similar attributes.

D. Tables do not contain multiple columns that represent similar attributes.

92. What is the purpose of defining a foreign key? A. To ensure that a column only allows values that meet a condition B. To uniquely identify each row in a table C. To allow data in two or more columns to uniquely identify each row in a table D. To define a relationship between two tables

D. To define a relationship between two tables

95. What is one reason to denormalize a database? A. To provide privacy protection for confidential data B. To eliminate redundant data C. To improve data modification performance D. To improve data retrieval performance

D. To improve data retrieval performance

33. When creating a table, what should you specify for a column that cannot contain duplicate values? A. NOT NULL B. CHECK C. DEFAULT D. UNIQUE

D. UNIQUE

50. Using a stored procedure: A. requires returning at least one value. B. eliminates the need for indexing. C. allows you to partition tables and optimize query performance. D. improves performance because parsing is not required with each execution.

D. improves performance because parsing is not required with each execution.

Name the three types of files Microsoft SQL uses to store databases

*Primary data files* - have an *.mdf* extension and contain user-defined objects. *Secondary data files* - have an *.ndf* extension, are used when the first hard disk runs out of space and are stored on separate physical hard disks. *Tertiary data files* - have an *.ldf * extension, these are transaction log files and do not contain tables or views.

30. You are creating a relational database that includes the tables shown in the exhibit. The InStock and OnOrder columns should have a value of 0 if no value is specified. You have already created the Products table and the Stores table, and need to create the Inventory table. Which statement should you issue? A. Create a table that has a single PRIMARY KEY constraint, two FOREIGN KEY constraints, and two columns that allow Nulls. B. Create a table that has a single PRIMARY KEY constraint, two FOREIGN KEY constraints, and two DEFAULT constraints. C. Create a table that has two PRIMARY KEY constraints, two FOREIGN KEY constraints, and two columns that allow Nulls. D. Create a table that has two PRIMARY KEY constraints, two FOREIGN KEY constraints, and two DEFAULT constraints.

B. Create a table that has a single PRIMARY KEY constraint, two FOREIGN KEY constraints, and two DEFAULT constraints.

89. Which type of constraint is used to enforce referential integrity? A. Primary key constraint B. Foreign key constraint C. Check constraint D. Unique constraint

B. Foreign key constraint

48. What is one difference between a stored procedure and a function? A. Stored procedures must include a RETURNS clause B. Functions return only a single scalar or table value C. Functions can accept input values, but stored procedures cannot. D. Stored procedures can only perform DML processing.

B. Functions return only a single scalar or table value

42. You are creating a view restricting the visible rows using a WHERE clause. You need to ensure that no changes made through the view cause the data to not conform to the view's WHEE clause condition. What should you do? A. Enable constraint checking using an ALTER TABLE statement. B. Include WITH CHECK OPTION in your CREATE VIEW statement. C. Include a CHECK constraint in your CREATE VIEW statement. D. Include WITH SCHEMABINDING in your CREATE VIEW statement.

B. Include WITH CHECK OPTION in your CREATE VIEW statement.

31. In your database, you have the tables shown in the exhibit. You need to ensure that if a category was removed, all related product rows would have their CategoryID set to a specific non-null value. What should you use? A. ON UPDATE CASCADE B. ON DELETE SET DEFAULT C. ON DELETE CASCADE D. ON UPDATE NO ACTION

B. ON DELETE SET DEFAULT

45. You need to create a module that performs the following tasks: *Accepts an order number *Sets the value of the Status column in the Orders table for that order number to Shipped *Retrieves a tracking number from a table *Logs shipping information to the Shipping table *Returns the number of items shipped and the customer's e-mail address What should you create? A. Scalar function B. Stored procedure C. CLR function D. Table-valued function

B. Stored procedure

90. The TestScores table is shown in the exhibit and has these characteristics: *Each student has a unique StudentID that references the Students table. *Each instructor has a unique InstructorID that references the Instructors table. *Each student may take multiple tests on the same date. *Each student may take tests on multiple dates. What column or columns should you use for the primary key? A. StudentID, Test, Date, InstructorID B. StudentID, Test, and Date C. StudentID, Date D. Test

B. StudentID, Test, and Date

32. What is the purpose of including a COLLATE clause in a column definition? A. To ensure that all values entered in the column fall within a specific range B. To specify sorting rules that are different than those used for the other columns in the table C. To create a computed column that can be included in an index D. To ensure functional dependency E. To create a relationship with a column in a different table

B. To specify sorting rules that are different than those used for the other columns in the table

102. A database that includes a table that contains repeating columns is said to be NORMALIZED. Choose whether the statement is correct as stated or choose the text that should replace the capitalized text to make the statement correct. A. The statement is correct and does not need any changes. B. denormalized C. fragmented D. defragmented

B. denormalized

101. You are creating a relational database. Your current data model meets the following requirements: *Each table represents a single entity identified by a primary key. *Repeating columns have been eliminated from each table. *All columns in each table depend only on the table's primary key. *Foreign keys have been implemented to relate the tables. Your database is considered to be: A. fragmented. B. normalized to third normal form. C. denormalized. D. normalized only to first normal form.

B. normalized to third normal form.

52. You are creating a table that has a column named Description. The Description column must store a string between 1 and 3500 characters in length. The Description column must support text written in multiple languages. Which data type will require the least amount of storage space? A. varbinary B. nvarchar(3500) C. nchar(3500) D. varchar(3500)

B. nvarchar(3500)

How do the DELETE statement differ from the TRUNCATE statement?

Both DELETE and TRUNCATE statements delete rows from a table, however, the DELETE statement does not free the space used by those rows while the TRUNCATE statement does.

How are traditional databases organized?

By fields, records, and files.

53. Which data type can store a floating point value between 0 and 200,000 to four decimal places of accuracy with the least amount of required storage? A. money B. numeric C. decimal D. smallmoney

D. smallmoney

38. Which statement shows a valid use of the CREATE VIEW statement? A. CREATE VIEW StudentsByGrade AS SELECT FirstName, LastName, Grade FROM Grades ORDER BY Grade B. CREATE VIEW ClassAverages AS SELECT Class, AVG(Grade) FROM Grades GROUP BY Class ORDER BY Grade DESC WITH CHECK OPTION C. CREATE VIEW TopStudents AS SELECT TOP 10 FirstName, LastName, Grade FROM Grades ORDER BY Grade D. CREATE VIEW AllStudents AS SELECT FirstName, LastName, Grade FROM Grades WITH SCHEMABINDING

C. CREATE VIEW TopStudents AS SELECT TOP 10 FirstName, LastName, Grade FROM Grades ORDER BY Grade

106. What is the maximum number of clustered indexes that a database table can have? A. 256 B. 2 C. 1 D. 999

C. 1

34. What should you include in a CREATE TABLE statement for an integer-type column that must be assigned a value between 1 and 10 or not be assigned a value at all? A. A FOREIGN KEY constraint B. A DEFAULT definition C. A CHECK constraint D. A PRIMARY KEY constraint

C. A CHECK constraint

37. At a minimum, what is required within a column definition in a CREATE TABLE statement? A. A table name and a check constraint B. A column name and constraint C. A column name and data type D. A table name and a default definition

C. A column name and data type

49. What is a primary difference between a function and a stored procedure? A. A stored procedure fires automatically in response to an event. B. A stored procedure cannot return a value to the caller. C. A function can be directly referenced in a SELECT statement. D. A function cannot return a value to the caller.

C. A function can be directly referenced in a SELECT statement.

40. Your database includes the tables shown in the exhibit. You need to allow users to query the ProductID, StoreID, Description, and Price for only products at stores in California. Which statement should you use? A. ALTER VIEW B. CREATE INDEX C. CREATE VIEW D. CREATE TRIGGER

C. CREATE VIEW

39. When a view definition contains a join: A. no DML statements can be performed using the view. B. no rows will be visible through the view. C. DML statements can be performed using the view, but only on a single base table. D. no rows from either table can be deleted using the view.

C. DML statements can be performed using the view, but only on a single base table.

35. What should you use to automatically assign sequential integer values to a column? A. CHECK B. ALTER C. IDENTITY D. DEFAULT

C. IDENTITY

104. An index that includes all columns used by the query is known as a: A. clustered index. B. nonclustered index. C. covering index. D. full-text index.

C. covering index.

54. What data type should you choose for a column that will store the date and time of a monetary transaction that also stores time zone information? A. time B. datetime2 C. datetimeoffset D. timestamp

C. datetimeoffset

44. An inline table-valued function: A. can have multiple statements within a BEGIN...END block in the body. B. must be deterministic. C. includes only a single SELECT statement. D. cannot accept any parameters.

C. includes only a single SELECT statement.

47. A user-defined function: A. cannot be directly referenced in a SELECT statement. B. can only be executed using an EXECUTE statement. C. must contain a RETURNS clause and a RETURN statement. D. must contain either a RETURNS clause or a RETURN statement.

C. must contain a RETURNS clause and a RETURN statement.

100. What is one result of normalizing a database? A. The number of tables is fewer. B. Tables have more columns. C. Data redundancy increases. D. Data redundancy is minimized.

D. Data redundancy is minimized.

36. Which column definition will successfully create a column? A. MyAmount money(5,2) DEFAULT 200 B. MyAmount money CHECK(BETWEEN 100 AND 500) C. MyAmount money>100 D. MyAmount money CHECK(MyAmount BETWEEN 100 AND 500)

D. MyAmount money CHECK(MyAmount BETWEEN 100 AND 500)

51. Which data type can store a whole number between -200 and 200 with the least amount of storage? A. tinyint B. float C. int D. numeric E. smallint

E. smallint

How do relational databases differ from flat-type databases and hierarchical databases?

Flat type databases hold only one record per line and are the simplest type of database. A hierarchical database is a bit more complex having parent/child relation within its tables. In a hierarchical database a parent table can have multiple children tables, but a child table cannot have more than one parent table. A relational database is similar to a hierarchical database, however unlike in hierarchical database a relational database allows for children tables to have multiple parent tables.

How are tables organized within a database?

The tables in a database are organized into rows and columns, with columns corresponding to an attribute and a row - to a single record or an instance of each column.

Why are query useful?

They allow for practical information retrieval because they eliminate the need to go through all the records manually to find what you are looking for.

What are the three fundamental characteristics of databases?

They can store billions rows of data. They are limited to computer's available hard disk storage. They are optimized to use all of the computer's memory to improve performance.

Name all the Data Definition Language statements.

USE, CREATE, ALTER, DROP, TRUNCATE

Name each type of constraint

Unique, Check, Default, Not Null, Primary Key, and Foreign Key

Data Definition Language (DDL)

part of Transact-SQL that deals with creating new objects in the database.

MERGE

performs update, insert and delete operations on a table based on a result of a join with another table.

Foreign key constraint

points to a primary key in the same or another table.

XQuery

programming language used to execute a query of XML data.

Transact-SQL

programming language used to program in SQL Server.

DROP

removes an object from a database.

TRUNCATE

removes rows from a table and frees the space used by them.

DELETE

removes rows from a table or a view but does not free the space used by them.

What are the disadvantages of indexes?

they need to be created and update and as a result take up space on hard disk.

Primary key constraint

uniquely identifies each record in a table. Cannot be duplicate or NULL.

SQL Server Management Studio (SSMS)

used in Microsoft SQL Server as a primary tool for managing servers and databases graphically.