RDBMS/SQL/PLSQL QUESTION

ORACLE SQL/PLSQL QUESTIONS

Theory Paper RDBMS/SQL/PLSQL

    Each question carry 1 Mark  DOWNLOAD

 1) What does the term Entity stand for ?

              A) A Row

B) A Table

C) A column

D) None of these

2) A DBMS that runs on many different operating systems is said to be

A) Scalable

B) Robust

C) Platform independent

D) Distributed

3) Denormalization is useful when

               A) We want to reduce Redundancy

               B)  We want data rarely

               C)  We want data retrieval faster

               D) None of the above

4)Normalization of database is used to

A) Eliminate Redundancy

B) Improve security

C) Improve efficiency

D) Minimize Errors

5) Manager’s salary details are hidden from the employee. This is Conceptual level data hiding.

A) Physical level data hiding

B) External level data hiding

C) All of these

D) None of these

6) A schema Contains

     A) Username

     B) All tables

     C) All Anonymous data

     D) User’s data

7) Define compound key

A) Is made up of a several pieces of information

B) Uniquely identifies an item in a list

C) Is a combination of each unique key

D) Both A and B

8) A relation in 2^nd normal form when

A) It is in 1st normal form

B) No partial dependency

                C) It has no transitive dependency

D) Both A and B

9) You will query which view to know that how many database objects you can access?

A.   ALL_TABLES                                        

B.   ALL_TAB_COLUMNS

C.   ALL_OBJECTS                         

D.   USER_OBJECTS

10) You issue a select statement on the BANK_ACCT table containing the order by clause. Which of                                      the following uses of the order by clause * would produce an error?

A.   order by acctno DESC;                         

B.   order by 1;

C.   order by length(1);                                              

D.   order by acctno ASC;

E.   None of the above.

11)            When you drop a table with DROP TABLE command, the views based on those tables are also dropped automatically along with the table.

A.   True                                                                     

B.   False.

C.   You can’t drop such table

D.   View can’t be drop

12)            Which statement would you use to remove the EMPLOYEE_ID_PK PRIMARY KEY constraint and all depending constraints from the EMPLOYEE table?

A.   ALTER TABLE employee DROP PRIMARY KEY CASCADE;

B.   ALTER TABLE employee DELETE PRIMARY KEY CASCADE;

C.   MODIFY TABLE employee DROP CONSTRAINT employee_id_pk CASCADE;

D.   ALTER TABLE employee DROP PRIMARY KEY employee_id_pk CASCADE;

E.   MODIFY TABLE employee DELETE PRIMARY KEY employee_id_pk CASCADE;

13)   When a BETWEEN operator is used in select statement

A.   The first value is included and the second value is excluded

B.   The first value is excluded and the second value is included

C.   Both the values are included in the range

D.   None

14)   DDL statements in SQL are:

A.   SELECT,UPDATE, DELETE,ALTER , CREATE, ALTER , DROP 

B.   SELECT, DELETE,ALTER , CREATE, ALTER , DROP 

C.   CREATE, ALTER , DROP 

D.   None.

15)            To impose conditions on the grouped values

A.   Where condition is used

B.   HAVING Clause is used

C.   Conditions cannot be imposed on grouped values

D.   None of these

16)    Assuming today is Monday, 16 DEC 2014, what is returned by this statement :

SELECT to_char(NEXT_DAY(sysdate, ‘MONDAY’), ‘DD-MON-RR’)

FROM dual;

A.   21-DEC-14

B.   10-DEC-14

C.   22-DEC-14

D.   16-DEC-14

17)    To produce a meaningful result set without any cartesian products, what is the minimum number

of conditions that should appear in the WHERE clause of a four-table join?

A.   There is no such criteria

B.   2

C.   3

D.   4

E.   5

18) The commit and rollback commands are not applicable for  __________

A.   Insert

B.   Create

C.   Delete

D.   Update

19)            EMPLOYEE table contains these columns:

FIRST_NAME     VARCHAR2(25)

LAST_NAME                  VARCHAR2(25)

JOB                                  VARCHAR2(25)

SALARY               NUMBER(7,2)

DEPT_ID             NUMBER(3)

You need to display the first name and last name for employees whose:

Last Name is Brown, Chan or Lindsey.

Job is Manager, Technician or Clerk

Salary is greater than 30000.

You issue this SQL statement:  SELECT first_name, last_name    FROM  employee

WHERE UPPER(last_name) IN ('BROWN', 'CHAN', 'LINDSEY')

AND UPPER(job) IN ('MANAGER', 'TECHNICIAN', 'CLERK')

AND salary <= 30000;

Which employees are displayed?

A.   Those who meet only one requirement

B.   Those who meet only two requirements

C.   Those who meet all three requirements.

D.   Those who meet none of the requirements.

20)            You alter the database with this command:

alter table inventory modify (price number(8,2) default 0);

Which task was accomplished?  Choose the best answer.

A.   A new column was added to the table

B.   A column constraint was added to the table

C.   A column was altered.

D.   A default value was added to a column

21)            Which of the following can be a valid column name?

A.   Column                                                    

B.   1966_Invoices

C.   Catch_#22

D.   #Invoices

E.   None of the above

