Microsoft Practice Test Questions (All)

84. The ProductInventory table and the ReorderList table are shown in the exhibit. The ReorderID column is an identity column. The ReorderList table stores a record for each product that has been ordered. You need to add rows to the ReorderList table that includes the ProductID, the current date, and a Quantity of 100 for all products that have a Quantity of 0 in the ProductInventory table. Which statement should you use? A. INSERT INTO ReorderList (ProductID, ReorderDate, Quantity) SELECT ProductID, getdate(), 100 FROM ProductInventory WHERE Quantity=0 B. INSERT INTO ReorderList (ProductID, ReorderDate, Quantity) VALUES (SELECT ProductID FROM ProductInventory WHERE Quantity=0, getdate(), 100) C. SELECT ProductID, getdate(), 100 INTO ReorderList FROM ProductInventory WHERE Quantity=0 D. SELECT ProductID, getdate(), 100 FROM ProductInventory WHERE Quantity=0 INTO ReorderList

A. INSERT INTO ReorderList (ProductID, ReorderDate, Quantity) SELECT ProductID, getdate(), 100 FROM ProductInventory WHERE Quantity=0

82. A table named Members exists in a SQL Server 2008 database. The table has a column named MemberID that is configured as an Identity column. You need to write a query that creates a row for a member named Renee Smith with a MemberID of 75. Which Transact-SQL should you use? A. SET IDENTITY_INSERT Members ON GO INSERT INTO Members(MemberID, FirstName, LastName) VALUES (75, 'Renee', 'Smith') SET IDENTITY_INSERT Members OFF B. INSERT INTO Members(FirstName, LastName) VALUES ('Renee', 'Smith') UPDATE Members SET memberID=75 WHERE FirstName='Renee' AND LastName='Smith' C. INSERT INTO Members (MemberID, FirstName, LastName) VALUES (75, 'Renee', 'Smith') D. SET IDENTITY_INSERT Members OFF GO INSERT INTO Members(MemberID, FirstName, LastName) VALUES (75, 'Renee', 'Smith') SET IDENTITY_INSERT Members ON

A. SET IDENTITY_INSERT Members ON GO INSERT INTO Members(MemberID, FirstName, LastName) VALUES (75, 'Renee', 'Smith') SET IDENTITY_INSERT Members OFF

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

87. What must be included in an INSERT statement? A. A table or view name B. A VALUES clause C. A column list D. The INTO keyword

A. A table or view name

72. You have a database that includes an Inventory table. Attempts to set the value of the Instock column to a value less than the value in the ReorderLevel column should be logged to the Reorder table. What should you use? A. A trigger B. A calculated column C. A view D. A constraint

A. A trigger

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.

81. You need to delete rows from two unrelated tables in your SQL Server 2008 database using DELETE statements. If either of the DELETE statements fails, then you do not want any deletions to occur. What should you do? A. Create an explicit transaction that executes both statements and rolls back the changes if either statement fails or commits the changes if both are successful. B. Issue an ALTER TABLE statement that includes ON DELETE CASCADE. C. Set IMPLICIT_TRANSACTIONS to ON and execute both statements. D. Use a single DELETE statement that references both tables instead of two separate statements.

A. Create an explicit transaction that executes both statements and rolls back the changes if either statement fails or commits the changes if both are successful.

77. You need to log data about each record deleted from the Members table in the FormerMembers table. What should you create? A. DELETE trigger B. FOREIGN KEY constraint with the ON DELETE CASCADE option C. INSTEAD OF DELETE trigger D. FOREIGN KEY constraint with the ON DELETE SET DEFAULT option

A. DELETE trigger

58. Which operator returns only rows that are in both of the result sets returned by two other separate queries? A. INTERSECT B. UNION C. JOIN D. EXCEPT

A. INTERSECT

3. Where does a record of each data modification get stored before being written to the database? A. In the transaction log B. In a view C. In a database trigger D. In the Application log E. In a subquery

A. In the transaction log

2. Which of the following are characteristics of an Online Transaction Processing (OLTP) database? A. It is optimized to handle a large number of simultaneous updates, additions, and deletions. B. It consolidates data from multiple sources into a single storage structure. C. It provides a fixed source of historical data. D. It is optimized to handle a large number of analytical queries.

