Tweet
Hire Me on Freelancer.com

Wednesday, 24 October 2012

Mostly asked SQL Server 2005 2008 Interview Qusetion and Answers (Part 1)

Asp.net interview, .NET Interview Questions and Answers, asp.net interview questions, asp.net interview questions and answers, C# interview questions and answers, IIS interview questions and answers, sql server interview questions and answers, vb.net interview questions and answers, XML interview questions and answers, Asp.Net 3.5,Asp.Net 4.0,Asp.Net4.5, Asp.Net C#, VB.Net.



SQL: Queries

1. Write a query to select the second highest salary from a table.
Answer: SELECT max (salary) AS salary2 FROM orders WHERE salary < (SELECT max (salary) AS salary1 FROM orders)

2. Write a query to select the 5th highest salary from a table.
Answer: SELECT min (salary) AS high5 FROM employee WHERE salary IN (SELECT DISTINCT TOP 5 salary FROM orders ORDER BY salary DESC)

3. How to find duplicate records with the number they are duplicated?
Answer: SELECT Id, count (*) as number records from table group by id having count (*) > 1.

SQL: Questions

1. What is the difference between Delete and Truncate command in SQL?
Answer: Delete command and truncate command both will delete the data, however the truncate command cannot be rolled back as delete can be. The delete command can be used for selected records using the where clause but with the truncate command we have to lose data. DELETE statement is a logged operation and hence takes more time then truncate.

2. What is Magic Table in SQL?
Answer: The insert and Delete commands are known as magic tables in SQL.

3. Can Primary key is a Foreign Key on the same table?
Answer: Yes, Consider a category table in an e-commerce web site.
Category_Id, Category Name, Parent_Category_ID. In this table all the parent categories are also categories. When we create a self join category id will be treated as foreign key to the same table.

4. What is Normalization? What are its rules?
Answer: Normalization is the technique in the database design where the idea is to reduce the redundancy of non key data items across the table.

1.       Rule 1: There should be a one-to-one relationship between the instances of an entity and the rows of the table.
2.       Rule 2: A field should have the same meaning in each row of the table.
3.       Rule 3: Each table should represent at most one entity.
4.       Rule 4: Multiple instances of an entity should be represented by multiple rows in a table.
5.       Rule 5: Joins should be based only on primary and foreign-key equality.
6.       Rule 6: Make sure keys are linked correctly.

5. What are the advantages and disadvantages of Normalization?
Answer: There are several advantages of normalization as under:

1.       Faster sorting and index creation.
2.       A larger number of clustered indexes.
3.       Narrower and more compact indexes.
4.       Fewer indexes per tables, which improve the performance of INSERT, UPDATE, and DELETE statements
5.       Fewer null values and less opportunity for inconsistency, which increase database compactness.
6.       Beside the above benefits there are few disadvantages as well:
7.       Increased amount of Normalization increases the amount of complexity of joins between tables and that hinders the performance.

6. What are the conditions to achieve the normalization?
Answer: There are few conditions to achieve the normalization:

1.       There should be a unique row identifier.
2.       A table should store only data for a single type of entity. For e.g. details for book’s publisher and book’s author should be saved under different table.
3.       A table should avoid columns which can be null-able.
4.       A table should avoid duplication of data and columns.

7. What is a Stored Procedure? State its advantage.
Answer: A stored procedure is a set of pre-compiled SQL commands (query statements), which are stored in the server. It is faster than the loose SQL statements processed on client, as it is pre-compiled. It can execute more than one SQL commands once as they are bundled in a single entity. We can use control statements within the stored procedure, which will allow us to repeat some SQL command. It can send return values depending upon the result. Stored procedures are used to reduce network traffic.

8. What is a Trigger?
Answer: Triggers are a special type of stored procedure, which gets invoked upon a certain event. They can be performed upon an INSERT, UPDATE and DELETE.

9. What is a Clustered Index?
Answer: The data rows are stored in order based on the clustered index key. Data stored is in a sequence of the index. In a clustered index, the physical order of the rows in the table is the same as the logical (indexed) order of the key values. A table can contain only one clustered index. A clustered index usually provides faster access to data than does a non-clustered index

10. What is a Non-Clustered Index?
Answer: The data rows are not stored in any particular order, and there is no particular order to the sequence of the data pages. In a non-clustered index, the physical order of the rows in the table is not same as the logical (indexed) order of the key values.

11. Describe the three levels of data abstraction?
They are three levels of abstraction:

1.       Physical level: The lowest level of abstraction describes how data are stored.
2.       Logical level: The next higher level of abstraction, describes what data are stored in database and what relationship among those data.
3.       View level: The highest level of abstraction describes only part of entire database.

12. What is DDL (Data Definition Language)?
Answer: A data base schema which is specified by a set of definitions expressed by a special language is called DDL. Data Definition Language (DDL) is used to define and manage all the objects in an SQL database.

13. What is DML?
Answer: It is a special language used to manipulate the Data. Data Manipulation Language (DML), which is used to select, insert, update, and delete data in the objects defined using DDL.

14. What is a PRIMARY KEY?
Answer: The PRIMARY KEY is the column(s) used to uniquely identify each row of a table.

