Oracle Exam MCQs Short Question SQL Query and Joins Sample Question

 Oracle Exam Online MCQs Short Question SQL Query and Joins Sample Question

1.      Select ename ||' : '||'1'|| 'Monthly sal  *12= '||sal Monthly                                      (30)

From EMP;

How the Result will be displayed:

A.    MONTHLY

------------------------------------------------------------------------

SMITH : 1Monthly sal  *12= 800

B.     ENAME||':'||'1'||'MONTHLY'||SAL*12                                SAL

------------------------------------------------------------------- ----------

SMITH  :   1  Monthly 9600                                                 800

C.     ENAME          MONTHLY sal                                Monthly

------------------------------------------------------------------- ----------

SMITH  :   1  Monthly 9600                                                 800

D.    Statement will not execute successfully.

2.      Evaluate these select statements

                                i.      Select ename||123,empno from emp;

                              ii.      Select ename||'123',empno from emp;

A.    Statement 1 will not execute and statement 2 will execute successfully.

B.     Statement 2 will not execute and statement 1 will execute successfully.

C.     Both statements will not execute successfully.

D.    Both Statements will execute but the result will be different.

E.     Both Statements will execute successfully and result also will be the same.

3.      To_Char(Price, ‘$999990.99’)

If price=0.25

Then the display will be

a)  0.25          b)   $.25         c)   $0.25           d)   $000000.25

4.        Which single row function can be used on a varchar2 column?

a)  NVL    b)   TRUNC   c)   ROUND  d)  SYSDATE

5.      Which command would cause a pending transaction to end?

A)  ALTER  B)  DELETE  C)   INSERT  D)  UPDATE  E)  SELECT

6.      1.Create table 1234;

      2.Create table A1234;

A)     Statement 1 will execute successfully.

B)     Statement 2 will execute successfully.

C)     Both will not execute.

D)     Both will execute successfully.

7.      Operators of the same priority are evaluated from ……………. To…………………

8.      Eliminate Duplicate rows by using the ………….. Keyword in the ………… clause.

9.      What will be result of these values?

Select ROUND (99.923,2), ROUND (99.923,0), ROUND (99.923,-1), TRUNC(99.923,2), TRUNC(99.923,0), TRUNC(99.923,-1) FROUM DUAL;

10.  1.SelectDistinct from emp;

      2.Select job,Distinct deptno, ename from emp;

A.    Statement 1 will execute successfully.

B.     Statement 2 will execute successfully.

C.     Both will not execute.

D.    Both will execute successfully.

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

12.  Which of the functions only use with numeric data.

A) AVG               B) COUNT                 C) MAX          D) MIN

E) STDDEV        F) SUM                       G) VARIANCE

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

15.  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?

1. CREATE VIEW parts_456874_vu

AS SELECT      id_number, description, quantity

FROM               inventory

WITH CHECK CONSTRAINT;

2. CREATE VIEW parts_456874_vu

AS SELECT   id_number, description, quantity

FROM            inventory

HAVING         manufacturer_id = 456874

WITH READ ONLY;

3. CREATE VIEW parts_456874_vu

AS SELECT    id_number, description, quantity

FROM             inventory

WHERE          manufacturer_id = 456874

WITH READ ONLY;

4. CREATE VIEW parts_456874_vu

AS SELECT     id_number, description, quantity

FROM              inventory

WHERE           manufacturer_id = 456874

WITH CHECK OPTION;

16.        Which function would you use to display date value in “mm/yy” format?

A). To_Char          B). To_Date     C). To_Number                       D). None

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

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

19.        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?

1. There are no students named IRFAN SHEHZAD.
2. There is more than one student named SHEHZAD.
3. There is more than one student named IRFAN SHEHZAD.
4. The FIRST_NAME  and LAST_NAME values in the database are in lowercase.

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

21.        Which of the function include Null values:

A)    AVG                     B) COUNT                 C) MAX                      D) MIN           E) STDDEV              F) SUM               G) VARIANCE

22.        How Column headings are displayed:

A.    Date left justified

B.     Character left justified

C.     Number right justified

D.    All of the above

E.     None of the above

23.        For which two types of constraints is an index automatically created?

A) CHECK                             B) UNIQUE                           C) NOT NULL       D) FOREIGN KEY                   E) PRIMARY KEY

24.        Which characteristic relates to a single row function?

1. Act on each row returned in the query.
2. Return one result per row.
3. May return a data value of a different type than that referenced.
4. May expect one or more argument.
5. All of the above.
6. None of the above.

25.        Character functions Accept character input and can return:

A.    Only character data.

B.     Both character and number data.

C.     Only number data.

D.    None of the above.

26.        The values in inventory table are follows:

ID_Number                                         ORDER_DATE

   25025                                                  10-jun-97

   25027                                                  11-oct-96

   25023                                                  19-apr-97

   32096                                                  25-jul-97

   25026                                                  11-sep-97

   45025                                                  15-sep-97

Delete from inventory

            Where order_date>to_date(’25.07.1997’,’DD.MM.YYYY’);

which id_number value would be deleted

A) 32096                     B) 25026                     C) 32081                 D) No value will be deleted

27.        In order to Truncate a table you must have:

A.    Create Table Privileges.

B.     Delete Table Privileges.

C.     Select Privileges.

D.    Both Create and Delete Privileges.

E.     DBA Privileges.

28.        what is the default length value of a CHAR column?

A) 1          B) 9                        C) 38           D) A column length must be specified for a CHAR column

29.        What would happen if you created an index on all six columns in the student table:

A.    The speed of updates would be increased.

B.     Queries with a where clause would be slower.

C.     The deletes operations on the table would be slower.

D.    All the select statements issued on the table would be faster.

E.     Non of the above.

30.        Which two privileges can only be granted to a user and not to a Role.

A) ALTER                   B) INDEX                    C) DELETE             D) INSERT

E) EXECUTE               F) REFERENCES

(Part II)                                                            (10)

With Reference to your ORACLE project What type of queries you have developed to facilitate user provide some examples. How can you integrate your modules and use Constraints to implement a RDBMS. Draw a Prototype Model of your project (not ERD&DFD).