1Z0-051: Oracle 11g SQL Exam Online Test

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?


































































































































































































































Description:

This is a Mock Exam for OCA first paper 1Z0-051: Oracle 11g- Introduction to SQL to diagnose your knowledge. Contact us at info@dtech-education.com for Exam preparation classes and course materials.

About us: DTecH IT Education is the brainchild of a Team of IT Professionals from Oracle, Accenture, HP and Infosys. Founded in 2009, it is aimed at budding professionals to leverage and keep them in sync with the trends and techniques in the ever changing Technology platforms and metamorphose them from fresh minds to Real professionals.

Tags:

SQL

Discussion

Latecia.A

Tak for oplysningerne.

246 days 12 hours 39 minutes ago

Valravn

I can also advise https://credit-10.com/dk/der-login/

934 days 14 hours 8 minutes ago

Michael

I recommend you to visit https://www.pengeinfo.dk/forbrugslaan/ if you are interested in comparison services and want to learn more.

1109 days 12 hours 33 minutes ago

Diana

It wouldn't hurt to check out https://kviklaan-guide.dk/. Here, you can get valuable information.

1110 days 9 hours 49 minutes ago

Thomas Poulsen

Feel free to check out this site https://walletform.com/da/laan-penge/billige-laan/ or simply check out our official Danish service https://walletform.com/da/

1333 days 1 hours 1 minutes ago

Mathias Clausen

Try to check the site Rabatkodeautomaten and see there info
https://rabatkodeautomaten.dk

1389 days 8 minutes ago

Mathias Clausen

Try to check the site Bredbåndfinder.dk and see there info
https://bredbåndfinder.dk

1389 days 13 minutes ago

Christina

check out https://kassekreditten.dk/blog/

1453 days 9 hours 7 minutes ago

Rolf Hansen

You should check out https://moneyarcher.com/dk/ if you're for Denmark or https://moneyarcher.com/se/ if your are from sweden.

1519 days 6 hours 47 minutes ago

DTecH IT Education
DTecH IT Education-Your mentor for a better Career
User
42 Followers

Your Facebook Friends on WizIQ

More Tests By Author

SQL Course completion Exam
10 Questions | 875 Attempts

Oracle Interview Questions - Frequently Asked
10 Questions | 7044 Attempts

Oracle SQL, PL/SQL Quiz
10 Questions | 52124 Attempts

Explore Similar Courses