sql intervw qtns

8/17/2019 sql intervw qtns

1/8

1.WHAT IS INDEX AND WHAT ARE THE TYPES OF INDEX?

Indexes o f sql server are similar to the indexes i n books, When there are thousandsof records i n a table, retrieving information will take a long time, therefore indexes are

created on columns w hich are accessed frequently, so that the data can be retrievedquickly.

An Index is a physical structure containing pointers t o the data.

These indexes can be created on a single column or combination of columns, whenan Index is cr eated it rst sorts t he data and it assigns a ROW ID for each row.

The m ain types of indexes a re:

1.B-TREE INDEXES-These indexes created on unique data, this i s t he default index

2.BIT MAP INDEXES-these a re cr eated on duplicate d ata o f the column.

3.FUNCTION BASED INDEXESThese indexes cr eated on the functions o n the column, cause if we use oraclefunctions on the indexed c olumns, the indexes w ont work.

2.WHAT IS VIEW?

View is a sql statement which is st ored in the database with an alias n ame.

Whenever we run view, sql executes in back e nd. Views a re used to restrict the dataaccess of table.

A View is like a virtual table ,it does not physically exist, rather it created by a query joining one or more tabl

A view can be created from one or more tables, which depends on the written sqlquery to create view.

3.WHAT IS MATERIALISED VIEW?

Materialized view stores t he result set of sql statements.How does t hese m aterialized views work?

When we run materialized views for the rst time it hits the base t ables rst andstores t he output into the log tables. From the next run onwards i t hits t he log tables

instead o f base tables.


2/8

We u se t he these M aterialised views in sce narios w here we h ave co mplex a ggregatequeries.

4.WHAT IS INLINE VIEW?

Basically inline view is a query inside a query, which is k nown as s ub query b ut witha twist.An inline view is a S ELECT statement in the FROM cause o f another SELECTstatement.These are commonly used to simplify co mplex q ueries by r emoving J oin operationsand integrating several SQL statements in a single query/

Ex: SELECT A.lastname, A.salary,A.department_id,B.max sal FROMemployees A,

(SELECT department_id,MAX(salary),maxsal FROMemployees

GROUP BY department_id ) ,BWHERE A.department_id=B.department_id AND

A.salary=b.max sal ;

5.WHATS THE DIFFERNCES BETWEEN SUB QUERY AND CORELATEDQUERY?

A query inside the query is cal led SUBQUERY, when we run sub query, the inner

query e xecuted rst and runs o nly once and the output is u sed as d ata source for theouter query. whereas ,In the CORELATED SUBQUERY the outer query will executed rst and for every rowof outer query , inner query w ill get executed. So the inner query w ill executed asmany times a s n o of rows i n the result of the outer query.The outer query output can use the inner query output for comparison.this means inner query and outer query depend on each other.

Ex: SELECT cust_name,dept_id FROM customer WHERE cust_name IN (SELECTcust_name FROM dept

WHERE cust_id = dep t.dept_id) ;

6.WHAT IS THE DIFFERENCE BETWEEN ROWID AND ROW NUMBER?

ROWID

It is t he physical address of the rows.

Row id is a utomatically g eneratedunique id of a row and it is g eneratedat the time of insertion of row


3/8

ROW NUMBER

Row number is the sequential numberallocated to each returned row duringquery e xecution.

Row number is a dynam ic valueautomatically retrieved along withselect statement output.

7.WHAT IS CURSOR AND WHAT ARE THE TYPES OF CURSORS?

In order to process t he sql statement oracle will allocate an area of memory is kn owas c ontext area.A Cursor is a handle or pointer to the context area.

TYPES OF CURSORS1.Implicit cursors 2.explicit cursors

Implicit cursors a re the default cursors o f the procedures.The oracle server uses t he implicit cursors t o parse and execute our sql statements.Where as the explicit cursors, the programmer explicitly d eclares i n procedures andmanually ca lls t hem.

STEPS TO DEFINE THE CURSOR:The 4 pl/sql steps n ecessary to for explicit cursor processing are as f ollows

1.Declare the cursor2.Open the cursor for query3.fetch the result into pl/sql variables.4.close the cursor.

8.WHAT IS THE DIFFERENCE BETWEEN STORED PROCEDURE AND

FUNCTIONS?

Stored procedure

1.Stored procedure m ay o r may n otreturns values.

2. These cannot be used in a select /where/having clauses.

3.stored procedure can be runindependently.

4.These ca n ca ll user-denedfunctions.


4/8

5.these can h ave Input and outputparameters.

