First Midterm- CSCI 276 – Oracle SQL and PL/SQL Programming

DO NOT WRITE ON THIS TEST!!

Instructor: Melody Callan Time Allowed: 2 hours

Number of Questions: 30 Number of Pages: 11

This test is closed book, notes, computer and neighbor. The test is Multiple Choice. Choose the best possible answer(s)

1. Which two operators cannot be used in an outer join condition (choose two)

Correct answer = 2, 4

2. You query the database with this command:

SELECT id_number, (quantity – 100 / 0.15 – 35 * 20)

FROM inventory

Which expression is evaluated first (choose one)?

quantity – 100
0.15-35
35*20
100/0.15

Correct answer = 4

3. Under what conditions can a Cartesian Product result from a SELECT statement? (choose all that apply - one or more)

all rows in the first table are joined to all rows in the second table.
the join condition was not an equijoin.
a join condition is omitted.
a join condition is invalid.
none of the above.

4. Which of the following are legal queries? (choose one or more)

1. SELECT deptno, count(deptno)

FROM emp

GROUP BY ename;

2. SELECT deptno, count(deptno), job

FROM emp

GROUP BY deptno;

3. SELECT deptno, avg(sal)

FROM emp;

4. SELECT deptno, avg(sal)

FROM emp

GROUP BY deptno;

5. SELECT avg(sal)

FROM emp

GROUP BY deptno;

5. How would you display a listing of the sums of employee salaries for those employees not making a commission, for each job type, including only those sums greater than 2500? (choose one)

select job, sum(sal)

from emp

where sum(sal) > 2500 and comm is null;

select job, sum(sal)

from emp

where comm is null

group by job

having sum(sal) > 2500;

select job, sum(sal)

from emp

where sum(sal) > 2500 and comm is null

group by job;

select job, sum(sal)

from emp

group by job

having sum(sal) > 2500 and comm is not null;

none of the above

6. The following SQL statement is illegal because: (choose one)

SELECT deptno, AVG(sal)

FROM emp

WHERE AVG(sal)> 2000

GROUP BY deptno;

it requires data from more than one table, yet only one table is listed.
sal is not a legal column name
instead of a WHERE clause, a HAVING clause must be used to restrict groups
the GROUP BY clause must contain AVG(sal)
this SELECT statement is perfectly legal

7. All of the following can ONLY be used with numeric datatypes except: (choose one)

AVG
COUNT
SUM
STDDEV
VARIANCE

8. Mark all that are true for subqueries: (one or more may apply)

The order by clause is not allowed in subqueries.
Subqueries can be used only in WHERE clauses and FROM clauses.
Subqueries can return multiple columns.
Subqueries must return a single row only.
Subqueries can appear only in WHERE clauses and HAVING clauses.

9. What operator would you choose to prevent this Oracle error message? (choose one)

ORA-01427:single -row subquery returns more than one row

Use the IN operator
Use the >= operator
Use the CAN EXIST operator
Use the = operator
Use the <= operator

10. Operator John needs to search for text data in a column, but he only remembers part of the string. Which of the following SQL operations allows the use of wildcard comparisons (choose one)?

A. BETWEEN

B. IN

C. LIKE

D. EXISTS

( Correct Answer: C )

11. Given the following data in the emp table:

ENAME SALARY

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

PING 5000

AILYN 4999

SAM 1000

LESLIE 3000

TOM 2500

RAVI 10000

What will the following select statement produce (choose one)?

SELECT ename FROM emp WHERE salary BETWEEN 3000 AND 5000;

A. ENAME

--------

AILYN

B. ENAME

--------

PING

CHRIS

LESLIE

C. an error

D. None of the above

( Correct Answer: B )

12. Which one of the following statements contains an error (choose one)?

A) Select * from EMP where EMPID = 493945;

B) Select EMPID from EMP where EMPID = 493945;

C) Select EMPID from EMP;

D) Select EMPID where EMPID = 56949 and LASTNAME = ‘SMITH’;

E) None of the statements above contains an error.

Correct answer: D

13. Which of the following queries would show the salaries of all employees (not the boss) who have the same name as the boss (the only employee without a manager (mgr)) (choose one)?

A. select sal

from emp

where ename same as

(ename where mgr is NULL);

B. select sal

from emp

where ename like

