SQL Inertview Questio List

Basics

---

1. SELECTing columns from a table
2. Aggregates Part 1: COUNT, SUM, MAX/MIN
3. Aggregates Part 2: DISTINCT, GROUP BY, HAVING

Intermediate

---

1. JOINs, ANSI-89 and ANSI-92 syntax
2. UNION vs UNION ALL
3. NULL handling: COALESCE & Native NULL handling
4. Subqueries: IN, EXISTS, and inline views
5. Subqueries: Correlated
6. WITH syntax: Subquery Factoring/CTE
7. Views

Advanced Topics

---

• Functions, Stored Procedures, Packages
• Pivoting data: CASE & PIVOT syntax
• Hierarchical Queries
• Cursors: Implicit and Explicit
• Triggers
• Dynamic SQL
• Materialized Views
• Query Optimization: Indexes
• Query Optimization: Explain Plans
• Query Optimization: Profiling
• Data Modelling: Normal Forms, 1 through 3
• Data Modelling: Primary & Foreign Keys
• Data Modelling: Table Constraints
• Data Modelling: Link/Corrollary Tables
• Full Text Searching
• XML
• Isolation Levels
• Entity Relationship Diagrams (ERDs), Logical and Physical
• Transactions: COMMIT, ROLLBACK, Error Handling

Interview Question for Experienced

1)      What is Table Datatype in SQL?

2)      Create proc ABC

              {

Select * from Customer;

Select * from Order;                  

}

How to get both table on .cs page?

 3) Difference between Querystring And POST ?

 4) What is TEMP table in SQL & Types & Scope ?

 5)What is HttpWebRequest ?

 6)What is View ?

 7) Where Session data Store?

8) Viewstate (Client Side or Server Side)?

  Answer-

            There are two different types of state management:

1. Client Side State Management
• View State
• Hidden Field
• Cookies
• Control State
2. Server Side State Management
• Session
• Application Object
• Caching
• Database

9)A gridview have 1000000 records. What you will do so that grid load faster?

Answer-we will leverage custom paging, caching and AJAX. We will also implement sorting to load    gridview fast.

10) Page life cycle stage ?

11) Can a class uses as a service Contract ?

12) what is fault Contract?

13)what is message contract?

14)Difference between class and interface? 

15) Difference between  abstract class and interface ?

16)what is index and there types?

17)can wcf interface have overloaded function? If yes then how can access at client side?

18)Types of joining.  And example of self join?

19)Difference between Stored Procedure and function?

20) Write code for URL rewriting code in web.config?

  Answer—

<urlMappings enabled="true">

    <add url="~/index.htm" mappedUrl="~/default.aspx"/>

 <add url="~/mypage" mappedUrl="~/mypage.aspx"/>

</urlMappings>

21)How to remove special characters from XML write code?

22) Xml case sensitive or not?

Answer-  Xml is Case Sensitive .

23) Difference between read only & constant & static variable ?

Answer-

            readonly is a runtime constant while const is a compile time constant i.e. you can intialize the value of variable only once at runtime in a constructor and if you try to modify later it will throw error while const is once declared during compilation can't be changed at runtime
another important thing about const is that const can be applied with value types only because you simply can't intialize the reference type at compilation. so to make a constant ref type you need to use Readonly
while static is altogether different from these two. static variable is a property of a class rather than the insatnce of class i.e. static represnt a kind of a global value for all the instances of that class

24)what is static variable ?

Answer-

            The static keyword can be used to declare variables, functions, class data members and class functions.
By default, an object or variable that is defined outside all blocks has static duration and external linkage. Static duration means that the object or variable is allocated when the program starts and is deallocated when the program ends. External linkage means that the name of the variable is visible from outside the file in which the variable is declared. Conversely, internal linkage means that the name is not visible outside the file in which the variable is declared.
The static keyword can be used in the following situations.

