Oracle 1Z0-051 Free Practice Questions

Q1. - (Topic 1) 

Examine the structure of the MARKS table: 

Exhibit: 

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

A. SELECT student_name,subject1 

FROM marks 

WHERE subject1 > AVG(subject1); 

B. SELECT student_name,SUM(subject1) 

FROM marks 

WHERE student_name LIKE 'R%' 

C. SELECT SUM(subject1+subject2+subject3) 

FROM marks 

WHERE student_name IS NULL; 

D. SELECT SUM(DISTINCT NVL(subject1,0)), MAX(subject1) 

FROM marks 

WHERE subject1 > subject2; 

View Answer

Q2. - (Topic 1) 

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? 

A. 152526**** 

B. **152525.99 

C. 152525** 

D. an error message 

View Answer

Q3. - (Topic 2) 

Which statement describes the ROWID data type? 

A. Binary data up to 4 gigabytes. 

B. Character data up to 4 gigabytes. 

C. Raw binary data of variable length up to 2 gigabytes. 

D. Binary data stored in an external file, up to 4 gigabytes. 

E. A hexadecimal string representing the unique address of a row in its table. 

View Answer

Q4. - (Topic 1) 

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

A. SELECT cust_city, cust_income_level, cust_last_name FROM customers ORDER BY cust_city desc, cust_income_level DESC; 

B. SELECT cust_city, cust_income_level, cust_last_name FROM customers ORDER BY cust_income_level desc, cust_city DESC; 

C. SELECT cust_city, cust_income_level, cust_last_name 

FROM customers 

ORDER BY (cust_city, cust_income_level) DESC; 

D. SELECT cust_city, cust_income_level, cust_last_name FROM customers ORDER BY cust_city, cust_income_level DESC; 

View Answer

Q5. - (Topic 1) 

View the Exhibit and examine the structure of the CUSTOMERS and CUST_HISTORY tables. 

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? 

A. INTERSECT 

B. UNION ALL 

C. MINUS 

D. UNION 

View Answer

Q6. - (Topic 2) 

Which best describes an inline view? 

A. a schema object 

B. a sub query that can contain an ORDER BY clause 

C. another name for a view that contains group functions 

D. a sub query that is part of the FROM clause of another query 

View Answer

Q7. - (Topic 2) 

You need to write a SQL statement that returns employee name, salary, department ID, and maximum salary earned in the department of the employee for all employees who earn less than the maximum salary in their department. 

Which statement accomplishes this task? 

A. SELECT a.emp_name, a.sal, b.dept_id, MAX(sal) FROM employees a, departments b WHERE a.dept_id = b.dept_id AND a.sal < MAX(sal) GROUP BY b.dept_id; 

B. SELECT a.emp_name, a.sal, a.dept_id, b.maxsal FROM employees a, (SELECT dept_id, MAX(sal) maxsal FROM employees GROUP BY dept_id) b WHERE a.dept_id = b.dept_id AND a.sal < b.maxsal; 

C. SELECT a.emp_name, a.sal, a.dept_id, b.maxsal FROM employees a WHERE a.sal < (SELECT MAX(sal) maxsal FROM employees b GROUP BY dept_id); 

D. SELECT emp_name, sal, dept_id, maxsal FROM employees, (SELECT dept_id, MAX(sal) maxsal FROM employees GROUP BY dept_id) WHERE a.sal < maxsal; 

View Answer

Q8. - (Topic 1) 

For which action can you use the TO_DATE function? 

A. Convert any date literal to a date 

B. Convert any numeric literal to a date 

C. Convert any character literal to a date 

D. Convert any date to a character literal 

E. Format ’10-JAN-99’ to ‘January 10 1999’ 

View Answer

Q9. - (Topic 2) 

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

A. SELECT TO_CHAR(prod_price* .25,'$99,999.99') FROM PRICE_LIST; 

B. SELECT TO_CHAR(TO_NUMBER(prod_price)* .25,'$99,999.00') FROM PRICE_LIST; 