(select ename from emp where mgr is NULL)

and mgr is not NULL;

C. select sal

from emp

where mgr != NULL and ename =

(select ename from emp where mgr = NULL);

D. All of the above

E. None of the above

( Correct Answer: B )

14. You wish to join the data from two tables, DEPARTMENT and EMPLOYEE, into one result set and display that set in your session. Tables DEPARTMENT and EMPLOYEE have a common column, called dept_no in both tables. Which of the following choices correctly displays the where clause you would use if you want to see the data in table DEPARTMENT where the value is 80 in column dept_no, even when there was no corresponding value in table EMPLOYEE (choose one)?

A. where DEPARTMENT.dept_no = 80 AND DEPARTMENT.dept_no(+) = EMPLOYEE.dept_no;

B. where DEPARTMENT.dept_no = 80 AND DEPARTMENT.dept_no = EMPLOYEE.dept_no(+);

C. where DEPARTMENT.dept_no = 80 AND DEPARTMENT.dept_no(+) = EMPLOYEE.dept_no(+);

D. where DEPARTMENT.dept_no = 80;

( Correct Answer: B )

15. Which of the following are true when using table aliases? (Choose three)

A. Table aliases can be up to 30 characters in length.

B. Table aliases should be as long as possible for readability.

C. If a table alias is used for a particular table name in the FROM clause, then that

table alias must be substituted for the table name throughout the SELECT statement.

D. they must be less than 30 characters in length.

E. A table alias is valid for the entire session.

( Correct Answers: A,C,D )

16. How many join conditions are required to join n tables (choose one)?

A. n

B. any number of join conditions

C. at least 3

D. (n-1)

( Correct Answers: D )

17. What type of queries typically involve self-joins (choose the one best answer)?

A. queries where rows in a table refer to other rows in the same table

B. queries where the join column can be null

C. queries involving multiple tables with foreign keys

D. introspective queries

( Correct Answers: A )

18. In an application, you are searching for specific employee information in the

EMPLOYEE table corresponding to an invoice number you have. The INVOICE table contains empid, the primary key for EMPLOYEE. Which of the following operations is appropriate for obtaining data from EMPLOYEE using your invoice number (choose one)?

A. select * from EMPLOYEE where empid = 10465312;

B. select * from EMPLOYEE where empid = (select invoice_no from invoice

where invoice_no = 10465312);

C. select * from EMPLOYEE where empid = (select empid from invoice

where invoice_no = 10465312);

D. select e.empid, i.invoice_no

from EMPLOYEE e, INVOICE i

where e.invoice_no = i.invoice_no;

( Correct Answers: C )

19. You are developing advanced queries for an Oracle database. Which of the following where clauses makes use of Oracle's ability to logically test a value against a set of results returned without explicitly knowing what the set is before executing the query (choose one)?

A. where COL_A = 5

B. where COL_A in (1,2,3,4,5)

C. where COL_A between 6 and 10

D. where COL_A in (select num from TAB_OF_NUMS)

( Correct Answers: D )

20. What is the effect of an ORDER BY clause inside a nested subquery (choose one)?

A. It causes matching rows in the outer query to be displayed in that order

B. It has no effect on the final output, and therefore reduces the efficiency of the query

C. It generates an error

D. It is ignored

E. None of the above

( Correct Answers: C )

21. Choose the SQL phrase that is equivalent to (choose one):

where sal in (select sal from emp

where job = 'MANAGER')

A. where sal <> ALL(select sal from emp

where job = 'MANAGER')

B. where sal = ANY(select sal from emp

where job = 'MANAGER')

C. where sal >= (select MIN(sal)

from emp

where job = 'MANAGER')

and sal <= (select MAX(sal)

from emp

where job = 'MANAGER')

D. All of the above

E. None of the above

( Correct Answers: B )

22. Which query will return the job with the lowest average salary (choose one)?

A. SELECT job, min(avg(sal))

FROM emp

GROUP BY job;

B. SELECT job, sal

FROM emp

WHERE sal <=

(SELECT min(avg(sal))

FROM emp

GROUP BY job);

C. SELECT job, avg(sal)

FROM emp

GROUP BY job

HAVING avg(sal) =

(SELECT min(avg(sal))

FROM emp

GROUP BY job);