Function

1.functions should returns the values.

2.function can be called fromselect/where/having c lause

3.these can b e executed usingEXECUTE or EXEC command.(These a re part of sql commands)

4.function cannot call the storedprocedures.

5.functions h ave only i/p parameters.

9.WHAT ARE THE DIFFERENCE BETWEEN DELETE AND TRUNCATE?

DELETE

This is DDL command

DELETE command can delete all therows if no condition is specied (or) itcan deletes o nly the rows f rom the

table, based on condition given inWHERE clause.

We ca n Roll back a fter delete.

It can work o n a table that is p art ofcluster.


5/8

It can work outside the users sch ema(as l ong as u ser has d elete privilegeon the object).

It does n ot free the space containingthe table.

TRUNCATE

This is a DML command

Truncate command deletes al l the rowsfrom the table

We ca nnot rollback on ce w e truncatethe table.

This command cannot work on t he

table that is part of the cluster. Wehave to truncate whole cluster or useeither DROP command.

Table can be truncated in ones ownschema o nly.

It will free the space containing thetable.


6/8

10.HOW TO FIND DUPLICATES IN ORACLE?

SELECT column 1,column 2,COUNT(*) FROM table

GROUP BY table HAVING COUNT (*)>1 ;

11.HOW TO DELETE DUPLICATES IN ORACLE?

DELETE FROM < table name >WHERE row id NOT IN (SELECT MIN (row id) GROUP BY column 1,

column 2….. );

12.QUERY TO FIND AN EMPLOYEES CORRESPONDING MANAGERS?

Using a sub query

SQL> SELECT ename, emp no,(SELECT ename FROM emp WHERE emp no = mgr) as manager,mgr ;

(OR)

Using self join

SELECT e.ename,e.emp no,m.ename as manager,e.mgr FROM emp e,emp.mWHERE e.mgr=M.emp no

13.WHAT IS SURROGATE KEY?

A Surrogate key by denition means an sole purpose is t o identify a dimension row uniquely.

Surrogate is a substitution for the natural primary key in the dimension tables.Surrogate key is a numeric n umber that uniquely identies r ecords in the dimensiontable.It is a lso know as a rticial or identity k ey in data warehouse.It is u seful because the natural primary key can change and this m akes u pdatesmore difficult.


7/8

14.WHAT IS THE DIFFERENCE BETWEEN PRIMARY KEY AND UNIQUE KEY?

Primary key:It is an attribute in table, which uniquely identies a record (row).Unique key:

it is a key which stores u nique values(no duplicates)for the particular column.

1.Unique key a llows n ull values BUT Primary ke y d oes n ot allow null values.2.We can h ave multiple u nique ke ys in a table b ut primary key i s o ne a nd o nly one.3.Primary key i n itself is a unique key.4.Primary c an be referenced to other table as f oreign key.

15.CAN WE IMPLEMENT DML COMMANDS IN VIEW?

DML commands ca n be p ossible if view has o nly on e b ase t able, if the view is ba sedon columns f rom one or more table then its n ot possible.

But with some restrictions, it can possible to insert/update/delete from a base tableusing view.

1.If a view is d ened by a query that contains SET or DISTINCT operators, aGROUP BY clause, or a group function, then rows ca nnot be inserted into, updated

in, or deleted from the base tables u sing the view.

2 For views w ith “WITH CHECK” options e nabled, its i mportant to keep in mind thatthe data begin inserted qualies i n the WHERE clause of the view.

3.if a NOT NULL column that does n ot have DEFAULT clause i s om itted from theview, then a row can be inserted.

4.if the view was cr eated by u sing an expression ,such as DECODE ,then rows can

not be inserted into or updated in the base table using the view.16.QUERY TO FIND THE TOP N th SALARIES OF EMLOYEES?

SELECT TOP 1 salaryFROM (SELECT DISTINCT TOP n salaryFROM employeeORDER BY salary DESC ) aORDER BY salary


8/8

17.WHAT ARE THE DIFFERENT TYPES OF JOINS IN ORACLE?

The purpose of the join is t o combine the data across t ables.A join is ac tually pe rformed by the W HERE clause, which co mbines t he sp ecied

rows o f tables.If a join involves i n more than two tables t hen oracle joins rst two tables b ased onthe joins co ndition and then compares t he result with next table and so on.

TYPES:1 Equi join2 Non-equi join3 Self join4 Natural join5 cros s join6 outer joinLeft outerRight outerFull outer

7 Inner join8 Using clause9 On clause

sql intervw qtns

Documents