728x90 AdSpace

Latest Article

SQL MCQ Exam Question Using Oracle Database

1.                  Which cursor attribute evaluates to TRUE if the most recent SQL statement affects one or more rows?

A). SQL%FOUND.         B) .SQL%ISOPEN.          C).SQL%ROWCOUNT.
            D). SQL%NOTFOUND.

2.                  Evaluate this PL/SQL block:

          v_quota         BOOLEAN := TRUE;
          v_stock         BOOLEAN :=  NULL;
          v_approval    BOOLEAN;
          v_approval := v_quota AND v_stock;

Which value is assigned to the V_APPROVAL?

      A). TRUE                B). Null                C). False                   D).None

3.                  Which character function converts mixed case character strings to lowercase?

A). NVL               B). UPPER     C). LOWER    D). LENGTH
E). SUBSTR         F). INITCAP

4.                  The TEACHER table contains these columns:

ID                     NUMBER(9)
SALARY           NUMBER(7,2)

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

5.                  Which characteristic relates to a single row function?
A.                Can be nested.
B.                 Can only be used in a WHERE clause.
C.                 May return more than one result per row.
D.                Can use a column position as an argument.

6.                  Which characteristic applies to an implicit cursor?

A.                Will process only one row.
B.                 Will attempt only one fetch.
C.                 Allows the programmer to control the number of fetches performed.
D.                Will perform only one fetch and will process all of the rows returned by the query.

7.                  Which two clauses must be included in a SELECT statement? (Choose two.)

A). FROM                  B). WHERE                C). SELECT
D). HAVING             E). ORDER BY          F). GROUP BY

8.                  Which operator would it be most appropriate to use with a single row subquery?

A). =                B). IN              C). LIKE                     D). BETWEEN

9.                  Which PL/SQL section contains SQL statements to manipulate data in the database?

A). Header      B). Exception              C). Executable                        D). Declarative.

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

     SELECT NVL (100 / quantity, none)
     FROM    inventory;

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

A.                The expression attempts to divide by a null value.
B.                 The datatypes in the conversion function are incompatible.
C.                 The character string none should be enclosed in single quotes (' ').
D.                A null value used in an expression cannot be converted to an actual value.

11.              You issue this command:

     CREATE FORCE VIEW parts_vu
          (company, contact)
     AS SELECT     manufacturer_name, contact_name
     FROM              inventory

Which command can be issued on the PARTS_VU view?

A). UPDATE        B). DELETE   C). SELECT               D). INSERT

12.              You logged onto the database to update the INVENTORY table.  After your session began, you issued three UPDATE commands and then you issued an ALTER table command to add a column constraint.  You were about to issue a COMMIT command when the system crashed.  Which changes were made to the INVENTORY table?

A.                Only the UPDATE commands.
B.                 Only the ALTER TABLE command.
C.                 both the UPDATE commands and the ALTER TABLE command
D.                NONE.

13.              On which two objects can a view be based? (Choose two.)

A). VIEW                   B). QUERY                            C). TABLE    

14.              Which privilege is an object privilege?

A). INDEX                 B). DROP USER              C). CREATE SESSION

15.              Which guideline relates to the GROUP BY clause?

A). Are in ascending order                 B). Can use a column alias.
C). Can use a positional notation.       D). Can be followed by a WHERE clause.

16.              Which type of variable has a colon (:) prefix in a PL/SQL statement?

A.                declared PL/SQL variable
B.                 SQL*Plus global variable
C.                 PL/SQL function variable
D.                PL/SQL procedure variable

17.              Evaluate this SQL script:

CREATE ROLE hr_director;
GRANT hr_director TO hr;
GRANT SELECT ON teacher TO hr_director;
How many users are granted the HR_DIRECTOR role and how many privileges are granted to the HR_DIRECTOR role?
A.                1 user and 1 privilege
B.                 1 user and no privileges
C.                 no users and 1 privilege
D.                no users and no privileges

18.              In which section of a PL/SQL block could a new value be assigned to an initialized variable?

     A). END                B). HEADER             C). EXECUTEABLE
     D). DECLARATIVE                                E). EXCEPTION HANDLING

19.              Evaluate this PL/SQL loop:

WHILE v_price < &&p_count LOOP
v_amount := v_amount + 1;
v_price := v_amount * &&p_pct_increase;

If V_PRICE is 45,  P_COUNT is 75, and V_AMOUNT is 5, which P_PCT_INCREASE value would cause the loop to terminate?

A). 0                      B). 15              C). 20              D). 45              E). 75

20.              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.

21.              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"

22.              For which task would you use the TO_DATE function?

A.    to convert a number value to a date value
B.     to convert a date value to a number value
C.     to convert a date value to a VARCHAR2 character string
D.    to convert a character string representing a date to a date value

23.              You query the database with this command:

     SELECT          id_number, description, price "Cost Per Unit"
     FROM             inventory
     WHERE          price > 5.00
     ORDER BY     "Cost Per Unit";
How is the data sorted?

A). Randomly             B). ascending numerically       C). descending numerically
D). ascending alphabetically               E). descending alphabetically

24.              Evaluate this command:

     SELECT         COUNT(*)
     FROM             inventory
     WHERE         price > 5.00
     GROUP BY    manufacturer_id
     HAVING         COUNT(*) > 10
     ORDER BY    order_date;