C. SELECT TO_CHAR(TO_NUMBER(prod_price,'$99,999.99')* .25,'$99,999.00') FROM PRICE_LIST; 

D. SELECT TO_NUMBER(TO_NUMBER(prod_price,'$99,999.99')* .25,'$99,999.00') FROM PRICE_LIST; 

View Answer

Q10. - (Topic 1) 

View 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: 

Which statement is true regarding the above query? 

A. It executes successfully and gives the required result. 

B. It executes successfully but does not give the required result. 

C. It produces an error because the join order of the tables is incorrect. 

D. It produces an error because equijoin and nonequijoin conditions cannot be used in the same SELECT statement. 

View Answer

Q11. - (Topic 2) 

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

A. 

SELECT LPAD(SUBSTR(cust_name,INSTR(cust_name,' ')),LENGTH(cust_name),'*') 

"CUST NAME" FROM customers 

WHERE INSTR(cust_name, ' ',1,2)<>0; 

B. 

SELECT LPAD(SUBSTR(cust_name,INSTR(cust_name,' ')),LENGTH(cust_name),'*') 

"CUST NAME" FROM customers 

WHERE INSTR(cust_name, ' ',-1,2)<>0; 

C. 

SELECT LPAD(SUBSTR(cust_name,INSTR(cust_name,' ')),LENGTH(cust_name)-INSTR(cust_name,''),'*') "CUST NAME" 

FROM customers 

WHERE INSTR(cust_name, ' ',-1,-2)<>0; 

D. 

SELECT LPAD(SUBSTR(cust_name,INSTR(cust_name,' ')),LENGTH(cust_name)-INSTR(cust_name,' '),'*') "CUST NAME" 

FROM customers 

WHERE INSTR(cust_name, ' ',1,2)<>0 ; 

View Answer

Q12. - (Topic 2) 

You own a table called EMPLOYEES with this table structure: 

EMPLOYEE_ID NUMBER Primary Key FIRST_NAME VARCHAR2(25) LAST_NAME VARCHAR2(25) HIRE_DATE DATE What happens when you execute this DELETE statement? 

DELETE employees; 

A. You get an error because of a primary key violation. 

B. The data and structure of the EMPLOYEES table are deleted. 

C. The data in the EMPLOYEES table is deleted but not the structure. 

D. You get an error because the statement is not syntactically correct. 

View Answer

Q13. - (Topic 2) 

View 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_datepromo_ begin_date))); 

What would be the outcome of the above SQL statement? 

A. It displays prod IDs in the promo with the lowest cost. 

B. It displays prod IDs in the promos with the lowest cost in the same time interval. 

C. It displays prod IDs in the promos with the highest cost in the same time interval. 

D. It displays prod IDs in the promos with cost less than the highest cost in the same time interval. 

View Answer

Q14. - (Topic 2) 

View the Exhibit and examine the structure of the PRODUCTS, SALES, and SALE_SUMMARY tables. 

SALE_VW is a view created using the following command: 

SQL>CREATE VIEW sale_vw AS 

SELECT prod_id, SUM(quantity_sold) QTY_SOLD 

FROM sales GROUP BY prod_id; 

You issue the following command to add a row to the SALE_SUMMARY table: 

SQL>INSERT INTO sale_summary 

SELECT prod_id, prod_name, qty_sold FROM sale_vw JOIN products 

USING (prod_id) WHERE prod_id = 16; 

What is the outcome? 

A. It executes successfully. 

B. It gives an error because a complex view cannot be used to add data into the SALE_SUMMARY table. 

C. It gives an error because the column names in the subquery and the SALE_SUMMARY table do not match. 

D. It gives an error because the number of columns to be inserted does not match with the number of columns in the SALE_SUMMARY table. 

View Answer

Q15. - (Topic 1) 

Evaluate the following SQL query; 

What would be the outcome? 

A. 200 

B. 16 

C. 160 

D. 150 

E. 100 

View Answer