Oracle Introduction to Oracle9i SQL

Question - 30 Maximum mark : - 52

Home
All Exams
Oracle Introduction to Oracle9i SQL Online Mock Tests
Oracle Introduction to Oracle9i SQL

Which four statements correctly describe functions that are available in SQL? (Choose four)

INSTR returns the numeric position of a named character. NULLIF compares twp expressions and returns null if they are equal, or the first expression if they are notequal. TRUNCATE rounds the column, expression, or value to n decimal places. DECODE translates an expression after comparing it to each search value. TRIM trims the heading of trailing characters (or both) from a character string.

Answer & Solution Discuss in Board Save for Later

Answer & Solution

Answer: Option B

Solution:

It provides fallback for IE8.

Examine the structures of the EMPLOYEES and TAX tables.

EMPLOYEES
EMPLOYEE_ID NUMBER NOT NULL, Primary
Key
EMP_NAME VARCHAR2 (30)
JOB_ID VARCHAR2 (20)
SALARY NUMBER
References
MGR_ID NUMBER
EMPLOYEE_ID
column
DEPARTMENT_ID NUMBER Foreign key to
DEPARTMENT _ID
column of the
DEPARTMENT table
TAX
MIN_SALARY NUMBER
MAX_SALARY NUMBER
TAX_PERCENT NUMBER
Percentage tax for given
salary range

You need to find the percentage tax applicable for each employee. Which SQL statement would you use?

SELECT employee_id, salary, tax_percent
FROM employees e, tax t
WHERE e.salary BETWEEN t.min _ salary AND t.max_salary

✘

SELECT employee_id, salary, tax_percentFROM employees e, tax t
WHERE e.salary > t.min_salary, tax_percent

✘

SELECT employee_id, salary, tax_percentFROM employees e, tax t
WHERE MIN(e.salary) = t.min_salary
AND MAX(e.salary) = t.max_salary

✘ You cannot find the information because there is no common column between the two tables. ✔

Answer & Solution Discuss in Board Save for Later

Answer & Solution

Answer: Option B

Solution:

It provides fallback for IE8.

Which are DML statements? (Choose all that apply)

COMMIT... MERGE... UPDATE... DELETE... CREATE...

Answer & Solution Discuss in Board Save for Later

Answer & Solution

Answer: Option B

Solution:

It provides fallback for IE8.

Mary has a view called EMP_DEPT_LOC_VU that was created based on the EMPLOYEES, DEPARTMENTS,
and LOCATIONS tables. She granted SELECT privilege to Scott on this view. Which option enables Scott to
eliminate the need to qualify the view with the name MARY .EMP_DEP_LOC_VU each time the view is
referenced?

Scott can create a synonym for the EMP_DEPT_LOC_VU bus using the command:
CREATE PRIVATE SYNONYM EDL_VU
FOR mary.EMP DEPT_LOC_VU;
then he can prefix the columns with this synonymn.

✘

Scott can create a synonym for the EMP_DEPT_LOC_VU by using the command:CREATE SYNONYM EDL_VU
FOR mary.EMP_DEPT_LOC_VU;
then he can prefix the columns with this synonym.

✘

Scott can create a synonym for the EMP_DEPT_LOC_VU by using the command:CREATE LOCAL SYNONYM EDL_VU
FOR mary.EMP DEPT_LOC_VU;
then he can prefix the columns with this synonym.

✘

Scott can create a synonym for the EMP_DEPT_LOC_VU by using the command:CREATE SYNONYM EDL_VU
ON mary(EMP_DEPT_LOC_VU);
then he can prefix the columns with this synonym.

✔ Scott cannot create a synonym because synonyms can be created only for tables. ✔

Answer & Solution Discuss in Board Save for Later

Answer & Solution

Answer: Option B

Solution:

It provides fallback for IE8.

Examine the structure of the EMPLOYEES table:

EMPLOYEE_ID NUMBER Primary Key
FIRST_NAME VARCHAR2(25)
LAST_NAME VARCHAR2(25)
HIRE_DATE DATE
You issue these statements:
CREATE table new_emp ( employe_id NUMBER, name VARCGAR2(30)); INSERT INTO new_emp SELECT
employee_id, last_name from employees; Savepoint s1;
UPDATE new_emp set name = UPPER(name);
Savepoint s2;
Delete from new_emp;
Rollback to s2;
Delete from new_emp where employee_id=180;
UPDATE new_emp set name = 'James';
Rollback to s2;
UPDATE new_emp sey name = 'James' Where employee_id=180;
Rollback;

At the end of this transaction, what is true?

You have no rows in the table. ✘ You have an employee with the name of James. ✘ You cannot roll back to the same savepoint more than once. ✘ Your last update fails to update any rows because employee ID 180 was already deleted. ✔

Answer & Solution Discuss in Board Save for Later

Answer & Solution

Answer: Option B

Solution:

It provides fallback for IE8.

In which scenario would index be most useful?

The indexed column is declared as NOT NULL. ✘ The indexed columns are used in the FROM clause. ✘ The indexed columns are part of an expression. ✘ The indexed column contains a wide range of values. ✔

Answer & Solution Discuss in Board Save for Later

Answer & Solution

Answer: Option B

Solution:

It provides fallback for IE8.

Which SQL statement generates the alias Annual Salary for the calculated column SALARY*12?

SELECT ename, salary*12 'Annual Salary'
FROM employees;

✘

SELECT ename, salary*12 "Annual Salary"FROM employees;

✘

SELECT ename, salary*12 AS Annual SalaryFROM employees;

✘

SELECT ename, salary*12 AS INITCAP("ANNUAL SALARY")FROM employees

✔

Answer & Solution Discuss in Board Save for Later

Answer & Solution

Answer: Option B

Solution:

It provides fallback for IE8.

Examine the structure of the EMPLOYEES table:

EMPLOYEE_ID NUMBER Primary Key
FIRST_NAME VARCHAR2(25)
LAST_NAME VARCHAR2(25)
DEPARTMENT_ID NUMBER
SALARY NUMBER

What is the correct syntax for an inline view?

SELECT a.last_name, a.salary, a.department_id,

✘

maxsalFROM employees a,
(SELECT department_id, max(salary)maxsal
FROM employees
GROUP BY department_id) b
WHERE a.department_id = b.department_id
AND a.salary < b.maxsal;

✘

SELECT a.last name, a.salary, a.department_idFROM employees a
WHERE a.department_id IN
(SELECT department_id
FROM employees b
GROUP BY department_id having salary =
(SELECT max(salary) from employees))

✘

SELECT a.last_name, a.salary, a.department_idFROM employees a
WHERE a.salary =
(SELECT max(salary)
FROM employees b
WHERE a.department _ id = b.department _ id);

✔

SELECT a.last_name, a.salary, a.department_idFROM employees a
WHERE (a.department_id, a.salary) IN
(SELECT department_id, a.salary) IN
(SELECT department_id max(salary)
FROM employees b
GROUP BY department_id
ORDER BY department _ id);

✔

Answer & Solution Discuss in Board Save for Later

Answer & Solution

Answer: Option B

Solution:

It provides fallback for IE8.

Which clause would you use in a SELECT statement to limit the display to those employees whose salary is
greater then 5000?

ORDER BY SALARY > 5000

✘

GROUP BY SALARY > 5000

✘

HAVING SALARY > 5000

✘

WHERE SALARY > 5000

✔

Answer & Solution Discuss in Board Save for Later

Answer & Solution

Answer: Option B

Solution:

It provides fallback for IE8.

10.

Examine the structure of the EMPLOYEES table:

EMPLOYEE_ID NUMBER NOT NULL
EMP_NAME VARCHAR2(30)
JOB_ID VARCHAR2(20) DEFAULT 'SA_REP'
SAL NUMBER
COMM_PCT NUMBER
MGR_ID NUMBER
DEPARTMENT_ID NUMBER
You need to update the records of employees 103 and 115. The UPDATE statement you specify should update
the rows with the values specified below:
JOB_ID: Default value specified for this column definition.
SAL: Maximum salary earned for the job ID SA_REP.
COMM_PCT: Default value specified for this commission percentage column, if any. If no default value is
specified for the column, the value should be NULL. DEPARTMENT_ID: Supplied by the user during run time
through substitution variable.

Which UPDATE statement meets the requirements?

UPDATE employees
SET job_id = DEFAULT
AND Sal = (SELECT MAX(sal)
FROM employees
WHERE job_id = 'SA_REP')
AND comm_pct = DEFAULT
AND department_id = &did
WHERE employee _id IN (103,115);

✘

UPDATE employeesSET job_id = DEFAULT
AND Sal = MAX(sal)
AND comm_pct = DEFAULT OR NULL
AND department_id = &did
WHERE employee_id IN (103,115)
AND job _ id = 'SA_ REP';

✘

UPDATE employeesSET job_id = DEFAULT,
Sal = (SELECT MAX(sal)
FROM employees
WHERE job_id = 'SA_REP'),
comm_pct = DEFAULT,
department_id = &did
WHERE employee_id IN (103,115);

✘

UPDATE employeesSET job_id = DEFAULT,
Sal = MAX(sal),
comm_pct = DEFAULT,
department_id = &did
WHERE employee_id IN (103,115)
AND job _ id = 'SA_ REP';

✔

UPDATE employeesSET job_id = DEFAULT,
Sal = (SELECT MAX(sal)
FROM employees
WHERE job_id = 'SA_REP')
comm_pct = DEFAULT OR NULL,
department_id = &did
WHERE employee_id IN (103,115);

✔

Answer & Solution Discuss in Board Save for Later

Answer & Solution

Answer: Option B

Solution:

It provides fallback for IE8.

Prev Next

Congratulations

you have successfully completed the test

Back to Question View Result

Detail Form

We will send your result on your email id and phone no. please fill detail

Name

Email ID Note: Report will be send to the above Email ID

Phone No

Oracle Introduction to Oracle9i SQL

Answer & Solution

Answer: Option B

Solution:

Answer & Solution

Answer: Option B

Solution:

Answer & Solution

Answer: Option B

Solution:

Answer & Solution

Answer: Option B

Solution:

Answer & Solution

Answer: Option B

Solution:

Answer & Solution

Answer: Option B

Solution:

Answer & Solution

Answer: Option B

Solution:

Answer & Solution

Answer: Option B

Solution:

Answer & Solution

Answer: Option B

Solution:

Answer & Solution

Answer: Option B

Solution:

Congratulations

COMPANY

Products

OTHERS

Partner