Quiz
DBA 191
This quiz has a time limit, so it opens in the usual player.
Questions in this quiz (191)
1Examine the structure of the PROMOTIONS table.
Each promotion has a duration of at least seven days. Your manager has asked you to generate a report, which provides the weekly cost for each promotion done to date.
Which query would achieve the required result?
- ASELECT promo_name, promo_cost/promo_end_date-promo_begin_date/7 FROM promotions;
- BSELECT promo_name,(promo_cost/promo_end_date-promo_begin_date)/7 FROM promotions;
- CSELECT promo_name, promo_cost/(promo_end_date-promo_begin_date/7) FROM promotions;
- DSELECT promo_name, promo_cost/((promo_end_date-promo_begin_date)/7) FROM promotions;
2View the Exhibit and examine the description for the PRODUCTS and SALES table. PROD_ID is a primary key in the PRODUCTS table and foreign key in the SALES table. You want to remove all the rows from the PRODUCTS table for which no sale was done for the last three years. Which is the valid DELETE statement?
- ADELETE
FROM products
WHERE prod_id = (SELECT prod_id
FROM sales
WHERE time_id – 3*365 = SYSDATE ); - BDELETE
FROM products
WHERE prod_id = (SELECT prod_id
FROM sales
WHERE SYSDATE >= time_id– 3*365); - CDELETE
FROM products
WHERE prod_id IN (SELECT prod_id
FROM sales
WHERE SYSDATE–3*365 >= time_id); - DDELETE
FROM products
WHERE prod_id IN (SELECT prod_id
FROM sales
WHERE time_id >= SYSDATE–3*365);
- ADELETE
3View the Exhibit and examine the description of SALES and PROMOTIONS tables.
You want to delete rows from the SALES table, where the PROMO_NAME column in the PROMOTIONS table has either blowout sale or everyday low price as values.
Which DELETE statements are valid? (Choose all that apply.)
- ADELETE
FROM sales
WHERE promo_id = (SELECT promo_id
FROM promotions
WHERE promo_name = ‘blowout sale’)
AND promo_id = (SELECT promo_id
FROM promotions
WHERE promo_name = ‘everyday low price’); - BDELETE
FROM sales
WHERE promo_id = (SELECT promo_id
FROM promotions
WHERE promo_name = ‘blowout sale’)
OR promo_id = (SELECT promo_id
FROM promotions
WHERE promo_name = ‘everyday low price’); - CDELETE
FROM sales
WHERE promo_id IN (SELECT promo_id
FROM promotions
WHERE promo_name = ‘blowout sale’
OR promo_name = ‘everyday low price’); - DDELETE
FROM sales
WHERE promo_id IN (SELECT promo_id
FROM promotions
WHERE promo_name IN (‘blowout sale’,’everyday low price’));
- ADELETE
4View the Exhibit and examine the description for the CUSTOMERS table.
You want to update the CUST_INCOME_LEVEL and CUST_CREDIT_LIMIT columns for the customer with the CUST_ID 2360. You want the value for the CUST_INCOME_LEVEL to have the same value as that of the customer with the CUST_ID 2560 and the CUST_CREDIT_LIMIT to have the same value as that of the customer with CUST_ID 2566.
Which UPDATE statement will accomplish the task?
- AUPDATE customers
SET cust_income_level = (SELECT cust_income_level
FROM customers
WHERE cust_id = 2560),
cust_credit_limit = (SELECT cust_credit_limit
FROM customers
WHERE cust_id = 2566)
WHERE cust_id=2360; - BUPDATE customers
SET (cust_income_level,cust_credit_limit) = (SELECT
cust_income_level, cust_credit_limit
FROM customers
WHERE cust_id=2560 OR cust_id=2566)
WHERE cust_id=2360; - CUPDATE customers
SET (cust_income_level,cust_credit_limit) = (SELECT
cust_income_level, cust_credit_limit
FROM customers
WHERE cust_id IN(2560, 2566)
WHERE cust_id=2360; - DUPDATE customers
SET (cust_income_level,cust_credit_limit) = (SELECT
cust_income_level, cust_credit_limit
FROM customers
WHERE cust_id=2560 AND cust_id=2566)
WHERE cust_id=2360;
- AUPDATE customers
5View the Exhibit and examine the structure of CUSTOMERS and SALES tables.
Evaluate the following SQL statement:
UPDATE (SELECT prod_id, cust_id, quantity_sold, time_id
FROM sales)
SET time_id = ’22-MAR-2007′
WHERE cust_id = (SELECT cust_id
FROM customers
WHERE cust_last_name = ‘Roberts’ AND credit_limit = 600);
Which statement is true regarding the execution of the above UPDATE statement?- AIt would not execute because two tables cannot be used in a single UPDATE statement.
- BIt would not execute because the SELECT statement cannot be used in place of the table name.
- CIt would execute and restrict modifications to only the columns specified in the SELECT statement.
- DIt would not execute because a subquery cannot be used in the WHERE clause of an UPDATE statement.
6View the Exhibit and examine the description for the CUSTOMERS table.
You want to update the CUST_CREDIT_LIMIT column to NULL for all the customers, where CUST_INCOME_LEVEL has NULL in the CUSTOMERS table.
Which SQL statement will accomplish the task?
- AUPDATE customers
SET cust_credit_limit = NULL
WHERE CUST_INCOME_LEVEL = NULL; - BUPDATE customers
SET cust_credit_limit = NULL
WHERE cust_income_level IS NULL; - CUPDATE customers
SET cust_credit_limit = TO_NUMBER(NULL)
WHERE cust_income_level = TO_NUMBER(NULL); - DUPDATE customers
SET cust_credit_limit = TO_NUMBER(‘ ‘,9999)
WHERE cust_income_level IS NULL;
- AUPDATE customers
7View the exhibit and examine the description for the SALES and CHANNELS tables.
You issued the following SQL statement to insert a row in the SALES table:
INSERT INTO sales VALUES
(23, 2300, SYSDATE, (SELECT channel_id
FROM channels
WHERE channel_desc=’Direct Sales’), 12, 1, 500);
Which statement is true regarding the execution of the above statement?
- AThe statement will execute and the new row will be inserted in the SALES table.
- BThe statement will fail because subquery cannot be used in the VALUES clause.
- CThe statement will fail because the VALUES clause is not required with subquery.
- DThe statement will fail because subquery in the VALUES clause is not enclosed with in single quotation marks.
8View the Exhibit and examine the structure of ORDERS and CUSTOMERS tables.
There is only one customer with the cust_last_name column having value Roberts.
Which INSERT statement should be used to add a row into the ORDERS table for the customer whose
CUST_LAST_NAME is Roberts and CREDIT_LIMIT is 600?
- AINSERT INTO orders
VALUES (1,’10-mar-2007′, ‘direct’,
(SELECT customer_id
FROM customers
WHERE cust_last_name=’Roberts’ AND credit_limit=600), 1000); - BINSERT INTO orders (order_id,order_date,order_mode,
(SELECT customer_id
FROM customers
WHERE cust_last_name=’Roberts’ AND credit_limit=600),order_total)
VALUES(1,’10-mar-2007′, ‘direct’, &&customer_id, 1000); - CINSERT INTO(SELECT o.order_id, o.order_date,o.order_mode,c.customer_id, o.order_total
FROM orders o, customers c
WHERE o.customer_id = c.customer_id
AND c.cust_last_name=’Roberts’ ANDc.credit_limit=600 )
VALUES (1,’10-mar-2007′, ‘direct’,(SELECT customer_id
FROM customers
WHERE cust_last_name=’Roberts’ AND credit_limit=600), 1000); - DINSERT INTO orders (order_id,order_date,order_mode,
(SELECT customer_id
FROM customers
WHERE cust_last_name=’Roberts’ AND credit_limit=600),order_total)
VALUES(1,’10-mar-2007′, ‘direct’, &customer_id, 1000);
- AINSERT INTO orders
9View 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:
INSERT INTO new_customers (cust_id, cust_name, cust_city)
VALUES(SELECT cust_id,cust_first_name’ ‘cust_last_name,cust_city
FROM customers
WHERE cust_id > 23004);
The INSERT statement fails when executed.
What could be the reason?
- AThe VALUES clause cannot be used in an INSERT with a subquery.
- BColumn names in the NEW_CUSTOMERS and CUSTOMERS tables do not match.
- CThe WHERE clause cannot be used in a subquery embedded in an INSERT statement.
- DThe total number of columns in the NEW_CUSTOMERS table does not match the total number of columns in the CUSTOMERS table.
10View the Exhibit and examine the structure of the PRODUCTS tables.
You want to generate a report that displays the average list price of product categories where the average list price is less than half the maximum in each category.
Which query would give the correct output?
- ASELECT prod_category,avg(prod_list_price)
FROM products
GROUP BY prod_category
HAVING avg(prod_list_price) < ALL
(SELECT max(prod_list_price)/2
FROM products
GROUP BY prod_category); - BSELECT prod_category,avg(prod_list_price)
FROM products
GROUP BY prod_category
HAVING avg(prod_list_price) > ANY
(SELECT max(prod_list_price)/2
FROM products
GROUP BY prod_category); - CSELECT prod_category,avg(prod_list_price)
FROM products
HAVING avg(prod_list_price) < ALL
(SELECT max(prod_list_price)/2
FROM products
GROUP BY prod_category); - DSELECT prod_category,avg(prod_list_price)
FROM products
GROUP BY prod_category
HAVING avg(prod_list_price) > ANY
(SELECT max(prod_list_price)/2
FROM products);
- ASELECT prod_category,avg(prod_list_price)
11View the Exhibit and examine the structure of the PROMOTIONS table.
Evaluate the following SQL statement:
SQL>SELECT promo_name,CASE
WHEN promo_cost >=(SELECT AVG(promo_cost)
FROM promotions
WHERE promo_category=’TV’)
then ‘HIGH’
else ‘LOW’
END COST_REMARK
FROM promotions;
- AIt shows COST_REMARK for all the promos in the table.
- BIt produces an error because the subquery gives an error.
- CIt shows COST_REMARK for all the promos in the promo category ‘TV’.
- DIt produces an error because subqueries cannot be used with the CASE expression.
12View the Exhibit and examine the structure of the CUSTOMERS table.
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’:
SQL> SELECT cust_id,cust_last_name
FROM customers
WHERE country_id IN(SELECT country_id
FROM customers
WHERE cust_last_name =’King’)
AND cust_credit_limit < (SELECT MAX(cust_credit_limit)
FROM customers
WHERE country_id IN(SELECT country_id
FROM customers
WHERE cust_last_name=’King’));
Which statement is true regarding the outcome of the above query?
- AIt executes and shows the required result.
- BIt produces an error and the < operator should be replaced by < ALL to get the required output.
- CIt produces an error and the < operator should be replaced by < ANY to get the required output.
- DIt produces an error and the IN operator should be replaced by = in the WHERE clause of the main query to get the required output.
13View the Exhibit and examine the structure of the PRODUCTS table.
You want to display the category with the maximum number of items.
You issue the following query:
SQL>SELECT COUNT(*),prod_category_id
FROM products
GROUP BY prod_category_id
HAVING COUNT(*) = (SELECT MAX(COUNT(*)) FROM products);
What is the outcome?
- AIt generates an error because = is not valid and should be replaced by the IN operator.
- BIt executes successfully but does not give the correct output.
- CIt executes successfully and gives the correct output.
- DIt generates an error because the subquery does not have a GROUP BY clause.
14View 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?
- ASELECT promo_name, promo_begin_date FROM promotions
WHERE promo_begin_date > ANY (SELECT promo_begin_date
FROM promotions
WHERE promo_category = ‘INTERNET’); - BSELECT promo_name, promo_begin_date FROM promotions
WHERE promo_begin_date IN (SELECT promo_begin_date
FROM promotions
WHERE promo_category=’INTERNET’); - CSELECT promo_name, promo_begin_date FROM promotions
WHERE promo_begin_date > ALL (SELECT MAX(promo_begin_date)
FROM promotions) AND
promo_category = ‘INTERNET’; - DSELECT promo_name, promo_begin_date FROM promotions
WHERE promo_begin_date > ALL (SELECT promo_begin_date
FROM promotions
WHERE promo_category = ‘INTERNET’);
- ASELECT promo_name, promo_begin_date FROM promotions
15View the Exhibit and examine the structure of the PRODUCTS table.
Evaluate the following query:
SQL> SELECT prod_name
FROM products
WHERE prod_id IN (SELECT prod_id FROM products
WHERE prod_list_price =
(SELECT MAX(prod_list_price)FROM products
WHERE prod_list_price <
(SELECT MAX(prod_list_price)FROM products)));
What would be the outcome of executing the above SQL statement?
- AIt shows the names of all products whose list price is less than the maximum list price.
- BIt shows the names of products whose list price is the second highest in the table.
- CIt shows the names of all products in the table.
- DIt produces an error.
16View the Exhibit and examine the structure of the PRODUCTS table.
Which two tasks would require subqueries? (Choose two.)
- ADisplay the total number of products supplied by supplier 102 and have product status as ‘obsolete’.
- BDisplay all products whose minimum list price is more than the average list price of products and have the status ‘orderable’.
- CDisplay all suppliers whose list price is less than 1000.
- DDisplay the number of products whose list price is more than the average list price.
- EDisplay the minimum list price for each product status.
17View the Exhibit and examine the structure of the PROMOTIONS table.
Examine the following two SQL statements:
Statement 1
SQL>SELECT promo_category,SUM(promo_cost)
FROM promotions
WHERE promo_end_date-promo_begin_date > 30
GROUP BY promo_category;
Statement 2
SQL>SELECT promo_category,sum(promo_cost)
FROM promotions
GROUP BY promo_category
HAVING MIN(promo_end_date-promo_begin_date)>30;
Which statement is true regarding the above two SQL statements?
- Astatement 1 and statement 2 execute successfully and give the same output
- Bstatement 1 and statement 2 execute successfully and give a different output
- Cstatement 1 gives an error, statement 2 executes successfully
- Dstatement 2 gives an error, statement 1 executes successfully
18View the Exhibit and examine the structure of the CUSTOMERS table.
Evaluate the following SQL statement:
SQL> SELECT cust_city, COUNT(cust_last_name)
FROM customers
WHERE cust_credit_limit > 1000
GROUP BY cust_city
HAVING AVG(cust_credit_limit) BETWEEN 5000 AND 6000;
Which statement is true regarding the outcome of the above query?
- AIt executes successfully.
- BIt returns an error because WHERE and HAVING clauses cannot be used in the same SELECT statement.
- CIt returns an error because WHERE and HAVING clauses cannot be used to apply conditions on the same column.
- DIt returns an error because the BETWEEN operator cannot be used in the HAVING clause.
19View the Exhibit and examine the structure of the SALES table.
The following query is written to retrieve all those product ID s from the SALES table that have more than 55000 sold and have been ordered more than 10 times.
SQL> SELECT prod_id
FROM sales
WHERE quantity_sold > 55000 AND COUNT(*)>10
GROUP BY prod_id
HAVING COUNT(*)>10;
Which statement is true regarding this SQL statement?
- AIt executes successfully and generates the required result.
- BIt executes successfully but produces no result because COUNT(prod_id) should be used instead of COUNT(*).
- CIt produces an error because COUNT(*) should be specified in the SELECT clause also.
- DIt produces an error because COUNT(*) should be only in the HAVING clause and not in the WHERE clause.
20View the Exhibit and examine the structure of the PROMOTIONS table.
Evaluate the following SQL statement:
SQL>SELECT promo_category, AVG(promo_cost) Avg_Cost, AVG(promo_cost)*.25 Avg_Overhead
FROM promotions
WHERE UPPER(promo_category) IN (‘TV’, ‘INTERNET’,’POST’)
GROUP BY Avg_Cost
ORDER BY Avg_Overhead;
The above query generates an error on execution.
Which clause in the above SQL statement causes the error?
- ASELECT
- BWHERE
- CORDER BY
- DGROUP BY
21View 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?
- ASELECT cust_city, cust_income_level, MAX(cust_credit_limit) FROM customers
GROUP BY cust_credit_limit, cust_income_level, cust_city ; - BSELECT cust_city, cust_income_level, MAX(cust_credit_limit ) FROM customers
GROUP BY cust_city, cust_income_level, cust_credit_limit; - CSELECT cust_city, cust_income_level, MAX(cust_credit_limit) FROM customers
GROUP BY cust_city, cust_income_level, MAX(cust_credit_limit); - DSELECT cust_city, cust_income_level, MAX(cust_credit_limit) FROM customers
GROUP BY cust_city, cust_income_level;
- ASELECT cust_city, cust_income_level, MAX(cust_credit_limit) FROM customers
22View the Exhibit and examine the structure of the CUSTOMERS table.
Using the CUSTOMERS table, you need to generate a report that shows the average credit limit for customers in WASHINGTON and NEW YORK.
Which SQL statement would produce the required result?
- ASELECT cust_city, AVG(cust_credit_limit)
FROM customers
WHERE cust_city IN (‘WASHINGTON’,’NEW YORK’)
GROUP BY cust_city; - BSELECT cust_city, AVG(cust_credit_limit)
FROM customers
WHERE cust_city IN (‘WASHINGTON’,’NEW YORK’)
GROUP BY cust_credit_limit, cust_city; - CSELECT cust_city, AVG(cust_credit_limit)
FROM customers
WHERE cust_city IN (‘WASHINGTON’,’NEW YORK’)
GROUP BY cust_city,cust_credit_limit; - DSELECT cust_city, AVG(NVL(cust_credit_limit,0))
FROM customers
WHERE cust_city IN (‘WASHINGTON’,’NEW YORK’);
- ASELECT cust_city, AVG(cust_credit_limit)
23View the Exhibit and examine the structure of the PROMOTIONS table.
Which SQL statements are valid? (Choose all that apply.)
- ASELECT promo_id, DECODE(promo_cost, >10000, ‘High’,
<10000, ‘Low’) “Range”
FROM promotions; - BSELECT promo_id, DECODE(NVL(promo_cost,0), promo_cost,
promo_cost * 0.25, 100) “Discount”
FROM promotions; - CSELECT promo_id, DECODE(NULLIF(promo_cost, 10000),
NULL, promo_cost*.25, ‘N/A’) “Catcost”
FROM promotions; - DSELECT promo_id, DECODE(promo_cost, 10000,
DECODE(promo_category, ‘G1’, promo_cost *.25, NULL),
NULL) “Catcost”
FROM promotions;
- ASELECT promo_id, DECODE(promo_cost, >10000, ‘High’,
24View the Exhibit and examine the structure of the PROMOTIONS table.
Using the PROMOTIONS table, you need to find out the average cost for all promos in the ranges $0-2000 and $2000-5000 in category A
You issue the following SQL statement:
SQL>SELECT AVG(CASE
WHEN promo_cost BETWEEN 0 AND 2000 AND promo_category=’A’
then promo_cost
ELSE null END) “CAT_2000A”,
AVG(CASE
WHEN promo_cost BETWEEN 2001 AND 5000 AND promo_category=’A’
THEN promo_cost
ELSE null END) “CAT_5000A”
FROM promotions;
What would be the outcome?
- AIt generates an error because CASE cannot be used with group functions.
- BIt generates an error because NULL cannot be specified as a return value.
- CIt generates an error because multiple conditions cannot be specified for the WHEN clause.
- DIt executes successfully and gives the required result.
25View the Exhibit and examine the structure of the CUSTOMERS table.
Using the CUSTOMERS table, you need to generate a report that shows an increase in the credit limit by 15% for all customers. Customers whose credit limit has not been entered should have the message “Not Available” displayed.
Which SQL statement would produce the required result?
- ASELECT TO_CHAR(NVL(cust_credit_limit*.15,’Not Available’)) “NEW CREDIT” FROM
customers; - BSELECT NVL(TO_CHAR(cust_credit_limit*.15),’Not Available’) “NEW CREDIT” FROM
customers; - CSELECT NVL(cust_credit_limit*.15,’Not Available’) “NEW CREDIT” FROM customers;
- DSELECT NVL(cust_credit_limit,’Not Available’)*.15 “NEW CREDIT” FROM customers;
- ASELECT TO_CHAR(NVL(cust_credit_limit*.15,’Not Available’)) “NEW CREDIT” FROM
26View the Exhibit and examine the structure of the PROMOTIONS table.
Which two SQL statements would execute successfully? (Choose two.)
- AUPDATE promotions
SET promo_cost = promo_cost+ 100
WHERE promo_end_date > TO_DATE(SUBSTR(’01-JAN-2000′,8)); - BSELECT promo_begin_date
FROM promotions
WHERE TO_CHAR(promo_begin_date,’mon dd yy’)=’jul 01 98′; - CUPDATE promotions
SET promo_cost = promo_cost+ 100
WHERE TO_CHAR(promo_end_date, ‘yyyy’) > ‘2000’; - DSELECT TO_CHAR(promo_begin_date,’dd/month’)
FROM promotions
WHERE promo_begin_date IN (TO_DATE(‘JUN 01 98’), TO_DATE(‘JUL 01 98’));
- AUPDATE promotions
27View the Exhibit and examine the structure of the EMPLOYEES table.
Examine the data in the ENAME and HIREDATE columns of the EMPLOYEES table:
ENAME HIREDATE
SMITH 17-DEC-80
ALLEN 20-FEB-81
WARD 22-FEB-81
You want to generate a list of user IDs as follows:
USERID
Smi17DEC80
All20FEB81
War22FEB81
You issue the following query:
SQL>SELECT CONCAT(SUBSTR(INITCAP(ename),1,3), REPLACE(hiredate,’-‘)) “USERID”
FROM employees;
What is the outcome?
- AIt executes successfully but does not give the correct output.
- BIt generates an error because the REPLACE function is not valid.
- CIt executes successfully and gives the correct output.
- DIt generates an error because the SUBSTR function cannot be nested in the CONCAT function.
28View the Exhibit and examine the structure of the CUSTOMERS table.
In the CUSTOMERS table, the CUST_LAST_NAME column contains the values ‘Anderson’ and ‘Ausson’.
You issue the following query:
SQL> SELECT LOWER(REPLACE(TRIM(‘son’ FROM cust_last_name),’An’,’O’))
FROM CUSTOMERS
WHERE LOWER(cust_last_name) LIKE ‘a%n’;
What would be the outcome?
- Aan error because the LOWER function specified is not valid
- B‘Oder’ and ‘Aus’
- Can error because the REPLACE function specified is not valid
- Dan error because the TRIM function specified is not valid
29View the Exhibit and examine the structure of the PRODUCTS table.
You want to display only those product names with their list prices where the list price is at least double the minimum price. The report should start with the product name having the maximum list price satisfying this condition.
Evaluate the following SQL statement:
SQL>SELECT prod_name,prod_list_price
FROM products
WHERE prod_list_price >= 2 * prod_min_price
Which ORDER BY clauses can be added to the above SQL statement to get the correct output?
(Choose all that apply.)
- AORDER BY prod_name DESC, prod_list_price DESC;
- BORDER BY (2*prod_min_price)DESC, prod_name;
- CORDER BY prod_list_price DESC, prod_name;
- DORDER BY prod_name, (2*prod_min_price)DESC;
- EORDER BY prod_list_price DESC, prod_name DESC;
30View the Exhibit and examine the data in the COSTS table.
You need to generate a report that displays the IDs of all products in the COSTS table whose unit price is at least 25% more than the unit cost. The details should be displayed in the descending order of 25% of the unit cost.
You issue the following query:
SQL>SELECT prod_id
FROM costs
WHERE unit_price >= unit_cost * 1.25
ORDER BY unit_cost * 0.25 DESC;
Which statement is true regarding the above query?
- AIt produces an error because an expression cannot be used in the ORDER BY clause.
- BIt produces an error because the DESC option cannot be used with an expression in the ORDER BY clause.
- CIt executes and produces the required result.
- DIt produces an error because the expression in the ORDER BY clause should also be specified in the SELECT clause.
31View 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?
- ASELECT cust_city, cust_income_level, cust_last_name
FROM customers
ORDER BY cust_income_level desc, cust_city DESC; - BSELECT cust_city, cust_income_level, cust_last_name
FROM customers
ORDER BY (cust_city, cust_income_level) DESC; - CSELECT cust_city, cust_income_level, cust_last_name
FROM customers
ORDER BY cust_city desc, cust_income_level DESC ; - DSELECT cust_city, cust_income_level, cust_last_name
FROM customers
ORDER BY cust_city, cust_income_level DESC;
- ASELECT cust_city, cust_income_level, cust_last_name
32View 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?
- ABoth queries execute successfully and give the same result.
- BOnly the second query executes successfully.
- CBoth queries execute successfully but give different results.
- DOnly the first query executes successfully.
33View the Exhibit and examine the structure of the CUSTOMERS table.
You want to generate a report showing the last names and credit limits of all customers whose last names start with A, B, or C, and credit limit is below 10, 000.
Evaluate the following two queries:
SQL> SELECT cust_last_name, cust_credit_limit FROM customers
WHERE (UPPER(cust_last_name) LIKE ‘A%’ OR
UPPER(cust_last_name) LIKE ‘B%’ OR UPPER(cust_last_name) LIKE ‘C%’)
AND cust_credit_limit < 10000;
SQL>SELECT cust_last_name, cust_credit_limit FROM customers
WHERE UPPER(cust_last_name) BETWEEN ‘A’ AND ‘C’
AND cust_credit_limit < 10000;
Which statement is true regarding the execution of the above queries?
- ABoth execute successfully but do not give the required result.
- BBoth execute successfully and give the same result.
- COnly the second query gives the correct result.
- DOnly the first query gives the correct result.
34Refer to the exhibit. Promotions Table
The promo name should not begin with ‘T’ or ‘N’.
The promo should cost more than $20000.
The promo should have ended after 1st January 2001.
Which WHERE clause would give the required result?
- AWHERE (promo_name NOT LIKE ‘%T%’ OR promo_name NOT LIKE ‘%N%’)
AND(promo_cost > 20000 AND promo_end_date > ‘1-JAN-01’) - BWHERE promo_name NOT LIKE ‘T%’ OR promo_name NOT LIKE ‘N%’ AND promo_cost >
20000 AND promo_end_date > ‘1-JAN-01’) - CWHERE (promo_name NOT LIKE ‘T%’ AND promo_name NOT LIKE ‘N%’)OR promo_cost
> 20000 OR promo_end_date > ‘1-JAN-01’) - DWHERE promo_name NOT LIKE ‘T%’ AND promo_name NOT LIKE ‘N%’ AND promo_cost
> 20000 AND promo_end_date > ‘1-JAN-01’)
- AWHERE (promo_name NOT LIKE ‘%T%’ OR promo_name NOT LIKE ‘%N%’)
35View the Exhibit and examine the structure of CUSTOMERS table.
Evaluate the following query:
SQL>SELECT cust_id, cust_city
FROM customers
WHERE cust_first_name NOT LIKE ‘A_%g_%’ AND
cust_credit_limit BETWEEN 5000 AND 15000 AND
cust_credit_limit NOT IN (7000, 11000) AND
cust_city NOT BETWEEN ‘A’ AND ‘B’;
Which statement is true regarding the above query?
- AIt executes successfully.
- BIt produces an error because the condition on the CUST_FIRST_NAME column is not valid.
- CIt produces an error because conditions on the CUST_CREDIT_LIMIT column are not valid.
- DIt produces an error because the condition on the CUST_CITY column is not valid.
36View the Exhibit and examine the data in the PRODUCTS table.
You need to display product names from the PRODUCTS table that belong to the ‘Software/Other ‘ category with minimum prices as either $2000 or $4000 and no unit of measure.
You issue the following query:
SQL>SELECT prod_name, prod_category, prod_min_price
FROM products
WHERE prod_category LIKE ‘%Other%’ AND (prod_min_price = 2000 OR
prod_min_price = 4000) AND prod_unit_of_measure <> ”;
Which statement is true regarding the above query?
- AIt generates an error because the condition specified for PROD_UNIT_OF_MEASURE is not valid.
- BIt generates an error because the condition specified for the PROD_CATEGORY column is not valid.
- CIt executes successfully and returns the required result.
- DIt executes successfully but returns no result.
37View the Exhibit and examine the structure of the PROMOTIONS table.
Using the PROMOTIONS table, you need to find out the names and cost of all the promos done on ‘TV’ and ‘internet’ that ended in the time interval 15th March ’00 to 15th October ’00.
Which two queries would give the required result? (Choose two.)
- ASELECT promo_name, promo_cost
FROM promotions
WHERE (promo_category BETWEEN ‘TV’ AND ‘internet’) AND
(promo_end_date IN (’15-MAR-00′,’15-OCT-00′)); - BSELECT promo_name, promo_cost
FROM promotions
WHERE promo_category IN (‘TV’, ‘internet’) AND
promo_end_date BETWEEN ’15-MAR-00′ AND ’15-OCT-00′; - CSELECT promo_name, promo_cost
FROM promotions
WHERE (promo_category = ‘TV’ OR promo_category =’internet’) AND
(promo_end_date >=’15-MAR-00′ AND promo_end_date <=’15-OCT-00′); - DSELECT promo_name, promo_cost
FROM promotions
WHERE promo_category = ‘TV’ OR promo_category =’internet’ AND
promo_end_date >=’15-MAR-00′ OR promo_end_date <=’15-OCT-00′;
- ASELECT promo_name, promo_cost
38View the Exhibit and examine the structure of the CUSTOMERS table.
Evaluate the query statement:
SQL> SELECT cust_last_name, cust_city, cust_credit_limit
FROM customers
WHERE cust_last_name BETWEEN ‘A’ AND ‘C’ AND cust_credit_limit BETWEEN
1000 AND 3000;
What would be the outcome of the above statement?
- AIt produces an error because the condition on CUST_LAST_NAME is invalid.
- BIt produces an error because the AND operator cannot be used to combine multiple BETWEEN clauses.
- CIt executes successfully only if the CUST_CREDIT_LIMIT column does not contain any null values.
- DIt executes successfully.
39View 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?
- ASELECT promo_name, promo_cost, promo_begin_date
FROM promotions
WHERE promo_category LIKE ‘P%’ AND promo_begin_date < ‘1-JANUARY-00’; - BSELECT promo_name, promo_cost, promo_begin_date
FROM promotions
WHERE promo_category = ‘post’ AND promo_begin_date < ’01-01-00′; - CSELECT promo_name, promo_cost, promo_begin_date
FROM promotions
WHERE promo_category LIKE ‘%post%’ AND promo_begin_date < ‘1-JAN-00’; - DSELECT promo_name, promo_cost, promo_begin_date
FROM promotions
WHERE promo_cost LIKE ‘post%’ AND promo_begin_date < ’01-01-2000′;
- ASELECT promo_name, promo_cost, promo_begin_date
40View the Exhibit and examine the structure of the PRODUCTS table.
Using the PRODUCTS table, you issue the following query to generate the names, current list price, and discounted list price for all those products whose list price falls below $10 after a discount of 25% is applied on it.
SQL>SELECT prod_name, prod_list_price,
prod_list_price – (prod_list_price * .25) “DISCOUNTED_PRICE”
FROM products
WHERE discounted_price < 10;
The query generates an error.
What is the reason for the error?
- AThe column alias should be put in uppercase and enclosed with in double quotation marks in the WHERE clause.
- BThe column alias should be replaced with the expression in the WHERE clause.
- CThe parenthesis should be added to enclose the entire expression.
- DThe double quotation marks should be removed from the column alias.
41View the Exhibit and examine the structure of ORD and ORD_ITEMS tables.
The ORD_NO column is PRIMARY KEY in the ORD table and the ORD_NO and ITEM_NO columns are composite PRIMARY KEY in the ORD_ITEMS table.
Which two CREATE INDEX statements are valid? (Choose two.)
- ACREATE INDEX ord_idx3
ON ord_items(item_no); - BCREATE INDEX ord_idx4
ON ord,ord_items(ord_no, ord_date,qty); - CCREATE INDEX ord_idx1
ON ord(ord_no); - DCREATE INDEX ord_idx2
ON ord_items(ord_no);
- ACREATE INDEX ord_idx3
42View the Exhibit and examine the structure of the ORD table.
Evaluate the following SQL statements that are executed in a user session in the specified order:
CREATE SEQUENCE ord_seq;
SELECT ord_seq.nextval
FROM dual;
INSERT INTO ord
VALUES (ord_seq.CURRVAL, ’25-jan-2007′,101);
UPDATE ord
SET ord_no= ord_seq.NEXTVAL
WHERE cust_id =101;
What would be the outcome of the above statements?
- AThe CREATE SEQUENCE command would not execute because the minimum value and maximum value for the sequence have not been specified.
- BAll the statements would execute successfully and the ORD_NO column would have the value 20 for the CUST_ID 101 because the default CACHE value is 20.
- CAll the statements would execute successfully and the ORD_NO column would contain the value 2 for the CUST_ID 101.
- DThe CREATE SEQUENCE command would not execute because the starting value of the sequence and the increment value have not been specified.
43View the Exhibit to examine the description for the SALES and PRODUCTS tables.
You want to create a SALE_PROD view by executing the following SQL statement:
CREATE VIEW sale_prod
AS SELECT p.prod_id, cust_id, SUM(quantity_sold) “Quantity” , SUM(prod_list_price) “Price”
FROM products p, sales s
WHERE p.prod_id=s.prod_id
GROUP BY p.prod_id, cust_id;
Which statement is true regarding the execution of the above statement?- AThe view will not be created because the join statements are not allowed for creating a view.
- BThe view will be created and you can perform DML operations on the view.
- CThe view will be created but no DML operations will be allowed on the view.
- DThe view will not be created because the GROUP BY clause is not allowed for creating a view.
44View the Exhibit and examine the structure of the PRODUCTS table.
You need to generate a report in the following format:
CATEGORIES
5MP Digital Photo Camera’s category is Photo
Y Box’s category is Electronics
Envoy Ambassador’s category is Hardware
Which two queries would give the required output? (Choose two.)
- ASELECT prod_name || q’\’s\’ || ‘ category is ‘ || prod_category
AS CATEGORIES
FROM products; - BSELECT prod_name || q”’s || ‘ category is ‘ || prod_category
AS CATEGORIES
FROM products; - CSELECT prod_name || q'<‘s >’ || ‘category is ‘ || prod_category
AS CATEGORIES
FROM products; - DSELECT prod_name || q'[‘s ]’ || category is ‘ || prod_category
AS CATEGORIES
FROM products;
- ASELECT prod_name || q’\’s\’ || ‘ category is ‘ || prod_category
45View the Exhibit and examine the structure of the PRODUCTS table.
All products have a list price.
You issue the following command to display the total price of each product after a discount of 25% and a tax of 15% are applied on it. Freight charges of $100 have to be applied to all the products.
SQL>SELECT prod_name, prod_list_price -(prod_list_price*(25/100))
+(prod_list_price -(prod_list_price*(25/100))*(15/100))+100
AS “TOTAL PRICE”
FROM products;
What would be the outcome if all the parentheses are removed from the above statement?
- AThe result remains unchanged.
- BIt produces a syntax error.
- CThe total price value would be lower than the correct value.
- DThe total price value would be higher than the correct value.
46View the Exhibit and examine the structure of the CUSTOMERS table.
Which two tasks would require subqueries or joins to be executed in a single statement? (Choose two.)
- Afinding the average credit limit of male customers residing in ‘Tokyo’ or ‘Sydney’
- Blisting of those customers whose credit limit is the same as the credit limit of customers residing in the city ‘Tokyo’
- Clisting of customers who do not have a credit limit and were born before 1980
- Dfinding the number of customers, in each city, whose marital status is ‘married’
- Efinding the number of customers, in each city, whose credit limit is more than the average credit limit of all the customers
47View the Exhibit to examine the description for the SALES table.
Which views can have all DML operations performed on it? (Choose all that apply.)
- ACREATE VIEW v1
AS SELECT * FROM SALES
WHERE time_id <= SYSDATE – 2*365
WITH CHECK OPTION; - BCREATE VIEW v2
AS SELECT prod_id, cust_id, time_id FROM SALES
WHERE time_id <= SYSDATE – 2*365
WITH CHECK OPTION; - CCREATE VIEW v3
AS SELECT * FROM SALES
WHERE cust_id = 2034
WITH CHECK OPTION; - DCREATE VIEW v4
AS SELECT prod_id, cust_id, SUM(quantity_sold) FROM SALES
WHERE time_id <= SYSDATE – 2*365
GROUP BY prod_id, cust_id
WITH CHECK OPTION;
- ACREATE VIEW v1
48View the Exhibit and examine the structure of the SALES, CUSTOMERS, PRODUCTS, and TIMES tables.
The PROD_ID column is the foreign key in the SALES table, which references the PRODUCTS table.
Similarly, the CUST_ID and TIME_ID columns are also foreign keys in the SALES table referencing the CUSTOMERS and TIMES tables, respectively.
Evaluate the following CREATE TABLE command:
CREATE TABLE new_sales(prod_id, cust_id, order_date DEFAULT SYSDATE)
AS
SELECT prod_id, cust_id, time_id
FROM sales;
Which statement is true regarding the above command?- AThe NEW_SALES table would not get created because the column names in the CREATE TABLE command and the SELECT clause do not match.
- BThe NEW_SALES table would not get created because the DEFAULT value cannot be specified in the column definition.
- CThe NEW_SALES table would get created and all the FOREIGN KEY constraints defined on the specified columns would be passed to the new table.
- DThe NEW_SALES table would get created and all the NOT NULL constraints defined on the specified columns would be passed to the new table.
49Which two statements are true regarding subqueries? (Choose two.)
- AA subquery can be used only in SQL query statements.
- BA subquery can retrieve zero or more rows.
- CA subquery can appear on either side of a comparison operator.
- DOnly two subqueries can be placed at one level.
- EThere is no limit on the number of subquery levels in the WHERE clause of a SELECT statement.
50Which two statements are true regarding savepoints? (Choose two.)
- ASavepoints may be used to ROLLBACK.
- BSavepoints are effective for both COMMIT and ROLLBACK.
- CSavepoints are effective only for COMMIT.
- DSavepoints can be used for only DML statements.
- ESavepoints can be used for both DML and DDL statements.
51Which statement is true regarding transactions? (Choose all that apply.)
- AA transaction consists of a set of DML or DCL statements.
- BA transaction can consist only of a set of DML and DDL statements.
- CA part or an entire transaction can be made permanent with a COMMIT.
- DA part or an entire transaction can be undone by using ROLLBACK command.
- EA transaction can consist of only a set of queries or DML or DDL statements.
52When does a transaction complete? (Choose all that apply.)
- Awhen a PL/SQL anonymous block is executed
- Bwhen a DELETE statement is executed
- Cwhen a ROLLBACK command is executed
- Dwhen a data definition language (DDL) statement is executed
- Ewhen a TRUNCATE statement is executed after the pending transaction
53Which two statements describe the consequences of issuing the ROLLBACK TO SAVE POINT a command in the session?
The SQL statements executed in a user session are as follows:
SQL>CREATE TABLE product
(pcode NUMBER(2),
pname VARCHAR2(10));
SQL>INSERT INTO product VALUES (1,‘pen’);
SQL>INSERT INTO product VALUES (2,’pencil’);
SQL>SAVEPOINT a;
SQL>UPDATE product SET pcode = 10 WHERE pcode = 1;
SQL>SAVEPOINT b;
SQL>DELETE FROM product WHERE pcode = 2;
SQL>COMMIT;
SQL>DELETE FROM product WHERE pcode=10;
Which two statements describe the consequences of issuing the ROLLBACK TO SAVE POINT a command
in the session? (Choose two.)- ANo SQL statements are rolled back.
- BOnly the DELETE statements are rolled back.
- CThe rollback generates an error.
- DOnly the second DELETE statement is rolled back.
- EBoth the DELETE statements and the UPDATE statement are rolled back.
54Which three statements/commands would cause a transaction to end? (Choose three.)
- ACREATE
- BROLLBACK
- CCOMMIT
- DSELECT
- ESAVEPOINT
55Which two statements are true regarding the DELETE and TRUNCATE commands? (Choose two.)
- ADELETE can be used only on a table that is a parent of a referential integrity constraint.
- BDELETE can be used to remove only rows from multiple tables at a time.
- CDELETE can be used to remove data from specific columns as well as complete rows.
- DDELETE can be used to remove only rows from only one table at a time.
- EDELETE and TRUNCATE can be used on a table that is a parent of a referential integrity constraint having ON DELETE rule .
56Which statement is true about the DELETE statement?
Evaluate the following DELETE statement:
DELETE FROM sales;
There are no other uncommitted transactions on the SALES table. Which statement is true about the DELETE statement?- AIt removes all the rows in the table and deleted rows can be rolled back.
- BIt would not remove the rows if the table has a primary key.
- CIt removes all the rows as well as the structure of the table.
- DIt removes all the rows in the table and deleted rows cannot be rolled back.
57Which statements are true regarding the FOR UPDATE clause in a SELECT statement? (Choose all that apply.)
- AIt can be used only in SELECT statements that are based on a single table.
- BIt locks the rows that satisfy the condition in the SELECT statement.
- CAfter it is enforced by a SELECT statement, no other query can access the same rows until a COMMIT or ROLLBACK is issued.
- DIt can be used in SELECT statements that are based on a single or multiple tables.
- EIt locks only the columns specified in the SELECT list.
58Evaluate 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 clauses are valid for the above query? (Choose all that apply.)- AORDER BY CUST_NO
- BORDER BY "Last Name"
- CORDER BY 2,1
- DORDER BY "CUST_NO"
- EORDER BY 2,cust_id
59Evaluate the following SQL statement:
SQL> SELECT promo_id, promo_category
FROM promotions
WHERE promo_category = ‘Internet’ ORDER BY 2 DESC
UNION
SELECT promo_id, promo_category
FROM promotions
WHERE promo_category = ‘TV’
UNION
SELECT promo_id, promo_category
FROM promotions
WHERE promo_category =’Radio’;
Which statement is true regarding the outcome of the above query?- AIt produces an error because positional notation cannot be used in the ORDER BY clause with SET operators.
- BIt executes successfully and displays rows in the descending order of PROMO_CATEGORY.
- CIt produces an error because the ORDER BY clause should appear only at the end of a compound query-that is, with the last SELECT statement.
- DIt executes successfully but ignores the ORDER BY clause because it is not located at the end of the compound statement.
60Which statement is true regarding the UNION operator?
- ANames of all columns must be identical across all SELECT statements.
- BThe number of columns selected in all SELECT statements need not be the same.
- CBy default, the output is not sorted.
- DNULL values are not ignored during duplicate checking.
61Evaluate the following SQL statement:
SQL> SELECT cust_id, cust_last_name
FROM customers
WHERE cust_credit_limit IN(select cust_credit_limit
FROM customers
WHERE cust_city =’Singapore’);
Which statement is true regarding the above query if one of the values generated by the subquery is NULL?- AIt ignores the NULL value and generates output for the values produced by the subquery.
- BIt generates output for NULL as well as the other values produced by the subquery.
- CIt executes but returns no rows.
- DIt produces an error.
62Examine the structure of the PRODUCTS table:
name Null Type
------------ -------- - ------------
PROD_ID NOT NULL NUMBER(4)
PROD_NAME VARCHAR2(20)
PROD_STATUS VARCHAR2(6)
QTY_IN_HAND NUMBER(8,2)
UNIT_PRICE NUMBER(10,2)
You want to display the names of the products that have the highest total value for UNIT_PRICE * TY_IN_HAND. Which SQL statement gives the required output?- ASELECT prod_name
FROM products
WHERE (unit_price*qty_in_hand) = (SELECT MAX(unit_price*qty_in_hand)
FROM products
GROUP BY prod_name); - BSELECT prod_name
FROM products
GROUP BY prod_name
HAVING MAX(unit_price*qty_in_hand) = (SELECT MAX(unit_price*qty_in_hand)
FROM products
GROUP BY prod_name); - CSELECT prod_name
FROM products
WHERE (unit_price*qty_in_hand) = (SELECT MAX(unit_price*qty_in_hand)
FROM products); - DSELECT prod_name
FROM products
WHERE (unit_price*qty_in_hand) = (SELECT
MAX(SUM(unit_price*qty_in_hand))
FROM products)
GROUP BY prod_name;
- ASELECT prod_name
63Which three statements are true about multiple-row subqueries? (Choose three.)
- AThey can return multiple columns as well as rows.
- BThey can contain group functions and the GROUP BY clause, but not the HAVING clause.
- CThey can contain a subquery within a subquery.
- DThey cannot contain a subquery within a subquery.
- EThey can return only one column but multiple rows.
- FThey can contain group functions and GROUP BY and HAVING clauses.
64Which statement is true regarding subqueries?
- AThe NOT IN operator is equivalent to IS NULL with single-row subqueries.
- BThe NOT operator can be used with IN, ANY, and ALL operators in multiple-row subqueries.
- CThe LIKE operator cannot be used with single-row subqueries.
- D=ANY and =ALL operators have the same functionality in multiple-row subqueries.
65Which three statements are true regarding subqueries? (Choose three.)
- ASubqueries can contain GROUP BY and ORDER BY clauses.
- BOnly one column or expression can be compared between the main query and subquery.
- CMain query and subquery must get data from the same tables.
- DMain query and subquery can get data from different tables.
- ESubqueries can contain ORDER BY but not the GROUP BY clause.
- FMultiple columns or expressions can be compared between the main query and subquery.
66Where can subqueries be used? (Choose all that apply.)
- Athe HAVING clause in the SELECT statement
- Bthe GROUP BY clause in the SELECT statement
- Cfield names in the SELECT statement
- Dthe WHERE clause in SELECT as well as all DML statements
- Ethe FROM clause in the SELECT statement
- Fthe WHERE clause in only the SELECT statement
67Examine the data in the CUSTOMERS table:
CUSTNO CUSTNAME CITY
--------------- ---------------- --------------
1 KING SEATTLE
2 GREEN BOSTON
3 KOCHAR SEATTLE
4 SMITH NEW YORK
You want to list all cities that have more than one customer along with the customer details.
Evaluate the following query:
SQL>SELECT c1.custname, c1.city
FROM Customers c1 __________________ Customers c2
ON (c1.city=c2.city AND c1.custname<>c2.custname);
Which two JOIN options can be used in the blank in the above query to give the correct output? (Choose two.)- ANATURAL JOIN
- BFULL OUTER JOIN
- CJOIN
- DLEFT OUTER JOIN
- ERIGHT OUTER JOIN
68Which two methods can you use to get the required result?
Examine the structure of the CUSTOMERS table:
name Null Type
------------------ -------- --------------
CUSTNO NOT NULL NUMBER(3)
CUSTNAME NOT NULL VARCHAR2(25)
CUSTADDRESS VARCHAR2(35)
CUST_CREDIT_LIMIT NUMBER(5)
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? (Choose two.)- Afull outer-join with self-join
- Bleft outer-join with self-join
- Cself-join
- Dright outer-join with self-join
- Esubquery
69Which two statements are true regarding the USING clause in table joins? (Choose two.)
- AIt can be used to join tables that have columns with the same name and compatible data types.
- BIt can be used to join a maximum of three tables.
- CIt can be used to access data from tables through equijoins as well as nonequijoins.
- DIt can be used to restrict the number of columns used in a NATURAL join.
70Examine the data in the ORD_ITEMS table:
ORD_NO ITEM_NO QTY
--------- --------- --------
1 111 10
1 222 20
1 333 30
2 333 30
2 444 40
3 111 40
Evaluate the following query:
SQL>SELECT item_no, AVG(qty)
FROM ord_items
HAVING AVG(qty) > MIN(qty) * 2
GROUP BY item_no;
Which statement is true regarding the outcome of the above query?- AIt displays the item nos with their average quantity where the average quantity is more than double the overall minimum quantity of all the items in the table.
- BIt gives an error because the HAVING clause should be specified after the GROUP BY clause.
- CIt displays the item nos with their average quantity where the average quantity is more than double the minimum quantity of that item in the table.
- DIt gives an error because all the aggregate functions used in the HAVING clause must be specified in the SELECT list.
71Which statements are true regarding the WHERE and HAVING clauses in a SELECT statement? (Choose all that apply.)
- AThe WHERE clause can be used to exclude rows after dividing them into groups.
- BThe aggregate functions and columns used in the HAVING clause must be specified in the SELECT list of the query.
- CThe HAVING clause can be used with aggregate functions in subqueries.
- DThe WHERE clause can be used to exclude rows before dividing them into groups.
- EThe WHERE and HAVING clauses can be used in the same statement only if they are applied to different columns in the table.
72You issue the following query:
SQL> SELECT AVG(MAX(qty))
FROM ord_items
GROUP BY item_no
HAVING AVG(MAX(qty))>50;
Which statement is true regarding the outcome of this query?- AIt executes successfully but does not give the correct output.
- BIt executes successfully and gives the correct output.
- CIt gives an error because the GROUP BY expression is not valid.
- DIt gives an error because the HAVING clause is not valid.
73Examine the structure of the ORDERS table:
Name Null Type
-------------- --------- -----------
ORDER_ID NOT NULL NUMBER(12)
ORDER_DATE NOT NULL TIMESTAMP(6)
CUSTOMER_ID NOT NULL NUMBER(6)
ORDER_STATUS NUMBER(2)
ORDER_TOTAL NUMBER(8,2)
You want to find the total value of all the orders for each year and issue the following command:
SQL> SELECT TO_CHAR(order_date,’rr’), SUM(order_total)
FROM orders
GROUP BY TO_CHAR(order_date,’yyyy’);
Which statement is true regarding the outcome?- AIt gives an error because the TO_CHAR function is not valid.
- BIt executes successfully and gives the correct output.
- CIt gives an error because the data type conversion in the SELECT list does not match the data type conversion in the GROUP BY clause.
- DIt executes successfully but does not give the correct output.
74Examine the structure of the MARKS table:
Name Null Type
------------------------ ----------------- -------------------------
STUDENT_ID NOT NULL VARCHAR2(4)
STUDENT_NAME VARCHAR2(25)
SUBJECT1 NUMBER(3)
SUBJECT2 NUMBER(3)
SUBJECT3 NUMBER(3)
Which two statements would execute successfully? (Choose two.)- ASELECT SUM(DISTINCT NVL(subject1,0)), MAX(subject1)
FROM marks
WHERE subject1 > subject2; - BSELECT student_name,subject1
FROM marks
WHERE subject1 > AVG(subject1); - CSELECT student_name,SUM(subject1)
FROM marks
WHERE student_name LIKE ‘R%’; - DSELECT SUM(subject1+subject2+subject3)
FROM marks
WHERE student_name IS NULL;
- ASELECT SUM(DISTINCT NVL(subject1,0)), MAX(subject1)
75Which two statements are true regarding the COUNT function? (Choose two.)
- ACOUNT(cust_id) returns the number of rows including rows with duplicate customer IDs and NULL value in the CUST_ID column.
- BThe COUNT function can be used only for CHAR, VARCHAR2, and NUMBER data types.
- CA SELECT statement using the COUNT function with a DISTINCT keyword cannot have a WHERE clause.
- DCOUNT(*) returns the number of rows including duplicate rows and rows containing NULL value in any of the columns.
- ECOUNT(DISTINCT inv_amt)returns the number of rows excluding rows containing duplicates and NULL values in the INV_AMT column.
76Examine the data in the LIST_PRICE and MIN_PRICE columns of the PRODUCTS table:
LIST_PRICE MIN_PRICE
--------------- -----------------
10000 8000
20000
30000 30000
Which two expressions give the same output? (Choose two.)- ACOALESCE(NVL2(list_price, list_price, min_price), 0)
- BNVL(NULLIF(list_price, min_price), 0)
- CNVL(COALESCE(list_price, min_price), 0)
- DNVL2(COALESCE(list_price, min_price), min_price, 0)
77Examine the structure of the PROMOS table:
Name Null Type
------------------------ ---------------- -----------------------
PROMO_ID NOT NULL NUMBER(3)
PROMO_NAME VARCHAR2(30)
PROMO_START_DATE NOT NULL DATE
PROMO_END_DATE NOT NULL DATE
You want to display the list of promo names with the message ‘Same Day’ for promos that started and ended on the same day. Which query gives the correct output?- ASELECT promo_name, DECODE((NULLIF(promo_start_date, promo_end_date)),
NULL,’Same day’)
FROM promos; - BSELECT promo_name, NVL(NULLIF(promo_start_date, promo_end_date), ‘Same Day’)
FROM promos; - CSELECT promo_name, NVL2(TO_CHAR(TRUNC(promo_end_date-promo_start_date)),
NULL,’Same Day’) - DSELECT promo_name, NVL(TRUNC(promo_end_date – promo_start_date), ‘Same Day’)
FROM promos;
- ASELECT promo_name, DECODE((NULLIF(promo_start_date, promo_end_date)),
78 Examine the structure of the PROMOS table:
Name Null Type
------------------------- ---------------- -----------------
PROMO_ID NOT NULL NUMBER(3)
PROMO_NAME VARCHAR2(30)
PROMO_START_DATE NOT NULL DATE
PROMO_END_DATE DATE
You want to generate a report showing promo names and their duration (number of days). If the PROMO_END_DATE has not been entered, the message ‘ONGOING’ should be displayed. Which queries give the correct output? (Choose all that apply.)- ASELECT promo_name, NVL(TO_CHAR(promo_end_date -promo_start_date),
’ONGOING’)
FROM promos; - BSELECT promo_name, TO_CHAR(NVL(promo_end_date -promo_start_date,
’ONGOING’))
FROM promos; - CSELECT promo_name, decode(coalesce(promo_end_date,promo_start_date),
null,’ONGOING’, promo_end_date – promo_start_date)
FROM promos; - DSELECT promo_name,COALESCE(TO_CHAR(promo_end_date – promo_start_date),
’ONGOING’)
FROM promos; - ESELECT promo_name, DECODE(promo_end_date -promo_start_date,
NULL,’ONGOING’, promo_end_date – promo_start_date)
FROM promos;
- ASELECT promo_name, NVL(TO_CHAR(promo_end_date -promo_start_date),
79Examine the structure of the TRANSACTIONS table:
Name Null Type
------------------------ ----------------- --------------------
TRANS_ID NOT NULL NUMBER(3)
CUST_NAME VARCHAR2(30)
TRANS_DATE DATE
TRANS_AMT NUMBER(10,2)
You want to display the transaction date and specify whether it is a weekday or weekend.
Evaluate the following two queries:
SQL>SELECT TRANS_DATE, CASE
WHEN TRIM(TO_CHAR(trans_date,’DAY’)) IN (‘SATURDAY’,’SUNDAY’) THEN ‘weekend’
ELSE ‘weekday’
END "Day Type"
FROM transactions;
SQL>SELECT TRANS_DATE, CASE
WHEN TO_CHAR(trans_date,’DAY’) BETWEEN ‘MONDAY’ AND ‘FRIDAY’ THEN ‘weekday’
ELSE ‘weekend’
END "Day Type"
FROM transactions;
Which statement is true regarding the above queries?- ABoth give wrong results.
- BBoth give the correct result.
- COnly the first query gives the correct result.
- DOnly the second query gives the correct result.
80Examine the structure of the TRANSACTIONS table:
Name Null Type
------------------------ --------------- -------------------------
TRANS_ID NOT NULL NUMBER(3)
CUST_NAME VARCHAR2(30)
TRANS_DATE TIMESTAMP
TRANS_AMT NUMBER(10,2)
You want to display the date, time, and transaction amount of transactions that where done before 12 noon. The value zero should be displayed for transactions where the transaction amount has not been entered. Which query gives the required result?- ASELECT TO_CHAR(trans_date,’dd-mon-yyyy hh24:mi:ss’),
COALESCE(TO_NUMBER(trans_amt,’$99999999.99′),0)
FROM transactions
WHERE TO_DATE(trans_date,’hh24′) < 12; - BSELECT TO_CHAR(trans_date,’dd-mon-yyyy hh24:mi:ss’),
TO_CHAR(trans_amt,’$99999999D99′)
FROM transactions
WHERE TO_NUMBER(TO_DATE(trans_date,’hh24′)) < 12
AND COALESCE(trans_amt,NULL)<>NULL; - CSELECT TO_DATE (trans_date,’dd-mon-yyyy hh24:mi:ss’),
NVL2(trans_amt,TO_NUMBER(trans_amt,’$99999999.99′), 0)
FROM transactions
WHERE TO_DATE(trans_date,’hh24′) < 12; - DSELECT TO_CHAR(trans_date,’dd-mon-yyyy hh24:mi:ss’),
NVL(TO_CHAR(trans_amt,’$99999999D99′),0)
FROM transactions
WHERE TO_CHAR(trans_date,’hh24′) < 12;
- ASELECT TO_CHAR(trans_date,’dd-mon-yyyy hh24:mi:ss’),
81Examine the data in the PROMO_BEGIN_DATE column of the PROMOTIONS table:
PROMO_BEGIN _DATE
---------------------------
04-jan-00
10-jan-00
15-dec-99
18-oct-98
22-aug-99
You want to display the number of promotions started in 1999 and 2000.
Which query gives the correct output?- ASELECT SUM(CASE TO_CHAR(promo_begin_date,’yyyy’)
WHEN ’99’ THEN 1 ELSE 0 END) "1999",
SUM(CASE TO_CHAR(promo_begin_date,’yyyy’)
WHEN ’00’ THEN 1 ELSE 0 END) "2000"
FROM promotions; - BSELECT COUNT(CASE TO_CHAR(promo_begin_date,’yyyy’)
WHEN ’99’ THEN 1 ELSE 0 END) "1999",
COUNT(CASE TO_CHAR(promo_begin_date,’yyyy’)
WHEN ’00’ THEN 1 ELSE 0 END) "2000"
FROM promotions; - CSELECT SUM(DECODE(SUBSTR(promo_begin_date,8),’00’,1,0)) "2000",
SUM(DECODE(SUBSTR(promo_begin_date,8),’99’,1,0)) "1999"
FROM promotions; - DSELECT COUNT(DECODE(SUBSTR(TO_CHAR(promo_begin_date,’yyyy’), 8),
‘1999’, 1, 0)) "1999",
COUNT(DECODE(SUBSTR(TO_CHAR(promo_begin_date,’yyyy’), 8),’2000′,
1, 0)) "2000"
FROM promotions;
- ASELECT SUM(CASE TO_CHAR(promo_begin_date,’yyyy’)
82Which statement is true regarding the COALESCE function?
- AIt requires that at least one of the expressions in the list must have a NOT NULL value.
- BIt returns the highest NOT NULL value in the list for all rows.
- CIt can have a maximum of five expressions in a list.
- DIt requires that all expressions in the list must be of the same data type.
83 Examine the structure of the INVOICE table.
Name Null Type
---------------- ---------------- ---------------------
INV_NO NOT NULL NUMBER(3)
INV_DATE DATE
INV_AMT NUMBER(10,2)
Which two SQL statements would execute successfully? (Choose two.)- ASELECT inv_no,NVL2(inv_amt,inv_date,’Not Available’)
FROM invoice; - BSELECT inv_no,NVL2(inv_date,’Pending’,’Incomplete’)
FROM invoice; - CSELECT inv_no,NVL2(inv_date,sysdate-inv_date,sysdate)
FROM invoice; - DSELECT inv_no,NVL2(inv_amt,inv_amt*.25,’Not Available’)
FROM invoice;
- ASELECT inv_no,NVL2(inv_amt,inv_date,’Not Available’)
84The PRODUCTS table has the following structure:
Name Null Type
-------------------------- ---------------- ------------------------
PROD_ID NOT NULL NUMBER(4)
PROD_NAME VARCHAR2(25)
PROD_EXPIRY_DATE DATE
Evaluate the following two SQL statements:
SQL>SELECT prod_id, NVL2(prod_expiry_date, prod_expiry_date + 15,”)
FROM products;
SQL>SELECT prod_id, NVL(prod_expiry_date, prod_expiry_date + 15)
FROM products;
Which statement is true regarding the outcome?- AOnly the first SQL statement executes successfully.
- BOnly the second SQL statement executes successfully.
- CBoth the statements execute and give the same result.
- DBoth the statements execute and give different results.
85 Examine the structure of the PROGRAMS table:
Name Null Type
--------------- ---------------- ------------------
PROG_ID NOT NULL NUMBER(3)
PROG_COST NUMBER(8,2)
START_DATE NOT NULL DATE
END_DATE DATE
Which two SQL statements would execute successfully? (Choose two.)- ASELECT NVL(MONTHS_BETWEEN(start_date,end_date),’Ongoing’)
FROM programs; - BSELECT NVL(ADD_MONTHS(END_DATE,1),SYSDATE)
FROM programs; - CSELECT NVL(TO_CHAR(MONTHS_BETWEEN (start_date,end_date)),
’Ongoing’)
FROM programs; - DSELECT TO_DATE(NVL(SYSDATE-END_DATE,SYSDATE))
FROM programs;
- ASELECT NVL(MONTHS_BETWEEN(start_date,end_date),’Ongoing’)
86Examine the structure and data in the PRICE_LIST table:
Name Null Type
-------------- -------------- -----------
PROD_ID NOT NULL NUMBER(3)
PROD_PRICE VARCHAR2(10)
PROD_ID PROD_PRICE
--------- ---------------
100 $234.55
101 $6,509.75
102 $1,234
You plan to give a discount of 25% on the product price and need to display the discount amount in the same format as the PROD_PRICE. Which SQL statement would give the required result?- ASELECT TO_NUMBER(TO_NUMBER(prod_price,’$99,999.99′)* .25,’$99,999.00′)
FROM PRICE_LIST; - BSELECT TO_CHAR(TO_NUMBER(prod_price,’$99,999.99′)* .25,’$99,999.00′)
FROM PRICE_LIST; - CSELECT TO_CHAR(TO_NUMBER(prod_price)* .25,’$99,999.00′)
FROM PRICE_LIST; - DSELECT TO_CHAR(prod_price* .25,’$99,999.99′)
FROM PRICE_LIST;
- ASELECT TO_NUMBER(TO_NUMBER(prod_price,’$99,999.99′)* .25,’$99,999.00′)
87You need to display the date 11-oct-2007 in words as ‘Eleventh of October, Two Thousand Seven’.
Which SQL statement would give the required result?- ASELECT TO_DATE(TO_CHAR(’11-oct-2007′,’fmDdspth ”of” Month, Year’))
FROM DUAL; - BSELECT TO_CHAR(’11-oct-2007′, ‘fmDdspth "of" Month, Year’)
FROM DUAL; - CSELECT TO_CHAR(TO_DATE(’11-oct-2007′), ‘fmDdspth of month, year’)
FROM DUAL; - DSELECT TO_CHAR(TO_DATE(’11-oct-2007′), ‘fmDdthsp "of" Month, Year’)
FROM DUAL;
- ASELECT TO_DATE(TO_CHAR(’11-oct-2007′,’fmDdspth ”of” Month, Year’))
88Which SQL statements would give the required output?
You need to calculate the number of days from 1st January 2007 till date. Dates are stored in the default format of dd-mon-rr.
Which SQL statements would give the required output? (Choose two.)- ASELECT TO_CHAR(SYSDATE, ‘DD-MON-YYYY’) – ’01-JAN-2007′
FROM DUAL; - BSELECT SYSDATE – ’01-JAN-2007′
FROM DUAL; - CSELECT SYSDATE – TO_DATE(’01/JANUARY/2007′)
FROM DUAL; - DSELECT TO_DATE(SYSDATE, ‘DD/MONTH/YYYY’) – ’01/JANUARY/2007′
FROM DUAL; - ESELECT SYSDATE – TO_DATE(’01-JANUARY-2007′)
FROM DUAL;
- ASELECT TO_CHAR(SYSDATE, ‘DD-MON-YYYY’) – ’01-JAN-2007′
89You want to display the date for the first Mon day of the next month and issue the following command:
SQL>SELECT TO_CHAR(NEXT_DAY(LAST_DAY(SYSDATE),’MON’), ‘dd "is the first Monday for" fmmonth rrrr’)
FROM DUAL;
What is the outcome?- AIt executes successfully but does not return the correct result.
- BIt executes successfully and returns the correct result.
- CIt generates an error because rrrr should be replaced by rr in the format string.
- DIt generates an error because TO_CHAR should be replaced with TO_DATE.
- EIt generates an error because fm and double quotation marks should not be used in the format string.
90Examine the structure and data of the CUST_TRANS table:
CUST_TRANS
Name Null Type
------------------------ --------------- --------------------
CUSTNO NOT NULL CHAR(2)
TRANSDATE DATE
TRANSAMT NUMBER(6,2)
CUSTNO TRANSDATE TRANSAMT
------------ ---------- -------------
11 01-JAN-07 1000
22 01-FEB-07 2000
33 01-MAR-07 3000
Dates are stored in the default date format dd-mon-rr in the CUST_TRANS table.
Which SQL statements would execute successfully? (Choose three.)- ASELECT * FROM cust_trans WHERE transdate = ’01-01-07′;
- BSELECT transdate + ’10’ FROM cust_trans;
- CSELECT * FROM cust_trans WHERE transdate=’01-JANUARY-07′;
- DSELECT transamt FROM cust_trans WHERE custno > ’11’;
- ESELECT custno + ‘A’ FROM cust_trans WHERE transamt > 2000;
91Examine the data in the CUST_NAME column of the CUSTOMERS table.
CUST_NAME
----------------------
Lex De Haan
Renske Ladwig
Jose Manuel Urman
Jason Mallin
You want to extract only those customer names that have three names and display the * symbol in place of the first name as follows:
CUST NAME
---------------------------
*** De Haan
**** Manuel Urman
Which two queries give the required output? (Choose two.)- ASELECT LPAD(SUBSTR(cust_name,INSTR(cust_name,’ ‘)),
LENGTH(cust_name)- INSTR(cust_name,”),’*’) "CUST NAME"
FROM customers
WHERE INSTR(cust_name, ‘ ‘,-1,-2)<>0; - BSELECT LPAD(SUBSTR(cust_name,INSTR(cust_name,’ ‘)),
LENGTH(cust_name), ’*’) "CUST NAME"
FROM customers
WHERE INSTR(cust_name, ‘ ‘,1,2)<>0; - CSELECT LPAD(SUBSTR(cust_name,INSTR(cust_name,’ ‘)),
LENGTH(cust_name)- INSTR(cust_name,’ ‘),’*’) "CUST NAME"
FROM customers
WHERE INSTR(cust_name, ‘ ‘,1,2)<>0 ; - DSELECT LPAD(SUBSTR(cust_name,INSTR(cust_name,’ ‘)),
LENGTH(cust_name), ’*’) "CUST NAME"
FROM customers
WHERE INSTR(cust_name, ‘ ‘,-1,2)<>0;
- ASELECT LPAD(SUBSTR(cust_name,INSTR(cust_name,’ ‘)),
92Examine the data in the CUST_NAME column of the CUSTOMERS table.
CUST_NAME
------------------------
Renske Ladwig
Jason Mallin
Samuel McCain
Allan MCEwen
Irene Mikkilineni
Julia Nayer
You need to display customers’ second names where the second name starts with "Mc" or "MC."
Which query gives the required output?- ASELECT SUBSTR(cust_name, INSTR(cust_name,’ ‘)+1)
FROM customers
WHERE INITCAP(SUBSTR(cust_name, INSTR(cust_name,’ ‘)+1))=’Mc’; - BSELECT SUBSTR(cust_name, INSTR(cust_name,’ ‘)+1)
FROM customers
WHERE SUBSTR(cust_name, INSTR(cust_name,’ ‘)+1) LIKE INITCAP(‘MC%’); - CSELECT SUBSTR(cust_name, INSTR(cust_name,’ ‘)+1)
FROM customers
WHERE INITCAP(SUBSTR(cust_name, INSTR(cust_name,’ ‘)+1)) =
INITCAP(‘MC%’); - DSELECT SUBSTR(cust_name, INSTR(cust_name,’ ‘)+1)
FROM customers
WHERE INITCAP(SUBSTR(cust_name, INSTR(cust_name,’ ‘)+1)) LIKE ‘Mc%’;
- ASELECT SUBSTR(cust_name, INSTR(cust_name,’ ‘)+1)
93You are currently located in Singapore and have connected to a remote database in Chicago. You issue the following command:
SQL> SELECT ROUND(SYSDATE-promo_begin_date,0)
FROM promotions
WHERE (SYSDATE - promo_begin_date)/365 > 2;
PROMOTIONS is the public synonym for the public database link for the PROMOTIONS table. What is the outcome?- Aan error because the ROUND function specified is invalid
- Ban error because the WHERE condition specified is invalid
- Cnumber of days since the promo started based on the current Singapore date and time
- Dnumber of days since the promo started based on the current Chicago date and time
94Which two statements are true regarding working with dates? (Choose two.)
- AThe default internal storage of dates is in the character format.
- BThe default internal storage of dates is in the numeric format.
- CThe RR date format automatically calculates the century from the SYSDATE function but allows the user to enter the century if required.
- DThe RR date format automatically calculates the century from the SYSDATE function and does not allow the user to enter the century.
95Evaluate the following query:
SQL> SELECT TRUNC(ROUND(156.00,-1),-1)
FROM DUAL;
What would be the outcome?- A16
- B100
- C160
- D200
- E150
96In the CUSTOMERS table, the CUST_CITY column contains the value ‘Paris’ for the
CUST_FIRST_NAME ‘ABIGAIL’.
Evaluate the following query:
SQL> SELECT INITCAP(cust_first_name || ‘ ‘ || UPPER(SUBSTR(cust_city,- LENGTH(cust_city),2)))
FROM customers
WHERE cust_first_name = ‘ABIGAIL’;
What would be the outcome?- AAbigail IS
- BAbigail PA
- CAbigail Pa
- Dan error message
97You need to display the first names of all customers from the CUSTOMERS table that contain the character ‘e’ and have the character ‘a’ in the second last position. Which query would give the required output?
- ASELECT cust_first_name
FROM customers
WHERE INSTR(cust_first_name, ‘e’)IS NOT NULL - BSELECT cust_first_name
FROM customers
WHERE INSTR(cust_first_name, ‘e’)<>0
AND SUBSTR(cust_first_name, LENGTH(cust_first_name),-2)=’a’; - CSELECT cust_first_name
FROM customers
WHERE INSTR(cust_first_name, ‘e’)<>”
AND SUBSTR(cust_first_name, -2, 1)=’a’; - DSELECT cust_first_name
FROM customers
WHERE INSTR(cust_first_name, ‘e’)<>0
AND SUBSTR(cust_first_name, -2, 1)=’a’;
- ASELECT cust_first_name
98The following data exists in the PRODUCTS table:
PROD_ID PROD_LIST_PRICE
123456 152525.99
You issue the following query:
SQL> SELECT RPAD(( ROUND(prod_list_price)), 10,’*’)
FROM products
WHERE prod_id = 123456;
What would be the outcome?- A152526 ****
- B**152525.99
- C152525** **
- Dan error message
99Which statements are true regarding single row functions? (Choose all that apply.)
- AMOD : returns the quotient of a division
- BTRUNC : can be used with NUMBER and DATE values
- CCONCAT : can be used to combine any number of values
- DSYSDATE : returns the database server current date and time
- EINSTR : can be used to find only the first occurrence of a character in a string
- FTRIM : can be used to remove all the occurrences of a character from a string
100Which tasks can be performed using SQL functions that are built into Oracle database? (Choose three.)
- Afinding the remainder of a division
- Badding a number to a date for a resultant date value
- Ccomparing two expressions to check whether they are equal
- Dchecking whether a specified character exists in a given string
- Eremoving trailing, leading, and embedded characters from a character string
101Which tasks can be performed using SQL functions built into Oracle Database?
(Choose three.)- Adisplaying a date in a nondefault format
- Bfinding the number of characters in an expression
- Csubstituting a character string in a text expression with a specified string
- Dcombining more than two columns or expressions into a single column in the output
102Which arithmetic operations can be performed on a column by using a SQL function that is built into Oracle database ? (Choose three.)
- Aaddition
- Bsubtraction
- Craising to a power
- Dfinding the quotient
- Efinding the lowest value
103You need to generate a list of all customer last names with their credit limits from the CUSTOMERS table. Those customers who do not have a credit limit should appear last in the list.
Which two queries would achieve the required result? (Choose two.)- ASELECT cust_last_name, cust_credit_limit
FROM customers
ORDER BY cust_credit_limit DESC ; - BSELECT cust_last_name, cust_credit_limit
FROM customers
ORDER BY cust_credit_limit; - CSELECT cust_last_name, cust_credit_limit
FROM customers
ORDER BY cust_credit_limit NULLS LAST; - DSELECT cust_last_name, cust_credit_limit
FROM customers
ORDER BY cust_last_name, cust_credit_limit NULLS LAST;
- ASELECT cust_last_name, cust_credit_limit
104Which statement is true regarding the default behavior of the ORDER BY clause?
- AIn a character sort, the values are case- sensitive.
- BNULL values are not considered at all by the sort operation.
- COnly those columns that are specified in the SELECT list can be used in the ORDER BY clause.
- DNumeric values are displayed from the maximum to the minimum value if they have decimal positions.
105Which two statements are true regarding the ORDER BY clause? (Choose two.)
- AIt is executed first in the query execution.
- BIt must be the last clause in the SELECT statement.
- CIt cannot be used in a SELECT statement containing a HAVING clause.
- DYou cannot specify a column name followed by an expression in this clause.
- EYou can specify a combination of numeric positions and column names in this clause.
106The PART_CODE column in the SPARES table contains the following list of values:
PART_CODE
A%_WQ123
A%BWQ123
AB_WQ123
Evaluate the following query:
SQL> SELECT part_code
FROM spares
WHERE part_code LIKE ‘%\%_WQ12%’ ESCAPE ‘\’ ;
Which statement is true regarding the outcome of the above query?- AIt produces an error.
- BIt displays all values.
- CIt displays only the values A%_WQ123 and AB_WQ123.
- DIt displays only the values A%_WQ123 and A%BWQ123 .
- EIt displays only the values A%BWQ123 and AB_WQ123.
107Customers whose tax amount is null should not be considered.
Which statement accomplishes all the required tasks?
Tax amount equals cust_credit_limit *0.05- ASELECT cust_first_name, cust_credit_limit * .05 AS TAX_AMOUNT FROM customers
WHERE cust_income_level IS NOT NULL AND
tax_amount IS NOT NULL; - BSELECT cust_first_name, cust_credit_limit * .05 AS TAX_AMOUNT FROM customers
WHERE cust_income_level IS NOT NULL AND
cust_credit_limit IS NOT NULL; - CSELECT cust_first_name, cust_credit_limit * .05 AS TAX_AMOUNT FROM customers
WHERE cust_income_level <> NULL AND
tax_amount <> NULL; - DSELECT cust_first_name, cust_credit_limit * .05 AS TAX_AMOUNT FROM customers
WHERE (cust_income_level,tax_amount) IS NOT NULL;
- ASELECT cust_first_name, cust_credit_limit * .05 AS TAX_AMOUNT FROM customers
108Evaluate the following two queries:
SQL> SELECT cust_last_name, cust_city
FROM customers
WHERE cust_credit_limit IN (1000, 2000, 3000);
SQL> SELECT cust_last_name, cust_city
FROM customers
WHERE cust_credit_limit = 1000 OR cust_credit_limit = 2000 OR
cust_credit_limit = 3000;
Which statement is true regarding the above two queries?- APerformance would improve in query 2.
- BPerformance would degrade in query 2.
- CThere would be no change in performance.
- DPerformance would improve in query 2 only if there are null values in the CUST_CREDIT_LIMIT column.
109Which statement is true regarding synonyms?
- ASynonyms can be created only for a table.
- BSynonyms are used to reference only those tables that are owned by another user.
- CA public synonym and a private synonym can exist with the same name for the same table.
- DThe DROP SYNONYM statement removes the synonym, and the table on which the synonym has been created becomes invalid.
110SLS is a private synonym for the SH.SALES table.
The user SH issues the following command:
DROP SYNONYM sls;
Which statement is true regarding the above SQL statement?- AOnly the synonym would be dropped.
- BThe synonym would be dropped and the corresponding table would become invalid.
- CThe synonym would be dropped and the packages referring to the synonym would be dropped.
- DThe synonym would be dropped and any PUBLIC synonym with the same name becomes invalid.
111The ORDERS table belongs to the user OE. OE has granted the SELECT privilege on the ORDERS
table to the user HR.
Which statement would create a synonym ORD so that HR can execute the following query successfully?
SELECT * FROM ord;- ACREATE SYNONYM ord FOR orders; This command is issued by OE.
- BCREATE PUBLIC SYNONYM ord FOR orders; This command is issued by OE.
- CCREATE SYNONYM ord FOR oe.orders; This command is issued by the database administrator.
- DCREATE PUBLIC SYNONYM ord FOR oe.orders; This command is issued by the database administrator.
112Which two statements are true regarding indexes? (Choose two.)
- AThey can be created on tables and clusters.
- BThey can be created on tables and simple views.
- CYou can create only one index by using the same columns.
- DYou can create more than one index by using the same columns if you specify distinctly different combinations of the columns.
113Which statements are correct regarding indexes? (Choose all that apply.)
- AWhen a table is dropped, the corresponding indexes are automatically dropped.
- BA FOREIGN KEY constraint on a column in a table automatically creates a nonunique index.
- CA nondeferrable PRIMARY KEY or UNIQUE KEY constraint in a table automatically creates a unique index.
- DFor each data manipulation language (DML) operation performed, the corresponding indexes are automatically updated.
114Which two statements are true about sequences created in a single instance database? (Choose two.)
- AThe numbers generated by a sequence can be used only for one table.
- BDELETE <sequencename> would remove a sequence from the database.
- CCURRVAL is used to refer to the last sequence number that has been generated.
- DWhen the MAXVALUE limit for a sequence is reached, you can increase the MAXVALUE limit by using the ALTER SEQUENCE statement.
- EWhen a database instance shuts down abnormally, the sequence numbers that have been cached but not used would be available once again when the database instance is restarted.
115Evaluate the following CREATE SEQUENCE statement:
CREATE SEQUENCE seq1
START WITH 100
INCREMENT BY 10
MAXVALUE 200
CYCLE
NOCACHE;
The SEQ1 sequence has generated numbers up to the maximum limit of 200. You issue the following SQL
statement:
SELECT seq1.nextval FROM dual;
What is displayed by the SELECT statement?- A1
- B10
- C100
- Dan error
116Which three statements are true regarding views? (Choose three.)
- AViews can be created only from tables.
- BViews can be created from tables or other views.
- COnly simple views can use indexes existing on the underlying tables.
- DBoth simple and complex views can use indexes existing on the underlying tables.
- EComplex views can be created only on multiple tables that exist in the same schema.
- FComplex views can be created on multiple tables that exist in the same or different schemas.
117Which two statements are true regarding views? (Choose two.)
- AA subquery that defines a view cannot include the GROUP BY clause.
- BA view that is created with the subquery having the DISTINCT keyword can be updated.
- CA view that is created with the subquery having the pseudo column ROWNUM keyword cannot be updated.
- DA data manipulation language (DML) operation can be performed on a view that is created with the subquery having all the NOT NULL columns of a table.
118Evaluate the following command:
CREATE TABLE employees
(employee_id NUMBER(2) PRIMARY KEY,
last_name VARCHAR2(25) NOT NULL,
department_id NUMBER(2)NOT NULL,
job_id VARCHAR2(8),
salary NUMBER(10,2));
You issue the following command to create a view that displays the IDs and last names of the sales staff in the organization:
CREATE OR REPLACE VIEW sales_staff_vu AS
SELECT employee_id, last_name,job_id
FROM employees
WHERE job_id LIKE ‘SA_%’
WITH CHECK OPTION;
Which two statements are true regarding the above view? (Choose two.)- AIt allows you to insert rows into the EMPLOYEES table .
- BIt allows you to delete details of the existing sales staff from the EMPLOYEES table.
- CIt allows you to update job IDs of the existing sales staff to any other job ID in the EMPLOYEES table.
- DIt allows you to insert IDs, last names, and job IDs of the sales staff from the view if it is used in multitable INSERT statements.
119Which two statements are true regarding views? (Choose two.)
- AA simple view in which column aliases have been used cannot be updated.
- BRows cannot be deleted through a view if the view definition contains the DISTINCT keyword.
- CRows added through a view are deleted from the table automatically when the view is dropped.
- DThe OR REPLACE option is used to change the definition of an existing view without dropping and re-creating it.
- EThe WITH CHECK OPTION constraint can be used in a view definition to restrict the columns displayed through the view.
120You issued the following command to drop the PRODUCTS table:
SQL> DROP TABLE products;
What is the implication of this command? (Choose all that apply.)- AAll data along with the table structure is deleted.
- BThe pending transaction in the session is committed.
- CAll indexes on the table will remain but they are invalidated.
- DAll views and synonyms will remain but they are invalidated.
- EAll data in the table are deleted but the table structure will remain.
121Examine the following SQL commands:
SQL>CREATE TABLE products (
prod_id NUMBER(3) CONSTRAINT p_ck CHECK (prod_id > 0),
prod_name CHAR(30),
prod_qty NUMBER(6),
CONSTRAINT p_name NOT NULL,
CONSTRAINT prod_pk PRIMARY KEY (prod_id));
SQL>CREATE TABLE warehouse (
warehouse_id NUMBER(4),
roomno NUMBER(10) CONSTRAINT r_id CHECK(roomno BETWEEN 101 AND 200),
location VARCHAR2(25),
prod_id NUMBER(3),
CONSTRAINT wr_pr_pk PRIMARY KEY (warehouse_id,prod_id),
CONSTRAINT prod_fk FOREIGN KEY (prod_id) REFERENCES products(prod_id));
Which statement is true regarding the execution of the above SQL commands?- ABoth commands execute successfully.
- BThe first CREATE TABLE command generates an error because the NULL constraint is not valid.
- CThe second CREATE TABLE command generates an error because the CHECK constraint is not valid.
- DThe first CREATE TABLE command generates an error because CHECK and PRIMARY KEY constraints cannot be used for the same column.
- EThe first CREATE TABLE command generates an error because the column PROD_ID cannot be used in the PRIMARY KEY and FOREIGN KEY constraints.
122Which two statements are true regarding constraints? (Choose two.)
- AA table can have only one primary key and one foreign key.
- BA table can have only one primary key but multiple foreign keys.
- COnly the primary key can be defined at the column and table levels.
- DThe foreign key and parent table primary key must have the same name.
- EBoth primary key and foreign key constraints can be defined at both column and table levels.
123You created an ORDERS table with the following description:
name Null Type
ORD_ID NOT NULL NUMBER(2)
CUST_ID NOT NULL NUMBER(3)
ORD_DATE NOT NULL DATE
ORD_AMOUNT NOT NULL NUMBER (10,2)
You inserted some rows in the table. After some time, you want to alter the table by creating the PRIMARY KEY constraint on the ORD_ID column. Which statement is true in this scenario?- AYou cannot have two constraints on one column.
- BYou cannot add a primary key constraint if data exists in the column.
- CThe primary key constraint can be created only at the time of table creation.
- DYou can add the primary key constraint even if data exists, provided that there are no duplicate values.
124You want to create an ORD_DETAIL table to store details for an order placed having the following business requirement:
1) The order ID will be unique and cannot have null values.
2) The order date cannot have null values and the default should be the current date.
3) The order amount should not be less than 50.
4) The order status will have values either shipped or not shipped.
5) The order payment mode should be cheque, credit card, or cash on delivery (COD).
Which is the valid DDL statement for creating the ORD_DETAIL table?- ACREATE TABLE ord_details
(ord_id NUMBER(2) CONSTRAINT ord_id_nn NOT NULL,
ord_date DATE DEFAULT SYSDATE NOT NULL,
ord_amount NUMBER(5, 2) CONSTRAINT ord_amount_min
CHECK (ord_amount > 50),
ord_status VARCHAR2(15) CONSTRAINT ord_status_chk
CHECK (ord_status IN (‘Shipped’, ‘Not Shipped’)),
ord_pay_mode VARCHAR2(15) CONSTRAINT ord_pay_chk
CHECK (ord_pay_mode IN (‘Cheque’, ‘Credit Card’,
‘Cash On Delivery’))); - BCREATE TABLE ord_details
(ord_id NUMBER(2) CONSTRAINT ord_id_uk UNIQUE NOT NULL,
ord_date DATE DEFAULT SYSDATE NOT NULL,
ord_amount NUMBER(5, 2) CONSTRAINT ord_amount_min
CHECK (ord_amount > 50),
ord_status VARCHAR2(15) CONSTRAINT ord_status_chk
CHECK (ord_status IN (‘Shipped’, ‘Not Shipped’)),
ord_pay_mode VARCHAR2(15) CONSTRAINT ord_pay_chk
CHECK (ord_pay_mode IN (‘Cheque’, ‘Credit Card’,
‘Cash On Delivery’))); - CCREATE TABLE ord_details
(ord_id NUMBER(2) CONSTRAINT ord_id_pk PRIMARY KEY,
ord_date DATE DEFAULT SYSDATE NOT NULL,
ord_amount NUMBER(5, 2) CONSTRAINT ord_amount_min
CHECK (ord_amount >= 50),
ord_status VARCHAR2(15) CONSTRAINT ord_status_chk
CHECK (ord_status IN (‘Shipped’, ‘Not Shipped’)),
ord_pay_mode VARCHAR2(15) CONSTRAINT ord_pay_chk
CHECK (ord_pay_mode IN (‘Cheque’, ‘Credit Card’,
‘Cash On Delivery’))); - DCREATE TABLE ord_details
(ord_id NUMBER(2),
ord_date DATE NOT NULL DEFAULT SYSDATE,
ord_amount NUMBER(5, 2) CONSTRAINT ord_amount_min
CHECK (ord_amount >= 50),
ord_status VARCHAR2(15) CONSTRAINT ord_status_chk
CHECK (ord_status IN (‘Shipped’, ‘Not Shipped’)),
ord_pay_mode VARCHAR2(15) CONSTRAINT ord_pay_chk
CHECK (ord_pay_mode IN (‘Cheque’, ‘Credit Card’,
‘Cash On Delivery’)));
- ACREATE TABLE ord_details
125Which CREATE TABLE statement is valid?
- ACREATE TABLE ord_details
(ord_no NUMBER(2) PRIMARY KEY,
item_no NUMBER(3) PRIMARY KEY,
ord_date DATE NOT NULL); - BCREATE TABLE ord_details
(ord_no NUMBER(2) UNIQUE, NOT NULL,
item_no NUMBER(3),
ord_date DATE DEFAULT SYSDATE NOT NULL); - CCREATE TABLE ord_details
(ord_no NUMBER(2) ,
item_no NUMBER(3),
ord_date DATE DEFAULT NOT NULL,
CONSTRAINT ord_uq UNIQUE (ord_no),
CONSTRAINT ord_pk PRIMARY KEY (ord_no)); - DCREATE TABLE ord_details
(ord_no NUMBER(2),
item_no NUMBER(3),
ord_date DATE DEFAULT SYSDATE NOT NULL,
CONSTRAINT ord_pk PRIMARY KEY (ord_no, item_no));
- ACREATE TABLE ord_details
126Evaluate the following SQL commands:
SQL>CREATE SEQUENCE ord_seq
INCREMENT BY 10
START WITH 120
MAXVALUE 9999
NOCYCLE;
SQL>CREATE TABLE ord_items
(ord_no NUMBER(4)DEFAULT ord_seq.NEXTVAL NOT NULL,
item_no NUMBER(3),
qty NUMBER(3) CHECK (qty BETWEEN 100 AND 200),
expiry_date date CHECK (expiry_date > SYSDATE),
CONSTRAINT it_pk PRIMARY KEY (ord_no,item_no),
CONSTRAINT ord_fk FOREIGN KEY(ord_no) REFERENCES orders(ord_no));
The command to create a table fails. Identify the reason for the SQL statement failure? (Choose all that apply.)- AYou cannot use SYSDATE in the condition of a CHECK constraint.
- BYou cannot use the BETWEEN clause in the condition of a CHECK constraint.
- CYou cannot use the NEXTVAL sequence value as a DEFAULT value for a column.
- DYou cannot use ORD_NO and ITEM_NO columns as a composite primary key because ORD_NO is also the FOREIGN KEY.
127Evaluate the following CREATE TABLE commands:
CREATE TABLE orders
(ord_no NUMBER(2) CONSTRAINT ord_pk PRIMARY KEY,
ord_date DATE,
cust_id NUMBER(4));
CREATE TABLE ord_items
(ord_no NUMBER(2),
item_no NUMBER(3),
qty NUMBER(3) CHECK (qty BETWEEN 100 AND 200),
expiry_date date CHECK (expiry_date > SYSDATE),
CONSTRAINT it_pk PRIMARY KEY (ord_no,item_no),
CONSTRAINT ord_fk FOREIGN KEY(ord_no) REFERENCES orders(ord_no));
The above command fails when executed. What could be the reason?- ASYSDATE cannot be used with the CHECK constraint.
- BThe BETWEEN clause cannot be used for the CHECK constraint.
- CThe CHECK constraint cannot be placed on columns having the DATE data type.
- DORD_NO and ITEM_NO cannot be used as a composite primary key because ORD_NO is also the FOREIGN KEY.
128Which two statements are true regarding constraints? (Choose two.)
- AA foreign key cannot contain NULL values.
- BThe column with a UNIQUE constraint can store NULLS .
- CA constraint is enforced only for an INSERT operation on a table.
- DYou can have more than one column in a table as part of a primary key.
129Which two statements are true regarding constraints? (Choose two.)
- AA foreign key cannot contain NULL values.
- BA column with the UNIQUE constraint can contain NULL values.
- CA constraint is enforced only for the INSERT operation on a table.
- DA constraint can be disabled even if the constraint column contains data.
- EAll constraints can be defined at the column level as well as the table level.
130Which two statements are true regarding tables? (Choose two.)
- AA table name can be of any length.
- BA table can have any number of columns.
- CA column that has a DEFAULT value cannot store null values.
- DA table and a view can have the same name in the same schema.
- EA table and a synonym can have the same name in the same schema.
- FThe same table name can be used in different schemas in the same database.
131Which is the valid CREATE TABLE statement?
- ACREATE TABLE emp9$# (emp_no NUMBER (4));
- BCREATE TABLE 9emp$# (emp_no NUMBER(4));
- CCREATE TABLE emp*123 (emp_no NUMBER(4));
- DCREATE TABLE emp9$# (emp_no NUMBER(4), date DATE);
132Resume (character large object [CLOB] data type), which contains the resume submitted by the employee
Which is the correct syntax to create this table?- ACREATE TABLE EMP_1
(emp_id NUMBER(4),
emp_name VARCHAR2(25),
start_date DATE,
e_status VARCHAR2(10) DEFAULT ‘ACTIVE’,
resume CLOB(200)); - BCREATE TABLE 1_EMP
(emp_id NUMBER(4),
emp_name VARCHAR2(25),
start_date DATE,
emp_status VARCHAR2(10) DEFAULT ‘ACTIVE’,
resume CLOB); - CCREATE TABLE EMP_1
(emp_id NUMBER(4),
emp_name VARCHAR2(25),
start_date DATE,
emp_status VARCHAR2(10) DEFAULT "ACTIVE",
resume CLOB); - DCREATE TABLE EMP_1
(emp_id NUMBER,
emp_name VARCHAR2(25),
start_date DATE,
emp_status VARCHAR2(10) DEFAULT ‘ACTIVE’,
resume CLOB);
- ACREATE TABLE EMP_1
133Examine the structure proposed for the TRANSACTIONS table:
name Null Type
TRANS_ID NOT NULL NUMBER(6)
CUST_NAME NOT NULL VARCHAR2(20)
CUST_STATUS NOT NULL VARCHAR2
TRANS_DATE NOT NULL DATE
TRANS_VALIDITY INTERVAL DAY TO SECOND
CUST_CREDIT_VALUE NUMBER(10)
Which two statements are true regarding the storage of data in the above table structure? (Choose two.)- AThe TRANS_DATE column would allow storage of dates only in the dd-mon-yyyy format.
- BThe CUST_CREDIT_VALUE column would allow storage of positive and negative integers.
- CThe TRANS_VALIDITY column would allow storage of a time interval in days, hours, minutes, and seconds.
- DThe CUST_STATUS column would allow storage of data up to the maximum VARCHAR2 size of 4,000 characters.
134Examine the structure proposed for the TRANSACTIONS table:
name Null Type
TRANS_ID NOT NULL NUMBER(6)
CUST_NAME NOT NULL VARCHAR2(20)
CUST_STATUS NOT NULL CHAR
TRANS_DATE NOT NULL DATE
TRANS_VALIDITY VARCHAR2
CUST_CREDIT_LIMIT NUMBER
Which statements are true regarding the creation and storage of data in the above table structure? (Choose all that apply.)- AThe CUST_STATUS column would give an error.
- BThe TRANS_VALIDITY column would give an error.
- CThe CUST_STATUS column would store exactly one character.
- DThe CUST_CREDIT_LIMIT column would not be able to store decimal values.
- EThe TRANS_VALIDITY column would have a maximum size of one character.
- FThe TRANS_DATE column would be able to store day, month, century, year, hour, minutes, seconds, and fractions of seconds.
135You need to create a table for a banking application. One of the columns in the table has the following
requirements:
1) You want a column in the table to store the duration of the credit period.
2) The data in the column should be stored in a format such that it can be easily added and subtracted with DATE data type without using conversion functions.
3) The maximum period of the credit provision in the application is 30 days.
4) The interest has to be calculated for the number of days an individual has taken a credit for.
Which data type would you use for such a column in the table?- ADATE
- BNUMBER
- CTIMESTAMP
- DINTERVAL DAY TO SECOND
- EINTERVAL YEAR TO MONTH
136Examine the description of the EMP_DETAILS table given below:
name NULL TYPE
EMP_ID NOT NULL NUMBER
EMP_NAME NOT NULL VARCHAR2 (40)
EMP_IMAGE LONG
Which two statements are true regarding SQL statements that can be executed on the EMP_DETAIL table?
(Choose two.)- AAn EMP_IMAGE column can be included in the GROUP BY clause.
- BAn EMP_IMAGE column cannot be included in the ORDER BY clause.
- CYou cannot add a new column to the table with LONG as the data type.
- DYou can alter the table to include the NOT NULL constraint on the EMP_IMAGE column.
137Which three statements are true regarding the data types in Oracle Database 10g/11g? (Choose three.)
- AOnly one LONG column can be used per table.
- BA TIMESTAMP data type column stores only time values with fractional seconds.
- CThe BLOB data type column is used to store binary data in an operating system file.
- DThe minimum column width that can be specified for a VARCHAR2 data type column is one.
- EThe value for a CHAR data type column is blank-padded to the maximum defined column width.
138Evaluate the following query:
SELECT INTERVAL ‘300’ MONTH(3),
INTERVAL ’54-2′ YEAR TO MONTH,
INTERVAL ’11:12:10.1234567′ HOUR TO SECOND(7)
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
139Examine the structure of the PROMOTIONS table:
name Null Type
PROMO_ID NOT NULL NUMBER(6)
PROMO_NAME NOT NULL VARCHAR2(30)
PROMO_CATEGORY NOT NULL VARCHAR2(30)
PROMO_COST NOT NULL NUMBER(10,2)
The management wants to see a report of unique promotion costs in each promotion category.
Which query would achieve the required result?- ASELECT DISTINCT promo_cost, promo_category FROM promotions;
- BSELECT promo_category, DISTINCT promo_cost FROM promotions;
- CSELECT DISTINCT promo_cost, DISTINCT promo_category FROM promotions;
- DSELECT DISTINCT promo_category, promo_cost FROM promotions ORDER BY 1;
140Evaluate the following query:
SQL> SELECT promo_name || q'{‘s start date was }’ || promo_begin_date
AS “Promotion Launches”
FROM promotions;
What would be the outcome of the above query?- AIt produces an error because flower braces have been used.
- BIt produces an error because the data types are not matching.
- CIt executes successfully and introduces an ‘s at the end of each promo_name in the output.
- DIt executes successfully and displays the literal ” {‘s start date was } ” for each row in the output.
141Using 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?- ASELECT cust_income_level, DISTINCT cust_credit_limit * 0.50 AS "50% Credit Limit"
FROM customers; - BSELECT DISTINCT cust_income_level, DISTINCT cust_credit_limit * 0.50 AS "50% Credit Limit"
FROM customers; - CSELECT DISTINCT cust_income_level || cust_credit_limit * 0.50 AS "50% Credit Limit"
FROM customers; - DSELECT cust_income_level || cust_credit_limit * 0.50 AS "50% Credit Limit" FROM customers;
- ASELECT cust_income_level, DISTINCT cust_credit_limit * 0.50 AS "50% Credit Limit"
142You need to produce a report where each customer’s credit limit has been incremented by $1000. In the output, t he customer’s last name should have the heading Name and the incremented credit limit should be labeled New Credit Limit. The column headings should have only the first letter of each word in uppercase .
Which statement would accomplish this requirement?- ASELECT cust_last_name Name, cust_credit_limit + 1000
“New Credit Limit”
FROM customers; - BSELECT cust_last_name AS Name, cust_credit_limit + 1000
AS New Credit Limit
FROM customers; - CSELECT cust_last_name AS “Name”, cust_credit_limit + 1000
AS “New Credit Limit”
FROM customers; - DSELECT INITCAP(cust_last_name) “Name”, cust_credit_limit + 1000 INITCAP(“NEW CREDIT LIMIT”)
FROM customers;
- ASELECT cust_last_name Name, cust_credit_limit + 1000
143Which statement is true regarding the INTERSECT operator?
- AIt ignores NULL values.
- BReversing the order of the intersected tables alters the result.
- CThe names of columns in all SELECT statements must be identical.
- DThe number of columns and data types must be identical for all SELECT statements in the query.
144Which two statements are true regarding the USING and ON clauses in table joins? (Choose two.)
- ABoth USING and ON clauses can be used for equijoins and nonequijoins.
- BA maximum of one pair of columns can be joined between two tables using the ON clause.
- CThe ON clause can be used to join tables on columns that have different names but compatible data types.
- DThe WHERE clause can be used to apply additional conditions in SELECT statements containing the ON or the USING clause.
145Examine the structure of the SHIPMENTS table:
name Null Type
PO_ID NOT NULL NUMBER(3)
PO_DATE NOT NULL DATE
SHIPMENT_DATE NOT NULL DATE
SHIPMENT_MODE VARCHAR2(30)
SHIPMENT_COST NUMBER(8,2)
You want to generate a report that displays the PO_ID and the penalty amount to be paid if the
SHIPMENT_DATE is later than one month from the PO_DATE. The penalty is $20 per day.
Evaluate the following two queries:
SQL> SELECT po_id, CASE
WHEN MONTHS_BETWEEN (shipment_date,po_date)>1 THEN
TO_CHAR((shipment_date – po_date) * 20) ELSE ‘No Penalty’ END PENALTY
FROM shipments;
SQL>SELECT po_id, DECODE
(MONTHS_BETWEEN (po_date,shipment_date)>1,
TO_CHAR((shipment_date – po_date) * 20), ‘No Penalty’) PENALTY
FROM shipments;
Which statement is true regarding the above commands?- ABoth execute successfully and give correct results.
- BOnly the first query executes successfully but gives a wrong result.
- COnly the first query executes successfully and gives the correct result.
- DOnly the second query executes successfully but gives a wrong result.
- EOnly the second query executes successfully and gives the correct result.
146Which SQL statements would display the value 1890.55 as $1,890.55? (Choose three .)
- ASELECT TO_CHAR(1890.55,’$0G000D00′)
FROM DUAL; - BSELECT TO_CHAR(1890.55,’$9,999V99′)
FROM DUAL; - CSELECT TO_CHAR(1890.55,’$99,999D99′)
FROM DUAL; - DSELECT TO_CHAR(1890.55,’$99G999D00′)
FROM DUAL; - ESELECT TO_CHAR(1890.55,’$99G999D99′)
FROM DUAL;
- ASELECT TO_CHAR(1890.55,’$0G000D00′)
147Which two statements are true regarding single row functions? (Choose two.)
- AThey accept only a single argument.
- BThey can be nested only to two levels.
- CArguments can only be column values or constants.
- DThey always return a single result row for every row of a queried table.
- EThey can return a data type value different from the one that is referenced.
148You need to extract details of those products in the SALES table where the PROD_ID column contains
the string ‘_D123’.
Which WHERE clause could be used in the SELECT statement to get the required output?- AWHERE prod_id LIKE ‘%_D123%’ ESCAPE ‘_’
- BWHERE prod_id LIKE ‘%\_D123%’ ESCAPE ‘\’
- CWHERE prod_id LIKE ‘%_D123%’ ESCAPE ‘%_’
- DWHERE prod_id LIKE ‘%_D123%’ ESCAPE ‘\_’
149Evaluate the SQL statement:
TRUNCATE TABLE DEPT;
Which three are true about the SQL statement? (Choose three.)- AIt releases the storage space used by the table
- BIt does not release the storage space used by the table.
- CYou can roll back the deletion of rows after the statement executes.
- DYou can NOT roll back the deletion of rows after the statement executes
- EAn attempt to use DESCRIBE on the DEPT table after the TRUNCATE statement executes will display an
error. - FYou must be the owner of the table or have DELETE ANY TABLE system privileges to truncate the DEPT
table
150Which statement correctly describes SQL and /SQL*Plus?
- ABoth SQL and /SQL*plus allow manipulation of values in the database.
- B/SQL*Plus recognizes SQL statements and sends them to the server; SQL is the Oracle proprietary interface for executing SQL statements.
- C/SQL*Plus is a language for communicating with the Oracle server to access data; SQL recognizes SQL statements and sends them to the server.
- DSQL manipulates data and table definitions in the database; /SQL*Plus does not allow manipulation of values in the database.
151Which are /SQL*Plus commands? (Choose all that apply.)
- AINSERT
- BUPDATE
- CSELECT
- DDESCRIBE
- EDELETE
- FRENAME
152Evaluate this SQL statement:
SELECT e.emp_name, d.dept_name
FROM employees e JOIN departments d USING (department_id)
WHERE d.department_id NOT IN (10,40)
ORDER BY dept_name;
The statement fails when executed. Which change fixes the error?- Aremove the ORDER BY clause
- Bremove the table alias prefix from the WHERE clause
- Cremove the table alias from the SELECT clause
- Dprefix the column in the USING clause with the table alias
- Eprefix the column in the ORDER BY clause with the table alias
- Freplace the condition ”d.department_id NOT IN (10,40)” in the WHERE clause with ”d.department_id <>
10 AND d.department_id <> 40”
153See the exhibit and examine the structure of the CUSTOMERS and GRADES tables:
You need to display names and grades of customers who have the highest credit limit.
Which two SQL statements would accomplish the task? (Choose two.)- ASELECT custname, grade
FROM customers, grades
WHERE (SELECT MAX(cust_credit_limit) FROM customers) BETWEEN startval AND endval; - BSELECT custname, grade FROM customers, grades
WHERE (SELECT MAX(cust_credit_limit)
FROM customers) BETWEEN startval and endval
AND cust_credit_limit BETWEEN startval AND endval; - CSELECT custname, grade
FROM customers, grades
WHERE cust_credit_limit = (SELECT MAX(cust_credit_limit)
FROM customers)
AND cust_credit_limit BETWEEN startval AND endval; - DSELECT custname, grade
FROM customers , grades
WHERE cust_credit_limit IN (SELECT MAX(cust_credit_limit)
FROM customers)
AND MAX(cust_credit_limit) BETWEEN startval AND endval;
- ASELECT custname, grade
154Which three statements are true regarding subqueries? (Choose three.)
- AMultiple columns or expressions can be compared between the main query and sub query
- BMain query and sub query can get data from different tables
- CSub queries can contain GROUP BY and ORDER BY clauses
- DMain query and sub query must get data from the same tables
- ESubqueries can contain ORDER BY but not the GROUP BY clause
- FOnly one column or expression can be compared between the main query and subqeury
155See the Exhibits and examine the structures of PRODUCTS, SALES and CUSTOMERS table:
You issue the following query:
SQL> SELECT p.promo_id, p.promo_name, prod_list_price, quantity_sold, cust_last_name
FROM products p NATURAL JOIN sales s NATURAL JOIN customers c
WHERE prod_id = 148;
Which statement is true regarding the outcome of this query?- AIt produces an error because the NATURAL join can be used only with two tables
- BIt produces an error because a column used in the NATURAL join cannot have a qualifier
- CIt produces an error because all columns used in the NATURAL join should have a qualifier
- DIt executes successfully
156Here is the structure and data of the CUST_TRANS table:
Exhibit:
[CUST_TRANS table]
Name NULL Type
CUSTNO NOT NULL CHAR(2)
TRANSDATE DATE
TRANSAMT NUMBER(6,2)
CUSTNO TRANSDATE TRANSAMT
11 01-JAN-07 1000
22 01-FEB-07 2000
33 01-MAR-07 3000
Dates are stored in the default date format dd-mm-rr in the CUST_TRANS table.
Which three SQL statements would execute successfully? (Choose three.)- ASELECT transdate + '10' FROM cust_trans;
- BSELECT * FROM cust_trans WHERE transdate = '01-01-07';
- CSELECT transamt FROM cust_trans WHERE custno > '11';
- DSELECT * FROM cust_trans WHERE transdate='01-JANUARY-07';
- ESELECT custno + 'A' FROM cust_trans WHERE transamt > 2000;
157See the Exhibit and examine the structure and data in the INVOICE table:
Exhibit:
[INVOICE Table]
NAME NULL TYPE
INV_NO NOT NULL NUMBER(3)
INV_DATE DATE
CUST_ID VARCHAR2(4)
INV_AMT NUMBER(8,2)
INV_NO INV_DATE CUST_ID INV_AMT
1 01-APR-07 AIQ 1000
2 01-OCT-07 BIR 2000
1 01-FEB-07 3000
Which two SQL statements would executes successfully? (Choose two.)- ASELECT MAX(inv_date),MIN(cust_id) FROM invoice;
- BSELECT MAX(AVG(SYSDATE - inv_date)) FROM invoice;
- CSELECT AVG(inv_date) FROM invoice;
- DSELECT AVG(inv_date - SYSDATE),AVG(inv) amt FROM invoice;
158You need to design a student registration database that contains several tables storing academic information.
The STUDENTS table stores information about a student. The STUDENT_GRADES table stores information about the student's grades. Both of the tables have a column named STUDENT_ID. The STUDENT_ID column in the STUDENTS table is a primary key.
You need to create a foreign key on the STUDENT_ID column of the STUDENT_GRADES table that points to the STUDENT_ID column of the STUDENTS table.
Which statement creates the foreign key?- ACREATE TABLE student_grades (
student_id NUMBER(12),
semester_end DATE,
gpa NUMBER(4,3),
CONSTRAINT student_id_fk REFERENCES (student_id) FOREIGN KEY
students(student_id)); - BCREATE TABLE student_grades(
student_id NUMBER(12),
semester_end DATE,
gpa NUMBER(4,3),
student_id_fk FOREIGN KEY (student_id) REFERENCES students(student_id)); - CCREATE TABLE student_grades(
student_id NUMBER(12),
semester_end DATE,
gpa NUMBER(4,3),
CONSTRAINT FOREIGN KEY (student_id) REFERENCES students(student_id)); - DCREATE TABLE student_grades(
student_id NUMBER(12),
semester_end DATE,
gpa NUMBER(4,3),
CONSTRAINT student_id_fk FOREIGN KEY (student_id) REFERENCES students(student_id));
- ACREATE TABLE student_grades (
159Which four are types of functions available in SQL? (Choose 4)
- Astring
- Bcharacter
- Cinteger
- Dcalendar
- Enumeric
- Ftranslation
- Gdate
- Hconversion
160Examine the structure of the EMPLOYEES and NEW_EMPLOYEES tables:
Which MERGE statement is valid?- AMERGE INTO new_employees c
USING employees e ON (c.employee_id = e.employee_id)
WHEN MATCHED THEN
UPDATE SET name = e.first_name ||','|| e.last_name
WHEN NOT MATCHED THEN
INSERT values (e.employee_id, e.first_name ||', '||e.last_name); - BMERGE new_employees c
USING employees e ON (c.employee_id = e.employee_id)
WHEN EXISTS THEN
UPDATE SET name = e.first_name ||','|| e.last_name
WHEN NOT MATCHED THEN
INSERT valueS(e.employee_id, e.first_name ||', '||e.last_name); - CMERGE INTO new_employees c
USING employees e ON (c.employee_id = e.employee_id)
WHEN EXISTS THEN
UPDATE SET name = e.first_name ||','|| e.last_name
WHEN NOT MATCHED THEN
INSERT values (e.employee_id, e.first_name ||','||e.last_name); - DMERGE new_employees c
FROM employees e ON (c.employee_id = e.employee_id)
WHEN MATCHED THEN
UPDATE SET name = e.first_name ||','|| e.last_name
WHEN NOT MATCHED THEN
INSERT INTO new_employees values (e.employee_id, e.first_name ||', '||e.last_name);
- AMERGE INTO new_employees c
161Which view should a user query to display the columns associated with the constraints on a table owned by the user?
- AUSER_CONSTRAINTS
- BUSER_OBJECTS
- CALL_CONSTRAINTS
- DUSER_CONS_COLUMNS
- EUSER_COLUMNS
162Examine the structure of the EMPLOYEES table:
You want to create a SQL script file that contains an INSERT statement. When the script is run, the INSERT statement should insert a row with the specified values into the EMPLOYEES table. The INSERT statement should pass values to the table columns as specified below:
Which INSERT statement meets the above requirements?- AINSERT INTO employees VALUES (emp_id_seq.NEXTVAL, '&ename', '&jobid', 2000, NULL, &did);
- BINSERT INTO employees VALUES (emp_id_seq.NEXTVAL, '&ename', '&jobid',2000, NULL, &did IN (20,50));
- CINSERT INTO (SELECT * FROM employees WHERE department_id IN (20,50))
VALUES (emp_id_seq.NEXTVAL, '&ename', '&jobid', 2000, NULL, &did); - DINSERT INTO (SELECT * FROM employees WHERE department_id IN (20,50) WITH CHECK OPTION) VALUES (emp_id_seq.NEXTVAL, '&ename', '&jobid', 2000, NULL, &did);
- EINSERT INTO (SELECT * FROM employees WHERE (department_id = 20 AND department_id = 50) WITH CHECK OPTION ) VALUES (emp_id_seq.NEXTVAL, '&ename', '&jobid', 2000, NULL, &did);
163Examine the statement:
Create synonym emp for hr.employees;
What happens when you issue the statement?- AAn error is generated
- BYou will have two identical tables in the HR schema with different names
- CYou create a table called employees in the HR schema based on you EMP table
- DYou create an alternative name for the employees table in the HR schema in your own schema
164View the Exhibit and examine the structure of the CUSTOMERS and CUST_HISTORY tables.
[CUSTOMERS table]
Name NULL Type
CUST_ID NOT NULL NUMBER(4)
CUST_NAME VARCHAR2(20)
CUST_ADDRESS VARCHAR2(30)
CUST_CITY VARCHAR2(20)
[CUST_HISTORY table]
Name NULL Type
CUST_ID NOT NULL NUMBER(4)
CUST_NAME VARCHAR2(20)
CUST_CITY VARCHAR2(20)
CHANGE_DATE DATE
The CUSTOMERS table contains the current location of all currently active customers. The CUST_HISTORY table stores historical details relating to any changes in the location of all current as well as previous customers who are no longer active with the company. You need to find those customers who have never changed their address.
Which SET operator would you use to get the required output?- AINTERSECT
- BUNION ALL
- CMINUS
- DUNION
165Which two statements about subqueries are true? (Choose two.)
- AA sub query should retrieve only one row.
- BA sub query can retrieve zero or more rows
- CA sub query can be used only in SQL query statements
- DSub queries CANNOT be nested by more than two levels
- EA sub query CANNOT be used in an SQL query statement that uses group functions.
- FWhen a sub query is used with an inequality comparison operator in the outer SQL statement, the column list in the SELECT clause of the sub query should contain only one column.
166You need to calculate the number of days from 1st Jan 2007 till date:
Dates are stored in the default format of dd-mm-rr.
Which two SQL statements would give the required output? (Choose two.)- ASELECT SYSDATE - TO_DATE('01/JANUARY/2007') FROM DUAL;
- BSELECT TO_DATE(SYSDATE,'DD/MONTH/YYYY')-'01/JANUARY/2007' FROM DUAL;
- CSELECT SYSDATE - TO_DATE('01-JANUARY-2007') FROM DUAL
- DSELECT SYSDATE - '01-JAN-2007' FROM DUAL. SELECT TO_CHAR(SYSDATE,'DD-MON-YYYY')-'01-JAN-2007' FROM DUAL;
167Which two are true about aggregate functions? (Choose two.)
- AYou can use aggregate functions in any clause of a SELECT statement.
- BYou can use aggregate functions only in the column list of the select clause and in the WHERE clause of a SELECT statement.
- CYou can mix single row columns with aggregate functions in the column list of a SELECT statement by grouping on the single row columns.
- DYou can pass column names, expressions, constants, or functions as parameter to an aggregate function.
- EYou can use aggregate functions on a table, only by grouping the whole table as one single group.
- FYou cannot group the rows of a table by more than one column while using aggregate functions.
168Which three are true? (Choose three.)
- AA MERGE statement is used to merge the data of one table with data from another.
- BA MERGE statement replaces the data of one table with that of another.
- CA MERGE statement can be used to insert new rows into a table.
- DA MERGE statement can be used to update existing rows in a table.
169Examine the structure of the EMPLOYEES and NEW_EMPLOYEES tables:
[EMPLOYEES Table]
EMPLOYEE_ID NUMBER Primary Key
FIRST_NAME VARCHAR2(25)
LAST_NAME VARCHAR2(25)
HIRE_DATE DATE
[NEW EMPLOYEES Table]
EMPLOYEE_ID NUMBER Primary Key
NAME VARCHAR2(60)
Which DELETE statement is valid?- ADELETE FROM employees WHERE employee_id = (SELECT employee_id FROM employees);
- BDELETE * FROM employeesWHERE employee_id=(SELECT employee_id FROM new_employees);
- CDELETE FROM employees WHERE employee_id IN (SELECT employee_id FROM new_employees WHERE name = ‘Carrey’);
- DDELETE * FROM employees WHERE employee_id IN (SELECT employee_idFROM new_employees WHERE name = ‘Carrey’);
170View the Exhibits and examine the structures of the PROMOTIONS and SALES tables.
Evaluate the following SQL statements:
SQL> SELECT p.prod_id, p.prod_name, s.prod_id
FROM sales s RIGHT OUTER JOIN promotions p
ON (s.promo_id = p.promo_id);
Which statement is true regarding the output of the above query?- AIt gives details of product IDs that have been sold irrespective of whether they had a promo or not
- BIt gives the details of promos for which there have been no sales
- CIt gives the details of promos for which there have been sales
- DIt gives details of all promos irrespective of whether they have resulted in a sale or not
171Examine the structure of the EMPLOYEES table:
[EMPLOYEES Table]
EMPLOYEE_ID NUMBER Primary Key
FIRST_NAME VARCHAR2(25)
LAST_NAME VARCHAR2(25)
HIRE_DATE DATE
Which UPDATE statement is valid?- AUPDATE employees SET first_name = ‘John’ SET last_name = ‘Smith’ WHERE employee_id = 180;
- BUPDATE employees SET first_name = ‘John’,SET last_name = ‘Smoth’ WHERE employee_id = 180;
- CUPDATE employee SET first_name = ‘John’ AND last_name = ‘Smith’ WHERE employee_id = 180;
- DUPDATE employee SET first_name = ‘John’, last_name = ‘Smith’ WHERE employee_id = 180;
172View the Exhibit and evaluate structures of the SALES, PRODUCTS, and COSTS tables.
Evaluate the following SQL statements:
SQL> SELECT prod_id FROM products
INTERSECT
SELECT prod_id FROM sales
MINUS
SELECT prod_id FROM costs;
Which statement is true regarding the above compound query?- AIt shows products that have a cost recorded irrespective of sales
- BIt shows products that were sold and have a cost recorded
- CIt shows products that were sold but have no cost recorded
- DIt reduces an error
173You work as a database administrator at ABC.com. You study the exhibit carefully.
Exhibit: see PROMOTIONS Table
Which two SQL statements would execute successfully? (Choose two.)- AUPDATE promotions
SET promo_cost = promo_cost+ 100
WHERE TO_CHAR(promo_end_date, 'yyyy') > '2000'; - BSELECT promo_begin_date
FROM promotions
WHERE TO_CHAR(promo_begin_date,'mon dd yy')='jul 01 98'; - CUPDATE promotions
SET promo_cost = promo_cost+ 100
WHERE promo_end_date > TO_DATE(SUBSTR('01-JAN-2000',8)) - DSELECT TO_CHAR(promo_begin_date,'dd/month')
FROM promotions
WHERE promo_begin_date IN (TO_DATE('JUN 01 98'), TO_DATE('JUL 01 98'));
- AUPDATE promotions
174Examine these statements:
CREATE ROLE registrar;
GRANT UPDATE ON student_grades TO registrar;
GRANT registrar to user1, user2, user3;
What does this set of SQL statements do?- AThe set of statements contains an error and does not work
- BIt creates a role called REGISTRAR, adds the MODIFY privilege on the STUDENT_GRADES object to the role, and gives the REGISTRAR role to three users
- CIt creates a role called REGISTRAR, adds the UPDATE privilege on the STUDENT_GRADES object to the role, and gives the REGISTRAR role to three users
- DIt creates a role called REGISTRAR, adds the UPDATE privilege on the STUDENT_GRADES object to the role, and creates three users with the role.
- EIt creates a role called REGISTRAR, adds the UPDATE privilege on three users, and gives the REGISTRAR role to the STUDENT_GRADES object.
- FIt creates a role called STUDENT_GRADES, adds the UPDATE privilege on three users, and gives the UPDATE role to the registrar
175The COMMISSION column shows the monthly commission earned by the employee.
Exhibit:
Which two tasks would require sub queries or joins in order to be performed in a single step? (Choose two.)
- Alisting the employees who earn the same amount of commission as employee 3
- Bfinding the total commission earned by the employees in department 10
- Cfinding the number of employees who earn a commission that is higher than the average commission of the company
- Dlisting the departments whose average commission is more than 600
- Elisting the employees who do not earn commission and who are working for department 20 in descending order of the employee ID
- Flisting the employees whose annual commission is more than 6000
176Examine the structure of the STUDENTS table:
You need to create a report of the 10 students who achieved the highest ranking in the course INT SQL and who completed the course in the year 1999.
Which SQL statement accomplishes this task?- ASELECT student_ id, marks, ROWNUM "Rank"
FROM students
WHERE ROWNUM <= 10 AND finish_date BETWEEN '01-JAN-99' AND '31-DEC-99
AND course_id = 'INT_SQL'
ORDER BY marks DESC; - BSELECT student_id, marks, ROWID "Rank"
FROM students
WHERE ROWID <= 10
AND finish_date BETWEEN '01-JAN-99' AND '31-DEC-99'
AND course_id = 'INT_SQL'
ORDER BY marks; - CSELECT student_id, marks, ROWNUM "Rank"
FROM (SELECT student_id, marks
FROM students
WHERE ROWNUM <= 10
AND finish_date BETWEEN '01-JAN-99' AND '31-DEC-99'
AND course_id = 'INT_SQL'
ORDER BY marks DESC); - DSELECT student_id, marks, ROWNUM "Rank”
FROM (SELECT student_id, marks
FROM students
WHERE (finish_date BETWEEN ’01-JAN-99 AND ’31-DEC-99’
AND course_id = ‘INT_SQL’
ORDER BY marks DESC)
WHERE ROWNUM <= 10 ; - ESELECT student id, marks, ROWNUM “Rank”
FROM (SELECT student_id, marks
FROM students
ORDER BY marks)
WHERE ROWNUM <= 10
AND finish date BETWEEN ’01-JAN-99’ AND ’31-DEC-99’
AND course_id = ‘INT_SQL’;
- ASELECT student_ id, marks, ROWNUM "Rank"
177View the Exhibit and examine the structure of the SALES and PRODUCTS tables. In the SALES table, PROD_ID is the foreign key referencing PROD_ID in the PRODUCTS table. You want to list each product ID and the number of times it has been sold.
Evaluate the following query:
SQL>SELECT p.prod_id, COUNT(s.prod_id) FROM products p _____________ sales s ON p.prod_id = s.prod_id GROUP BY p.prod_id;
Which two JOIN options can be used in the blank in the above query to get the required output? (Choose two.)
SALES
Name Null? Type
PROD_ID NOT NULL NUMBER(3)
CUST_ID NOT NULL NUMBER(4)
TIME_ID DATE
QTY_SOLD NUMBER(10,2)
PRODUCTS
Name Null? Type
PROD_ID NOT NULL NUMBER(3)
PROD_NAME VARCHAR2(30)
PROD_LIST_PRICE NUMBER(8,2)- AJOIN
- BFULL OUTER JOIN
- CLEFT OUTER JOIN
- DRIGHT OUTER JOIN
178You need to generate a report that shows all country names, with corresponding customers (if any) and sales details (if any), for all customers.
Which FROM clause gives the required result?- AFROM sales JOIN customers USING (cust_id) FULL OUTER JOIN countries USING (country_id);
- BFROM sales JOIN customers USING (cust_id) RIGHT OUTER JOIN countries USING (country_id);
- CFROM customers LEFT OUTER JOIN sales USING (cust_id) RIGHT OUTER JOIN countries USING (country_id);
- DFROM customers LEFT OUTER JOIN sales USING (cust_id) LEFT OUTER JOIN countries USING (country_id);
179View the Exhibits and examine the structures of the COSTS and PROMOTIONS tables. Evaluate the following SQL statement:
SQL> SELECT prod_id
FROM costs
WHERE promo_id IN (SELECT promo_id FROM promotions WHERE promo_cost < ALL
(SELECT MAX(promo_cost) FROM promotions GROUP BY (promo_end_date - promo_begin_date)));
What would be the outcome of the above SQL statement?- AIt displays prod IDs in the promo with the lowest cost.
- BIt displays prod IDs in the promos with the lowest cost in the same time interval.
- CIt displays prod IDs in the promos with the highest cost in the same time interval.
- DIt displays prod IDs in the promos with cost less than the highest cost in the same time interval.
180View the Exhibit and examine the structure of the PROMOTIONS, SALES, and CUSTOMER tables. You need to generate a report showing the promo name along with the customer name for all products that were sold during their promo campaign and before 30th October 2007. You issue the following query:
SQL> SELECT promo_name,cust_name
FROM promotions p JOIN sales s ON(time_id BETWEEN promo_begin_date AND promo_end_date)JOIN customer c ON (s.cust_id = c.cust_id) AND time_id < '30-oct-2007';
Which statement is true regarding the above query?
- AIt executes successfully and gives the required result.
- BIt executes successfully but does not give the required result.
- CIt produces an error because the join order of the tables is incorrect.
- DIt produces an error because equijoin and nonequijoin conditions cannot be used in the same SELECT statement.
181View 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:
SQL>SELECT p.prod_name, i.item_cnt FROM (SELECT prod_id, COUNT(*) item_cnt FROM sales GROUP BY prod_id) i RIGHT OUTER JOIN products p ON i.prod_id = p.prod_id;
What happens when the above statement is executed?- AThe statement executes successfully and produces the required output.
- BThe statement produces an error because ITEM_CNT cannot be displayed in the outer query.
- CThe statement produces an error because a subquery in the FROM clause and outer-joins cannot be used together.
- DThe statement produces an error because the GROUP BY clause cannot be used in a subquery in the FROM clause.
182View the Exhibit and evaluate the structure and data in the CUST_STATUS table. You issue the following SQL statement:
SQL> SELECT custno, NVL2(NULLIF(amt_spent, credit_limit), 0, 1000)"BONUS" FROM cust_status; Which statement is true regarding the execution of the above query?
- AIt produces an error because the AMT_SPENT column contains a null value.
- BIt displays a bonus of 1000 for all customers whose AMT_SPENT is less than CREDIT_LIMIT.
- CIt displays a bonus of 1000 for all customers whose AMT_SPENT equals CREDIT_LIMIT, or AMT_SPENT is null .
- DIt produces an error because the TO_NUMBER function must be used to convert the result of the NULLIF function before it can be used by the NVL2 function.
183View the Exhibits and examine the structures of the PRODUCTS and SALES tables.
Which two SQL statements would give the same output? (Choose two.)- ASELECT prod_id FROM products INTERSECT SELECT prod_id FROM sales;
- BSELECT prod_id FROM products MINUS SELECT prod_id FROM sales;
- CSELECT DISTINCT p.prod_id FROM products p JOIN sales s ON p.prod_id=s.prod_id;
- DSELECT DISTINCT p.prod_id FROM products p JOIN sales s ON p.prod_id <> s.prod_id;
184View the Exhibit and examine the structure of CUSTOMERS table. Evaluate the following query: SQL>SELECT cust_id, cust_city FROM customers WHERE cust_first_name NOT LIKE 'A_%g_%' AND cust_credit_limit BETWEEN 5000 AND 15000 AND cust_credit_limit NOT IN (7000, 11000) AND cust_city NOT BETWEEN 'A' AND 'B';
Which statement is true regarding the above query?
- AIt executes successfully.
- BIt produces an error because the condition on the CUST_CITY column is not valid.
- CIt produces an error because the condition on the CUST_FIRST_NAME column is not valid.
- DIt produces an error because conditions on the CUST_CREDIT_LIMIT column are not valid.
185View the Exhibit and examine the structure of the PRODUCT, COMPONENT, and PDT_COMP tables.
In PRODUCT table, PDTNO is the primary key. In COMPONENT table, COMPNO is the primary key. In PDT_COMP table, (PDTNO,COMPNO) is the primary key, PDTNO is the foreign key referencing PDTNO in PRODUCT table and COMPNO is the foreign key referencing the COMPNO in COMPONENT table.
You want to generate a report listing the product names and their corresponding component names, if the component names and product names exist.
Evaluate the following query:
SQL>SELECT pdtno,pdtname, compno,compname FROM product _____________ pdt_comp USING (pdtno) ___________ component USING(compno) WHERE compname IS NOT NULL;
Which combination of joins used in the blanks in the above query gives the correct output?
- AJOIN; JOIN
- BFULL OUTER JOIN; FULL OUTER JOIN
- CRIGHT OUTER JOIN; LEFT OUTER JOIN
- DLEFT OUTER JOIN; RIGHT OUTER JOIN
186View the Exhibits and examine the structures of the CUSTOMERS, SALES, and COUNTRIES tables. You need to generate a report that shows all country names, with corresponding customers (if any) and sales details (if any), for all customers. Which FROM clause gives the required result?
- AFROM sales JOIN customers USING (cust_id) FULL OUTER JOIN countries USING (country_id);
- BFROM sales JOIN customers USING (cust_id) RIGHT OUTER JOIN countries USING (country_id);
- CFROM customers LEFT OUTER JOIN sales USING (cust_id) RIGHT OUTER JOIN countries USING (country_id);
- DFROM customers LEFT OUTER JOIN sales USING (cust_id) LEFT OUTER JOIN countries USING (country_id);
187Examine the structure of the ORDERS table:
Name Null Type
ORDER_ID NOT NULL NUMBER(12)
ORDER_DATE NOT NULL TIMESTAMP(6)
CUSTOMER_ID NOT NULL NUMBER(6)
ORDER_STATUS NUMBER(2)
ORDER_TOTAL NUMBER(8,2)
You want to find the total value of all the orders for each year and issue the following command: SQL>SELECT TO_CHAR(order_date,'rr'), SUM(order_total) FROM orders GROUP BY TO_CHAR(order_date,'yyyy');
Which statement is true regarding the outcome?- AIt executes successfully and gives the correct output.
- Bt gives an error because the TO_CHAR function is not valid. C. It
- CIt executes successfully but does not give the correct output.
- DIt gives an error because the data type conversion in the SELECT list does not match the data type conversion in the GROUP BY clause.
188You want to display the date for the first Mon day of the next month and issue the following command:
SQL>SELECT TO_CHAR(NEXT_DAY(LAST_DAY(SYSDATE),'MON'),‘dd "is the first Monday for" fmmonth rrrr') FROM DUAL;
What is the outcome?- AIt executes successfully and returns the correct result.
- BIt executes successfully but does not return the correct result.
- CIt generates an error because TO_CHAR should be replaced with TO_DATE.
- DIt generates an error because rrrr should be replaced by rr in the format string.
- EIt generates an error because fm and double quotation marks should not be used in the format string.
189You 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 'ACTIVE' 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?- ACREATE TABLE EMP_1 (emp_id NUMBER(4), emp_name VARCHAR2(25), start_date DATE, e_status VARCHAR2(10) DEFAULT 'ACTIVE', resume CLOB(200));
- BCREATE TABLE 1_EMP (emp_id NUMBER(4), emp_name VARCHAR2(25), start_date DATE, emp_status VARCHAR2(10) DEFAULT 'ACTIVE', resume CLOB);
- CCREATE TABLE EMP_1 (emp_id NUMBER(4), emp_name VARCHAR2(25), start_date DATE, emp_status VARCHAR2(10) DEFAULT "ACTIVE", resume CLOB);
- DCREATE TABLE EMP_1 (emp_id NUMBER, emp_name VARCHAR2(25), start_date DATE, emp_status VARCHAR2(10) DEFAULT 'ACTIVE', resume CLOB);
190View the Exhibit and examine the structure and data in the INVOICE table. Which two SQL statements would execute successfully? (Choose two.)
- ASELECT AVG(inv_date ) FROM invoice;
- BSELECT MAX(inv_date),MIN(cust_id) FROM invoice;
- CSELECT MAX(AVG(SYSDATE - inv_date)) FROM invoice;
- DSELECT AVG( inv_date - SYSDATE), AVG(inv_amt) FROM invoice;
191View the Exhibit and evaluate the structure and data in the CUST_STATUS table. You issue the following SQL statement:
SQL> SELECT custno, NVL2(NULLIF(amt_spent, credit_limit), 0, 1000) "BONUS" FROM cust_status; Which statement is true regarding the execution of the above query?
- AIt produces an error because the AMT_SPENT column contains a null value.
- BIt displays a bonus of 1000 for all customers whose AMT_SPENT is less than CREDIT_LIMIT.
- CIt displays a bonus of 1000 for all customers whose AMT_SPENT equals CREDIT_LIMIT, or AMT_SPENT is null.
- DIt produces an error because the TO_NUMBER function must be used to convert the result of the NULLIF function before it can be used by the NVL2 function.
Information
- Description
- 191 DBA Quiz on DBA 191, created by Carla Hales on 15/06/2016.
- Author
- Carla Hales
- Language
- English
- Views
- 59
- History
- Created on 15 Jun 2016 · updated on 3 Feb 2018
- Tags
- Rating
- 5.0 out of 5 · 3 ratings Rate this quiz