Oracle MySQL 5.0, 5.1 and 5.5 Certified Associate Set 4

1.

You work as a Database Administrator for Pass4sure.com. You have created a table named Students in a database. You want to add a PRIMARY KEY constraint to the Student_ID column of the Students table. Which
of the following statements will you use to accomplish the task?

UPDATE TABLE Students
ADD CONSTRAINT Stu_ID_pk PRIMARY KEY (Student_ID); MODIFY CONSTRAINT Stu_ID_pk PRIMARY KEY (Student_ID); ALTER TABLE StudentsADD CONSTRAINT Stu_ID_pk PRIMARY KEY (Student_ID); ADD CONSTRAINT Stu_ID_pk PRIMARY KEY (Student_ID);

2.

What kind of join automatically joins two or more tables based on common column names?

Equi-join Inner Join Self Join Natural Join

3.

Review the following ER diagram:
Which of the following queries will properly display the average unit_cost (rounded to two decimal places) for a given product on a Saturday and Order the results by the PROD_NAME column?

SELECT a.prod_name, AVG(b.unit_cost)
FROM products a, costs b, times c
WHERE a.prod_id=b.prod_id
AND c.time_id=b.time_id
AND c.day_name='Saturday'
GROUP BY a.prod_name
ORDER BY a.prod_name; SELECT a.prod_name, ROUND(AVG(b.unit_cost),2)FROM products a, costs b, times c
WHERE a.prod_id=b.prod_id
AND c.time_id=b.time_id
AND c.day_name='Saturday'; SELECT a.prod_name, ROUND(AVG(b.unit_cost),2)FROM products a, costs b, times c
WHERE a.prod_id=b.prod_id
AND c.time_id=b.time_id
AND c.day_name='Saturday GROUP BY a.prod_nameORDER BY ROUND(AVG(b.unit_cost),2); SELECT a.prod_name, ROUND(AVG(b.unit_cost),2)FROM products a, costs b, times c
WHERE a.prod_id=b.prod_id
AND c.time_id=b.time_id
AND c.day_name='Saturday'
GROUP BY a.prod_name
ORDER BY a.prod_name;

4.

Which of the following is the correct syntax for the DESCRIBE command?

DESC[RIBE] {[schema.] Object. table_name} DESC[RIBE] {[schema.] Object [@net_service_name]} DESC[RIBE] {[schema.] table_name} DESC[RIBE] {[schema.entity] WHERE [@net_service_name]}

5.

Which of the following are true about UPDATE statements? Each correct answer represents a complete solution. Choose all that apply.

You can use co-related sub query in UPDATE statements to update data from other tables. If you don't use the WHERE clause then the UPDATE will not update any rows in the table. You use the SET clause to update multiple columns of a table separated by commas. You can use the WHERE clause to have your update affects a specific set of rows.

6.

Which of the following INSERT statements is correct?

INSERT INTO Employees VALUES (1001, 'Maria', '100 Park Street', '17-JUN-99'); INSERT INTO Employees VALUES (1001, 'Maria', '100 Park Street', '17-JUN-99') INSERT Employees (1001, 'Maria', '100 Park Street', '17-JUN-99'); INSERT INTO Employees VALUES (1001, Maria, 100 Park Street, '17-JUN-99');

7.

You work as a Database Administrator for Pass4sure.com. The company uses MySQL as its database. You have created a table named Employees. You want to insert a row in the table. The structure of the table is as follows: Emp_ID NUMBER PRIMARY KEY Emp_Name VARCHAR2 (30) Emp_Address VARCHAR2 (40) Which of the following statements can you use to accomplish the task? Each correct answer represents a complete solution. Choose all that apply.

INSERT INTO Employees ('Adam', 'California'); INSERT INTO EmployeesVALUES (100, 'Adam', 'California'); INSERT INTO Employees (Emp_Name, Emp_Address)VALUES ('Adam', 'California'); INSERT INTO Employees (Emp_ID, Emp_Name, Emp_Address) VALUES (100, 'Adam', 'California');

8.

Which of the following clauses is used with the CREATE VIEW statement?

ORDER BY WHERE WITH CHECK OPTION GROUP BY

9.

Adam works as a Database Administrator for Pass4sure.com. He creates a table named Students. He wants to create a new table named Class with the help of the Students table. Which of the following syntaxes will Adam
use to accomplish the task?

CREATE TABLE Class
INSERT INTO SELECT * FROM Students; CREATE TABLE ClassAS SELECT * FROM Students; CREATE TABLE Class(SELECT * FROM Students); CREATE TABLE ClassFROM SELECT * FROM Students;

10.

Which of the following statements will a user use to display a database in MySQL?

SHOW DATABASE; SELECT * FROM DATABASES; VIEW DATABASES; DISPLAY DATABASES;

Oracle MySQL 5.0, 5.1 and 5.5 Certified Associate Set 4

Answer & Solution

Answer: Option B

Solution:

Answer & Solution

Answer: Option B

Solution:

Answer & Solution

Answer: Option B

Solution:

Answer & Solution

Answer: Option B

Solution:

Answer & Solution

Answer: Option B

Solution:

Answer & Solution

Answer: Option B

Solution:

Answer & Solution

Answer: Option B

Solution:

Answer & Solution

Answer: Option B

Solution:

Answer & Solution

Answer: Option B

Solution:

Answer & Solution

Answer: Option B

Solution:

Congratulations

COMPANY

Products

OTHERS

Partner

Oracle MySQL 5.0, 5.1 and 5.5 Certified Associate Set 4

Answer & Solution

Answer: Option B

Solution:

Answer & Solution

Answer: Option B

Solution:

Answer & Solution

Answer: Option B

Solution:

Answer & Solution

Answer: Option B

Solution:

Answer & Solution

Answer: Option B

Solution:

Answer & Solution

Answer: Option B

Solution:

Answer & Solution

Answer: Option B

Solution:

Answer & Solution

Answer: Option B

Solution:

Answer & Solution

Answer: Option B

Solution:

Answer & Solution

Answer: Option B

Solution:

Congratulations

Detail Form

COMPANY

Products

OTHERS

Partner