Exam Sample Question for Oracle Database and SQL Query

Online Exam Sample Question for Oracle Database and SQL Query Midterm Final its Helpfull for preparation

1.                  The TEACHER table contains these columns:

ID                     NUMBER(9)

SALARY           NUMBER(7,2)

SUBJECT_ID   NUMBER(3)

You need to create a SQL script that will prompt the user to input an id number and a percent increase value.  Each teacher's salary should be multiplied by the percent increase provided.

Which SQL*Plus script would you use to achieve the desired results?

A.                UPDATE      teacher

SET              salary = salary * &increase;

B.                 UPDATE      teacher

SET              salary = salary * &increase

WHERE       id = &id;

C.                 UPDATE      teacher

SET              salary = &salary

WHERE       subject_id = &subject_id

D.                UPDATE      teacher

SET              salary = salary * &increase

WHERE       subject_id = &subject_id

2.                  The PERSONNEL table contains these columns:

ID                        NUMBER(9)

LAST_NAME      VARCHAR2(25)

FIRST_NAME     VARCHAR2(25)

MANAGER_ID    NUMBER(9)

For this example, department managers are personnel.

Evaluate these two SQL statements:

SELECT      p.last_name, p.first_name, m.last_name, m.first_name

FROM         personnel p, personnel m

WHERE     m.id = p.manager_id;

SELECT     p.last_name, p.first_name, m.last_name, m.first_name

FROM        personnel p, personnel m

WHERE     m.manager_id = p.id;

How do the two SQL statements differ?

A.    One of the statements will not execute.

B.     One of the statements is not a self-join.

C.                 The results of the statements will be the same, but the format will be different.

D.                The results of the statements will be different; but the display will be the same.

3.                  The STUDENT table contains these columns:

ID                       NUMBER(9)                     PK

LAST_NAME     VARCHAR2(25)

FIRST_NAME    VARCHAR2(25)

SUBJECT_ID     NUMBER(9)

Compare these two SQL statements:

1.  SELECT         DISTINCT subject_id, last_name, first_name

     FROM            student

     ORDER BY    1;

2.  SELECT         id, last_name, first_name, subject_id

     FROM            student

     ORDER BY    subject_id;

How will the results differ?

A.                Statement 1 will be sorted alphabetically; statement 2 will not.

B.                 Statement 1 will limit duplicate subject ids; statement 2 will not.

C.                 Statement 1 will not eliminate duplicate rows from the output; statement 2 will.

D.                Statement 2 will display distinct combinations of the values in the STUDENT table; statement 1 will not.

4.                  Evaluate this command:

     CREATE FORCE VIEW id_number_description

     AS SELECT   id_number "Product Number", description

     FROM            inventory

     WHERE         price > 5.00

     GROUP BY    description

     ORDER BY    id_number;

Which clause will cause an error?

A.                FROM inventory

B.                 WHERE price > 5.00

C.                 ORDER BY id_number;

D.                GROUP BY description

E.                 AS SELECT id_number "Product Number", description.

5.                  Which SQL statement creates the PARTS_456874_VU view that contains the ID_NUMBER, DESCRIPTION, and QUANTITY columns for MANUFACTURER_ID 456874 from the INVENTORY table and does not allow the manufacturer values to be changed through the view?

A.                CREATE VIEW parts_456874_vu

AS SELECT      id_number, description, quantity

FROM               inventory

WITH CHECK CONSTRAINT;

B.                 CREATE VIEW parts_456874_vu

AS SELECT   id_number, description, quantity

FROM            inventory

HAVING         manufacturer_id = 456874

WITH READ ONLY;

C.                 CREATE VIEW parts_456874_vu

AS SELECT    id_number, description, quantity

FROM             inventory

WHERE          manufacturer_id = 456874

WITH READ ONLY;

D.                CREATE VIEW parts_456874_vu

AS SELECT     id_number, description, quantity

FROM              inventory

WHERE           manufacturer_id = 456874

WITH CHECK OPTION;

6.                  You attempt to query the database with this command:

     SELECT          i.id_number, m.id_number

     FROM             inventory i, manufacturer m

     WHERE          i.manufacturer_id = m.id_number

     ORDER BY     inventory.description;

Which clause causes an error?

A.          ORDER BY inventory.description;

B.           FROM inventory i, manufacturer m

C.           WHERE i.manufacturer_id = m.id_number

D.          SELECT i.id_number, m.manufacturer_name

7.                  The STUDENT table contains these columns:

ID                                 NUMBER(9)

FIRST_NAME              VARCHAR2(25)

LAST_NAME                VARCHAR2(25)

Evaluate this SQL statement:

SELECT       *

FROM          student

WHERE       id =

                               (SELECT id

                                FROM    student

                                WHERE           UPPER(last_name) = 'SHEHZAD')

                                AND                 UPPER(first_name) = 'IRFAN');

What would cause this statement to fail?

A.                There are no students named IRFAN SHEHZAD.

B.                 There is more than one student named SHEHZAD.

C.                 There is more than one student named IRFAN SHEHZAD.

D.                The FIRST_NAME  and LAST_NAME values in the database are in lowercase.

