Intel Interview Question and Answers

Intel Interview Question and Answers

1.       Difference Between Having and Where Clause

Answer: -Having and Where clause are used to filter the data in the query.

Where clause filters or sorts (like acts on the) data on the source table, Having clause sorts on the resultant table.

2.       Why should we use Stored procedures?

Answer: -To enhance the performance. Stored procedures are pre-compiled and will be directly executed at the run time.

Over comes the overhead of compiling and parsing the query each time the query is executed.

Mostly general purpose query’s are created as stored procedure.

3.       By default what is the type of Parameters in stored procedures Input or output?

Answer: -  default parameter in stored procedure is input.

4.       What is the Syntax to give input and output parameters?

Answer: - Input : create proc proc_name @par1 as select col1,col2 from tab1

                  Output : create proc proc_name @par1 output as select col1,col2 from tab1

5.       By default what are the databases in Sql Server?

Answer: - system databases : Master, msdb, temdb and model.

                  Optional databases : pubs, nothwind.

6.       What is the purpose of Tempdb?

Answer: - Temdb holds all temporary tables and temporary stored procedures. It also fills any other temporary storage needs such as work tables generated by SQL Server

7.       What is Clustered Index?

Answer: - Clustered Indexes are the logical view of the table. Clustered indexes have one row in sysindexes with indid = 1. The pages in the data chain and the rows in them are ordered on the value of the clustered index key.

8.       How many Indexes can we create both clustered and Non clustered?

Answer: - 1-Clustered

    249-Non Clustered

9.       What are user defined Datatypes?

Answer: -  Datatypes Created by user which can be used at application so that any customization is very easy rather than using system defined datatypes.

10.    Dataware Housing Concepts?

Answer: -  Read only database.

Historical database.

More redundancy.

Used in reports analysis, data mining.

11.    Triggers in SQL Server and what are database triggers?

Answer: - Triggers are auto executable object.

Insert, update, delete or any mixed combination of triggers.

Database triggers – Triggers fired at database level for update, drop or alerts.

12.    Describe u’r role in last project

Answer: -

13.    How do optimize Queries?

Answer: - By Query optimizer-

Query analyzer – give Show execution plan ,  SET SHOWPLAN_TEXT ON, SET SHOWPLAN_ALL ON.

Graphical execution plan.

14.    How does update statistics work

Answer: - SP_UPDATE STATUS,  any change in table, query will give optimal execution plan of query which should be in cache.

15.    Why do we need De-normalization

Answer: - For report analysis, data mining, Usage analysis, reports so that joins should be lesser with redundant data.

16.    What is Normalization

Answer: -Normalization is the process with which the data redundancy can be eliminated.

17.    What is DBMS ?

Answer: - DBMS is the collection of data and set of programs to retrieve that data in a convenient and efficient way.

DBMS is DataBase Management system.

18.    What is RDBMS ?

Answer: - Relational Database Management Systems.

19.    What are relationship types?

Answer: -  Four Types of  Relationships and they are One to One, One to Many, Many to One, Many to Many.

20.    What relationship is used?

Answer: -  one to one, one to many, many to many.

21.    When use Enterprise Manager, what is it that makes(behind) the enterprise manager work?

Answer: - SQL DMOs

22.    What is the full form of SQL-DMO?

Answer: - SQL Distributed Management Objects.

23.    What are System Catalogs?

Answer: - System catalogs are system tables that reside in the system database Master.

24.    What is update statistics?

Answer: -  Usage : update statistics table_name

                   Eg: update statistics authors

25.    In what circumstances use the update statistics?

Answer: - Create index, drop index, Update table, delete table, create table so that Query execution plan changes for that always update statistics required.

26.    How the Non clustered indexes are organized ?

Answer: -

1)       sysindexes with rooid = 0 or 2 to 250.

2)       Root Node.

3)       Leaf node having index rows.

4)       Data pages.

Physical order of the indexes is not equal to logical order of the indexes.

27.    What is isolation level, serialization?

Answer: -  How much time SQL Server takes to complete one transaction at a time- transaction isolation level.

Serialization : Type of Transaction isolation level. One transaction at a time. Always more consistency.

28.    What is first normalization? And Third Normalization?

Answer: - First Normal Form: - Non Repetitive Group types.(e.g Phone 1, Phone 2…)

Third Normal Form: - second normal form + no transitive dependecy.

29.    In a typical OLTP Environment, say you have 10 Users updating the same table?

       Answer: - Update Locks  or exclusive locks are created  then goes for Intent locks- exclusive locks upto transaction is completed.

30.    Talk about the locks and the lock escalation levels

Answer: - Shared locks are escalated to exclusive locks.

Update locks even escalated to exclusive locks.

When ever delete query—Exclusive locks are created with intent locks, intent shared or shared with intent exclusive.