A. It is optimized to handle a large number of simultaneous updates, additions, and deletions.

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.

61. You have two tables in your database named CurrentCourses and PreviousCourses. Both have a CourseName column. You need to display the names of all courses from both tables, but exclude duplicate course names. Which operator should you use in your query? A. UNION B. UNION ALL C. EXCEPT D. INTERSECT

A. UNION

69. For each statement, select Yes if the statement is true or No if the statement is false. A. An inner join returns only matched rows from two tables. B. A full outer join is the default join type. C. An inner join produces a Cartesian product. D. A right outer join of two tables returns fewer rows than an inner join.

A. Yes B. No C. No D. No

5. A reason to use a relational database instead of a flat file database would be to: A. minimize the amount of redundant data stored in the database. B. ensure that only specific users can access certain data. C. allow all information to be stored in a single database table. D. ensure that aggregate information can be tracked.

A. minimize the amount of redundant data stored in the database.

4. Using a relational database allows you to minimize redundant data and index the data so data can be retrieved... A. quickly and easily. B. using a data definition language (DDL) trigger. C. without writing queries. D. as long as no filter is specified.

A. quickly and easily.

13. You need to track organization members. For each organization member, you need to keep track of the member's unique membership number, name and address, membership expiration date, and the payment date and amount for each dues payment received from the member. A table in your database would represent: A. the members of the organization. B. the address fore each member. C. the membership expiration date for each member. D. the amount of each payment made by each member.

A. the members of the organization.

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

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

86. You have an empty Product table that you created with the following statement: CREATE TABLE Product( ProdID int PRIMARY KEY, CategoryID int NOT NULL, Description varchar(35), QtyOnHand int CHECK (QtyOnHand>0 AND QtyOnHand<=50)) Which statement will successfully insert data into the Product table? A. INSERT INTO Product(Description, QtyOnHand) VALUES ('Oval Wall Mirror', 15) B. INSERT INTO Product VALUES (101, 1, 'Outdoor Extension Cord', 25) C. INSERT INTO Product(ProdID, CategoryID, Description, QtyOnHand) VALUES(101, 2, 'Oval Wall Mirror', 65) D. INSERT INTO Product VALUES (101, NULL, 'Outdoor Extension Cord', 25)

B. INSERT INTO Product VALUES (101, 1, 'Outdoor Extension Cord', 25)

75. Which statement will increase the CreditLimit by five percent for the customer with a CustID of 350? A. UPDATE Customer SET CreditLimit=CreditLimit *.05 WHERE CustID=350 B. UPDATE Customer SET CreditLimit=CreditLimit*1.05 WHERE CustID=350 C. UPDATE Customer (CreditLimit) SET CreditLimit=CreditLimit*1.05 WHERE CustID=350 D. UPDATE Customer(350) SET CreditLimit=CreditLimit*1.05

B. UPDATE Customer SET CreditLimit=CreditLimit*1.05 WHERE CustID=350

62. Which statement should you use to display a list of products sorted from highest to lowest price with products with identical prices listed alphabetically? A. SELECT ProdName, Price FROM Product ORDER BY 2, 1 B. SELECT ProdName, Price FROM Product ORDER BY Price DESC, ProdName C. SELECT ProdName, Price FROM Product ORDER BY ProdName, Price DESC D. SELECT ProdName, Price FROM Product ORDER BY Price, ProdName

B. SELECT ProdName, Price FROM Product ORDER BY Price DESC, ProdName

66. You have two database tables, Loan and Borrower. The Borrower table contains 20 rows and the Loan table contains 60 rows. You execute the following select statement: SELECT BorrowerID, BorrowerName, LoanNumber FROM Borrower, Loan How many rows will the query return? A. 20 B. 1200 C. 120 D. 60

B. 1200

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

60. You have a database that has the Employees table shown in the exhibit. The ManagerID column has a foreign key constraint that references the EmployeeID column. The ManagerID column allows Nulls. You need to write a query that retrieves a two-column result set that shows the full names of the employees who report to each manager and the employees who do not have an assigned manager. Which join type should you use? A. A cross join B. A self join C. An inner join D. A full outer join

