Total Pageviews

February 19, 2015

2/19/2015 08:15:00 PM
50.  Mrs. Jensen is president of her company.  Four managers report to her, and all other employees report to the four managers.  Examine the following:

SELECT worker.wname
FROM work worker
WHERE worker, workno not in
SELECT manager.mgr
FROM work manager;

The above statement returns no rows.  Why?

a.     All employees have a manager.
b.     None of the employees have a manager.
c.     A null value is returned from the subquery.
d.     An operator is not allowed in subqueries.

51.  Which of the following statements regarding column subqueries is true?

a.     A pair wise comparison produces a cross product.
b.     A non-pair wise comparison produces a cross product.
c.     In a pair wise query, the values returned from the subquery are compared individually to the value in the outer query.
d.     In a non-pair wise query, the values returned from the subquery are compared as a group to the values in the outer query.

52.  You query a database with the following command:

SELECT dept_no,  AVG (MONTHS_BETWEEN (SYSDATE, hire_date))
FROM     worker
WHERE  AVG (MONTHS_BETWEEN (SYSDATE, hire_date))   >  60
GROUP  BY  dept_no
ORDER BY   AVG (MONTHS_BETWEEN (SYSDATE, hire_date) )

Where does the statement cause an error?

a.     A SELECT clause cannot contain a group function.
b.     A WHERE clause cannot be used to restrict groups.
c.     An ORDER BY clause cannot contain a group function.
d.     A group function cannot contain a single row function.

53.  A group function ______.

a.     Produces a group of results from each row.
b.     Produces one result from each row of a table.
c.     Produces one result from many rows per group.
d.     Produces many results from many rows per group.

54.  The worker table contains the following columns:

ID_NUMBER(9)                 PK
LAST_NAME                      VARCHAR2(25) NN
DEPT_ID                              NUMBER(9)

Evaluate the following:

DEFINEid_3=93011
SELECT
FROM    worker
WHEREid = (% id_3)

Which change should be made to the script so that it will execute?

a.     Remove the ampersand.
b.     Use the ACCEPT account.
c.     Close the cursor.
d.     No change is needed.

55.  Evaluate the following:

SELECT                w.id,  (.15* w.salary) + (.25* w.bonus))
                                (w.sale_amount * (.15* w.commision_pct))
FROM                    worker w , sales
WHERE                w.id = s.worker_id;

What would be the result of removing all parentheses from the calculation?

a.     Results will be higher.
b.     Results will be lower.
c.     The results will be the same.
d.     The statement will not execute.

56.  Which of the following is not an SQL Plus command?

a.     DESCRIBE
b.     UPDATE
c.     CHANGE
d.     ACCEPT

57.  When selecting data, projection ______.

a.     Allows you to choose rows.
b.     Allows you to choose columns.
c.     Allows you to join tables together.
d.     Allows you to add columns to a table.

58.  The worker table contains the following columns:

ID                                           NUMBER(9)
LAST_NAME                      VARCHAR2(25)
FIRST_NAME                     VARCHAR2(25)
COMMISSION                    NUMBER(7,2)

You must display commission calculations for employees, and are given the following guidelines:

·          
·         +Display commission multiplied by 1.5.
·         Exclude employees with zero commission.
·         Display a zero for employees with null commission value.

Evaluate the following statement:

SELECT                id, last_name, first_name, commission*1.5
FROM                    worker
WHERE                                commission <>0;

How many guidelines are met by the statement?

a.     3
b.     2
c.     1
d.     The statement generates an error.

59.  Click EXHIBIT and examine the trace instance chart for the worker table (exhibit not available).  What SQL statement must be used to display each worker hire date from earliest to last?

a.     SELECT                        hire_date
FROM                            worker;
b.     SELECT                        hire_date
FROM                            worker
ORDER BY                  hire_date;
c.     SELECT                        worker
FROM                            worker
ORDER BY                  hire_date;
d.     SELECT                        hire_date
FROM                            worker
ORDER BY                  hire_date DESC;

60.  The division table is structured as follows:

Name                     Null                         Type
DIVISIONNO       NOT NULL           NUMBER(2)
DNAME                                                                VARCHAR2(14)
LOC                                                       VARCHAR2(13)

Examine the following declaration:

DECLARE
TYPE     division_table_type  IS TABLE OF division &ROWTYPE
INDEX BY BINARYINTEGER
division_table       division_table_type;

You need to assign LOC file in record 13 of value ‘Boston’.  Which of the following statements should be used?

a.     division_table.loc.13                   :=                            ‘Boston’;
b.     division_table[13].loc                 :=                            ‘Boston’;
c.     division_table(13).loc                 :=                            ‘Boston’;
d.     division_table_type(13).loc       :=                            ‘Boston’;







 
Related Posts Plugin for WordPress, Blogger...