Microsoft MTA Database Fundamentals 98-364

Question 1

The terms “bitmap,” “b-tree,” and “hash” refer to which type of database structure? A. View B. Function C. Index D. Stored procedure E. Trigger

Answer 1

C. Index

Question 2

One reason to add an index is to: A. Decrease storage space. B. Increase database security. C. Improve performance of select statements. D. Improve performance of insert statements.

Answer 2

C. Improve performance of select statements.

Question 3

Which key uniquely identifies a row in a table? A. foreign B. primary C. local D. superkey

Answer 3

B. primary

Question 4

At 3:00 P.M. (1500 hours), you create a backup of your database. At 4:00 P.M. (1600 hours), you create a table named Customer and import data into the table. At 5:00 P.M. (1700 hours), your server fails. You run a script to apply only the 3:00 P.M. backup to your database. What is the result of the script? A. The Customer table no longer exists. B. The Customer table is unaffected. C. The Customer table exists but has no data. D. The script fails.

Answer 4

A. The Customer table no longer exists.

Question 5

In which situation do you need to perform a restore on a database? A. when data becomes corrupted in the database B. when you need to roll back a transaction C. when you encounter an error in your application D. when data needs to be deleted from the database

Answer 5

A. when data becomes corrupted in the database

Question 6

Which command should you use to give a user permission to read the data in a table? A. ALLOW SELECT B. LET READ C. PERMIT READ D. GRANT SELECT

Answer 6

D. GRANT SELECT

Question 7

You need to enable a new employee to authenticate to your database. Which command should you use? A. ALLOW USER B. CREATE USER C. ADD USER D. INSERT USER E. ALTER USER

Answer 7

B. CREATE USER

Question 8

Which keyword can be used in a create table statement? A. ORDER BY B. DISTINCT C. GROUP BY D. UNIQUE

Answer 8

D. UNIQUE

Question 9

You need to store product quantities, and you want to minimize the amount of storage space that is used. Which data type should you use? A. INTEGER B. DOUBLE C. COUNT D. FLOAT

Answer 9

A. INTEGER

Question 10

You need to store product names that vary from three to 30 characters. You also need to minimize the amount of storage space that is used. Which data type should you use? A. VARCHAR (3, 30) B. CHAR (3, 30) C. VARCHAR (30) D. CHAR (30)

Answer 10

C. VARCHAR (30)

Question 11

One reason to create a stored procedure is to: A. Improve performance. B. Minimize storage space. C. Bypass case sensitivity requirements. D. Give the user control of the query logic.

Answer 11

A. Improve performance.

Question 12

Which permission does a user need in order to run a stored procedure? A. EXECUTE B. ALLOW C. CALL D. RUN

Answer 12

A. EXECUTE

Question 13

You have a table named Product. You create a view that includes all the products from the Product table that are in the Furniture category. You execute a statement on the Product table that deletes all the products in the Furniture category. After you execute the statement, the result set of the view is: A. Empty B. Deleted C. Unchanged D. Archived

Answer 13

Answer : A

Question 14

In SQL, an insert statement is used to add a: A. User to a database. B. Row of data to a table. C. Table to a database. D. Column to a table definition.

Answer 14

B. Row of data to a table.

Question 15

You have two tables. Each table has three rows. How many rows will be included in the Cartesian product of these two tables? A. 0 B. 3 C. 6 D. 9

Answer 15

Answer : D

Question 16

You are writing an SQL statement to retrieve rows from a table. Which data manipulation language (DML) command should you use? A. READ B. SELECT C. OUTPUT D. GET

Answer 16

B. SELECT

Question 17

Which constraint ensures a unique value in the ID column for each customer? A. DISTINCT B. FOREIGN KEY C. SEQUENTIAL D. PRIMARY KEY

Answer 17

D. PRIMARY KEY

Question 18

The component that holds information for a single entry in a table is called a: A. Data type B. Row C. Column D. View

Answer 18

Answer : B

Question 19

You execute the following statement: SELECT EmployeeID, FirstName, DepartmentName FROM Employee, Department - This type of operation is called a/an: A. Intersection B. Outer join C. Equi-join D. Cartesian product

Answer 19

D. Cartesian product

Question 20

Which command should you use to add a column to an existing table? A. MODIFY B. ALTER C. UPDATE D. INSERT E. CHANGE

Answer 20

B. ALTER

Question 21

You are writing a select statement to find every product whose name contains a specific character. Which keyword should you use in your where clause? A. FIND B. BETWEEN C. INCLUDES D. LIKE

Answer 21

D. LIKE

Question 22

A database contains two tables named Customer and Order. You execute the following statement: DELETE FROM Order - WHERE CustomerID = 209 - What is the result? A. The first order for CustomerID 209 is deleted from the Order table. B. All orders for CustomerID 209 are deleted from the Order table, and CustomerID 209 is deleted from the Customer table. C. All orders for CustomerID 209 are deleted from the Order table. D. CustomerID 209 is deleted from the Customer table.

Answer 22

C. All orders for CustomerID 209 are deleted from the Order table.

Question 23