B. A self join

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

21. Which DDL statement should you use to permanently remove a column and all of the column's data from a table? A. UPDATE B. ALTER TABLE C. DELETE D. DROP TABLE

B. ALTER TABLE

24. What happens if you issue a DELETE Employees statement and omit the WHERE and TOP clauses? A. The first row in the Employees table is deleted. B. All rows of the Employees table are deleted C. No rows are deleted from the Employees table D. The Employees database is deleted E. The Employees table is deleted

B. All rows of the Employees table are deleted

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.

73. Which keyword should you include in an UPDATE statement to set a column's value equal to a value that is stored in another table? A. VALUES B. FROM C. INTO D. OUTPUT E. LIKE

B. FROM

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.

80. You need to ensure that if a row in a parent table is deleted, the related child rows in another table are also deleted. What should you use? A. An OPTION clause in the DELETE statement B. ON DELETE CASCADE C. ON DELETE NO ACTION D. A WHERE clause in the DELETE statement

B. ON DELETE CASCADE

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

7. You are creating a relational database that will store information about physicians and their patients. Each patient may have more than one physician. Each physician may have multiple patients. What should you include? A. Two tables and a single many-to-many relationship B. Three tables and two one-to-many relationships C. Three tables and three one-to-many relationships D. Two tables and a single one-to-many relationship

B. Three tables and two one-to-many relationships

26. When would you issue a data manipulation language (DML) statement? A. To add a new column to a table B. To set an attribute value for an entity instance C. To control the physical order of a table to optimize data retrieval D. To defragment the physical storage of a table

B. To set an attribute value for an entity instance

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

28. Which statement should you use to modify a column value in a table? A. ALTER B. UPDATE C. MODIFY D. INSERT E. CREATE

B. UPDATE

8. In a relational database, data is stored in: A. a single table with relationships defined using only foreign keys. B. multiple tables that are related using primary and foreign keys. C. multiple tables that are related using only foreign keys. D. a single table with relationships defined as columns in the table.

B. multiple tables that are related using primary and foreign keys.

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)

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

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

14. You are designing a database to track customer and sales information. You want to minimize redundant data that will be stored in the database. Each customer can place one or more orders. For each order, the customer may order one or more products sold by your company. Which tables should your database design include? A. A single Order table that includes a column to identify the customer and columns for each product ordered B. An Order table, an OrderDetails table, and a Product table C. A Customer table, an Order table, an OrderDetails table, and a Product table D. A Customer table and an Order table that includes columns for each product ordered

C. A Customer table, an Order table, an OrderDetails table, and a Product table

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

85. Which of the following columns do you need to set a value for when issuing an INSERT statement? A. A column that has a DEFAULT constraint B. An IDENTITY column that does not have a PRIMARY KEY constraint C. A column of the uniqueidentifier data type D. An IDENTITY column that has a PRIMARY KEY constraint

C. A column of the uniqueidentifier 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.

15. What is represented by a column in a well-designed relational database table? A. A single instance of an entity B. Multiple instances of an entity C. A single entity attribute D. Multiple entity attributes

C. A single entity attribute

17. Which statement should you use to add a column named SubCategory to the Products table? A. INSERT B. CREATE TYPE C. ALTER TABLE D. UPDATE

C. ALTER TABLE

18. The Orders table has a column named OrderDate. It currently has a default value of today's date. You need to modify the configuration so that there is no default value. Which statement should you use? A. DROP RULE B. DELETE C. ALTER TABLE D. DROP DEFAULT

C. ALTER TABLE

22. You have created a view using the statement shown in the exhibit. You need to modify the view so that only movies with a rating of G or PG are visible through the view. What should you use? A. An ALTER VIEW statement with a WITH CHECK OPTION clause B. An ALTER TABLE statement with an ALTER COLUMN clause C. An ALTER VIEW statement with a WHERE clause D. A MODIFY TABLE statement E. A MODIFY VIEW statement

C. An ALTER VIEW statement with a WHERE clause

11. How is data about each instance of an entity stored in a relational database? A. As a column in a table B. As a table C. As a row in a table D. As a file

