Learning SQL*Plus Section 1 Practice

Please find sample Certification questions related to Section 1. You should be able to answer these questions after reading and practicing Learning SQL*Plus Section 1. Please post the answers from your study with possible explanation in the Discussion Board. We will provide answers for these after few days with detailed explanation. All these questions are for 1Z0-001 Introduction to SQL Oracle Certification Exam. You can practice these at our certification center to test your knowledge. Even in the Interviews you can expect these kind of questions.

1. You need to create a report to display the ship date and order totals of your ordid table. If the order has not been shipped your report must display not shipped. If the total is not available your report must say not available. In the or did table the ship date column has a data type of date the total column has a data type of number. Which statement do you use to create this report?

A. Select ordid, ship date "Not shipped", total "Not available" FROM order;

B. Select ordid, NVL (ship date 'Not shipped'), NVL (total, "Not available") FROM order;

C. Select ordid, NVL (TO_CHAR (ship date), 'Not shipped'), NVL (TO_CHAR (total), 'Not available') FROM order;

D. Select ordid, TO_CHAR (ship date, 'Not shipped') ,TO_CHAR (total, 'Not available') FROM order;

2. You want of display the details or all employees whose last names is Smith. But you are not sure in which case last names are stored. Which statement will list all the employees whose last name is Smith?

A. Select last name, first name FROM emp WHERE last name= 'smith';

B. Select last name, first name FROM emp WHERE UPPER (last name)= 'smith';

C. Select last name, first name FROM emp WHERE last name=UPPER ('smith');

D. Select last name, first name FROM emp WHERE LOWER (last name)= 'smith';

3. You need to analyze how long your orders to be shipped from the date that the order is placed. To do this you Must create a report that displays the customer number, date order, date shipped and the number of months in Whole numbers from the time the order is placed to the time the order is shipped. Which statement produces the Required results?

A. SELECT custid, orderate, ship date, ROUND(MONTHS_BETWEEN(shipdate,orderate)) "Time Taken" FROM ordid;

B. SELECT custid, orderate, ship date, ROUND(DAYS_BETWEEN(shipdate,orderate))/30 FROM ordid;

C. SELECT custid, orderate, ship date, ROUND OFF (ship date-orderate) "Time Taken" FROM ord;

D. SELECT custid, orderate, ship date, MONTHS_BETWEEN (shipdate,orderate) "Time Taken". FROM ordid;

4. The employee table contains these columns.

LAST_NAME VARCHAR2(25)

FIRST_NAME VARCHAR2(25)

DEPT_ID NUMBER(9)

You need to display the names of the employees that are not assigned to the department. Evaluate this SQL statement.

SELECT last_name, first_name FROM employee WHERE dept_id is NULL

Which change should you make to achieve the desired result?

A. Create an outer join.

B. Change the column in the where condition.

C. Change the operator in the where condition

D. Add a second condition to the where condition

5. Examine the code:

SET SERVER OUTPUT ON

DECLARE

v_char_val varchar2(100);

BEGIN

v_char_val:= 'Hello World',

DBMS_OUTPUT.PUT_LINE(v_char_val);

END

SET SERVER OUTPUT OFF

This code is stored in a script file name "myproc,sql". Which statement executes the code in the script file?

A. Myproc.sql

B. RUN myproc,sql

C. START myproc.sql

D. EXECUTE myproc.sql

E. BEGIN myproc.sql END;

6. How do you send the output of your SQL* Plus session to a text operating system file called OUTPUT.LST?

A. SAVE OUTPUT.LST

B. SPOOL OUTPUT.LST

C. PRINT OUTPUT.LST

D. SEND OUTPUT.LST

7. The product table contains these columns.

ID NUMBER(9) PK

COST NUMBER(7,2)

SALE_PRICE NUMBER(7,2)

Management has asked you to calculate the net revenue per unit for each product, if the cost of each product is increased by 10% and the sale price of each product is increased by 25%. You issue this SQL statement.

SELECT id, sale_price * 1.25 - cost * 1.10 FROM product;

Which conclusion can you draw from the results?

A. Only the required results are displayed.

B. The results provide more information than management requested.

C. A function needs to be included in the SELECT statement to achieve the desired result.

D. The order on the operations in the calculation needs to be changed to achieve the required results.

8. Your company wants to get each employee a $100 salary increment. You need to evaluate the results, from the EMP table prior to actual modification. If you do not want to store the result in the database which statement is valid?

A. You need to add a column to the EMP table.

B. You need to give the arithmetic expression that involves the salary increment in the set clause of the update statement.

C. You need to give the arithmetic expression that involves the salary increment in the select clause of the select statement.

D. You need to give the arithmetic expression that involves the salary increment in the update clause of the

select statement.

E. You need to give the arithmetic expression that involves the salary increment in the display clause of the

select statement.

9. The employee table contains these columns

First_Name VARCHAR2(25)

Last_Name VARCHAR2(25)

Evaluate these two SQL statements.

