Which statement is true regarding the INTERSECT operator?
You are currently located in Singapore and have connected to a remote database in Chicago.
You issue the following command:
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?
You 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?
Evaluate the following query:
SQL> SELECT TRUNC(ROUND(156.00,-1),-1)
FROM DUAL;
What would be the outcome?
View the Exhibits and examine the structures of the PROMOTIONS and SALES tables.
Evaluate the following SQL statement:
SQL>SELECT p.promo_id, p.promo_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?
Using the CUSTOMERS table, you need to generate a report that shows 50% of each credit amount in each income level. The report should NOT show any repeated credit amounts in each income level. Which query would give the required result?
Which statement is true regarding the UNION operator?
The 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;
View the Exhibit and examine the structure of the PROMOTIONS table.
Which SQL statements are valid? (Choose all that apply.)
A. SELECT promo_id, DECODE(NVL(promo_cost,0), promo_cost,
promo_cost * 0.25, 100) "Discount"
FROM promotions;
B. SELECT promo_id, DECODE(promo_cost, 10000,
DECODE(promo_category, 'G1', promo_cost *.25, NULL),
NULL) "Catcost"
FROM promotions;
C. SELECT promo_id, DECODE(NULLIF(promo_cost, 10000),
NULL, promo_cost*.25, 'N/A') "Catcost"
FROM promotions;
D. SELECT promo_id, DECODE(promo_cost, >10000, 'High',
<10000, 'Low') "Range"
FROM promotions;
Evaluate 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?
The 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?
When does a transaction complete? (Choose all that apply.)
A. when a DELETE statement is executed
B. when a ROLLBACK command is executed
C. when a PL/SQL anonymous block is executed
D. when a data definition language (DDL) statement is executed
E. when a TRUNCATE statement is executed after the pending transaction
Which two statements are true regarding single row functions? (Choose two.)
A. They accept only a single argument.
B. They can be nested only to two levels.
C. Arguments can only be column values or constants.
D. They always return a single result row for every row of a queried table.
E. They can return a data type value different from the one that is referenced.
View the Exhibit and examine the structure of the PROMOTIONS table.
Evaluate the following SQL statement:
SELECT promo_category,
AVG(promo_cost) Avg_Cost, AVG(promo_cost)*.25
as 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?
Which statement is true regarding subqueries?
Which three SQL statements would display the value 1890.55 as $1,890.55? (Choose three.)
A. SELECT TO_CHAR(1890.55,'$0G000D00')
FROM DUAL;
B. SELECT TO_CHAR(1890.55,'$9,999V99')
FROM DUAL;
C. SELECT TO_CHAR(1890.55,'$99,999D99')
FROM DUAL;
D. SELECT TO_CHAR(1890.55,'$99G999D00')
FROM DUAL;
E. SELECT TO_CHAR(1890.55,'$99G999D99')
FROM DUAL;
View the Exhibit and examine the structure of the CUSTOMERS table. Which statement would display the highest credit limit available in each income level in each city in the CUSTOMERS table?
Evaluate the following SQL statement:
SQL> SELECT cust_id, cust_last_name "Last Name"
FROM customers
WHERE country_id = 10
UNION
SELECT cust_id CUST_NO, cust_last_name
FROM customers
WHERE country_id = 30;
Which ORDER BY clauses are valid for the above query? (Choose all that apply.)
A. ORDER BY 2,1
B. ORDER BY CUST_NO
C. ORDER BY 2,cust_id
D. ORDER BY "CUST_NO"
E. ORDER BY "Last Name"
View 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?
View the Exhibit and examine the structure of the CUSTOMERS table.
Evaluate the following SQL statement:
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?
The 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?
View 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?
Examine 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 three SQL statements would execute successfully? (Choose three.)
A. SELECT transdate + '10' FROM cust_trans;
B. SELECT * FROM cust_trans WHERE transdate = '01-01-07';
C. SELECT transamt FROM cust_trans WHERE custno > '11';
D. SELECT * FROM cust_trans WHERE transdate='01-JANUARY-07';
E. SELECT custno + 'A' FROM cust_trans WHERE transamt > 2000;
View the Exhibit and examine the structure of the PROMOTIONS table. You have to generate a report that displays the promo name and start date for all promos that started after the last promo in the 'INTERNET' category. Which query would give you the required output?
View the Exhibit and examine the structure of the PRODUCTS table. Evaluate the following query:
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?