Exam 2 Concept Review

Database Programming – Exam 2 Concept Review

Section 5 Lesson 4 to Section 9 Lesson 1

1. Group functions return a value for (each row / a row set) and (include / ignore) null values in their computations. They can avoid computations involving duplicate values by including the key word __DISTINCT______as an argument.

2. Which group functions below act on text, number and date data types?

a. SUM

b. MAX

c. MIN

d. AVG

e. COUNT

3. If a select list contains both a column as well as a group function then what clause is required?

a. having clause

b. join clause

c. order by clause

d. group by clause

4. (True / False) Both the order by and group by clauses can contain columns not found in the select list of their query.

5. Indicate why the query below will not execute successfully. (There may be more than one reason). Format the code according to conventions so it will execute.

Select * from employees

where job_id =

Select job_id from employees where department_id = 90 and (select salary from employees where emp_id = 101) > salary;

First subquery not enclosed in parenthesis and single rather than multiple value operator used. Second subquery found on the right instead of the left side of the operator.

SELECT *

FROM employees

WHERE job_id IN

(SELECT job_id

FROM employees

WHERE department_id = 90)

AND salary >

(SELECT salary

FROM employees

WHERE emp_id = 101)

Indicate why the following query will not execute successfully. (There may be more than one reason.) Format the code according to conventions so it will execute.
Select job_id, avg(salary) from employees where avg(salary) > (select avg(salary) from employees group by department_id) group by job_id;

A group function cannot be contained within the where clause. The operator for the subquery in the where clause needs to be a multiple value operator such as >any.

Possible Answer:

SELECT job_id,AVG(salary)

FROM employees

WHERE salary IN

(SELECT AVG(salary)

FROM employees

GROUP BY department_id)

GROUP BY job_id

7. In using the INSERT statement, if a column list is not provided then what values must be included?

a. a value for any column defined as not null.

b. a value for any column that does not have an explicit default value.

c. a value for every column in the table in the correct order.

d. only values for column(s) that make up the primary key.

8. (True / False ) Multiple subqueries may be used in SELECT, INSERT and UPDATE statements, but not DELETE statements.

9. You wish to change an existing department_id to a new department_id that does not exist. You also want to delete the old department id. There is a foreign key for department_id on your employees table that references the primary key in the departments table. What DML operations are needed?

a. An insert to departments, an update on employees, a delete on departments

b. An insert to employees, an update on departments, a delete on employees.

c. An update to the departments table followed by a merge to the employees table.

d. an update to the departments table and an update to the employees table.

10. When the WHERE clause is missing in a delete statement then: (choose all true answers)

a. all rows will be deleted from the table.

b. the table will be removed from the database.

c. rows can be restored using the RESTORE command

d. an error message will be displayed indicating incorrect syntax.

11. Implicit defaults will be used in the INSERT clause for columns not found in the COLUMN __ list. To assign a default value explicitly for columns in the values clause, you must use the key word _DEFAULT______.

Which keywords are used in DML statements?

a. create, alter, drop

b. insert, update, delete

c. select, from, where

d. rename, truncate, delete

A transaction can be defined as:

a. a SELECT statement ordering rows

b. a set of data control commands

c. collection of DML statements that form a logical unit of work.

d. any set of SQL statements

To add a row to a table, you use the

a. insert statement

b. add row statement

c. alter row statement

d. modify column statement

When adding a row to a table, you must specify 3 things in the insert statement:

a. datatype, table name, alias

b. row number, column name, table primary key

c. table name, column name and values

d. input table, primary key, alias

16. Which of the following would be found in an explicit insert statement.

a. no column list is present

b. no values list is present

c. NULL keyword appears in the values list

d. NULL is not allowed in an explicit insert statement

17.To insert the current date and time, use:

TO_DATE
TO_CHAR
CURRENT_DATE

d. SYSDATE

18. You can insert rows from one table to another using:

a. UPDATE

b. INSERT row

c. modify table

d. subquery

19. If you omit the WHERE clause in an UPDATE statement:

a. no rows are changed

b. all rows are changed

c. the query will not execute

d. an error message will appear

20. If you include a group function in a SELECT clause, which of the following is true?

a. the function will only return one row

b. you must include all non group function columns in the SELECT clause in a GROUP BY clause

c. group functions are not put in the SELECT clause

d. the query will generate an error

21. Which of the following is true?

a. GROUP BY clauses can only list one column name

b. The column names in a GROUP BY clause must have an alias

c. Group functions must be in the WHERE clause

d. The GROUP BY column does not have to be in the SELECT list.

22. T/F HAVING clauses are used to restrict rows being selected.

23. T/F A group function that finds the average will ignore NULL values

24. To change a table, you must specify 4 things:

a. select clause, column alias, from clause

b. conditions, values, data types, table constraints

c. table name, row names, insert statement

d. table, columns, values, conditions.

25. Subqueries can be used in an update statement in 2 places:

a. set clause and where clause

b. from clause and the where clause

c. select clause and the from clause

d. update clause and the where clause

26. T/F To delete rows from an existing table, you must use the DROP statement.

27. Which of the following are NOT data types?

a. SNOB

b. VAR2

c. VARCHARS

d. TIMESHARE

28. Which statement below is used to add, modify or drop columns?

a. UPDATE

b. INSERT

c. DROP

d. ALTER

29. If you want to remove all rows from a table to release the storage space the best statement to use is: