Q1. - (Topic 1) 

View the Exhibit and examine the structure of the CUSTOMERS table. Exhibit: 

you issue the following SQL statement on the CUSTOMERS table to display the customers who are in the same country as customers with the last name 'king' and whose credit limit is less than the maximum credit limit in countries that have customers with the last name 'king'. 

Which statement is true regarding the outcome of the above query? 

A. It produces an error and the < operator should be replaced by < ANY to get the required output 

B. It produces an error and the IN operator should be replaced by = in the WHERE clause of the main query to get the required output 

C. It executes and shows the required result 

D. It produces an error and the < operator should be replaced by < ALL to get the required output 

View Answer

Q2. - (Topic 2) 

Evaluate the following query: 

SELECT INTERVAL '300' MONTH, 

INTERVAL '54-2' YEAR TO MONTH, 

INTERVAL '11:12:10.1234567' HOUR TO SECOND 

FROM dual; 

What is the correct output of the above query? 

A. +25-00 , +54-02, +00 11:12:10.123457 

B. +00-300, +54-02, +00 11:12:10.123457 

C. +25-00 , +00-650, +00 11:12:10.123457 

D. +00-300 , +00-650, +00 11:12:10.123457 

View Answer

Q3. - (Topic 1) 

Which two are true about aggregate functions? (Choose two.) 

A. You can use aggregate functions in any clause of a SELECT statement. 

B. You can use aggregate functions only in the column list of the select clause and in the WHERE clause of a SELECT statement. 

C. You can mix single row columns with aggregate functions in the column list of a SELECT statement by grouping on the single row columns. 

D. You can pass column names, expressions, constants, or functions as parameter to an aggregate function. 

E. You can use aggregate functions on a table, only by grouping the whole table as one single group. 

F. You cannot group the rows of a table by more than one column while using aggregate functions. 

View Answer

Q4. - (Topic 2) 

Which constraint can be defined only at the column level? 

A. UNIQUE 

B. NOT NULL 

C. CHECK 

D. PRIMARY KEY 

E. FOREIGN KEY 

View Answer

Q5. - (Topic 1) 

View the Exhibit and examine the structure of the CUSTOMERS table. 

You have been asked to produce a report on the CUSTOMERS table showing the customers details sorted in descending order of the city and in the descending order of their income level in each city. Which query would accomplish this task? 

A. SELECT cust_city, cust_income_level, cust_last_name FROM customers ORDER BY cust_city desc, cust_income_level DESC; 

B. SELECT cust_city, cust_income_level, cust_last_name FROM customers ORDER BY cust_income_level desc, cust_city DESC; 

C. SELECT cust_city, cust_income_level, cust_last_name 

FROM customers 

ORDER BY (cust_city, cust_income_level) DESC; 

D. SELECT cust_city, cust_income_level, cust_last_name FROM customers ORDER BY cust_city, cust_income_level DESC; 

View Answer

Q6. - (Topic 1) 

Which three SQL statements would display the value 1890.55 as $1,890.55? (Choose three.) 

A. 

SELECT TO_CHAR(1890.55,'$99G999D00') FROM DUAL; 

B. 

SELECT TO_CHAR(1890.55,'$9,999V99') FROM DUAL; 

C. 

SELECT TO_CHAR(1890.55,'$0G000D00') FROM DUAL; 

D. 

SELECT TO_CHAR(1890.55,'$99G999D99') 

FROM DUAL; 

E. 

SELECT TO_CHAR(1890.55,'$9,999D99') FROM DUAL; 

View Answer

Q7. - (Topic 1) 

Which view should a user query to display the columns associated with the constraints on a table owned by the user? 

A. USER_CONSTRAINTS 

B. USER_OBJECTS 

C. ALL_CONSTRAINTS 

D. USER_CONS_COLUMNS 

E. USER_COLUMNS 

View Answer

Q8. - (Topic 1) 

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. SELECT cust_income_level, DISTINCT cust_credit_limit * 0.50 AS "50% Credit Limit" 

FROM customers; 

B. SELECT DISTINCT cust_income_level, DISTINCT cust_credit_limit * 0.50 AS "50% 

Credit Limit" 

FROM customers; 

C. SELECT DISTINCT cust_income_level ' ' cust_credit_limit * 0.50 AS "50% Credit Limit" 

FROM customers; 

D. SELECT cust_income_level ' ' cust_credit_limit * 0.50 AS "50% Credit Limit" FROM 

customers; 

View Answer

Q9. - (Topic 2) 

Which SQL statement returns a numeric value? 

A. SELECT ADD_MONTHS(MAX(hire_Date), 6) FROM EMP; 

B. SELECT ROUND(hire_date) FROM EMP; 

C. SELECT sysdate-hire_date FROM EMP; 

D. SELECT TO_NUMBER(hire_date + 7) FROM EMP; 

View Answer

Q10. - (Topic 2) 

View the Exhibit and examine the data in the PROMO_CATEGORY and PROMO_COST columns of the PROMOTIONS table. 

Evaluate the following two queries: 

SQL>SELECT DISTINCT promo_category to_char(promo_cost)"code" 

FROM promotions 

ORDER BY code; 

SQL>SELECT DISTINCT promo_category promo_cost "code" 

FROM promotions 

ORDER BY 1; 

Which statement is true regarding the execution of the above queries? 

A. Only the first query executes successfully. 

B. Only the second query executes successfully. 

C. Both queries execute successfully but give different results. 

D. Both queries execute successfully and give the same result. 

View Answer