NASSCOM Question-Paper Contributed by Neelima updated on Apr 2019

 

Basic Oracle

1)     What is the Back ground processes in Oracle and what are they.
This is one of the most frequently asked questions. There are basically 9 Processes but in a general system we need to mention the first five background processes. They do the house keeping activities for the Oracle and are common in any system. The various background processes in oracle are:


a) Data Base Writer (DBWR): Data Base Writer Writes Modified blocks from Database buffer cache to Data Files. This is required since the data is not written whenever a transaction is committed.


b) Log Writer (LGWR): Log Writer writes the redo log entries to disk. Redo Log data is generated in redo log buffer of SGA. As transaction commits and logs buffer fills, LGWR writes log entries into a online redo log file.


c) System Monitor (SMON): The System Monitor performs instance recovery at instance startup. This is useful for recovery from system failure


d) Process Monitor (PMON) :: The Process Monitor performs process recovery when user Process fails. Pmon Clears and Frees resources that process was using.


e) Checkpoint (CKPT) :: At Specified times, all modified database buffers in SGA are written to data files by DBWR at Checkpoints and Updating all data files and control files of database to indicate the most recent checkpoint


f) Achieves (ARCH) :: The Archiver copies online redo log files to archival storal when they are busy.


g) Recoveror (RECO) :: The Recoveror is used to resolve the distributed transaction in network


h) Dispatcher (Dnnn) :: The Dispatcher is useful in Multi Threaded Architecture


i) Lckn :: We can have up to 10 lock processes for inter instance locking in parallel sql.



2)     How many types of Sql Statements are there in Oracle
There are basically 6 types of sql statments.They are


a) Data Defination Language(DDL) :: The DDL statments define and maintain objects and drop objects.


b) Data Manipulation Language(DML) :: The DML statments manipulate database data.


c) Transaction Control Statements :: Manage change by DML


d) Session Control :: Used to control the properties of current session enabling and disabling roles and changing .e.g :: Alter Statements,Set Role


e) System Control Statements :: Change Properties of Oracle Instance .e.g:: Alter System


f) Embedded Sql :: Incorporate DDL,DML and T.C.S in Programming Language.e.g:: Using the Sql Statements in languages such as 'C', Open,Fetch, execute and close

3)     What is a Transaction in Oracle
A transaction is a Logical unit of work that compromises one or more SQL Statements executed by a single User. According to ANSI, a transaction begins with first executable statment and ends when it is explicitly commited or rolled back.

4)     Key Words Used in Oracle
The Key words that are used in Oracle are ::
a) Commiting :: A transaction is said to be commited when the transaction makes permanent changes resulting from the SQL statements. 


b) Rollback :: A transaction that retracts any of the changes resulting from SQL statements in Transaction.


c) SavePoint :: For long transactions that contain many SQL statements, intermediate markers or savepoints are declared. Savepoints can be used to divide a transactino into smaller points.


d) Rolling Forward :: Process of applying redo log during recovery is called rolling forward.


e) Cursor :: A cursor is a handle ( name or a pointer) for the memory associated with a specific stament. A cursor is basically an area allocated by Oracle for executing the Sql Statement. Oracle uses an implicit cursor statement for Single row query and Uses Explicit cursor for a multi row query.


f) System Global Area(SGA) :: The SGA is a shared memory region allocated by the Oracle that contains Data and control information for one Oracle Instance. It consists of Database Buffer Cache and Redo log Buffer.


g) Program Global Area (PGA) :: The PGA is a memory buffer that contains data and control information for server process.


g) Database Buffer Cache :: Database Buffer of SGA stores the most recently used blocks of database data. The set of database buffers in an instance is called Database Buffer Cache.


h) Redo log Buffer :: Redo log Buffer of SGA stores all the redo log entries. 


i) Redo Log Files :: Redo log files are set of files that protect altered database data in memory that has not been written to Data Files. They are basically used for backup when a database crashes.


j) Process :: A Process is a 'thread of control' or mechanism in Operating System that executes series of steps.

5) What are Procedure, functions and Packages?


Procedures and functions consist of set of PL/SQL statements that are grouped together as a unit to solve a specific problem or perform set of related tasks.


Procedures do not Return values while Functions return one One Value


Packages :: Packages Provide a method of encapsulating and storing related procedures, functions, variables and other Package Contents

6)     What are Database Triggers and Stored Procedures
Database Triggers :: Database Triggers are Procedures that are automatically executed as a result of insert in, update to, or delete from table.
Database triggers have the values old and new to denote the old value in the table before it is deleted and the new indicated the new value that will be used. DT are useful for implementing complex business rules which cannot be enforced using the integrity rules. We can have the trigger as Before trigger or After Trigger and at Statement or Row level.


e.g:: operations insert, update ,delete 3
before ,after 3*2 A total of 6 combinations
At statement level(once for the trigger) or row level( for every execution ) 6 * 2 A total of 12.
Thus a total of 12 combinations are there and the restriction of usage of 12 triggers has been lifted from Oracle 7.3 Onwards.
Stored Procedures :: Stored Procedures are Procedures that are stored in Compiled form in the database. The advantage of using the stored procedures is that many users can use the same procedure in compiled and ready to use format.

7)     How many Integrity Rules are there and what are they
There are Three Integrity Rules. They are as follows ::


a) Entity Integrity Rule :: The Entity Integrity Rule enforces that the Primary key cannot be Null