You have a table that contains information about all students in your school. Which SQL keyword should you use to change a student’s first name in the table? A. UPDATE B. CHANGE C. SELECT D. INSERT

Answer 23

A. UPDATE

Question 24

Which keyword would you use in a select statement to return rows that meet a specific condition? A. WHERE B. UNION C. ORDER BY D. FROM

Answer 24

A. WHERE

Question 25

Which command should you use to remove a table from a database? A. REMOVE TABLE B. UPDATE TABLE C. DROP TABLE D. DELETE TABLE

Answer 25

C. DROP TABLE

Question 26

You need to store the contact information for each student in your school database. You should store each student’s information in a: A. Stored procedure B. Function C. Row D. Variable

Answer 26

C. Row

Question 27

Which category of SQL statements is used to add, remove, and modify database structures? A. Data access language (DAL) B. Data manipulation language (DML) C. Data control language (DCL) D. Data definition language (DDL)

Answer 27

D. Data definition language (DDL)

Question 28

You have a Customer table and an Order table. You join the Customer table with the Order table by using the CustomerID column. The results include: All customers and their orders - -> Customers who have no orders Which type of join do these results represent? A. Complete join B. Partial join C. Inner join D. Outer join

Answer 28

D. Outer join

Question 29

Data in a database is stored in: A. Tables B. Queries C. Data types D. Stored procedures

Answer 29

A. Tables

Question 30

You have a table named Student that contains 100 rows. Some of the rows have a NULL value in the FirstName column. You execute the following statement: DELETE FROM Student - What is the result? A. All rows in the table will be deleted. B. All rows containing a NULL value in the FirstName column will be deleted. C. You will receive an error message. D. All rows and the table definition will be deleted.

Answer 30

Answer : A

Question 31

You need to establish a set of permissions that you can routinely assign to new users. What should you create? A. Resource B. Group C. List D. Role

Answer 31

D. Role

Question 32

Which database term is used to describe the process of applying a backup to a damaged or corrupt database? A. Recover B. Restore C. Commit D. Attach

Answer 32

B. Restore

Question 33

Denormalization is performed in order to: A. Reduce redundancy. B. Eliminate repeating groups. C. Create smaller tables. D. Improve query performance.

Answer 33

D. Improve query performance.

Question 34

You have a table named Product that contains one million rows. You need to search for product information in the Product table by using the product’s unique ID. What will make this type of search more efficient? A. A cursor B. A subquery C. A trigger D. An index

Answer 34

D. An index

Question 35

Which two keys establish a relationship between two tables? (Choose two.) A. candidate B. foreign C. superkey D. local E. primary

Answer 35

Answer : B, E

Question 36

One difference between a function and a stored procedure is that a function: A. Must be called from a trigger. B. Must return a value. C. Cannot contain a transaction. D. Cannot accept parameters.

Answer 36

B. Must return a value.

Question 37

Which keyword must be included in a create view statement? A. WHERE B. ORDER BY C. UPDATE D. SELECT

Answer 37

Answer : D

Question 38

You need to remove a view named EmployeeView from your database. Which statement should you use? A. DELETE VIEW EmployeeView B. DELETE EmployeeView C. DROP EmployeeView D. DROP VIEW EmployeeView

Answer 38

D. DROP VIEW EmployeeView

Question 39

A named group of SQL statements that can be executed in a database is called a: A. Subroutine B. Formula C. Stored procedure D. Method

Answer 39

C. Stored procedure

Question 40

A view can be used to: A. Save an extra copy of data stored in a separate table. B. Limit access to specific rows or columns of data in a table. C. Ensure referential integrity. D. Save historical data before deleting it from the base table.

Answer 40

B. Limit access to specific rows or columns of data in a table.

Question 41

On which database structure does an update statement operate? A. Table B. User C. Trigger D. Role

Answer 41

A. Table

Question 42

You delete rows in a table named Order. The corresponding rows in the OrderItem table are automatically deleted. This process is an example of a/an: A. Inherited delete B. Cascade delete C. Functional delete D. Waterfall delete E. Domino delete

Answer 42

B. Cascade delete

Question 43

You have a table named Employee that includes four columns. You execute the following statement: SELECT * FROM Employee - Which columns are returned? A. all columns B. only the last column C. only the first column D. only the first and last columns

Answer 43

A. all columns

Question 44

Which two elements are required to define a column? (Choose two.) A. A name B. A key C. An index D. A data type

Answer 44

Answer : A, D

Question 45

What defines the amount of storage space that is allocated to a value in a column? A. format B. key C. data type D. validator

Answer 45

C. data type

Question 46

You are creating a table to store customer data. The AccountNumber column uses values that always consist of one letter and four digits. Which data type should you use for the AccountNumber column? A. CHAR B. BYTE C. DOUBLE D. SMALLINT

Answer 46

A. CHAR

Question 47

What is one difference between an update statement and a delete statement? A. An update statement can change only one row. B. A delete statement cannot use a where clause. C. An update statement does not remove rows from a table. D. A delete statement works only within a stored procedure.

Answer 47

C. An update statement does not remove rows from a table.

Question 48

