MTA 98-364: Database Administration Fundamentals Exam Exercise

Review the underlined text. If it makes the statement correct, select "No change is needed". If the statement is incorrect, select the option that makes the statement correct: <u>ALTER TABLE</u> removes all rows from the table without logging the individual row deletions. A. No change is needed B. DROP TABLE C. TRUNCATE TABLE D. CREATE TABLE

C. TRUNCATE TABLE

The result of the query: SELECT COUNT(*) FROM Employees will be: A. All the records of employee tables B. All the records that contain the character * C. The number of rows in the table Employees D. All of the above

C. The number of rows in the table Employees

You need to create a view to filter rows of data from an underlying table. Which type of clause must be included in the CREATE VIEW statement? A. JOIN B. FILTER C. WHERE D. CONSTRAINT

C. WHERE

Remo works as 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 primary key cannot contain a null value. B. Each attribute should be defined as primary key separately C. A table cannot have two primary keys D. ProductName is declared varchar without specifying the width of the column.

A. An attribute declared as primary key cannot contain a null value. D. ProductName is declared varchar without specifying the width of the column.

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 efficient? A. An index B. A trigger C. A subquery D. A cursor

A. An index

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 the resolve the problem? A. Modify usp_GetEmp to include with the Execute As Owner clause. B. Modify usp_GetEmp to include with the 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.

A. Modify usp_GetEmp to include with the Execute As Owner clause.

Which of the following are DML commands? Each correct answer represents a complete solution. Choose all that apply. A. SELECT B. DELETE C. UPDATE D. ALTER E. USE F. INSERT

A. SELECT B. DELETE C. UPDATE F. INSERT

Which command should you use to display all records from the client table that have the state of New Mexico and the city of Roswell? A. SELECT * FROM clients WHERE state = 'New Mexico' AND city = 'Roswell' B. SELECT * FROM clients WHERE state = "New Mexico" AND city = "Roswell" C. SELECT * FROM clients WHERE state = New Mexico AND city = Roswell D. SELECT * FROM clients WHERE state = 'New Mexico' OR city = 'Roswell'

A. SELECT * FROM clients WHERE state = 'New Mexico' AND city = 'Roswell'

A unique constraint allows you to specify: A. Which column should not contain duplicate values B. The types of data a user can insert into the database C. That data is entered into the cell D. Each record in a database table

A. Which column should not contain duplicate values

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

A. must return a value

Which of the following fixed server roles can shut down the SQL Server? A. serveradmin B. setupadmin C. securityadmin D. processadmin

A. serveradmin

If you need to restore a database after an incremental backup, what do you need to do for a restore? A. Restore the full backup and the first incremental backup B. Restore the full backup and the last incremental backup C. Restore the full backup and the all the incremental backups since last full backups D. Restore the last incremental backup

C. Restore the full backup and the all the incremental backups since last full backups

You have a table named Customer. You need to add a new column named District. Which statement should you use? A. MODIFY TABLE Customer (District INTEGER) B. ALTER TABLE Customer ADD (District INTEGER) C. MODIFY TABLE Customer ADD (District INTEGER) D. ALTER TABLE Customer MODIFY (District INTEGER)

B. ALTER TABLE Customer ADD (District INTEGER)

Which keys establish a relationship between two tables? (more than 1 answer) A. Candidate B. Foreign C. Local D. Primary E. Superkey

B. Foreign D. Primary

Linda works as a database designer for Lion Inc. She has been given an assignment to design the database of the 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 4NF B. It violates 2NF C. It violates 1NF D. There is no violation

B. It violates 2NF

Which of these sentences is incorrect: A. SELECT * FROM cambridge WHERE id IN (1,3,5) B. SELECT * FROM cambridge WHERE id BETWEEN (1,5) C. SELECT * FROM cambridge WHERE id NOT IN (1,5) D. SELECT * FROM cambridge WHERE id <> (15)

B. SELECT * FROM cambridge WHERE id BETWEEN (1,5)

A table named Emp is given below that contains data of an organization. NAME Smith Flora Daniel Esha Emma Henry How many records will be displayed if the following SQL query is executed? SELECT * FROM Emp WHERE Name BETWEEN 'D' AND 'F'? A. 2 B. 3 C. 4 D. A character column cannot be used in the between operator

C. 4 - BETWEEN 'D' AND 'F' --> D,E,F

You have the following SQL query: SELECT FROM Employee WHERE Salary > 5000 The query takes too much time to return data. You need to improve the performance of the query. Which item should be added into the salary column? A. Foreign key B. Default constraint C. Index D. Stored procedure

C. Index

Consider the case of a fruit juice company.The company manufactures fruit juices and supply them to wholesalers. The Database Designer creates a table named Production. The code is given below: 1. CREATE Table Production 2. (Fruit_type varchar, 3. Fruit_name char(20), 4. Quantity int(3)) Which of the above mentioned line has an error? A. Line 3 B. Line 2 C. Line 1 D. Line 4

C. Line 2 (varchar - MUST SPECIFY length of character)

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

C. Outer join

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

D. DROP VIEW EmployeeView

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

D. Role

A company named Rel Inc. has many authorized dealers across the country who sells their products. The sales manager of the company wants to see the details of the authorized dealers, including the name, region, and total sales in ascending order of Sales. Which of the following queries should be issued to get the desired output? A. SELECT MAX(TotalSales) FROM Dealer B. SELECT FROM Dealer C. SELECT * FROM Dealer WHERE MAX(TotalSales) D. SELECT Name, Region, TotalSales FROM Dealer ORDER BY TotalSales

D. SELECT Name, Region, TotalSales FROM Dealer ORDER BY TotalSales

What would enable you to enforce the uniqueness property of columns, other than a primary key within a table? A. Secondary key B. Constant check C. Validator D. Unique constraint key

D. Unique constraint key

You need to store product name 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. CHAR(3,30) B. CHAR(30) C. VARCHAR(3,30) D. VARCHAR(30)

D. VARCHAR(30) example: the character length specified is 30 and there are 20 characters. char - uses 30 bytes to store regardless the length of character (one byte per char) varchar - uses 22 bytes. (one byte per char + 2 byte to hold length information)