b) Foreign Key Integrity Rule :: The FKIR denotes that the relationship between the foreign key and the primary key has to be enforced. When there is data in Child Tables the Master tables cannot be deleted.


c) Business Integrity Rules :: The Third Integrity rule is about the complex business processes which cannot be implemented by the above 2 rules.

8) What are the Various Master and Detail Relationships?
The various Master and Detail Relationship are


a) NonIsolated :: The Master cannot be deleted when a child is existing


b) Isolated :: The Master can be deleted when the child is existing


c) Cascading :: The child gets deleted when the Master is deleted.

9) What are the Various Block Coordination Properties?


The various Block Coordination Properties are 
a) Immediate Default Setting. The Detail records are shown when the Master Record are shown.


b) Deffered with Auto Query

Oracle Forms defer fetching the detail records until the operator navigates to the detail block.


c) Deffered with No Auto Query
The operator must navigate to the detail block and explicitly execute a query

10) What are the Different Optimisation Techniques?


The Various Optimisation techniques are 


a) Execute Plan :: we can see the plan of the query and change it accordingly based on the indexes


b) Optimizer_hint ::


set_item_property('DeptBlock',OPTIMIZER_HINT,'FIRST_ROWS');
Select /*+ First_Rows */ Deptno,Dname,Loc,Rowid from dept
where (Deptno > 25)
c) Optimize_Sql ::


By setting the Optimize_Sql = No, Oracle Forms assigns a single cursor for all SQL statements.This slow downs the processing because for evertime the SQL must be parsed whenver they are executed.
f45run module = my_firstform userid = scott/tiger optimize_sql = No
d) Optimize_Tp ::
By setting the Optimize_Tp= No, Oracle Forms assigns seperate cursor only for each query SELECT statement. All other SQL statements reuse the cursor.
f45run module = my_firstform userid = scott/tiger optimize_Tp = No
Nasscom - 2

Microprocessor


1.    What is a Microprocessor?
Microprocessor is a program-controlled device, which fetches the instructions from memory, decodesand executes the instructions. Most Micro Processor are single- chip devices.


2    .What are the flags in 8086?
In 8086 Carry flag, Parity flag, Auxiliary carry flag, Zero flag, Overflow flag, Trace flag, Interrupt flag,Direction flag, and Sign flag.


3.    Why crystal is a preferred clock source?
Because of high stability, large Q (Quality Factor) & the frequency that doesn’t drift with aging. Crystalis used as a clock source most of the times.


4.    In 8085 which is called as High order / Low order Register?
Flag is called as Low order register & Accumulator is called as High order Register.


5.    What is Tri-state logic?
Three Logic Levels are used and they are High, Low, High impedance state. The high and low arenormal logic levels & high impedance state is electrical open circuit conditions. Tri-state logic has athird line called enable line.


6.    What happens when HLT instruction is executed in processor?
The Micro Processor enters into Halt-State and the buses are tri-stated.


7.    Which Stack is used in 8085?
LIFO (Last In First Out) stack is used in 8085.In this type of Stack the last stored information can beretrieved first


8.    What is Program counter?
Program counter holds the address of either the first byte of the next instruction to be fetched forexecution or the address of the next byte of a multi byte instruction, which has not been completelyfetched. In both the cases it gets incremented automatically one by one as the instruction bytes getfetched. Also Program register keeps the address of the next instruction.


9.    What are the various registers in 8085?
Accumulator register, Temporary register, Instruction register, Stack Pointer, Program Counter are thevarious registers in 8085


10.    What is 1st / 2nd / 3rd / 4th generation processor?
The processor made of PMOS / NMOS / HMOS / HCMOS technology is called 1st / 2nd / 3rd / 4thgeneration processor, and it is made up of 4 / 8 / 16 / 32 bits.


11.    Name the processor lines of two major manufacturers?
High-end: Intel - Pentium (II, III, 4), AMD - Athlon. Low-end: Intel - Celeron, AMD - Duron. 64-bit: Intel- Itanium 2, AMD - Opteron.


12.    What’s the speed and device maximum specs for Firewire?
IEEE 1394 (Firewire) supports the maximum of 63 connected devices with speeds up to 400 Mbps.Where’s MBR located on the disk? Main Boot Record is located in sector 0, track 0, head 0, cylinder 0of the primary active partition.


13.    Where does CPU Enhanced mode originate from?
Intel’s 80386 was the first 32-bit processor, and since the company had to backward-support the8086. All the modern Intel-based processors run in the Enhanced mode, capable of switching betweenReal mode (just like the real 8086) and Protected mode, which is the current mode of operation.


14.    How many bit combinations are there in a byte?
Byte contains 8 combinations of bits.


15.     Have you studied buses? What types?
There are three types of buses.Address bus: This is used to carry the Address to the memory to fetch either Instruction or Data.Data bus : This is used to carry the Data from the memory.Control bus : This is used to carry the Control signals like RD/WR, Select etc.

16    .What is the Maximum clock frequency in 8086?
5 Mhz is the Maximum clock frequency in 8086.


17.What is meant by Maskable interrupts?
An interrupt that can be turned off by the programmer is known as Maskable interrupt.


18.What is Non-Maskable interrupts?
An interrupt which can be never be turned off (ie. disabled) is known as Non-Maskable interrupt


19.What are the different functional units in 8086?
Bus Interface Unit and Execution unit, are the two different functional units in 8086.


20.What are the various segment registers in 8086?
Code, Data, Stack, Extra Segment registers in 8086.

feedback