Oracle MySQL 5.0, 5.1 and 5.5 Certified Associate Set 1

You work as a Database Administrator for Pass4sure.com. The company uses MySQL as its database.
The database contains a table named Employees. You want to remove an index named Emp_name_idx from the Employees table. Which of the following statements should you use to accomplish the task?

Which of the following statements will delete all the records from the table T1? Each correct answer represents a complete solution. Choose all that apply.

You work as a Database Administrator for Pass4sure.com. The company uses the MySQL database.
You have created a table named Employees. The table contains the following structurE.
The company has three departments. A task has been assigned to you to calculate the sum of the average length of employees' last name per department. Which of the following queries will you execute to accomplish
the task?

You work as a Database Administrator for Pass4sure.com. The company uses MySQL as its database. You want to use group functions, so you have decided to put an alias of group functions as 'GP'. Which of the following are the correct ways to use group functions? Each correct answer represents a complete solution. Choose all that apply.

Adam works as a Database Administrator for Pass4sure.com. Adam has created a table named Students. In this table, Adam wants to create a column to store the fees of students. Which of the following data types will Adam use to accomplish the task? Each correct answer represents a complete solution. Choose all that apply.

Review the following ER diagram:
Which of the following SQL statements will return all rows for a given CUST_ID and PROD_ID where sales of specific products on a given day are greater than 10?
Additionally the list should be ordered by CUST_LAST_NAME, CUST_FIRST_NAME and PROD_NAME.

Speed Inc. is a courier company. It delivers letters, parcels, and other items to their desired destination. The company wants to create a database that keeps the records of items received, items delivered, and also the
information about any undelivered item. A table named Courierdetail has the following attributes:
CustomerName
Address
ContactNumber
DateOfReceiving
DeliveryAcknowledgement
Which of the above-mentioned attributes can be designated as the primary key?

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?

Which of the following functions can be performed by a view? Each correct answer represents a complete solution. Choose all that apply.

Mark works as a Database Administrator for Neon Inc. He issues the following query to insert rows in a table
named Emp:
SELECT * FROM Emp
SET AUTOCOMMIT=0
INSERT INTO Emp VALUES('XXX','John',04/02/1977)
INSERT INTO Emp VALUES('YYY','Richard',03/01/1977)
ROLLBACK
How many rows will be inserted in the Emp table after the ROLLBACK statement has been issued?