C. As a row in a table

20. Which DDL statement should you use to create a database object that can store information? A. CREATE TYPE B. INSERT C. CREATE TABLE D. UPDATE E. ALTER

C. CREATE TABLE

16. Users in the Market Analysis department require data from the Customers table and the SalesHistory table. They need to be able to issue a SELECT statement that retrieves the data. They should not be able to access the CreditCard or Expiration column of the Customers table. Which statement should you use to create the object? A. CREATE TABLE B. CREATE USER C. CREATE VIEW D. CREATE INDEX E. CREATE TRIGGER

C. CREATE VIEW

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

10. You are creating a relational database that will be used to track car rentals. Each renter can rent multiple cars. A car can be rented by multiple drivers. You need to reduce the amount of redundant data. How should you store the car rental data? A. Create a Cars table and a CarRentals table. B. Create a Cars table and a Renters table. C. Create a Cars table, a Renters table, and a CarRentals table. D. Create a CarRentals table.

C. Create a Cars table, a Renters table, and a CarRentals table.

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.

63. At a minimum, which clause or clauses are required in a SELECT statement that returns column values from a table? A. FROM and ORDER BY B. FROM and WHERE C. FROM D. FROM, WHERE, and GROUP BY

C. FROM

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

C. IDENTITY

64. Which clause returns only the matching rows from the two joined tables? A. CROSS JOIN B. FULL OUTER JOIN C. INNER JOIN D. RIGHT OUTER JOIN

C. INNER JOIN

88. What statement should you use to insert values from one table into another existing table that already contains data? A. SELECT INTO B. ALTER C. INSERT D. UPDATE

C. INSERT

9. What should you use to store the product id, product name, description, and price for 200 products in a relational database? A. Four tables with 200 rows in each table. B. One table with 4 rows and 200 columns C. One table with 4 columns and 200 rows D. Four tables with 200 columns in each table

C. One table with 4 columns and 200 rows

70. Which of the following are required clauses when issuing an UPDATE statement? A. WHERE B. OUTPUT C. SET D. FROM

C. SET

59. You have a database that has an OrderDetails table containing one or more rows for each order. Each order is sequentially assigned an OrderID to identify the order. You execute the following query against the OrderDetails table: SELECT TOP 20 OrderID, SUM(LineTotal) AS OrderTotal FROM OrderDetails GROUP BY OrderID ORDER BY OrderTotal DESC What is the result? A. The 20 orders with the lowest total B. A syntax error because the ORDER BY clause is incorrect C. The 20 orders with the highest total D. The first 20 orders

C. The 20 orders with the highest total

71. When would you use a .WRITE clause within an UPDATE statement? A. To update the columns with calculated values. B. To commit the changes to the database immediately. C. To update the value in a column that has the varchar(max) data type. D. To update the columns with values from another table.

C. To update the value in a column that has the varchar(max) data type.

23. Which of the following is a data manipulation language (DML) statement? A. CREATE B. DROP C. UPDATE D. ALTER

C. UPDATE

67. You execute the following query: SELECT LoanNumber, OriginalLoanAmt, InterestRate FROM Loan WHERE BorrowerID IN (SELECT BorrowerID WHERE BorrowerState='TX') What is this an example of? A. a union B. a trigger C. a subquery D. a cross join

C. a subquery

76. The ON DELETE CASCADE option: A. prevents rows that have a FOREIGN KEY constraint from being delete. B. prevents rows that are referenced through a foreign key CONSTRAINT from being deleted. C. causes rows related by a FOREIGN KEY constraint to be deleted when the referenced row is deleted. D. causes rows related by a FOREIGN KEY constraint to be deleted when the referencing row is deleted.

C. causes rows related by a FOREIGN KEY constraint to be deleted when the referenced row is deleted.

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

78. The TRUNCATE TABLE statement: A. deletes the specified number of rows from a table. B. requires more resources than the DELETE statement. C. deletes all rows from the table, but does not log each row deleted. D. deletes the specified percentage of rows from a table.

C. deletes all rows from the table, but does not log each row deleted.

