Total Pageviews

March 2, 2015

3/02/2015 10:47:00 PM
Oracle SQL Questions


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.



 
Related Posts Plugin for WordPress, Blogger...