Free Oracle 1Z0-051 Exam Dumps Questions & Answers

Examine the structure of the MARKS table: Exhibit:

Which two statements would execute successfully? (Choose two.)

• A.  SELECT SUM(subject1+subject2+subject3)
  FROM marks
  WHERE student_name IS NULL;
• B.  SELECT student_name,SUM(subject1)
  FROM marks
  WHERE student_name LIKE 'R%';
• C.  SELECT SUM(DISTINCT NVL(subject1,0)), MAX(subject1)
  FROM marks
  WHERE subject1 > subject2;
• D.  SELECT student_name,subject1
  FROM marks
  WHERE subject1 > AVG(subject1);

Examine the structure of the PROMOS table:

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.)

• A.  SELECT promo_name, NVL(TO_CHAR(promo_end_date -
  promo_start_date),'ONGOING') FROM promos;
• B.  SELECT promo_name, TO_CHAR(NVL(promo_end_date promo_start_date,'ONGOING')) FROM promos;
• C.  SELECT promo_name,COALESCE(TO_CHAR(promo_end_date promo_start_date),'ONGOING') FROM promos;
• D.  SELECT
  promo_name,ecode(coalesce(promo_end_date,promo_start_date),null,'ONGOING',
  promo_end_date - promo_start_date)
  FROM promos;
• E.  SELECT promo_name, DECODE(promo_end_date
  -promo_start_date,NULL,'ONGOING',promo_end_date - promo_start_date) FROM
  promos;

Which two statements are true about sequences created in a single instance database? (Choose two.)

• A.  The numbers generated by a sequence can be used only for one table
• B.  CURRVAL is used to refer to the last sequence number that has been generated
• C.  DELETE <sequencename> would remove a sequence from the database
• D.  When 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
• E.  When the MAXVALUE limit for a sequence is reached, you can increase the MAXVALUE limit by using the ALTER SEQUENCE statement

The DBA issues this SQL command:
CREATE USER Scott
IDENTIFIED by tiger;
What privileges does the user Scott have at this point?

• A.  No privileges.
• B.  Only the CONNECT privilege.
• C.  Only the SELECT privilege.
• D.  All the privileges of a default user.

Using the CUSTOMERS table, you need to generate a report that shows 50% of each credit amount in each income level. The report should NOT show any repeated credit amounts in each income level. Which query would give the required result?

• A.  SELECT DISTINCT cust_income_level ' ' cust_credit_limit * 0.50 AS "50% Credit Limit"
  FROM customers;
• B.  SELECT cust_income_level ' ' cust_credit_limit * 0.50 AS "50% Credit Limit" FROM
  customers;
• C.  SELECT DISTINCT cust_income_level, DISTINCT cust_credit_limit * 0.50 AS "50%
  Credit Limit"
  FROM customers;
• D.  SELECT cust_income_level, DISTINCT cust_credit_limit * 0.50 AS "50% Credit Limit"
  FROM customers;