·         When you declare a variable or function at file scope (global and/or namespace scope), the static keyword specifies that the variable or function has internal linkage. When you declare a variable, the variable has static duration and the compiler initializes it to 0 unless you specify another value.

·         When you declare a variable in a function, the static keyword specifies that the variable retains its state between calls to that function.

·         When you declare a data member in a class declaration, the static keyword specifies that one copy of the member is shared by all instances of the class. A static data member must be defined at file scope. An integral data member that you declare as const static can have an initializer.

·         When you declare a member function in a class declaration, the static keyword specifies that the function is shared by all instances of the class. A static member function cannot access an instance member because the function does not have an implicit this pointer. To access an instance member, declare the function with a parameter that is an instance pointer or reference.

·         You cannot declare the members of a union as static. However, a globally declared anonymous union must be explicitly declared static.

25)why make a class as static ?

26)what is OUT keyword?

27)why declare a function as virtual ?

28)How many events you use of Page Life Cycle?

29)How many ways can take WCF reference at client side ?

30)What is end point in WCF ?

31) How many types of Binding in WCF?

32) Difference between basicHttpBinding and wsHttpBinding ?

33)Write a query for  Find top 2 record from table ?

34) what is  IFNULL in sql?

35)Difference between SQL Server 2005 & 2008?

Answer—

Sr No	SQL Server 2005	SQL Server 2008
1	XML datatype is introduced.	XML datatype is used.
2	Can not encrypt the entire database.	Can encrypt the entire database introduced in 2008.
3	Datetime is used for both date and time.	Date and time are seperately used for date and time
4	No table datatype is included.	Table datatype introduced.
5	SSIS is started using.	SSIS avails in this version.
6	CMS is not available.	Central Management Server(CMS) is Introduced.
7	PBM is not available	Policy based management(PBM) server is Introduced.

36) What is Application Pool ?

37)Difference between Application Pool  & Application Domain ?

38) What is Connection Pool ?

39) Difference between Web Form & Web Gardening ?

Type of authentication modes in ASP.NET

Authentication providers

Assuming IIS passes a request to ASP.net, what happens next? The answer depends on the configuration of ASP.net itself. The ASP.net architecture includes the concept of and authentication provider a piece of code whose job is to verify credentials and decide whether a particular request should be considered authenticated. Out of the box ASP.net gives you a choice of three different authentication providers.

• The windows Authentication provider lets you authenticates users based on their windows accounts. This provider uses IIS to perform the authentication and then passes the authenticated identity to your code. This is the default provided for ASP.net.
• The passport authentication provider uses Microsoft's passport service to authenticate users.
• The forms authentication provider uses custom HTML forms to collect authentication information and lets you use your own logic to authenticate users. The user's credentials are stored in a cookie for use during the session.

Selecting an authentication provider is as simple as making an entry in the web.config file for the application. You can use one of these entries to select the corresponding built in authentication provider:

<authentication mode="windows/Passport/Forms/None">

ASP.net also supports custom authentication providers. This simply means that you set the authentication mode for the application to none, then write your own custom code to perform authentication. For example, you might install an ISAPI filter in IIS that compares incoming requests to list of source IP addresses, and considers requests to be authenticated if they come from an acceptable address. In that case, you would set the authentication mode to none to prevent any of the .net authentication providers from being triggered.

Windows authentication and IIS

If you select windows authentication for your ASP.NET application, you also have to configure authentication within IIS. This is because IIS provides Windows authentication. IIS gives you a choice for four different authentication methods:

Anonymous, basic digest, and windows integrated

If you select anonymous authentication, IIS doesn't perform any authentication, Any one is allowed to access the ASP.NET application.

If you select basic authentication, users must provide a windows username and password to connect. How ever this information is sent over the network in clear text, which makes basic authentication very much insecure over the internet.

If you select digest authentication, users must still provide a windows user name and password to connect. However the password is hashed before it is sent across the network. Digest authentication requires that all users be running Internet Explorer 5 or later and that windows accounts to stored in active directory.

