Learning SQL*Plus Section 1 Practice Answers

Please find the answers for the questions posted in this article Learning SQL*Plus Section 1 Practice with details explanation and links where you can understand and practice them. Please note that these questions are similer to Certification exam questions and also helps in the Interviews too since in most of the interview they ask questions based on these. After this we will be providing a site where you can practice basic to advanced SQL without having to install Oracle on your local PC.

**We are still updating this article with answers for the remaining 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;

Answer: B

Explanation :

Sometimes, a query for some information will produce a nothing result. In database terms, nothing is called NULL. In theory, the mathematical foundation for relational databases, NULL represents the value of an empty dataset, or a dataset containing no values. Unless specified otherwise, a column in a table is designed to accommodate the placement of nothing into the column.

You may want to substitute a value in place of NULL. Oracle provides this functionality with a special function called nvl( ). Assume that you do not want to see blank spaces for unshipped information. Instead, you want the output of the query to contain the word “unshipped”.

If the column specified in nvl( ) is not NULL, the value in the column is returned; when the column is NULL, the special string is returned. The nvl( ) function can be used on columns of all datatypes, but remember that the value specified to be returned if the column value is NULL must be the same datatype as the column specified. The basic syntax for nvl( ) is as follows:

NVL(column_name, value_if_null)

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';

Answer: D

Explanation :

Select last name, first name.

FROM emp

WHERE LOWER (last name)= 'smith'

Answer D shows all records with last name Smith because function LOWER returns the column value passed as smith into all lowercase

Here are the few functions similar to the Lower

lower(x) : Converts to lower case

upper(x) : Converts to upper case

initcap(x) : Converts first letter to Upper Case

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;

Answer: A

Explanation: shows the number of months (rounded to integer) between the date of order and the date of shipment.

months_between(x,y) : Returns a number of months between y and x as produced by x–y. This function can return a decimal value

The next date function determines the number of months between two different dates given. The name of the function is months_between( ). The syntax of this command is tricky, so it will be presented here. The syntax of this command is months_between(y,x), and the return value for this function is y-x:

SELECT MONTHS_BETWEEN('01-JAN-04','01-AUG-04') FROM DUAL;

round(x,y): Rounds x to the decimal precision of y. If y is negative, rounds to the precision of y places to the left of the decimal point.

So we get the required results using the 2 functions round and months_between.

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

Answer: C

Explanation: The NULL operator need to be changed in the WHERE condition to display correct result. There is nothing special in this question it’s basically covers the Select statement with Null function.

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;

Answer: C

Explanation: You use start command to run any SQL script stored in a file from SQL prompt. START is used to execute the code in the script file.

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

Answer: B

Explanation: SPOOL is used in SQL * Plus to send output of the session to a text

operation system file.

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.

Answer: A

Explanation: This Question covers Performing Arithmetic on Numeric Expressions, Select statements in Oracle require you specify columns or expressions following the select keyword and a table name after the from keyword.

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.

Answer: C

Explanation: you DON'T NEED to add a column to the EMP table, this can be done with SELECT statement only, without changing structure of table.

SELECT clause of the SELECT statement WILL NOT change data and will not store the result of operation in the database.

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.

Answer: A

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.

Answer: C

Explanation: After removing all the parenthesis the statement will have incorrect syntax. It’s important to check for parenthesis while using the arithmetic operations.

Tags

RS said: