Q1. - (Topic 1)
Evaluate the following SQL statement:
SQL> SELECT cust_id, cust_last_name "Last Name"
FROM customers
WHERE country_id = 10
UNION
SELECT cust_id CUST_NO, cust_last_name
FROM customers
WHERE country_id = 30;
Which ORDER BY clause are valid for the above query? (Choose all that apply.)
A. ORDER BY 2,1
B. ORDER BY CUST_NO
C. ORDER BY 2,cust_id
D. ORDER BY "CUST_NO"
E. ORDER BY "Last Name"
Answer: A,C,E
Explanation:
Using the ORDER BY Clause in Set Operations
-The ORDER BY clause can appear only once at the end of the compound query.
-Component queries cannot have individual ORDER BY clauses.
-The ORDER BY clause recognizes only the columns of the first SELECT query.
-By default, the first column of the first SELECT query is used to sort the output in an ascending order.
Q2. - (Topic 2)
What does the FORCE option for creating a view do?
A. creates a view with constraints
B. creates a view even if the underlying parent table has constraints
C. creates a view in another schema even if you don't have privileges
D. creates a view regardless of whether or not the base tables exist
Answer: D
Explanation:
create a view regardless of whether or not the base tables exist.
Incorrect Answer: Athe option is not valid Bthe option is not valid Cthe option is not valid
Refer: Introduction to Oracle9i: SQL, Oracle University Study Guide, 11-3
Q3. - (Topic 2)
Which statements are true regarding the WHERE and HAVING clauses in a SELECT statement?
(Choose all that apply.)
A. The HAVING clause can be used with aggregate functions in subqueries.
B. The WHERE clause can be used to exclude rows after dividing them into groups.
C. The WHERE clause can be used to exclude rows before dividing them into groups.
D. The aggregate functions and columns used in the HAVING clause must be specified in the SELECT list of the query.
E. The WHERE and HAVING clauses can be used in the same statement only if they are applied to different columns in the table.
Answer: A,C
Q4. - (Topic 1)
View the Exhibit and examine the structure of the CUSTOMERS table .Which statement would display the highest credit limit available in each income level in each city in the CUSTOMERS table?
A. SELECT cust_city, cust_income_level, MAX(cust_credit_limit ) FROM customers GROUP BY cust_city, cust_income_level, cust_credit_limit;
B. SELECT cust_city, cust_income_level, MAX(cust_credit_limit) FROM customers GROUP BY cust_city, cust_income_level;
C. SELECT cust_city, cust_income_level, MAX(cust_credit_limit) FROM customers GROUP BY cust_credit_limit, cust_income_level, cust_city ;
D. SELECT cust_city, cust_income_level, MAX(cust_credit_limit) FROM customers GROUP BY cust_city, cust_income_level, MAX(cust_credit_limit);
Answer: B
Q5. - (Topic 1)
Examine the structure of the EMPLOYEES table:
Which UPDATE statement is valid?
A.
UPDATE employees
SET first_name = ‘John’
SET last_name = ‘Smith’
WHERE employee_id = 180;
B.
UPDATE employees
SET first_name = ‘John’,
SET last_name = ‘Smoth’
WHERE employee_id = 180;
C.
UPDATE employee
SET first_name = ‘John’
AND last_name = ‘Smith’
WHERE employee_id = 180;
D.
UPDATE employee
SET first_name = ‘John’, last_name = ‘Smith’
WHERE employee_id = 180;
Answer: D
Q6. - (Topic 1)
Evaluate the following SQL statements: Exhibit:
Which is the correct output of the above query?
A. +00-300, +54-02,+00 11:12:10.123457
B. +00-300,+00-650,+00 11:12:10.123457
C. +25-00, +54-02, +00 11:12:10.123457
D. +25-00,+00-650,+00 11:12:10.123457
Answer: C
Q7. - (Topic 1)
View the Exhibit and examine the structure of the PROMOTIONS table.
You have to generate a report that displays the promo name and start date for all promos that started after the last promo in the 'INTERNET' category.
Which query would give you the required output?
A.
SELECT promo_name, promo_begin_date FROM promotions WHERE promo_begin_date > ALL (SELECT MAX(promo_begin_date) FROM promotions )AND promo_category = 'INTERNET'
B.
SELECT promo_name, promo_begin_date FROM promotions WHERE promo_begin_date IN (SELECT promo_begin_date FROM promotions WHERE promo_category='INTERNET');
C.
SELECT promo_name, promo_begin_date FROM promotions WHERE promo_begin_date > ALL (SELECT promo_begin_date FROM promotions WHERE promo_category = 'INTERNET');
D.
SELECT promo_name, promo_begin_date FROM promotions WHERE promo_begin_date > ANY (SELECT promo_begin_date FROM promotions WHERE promo_category = 'INTERNET');
Answer: C
Q8. - (Topic 2)
Evaluate the SQL statement:
SELECT LPAD (salary,10,’*’)
FROM EMP
WHERE EMP_ID = 1001;
If the employee with the EMP_ID 1001 has a salary of 17000, what is displayed?
A. 17000.00
B. 17000*****
C. ****170.00
D. **17000.00
E. an error statement
Answer: D
Q9. - (Topic 1)
Which two statements are true regarding the USING clause in table joins?(Choose two.)
A. It can be used to join a maximum of three tables.
B. It can be used to restrict the number of columns used in a NATURAL join.
C. It can be used to access data from tables through equijoins as well as nonequijoins.
D. It can be used to join tables that have columns with the same name and compatible data types.
Answer: B,D
Explanation:
NATURAL JOIN operation A NATURAL JOIN is a JOIN operation that creates an implicit join clause for you based on the common columns in the two tables being joined. Common columns are columns that have the same name in both tables. If the SELECT statement in which the NATURAL JOIN operation appears has an asterisk (*) in the select list, the asterisk will be expanded to the following list of columns (in this order):
All the common columns
Every column in the first (left) table that is not a common column
Every column in the second (right) table that is not a common column
An asterisk qualified by a table name (for example, COUNTRIES.*) will be expanded to
every column of that table that is not a common column.
If a common column is referenced without being qualified by a table name, the column
reference points to the column in the first (left) table if the join is an INNER JOIN or a LEFT
OUTER JOIN. If it is a RIGHT OUTER JOIN, unqualified references to a common column
point to the column in the second (right) table.
Syntax
TableExpression NATURAL [ { LEFT | RIGHT } [ OUTER ] | INNER ] JOIN {
TableViewOrFunctionExpression |
( TableExpression ) }
Examples
If the tables COUNTRIES and CITIES have two common columns named COUNTRY and
COUNTRY_ISO_CODE, the following two SELECT statements are equivalent:
SELECT * FROM COUNTRIES NATURAL JOIN CITIES
SELECT * FROM COUNTRIES JOIN CITIES
USING (COUNTRY, COUNTRY_ISO_CODE)
Q10. - (Topic 2)
View the Exhibit and examine the data in the PROMOTIONS table.
PROMO_BEGIN_DATE is stored in the default date format, dd-mon-rr.
You need to produce a report that provides the name, cost, and start date of all promos in the POST category that were launched before January 1, 2000.
Which SQL statement would you use?
A. SELECT promo_name, promo_cost, promo_begin_date FROM promotions WHERE promo_category = 'post' AND promo_begin_date < '01-01-00'
B. SELECT promo_name, promo_cost, promo_begin_date FROM promotions WHERE promo_cost LIKE 'post%' AND promo_begin_date < '01-01-2000'
C. SELECT promo_name, promo_cost, promo_begin_date FROM promotions WHERE promo_category LIKE 'P%' AND promo_begin_date < '1-JANUARY-00'
D. SELECT promo_name, promo_cost, promo_begin_date FROM promotions WHERE promo_category LIKE '%post%' AND promo_begin_date < '1-JAN-00'
Answer: D