22) You define a multiple-row subquery in the WHERE clause of an SQL query with a
comparison operator "=".What happens when the main query is executed?

A.   The main query executes with the first value returned by the subquery.

B.   The main query executes with the last value returned by the subquery.

C.   The main query fails because the multiple-row subquery cannot be used with the comparison operator.

D.   You cannot define a multiple-row subquery in the WHERE clause of a SQL query.

23) To write a query that performs an outer join of tables A and B and returns all rows from B, You need to write

A.   any outer join                  

B.   a left outer join

C.   a right outer join  

D.   an inner join

24) Which is not true about single row functions?

A.   cannot be nested

B.   manipulate data items

C.   act on each row returned

D.   return one result per row

25) A subquery can be used to _________.

A.   Create groups of data

B.   Sort data in a specific order

C.   Retrieve data based on an unknown condition

D.   Convert data to a different format

26) If you drop a parent row for which, a child exists, then what is the default behaviour?

A.   It won’t allow. Error will be returned.

B.   It will delete all the child rows along with the parent row.

C.   Only parent row is deleted.

D.   With the parent row, the first child row in the child table will be deleted.

27) You are running a query against a relational database.
What clause or command do you use in the query to help avoid a costly table scan?

A.   GROUP BY clause

B.   INDEX command

C.   HAVING clause

D.   FROM clause

E.   WHERE clause

28) Mark item for review Which best describes an inline view?

A.   A schema object

B.   A subquery that can contain an ORDER BY clause

C.   Another name for a view that contains group functions

D.   A subquery that is part of the FROM clause of another query

29) With SQL, how do you select all the records from a table named "Persons" where the value of the column "FirstName" starts with an "a"?

A.   SELECT * FROM PERSONS WHERE FIRSTnAME LIKE “%a”;

B.   SELECT * FROM PERSONS WHERE UPPER(FIRSTNAME) LIKE ‘%a%’;

C.   SELECT * FROM PERSONS WHERE FIRSTNAME =’%a’;

D.   SELECT * FROM PERSONS WHERE FIRSTNAME LIKE ‘a%’;

30) Which Query is will give error.

A.   select count(sysdate) from tab;

B.   select count(8) from emp;

C.   select count(‘d’) from dual;

D.   SELECT COUNT(+) FROM DUAL;

31)   A Student  table’s name column having values

Ajay

Aman

Brajesh

ManmeetSingh

Navneet

Khusboo

Zawed

What will be the result of this Queiry?

Select max(name) from student;

A.   Aman

B.   Zawed

C.   ManmeetSingh

D.   Ajay

32)___________ block is mandatory in a pl/sql block.

a)   Declaration                                        b)         Execution

c)Exception

33)In a pl/sql block the ___________ statement has to be associated with the INTO clause.

a)   Commit                                              b)         Delete

c)   Select                                                 d)         Update

34) For the following program which is not false statement

A) You have to declare x                                               

B) You have to initialize x

C) You can’t assign value to x

D) You have to use other operator than assignment in line 5

35) Which line number has error in the following program?

a) 2                       b) 5                 c) 3                   d) 7

36) Consider the following code:

 

The above code will throw a compile time error

a)   True                                                    b)         False

37) If a package specification is dropped then the package body gets dropped implicitly.

a)   True                                                    b)         False

38) A procedure can be called as a part of assignment

a)   True                                                    b)         False

39) The following program will throw error

               

a)   True                                                    b)         False

40) A private variable is declared inside a package body.

a)   True                                                    b)         False

41):new bind variable is associated with a statement level trigger.

a)   True                                                    b)         False

42) Which line number has error in the following program?

     

a)   2                                              b)         5                     

c)   7                                                         d)          4

43) A ___________ level trigger executes for every record affected in the transaction.

a)   Row                                                    b)         Statement

44) By default, IN OUT and OUT parameters are passed by _________________.

a)   Value                                                  b)         Reference

45) ______________ pragma is used to attach a user defined error name with predefined error number.

a)   AUTONOMOUS TRANSACTON    b)         EXCEPTION_INIT

c)   ERROR_INIT                         d)         RESTRICT_REFERENCES

46) While creating a package, you placed the function name in the specification and its implementation in the body ,then which construct you have to declared?

a)   public                                                  b)         illegal

c)   private                                                d)         one-time only

47) Which statement about packages is true?

A.   A package can be called.

B.   A package can be nested.

C.   A package can be parameterized.

D.   Package contents can be shared by multiple applications.

48) You decide to use packages to logically group related programming constructs. Which two types of constructs can be grouped within a package? (Choose two.)

A.   view                                             

B.   cursor

C.   variable   

D.   database trigger

49) Examine this code:

CREATE OR REPLACE PACKAGE prod_pack

IS

g_tax_rate NUMBER := .08;

END prod_pack;

Which statement about this code is true? Choose any 2

A.   This package specification can exist without a body.

B.   This package body can exist without a specification.

C.   This package body cannot exist without a specification.

D.   This package specification cannot exist without a body.

50) When creating stored procedures and functions, which construct allows you to transfer values to and from the calling environment?

a)   Local variables                                  b)         Formal arguments

c)   Boolean variables                             d)         Substitution variables