8.                  Evaluate this command:

     SELECT       id_number, description, SUM(price)

     FROM          inventory

     WHERE       price > 6.00

     GROUP BY  id_number

     ORDER BY  manufacturer_id;

Why will this command cause an error?

A.    The PRICE column must be included in the GROUP BY clause.

B.     The ORDER BY clause should immediately follow the WHERE clause.

C.                 The MANUFACTURER_ID column is not included in the SELECT clause.

D.                The ORDER BY clause cannot be used in a SELECT statement with a GROUP BY clause.

E.                 The DESCRIPTION and MANUFACTURER_ID columns are not included in the GROUP BY clause.

9.                  You attempt to query the database with this command:

     SELECT         inventory.id_number, manufacturer.id_number

     FROM            inventory i, manufacturer m

     WHERE         i.manufacturer_id = m.id_number

     ORDER BY    1;

Which clause causes an error?

A.                ORDER BY 1;

B.                 FROM inventory i, manufacturer m

C.                 WHERE i.manufacturer_id = m.id_number

D.                SELECT inventory.id_number, manufacturer.id_number

10.              Evaluate this PL/SQL block:

     DECLARE

          v_quota         BOOLEAN := NULL;

          v_stock         BOOLEAN := NULL;

          v_approval    BOOLEAN;

     BEGIN

          v_approval := v_quota AND v_stock;

     END;

Which value is assigned to the V_APPROVAL?

A). TRUE             B). NULL                   C). FALSE                  D). NONE

1.A database server is a server that:

A).              hosts application logic and services for an information system, that must communicate on the front end with clients, and on the back end with database servers.

B).              hosts one or more shared databases but also executes all database commands and services for information systems.

C).              hosts services for e-mail, calendaring, and other work group functionality.

D).              hosts services that ultimately ensure that all database updates for a single business transaction succeed or fail as a whole.

E).               none of the above

2.1. Create table 1234

        (Empno number);

        Create Table A1234

   (Empno number);

A.                Statement 1 will execute successfully.

B.                  Statement 2 will execute successfully.

C.                 Both will not execute.

D.                Both will execute successfully.

3.For which condition would you use an equijoin query?

A.                The INVENTORY table has two corresponding columns.

B.                 The INVENTORY and MANUFACTURER tables have corresponding columns.

C.                 The INVENTORY and MANUFACTURER tables do not have any corresponding columns.

D.                The INVENTORY and MANUFACTURER tables have corresponding columns, but the column in the INVENTORY table contains null values that need to be displayed.

4.Which statement would you use to query the database for the ID_NUMBER and DESCRIPTION values of each item that was ordered before January 1, 1997 and whose price is less than 1.00 or greater than 5.00?

A.                ELECT   id_number, description

FROM      inventory

WHERE   price IN (1.00, 5.00)

OR           order_date < '01-JAN-97';

B.                 SELECT   id_number, description

FROM      inventory

WHERE  price BETWEEN 1.00 AND 5.00

OR          order_date < '01-JAN-97';

C.                 SELECT     id_number, description

FROM        inventory

WHERE    price < 1.00

OR            price > 5.00

AND          order_date < '01-JAN-97';

D.                SELECT     id_number, description

WHERE    (price <1.00 OR price > 5.00)

AND          order_date < '01-JAN-97';

FROM        inventory

5.Evaluate this command:

     SELECT            id_number "Part Number", SUM(price) "Price"

     FROM               inventory

     WHERE            price > 5.00

     GROUP BY       "Part Number"

     ORDER BY       2;

Which clause will cause an error?

A.                ORDER BY 2;

B.                 FROM inventory

C.                 WHERE price > 5.00

D.                GROUP BY "Part Number"

E.                 SELECT id_number "Part Number", SUM(price) "Price"

6.You attempt to query the database with this command:

     SELECT 100/NVL(quantity, 0)

     FROM     inventory;

Why does this statement cause an error when QUANTITY values are null?

A.                The expression attempts to divide by zero.

B.                 The expression attempts to divide by a null value.

C.                 The datatypes in the conversion function are incompatible.

D.                A null value used in an expression cannot be converted to an actual value.

7.Evaluate this command:

     SELECT       id_number

     FROM          inventory

     WHERE      manufacturer_id IN

          (SELECT    manufacturer_id

           FROM       inventory

           WHERE    price < 1.00

           OR            price > 6.00);

How many values can the subquery return?

A). 0    B). ONLY 1               C). UP TO 2                D). UNLIMITED

8.You issue this command:

     GRANT update

     ON inventory

     TO joe

     WITH GRANT OPTION;

Which task has been accomplished?

A.                Only a system privilege was given to user JOE.

B.                 Only an object privilege was given to user JOE.

C.                 User JOE was granted all privileges on the object.

D.                Object privilege and a system privilege were given to user JOE.

9.You attempt to query the database with this command:

     SELECT         inventory.id_number, manufacturer.id_number

     FROM            inventory i, manufacturer m

     WHERE         i.manufacturer_id = m.id_number

     ORDER BY    1;

Which clause causes an error?

A.                ORDER BY 1;

B.                 FROM inventory i, manufacturer m

C.                 WHERE i.manufacturer_id = m.id_number

D.                SELECT inventory.id_number, manufacturer.id_number