15. What is a FOREIGN KEY?
Answer: A FOREIGN KEY is one or more columns whose values are based on the PRIMARY or CANDITATE KEY values from the database.

16. What is a UNIQUE KEY?
Answer: A UNIQUE KEY is one or more columns that must be unique for each row of the table.

17. What is the difference between UNIQUE and PRIMARY KEY?
Answer: The UNIQUE KEY column restricts entry of duplicate values but entry of NULL value is allowed. In case of PRIMARY KEY columns entry of duplicate as well as <NULL> value is also restricted.
18. What is a VIEW?
Answer: A View is a database object that is a logical representation of a table. It is derived from a table but has no storage space of its own and often may be used in the same manner as a table.
19. What is a ROWID?
Answer: ROWID is the logical address of a row, and it is unique within the database.

20. What is INDEX?
Answer: INDEX is a general term for an SQL feature used primarily to speed up execution and impose UNIQUENESS upon data. You can use an index to gain fast access to specific information in a database table. An index is a structure that orders the values of one or more columns in a database table. The index provides pointers to the data values stored in specified columns of the table, and then orders those pointers according to the sort order you specify.
21. What is a cursor?
Answer: An entity that maps over a result set and establishes a position on a single row within the result set. After the cursor is positioned on a row, operations can be performed on that row, or on a block of rows starting at that position. The most common operation is to fetch (retrieve) the current row or block of rows.
22. The Difference between ‘Count’ and ‘Count (*)’?
Answer: ‘Count’: Counts the number of non-null values. ‘Count (*)’: Counts the number of rows in the table, including null values and duplicates.

i will appreciate your comments and time for using my blog.
"Necessity is the mother of Invention"

18 comments:

  1. Great work, really appreciate your effort for the information and being enrolled in http://www.wiziq.com/course/46-INTERVIEW-PREPARATION-COURSE-BY-SHIVPRASAD-KOIRALA the OOps, .NET and SQL INTERVIEW Preparation i found your information very helpful.

    ReplyDelete
  2. Hi,I have studied these sql interview questions and queries..

    Theosoft

    ReplyDelete
  3. What is the difference between inner and outer join? Explain with example. And If you've a video must share with us, video can make it more easy to understand.

    My Signature: Stand out with a truly custom dental website (no templates). Optimized360 stunning dental sites are developed for doctors like you. View Example Now!

    ReplyDelete

  4. Great site for these post and i am seeing the most of contents have useful for my Carrier.Thanks to such a useful information.Any information are commands like to share him.

    digital marketing company in india

    ReplyDelete
  5. This blog is very well good and it i very much nice and interesting too.

    Web Design Company in Chennai

    ReplyDelete
  6. your questions are simply super as i was very much interested please say some more interview questions too.

    Digital Marketing Company in Chennai

    ReplyDelete
  7. This blog having the details of Processes running. The way of running is explained clearly. The content quality is really great. The full document is entirely amazing. Thank you very much for this blog.
    Seo Company in Chennai

    ReplyDelete
  8. I read your articles very excellent and the i agree our all points because all is very good information provided this through in the post.

    Web Design Company in Chennai

    ReplyDelete
  9. I read your articles very excellent and the i agree our all points because all is very good information provided this through in the post.

    Digital Marketing Company in Chennai

    ReplyDelete
  10. Very nice post here thanks for it .I always like and such a super contents of these post.Excellent and very cool idea and great content of different kinds of the valuable information's.

    Hadoop Training in Chennai

    ReplyDelete
  11. Great site for these post and i am seeing the most of contents have useful for my Carrier.Thanks to such a useful information.Any information are commands like to share him.
    Manpower Services in Chennai

    ReplyDelete
  12. I am not sure the place you are getting your information, however good topic.I needs to spend some time studying more or understanding more.Thank you for wonderful information I was in search of this info for my mission.

    Manpower Consultancy in Bangalore
    HR Consultancy in Bangalore
    Recruitment Consultancy in Bangalore
    HR Franchise in Bangalore

    ReplyDelete
  13. Very nice post here thanks for it .I always like and such a super contents of these post.Excellent and very cool idea and great content of different kinds of the valuable information's.

    Digital Marketing Company in Chennai

    ReplyDelete
  14. I’ve been browsing on-line greater than three hours today, but I never discovered any attention-grabbing article like yours. It is beautiful worth sufficient for me. Personally, if all webmasters and bloggers made good content material as you did, the net will be a lot more helpful than ever before.
    Office Interiors in Chennai
    Home Interior Decorators in Chennai

    ReplyDelete
  15. very very amazing explaintion....many things gather about yourself...yes realy i enjoy it
    Digital Marketing company in Chennai

    ReplyDelete
  16. Really Good article.provided a helpful information about sql Server interview questions.keep updating...
    E-mail marketing company in india

    ReplyDelete
  17. This article is very much helpful and i hope this will be an useful information for the needed one. Keep on updating these kinds of informative things...

    Android App Development Company
    iOS App Development Company

    ReplyDelete
  18. Its fantatic explaintion lot of information gather it...nice article....
    seo company in Chennai

    ReplyDelete