D. SELECT job, min(avg(sal))

FROM emp

WHERE sal = (SELECT min(avg(sal))

FROM emp

GROUP BY job);

( Correct Answers: C )

23. What is wrong with this select statement (choose three)?

SELECT ename 'Employee Name' + " works in department " + deptno

FROM emp;

A. There is no join criteria

B. 'Employee Name' should not be in single quotes

C. The concatenation operator is incorrect

D. "works in department" should not be in double quotes

E. Nothing, this statement is correct

Answer: B,C,D

Source: 1-17, 1-21

24. Given the following:

empno ename sal bonus

_____________________________

5123 SMITH 800 100

5124 JONES 900

5125 DONNER 500 50

What will result from the SQL query: SELECT ename, sal * 12 + bonus

WHERE empno = 5124 (choose one);

A. ENAME SAL*12+BONUS

_________________________

JONES 10800

B. ORA-ERROR

C. no rows selected

D. ENAME SAL*12+BONUS

_________________________

JONES

E. ENAME SAL*12+BONUS

________________________

JONES 0

Answer: D

Source: 1-15

25. Which of the following commands creates a file containing output from an SQL*Plus session (choose one)?

A. SAVE filename

B. SPOOL filename

C. SCRIPT filename

D. SEND >> filename

E. @filename

Answer: B

Source: 1-32

26. Which of the following SQL statements lists each department, at most, only once (choose three)? (deptno is the primary key of the dept table, and a foreign key of the emp table)

A. SELECT deptno FROM emp;

B. SELECT deptno FROM dept;

C. SELECT distinct deptno FROM dept;

D. SELECT distinct deptno FROM emp;

E. all of the above

Answer: B,C,D

Source:1-23

27. Given the following emp table and SQL query

empno ename sal

_______________________________

1 SEBASTIAN 100

2 SAM 100

3 SALLY 120

SELECT ename, sal + ( decode(length(ename),3,20,4,25,5,30,0)) "new salary"

FROM emp;

What will "new salary" for Sebastian be (choose one)?

A. 100

B. 103

C. 135

D. 130

E. 120

Answer: A

Source: 3-39

28. Which of the following formats will produce the date

Saturday, the fifth day of March (choose one)?

A. 'DD, "the" DTH "day of" MON'

B. 'DY ", the " D "day of" MONTH'

C. 'fmDAY, "the" DDspth "day of" fmMONTH'

D. 'DY, "the" Dspth "day of" MONTH'

E. 'DD, "the" DDTH "day of" MM'

Answer: C

Source: 3-30, 3-31

29. Given the statement: SELECT TO_CHAR(sal,’$9,999’) SALARY

FROM emp

WHERE ename = 'SCOTT’';

What happens if SCOTT makes $10,000 (choose one)?

A. An ORACLE error is generated

B. His salary will show as #######

C. His salary will be truncated to $1,000

D. The field will be automatically extended to handle the extra digit

E. The salary field will be left blank

Answer: B

Source: 3-34

30. Which of the following Date functions produces a date with the year 1995 (choose three)?

A. ROUND(to_date('25-JUL-95','DD-MON-YY'),'MONTH')

B. ROUND(to_date('25-JUL-95','DD-MON-YY'),'YEAR')

C. TRUNC(to_date('25-JUL-95','DD-MON-YY'),'MONTH')

D. TRUNC(to_date('25-JUL-95','DD-MON-YY'),'YEAR')

E. TRUNC(to_date('25-JUL-94','DD-MON-YY'),'YEAR')

Answer: A,C,D

Source: 3-22

31. Which of the statements below selects names in which the 3rd character is a vowel (a,e,i,o,u) (choose three) ?

A. SELECT decode(upper( substr(ename,3,1)), 'A',ename,

'E',ename,

'I',ename,

'O',ename,

'U',ename) FROM emp;

B. SELECT ename FROM emp WHERE upper(substr(ename,3,1)) like 'A' or

upper(substr(ename,3,1)) like 'E' or

upper(substr(ename,3,1)) like 'I' or

upper(substr(ename,3,1)) like 'O' or

upper(substr(ename,3,1)) like 'U';

C. SELECT ename FROM emp WHERE upper(ename) like '%A%' or

'%E%' or

'%I%' or

'%O%' or

'%U%';