65. You would include a HAVING clause in a query to: A. sort the results in descending order. B. group the results to provide totals. C. filter the results after grouping. D. combine two result sets into a single result.

C. filter the results after grouping.

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.

1. You are creating a database that will store information about pet vaccinations. You need to ensure the following: *Each pet must always be associated with an owner *Each vaccination must be associated with a pet *An owner can have multiple pets *A pet can have multiple vaccinations What should you do? A. *Create an Owners table, a Pets table, and a Vaccinations table *Create a column in the Owners table that references a column in the Pets table *Create a column in the Pets table that references a column in the Vaccinations table B. *Create a Pets table and a Vaccinations table *Create a column in the vaccinations table that references a column in the Pets table C. *Create an Owners table and a Pets table *Create a column in the Owners table that references a column in the Pets table. D. *Create an Owners table, a Pets table, and a Vaccinations table *Create a column in the Pets table that references a column in the Owners table *Create a column in the Vaccinations table that references a column in the Pets table

D. *Create an Owners table, a Pets table, and a Vaccinations table *Create a column in the Pets table that references a column in the Owners table *Create a column in the Vaccinations table that references a column in the Pets table

79. Which statement will remove all rows from the Materials table that have a Status value of 'Obsolete' but do not have a value for the VendorID column? A. DELETE MaterialID, Description, Status, VendorID FROM Materials WHERE Status='Obsolete' AND VendorID is NULL B. DELETE Materials WHERE Status='Obsolete' OR VendorID IS NULL C. DELETE FROM Materials WHERE Status='Obsolete' WHERE VendorID IS NULL D. DELETE FROM Materials WHERE Status='Obsolete' AND VendorID IS NULL

D. DELETE FROM Materials WHERE Status='Obsolete' AND VendorID IS NULL

83. When performing an insert with a VALUES clause, which column would require a specified value in the VALUES list? A. A column with a DEFAULT definition and a NOT NULL constraint B. A PRIMARY KEY column with the IDENTITY property C. A column with a DEFAULT definition D. A PRIMARY KEY column without the IDENTITY property

D. A PRIMARY KEY column without the IDENTITY property

19. Which statement is a data definition language (DDL) statement? A. UPDATE B. DELETE C. INSERT D. ALTER

D. ALTER

6. You are creating a relational database to store information about instructors and the courses that each instructor teaches. Each course is taught by a single instructor. You have created an Instructor table and a Course table as shown in the exhibit. You need to create a relationship between the Instructor table and the Course table. You need to keep duplicate data to a minimum. What should you do? A. Create a new column in the Instructor table. B. Create new columns in the Instructor table for each course taught. C. Create a new table that includes two columns. D. Create a new column in the Course table.

D. Create a new column in the Course table.

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.

12. What data structure physically stores the data in a relational database table that has a single nonclustered index? A. B-tree B. Queue C. Stack D. Heap

D. Heap

68. Which keyword should you include in your WHERE clause to return results for a VARCHAR column that contains a specific character string? A. ANY B. EXCEPT C. IN D. LIKE

D. LIKE

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)

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)

25. You can retrieve data sorted by the LastName column by using a: A. CREATE VIEW statement that includes the RANK function. B. SELECT statement with a GROUP BY clause. C. CREATE VIEW statement with an ORDER BY clause. D. SELECT statement with an ORDER BY clause.

D. SELECT statement with an ORDER BY clause.

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.

27. You have a relational database that stores information about projects within your organization. You have a Project table and a ProjectManager table as shown in the exhibit. Why would you issue a DML statement? A. To add a new column to the Project table to track each project's sponsor B. To add a new table to store the tasks associated with each project C. To create a view that can be used to query the project data D. To add a new project when a new project is launched

D. To add a new project when a new project is launched

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.

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

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

29. A DML statement can be used: A. only to add, change, or remove data from a table. B. to remove a table from an existing database. C. to create new database tables or views. D. to retrieve, add, change, or remove data from a table.

D. to retrieve, add, change, or remove data from a table.

74. Which clause of an UPDATE statement specifies the columns to be updated and the values to which they should be updated? A. WHERE B. VALUES C. FROM D. INTO E. SET

E. SET

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