Question

1. You query data from emp2 table with below SQL statement:

   SQL> SELECT *

       FROM  emp2;

   NAME SSN

   ------------- ------------------------

   Joe 452852452

   Mary 444525962

   Fred 445212525

   Tom 492525252

   Lucy   172826152  

   Now, you query data from emp2 table with below SQL statement again:

   SQL> SELECT AVG(LENGTH(name)) AS column1, SUM(INSTR(ssn, '%@',2,2)) AS column2

   FROM  emp2

   WHERE name = INICAP(name);

   What will be displayed for the output of column1 and column2?

   	A.	The value in column1 will be 3.5 and the value in column2 will be 11
   	B.	The value in column1 will be 3.6 and the value in column2 will be 19
   	C.	The value in column1 will be 3.5 and the value in column2 will be 26
   	D.	The value in column1 will be 3.6 and the value in column2 will be 26
   	E.	None of above will be displayed for the output

2 points   

QUESTION 2

1. REVIEW the structure and the roes in the emp50 table

   SQL> desc emp50

   Name Null? Type

   -------------- -------- -------------

   ID NUMBER

   NAME VARCHAR2(15)

   JOB_TITLE VARCHAR2(15)

   JOB_CODE NUMBER(2)

   SQL> SELECT *

   FROM emp50;

   ID NAME JOB_TITLE JOB_CODE

   ------ ---------------------- ---------------------- ---------------

   50 William Ford Programmer 6

   51 Wilbur Wright Jr Programmer 6

   52   Wyona Judd Programmer A 4   

   52   Wyona Judd Programmer B 12

   55 Wyatt Programmer A 12

   56   WoWo Smith Maintenance Eng 5

   The user issues the following statements:

   SQL> SELECT REPLACE(SUBSTR(name,1,6),'W','b')    

   FROM emp50

   WHERE LENGTH(name) BETWEEN job_code AND LENGTH(LOWER(job_title));

   What will be the outcome of executing the above SELECT statement?

   	A.	The SELECT statement will fail because of the wrong number of arguments in the SUBSTR function.
   	B.	The SELECT statement will return three rows, namely: bilbur byona bobo S
   	C.	The SELECT statement will fail due to a mismatch in the number of parenthesises.
   	D.	The SELECT statement will fail because you cannot perform the LENGTH of the LOWER of job_title.

Which functions below is single_valued NUMBER functions?

	A.	CONCAT LENGTH TRIM
	B.	REPLACE INSTR LPAD
	C.	ROUND SIGN ABS
	D.	NVL NVL2 ISNULL

he student table contains below columns:

ID NUMBER(9)

LAST_NAME VARCHAR2(25)

FIRST_NAME VARCHAR2(25)

ENROLL_DATE DATE

Evaluate below two SQL statements:

Statement 1:

SELECT CONCAT(INITCAP(first_name), INITCAP(last_name))

FROM student

WHERE enroll_date < '31-DEC-2007';

Statement 2:

SELECT INITCAP(first_name) || INITCAP(last_name)

FROM student

WHERE enroll_date < '31-DEC-2007';

Which of the following statements is TRUE?

	A.	Both statements will display the same data.
	B.	Both statements will NOT display the same data.
	C.	One of the statements will fail because of it contains a syntax error.
	D.	Both statements will retrieve the same data, but the display format of the returned values will differ.

Answer 1

Dear Student ,

As per the requirement submitted above , kindly find the below solution.

Question 1:

Answer :E.None of above will be displayed for the output

Explanation :Note that in the SQL Statement its a INITCAP()

Below screen shows the output

**********************************

Question 2:

Answer :B.

The SELECT statement will return three rows, namely:

bilbur

byona

bobo S

Explanation :Below screen shows the output in Oracle Live SQL

**********************************

Question 3:

Answer :C.

ROUND

SIGN

ABS

Explanation :Single valued number functions are ROUND ,SIGN and  ABS

**********************************

Question 4:

Answer :A.Both statements will display the same data.

Explanation :Both the sql statements will concatenate first name and last name.

**********************************

NOTE : PLEASE FEEL FREE TO PROVIDE FEEDBACK ABOUT THE SOLUTION.