MTA-Database-Reviewer-2-1 PDF

Database Fundamentals v1.0 (98-364) Question 1 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 : AD Question 2 What defines the amount of storage space that is allocated to a value in a column? A. for...

Database Fundamentals v1.0 (98-364) Question 1 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 : AD Question 2 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 : C Question 3 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 : A Question 4 Question 5 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 : C Question 6 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 : B Question 7 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... References: http://en.wikipedia.org/wiki/Data_manipulation_language Question 8 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 : C Question 9 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 : A Question 10 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 : A Question 11 First normal form requires that a database excludes: A. Foreign keys B. Composite keys C. Duplicate rows D. Repeating groups Answer : D Question 12 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 : D Question 13 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 : A Question 14 Your database contains a table named Customer. You need to delete the record from the Customer table that has a CusromerID of 12345. Which statement should you use? A. Option A B. Option B C. Option C D. Option D Answer : D Question 15 On which database structure does an insert statement operate? A. Role B. Trigger C. User D. Stored procedure E. Table Answer : E Question 6 ( Topic 1 ) Which statement creates a composite key? A. Option A B. Option B C. Option C D. Option D Answer : D Question 7 ( Topic 1 ) 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 : A Question 8 ( Topic 1 ) 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 : A Question 9 ( Topic 1 ) 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 : D Question 10 ( Topic 1 ) 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 : B Question 11 ( Topic 1 ) Which keyword can be used in a create table statement? A. ORDER BY B. DISTINCT C. GROUP BY D. UNIQUE Answer : D Question 12 ( Topic 1 ) 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 : A Question 13 ( Topic 1 ) Which statement will result in the creation of an index? A. Option A B. Option B C. Option C D. Option D Answer : A Question 14 ( Topic 1 ) You have the following table definition: CREATE TABLE Road - (RoadID INTEGER NOT NULL, Distance INTEGER NOT NULL) The Road table contains the following data: You execute the following statement: INSERT INTO Road VALUES (1234, 36) What is the result?  A. an error stating that NULL values are not allowed B. a new row in the table C. an error stating that duplicate IDs are not allowed D. a syntax error Answer : B Question 15 ( Topic 1 ) 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 : C Question 16 ( Topic 1 ) 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 : A Question 17 ( Topic 1 ) Which permission does a user need in order to run a stored procedure? A. EXECUTE B. ALLOW C. CALL D. RUN Answer : A Question 18 ( Topic 1 ) 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 Question 19 ( Topic 1 ) 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 : B Question 20 ( Topic 1 ) 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 Question 21 ( Topic 1 ) 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 : B Question 22 ( Topic 1 ) Which constraint ensures a unique value in the ID column for each customer? A. DISTINCT B. FOREIGN KEY C. SEQUENTIAL D. PRIMARY KEY Answer : D Question 23 ( Topic 1 ) 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 Question 24 ( Topic 1 ) 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 : D Question 25 ( Topic 1 ) Which command should you use to add a column to an existing table? A. MODIFY B. ALTER C. UPDATE D. INSERT E. CHANGE Answer : B Question 26 ( Topic 1 ) You have the following table definition: CREATE TABLE Product - (ID INTEGER PRIMARY KEY, Name VARCHAR(20), Quantity INTEGER) The Product table contains the following data. You execute the following statement: SELECT Name FROM Product WHERE Quantity IS NOT NULL How many rows are returned? A. 0 B. 1 C. 2 D. 3 E. 4 Answer : D Question 27 ( Topic 1 ) 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 : D Question 28 ( Topic 1 ) 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 : C Question 29 ( Topic 1 ) You have a table named Product. The Product table has columns for ProductDescription and ProductCategory. You need to change the ProductCategory value for all the spoons in the Product table to 43. Which statement should you use? A. Option A B. Option B C. Option C D. Option D Answer : A Question 30 ( Topic 1 ) 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 : A Question 31 ( Topic 1 ) You need to populate a table named EmployeeCopy with data from an existing table named Employee. Which statement should you use? A. Option A B. Option B C. Option C D. Option D Answer : D Question 32 ( Topic 1 ) You execute the following statement: This statement is an example of a/an: A. Subquery  B. Union C. Outer join D. Cartesian product Answer : A Question 33 ( Topic 1 ) 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 : A Question 34 ( Topic 1 ) You have a table named Employee that includes the following columns: EmployeeID - Smp1oyeeName - Which statement should you use to return the number of rows in the table? A. Option A B. Option B C. Option C D. Option D Answer : D Question 35 ( Topic 1 ) The Product table contains the following data. You execute the following statement: SELECT COUNT(*) FROM Product WHERE Quantity > 18 What is the value returned by this statement? A. 1 B. 2 C. 3 D. 4 Answer : C Question 36 ( Topic 1 ) 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 : C Question 37 ( Topic 1 ) 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 : C Question 38 ( Topic 1 ) 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 : D Question 39 ( Topic 1 ) You have a Customer table and an Order table. You join the Customer table with the Order table by using the CusromerID 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 : D Question 40 ( Topic 1 ) Data in a database is stored in: A. Tables B. Queries C. Data types D. Stored procedures Answer : A Question 41 ( Topic 1 ) 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 Question 42 ( Topic 1 ) 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 : D Question 43 ( Topic 1 ) 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 : B Question 44 ( Topic 1 ) You need to disable User1's access to view the data in the Customer table. Which statement should you use? A. Option A B. Option B  C. Option C D. Option D Answer : C Question 45 ( Topic 1 ) Denormalization is performed in order to: A. Reduce redundancy. B. Eliminate repeating groups. C. Create smaller tables. D. Improve query performance. Answer : D Question 46 ( Topic 1 ) 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 : D Question 47 ( Topic 1 ) You have a table named Product that contains the following data. The PrcducrID column is the primary key. The CategoryID column is a foreign key to a separate table named Category. You execute the following statement: INSERT INTO Product - VALUES (3296, 'Table', 4444) What is the result? A. a foreign key constraint violation B. a syntax error  C. a new row in the Product table D. a primary key constraint violation E. a new row in the Category table Answer : D Question 48 ( Topic 1 ) Which two keys establish a relationship between two tables? (Choose two.) A. candidate B. foreign C. superkey D. local E. primary Answer : BE Question 49 ( Topic 1 ) 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 : B Question 50 ( Topic 1 ) Which keyword must be included in a create view statement? A. WHERE B. ORDER BY C. UPDATE D. SELECT Answer : D Question 1 ( Topic 1 ) DRAG DROP - Note: This question is part of a series of questions that use the same scenario. For your convenience, the scenario is repeated in each question. Each question presents a different goal and answer choices, but the text of the scenario is exactly the same in each question in this series. You have a database named Sales that contains the following database tables: Customer, Order, and Products. The Products table and the Order table are shown in the following diagram. The customer table includes a column that stores the data for the last order that the customer placed. You plan to create a table named Leads. The Leads table is expected to contain approximately 20,000 records. Storage requirements for the Leads table must be minimized. Changes to the price of any product must be less a 25 percent increase from the current price. The shipping department must be notified about order and shipping details when an order is entered into the database. You need to implement the appropriate table objects. Which object should you use for each table? To answer, drag the appropriate objects to the correct tables. Each object may be used once, more than once, or not at all. You may need to drag the split bar between panes or scroll to view content. Select and Place: Answer : The Products table needs a primary key constraint on the ProductID field. The Orders table needs a foreign key constraint on the productID field, with a reference to the ProductID field in the Products table. Next Question Question 2 ( Topic 1 ) HOTSPOT - Note: This question is part of a series of questions that use the same scenario. For your convenience, the scenario is repeated in each question. Each question presents a different goal and answer choices, but the text of the scenario is exactly the same in each question in this series. You have a database named Sales that contains the following database tables: Customer, Order, and Products. The Products table and the Order table are shown in the following diagram. The customer table includes a column that stores the data for the last order that the customer placed. You plan to create a table named Leads. The Leads table is expected to contain approximately 20,000 records. Storage requirements for the Leads table must be minimized. You need to implement a stored procedure that deletes a discontinued product from the Products table. You identify the following requirements: -> If an open order includes a discontinued product, the records for the product must not be deleted. -> The stored procedure must return a custom error message if a product record cannot be deleted. The message must identify the OrderID for the open order. What should you do? To answer, select the appropriate Transact-SQL segments in the answer area. Hot Area: Answer : Using TRY...CATCH in Transact-SQL Errors in Transact-SQL code can be processed by using a TRY"¦CATCH construct. TRY"¦CATCH can use the following error function to capture error information: ERROR_MESSAGE() returns the complete text of the error message. The text includes the values supplied for any substitutable parameters such as lengths, object names, or times. References: https://technet.microsoft.com/en-us/library/ms179296(v=sql.105).aspx Next Question Question 3 ( Topic 1 ) HOTSPOT - Note: This question is part of a series of questions that use the same scenario. For your convenience, the scenario is repeated in each question. Each question presents a different goal and answer choices, but the text of the scenario is exactly the same in each question in this series. You have a database named Sales that contains the following database tables: Customer, Order, and Products. The Products table and the Order table are shown in the following diagram. The customer table includes a column that stores the data for the last order that the customer placed. You plan to create a table named Leads. The Leads table is expected to contain approximately 20,000 records. Storage requirements for the Leads table must be minimized. You need to create triggers that meet the following requirements: -> Optimize the performance and data integrity of the tables. -> Provide a custom error if a user attempts to create an order for a customer that does not exist. -> In the Customers table, update the value for the last order placed. -> Complete all actions as part of the original transaction. In the table below, identify the trigger types that meet the requirements. NOTE: Make only selection in each column. Each correct selection is worth one point. Hot Area: Answer : INSTEAD OF INSERT triggers can be defined on a view or table to replace the standard action of the INSERT statement. AFTER specifies that the DML trigger is fired only when all operations specified in the triggering SQL statement have executed successfully. References: https://technet.microsoft.com/en-us/library/ms175089(v=sql.105).aspx Next Question Question 4 ( Topic 1 ) HOTSPOT - Note: This question is part of a series of questions that use the same scenario. For your convenience, the scenario is repeated in each question. Each question presents a different goal and answer choices, but the text of the scenario is exactly the same in each question in this series. You have a database named Sales that contains the following database tables: Customer, Order, and Products. The Products table and the Order table are shown in the following diagram. The customer table includes a column that stores the data for the last order that the customer placed. You plan to create a table named Leads. The Leads table is expected to contain approximately 20,000 records. Storage requirements for the Leads table must be minimized. The Leads table must include the columns described in the following table. The data types chosen must consume the least amount of storage possible. You need to select the appropriate data types for the Leads table. In the table below, identify the data type that must be used for each table column. NOTE: Make only one selection in each column. Hot Area: Answer : Bit is aTransact-SQL integer data type that can take a value of 1, 0, or NULL. Smallint is a Transact-SQL integer data type that can take a value in the range from -32,768 to 32,767. int, bigint, smallint, and tinyint (Transact-SQL) Exact-number data types that use integer data. References: https://msdn.microsoft.com/en-us/library/ms187745.aspx https://msdn.microsoft.com/en-us/library/ms177603.aspx Next Question Question 5 ( Topic 1 ) HOTSPOT - Note: This question is part of a series of questions that use the same scenario. For your convenience, the scenario is repeated in each question. Each question presents a different goal and answer choices, but the text of the scenario is exactly the same in each question in this series. You have a database named Sales that contains the following database tables: Customer, Order, and Products. The Products table and the Order table are shown in the following diagram. The customer table includes a column that stores the data for the last order that the customer placed. You plan to create a table named Leads. The Leads table is expected to contain approximately 20,000 records. Storage requirements for the Leads table must be minimized. You need to modify the database design to meet the following requirements: -> Rows in the Orders table must always have a valid value for the ProductID column. -> Rows in the Products table must not be deleted if they arepart of any rows in the Orders table. -> All rows in both tables must be unique. In the table below, identify the constraint that must be configured for each table. NOTE: Make only one selection in each column. Hot Area: Answer : A FOREIGN KEY in one table points to a PRIMARY KEY in another table. Here the foreign key constraint is put on the ProductID in the Orders, and points to the ProductID of the Products table. With a check constraint on the ProductID we can ensure that the Products table contains only unique rows. References: http://www.w3schools.com/sql/sql_foreignkey.asp Question 1 ( Topic 1 ) You have the following flowchart: Use the drop-down menus to select the answer choice that completes each statement Each correct selection is worth one point. Answer : Explanation: C:\Users\Kamran\Desktop\image.jpg Next Question Question 2 ( Topic 1 ) Which language uses Data Definition Language (DDL) and Data Manipulation Language (DML)?  A. SQL B. C++ C. Pascal D. Java Answer : A Explanation: SQL uses DDL and DML. Next Question Question 3 ( Topic 1 ) For each of the following statements, select Yes if the statement is true. Otherwise, select No. Each correct selection is worth one point. Answer : Explanation: C:\Users\Kamran\Desktop\image.jpg Next Question Question 4 ( Topic 1 ) How many parameters can a default constructor have? A. 0 B. 1  C. 2 D. 3 or more Answer : A Explanation: If a class contains no instance constructor declarations, a default instance constructor is automatically provided. That default constructor simply invokes the parameterless constructor of the direct base class. Next Question Question 5 ( Topic 1 ) You have a server that limits the number of data connections. What should you use to optimize connectivity when the number of users exceeds the number of available connections? A. Connection timeouts B. Named pipes C. Normalization D. Connection pooling Answer : D Explanation: In software engineering, a connection pool is a cache of database connections maintained so that the connections can be reused when future requests to the database are required. https://www.itexams.com/exam/70-465 https://www.itexams.com/exam/98-361