Q1. In the customers table, the CUST_CITY column contains the value 'Paris' for the CUST_FIRST_NAME 'Abigail'. 

Evaluate the following query: 

What would be the outcome? 

A. Abigail PA 

B. Abigail Pa 

C. Abigail IS 

D. An error message 

View Answer

Q2. View the Exhibit for the structure of the student and faculty tables. 

You need to display the faculty name followed by the number of students handled by the faculty at the base location. 

Examine the following two SQL statements: Which statement is true regarding the outcome? 

A. Only statement 1 executes successfully and gives the required result. 

B. Only statement 2 executes successfully and gives the required result. 

C. Both statements 1 and 2 execute successfully and give different results. 

D. Both statements 1 and 2 execute successfully and give the same required result. 

View Answer

Q3. Examine the structure of the employees table: 

There is a parent/child relationship between EMPLOYEE_ID and MANAGER_ID. 

You want to display the name, joining date, and manager for all the employees. Newly hired employees are yet to be assigned a department or a manager. For them, 'No Manager1 should be displayed in the manager column. 

Which SQL query gets the required output? 

A. Option A 

B. Option B 

C. Option C 

D. Option D 

View Answer

Q4. Using the customers table, you need to generate a report that shows 50% of each credit amount in each income level. The report should NOT show any repeated credit amounts in each income level. 

Which query would give the required result? 

A. Option A 

B. Option B 

C. Option C 

D. Option D 

View Answer

Q5. View the Exhibit and examine the data in the PROMO_NAME and PROMO_END_DATE columns of the promotions table, and the required output format. 

Which two queries give the correct result? A. Option A 

B. Option B 

C. Option C 

D. Option D 

View Answer

Q6. View the Exhibits and examine products and sales tables. 

You issue the following query to display product name and the number of times the product has been sold: 

What happens when the above statement is executed? 

A. The statement executes successfully and produces the required output. 

B. The statement produces an error because item_cnt cannot be displayed in the outer query. 

C. The statement produces an error because a subquery in the from clause and outer-joins cannot be used together. 

D. The statement produces an error because the group by clause cannot be used in a subquery in the from clause. 

View Answer

Q7. View the Exhibit and examine the structure of the customers table. 

NEW_CUSTOMERS is a new table with the columns CUST_ID, CUST_NAME and CUST_CITY that have the same data types and size as the corresponding columns in the customers table. 

Evaluate the following insert statement: The insert statement fails when executed. 

What could be the reason? 

A. The values clause cannot be used in an INSERT with a subquery. 

B. Column names in the NEW_CUSTOMERS and CUSTOMERS tables do not match. 

C. The where clause cannot be used in a subquery embedded in an INSERT statement. 

D. The total number of columns in the NEW_CUSTOMERS table does not match the total number of columns in the CUSTOMERS table. 

View Answer

Q8. Examine the structure of the customers table: 

CUSTNO is the primary key in the table. You want to find out if any customers' details have been entered more than once using different CUSTNO, by listing all the duplicate names. 

Which two methods can you use to get the required result? 

A. Self-join 

B. Subquery 

C. Full outer-join with self-join 

D. Left outer-join with self-join 

E. Right outer-join with self-join 

View Answer

Q9. You need to create a table with the following column specifications: 

1. Employee ID (numeric data type) for each employee 

2. Employee Name (character data type) that stores the employee name 

3. Hire date, which stores the date of joining the organization for each employee 

4. Status (character data type), that contains the value 'active1 if no data is entered 

5. Resume (character large object [CLOB] data type), which contains the resume submitted by the employee 

Which is the correct syntax to create this table? 

A. Option A 

B. Option B 

C. Option C 

D. Option D 

View Answer

Q10. You issued the following command: 

SQL> DROP TABLE employees; 

Which three statements are true? 

A. All uncommitted transactions are committed. 

B. All indexes and constraints defined on the table being dropped are also dropped. 

C. Sequences used in the employees table become invalid. 

D. The space used by the employees table is reclaimed immediately. 

E. The employees table can be recovered using the rollback command. 

F. The employees table is moved to the recycle bin. 

View Answer