Bulk update/delete/insert – Bulk locks are created, -- exclusive locks.

31.    If a user comes and complaints that his query is taking a long time to run, How do you optimize it on the Database side and the application side?

1)       Query tuning – by joins

By indexes.

By  Locks – which locks are generated by query ?

2)       Which stored procedures  using ?

3)       SET SHOWPLAN_ TEXT ON

4)       SET SHOWPLAN_ON

5)       Graphical show plan.

6)       What is transaction isolation level ?

32.    Imagine a scenario, where a full backup is taken at 8 am and transaction log backup at every two hours

The database crashes at 5pm, what is the very first step you do ?

Answer: - Keep the DB in Single User mode first for recovery.

Use full database backup  at 8 PM + transactional log back  upto the time for recovery.

33.    What is a Cartesian product? And full outer join, how many rows are retrieved in each case?

Answer: - It is cross join. Each row from  first table gets multiplied by all the rows in second table.

First table : 5 rows,  Second table : 10 rows.

Cartesian Join : 50.

Full Join : 15.

34.    When a user is created in the database ? what is the system table that gets updated.

Answer: -  SYSUSER.

35.    When SQL Agent is on what database does it use?

        Answer: - msdb

36.    What RDBMS you know?

Answer: -   Microsoft SQL Server 6.5, 7.0,2000, MS Access 97, 2002, ORACLE 7.3. 8, 8I, SYBASE 11.0

37.    What is the type of environment you have worked OLTP or DSS?

Answer: -  OLTP- Online transaction processing

 DSS – Decision Support Services.

38.    What is the full form of DTS?

Answer: -  Data Transformation Services

39.    How do you do the column mapping using DTS

Answer: -    Column mapping screen is provided in DTS  wizard, with the help of VB Script, Jscript and Perlscript.

40.    Of the both BCP and DTS which one you choose and why?

Answer: -     Always DTS- data transformation services since customization, column mapping is possible with DTS Designer.

41.    What is third Normal form?

Answer: -  A table is said to be in third normal form if it is in second normal form and with out having transitive dependencies.

42.    What are the tasks you did in you recent project?

n  Answer: -  Administration of  database management.

n  Installation, creation, testing, maintenance, debugging of  SQL Server DB.

n  Good in stored procedures, cursors, triggers.

n  Experience on replication.

n  Experience on Database design.

n  Experience on maintenance plans.

n  Backup and restore strategy.

43.    Type of data in Dataware housing, Normalized? or Denormalized?

Answer: -   Denormalized.

44.    How many Cluster Indexes can be created on a Table and why?

Answer: -   Only One since it is stored in logical order of the table.

Data Access should be very fast.

Physical order of the indexes = logical order of the key rows in the table.

45.    How many non-cluster indexes can be created on a table?

Answer: -   249.

46.    Is a cursor overhead? If so, what is the alternate for the cursor?

Answer: -   Yes, Cursor is an overhead. Use select statement instead cursors.

47.    What is the difference between inner and outer join?

Answer: -  Inner Join: - Inner join uses a comparison operator to match rows from two tables based on the values in common columns from each table. For example, retrieving all rows where the student identification number is the same in both the students and courses tables.

Outer join: - Outer join can be a left, right, or full outer join

• LEFT JOIN or LEFT OUTER JOIN

The result set of a left outer join includes all the rows from the left table specified in the LEFT OUTER clause, not just the ones in which the joined columns match. When a row in the left table has no matching rows in the right table, the associated result set row contains null values for all select list columns coming from the right table.

• RIGHT JOIN or RIGHT OUTER JOIN.

A right outer join is the reverse of a left outer join. All rows from the right table are returned. Null values are returned for the left table any time a right table row has no matching row in the left table.

• FULL JOIN or FULL OUTER JOIN.

A full outer join returns all rows in both the left and right tables. Any time a row has no match in the other table, the select list columns from the other table contain null values. When there is a match between the tables, the entire result set row contains data values from the base tables.

48.    How many types of triggers available and what are they?

Answer: -   There are 3 types of triggers available in SQL Server. They are Insert, Update and delete.

49.    Do you suggest that the indexes in DW?

Answer: -   Yes, b’caz of large amount of data

50.    What are Temporary tables and how do you declare it?

Answer: -   temporary tables are stored in tempdb and are deleted automatically when no longer in use.  They can be declared by # and ##.

51.    How many types of temporary tables are available?

Answer: -   Two types.  Global(With ##) and Local(with #)

52.    How do you pass the value to a variable in stored procedure?

Answer: -       By Passing by reference and By passing by value.

53.  How indexes are stored in Ms SQL server?

Answer: -   In Extents. One Extent is 8 Contiguous pages and one page is 8KB.