Microsoft MTA Database Fundamentals 98-364 Flashcards
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
C. Index
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.
C. Improve performance of select statements.
Which key uniquely identifies a row in a table? A. foreign B. primary C. local D. superkey
B. primary
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.
A. The Customer table no longer exists.
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
A. when data becomes corrupted in the database
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
D. GRANT SELECT
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
B. CREATE USER
Which keyword can be used in a create table statement? A. ORDER BY B. DISTINCT C. GROUP BY D. UNIQUE
D. UNIQUE
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
A. INTEGER
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)
C. VARCHAR (30)
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.
A. Improve performance.
Which permission does a user need in order to run a stored procedure? A. EXECUTE B. ALLOW C. CALL D. RUN
A. EXECUTE
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 : A
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.
B. Row of data to a table.
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 : D
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
B. SELECT
Which constraint ensures a unique value in the ID column for each customer? A. DISTINCT B. FOREIGN KEY C. SEQUENTIAL D. PRIMARY KEY
D. PRIMARY KEY
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 : B
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
D. Cartesian product
Which command should you use to add a column to an existing table? A. MODIFY B. ALTER C. UPDATE D. INSERT E. CHANGE
B. ALTER
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
D. LIKE
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.
C. All orders for CustomerID 209 are deleted from the Order table.
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
A. UPDATE
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
A. WHERE
Which command should you use to remove a table from a database? A. REMOVE TABLE B. UPDATE TABLE C. DROP TABLE D. DELETE TABLE
C. DROP TABLE
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
C. Row
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)
D. Data definition language (DDL)
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
D. Outer join
Data in a database is stored in: A. Tables B. Queries C. Data types D. Stored procedures
A. Tables
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 : A
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
D. Role
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
B. Restore
Denormalization is performed in order to: A. Reduce redundancy. B. Eliminate repeating groups. C. Create smaller tables. D. Improve query performance.
D. Improve query performance.
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
D. An index
Which two keys establish a relationship between two tables? (Choose two.) A. candidate B. foreign C. superkey D. local E. primary
Answer : B, E
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.
B. Must return a value.
Which keyword must be included in a create view statement? A. WHERE B. ORDER BY C. UPDATE D. SELECT
Answer : D
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
D. DROP VIEW EmployeeView
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
C. Stored procedure
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.
B. Limit access to specific rows or columns of data in a table.
On which database structure does an update statement operate? A. Table B. User C. Trigger D. Role
A. Table
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
B. Cascade delete
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
A. all columns
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 : A, D
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
C. data type
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
A. CHAR
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.
C. An update statement does not remove rows from a table.
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
D. A unique constraint
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.
B. 100 rows and three columns.
What are three valid data manipulation language (DML) commands? (Choose three.) A. INSERT B. COMMIT C. DELETE D. OUTPUT E. UPDATE
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 …
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.
C. create new database users.
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
A. non-sequential
Which statement should you use to remove a foreign key? A. ALTER TABLE B. DELETE TABLE C. DELETE FOREIGN KEY D. ALTER FOREIGN KEY
A. ALTER TABLE
First normal form requires that a database excludes: A. Foreign keys B. Composite keys C. Duplicate rows D. Repeating groups
D. Repeating groups
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.
D. No rows will be deleted from the table.
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’
A. SET ProduetName = ‘glass’
On which database structure does an insert statement operate? A. Role B. Trigger C. User D. Stored procedure E. Table
E. Table
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
B. WHERE
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
D. UNION
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
C. Foreign key
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
B. Index key
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
B. Primary key
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
A. ALTER
B
D
D
A
B
D
A
D
A
D
C
C
A
A
A
D
D
A
A
B
D
D