Which clause specifies which rows will be returned from the INVENTORY table?

A.                WHERE price > 5.00
B.                 HAVING COUNT(*) > 10
C.                 ORDER BY order_date;
D.                GROUP BY manufacturer_id;

25.              When sorting query results from a table, what is the sort limit?

A). one column                             B). the number of columns in the table
C). the number of columns in the SELECT list                D). none

26.              Which guideline relates to a cursor FOR loop?

A.                requires an OPEN statement
B.                 requires a terminating condition
C.                 does not require a FETCH statement
D.                must declare the record that controls the loop

27.              Evaluate this IF statement:

IF v_num < 10 THEN
        v_example := 1;
ELSIF v_num < 15 THEN
        v_example := 2;
ELSIF v_num < 20 THEN
        v_example := 3;
ELSIF v_num < 39 THEN
        v_example := 4;
ELSE v_example := 5;

If V_NUM is 15, which value would be assigned to V_EXAMPLE?

A). 1                B). 2                C). 3                D). 4                E). 5

28.              Evaluate this PL/SQL block:

     UPDATE    teacher
     SET            salary = salary * 1.05
     WHERE     subject_id IN (101, 102, 103);

What would cause output to be displayed?

A). An error occurs.                      B). No rows were updated.
C). Only one row was updated.    D). More than one row was updated.

29.              Which type of commands are supported by PL/SQL?

A). DDL           B). DCL        C). DML         D). All are supported by PL/SQL.

30.              Which operator could be used to compare a known value to a NULL value?

A). =    B). !=               C). <>              D). IS NULL

31.              Which two commands would cause an implicit COMMIT command? (Choose two.)
A). GRANT    B). UPDATE              C). CREATE              D). COMMIT            

32.              Which clause restricts the groups of rows displayed to those groups meeting a specified condition?

A). FROM                              B). WHERE                C). SELECT
D). HAVING             E). GROUP BY          F). ORDER BY

33.              Which clause would you use in an ALTER TABLE command to drop the PRICE column from the INVENTORY table?

A). DROP                               B). ALTER                 C). DELETE              
D). REMOVE             E). A column cannot be dropped from a table.

34.              Which privilege is a system privilege?

A). SELECT               B). UPDATE              C). DELETE

35.              You query the database with this command:

     SELECT  price
     FROM     inventory
     WHERE  price (BETWEEN 1 AND 50)
     OR          (price IN(25, 70, 95)
    AND         price BETWEEN 25 AND 75);

Which value could the statement retrieve?

A). 30              B). 51                          C). 75                          D). 95

36.              What are two advantages of using the %TYPE attribute to declare a PL/SQL variable? (Choose two.)

A.                The data type of the underlying database column may be unknown.
B.                 The data type of the underlying database column may change at runtime.
C.                 All column constraints are applied to variables declared using %TYPE.
D.                The number and data types of the underlying database columns may be unknown.
E.                 The number and data types of the underlying database columns may change at runtime.

37.              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

38.              Which type of constraint can be defined at the column or table level and can apply to any column in the table, not just the column on which it is defined?

A). CHECK                B). UNIQUE              C). NOT NULL

39.              If you are writing a SELECT statement to join three tables, what is the minimum number of join conditions needed?

A). 0                B). 1                C). 2                D). 3

40.              Which ALTER TABLE statement would you use to add a primary key constraint on the MANUFACTURER_ID column of the INVENTORY table?

A.                ALTER TABLE inventory

B.                 ALTER TABLE inventory

C.                 ALTER TABLE inventory

D.                ALTER TABLE inventory

41.              Evaluate this command:

1.     CREATE TABLE sale_price
2.     (id_number$     NUMBER(35)
3.          CONSTRAINT sale_price_pk PRIMARY KEY,
4.      description#    VARCHAR2(200),
5.      price&              NUMBER(8,2));

Which line contains an error?

      A). 1             B). 2                C). 3                D). 4                E). 5

42.              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

43.              Which two operators can be used in an outer join condition? (Choose two.)

A). =                      B). OR                        C). IN                          D). AND
44.              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

45.              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.

46.              In which clause would you place the outer join condition?

A). FROM              B). WHERE                 C). SELECT
D). HAVING         E). GROUP BY                        F). ORDER BY

47.              You query the database with this command:

     SELECT    manufacturer_id
     FROM       inventory
     WHERE   manufacturer_id LIKE '%N\%P\%O%' ESCAPE \;

For which character pattern is the LIKE operator searching?

A). NPO                 B). N\P\O         C). N%P%O                D). N\%P/%O

48.              You issue this command:

     GRANT update
     ON inventory
     TO joe

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.

49.              Which select statement will execute successfully.

A)                      select * from emp
Where deptno=10and sal > 2000;
B)                       Select * from emp
Where deptno=10andsal > 2000;
C)                       Select * from emp
Where deptno=10 andsal > 2000;
D)                      None of the above.

50.              If the buffer is empty then you can go into buffer by Edit command.
   A) True             B)  False
no image
  • Title : SQL MCQ Exam Question Using Oracle Database
  • Posted by :
  • Date : 07:04
  • Labels :

  • Blogger Comments
  • Facebook Comments


Post a Comment