1. A group function produces ______.
a. A group of results from one row.
b. One result from each row in a table.
c. Many results from many rows per group.
d. One result from many rows per group.
2. Examine the structure of the division and worker tables below:
DIVISION
id PK Name
WORKER
id PK Last_name First_name Divion_id
Evaluate the following statement:
CRATE INDEX worker_division_id_idx
ON worker(divison_id);
What will be the result of the statement?
a. Store and index the worker table.
b. Increase the chance of full table scans.
c. Reduce disk I/O for SELECT statements.
d. Reduce disk I/O for INSERT statements.
4. You issue the following command:
CREATE SYNONYM work
FOR ed.employee;
Because of the command, the need to qualify an object name with its schema has been eliminated for ______.
a. All users.
b. Only yourself.
c. User Ed.
d. Users with access.
5. You must create a report that gives, per division, the number of workers and total salary as a percentage of all divisions. Examine the results of the report:
DIVISION %WORKERS %SALARY
10 21.4 30.15
20 35.71 37.47
30 42.86 32.39
Which of the following SELECT statements will produce the above report?
a. SELECT divsionno “division”,
(COUNT(*)/count(workno))* 100 “%workers”,
(SUM(sal)count(*))* 100 “%salary”
FROM scott.work GROUP BY divisiono;
b. SELECT divisionno “division”,
PCT(workno) “%workers”,
PCT(sal) “%salary”
FROM scott.work
GROUP BY divisionno;
c. SELECT a.divisionno “division”,
(a.num_work/COUNT(*))* 100 “%workers”,
(a.sal_sum/COUNT(*))*100 “%salary”.
FROM
(SELECT divisionno,COUNT(*)num_work,SUM(SAL)sal_sum
FROM scott.work
GROUP BY divisionno)a;
d. SELECT “division”,
a.divisionno.
ROUND(a.num_work/b.total_count * 100,2)“%workers”
ROUND(a.sal_sum/b.total_sal * 100,2) “%salary%
FROM
(SELECT divisionno,COUNT(*)num_work,SUM(SAL)sal_sum
FROM scott.work
GROUP BY divisionno)b;
6. In which situation would an outer query be used?
a. The worker table has two columns that correspond.
b. The worker table column corresponding to the region table contains null values for rows that need to be displayed.
c. The worker and region tables have no corresponding columns.
d. The worker and region tables have corresponding columns.
7. The worker table has three columns:
LAST_NAME VARCHAR2(23)
FIRST_NAME VARCHAR2(23)
SALARY NUMBER(7,2)
Your manager requests that you write a statement to display all workers earning more than the average salary of all workers. Evaluate the following SQL statement:
SELECT last_name
FROM worker
WHERE salary > AVG(salary);
What change should be made to the statement?
a. Move the function to the SELECT clause and add a GROUP BY clause.
b. Use a sub query in the WHERE clause to compare the salary value.
c. Change the function in the WHERE clause.
d. The statement requires no modification.
8. You attempt to query the worker database with the following command:
SELECT name,salary
FROM worker
Where salary=
(SELECT salary
FROM worker
WHERE last_name= ‘Johnson’ OR dept_no=43)
The statement will cause an error because ______.
a. Sub queries cannot be used with the WHERE clause.
b. A multiple-row sub query has been used with a single row comparison operator.
c. A single row query has been used with a multiple-row comparison operator.
d. Logical apparatus are not allowed in the WHERE clause.
9. Which statement will provide the view definition of the work_view that is created based on the worker table?
a. Describe work
b. DESCRIBE view work_view
c. SELECT TEXT
FROM user_views
WHERE view_name= ‘WORK_VIEW’;
d. SELECT view_text
FROM my_views
WHERE view_name= ‘WORK_VIEW’’
10. Examine the structure of the movie title, copy, and check_out tables:
MOVIE
IdPK Title Director
COPY
IdPK Title id PK Available
CHECK_OUT
IdPK Copy_id Title_id Check_out_date Expected_return_date Customer-id
You need to create the MOVIES_AVAILABE view, and have the following parameters:
· Include the title of each movie.
· Include the availability of each movie.
· Order the results by director.
Evaluate the following statement:
CREATE VIEW movies_available
AS
SELECT b.title,c.available
FROM movie_title b,copy c
WERE b.id=c.title_id
ORDER BY b.director;
Which of the parameters are met?
a. All
b. Two
c. One
d. A syntax error results.