QUESTION 1 Based on the HR.EMPLOYEES table, how do you write a SQL to get the de

Question

QUESTION 1

Based on the HR.EMPLOYEES table, how do you write a SQL to get the department numbers that have at least one employee working?

SELECT ALL DEPARTMENT_ID FROM EMPLOYEES;

SELECT DISTINCT DEPARTMENT_ID FROM EMPLOYEES;

SELECT DEPARTMENT_ID FROM EMPLOYEES WHERE DEPARTMENT_ID IS NOT NULL;

SELECT DEPARTMENT_ID FROM EMPLOYEES WHERE DEPARTMENT_ID > 1;

10 points   

QUESTION 2

Which operator can be used in SQL queries to join two text values?

+

|

||

++

10 points   

QUESTION 3

Consider the following SQL and choose the best option that summarizes the result of the query.
SELECT first_name, hire_date, salary, manager_id mid
FROM employees
WHERE department_id IN (110,100)
ORDER BY mid ASC, salary DESC;

Returns all rows from employees table, sorted in the order of manager IDs.

Returns only rows from departments 110 or 100, with the employee drawing highest salary in department at the top.

Returns rows from departments 110 and 100, only if manager id is not null.

Does not return any row, as some columns in the SELECT clause are missing in the ORDER BY clause.

All of the above are wrong.

10 points   

QUESTION 4

Which command is used to get information about database objects, using SQL*Plus?

DESCRIBE.

SELECT.

QUERY.

None of the above.

10 points   

QUESTION 5

In which order should the following clauses appear in a SELECT statement: FROM, ORDER BY, WHERE?

It does not matter, they can appear in any order.

FROM, ORDER BY, WHERE.

WHERE, FROM, ORDER BY.

FROM, WHERE, ORDER BY.

10 points   

QUESTION 6

Which function cannot be used to return a value, instead of NULL?

NULLIF.

NVL.

DECODE.

COALESCE.

10 points   

QUESTION 7

In the employees table, the commission_pct is a nullable column. Which of the following SQL is best suited to calculate the total compensation?

SELECT first_name, salary, commission_pct, salary + (salary * commission_pct) compensation FROM employees ;

SELECT first_name, salary, commission_pct, NVL(salary + (salary * commission_pct), 0) compensation FROM employees ;

SELECT first_name, salary, NVL(commission_pct,0), salary + (salary * commission_pct) compensation FROM employees ;

SELECT first_name, salary, commission_pct, salary + (salary * NVL(commission_pct,0)) compensation FROM employees ;

10 points   

QUESTION 8

Which function returns the first non-null value from the list of arguments?

NVL2

NULLIF

COALESCE

DECODE

10 points   

QUESTION 9

The function _____ can be used to find the character position of the first occurrence of a string.

10 points   

QUESTION 10

The result of evaluating function TO_DATE(‘01-01-98’,’DD-MM-YYYY’) is _________.

10 points   

SELECT ALL DEPARTMENT_ID FROM EMPLOYEES;

SELECT DISTINCT DEPARTMENT_ID FROM EMPLOYEES;

SELECT DEPARTMENT_ID FROM EMPLOYEES WHERE DEPARTMENT_ID IS NOT NULL;

SELECT DEPARTMENT_ID FROM EMPLOYEES WHERE DEPARTMENT_ID > 1;

Explanation / Answer

1. SELECT DEPARTMENT_ID FROM EMPLOYEES WHERE DEPARTMENT_ID IS NOT NULL; 2. a. + 3. All of the above are wrong. 4. SELECT 5. FROM, WHERE, ORDER BY. 6. NULLIF 7. d.SELECT first_name, salary, commission_pct, salary + (salary * NVL(commission_pct,0)) compensation FROM employees ; 8. COALESCE 9. POSITION 10. 01-01-1998

Related Questions

Navigate

• Browse (All)
• Browse Q
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.