D. SELECT ename FROM emp WHERE upper(substr(ename,3,1)) like '__A%' or

upper(substr(ename,3,1)) like '__E%' or

upper(substr(ename,3,1)) like '__I%' or

upper(substr(ename,3,1)) like '__O%' or

upper(substr(ename,3,1)) like '__U%' ;

E. SELECT ename FROM emp WHERE upper(ename) like '__A%' or

upper(ename) like '__E%' or

upper(ename) like '__I%' or

upper(ename) like '__O%' or

upper(ename) like '__U%';

Answer:A,B,E

Source: 2-13,3-11,3-41

32. Which of the following statements will get a count of every employee (5) in the emp table below (choose three)?

empno(primary key) ename deptno sal

______________________________________________________________

101 Washington 10

102 Lincoln 10 100

103 Roosevelt 20 150

104 Jackson 30 200

105 Roosevelt 50 200

A. SELECT count(*) FROM emp;

B. SELECT count(distinct empno) FROM emp;

C. SELECT count(all deptno) FROM emp;

D. SELECT count (distinct deptno) FROM emp;

E. SELECT count() FROM emp;

Answer: A,B,C,

Source: 5-4

33. Which is a true statement about the "having clause" (choose one)?

A. It is used to restrict rows

B. It is used to restrict groups

C. It is used to restrict columns

D. It must contain all non-group values in the select clause

E. None of the above

Answer: B

Source: 5-21

34. The select clause, "SELECT deptno, avg(sal), count(deptno)", requires (choose two):

A. A FROM clause

B. A GROUP BY clause

C. A HAVING clause

D. An ORDER BY clause

E. All of the above

Answer: A,B

Source: 5-13,1-4

35. Which of the following are group functions (choose three)?

A. count

B. round

C. min

D. length

E. avg

Answer: A,C,E

Source: 5-4

36. Which of the following statements displays the average salary for managers, by department, in order of increasing department number (choose one)?

A. SELECT avg(sal)

FROM emp

GROUP BY deptno

HAVING job = 'MANAGER'

ORDER BY deptno asc;

B. SELECT avg(sal)

FROM emp

WHERE job='MANAGER'

GROUP BY deptno

ORDER BY deptno;

C. SELECT avg(sal)

FROM emp

WHERE job == 'MANAGER'

ORDER BY deptno;

D. SELECT avg(sal)

FROM emp

GROUP BY job

HAVING job = 'MANAGER'

E. None of the above

Answer: B

Source: 5-13

37. Which of the following statements about multiple column subqueries are false (choose three)?

A. Can be in a where clause

B. Can be in a From Clause

C. Can only be in a From Clause

D. May be used in a where clause only for pairwise comparisons

E. Compares several columns but returns only one value

Answer: C,D,E

Source: 7-3,7-11

38. Which of the following queries correctly lists all employees who have no

subordinates (choose two)?

A. SELECT employee.ename

FROM emp employee

WHERE employee.empno NOT IN

(SELECT manager.mgr

FROM emp manager);

B. SELECT employee.ename FROM emp employee

WHERE employee.empno NOT IN

(SELECT manager.mgr

FROM emp manager

WHERE mgr IS NOT NULL);

C. SELECT employee.ename FROM emp employee

WHERE employee.empno NOT EXISTS

(SELECT manager.mgr

FROM emp manager);

D. SELECT employee.ename FROM emp employee, emp manager

WHERE employee.empno <> employee.mgr ;

E. None of the above

Answer: B,C

Source: 7-9, lecture

39. Which of the statements below describe the following query (choose two)?

SELECT empno, deptno, sal

FROM emp

WHERE deptno IN (SELECT deptno

FROM emp

WHERE empno = 1015)

AND

sal IN (SELECT sal

FROM emp

WHERE empno = 1015)

AND empno <> 1015;

A. Pairwise column comparison

B. Non- pairwise column comparison

C. Single row

D. Multiple column

E. None of the above

Answer: B,D

Source: 7-3,7-7

40. Which of the following statements are true (choose two)?

A. 'NOT IN' is equivalent to != ALL

B. 'IN' is equivalent to != ALL

C. 'NOT IN' is equivalent to =ANY

D. 'IN' is equivalent to =ANY

E. None of the above are true

Answer: A,D

Source: 7-9