You have a Department table and an Employee table in your database. You need to ensure that an employee can be assigned to only an existing department. What should you apply to the Employee table? A. A primary key B. An index C. A foreign key D. A unique constraint E. A data type

Answer 48

D. A unique constraint

Question 49

You need to store the first name, last name, and student ID for 100 students. This information will be stored in a table as: A. 100 items and three cells. B. 100 rows and three columns. C. three rows and 100 columns. D. three items and 100 cells.

Answer 49

B. 100 rows and three columns.

Question 50

What are three valid data manipulation language (DML) commands? (Choose three.) A. INSERT B. COMMIT C. DELETE D. OUTPUT E. UPDATE

Answer 50

Answer : ACE Explanation: Data manipulation languages have their functional capability organized by the initial word in a statement, which is almost always a verb. In the case of SQL, these verbs are: -> SELECT … FROM … WHERE … -> INSERT INTO … VALUES … -> UPDATE … SET … WHERE … -> DELETE FROM … WHERE …

Question 51

You assign User1 a set of permissions that include the WITH GRANT OPTION. The WITH GRANT OPTION enables User1 to: A. request a log of permission use. B. delegate permissions to other users. C. create new database users. D. view other users’ permissions.

Answer 51

C. create new database users.

Question 52

Which type of index changes the order in which the data is stored in a table? A. non-sequential B. sequential C. non-clustered D. clustered

Answer 52

A. non-sequential

Question 53

Which statement should you use to remove a foreign key? A. ALTER TABLE B. DELETE TABLE C. DELETE FOREIGN KEY D. ALTER FOREIGN KEY

Answer 53

A. ALTER TABLE

Question 54

First normal form requires that a database excludes: A. Foreign keys B. Composite keys C. Duplicate rows D. Repeating groups

Answer 54

D. Repeating groups

Question 55

You execute a statement inside a transaction to delete 100 rows from a table. The transaction fails after only 40 rows are deleted. What is the result in the database? A. The table will be corrupted. B. Forty (40) rows will be deleted from the table. C. The transaction will restart. D. No rows will be deleted from the table.

Answer 55

D. No rows will be deleted from the table.

Question 56

You have a table that contains product IDs and product names. You need to write an UPDATE statement to change the name of a specific product to glass. What should you include in the update statement? A. SET ProduetName = ‘glass’ B. LET ProduetName = ‘glass’ C. EXEC ProduetName = ‘glass’ D. ASSIGN ProduetName = ‘glass’

Answer 56

A. SET ProduetName = ‘glass’

Question 57

On which database structure does an insert statement operate? A. Role B. Trigger C. User D. Stored procedure E. Table

Answer 57

E. Table

Question 58

You have a table of products with fields for ProductID, Name, and Price. You need to write an UPDATE statement that sets the value in the InStock field to Yes for a specific ProductID. Which clause should you use in your update statement? A. THAT B. WHERE C. GROUP BY D. HAVING

Answer 58

B. WHERE

Question 59

You need to combine the results of two queries into a single result that contains all of the rows from both queries. Which Structured Query Language (SQL) statement should you use? A. TRUNCATE B. JOIN C. EXCEPT D. UNION

Answer 59

D. UNION

Question 60

You accept an IT internship at a local charity. The charity wants you to help them with compliance and auditing requirements. You need to ensure that a column or combination of columns uniquely identifies each row of a table. Which constraint should you define? A. Primary key B. Secondary key C. Foreign key D. Default key

Answer 60

C. Foreign key

Question 61

While attending college, you accept an IT internship at a local charity. The charity needs to report on data that is related and exists in two tables. You need to establish a relationship between the data that is in the two tables. Which constraint should you define? A. Foreign key B. Index key C. Link key D. Default key

Answer 61

B. Index key

Question 62

You have two tables named Salesperson and Sales. You need to ensure that each record in the Sales table has a valid associated salesperson record in the Salesperson table. Which database object should you add to the Sales table? A. Nonclustered index B. Primary key C. Foreign key D. Clustered index

Answer 62

B. Primary key

Question 63

You need to rename a column in a database table. Which data definition language (DDL) statement should you use? A. ALTER B. INSERT C. CREATE D. UPDATE

Answer 63

A. ALTER

Question 64

Answer 64

B

Question 65

Answer 65

D

Question 66

Answer 66

D

Question 67

Answer 67

A

Question 68

Answer 68

B

Question 69

Answer 69

D

Question 70

Answer 70

A

Question 71

Answer 71

D

Question 72

Answer 72

A

Question 73

Answer 73

D

Question 74

Answer 74

C

Question 75

Answer 75

C

Question 76

Answer 76

Question 77

Answer 77

Question 78

Answer 78

A

Question 79

Answer 79

A

Question 80

Answer 80

A

Question 81

Answer 81

Question 82

Answer 82

Question 83

Answer 83

Question 84

Answer 84

Question 85

Answer 85

Question 86

Answer 86

D

Question 87

Answer 87

D

Question 88

Answer 88

A

Question 89

Answer 89

A

Question 90

Answer 90

B

Question 91

Answer 91

D

Question 92

Answer 92

D