If you select windows integrated authentication, passwords never cross the network. Users must still have a username and password, but the application uses either the Kerberos or challenge/response protocols authenticate the user. Windows-integrated authentication requires that all users be running internet explorer 3.01 or later Kerberos is a network authentication protocol. It is designed to provide strong authentication for client/server applications by using secret-key cryptography. Kerberos is a solution to network security problems. It provides the tools of authentication and strong cryptography over the network to help to secure information in systems across entire enterprise

Passport authentication

Passport authentication lets you to use Microsoft's passport service to authenticate users of your application. If your users have signed up with passport, and you configure the authentication mode of the application to the passport authentication, all authentication duties are offloaded to the passport servers.

Passport uses an encrypted cookie mechanism to indicate authenticated users. If users have already signed into passport when they visit your site, they'll be considered authenticated by ASP.NET. Otherwise they'll be redirected to the passport servers to log in. When they are successfully log in, they'll be redirected back to your site

To use passport authentication you have to download the Passport Software Development Kit (SDK) and install it on your server. The SDK can be found at http://msdn.microdoft.com/library/default.asp?url=/downloads/list/websrvpass.aps. It includes full details of implementing passport authentication in your own applications.

Forms authentication

Forms authentication provides you with a way to handle authentication using your own custom logic with in an ASP.NET application. The following applies if you choose forms authentication.

1. When a user requests a page for the application, ASP.NET checks for the presence of a special session cookie. If the cookie is present, ASP.NET assumes the user is authenticated and processes the request.
2. If the cookie isn't present, ASP.NET redirects the user to a web form you provide
3. You can carry out whatever authentication, checks you like in your form. When the user is authenticated, you indicate this to ASP.NET by setting a property, which creates the special cookie to handle subsequent requests.

Interview Question

What is SQL Injection?
What is DBCC? Give few examples.
What is difference between View and Materialized view?
What is CTE (Common Table Expression)?
What is difference between clustered and non clustered index?
What is use of EXCEPT clause? How it differs from NOT IN clause.
What is difference between Index Seek vs. Index Scan?
What is ROW_NUMBER function?
What is Trigger?
What is Scheduled job and how to create it?
What is OPENXML in SQL Server?
What are Sparse columns?
What is RANK function?
What are cursors and when they are useful?
What is log shipping?
What is SQL Profiler?
What is Similarity and Difference between Truncate and Delete in SQL?
What is Normalization of database? What are its benefits?
What is Fill factor?
What are different types of replication in SQL Server?
What is REPLACE and STUFF function in SQL Server?
Give a example to search for a string in all stored procedure in SQL Server.
What are Magic tables in SQL Server?
What is difference between stored procedure and user defined function?
What are ACID properties of Transaction?
What are COMMIT and ROLLBACK in SQL?
What is a Linked Server?
What is a WITH(NOLOCK)?

Database Optimization Techniques

SQL Optimization Techniques:

1) The sql query becomes faster if you use the actual columns names in SELECT statement instead of than '*'.
For Example: Write the query as
SELECT id, first_name, last_name, age, subject FROM student_details;
Instead of:
SELECT * FROM student_details;

2) HAVING clause is used to filter the rows after all the rows are selected. It is just like a filter. Do not use HAVING clause for any other purposes.
For Example: Write the query as
SELECT subject, count(subject)
FROM student_details
WHERE subject != 'Science'
AND subject != 'Maths'
GROUP BY subject;
Instead of:
SELECT subject, count(subject)
FROM student_details
GROUP BY subject
HAVING subject!= 'Vancouver' AND subject!= 'Toronto';

