Database Fundamentals Exam Questions PDF

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 2. One reason to add an index is to: A. Decrease storage space. B. Increase...

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 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. 3. You have a table that contains the following data. You break the table into the following two tables. This process is referred to as: A. defragmentation B. normalization C. fragmentation D. denormalization 4. You have a table that contains the following data. Which database term is used to describe the relationship between ProductID and ProductCategory? A. Cohort B. Relationally dependent C. Deterministic D. Functionally dependent E. Compositional 5. Which key uniquely identifies a row in a table? A. foreiqn B. primary C. local D. superkey 6. Which statement creates a composite key? A. Option A B. Option B C. Option C D. Option D 7. 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. 8. 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 9. 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 10. 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 11. Which keyword can be used in a create table statement? A. ORDER BY B. DISTINCT C. GROUP BY D. UNIQUE 12. 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 13. Which statement will result in the creation of an index? A. Option A B. Option B C. Option C D. Option D 14. 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 15. 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) 16. 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. 17. Which permission does a user need in order to run a stored procedure? A. EXECUTE B. ALLOW C. CALL D. RUN 18. 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 19. 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. 20. You have two tables. Each table has three rows. You create a SQL query that uses a cross join. The query does not include a WHERE clause. How many rows will be included in the Cartesian product of those A. 0 B. 3 C. 6 D. 9 21. 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 22. Which constraint ensures a unique value in the ID column for each customer? A. DISTINCT B. FOREIGN KEY C. SEQUENTIAL D. PRIMARY KEY 23. The component that holds information for a single entry in a table is called a: A. Data type B. Row C. Column D. View 24. You execute the following query: 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 25. Which command should you use to add a column to an existing table? A. MODIFY B. ALTER C. UPDATE D. INSERT E. CHANGE 26. 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 27. 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 28. 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. 29. 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 30. 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 31. 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 32. You execute the following statement: This statement is an example of a/an: A. Subquery B. Union C. Outer join D. Cartesian product 33. 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 34. You have a table named Employee that includes the following columns: EmployeeID - EmployeeName -  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 35. 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 36. Which command should you use to remove a table from a database? A. REMOVE TABLE B. UPDATE TABLE C. DROP TABLE D. DELETE TABLE 37. 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 38. 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) 39. 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 40. Data in a database is stored in: A. Tables B. Queries C. Data types D. Stored procedures 41. 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. 42. 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 43. 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 44. 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 45. Denormalization is performed in order to: A. Reduce redundancy. B. Eliminate repeating groups. C. Create smaller tables. D. Improve query performance. 46. 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 47. 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 48. Which two keys establish a relationship between two tables? (Choose two.) A. candidate  B. foreign C. superkey D. local E. primary 49. 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. 50. Which keyword must be included in a create view statement? A. WHERE B. ORDER BY C. UPDATE D. SELECT 51. You have a table named Customer. You need to add a new column named District. Which statement should you use? A. Option A B. Option B C. Option C D. Option D 52. 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 53. 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 54. 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. 55. On which database structure does an update statement operate? A. Table B. User C. Trigger D. Role 56. You need to list the name and price of each product, sorted by price from highest to lowest. Which statement should you use? A. Option A B. Option B C. Option C D. Option D 57. 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 58. Which statement deletes the rows where the employee's phone number is not entered? A. Option A B. Option B C. Option C D. Option D 59. You need to insert two new products into the Product table. The first product is named Book and has an ID of 125. The second product is named Movie and has an ID of 126. Which statement should you use? A. Option A B. Option B C. Option C D. Option D 60. 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 61. Which two elements are required to define a column? (Choose two.) A. A name B. A key C. An index D. A data type 62. 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 63. 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 64. 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. 65. 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 66. 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. 67. What are three valid data manipulation language (DML) commands? (Choose three.) A. INSERT B. COMMIT C. DELETE D. OUTPUT E. UPDATE 68. 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. 69. 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 70. Which statement should you use to remove a foreign key? A. ALTER TABLE B. DELETE TABLE C. DELETE FOREIGN KEY D. ALTER FOREIGN KEY 71. First normal form requires that a database excludes: A. Foreign keys B. Composite keys C. Duplicate rows D. Repeating groups 72. 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. 73. 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' 74. 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 75. On which database structure does an insert statement operate? A. Role B. Trigger C. User D. Stored procedure E. Table 76. 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 77. You have the following table definition: CREATE TABLE Product - (ProductID INTEGER, Name VARCHAR(20)) You need to insert a new product. The product's name is Plate and the product's ID is 12345.  Which statement should you use? \ A. Option A B. Option B C. Option C D. Option D 78. Instructions: For each of the following statements, select Yes if the statement is true. Otherwise, select No. Each correct selection is worth one point. Hot Area: 79. The following graphic shows the components of a SQL Server application. You access the SQL Server application through Internet Explorer.  Instructions: Use the drop-down menus to select the answer choice that completes each statement. Each correct selection is worth one point. Hot Area: 80. You have the database table named Cars as defined below: You have the following Structured Query Language (SQL) statement: How many rows are returned by the SQL statement? A. 4 B. 5 C. 6 D. 7 81. You have a database table named SongInformation as defined below: You need to create a Structured Query Language (SQL) query to retrieve only the names of songs that sold more than 1000 compact discs (CDs). Which query should you use? A. Option A B. Option B C. Option C D. Option D 82. You accept an IT internship at a local charity. The charity asks you to keep a record of its volunteers by using a database table named Volunteer. The table has the following columns and rows: When volunteer information changes, you must update the table. You need to change Tia's name to Kimberly. Which statement should you choose? A. Option A B. Option B C. Option C D. Option D 83. This question requires that you evaluate the underlined text to determine if it is correct. Use the FROM keyword in a SELECT statement to return rows that meet a specific condition.  Instructions: Review the underlined text. If it makes the statement correct, select "No change is needed." If the statement is incorrect, select the answer choice that makes the statement correct A. No change is needed B. ORDER BY C. UNION D. WHERE 84. 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 85. This question requires that you evaluate the underlined text to determine if it is correct. The CREATE TABLE command removes one or more table definitions and all data, indexes, triggers, constraints, and permission specifications for those tables. Instructions: Review the underlined text. If it makes the statement correct, select "No change is needed." If the statement is incorrect, select the answer choice that makes the statement correct A. No change is needed B. DROP TABLE C. TRUNCATE TABLE D. ALTER TABLE 86. 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 87. The following illustration shows the structure of a clustered index in a single partition: Instructions: Use the drop-down menus to select the answer choice that completes each statement Each correct selection is worth one point. 88. You have the following table: Instructions: Use the drop-down menus to select the answer choice that completes each statement. Each correct selection is worth one point. Hot Area:  a. 89. 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 90. This question requires that you evaluate the underlined text to determine if it is correct. A view can be used to ensure referential integrity. Instructions: Review the underlined text. If it makes the statement correct, select "No change is needed." If the statement is incorrect, select the answer choice that makes the statement correct A. No change is needed. B. Save an extra copy of data stored in a separate table. C. Limit access to specific rows or columns of data in a table. D. Save historical data before deleting it from the base table. 91. You need to set up a database to provide a view of North American mammals. Match the Structured Query Language (SQL) statements to the SQL query. Instructions: To answer, drag the appropriate SQL statement from the column on the left to its place in the SQL query on the right Each SQL statement may be used once, more than once, or not at all. Each correct match is worth one point. Select and Place: a. 92. You have a database table that stores information about school attendance. You have a data set that is defined as follows: You have the following requirements: ✑ StudentName must consist of a string of characters. ✑ GradeLevel must be only a whole number. DaysAbsent can have one number after the decimal. Match the data types to the column names. Instructions: To answer, drag the appropriate data type from the column on the left to its column name on the right Each data type may be used once, more than once, or not at all. Each correct match is worth one point. Select and Place: a. 93. Instructions: For each of the following statements, select Yes if the statement is true. Otherwise, select No. Each correct selection is worth one point.  Hot Area: a. 94. This question requires that you evaluate the underlined text to determine if it is correct. In a database table, each column represents a unique record. Instructions: Review the underlined text. If it makes the statement correct, select "No change is needed." If the statement is incorrect, select the answer choice that makes the statement correct A. No change is needed B. Table C. Index D. Row 95. This question requires that you evaluate the underlined text to determine if it is correct. You have two tables. Each table has three rows. You create a SQL query that uses a cross join. The query does not include a WHERE clause. Nine rows will be included in the Cartesian product of these two tables. Instructions: Review the underlined text. If it makes the statement correct, select "No change is needed." If the statement is incorrect, select the answer choice that makes the statement corrector. A. No change is needed B. Zero C. Three D. Six 96. 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 97. Your class project requires that you help a charity set up a website. The website collects information about volunteers. Each volunteer record must be uniquely identified. The following information is stored for each volunteer: ✑ Given name ✑ Surname ✑ Date of birth ✑ Phone number ✑ Photo You need to create a table to meet the requirements. Match the Structured Query Language SQL statements to the SQL query. Instructions: To answer, drag the appropriate SQL statement from the column on the left to its place in the SQL query on the right. Each SQL statement may be used once, more than once, or not at all. Each correct match is worth one point. Select and Place: a. 98. This question requires that you evaluate the underlined text to determine if it is correct. Use the ALTER statement to add a new table in a database. Instructions: Review the underlined text. If it makes the statement correct, select "No change is needed." If the statement is incorrect, select the answer choice that makes the statement correct. A. No change is needed B. UPDATE C. INSERT D. CREATE 99. 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 100. You have two database tables as defined below. The first table contains information about students. The second table contains information about courses. The two tables are related by CourseID. Match the CourseName to the StudentName. Instructions: To answer, drag the appropriate CourseName from the column on the left to its StudentName on the right. Each CourseName may be used once, more than once, or not at all. Each correct match is worth one point. Select and Place: a. 101. This question requires that you evaluate the underlined text to determine if it is correct. Use indexing to create, remove, or change database objects. Instructions: Review the underlined text. If it makes the statement correct, select "No change is needed." If the statement is incorrect, select the answer choice that makes the statement correct. A. No change is needed. B. Data manipulation language (DML) statements C. Data definition language (DDL) statements D. A unique constraint 102. You need to add rows to a database table. Which Structured Query Language (SQL) keyword should you use? A. JOIN B. INSERT C. CREATE D. UPDATE 103. You need to retrieve data from two related database tables based on a column that exists in both tables. Which command should you use in a query? A. INTERSECT B. UNION C. JOIN D. TRUNCATE 104. You have a database table that contains the following columns: You must insert the following record into the table: Which two Structured Query Language (SQL) statements can you use? (Choose two.) A. Option A B. Option B C. Option C D. Option D 105. Which keyword combines the results of two queries and returns only rows that appear in both result sets? A. No change is needed B. INTERSECT C. ALL D. UNION 106. You work at a restaurant and they ask you to help them with a data issue. They provide you with the following recipe data:  You need to normalize the data to third normal form. How many tables should you create? A. 1 B. 2 C. 3 D. 4 107. You accept an IT internship at a local charity. The charity has two tables in their data model named Chapter and Language, as defined below: You create a third table named ChapterLanguage to relate the Chapter table and the Language table. You need to select columns from the Chapter and Language tables to create a composite primary key for the ChapterLanguage table. Which two columns should you select? (Choose two.) A. ChapterId B. LanguageId C. Country D. Region E. City F. LanguageName 108. Instructions: For each of the following statements, select Yes if the statement is true. Otherwise, select No. Each correct selection is worth one point.  Hot Area: 109. This question requires that you evaluate the underlined text to determine if it is correct. You combine data from three tables into one table. The new table includes redundancy to optimize read performance. The data in the new table has been denormalized. Instructions: Review the underlined text. If it makes the statement correct, select "No change is needed." If the statement is incorrect, select the answer choice that makes the statement correct. A. No change is needed B. Normalized C. Truncated D. Indexed 110. You need to delete a database table. Which data definition language (DDL) keyword should you use? A. ALTER B. DELETE C. DROP D. TRUNCATE 111. This question requires that you evaluate the underlined text to determine if it is correct. Create a query that returns a set of table data by using the UPDATE statement. Instructions: Review the underlined text. If it makes the statement correct, select "No change is needed." If the statement is incorrect, select the answer choice that makes the statement correct. A. No change is needed  B. INSERT C. REPLACE D. SELECT 112. Your class project requires that you help a charity to create a website that registers volunteers. The website must store the following data about the volunteers: ✑ Given name ✑ Surname ✑ Telephone number ✑ Email address You need to recommend a correct way to store the data. What do you recommend? A. Create a table that contains columns that are named given name, surname, phone number, and email. B. Create a table that contains rows that are named given name, surname, phone number, and email. C. Create a view that contains columns that are named given name surname, phone number, and email. D. Create a view that contains rows that are named given name surname, phone number, and email 113. This question requires that you evaluate the underlined text to determine if it is correct. Truncate is a database term used to describe the process of applying a backup to a damaged or corrupt database. Instructions: Review the underlined text. If it makes the statement correct, select "No change is needed." If the statement is incorrect, select the answer choice that makes the statement corrector. A. Change is needed B. Commit C. Attach D. Restore 114. This question requires that you evaluate the underlined text to determine if it is correct. Use the ALLOW SELECT command to give a user permission to read the data in a table. Instructions: Review the underlined text. If it makes the statement correct, select "No change is needed." If the statement is incorrect, select the answer choice that makes the statement correct A. No change is needed B. GRANT SELECT C. LET HEAD D. PERMIT READ 115. You have a user that has server roles as defined below: Instructions: Use the drop-down menus to select the answer choice that completes each statement. Each correct selection is worth one point. Hot Area: 116. The following illustration shows backup strategies that use the simple recovery model and the full recovery model: Instructions: Use the drop-down menus to select the answer choice that completes each statement. Each correct selection is worth one point.  Hot Area: 117. Instructions: For each of the following statements, select Yes if the statement is true. Otherwise, select No. Each correct selection is worth one point. Hot Area: 118. This question requires that you evaluate the underlined text to determine if it is correct. Views are database objects that contain all of the data in a database.  Instructions: Review the underlined text. If it makes the statement correct, select "No change is needed." If the statement is incorrect, select the answer choice that makes the statement correct A. No change is needed B. Queries C. Stored procedures D. Tables 119. You have two tables named Cars and Color as defined below. The two tables are related by ColorId. You run the following SQL statement: select: How many rows does the SQL statement return? A. 0 B. 2 C. 3 D. 6 120. You accept an IT internship at a local charity. The charity asks you to keep a record of its volunteers by using a database table named Volunteer. When volunteers ask to be removed from mailing lists, the table must be updated. You need to use a transaction to ensure that the database has data integrity and referential integrity. Which statement should you use? A. Option A B. Option B C. Option C D. Option D 121. Which statement removes all rows from a table without logging the individual row deletions? A. No change is needed  B. DROP TABLE C. TRUNCATE TABLE D. CREATE TABLE 122. You work at a coffee shop. They ask you to set up a website that stores charges on purchases. You need to recommend a data type in a database table to run financial functions against the charged amounts. Which data type should you recommend? A. decimal B. Bit C. Varchar D. Binary 123. This question requires that you evaluate the underlined text to determine if it is correct. A row holds information for a single record in a table. Instructions: Review the underlined text. If it makes the statement correct, select "No change is needed." If the statement is incorrect, select the answer choice that makes the statement correct A. No change is needed B. Column C. Data type D. View 124. Instructions: For each of the following statements, select Yes if the statement is true. Otherwise, select No. Each correct selection is worth one point. Hot Area: 125. This question requires that you evaluate the underlined text to determine if it is correct. Ports 20 and 21 are the default ports to secure a SQL Server. Instructions: Review the underlined text. If it makes the statement correct, select "No change is needed." If the statement is incorrect, select the answer choice that makes the statement correct. A. No change is needed B. 1433 and 1434 C. 411 and 412 D. 67 and 68 126. You ate creating a database object named Student to store the following data: Which syntax should you use to create the object? A. Option A B. Option B C. Option C D. Option D 127. You develop a database to store data about textbooks. The data must be stored to process at a later time. Which database object should you use to store the data? A. View B. Table C. Function D. Stored procedure 128. This question requires that you evaluate the underlined text to determine if it is correct. First normal form requires that a database excludes repeating groups. Instructions: Review the underlined text. If it makes the statement correct, select "No change is needed." If the statement is incorrect, select the answer choice that makes the statement correct. A. No change is needed. B. Composite keys C. Duplicate rows D. Foreign keys 129. You have two database tables as defined below. The StateID column is unique in the State table. The AddressID column is unique in the Address table. The two tables are related by the StateID column. Instructions: Use the drop-down menus to select the answer choice that completes each statement. Each correct selection is worth one point. Hot Area: 130. You have the following SQL query SELECT * FROM dbo.ProAthlete WHERE Salary > 500000 The query takes too much time to return data. You need to improve the performance of the query. Which item should you add to the Salary column? A. Non-null constraint B. Default constraint C. Index D. Foreign key 131. You work for a small auto trading company. You need to remove a car from the company database. Information about the car is stored in the following tables. All of the car's parts are custom made, so they each have a row in every table. Cascading deletes  are not enabled but referential integrity is. From which table must you first make your deletion? A. Make B. Model C. Engine D. ModelEngine 132. This question requires that you evaluate the underlined text to determine if it is correct. You have a table that contains information about all students in your school. You use the INSERT SQL keyword to change a student's given name in the table. Instructions: Review the underlined text. If it makes the statement correct, select `No change is needed.` If the statement is incorrect, select the answer choice that makes the statement correct. A. CHANGE B. UPDATE C. No change needed D. SELECT 133. You create the following table, which lists how many books you have on loan to your friends. Harry in San Francisco returns your books. Which statement will update your table correctly? A. UPDATE LoanedBooks SET Books = 0 WHERE (Name = 'Harry' AND City = 'San Francisco') B. UPDATE LoanedBooks SET Books = 0 WHERE (Name = 'Harry' OR City = 'San Francisco') C. UPDATE LoanedBooks SET Books = 0 WHERE (Name in 'Harry', 'San Francisco') D. INSERT INTO LoanedBooks SET Books = 0 WHERE ID = 134. You are developing a SQL query. Which two SQL constructs represent data manipulation language (DML) statements? Choose two. A. SELECT EmployeeName FROM Employee WHERE EmployeeName = 'Jack Smith'; B. INSERT INTO Employee VALUES ('Jack Smith') C. ALTER TABLE Employee ADD EmployeeName Varchar; D. CREATE ROW IN Employee WHERE EmployeeName = 'Jack Smith'; 135. You need to create a view to filter rows of data from an underling table. Which type of clause must be included in the CREATE VIEW statement? A. CONSTRAINT B. WHERE C. JOIN D. FILTER 136. This question requires that you evaluate the underlined text to determine if it is correct. A key defines the amount of storage space that is allocated to a value in a column.  Instructions: Review the underlined text. If it makes the statement correct, select "No change is needed." If the statement is incorrect, select the answer choice that makes the statement correct A. No change is needed B. format C. data type D. validator 137. This question requires that you evaluate the underlined text to determine if it is correct. A relational database management system employs the concept of an attribute to ensure that data entered into a field in a column is valid. Instructions: Review the underlined text. If it makes the statement correct, select "No change is needed." If the statement is incorrect, select the answer choice that makes the statement correct. A. No change is needed B. a primary key C. a constraint D. an index 138. You are structuring a table in a relational database. Instructions: For each of the following statement, select Yes if the statement is true. Otherwise, select No. Each correct selection is worth one point. Hot Area: 139. You have a database that contains 10 terabytes of data. You need to back up the database every two hours. Which type of backup should you use?  A. archive B. incremental C. partial D. full 140. This question requires that you evaluate the underlined text to determine if it is correct. The UNION keyword combines the results of two queries and returns only rows that appear in both result sets. Instructions: Review the underlined text. If it makes the statement correct, select "No change is needed." If the statement is incorrect, select the answer choice that makes the statement correct A. No change is needed B. INTERSECT C. ALL D. JOIN 141. Mark works as a Database Administrator for ExamTopics Inc. The company has a SQL Server database. Management instructs him to ensure that no inconsistent data is entered in the database. Which of the following will help him to accomplish the task? A. Referential integrity B. Abstraction C. Encryption D. Authentication E. Native auditing 142. Which of the following statements about rebuilding an index is true? A. The NOLOGGING and ONLINE keywords can never be used together. B. The NOLOGGING and ONLINE keywords are always used together. C. Without the ONLINE keyword the index is locked for any DML operation. D. Without the ONLINE keyword the index is locked for the SELECT operation. 143. You work as a database developer for www.company.com.com. The company has a database named www.company.com that is hosted on a SQL Server 2008 server. The database includes a table named Employees, which contains the details of the employees. You want to ensure that anytime any data in the Employees table is modified with an UPDATE statement, they are recorded in a table named EmployeeChanges. The EmployeeChanges table includes columns to record what the change was, who made the change, and when they made the change. What should you create to meet this need? A. A DDL trigger that queries the updated table B. A DML trigger that queries the inserted table C. A DDL trigger that queries the inserted table D. A DML trigger that queries the updated table 144. You want to recover one or more tablespaces to a point in time older than the rest of the database. Which of the following are the recovery situations in which tablespace Point-in-Time Recovery (TSPITR) should be used? Each correct answer represents a part of the solution. (Choose all that apply.) A. To recover a tablespace that contains rollback segments B. To recover a table that has become logically corrupted  C. To recover a DML statement that has affected only a subset of the database D. To recover a tablespace on a very large database. 145. Linda works as a Database Designer for Lion Inc. She has been given an assignment to design the database of a publishing company. The database has a table named Author, which has a composite key, AuthorID and TitleID. Royalty and LiteraryAgent are the other attributes. The functional dependencies are such that AuthorID + TitleID-> Royalty (i.e. Royalty is functionally dependent on AuthorID and TitleID) and AuthorID-> LiteraryAgent (i.e. LiteraryAgent is functionally dependent on AuthorID). Which of the following is true about this table? A. It violates 4 NF. B. It violates 2 NF. C. It violates 1 NF. D. There is no violation. 146. Which of the following is a management activity that allows the stages of the database application to be realized as efficiently and effectively as possible? A. Database planning B. Identifying information for objects C. Object identification D. Requirements collection and analysis 147. You work as a Database Administrator for Bell Ceramics Inc. An employee of the company has fired a query, including a DML statement, such as INSERT, against a table named Sales. You notice that the DML statement has not executed. What will you do to resolve the issue? Each correct answer represents a complete solution. (Choose two.) A. Clean up all the uncommitted transactions on the Sales table. B. Add more space to the tablespace and increase the users quota on the tablespace. C. Provide redundant network paths from the client computer to the server along with additional listener connections on the Oracle server and redundant network cards on the Oracle server. D. Provide an appropriate privilege or create views on the Sales table, and grant privileges on the view. 148. Which of the following is the process of extracting trails on a regular basis so that they can be transferred to a designated security system where the database administrators do not have access? A. Native auditing B. Abstraction C. Database security D. Encryption 149. You manage a large database named Sales. The Sales database contains a table named OrderDetails, which is a heavily transacted table with frequent inserts. Indexes in the table often become fragmented due to excessive page splitting. You want to minimize the amount of fragmentation due to page splits. What should you do? A. Update the statistics on the indexes. B. Change the fillfactor for the indexes to 60. C. Change the fillfactor for the indexes to 100 D. Change the fillfactor for the indexes to 0. 150. You are the database administrator of a MySQL server that runs on a Windows server. All clients are local clients. For security, you want to disable connections from the remote clients. Which of the following steps will you take to accomplish the task? A. Start the server with the --disable-networking option. B. Start the server with the --shared-memory option. C. Start the server with the --skip-networking option. D. Start the server with the --secure-auth option. 151. Which of the following statements is true about referential integrity? A. It checks that no component of a primary key can have a null value and no duplicate entries can exist. B. It checks that the value of a primary key must be consistent throughout the life of an instance. C. It distinguishes between null values and zero entries. D. It checks that no record in a child table can exist if its corresponding record is not available in the parent table. 152. Which of the following are DML commands? Each correct answer represents a complete solution. (Choose all that apply.) A. SELECT INTO B. DELETE C. UPDATE D. ALTER E. SELECT F. INSERT 153. Which of the following commands can be used to change the authorization type in DB2 9? Each correct answer represents a complete solution. (Choose all that apply.) A. UPDATE DATABASE CONFIGURATION B. SET AUTHORISATION C. RESET DATABASE CONFIGURATION D. UPDATE DBM CFG 154. Smith works as a Database Administrator for ExamTopics Inc. The management instructs him to plan a database where identifying the key objects or entities for database management is important. Which of the following database planning steps will help him to accomplish the task? A. Object identification B. Gathering information C. Identifying the relationships between objects D. Object modeling 155. You are a database developer for a database named Customers hosted on a SQL Server 2008 server. Recently, several customers were deleted from the Customers database. To ensure this is not repeated in future, you have decided to create a DML trigger to prevent it. What code will create the trigger to meet your goals? Each correct answer represents a complete solution. (Choose all that apply.) A. CREATE TRIGGER trgDeleteCustomer ON dbo.Customers BEFORE DELETE AS RAISERROR('Customers cannot be deleted. An error has been logged', 16, 10) WITH LOG ROLLBACK TRANSACTION  B. CREATE TRIGGER trgDeleteCustomer ON dbo.Customers AFTER DELETE AS RAISERROR('Customers cannot be deleted. An error has been logged', 16, 10) WITH LOG ROLLBACK TRANSACTION C. CREATE TRIGGER trgDeleteCustomer ON dbo.Customers AFTER DELETE AS IF(SELECT COUNT(*) FROM DELETED) > 1 BEGIN RAISERROR('Customers cannot be deleted. An error has been logged', 16, 10) WITH LOG ROLLBACK TRANSACTION END D. CREATE TRIGGER trgDeleteCustomer ON dbo.Customers AFTER DELETE AS IF (SELECT COUNT(*) FROM DELETED) > 0 BEGIN RAISERROR('Customers cannot be deleted. An error has been logged', 16, 10) WITH LOG ROLLBACK TRANSACTION END 156. JDBC API 2.0 defines five levels of transaction isolation for database concurrency control. Which of the following is the lowest level of transaction isolation? A. TRANSACTION_SERIALIZABLE B. TRANSACTION_NONE C. TRANSACTION_READ_COMMITTED D. TRANSACTION_READ_UNCOMMITTED E. TRANSACTION_REPEATABLE_READ 157. Which of the following provides reliable units of work that allow correct recovery from failures and keeps a database consistent even in cases of system failure? A. Database security B. Concurrency control C. Two-phase commit D. Database transaction 158. John works as a Database Administrator for Bluewell Inc. The company has a SQL Server database. A table in the database has a candidate key and an attribute that is not a constituent of the candidate key. The non-key attribute depends upon the whole of the candidate key rather than just a part of it. Which of the following normal forms is represented in the scenario? A. 4 NF B. 2 NF C. 1 NF D. 3 NF 159. You work as a Database Designer for DataOneWorld Inc. The company has a SQL Server database. You are assigned the task of creating a data model of an enterprise based on a specific data model. The model to be created should be independent of a particular DBMS. Which of the following database designs will help you accomplish the task? A. Logical database design B. Conceptual database design C. Physical database design D. Application design 160. Which of the following steps in database planning helps to determine the requirements of the database through interviewing? A. Gathering information B. Identifying the relationship between objects C. Identifying the objects D. Identifying the type of information for each object E. Modeling the object 161. Consider the case of a fruit juice company. The company manufactures fruit juices and supplies them to wholesalers. The Database Designer creates a table named Production. The code is given below: Which of the above-mentioned lines has an error? A. Line 1 B. Line 2 C. Line 3 D. Line 4 162. John works as a Database Administrator for DataOneWorld Inc. The company has a SQL Server database. John wants to insert records in a table where the database is structured in a fixed format. Which of the following data models will he use to accomplish the task? A. Object relational data model B. Entity-Relationship Model C. Network data model D. Relational model 163. You manage a database named Customers, which includes a table named Orders. The Orders table is frequently queried, but only orders with a sales total of more than $1000.00 are required in the query. You want to create an index to speed up these types of queries at the same time, ensuring the index is as small as possible. What type of index should you use? A. Non-clustered B. Filtered C. Clustered D. XML 164. Which of the following database terms is described in the statement below? It prevents the current database operation from reading or writing a data item while the data item is being accessed by another operation. A. Lock B. Encryption C. Constraint D. Deadlock 165. Your Company is designing and developing a number of databases for a stock exchange. One of the databases will contain highly sensitive data for which high level of security will be required. Although high processing speed is one of the prime requirements of the customer, for this database, security of the data will take priority over the processing speed. It needs to be ensured that even if unauthorized access to the database is obtained, the rogue user is unable to read the data. Which of the following protection techniques will you suggest for this database? A. Authentication B. Encryption C. Native auditing  D. Integrity controls 166. Which of the following are the main approaches in the database design? Each correct answer represents a complete solution. (Choose three.) A. Top-down approach B. Bottom-up approach C. Middle approach D. Inside-out approach 167. You are a Database Administrator of MySQL database. Few days back, your server crashed. Now, it is up and online. You need to check the integrity of various tables used in the database. You need to issue the CHECK TABLE command. For which of the following tables will this command work? Each correct answer represents a complete solution. (Choose two.) A. FEDERATED B. MyISAM C. MERGE D. InnoDB 168. Which of the following can be used to populate a table? Each correct answer represents a complete solution. (Choose all that apply.) A. Data Pump B. SQL*Loader C. INSERT statement D. MERGE statement 169. Which of the following statements about external tables is true? A. They can have constraints or triggers. B. They cannot be written to with DML commands. C. They can have indexes. D. They cannot be used in joins, views, and subqueries. 170. Which of the following values cannot be stored in a character column defined as the primary key of a table? A. null B. '0' C. 'null' D. " 171. Mark works as a Database Administrator for DataOneWorld Inc. Management instructs him to provide an additional security layer to the database to prevent unauthorized viewing of data with the help of an algorithm called cipher. Which of the following will help him to accomplish the task? A. Native auditing B. Authentication C. Encryption D. Abstraction 172. Which of the following is a design of the user interface and the application programs that use and process the database? A. Application Design  B. Object Modeling C. Database Design D. Database Planning 173. Which of the following is used to give a default value to a column if the value for that column is unknown? A. Concurrency B. DEFAULT constraint C. Nested transaction D. Transaction 174. Suzanne works as a Database Designer for DataOneWorld Inc. She is assigned the task of decomposing the components of a database, but she has to ensure that all the components are decomposed to only some extent. Which of the following approaches will she use to accomplish the task? A. Mixed approach B. Inside-out approach C. Top-down approach D. Bottom-up approach 175. Consider the case of a distance education university. The university has many regional centers across the country. Each regional center has a head known as regional manager. Each regional center has allotted more than one study center in its region. Each regional center has a region code, which is unique and specifies a region. Each study center also has a study center code, which is also unique. What is the relationship between regional center and study center? A. One-to-one B. One-to-many C. There is no relationship. D. Many-to-many 176. You work as the Database Administrator for www.company.com. All servers on the www.company.com network run Windows Server 2003 and Windows Server 2000, and all client computers run Windows XP professional and Windows Vista. The www.company.com network area consists of a single Active Directory domain named www.company.com. The www.company.com network contains a Microsoft SQL Server 2005 database server named COMPANY-DB111, which you administer at a regular interval of time. COMPANY-DB111 contains and hosts three databases that support different types of business requirements. The network uses the DBA database that supports internal operations including maintaining data, accounting, and mailing. The network's regular users make use of stored procedures for the data values that need insertion into the tables. The stored procedures used in the network of the company are designed to access SQL variant type parameters and then use the values to build and execute ad hoc query statements that are the part of the SQL statements. During a routine network check, you discover that there are several odd occurrences in the database. The odd occurrences include data deleted from tables and other unauthorized activity. You suspect a user is executing the unauthorized statements through the stored procedures. You are required to stop the unauthorized changes while having least impact on the stored procedures that are in use. Your explanation must use the least administrative effort. What should you do? A. The input parameters should be parsed to watch for and block any input including single quotes.  B. The stored procedures used for the table should be replaced with ad hoc queries. C. The stored procedure should be customized to use type-specific and length-restricted parameters. D. All data handling activity on the table should be audited and recorded. 177. You work as a Database Administrator for DataOneWorld Inc. Management instructs you to remove an object from the relational database management system. Which of the following statements will you use to accomplish the task? A. DROP B. SELECT C. CREATE D. ALTER 178. Which of the following enables a user to use generic methods to access a database and to focus on the coding rather than the syntax for a specific database API? A. Abstraction B. Database security C. Native auditing D. Authentication 179. You work as a Database Administrator for InfoTech Inc. The company has a database and they want you to create a design for a database that will support the enterprise's operations and objectives. Which of the following will you use to accomplish the task? A. Application design B. Database design C. Database planning D. Requirements collection and analysis 180. Which of the following scripts is used to convert Unix-type zone files into SQL statements, and loads the time zone tables in a mysql database? A. mysql_tzinfo_to_sql B. mysql_time_to_sql C. mysql_timezone_to_sql D. mysql_tz_to_sql 181. You work as a Database Designer for Tritech Inc. You are designing a table named Info. The table contains the following attributes: Login and Password - Name - ContactNumber - Address - Each new employee joining the company is assigned an E-mail Login and Password. This E-mail Login and Password is used by each employee to access his account on the company's Web site. The value of both the attributes should be unique and cannot be NULL. You design an artificial key EmpID in the Info table to serve as a primary key. Which of the following factors lead you to define an artificial key for use as a primary key in the Info  table? Each correct answer represents a part of the solution. (Choose all that apply.) A. The value of the primary key cannot be NULL. B. The value of the primary key must be persistent. C. The natural key contains highly sensitive data D. The value of the primary key must be unique. 182. In addition to taking backups of your databases, you should also backup up files stated below. Which of these files store updates that have been made after the backup was made? A. Binary log files B. My.ini C. My.cnf D. Master.info 183. Which of the following is a building working model of a database system? A. Prototyping B. Bottom-up approach C. Conceptual database design D. Top-down approach 184. You are the database administrator for a MySQL database server. The network contains new and old (pre-4.1) clients. You configure another database server on the network. You allow the network clients to connect to the new server. Some users complain that when they try to connect to the server, they receive the following error: ERROR 1251: The client does not support authentication protocol requested by the server; consider upgrading MySQL client You do not want to upgrade any client or server right now, and want to enable all clients to connect to the new server successfully. Which of the following steps will you take to resolve the issue? A. Run the server with the --secure-auth option. B. Run the server with the --old-password option. C. Run the server with the --allow-old option. D. Run the server with the --enable-old option. 185. Which of the following steps in database planning is used to record an object so that it is represented visually in the system? A. Identifying the relationships between objects B. Gathering information C. Object identification D. Object modeling 186. Remo works as a Database Designer for Tech Inc. He wants to create a table named Product. He issues the following query to create the Product table: CREATE Product ( ProductID Char (10) NOT NULL, OrderID Char (10) NULL - ProductName Varchar NOT NULL, Primary key (OrderID, ProductID)) What are the errors in the above query? Each correct answer represents a complete solution. (Choose two.)  A. An attribute declared as a primary key cannot contain NULL values. B. Each attribute should be defined as a primary key separately. C. A table cannot have two primary keys. D. ProductName is declared as Varchar without specifying the width of the column. 187. What is the relationship between the foreign key and primary key? Each correct answer represents a complete solution. (Choose all that apply.) A. A foreign key constraint works in conjunction with a primary key constraint to enforce referential integrity among related entities. B. A foreign key ties attribute(s) of an entity to the primary key of another entity, for the purpose of creating a dependency. C. There is no relationship between a primary key and a foreign key. D. A foreign key and a primary key create a link between two entities. 188. Which of the following DML SQL statements support usage of correlated sub-queries? Each correct answer represents a complete solution. (Choose all that apply.) A. INSERT B. UPDATE C. DELETE D. SELECT 189. Which of the following are the types of prototyping strategies? Each correct answer represents a complete solution. (Choose two.) A. Requirements prototyping B. Physical prototyping C. Revolutionary prototyping D. Evolutionary prototyping 190. Which of the following commands cannot be rolled back? Each correct answer represents a complete solution. (Choose two.) A. DELETE B. TRUNCATE C. UPDATE D. COMMIT 191. Mark works as a Database Designer for Reon Inc. He is assigned the task to create a database for the company. He issues the following query to create the database: CREATE DATABASE '24342' What will be the output of the query? A. A database will be created. B. An error will be generated stating that a database name must begin with an alphabet and all the alphabets should be in uppercase. C. An error will be generated stating that a database name must begin with an alphabet. D. An error will be generated stating that a database name should be a combination of numerals and alphabets. 192. What is the ideal time to back up dynamic log files?  A. When the server is running. B. You can back up dynamic log files any time. C. When the server is stopped. D. When the server is shut down. 193. You work as a ExamTopics MySQL Database Administrator. Your server crashed and it was recovered and made online within half an hour. What will you do to ensure that the table structure and contents are in a proper state? A. Perform a table optimization operation. B. Perform a table repair operation. C. Perform a table analysis operation. D. Perform a table check operation. 194. James works as an Administrator for Softech Inc. The company has a SQL Server 2005 computer named SQL1. The company has six departments; Accounting, Sales, Marketing, Manufacturing, Production, and Research. The user accounts of each department are located in a domain group named after the department. James wants to create a separate database on SQL1 for each department which will store the department related information. Users in each department must be able to read and update data in the database belonging to their respective departments. No user must be able to access the database belonging to another department. James wants to configure access to the database to meet these requirements. What should James do to accomplish the task? A. James should create a Windows Authentication login for each domain group and configure the logins as database users for the appropriate database. Add each database user to the db_ddladmin database role. B. James should create a Windows Authentication login for each domain group and configure the logins as database users for the appropriate database. Add each database user to the db_datareader and db_datawriter database roles. C. James should create a Windows Authentication login for each domain user and configure the logins as database users for the appropriate database. Add each database user to the db_ddladmin database role. D. James should create a Windows Authentication login for each domain user and configure the logins as database users for the appropriate database. Add each database user to the db_datareader and db_datawriter database roles. 195. You are the database administrator for a MySQL database. The database server is installed on a Unix system. The time zone files for the system are located at /usr/share/zoneinfo. You need to ensure that the system and MySQL time zones are based on the same information. Which of the following statements will you use to accomplish the task? A. shell> /ust/share/zoneinfo mysql_tzinfo_to_sql | mysql -u root mysql B. shell> /ust/share/zoneinfo mysql_tz_to_sql | mysql -u root mysal C. shell> mysql_tz_to_sql /ust/share/zoneinfo | mysql -u root mysql D. shell> mysql_tzinfo_to_sql /ust/share/zoneinfo | mysql -u root mysql 196. Which of the following is the process of creating a design for the database that will support the enterprise's operations and objectives for the required database system? A. Application design B. Database design C. Database planning D. Identifying relationships between objects 197. You manage a database named Exams accessed via a website. The website is regularly accessed by users in different countries. You are redesigning a table named History that records when users have purchased and taken online exams. You need one of the columns in the History table to record what time of day that a user has taken an exam. Additionally, you need this column to have time zone awareness. What data type should you use? A. datetimeoffset B. datetime C. Use two different columns with one column of datetime, and the second column indicating the UTC offset. D. datetime2 198. Fill in the blank with the appropriate word. The _______________ model for database management is a database model based on first-order predicate logic. A. relational B. traditional C. truncate 199. You work as a Database Administrator for Dowtech Inc. A table named StudentInformation exists in a database. The table has columns named student_names, student_marks, and student_courses. Some students who have opted for various courses have got different marks in them. This implies that the table has two many-to-many relationships. You have to ensure that these two relationships are not represented in a single table and that the student_name column exists in both tables. Which of the following normal forms will you use to represent these relationships in the tables? A. 4 NF B. 3 NF C. 2 NF D. 1 NF 200. Which of the following are the steps of database planning life cycle? Each correct answer represents a part of the solution. (Choose three.) A. Application Design B. Data conversion and loading C. Conceptual database design D. Operational Maintenance 201. Which of the following are the characteristics of application design? Each correct answer represents a complete solution. (Choose three.) A. Output of the transaction B. Shortlisting of two or three products C. Data can be used from the transaction D. Expected rate of usage 202. John works as a database designer in ABC Tech. He wants to produce a description of a database implemented on secondary storage media. Which of the following designs will he implement to accomplish the task? A. Logical database design B. Conceptual database design C. Database design  D. Physical database design 203. You have developed a stored procedure named usp_GetEmp that accepts an employee number as a parameter and retrieves the details about the employee from the CurrentEmp table of a database named Employees. You have tested it, and it works exactly as you expected. Later, another employee tries to use the stored procedure and receives the following error: The SELECT permission was denied on the object 'CurrentEmp', database 'Employees.schema 'dbo' What should you do to resolve the problem? A. Modify usp_GetEmp to include the With Execute As Owner clause B. Modify usp_GetEmp to include the With Execute As Caller clause C. Grant the employee the SELECT permission on the CurrentEmp table D. Grant the employee the SELECT permission on the Employees database. 204. Peter works as a Database Administrator for DataOneWorld Inc. The company has a SQL Server database. Peter has to construct an Entity-Relationship schema design for the data he will use in the database. Which of the following database designs will he use to accomplish the task? A. Physical database design B. Conceptual database design C. Logical database design D. Application design 205. You are taking back up of your MySQL database server. You need to take a back up of the file that has been created by the replication slave server and contains information needed for connecting to the master server. Which of the following files will you copy? A. Master.info B. My.ini C. Relay-log.info D. My.cnf 206. Mark works as the Database Administrator for DataWorld Inc. The company has a SQL server 2008 database. Mark wants to protect the database from unintended activities made by authorized individuals or processes such as authenticated misuse, malicious attacks, or inadvertent mistakes. Which of the following should he use to accomplish the task? A. Native auditing B. Abstraction C. Referential integrity D. Database security 207. Which of the following files contain configuration information that must be restored after a MySQL database server has crashed? Each correct answer represents a complete solution. (Choose two.) A. My.ini B. My.cnf C. Master.info D. Binary log files 208. John works as a Database Administrator in www.company.com Inc. The company has a SQL Server database. John wants to create a table named employees in the database. The table will have the id, firstname, lastname, and dateofbirth columns. John has to ensure that the lastname column does not allow null values. Which of the following queries will he use to accomplish the task? A. CREATE TABLE employees (id INTEGER PRIMARY KEY, firstname CHAR(50)NULL, lastname CHAR (75)NOT NULL, dateofbirth DATE NULL); B. CREATE TABLE employees (id INTEGER PRIMARY KEY, firstname CHAR(50)NULL, lastname CHAR (75), dateofbirth NULL); C. CREATE TABLE employees (id INTEGER PRIMARY KEY, firstname CHAR(50)NULL, lastname CHAR (75) NOT NULL); D. CREATE TABLE employees (id INTEGER PRIMARY KEY, firstname CHAR(50)NULL, lastname CHAR (75), dateofbirth DATE NULL); 209. Which of the following statements are true regarding DML? Each correct answer represents a complete solution. (Choose all that apply.) A. It enables users to access/manipulate data in a database. B. It is used to specify additional properties of the data C. It is used to specify a database schema by a set of definitions. D. It can be procedural or declarative. 210. You work as a Database Designer for Realtech Inc. You want to create two tables, Product and Order, such that a row in the Order table does not exist without a corresponding row in the Product table. The structure of the Product and Order tables with their respective columns is given below: Product - ProductID - ProductNumber - Date_of_ manufacturing - SupplierID - ProductPrice - ProductQty - Order - OrderID - OrderDate - OrderQuantity - OrderPrice - Based on this information, while maintaining the integrity of this rule, what will you do to accomplish the task? A. Create the Product table, and then create the Order table. Create a third table that will be used to join the Product and Order tables by using FOREIGN KEY constraints. B. Create the Product table, and then create the Order table that has a primary key referenced by the foreign key in the Product table. C. Create the Product table, and then create the Order table. Create a primary key in both the tables and form a one-to-one relationship.  D. Create the Product table, and then create the Order table that has a foreign key constraint that references the primary key in the Product table. 211. Peter works as a Database Administrator for UnrealTech Inc. Management instructs him to create a schema that is gradually expanded from one level to another through appropriate modifications. Which of the following approaches will he use to accomplish the task? A. Top down approach B. Bottom up approach C. Mixed approach D. Inside-out approach 212. You are a database developer for a database named Sales hosted on a SQL Server 2008 server. The Sales database includes the Exams and ExamQuestions tables. If any questions are modified for an exam, you need to ensure that the ExamModifiedDate in the Exams table is updated with the date and time of the update. The ExamQuestions table includes an INSTEAD OF trigger that records when and who last modified each question. Therefore, you decide to create a trigger. You only want the trigger to fire if a constraint violation has not occurred. Which of the following code will create the trigger to meet your goals? A. CREATE TRIGGER trgExamQuestionsModified ON dbo.ExamQuestions INSTEAD OF UPDATE NOT FOR REPLICATION AS UPDATE Exams SET ExamModifiedDate = GetDate() FROM inserted WHERE inserted.ExamID = Exams.ExamID B. CREATE TRIGGER trgExamQuestionsModified ON dbo.ExamQuestions AFTER UPDATE NOT FOR REPLICATION AS UPDATE Exams SET ExamModifiedDate = GetDate() FROM inserted WHERE inserted.ExamID = Exams.ExamID C. CREATE TRIGGER trgExamQuestionsModified ON dbo.ExamQuestions AFTER UPDATE FOR REPLICATION AS UPDATE Exams SET ExamModifiedDate = GetDate() FROM inserted WHERE inserted.ExamID = Exams.ExamID D. CREATE TRIGGER trgExamQuestionsModified ON dbo.Exams AFTER UPDATE NOT FOR REPLICATION AS UPDATE Exams SET ExamModifiedDate = GetDate() FROM inserted WHERE inserted.ExamID = Exams.ExamID 213. You are the database administrator of a MySQL database server. Your network contains a combination of new and old (pre-4.1) MySQL clients. You are configuring a new server for testing purposes. You want to allow connections only by the clients that have new-format passwords. Which of the following steps will you take to accomplish the task? A. Start the server with the --old-password option. B. Start the server with the --secure-auth option. C. Start the server with the --enable-named-pipe option. D. Start the server with the --shared-memory option. 214. Which of the following commands are used to terminate a transaction? Each correct answer represents a complete solution. (Choose all that apply.) A. TRUNCATE B. DELETE C. ROLLBACK D. COMMIT 215. Which of the following refers to the system, processes, and procedures that protect a database from unintended activities, such as authenticated misuse, malicious attacks, or inadvertent mistakes made by authorized individuals or processes? A. Database security B. Encryption C. Abstraction D. Referential integrity 216. Which of the following is undertaken at any time prior to the logical design, provided that sufficient information is available regarding system requirements? A. Application design B. Prototyping C. DBMS selection D. Database planning 217. Mark works as a Database Administrator for Techworld Inc. He wants to remove the columns from a table that are not dependent on the primary key. Which of the following normal forms will help him accomplish the task? A. 4 NF B. 6 NF C. 5 NF D. 3 NF 218. You are a Database Administrator for MySQL database. Users are complaining of slow query execution in a few tables. You need to ensure that contents of those tables are accessed more efficiently. You are sure that the table structure and its contents have no problems. Which of the following steps will you take to accomplish the task? Each correct answer represents a part of the solution. (Choose two.) A. Perform a table analysis operation. B. Perform a table repair operation. C. Perform a table optimization operation. D. Perform a table check operation. 219. Which of the following are the correct statements of using the Flashback Query feature in Oracle? Each correct answer represents a complete solution. (Choose all that apply.) A. CREATE TABLE AS SELECT AS OF B. SELECT AS OF C. CREATE TABLE AS SELECT D. INSERT INTO (SELECT) 220. Which of the following statements are correct regarding recovery of the InnoDB tables after a server crash? Each correct answer represents a complete solution. (Choose all that apply.) A. They can only be recovered from the dump files. B. They are automatically recovered at the server startup. C. They are recovered only after restarting the server with the innodb_force_recovery option configured. D. The innodb_force_recovery option opens the InnoDB tables in the read-only mode. 221. Adam works as a Database Designer for DataOne Inc. The company has a SQL Server database. Adam has to ensure that either all the databases are  updated or none of them are updated, so that the databases remain synchronized. Which of the following will he use to accomplish the task? A. Native auditing B. Two-phase commit C. Encryption D. Concurrency control 222. Meryl works as a Database Designer for Klone Inc. She wants to create a table named Emp. She issues the following query: CREATE Table Emp (id Int - UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY name Char(10)) What will happen after executing this query? A. The query will fail, as the id and name attributes are created in lowercase. B. The query will execute partially, as it will create the table but will fail to create the attributes. C. The query will fail because the id and name attributes must be separated by a comma. D. The query will create a table named Emp. 223. James works as a Database Administrator for DataOneWorld Inc. The company has a SQL Server database. James has been assigned the task to store information for each object in the database. Which of the following steps will help him accomplish the task? A. Identifying the information for objects B. Object modeling C. Object identification D. Identifying the relationships between objects 224. Which of the following terms is described in statement below? It is a collection of conceptual tools for describing data, data relationship, data semantics, and consistency constraints. A. Data mode B. Generic Data Model C. Relational model D. Entity-Relationship Model 225. You work as an Application Developer for Nero Inc. You want to instruct the DBMS to set the transaction isolation level so that when a user updates a table in the database, other users cannot read the value at the same time. Which of the following methods will you use to define the transaction level? A. setTransactionIsolation() B. isClosed() C. getTransactionIsolation() D. supportsTransactions() 226. A table named Flight contains the following fields: You need to display the flight numbers of all flights that arrive at LaGuardia Airport (LGA) later today. The results should be sorted by the most recent arrival time. Which query should you use? Complete the code by selecting the correct option from each drop-down list. Note: You will receive partial credit for each correct selection. 227. Which SQL Statement is a data manipulation language (DML) statements? 228. A database table named Cars is defined as shown. You have the following SQL statement: How many rows are returned by the SQL statements? 229. A table named PlayerStat contains the following fields: You need to display the total number of points per player on the team whose TeamID is 1. Move the appropriate code segments from the list on the left to the correct locations on the right. You may use each code segments once, more than once, or not at all. Note: You will receive partial credit for each correct selection. 230. For each statement, select True or False. Note: You will receive partial credit for each correct selection. 231. Which query returns a result set of orders placed after January 2023 in all states except California(CA)? 232. A stored procedure contains the following query: SELECT ‘Greetings’ + Prefix + ‘ ‘ + FirstName FROM Person; The stored procedure returns all null values. You verify that there is data in the Person table. What is likely the cause of this problem? 233. The products table contains the following data: Which query will retrieve ItemName and Price when chocolate appears in the ItemDescription column? 234. One difference between a function and a stored procedure is that a function: 235. A table has a clustered index. Complete the statements by selecting the correct option from each drop-down list.| Note: You will receive partial credit for each correct selection.  236. You create the following table, which displays winners of past pet shows. Which column prevents the table from being in third normal form? 237. Which SQL statement will return the country and number of orders in each country where the number of orders is less than 50? orderID represents an individual order, Country represents the country, and Orders represents the number of orders. 238. Which feature does a relational database use to ensure that data entered into a column is valid? You're the database admin for a university that stores student information in a table named students. This table has a foreign key named courseID that links to the primary koy in a table named courses. You need to return the student name and courso nome using both tables where there is a match on courseID. You execute the following query: 239. You need to create a report of data in the students table. The report must meet the following requirements: enrollment_date specifies the enrollment date graduation_date specifies the graduscon date. An academic_status of Graduated indicates that a student has graduated. Which query should you use? 240. Another developer is trying to add a column named Prefix in a table by using the following query: ALTER TABLE Person ADD Prefix varchar(4) NOT NULL; The developer receives an error message after running the query. The developer is unable to remember the exact error message. What is likely the cause of this problem?