Dbms130 - S09-S16

DBMS130 - S09-S16 Q&A
How many records are returned by the following query?


SELECT SUM(SALARY), DEPARTMENT_ID FROM EMPLOYEES

GROUP BY DEPARTMENT_ID;



Assume there are 11 nonnull and 1 null unique DEPARTMENT_ID values. All records
have a nonnull SALARY value. - (correct answer)12



What value is returned after executing the following statement?



SELECT AVG(NVL(SALARY,100)) FROM EMPLOYEES;



Assume there are ten employee records and each contains a SALARY value of 100,
except for one employee, who has a null value in the SALARY field. - (correct
answer)100



What result is returned by the following statement? SELECT COUNT(*) FROM DUAL; -
(correct answer)1



How many rows of data are returned after executing the following statement?


SELECT DEPT_ID, SUM(SALARY) FROM EMP

GROUP BY DEPT_ID HAVING SUM(NVL(SALARY,100)) > 400;



Assume the EMP table has ten rows and each contains a SALARY value of 100, except
for one, which has a null value in the SALARY field. The first and second five rows have
DEPT_ID values of 10 and 20, respectively. - (correct answer)2 rows

, Which values are returned after executing the following statement?



SELECT COUNT(*), COUNT(SALARY) FROM EMPLOYEES;



Assume there are 10 employee records and each contains a SALARY value of 100,
except for 1, which has a null value in their SALARY field. - (correct answer)10 and 9



Which of the following statements are syntactically correct? - (correct answer)SELECT *
FROM EMPLOYEES E JOIN DEPARTMENTS D USING (DEPARTMENT_ID);



Choose one correct statement regarding the following query:



SELECT * FROM EMPLOYEES E

JOIN DEPARTMENTS D ON (D.DEPARTMENT_ID=E.DEPARTMENT_ID) JOIN

LOCATIONS L ON (L.LOCATION_ID =D.LOCATION_ID); - (correct answer)The
JOIN...ON clause may be used for joins between multiple tables.



If you precede the subquery by the ____ operator, the condition is true only if it satisfies
all values produced by the subquery. - (correct answer)ALL



You create an alias by typing the name of the table, pressing the ____, and then typing
the name of the alias. - (correct answer)Spacebar


What are the distinguishing characteristics of a scalar subquery? - (correct answer)A
scalar subquery returns one row.

A scalar subquery returns one column.



Which of the following statements are equivalent? - (correct answer)select employee_id
from employees where salary < all (select salary from employees where
department_id= 10);