Free Oracle 1Z0-051 Exam Dumps Questions & Answers

Examine the description of the EMPLOYEES table:
EMP_ID NUMBER(4) NOT NULL
LAST_NAME VARCHAR2(30) NOT NULL
FIRST_NAME VARCHAR2(30)
DEPT_ID NUMBER(2)
Which statement produces the number of different departments that have employees with last name Smith?

• A.  SELECT UNIQUE(dept_id) FROM employees WHERE last_name='Smith';
• B.  SELECT COUNT (dept_id) FROM employees WHERE last_name='Smith';
• C.  SELECT COUNT(*) FROM employees WHERE last_name='Smith';
• D.  SELECT COUNT(DISTINCT dept_id) FROM employees WHERE last_name='Smith';
• E.  SELECT DISTINCT(COUNT(dept_id)) FROM employees WHERE last_name='Smith';

Which three tasks can be performed using SQL functions built into Oracle Database? (Choose three.)

• A.  Substituting a character string in a text expression with a specified string
• B.  Displaying a date in a nondefault format
• C.  Finding the number of characters in an expression
• D.  Combining more than two columns or expressions into a single column in the output

Examine 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?

• A.  SELECT SUBSTR(cust_name, INSTR(cust_name,' ')+1)
  FROM customers
  WHERE INITCAP(SUBSTR(cust_name, INSTR(cust_name,' ')+1)) LIKE 'Mc%';
• B.  SELECT SUBSTR(cust_name, INSTR(cust_name,' ')+1)
  FROM customers
  WHERE SUBSTR(cust_name, INSTR(cust_name,' ')+1) LIKE INITCAP('MC%');
• C.  SELECT SUBSTR(cust_name, INSTR(cust_name,' ')+1)
  FROM customers
  WHERE INITCAP(SUBSTR(cust_name, INSTR(cust_name,' ')+1)) = INITCAP('MC%');
• D.  SELECT SUBSTR(cust_name, INSTR(cust_name,' ')+1)
  FROM customers
  WHERE INITCAP(SUBSTR(cust_name, INSTR(cust_name,' ')+1))='Mc';

You work as a database administrator at ABC.com. You study the exhibit carefully. Exhibit:

Examine the structure of 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 fails below $10 after a discount of 25% is applied on it.
Exhibit:

The query generates an error.
What is the reason of generating error?

• A.  The column alias should be put in uppercase and enclosed within double quotation marks in the WHERE clause
• B.  The column alias should be replaced with the expression in the WHERE clause
• C.  The double quotation marks should be removed from the column alias
• D.  The parenthesis should be added to enclose the entire expression

For which action can you use the TO_DATE function?

• A.  Format '10-JAN-99' to 'January 10 1999'
• B.  Convert any character literal to a date
• C.  Convert any date literal to a date
• D.  Convert any date to a character literal
• E.  Convert any numeric literal to a date