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"
