728x90 AdSpace

Latest Article

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

no image
  • Title : Exam Sample Question for Oracle Database and SQL Query
  • Posted by :
  • Date : 06:56
  • Labels :





  • Blogger Comments
  • Facebook Comments

0 comments:

Post a Comment

Top