3) Sometimes you may have more than one subqueries in your main query. Try to minimize the number of subquery block in your query.
For Example: Write the query as
SELECT name
FROM employee
WHERE (salary, age ) = (SELECT MAX (salary), MAX (age)
FROM employee_details)
AND dept = 'Electronics';
Instead of:
SELECT name
FROM employee
WHERE salary = (SELECT MAX(salary) FROM employee_details)
AND age = (SELECT MAX(age) FROM employee_details)
AND emp_dept = 'Electronics';

4) Use operator EXISTS, IN and table joins appropriately in your query.
a) Usually IN has the slowest performance.
b) IN is efficient when most of the filter criteria is in the sub-query.
c) EXISTS is efficient when most of the filter criteria is in the main query.
For Example: Write the query as
Select * from product p
where EXISTS (select * from order_items o
where o.product_id = p.product_id)
Instead of:
Select * from product p
where product_id IN
(select product_id from order_items

5) Use EXISTS instead of DISTINCT when using joins which involves tables having one-to-many relationship.
For Example: Write the query as
SELECT d.dept_id, d.dept
FROM dept d
WHERE EXISTS ( SELECT 'X' FROM employee e WHERE e.dept = d.dept);
Instead of:
SELECT DISTINCT d.dept_id, d.dept
FROM dept d,employee e
WHERE e.dept = e.dept;

6) Try to use UNION ALL in place of UNION.
For Example: Write the query as
SELECT id, first_name
FROM student_details_class10
UNION ALL
SELECT id, first_name
FROM sports_team;
Instead of:
SELECT id, first_name, subject
FROM student_details_class10
UNION
SELECT id, first_name
FROM sports_team;

7) Be careful while using conditions in WHERE clause.
For Example: Write the query as
SELECT id, first_name, age FROM student_details WHERE age > 10;
Instead of:
SELECT id, first_name, age FROM student_details WHERE age != 10;
Write the query as
SELECT id, first_name, age
FROM student_details
WHERE first_name LIKE 'Chan%';
Instead of:
SELECT id, first_name, age
FROM student_details
WHERE SUBSTR(first_name,1,3) = 'Cha';
Write the query as
SELECT id, first_name, age
FROM student_details
WHERE first_name LIKE NVL ( :name, '%');
Instead of:
SELECT id, first_name, age
FROM student_details
WHERE first_name = NVL ( :name, first_name);
Write the query as
SELECT product_id, product_name
FROM product
WHERE unit_price BETWEEN MAX(unit_price) and MIN(unit_price)
Instead of:
SELECT product_id, product_name
FROM product
WHERE unit_price >= MAX(unit_price)
and unit_price <= MIN(unit_price)
Write the query as
SELECT id, name, salary
FROM employee
WHERE dept = 'Electronics'
AND location = 'Bangalore';
Instead of:
SELECT id, name, salary
FROM employee
WHERE dept || location= 'ElectronicsBangalore';
Use non-column expression on one side of the query because it will be processed earlier.
Write the query as
SELECT id, name, salary
FROM employee
WHERE salary < 25000;
Instead of:
SELECT id, name, salary
FROM employee
WHERE salary + 10000 < 35000;
Write the query as
SELECT id, first_name, age
FROM student_details
WHERE age > 10;
Instead of:
SELECT id, first_name, age
FROM student_details
WHERE age NOT = 10;
8) Use DECODE to avoid the scanning of same rows or joining the same table repetitively. DECODE can also be made used in place of GROUP BY or ORDER BY clause.
For Example: Write the query as
SELECT id FROM employee
WHERE name LIKE 'Ramesh%'
and location = 'Bangalore';
Instead of:
SELECT DECODE(location,'Bangalore',id,NULL) id FROM employee
WHERE name LIKE 'Ramesh%';
9) To store large binary objects, first place them in the file system and add the file path in the database.
10) To write queries which provide efficient performance follow the general SQL standard rules.
a) Use single case for all SQL verbs
b) Begin all SQL verbs on a new line
c) Separate all words with a single space
d) Right or left aligning verbs within the initial SQL verb