Total Pageviews

March 2, 2015

3/02/2015 09:17:00 AM
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.

3.        Examine the patient table:

Column name                 id_number              last_name               first_name            birth_date     Physician_id
Key type                          PK                                                                                                                      
Nulls/Unique                   NN, U                       NN                            NN                                                 
FK table                                                                                                                                                       PHYSICIAN
FK column                                                                                                                                                   ID_NUMBER
Data type                         NUM                        VARCHAR2          VARCHAR2        DATE             NUM
Length                              10                             25                             25                                                   10

You must create the patient_id_seq sequence to be used with the patient table’s primary key column.  The sequence will begin with 1000, have a maximum value of 9999999, not reuse numbers, and increment in quantities of 1.  Which of the following statements will accomplish the task?

a.     CREATE SEQUENCE patient_id_seq
START WITH 1000
MAXVALUE 9999999
NO CYCLE:
b.     CREATE SEQUENCE patient_id_seq
START WITH 1000
MAXVALUE 9999999
STEP BY 1;
c.     CREATE SEQUENCE patient_id_seq
ON PATIENT(patient_id)
MINVALUE 1000
MAXVALUE 9999999
INCREMENT BY 1
NO CYCLE;
d.     This cannot be done.

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.