1. SELECT Concat(first_name, last) Length(concat(first_name, last_name) FROM employee

WHERE UPPER (last_name) Like '%J' OR UPPER (last_name) Like '%K' ;

2. SELECT INITCAP (first_name) || initcap (last_name), Length (last_name) + Length (first_name)

FROM employee WHERE INITCAP (SUBSTR (last_name, 1, 1)) IN ('J' , 'K' , 'L')

How will the results differ?

A. The statement will retrieve different data from the database.

B. The statement will retrieve the same data from the database, but will display it differently.

C. Statement1 will execute but statement2 will not.

D. Statement2 will execute but statement1 will not.

10. Evaluate the SQL statement.

SELECT e.id, (.15* e.salary) + (.25* e.bonus),

(s.sale_amount * (.15* e.commision_pct))

FROM employee e , sales

WHERE e.id = s.emp_id;

What would happen if you removed all the parenthesis from calculation?

A. Results will be lower.

B. Results will be higher.

C. Statement will not execute.

D. Statement will achieve some results.

11. Which is not an SQL Plus command?

A. List.

B. Accept.

C. Change.

D. Update.

E. Describe.

12. When selecting data which statement is valid about projection?

A. Projection allows due to choose rows.

B. Projection allows due to choose columns.

C. Projection allows due to joined tables together.

D. Projection allows due to add columns to a table.

13. The employee table contains these columns:

ID NUMBER(9)

LAST_NAME VARCHAR2(25)

FIRST_NAME VARCHAR2(25)

COMMISSION NUMBER(7,2)

You need to display the current commission for all employees. Desired results are:

1. Display the commission multiplied by 1.5

2. Exclude employees with zero commission.

3. Display a zero for employees with null commission value.

Evaluate this SQL statement.

SELECT id, last_name, first_name, commission*1. 5 FROM employee WHERE commission <>0;

Which of the desired results does the statement provide?

A. All the desired results.

B. Two of the desired results.

C. One of the desired results.

D. A syntax error.

14. Examine the trace instance chart for employee table. You want to display each employee hire date from earliest to latest. Which SQL statement will you use?

A. SELECT hire_date FROM employee;

B. SELECT hire_date FROM employee ORDER BY hire_date;

C. SELECT employee FROM employee ORDER by hire_date;

D. SELECT hire_date FROM employee ORDER BY hire_date DESC;

15. Which SELECT statement would you use in a PL/SQL block to query the employee table and retrieve the last name and salary of the employee whose ID is 3?

A. SELECT last_name,salary FROM employee;

B. SELECT last_name,salary FROM employee WHERE id=3;

C. SELECT last_name,salary INTO v_last_name,v_salary FROM employee WHERE id=3;

D. SELECT l last_name,salary FROM employee INTO v_last_name,v_salary WHERE id=3;

E. SELECT last_name,salary INTO v_last_name,v_salary WHERE id=3;

16. You need to retrieve the employee names and salaries from emp tables assorted by the salary in descending order. If two names match for a salary then two names must be displayed in alphabetical order. Which statement produces the required results?

A. SELECT ename,sal FROM emp ORDER BY ename,sal;

B. SELECT ename,sal FROM emp ORDER BY sal,ename;

C. SELECT ename,sal FROM emp SORT BY sal DESC,ename;

D. SELECT ename,sal FROM emp ORDER BY sal DESC,ename;

E. SELECT ename,sal FROM emp ORDER BY sal DESC,ename ASCENDING;

17. Employee table has HIRE_DATE columns,

You need to display the hire_date values in this format:

10 of October 1999 Which SELECT statement can you use?

A. SELECT hire_date('fmDD "of"MONTH YYYY') "Date Hired" FROM employee;

B. SELECT hire_date('DD "of"MONTH YYYY') "Date Hired" FROM employee;

C. SELECT TO_CHAR (hire_date,'DDspth of MONTH YYYY') "Date Hired" FROM employee;

D. SELECT TO_CHAR(hire_date,'fmDD "of" MONTH YYYY') DATE HIRED FROM employee;

18. Employee table has the following columns,

Column name

ID_NO

NAME

SALARY

DEPT_NO

HIRE_DA TE

You want to display employee hire date from the earliest to latest. Which SQL statement would you use?

A. SELECT hire_date. FROM employee;

B. SELECT hire_date. FROM employee ORDER BY hire_date;

C. SELECT hire_date. FROM employee GROUP BY hire_date;

D. SELECT hire_date. FROM employee ORDER BY hire_date DESC;

19. You want to create a report that displays the employee details along with the tax category of each employee. The tax category is determined by comparing the salary of the employees. The tax category is determined by comparing the salary of the employee from the emp table to the lower and the upper salary values in the tax table.

Which select statement produces the required result?

A. SELECT e.name,e.salary,e.tax grade FROM emp e,tax t WHERE e.salary between t.lowsal and t.highsal;

B. SELECT e.name,e.salary,e.tax grade FROM emp e,tax t WHERE e.salary>=t.lowsal and <= t.highsal;

C. SELECT e.name,e.salary,e.tax grade FROM emp e,tax t WHERE e.salary<=t.lowsal and >=t.highsal;

D. SELECT e.name,e.salary,e.tax grade FROM emp e,tax t WHERE e.salary in t.lowsal and t.highsal;