Thursday, March 8, 2018

SQL Server

http://www.c-sharpcorner.com/blogs/how-to-find-nth-highest-and-lowest-salary-in-sqlThis is the one of the most common question asked in SQL. Once I was in an interview, the same question was asked to me.
I knew the answer so I wrote the query to find the Nth highest salary in a SQL table. I used the top keyword to write the SQL query.
But after that interviewer asked me, "Could you please write the query without using TOP keyword?". I did not know the answer.

After interview got finished, I searched for the answer and I found some interesting articles.

So here I am posting the method to find out the Nth highest and lowest salary in a SQL table with both, using TOP keyword and without using it, methods.

So let's start.

Here I have a SQL table which is having employees names and their salaries.
To find out the Nth highest salary (for example: here I am finding 3rd highest salary), I wrote the query like below 
  1. SELECT TOP 1 Salary AS 'Higest Salary',Name FROM (SELECT DISTINCT TOP 3 Salary,Name FROM tblSalary ORDER BY Salary DESC) a ORDER BY Salary ASC   
and the result is :



To find out the Nth lowest salary (For Example: here I am finding 3rd lowest salary) I wrote the query like below
  1. SELECT TOP 1 Salary AS 'Lowest Salary',Name FROM (SELECT DISTINCT TOP 3 salary,Name FROM tblSalary ORDER BY Salary ASC) a ORDER BY Salary DESC   
Result:




Now I founded the query for the same but without using TOP keyword.
  1. SELECT Name, Salary FROM tblSalary a1 WHERE N-1= (SELECT COUNT(DISTINCT Salary) FROM tblSalary a2 WHERE a2.Salary < OR > a1.Salary)   
You just need to replace "N" with the "Number", like below I need to find out the 3rd highest and lowest salary, so I replaced N with 3.
To find 3rd highest salary 
  1. SELECT Name, Salary FROM tblSalary a1 WHERE 3-1= (SELECT COUNT(DISTINCT Salary) FROM tblSalary a2 WHERE a2.Salary > a1.Salary)   
Result:



To find 3rd lowest salary 
  1. SELECT Name, Salary FROM tblSalary a1 WHERE 3-1= (SELECT COUNT(DISTINCT Salary) FROM tblSalary a2 WHERE a2.Salary < a1.Salary)   
Result:



I hope this would be helpful.


    Thursday, September 2, 2010

    Computer Science: ADO .net tutorial

    Computer Science: ADO .net tutorial: "ado .net tutorial contain"

    ado .net tutorial

    ADO.NET offers two central Data Access Components. In this Article we will discuss about their features and when to use what.

    ADO.NET provides two central Data Access Components. The excellent thing is that, they are common across all Databases, be it SQL Server or other competitive databases. Its only the namespace to be used, that differs, while using a Database other than SQL Server.

    The two Data Access Compnents are:


    1. DataSet

    2. The .NET data provider, which is a set of components including the Connection, Command, DataReader, and DataAdapter objects.


    Its common that the doubt arises on when to use DataReader and DataAdapter, DataSet.

    The thumb rule I would suggest is,


    1. If your Data access operations is mainly fetching and displaying the records and doesnt involve insert/update/delete statements and other manipulations (forward only, read only) actions, go for the DataReader.


    DataReader offers a forward only read stream of access to the records. It is very useful in cases where we just grab the data from the database to display in DataGrid, Label and other Webcontrols.

    However, DataReader requires the connection with the database open until its operation is completed.


    2. If your Data access operations extend to as simple as an Insert/Update/Delete statement to as complex as using it as a return type from a web service, go for the DataSet. The DataSet has the following advantages.

    i. Complex Data type supporting numerous methods such as ReadXML which reads new XML data to form a dataset, WriteXML which provides an XML format of the existing data, etc.,

    ii. Works on the disconnected architecutre i.e. The connection doesnt need to be on for the DataSet to perform the manipulations and other actions and is required only at the time of Updating the records to the Database Table.

    iii. Provides an In-memory representation of the Data. Can contain one or more DataTable objects that have primary key, foreign key, and constraints between them and can enforce constraints such as unique or be configured to ignore them
    iv. Has Individual elements such as DataTable, DataRow and DataColumn to access specific values.

    v. While using DataSet and DataAdapter, you don't need to explicitly open the connection. DataAdapter automatically opens and closes the connection as and when required.

    Hope it provided some insight into DataReader, DataSet and DataAdapter.

    Is Dataset slower than Data Reader due to…?

    A) Datasets can be 10+ times slower than Data Reader at retrieving data from DB. I assume this is due to overhead of Datasets having to deal with relations etc.

    B) But is the speed difference between Datasets and Data Reader due to Datasets having to retrieve more data ( information about relations ... ) from DB, or due to application having to do more processing, or both?

    C) I assume Data Adapter uses Data Reader under the hood and thus the number of commands application needs to execute in order to retrieve 100 rows with Data Adapter is equal to or greater than number of commands app needs to execute if these 100 rows are retrieved directly by Data Reader?!

    2) Does Data Reader retrieve one row at the time or one field (of a particular row) at the time?

    Friday, August 20, 2010

    What is MVC in .net

    Model View Controller architecture (or pattern) allows us to separate different parts of our applications into tiers to fulfill this need.

    MVC Overview

    Model View Controller architecture aims to separate an application into three parts:

    Model: It is the business logic of an application. From an object oriented perspective it would consist of a set of classes that implement the critical functionality of an application from a business point of view.

    View: It can consist of every type of interface given to the user. In ASP.NET the view is the set of web pages presented by a web application.

    Controller: This part of the architecture is the most difficult to explain, hence the most difficult to implement in many platforms. The controller is the object that allows the manipulation of the view. Usually many applications implement Model-Controller tiers that contain the business logic along with the necessary code to manipulate a user interface. In an ASP.NET application the controller is implicitly represented by the code-behind or the server side code that generates the HTML presented to the user.

    Implementing MVC in ASP.NET

    A basic diagram that would help us understand perfectly the specific parts that implement the Model View Controller architecture in an ASP.NET application is presented below:

    MVC implementation in ASP.NET

    MVC Model Implementation

    When implementing the business logic of an application it is a must to use a Class Library project in order to generate a .dll file that will encapsulate all the functionality. This is critical as we as professional developers would not like to jeopardize the source code of a software product by placing the actual .cs files as a reference in a web application.

    This type of project can be easily created in Visual Studio 2005 under the Visual C# or Visual Basic tabs:

    Creating a Class Library Project

    As a tutorial example we will develop a simple calculator under a new namespace we will call "Math".

    Once the project is created we will add a class called Calculator:

    Adding Calculator class

    As the code is very simple and a sample is provided in this tutorial we will not get into much detail as far as how it is developed. The only important thing we need to mention is the way errors have to be handled in this class. Take a look at the following code:

    1. protected float Divide(float fNumber1, float fNumber2)
    2. {
    3. if (fNumber2 == 0)
    4. {
    5. throw new Exception( "Second number cannot be equal to zero.");
    6. }

    7. return (fNumber1 / fNumber2);
    8. }

    When implementing the Divide function we need to ensure that the user would not be able to set the "fNumber2" parameter (line 1) to zero as a division between zero does not exist. The validation statement in lines 3-6 takes care of this case but the important fact we need to notice is that this class will NEVER use specific methods to present errors like message boxes or writing into labels. Errors captured in the model part of the architecture ALWAYS have to be presented in the form of exceptions (line 5). This will allow us to use this object in several types of applications like ASP.NET applications, Windows applications, Web services, etc.

    Once we have finished coding our Calculator class the project has to be built in order to get the .dll file we will use in our Web application.

    MVC View-Controller Implementation

    The View and the Controller objects will be implemented by using a common ASP.NET Website. Once we have created our project we need to add the reference to the .dll file we created before.

    The option to do this can be found in the context menu when right-clicking the project in the solution explorer:

    Adding the a reference

    We can find the file in the path "\bin\Release" (or "\bin\Debug" depending on how you build your class library) inside our main folder containing the math class library project:

    Adding Math.dll reference

    Once we have referenced our library we will create a simple web page that will allow us to choose between the four basic arithmetic operations and type two different numbers to operate.

    The web page will look like this:

    Calculator Web page

    In the code behind we need to reference the Math namespace in order to use our Calculator class. The following statement will do that:

    using Math;

    As the code for this application is also simple we will only explain the method called when the "Operate!" button is clicked:

    1. protected void btnOperate_Click(object sender, EventArgs e)
    2. {
    3. if (pbValidateNumbers())
    4. {
    5. Calculator cOperator = new Calculator();
    6. try
    7. {
    8. txtResult.Text = cOperator.Operate(float.Parse(txtNumber1.Text.Trim()),float.Parse(txtNumber2.Text.Trim()), Convert.ToInt16(rblOperations.SelectedValue)).ToString();
    9. lbError.Text = "";
    10. }
    11. catch (Exception ex)

    12. {
    13. txtResult.Text = "";
    14. lbError.Text = ex.Message;
    15. }
    16. }
    17.}

    In line 3 we call the bool function "pbValidateNumbers" that will return true if the numbers typed in both textboxes are valid. These types of validations have to be performed by the controller object as they allow the interface to work properly and have nothing to do with the business logic.
    In line 5 we create an instance of our Calculator class so we can perform the arithmetic operation. We call the method "Operate" (line 8) and return the value in another textbox. An important thing to mention is that we have to use a try-catch statement (lines 6-15) to handle any exception that could be thrown by our method "Operate" as every error caught in our Calculator class is handled by throwing a "digested" exception that is readable to the user.

    In the code above we can appreciate how well encapsulated the business logic is, hence it can be reused in several applications without having to code it again.

    Advantages of using MVC in ASP.NET

    • There's no duplicated code.

    • The business logic is encapsulated; hence the controller code is transparent and safer.

    • The business logic can be used in several front ends like Web pages, Web services, Windows applications, services, etc.

    • Exception handling is well managed showing the user only digested error messages.

    • Testing every part of an application is easier as it can be done separately using automated methods.

    • Application changes are easier to apply as they are focused in one part of the architecture only.


    For More Click Here>>>

    Tuesday, August 17, 2010

    SQL Server Questions

    What are the OS services that the SQL Server installation adds?
    MS SQL SERVER SERVICE, SQL AGENT SERVICE, DTC (Distribution transac co-ordinator)
    What is an execution plan? When would you use it? How would you view the execution plan?
    An execution plan is basically a road map that graphically or textually shows the data retrieval methods chosen by the SQL Server query optimizer for a stored procedure or ad-hoc query and is a very useful tool for a developer to understand the performance characteristics of a query or stored procedure since the plan is the one that SQL Server will place in its cache and use to execute the stored procedure or query. From within Query Analyzer is an option called “Show Execution Plan” (located on the Query drop-down menu). If this option is turned on it will display query execution plan in separate window when query is ran again.
    Which virtual table does a trigger use?
    Inserted and Deleted.
    How to know which INDEX a table is using in SQL SERVER?
    SELECT table_name,index_name FROM user_constraints
    What is Cross Join in SQL Server?
    A cross join that does not have a WHERE clause produces the Cartesian product of the tables involved in the join. The size of a Cartesian product result set is the number of rows in the first table multiplied by the number of rows in the second table. The common example is when company wants to combine each product with a pricing table to analyze each product at each price.
    What is Self Join in SQL Server?
    This is a particular case when one table joins to itself, with one or two aliases to avoid confusion. A self join can be of any type, as long as the joined tables are the same. A self join is rather unique in that it involves a relationship with only one table. The common example is when company have a hierarchal reporting structure whereby one member of staff reports to another.
    What is the basic functions for master, msdb, model, tempdb databases?
    The Master database holds information for all databases located on the SQL Server instance and is the glue that holds the engine together. Because SQL Server cannot start without a functioning master database, you must administer this database with care.

    The msdb database stores information regarding database backups, SQL Agent information, DTS packages, SQL Server jobs, and some replication information such as for log shipping.

    The tempdb holds temporary objects such as global and local temporary tables and stored procedures.

    The model is essentially a template database used in the creation of any new user database created in the instance.
    What is the REPLACE function in SQL?
    REPLACE function replaces all occurrences of the second given string expression in the first string expression with a third expression.
    Syntax REPLACE(string_expression, search_string, replacement_string), where every incidence of search_string found in the string_expression will be replaced with eplacement_string.
    Note: Returns NULL if any of the arguments is NULL.
    What is the difference between CAST and CONVERT in SQL?
    Both CAST and CONVERT perform datatye conversions. In most respects, they both do the same thing, with the exception that CONVERT also does some date formatting conversions that CAST doesn't offer.
    Note:- If CONVERT does everything that CAST does, and CONVERT also does date conversions, then why would ever use CAST? Simple answer for that - ANSI compliance. CAST is ASNI-compliant, and CONVERT isn't.
    How to use EXISTS operator OR
    How to use subqueries with the EXISTS operators?
    A subquery can be used with the EXISTS operator as "EXISTS (subquery)", which returns true if the subquery returns one or more rows. Example -
    Select custID, CompanyName from Customers cu where EXISTS (Select OrderID from Orders o where o.custID = cu.custID)
    Note:- When you use EXISTS, you don't really return data - instead, you return a simple TRUE/FLASE regarding the existence of data that meets the criteria established in the query that the EXISTS statement is operating against.
    How to SELECT TOP N records from mytable without using TOP keyword?
    SET ROWCOUNT N
    select * from mytable
    SET ROWCOUNT 0

    To turn this option off (so that all rows are returned), specify SET ROWCOUNT 0.
    What is the advantage of SET NOCOUNT ON?
    When we use SELECT and DML statement in SQL. SQL SERVER return a message which specify the number of rows affected by these statements. This information helps developer when they are debugging the code other-wise this is not useful. We can disable this by typing SET NOCOUNT ON. Setting SET NOCOUNT ON for stored procedure contains lots of statements, loops increases the performance and boost network traffic.
    What are the types of constraints in SQL Server?
    There are three types of constraints in SQL Server -

    1. Domain Constraint - deals with one are more columns.
    2. Entity Constraint - are all about individual rows.
    2. Referential Integrity Constraint - are created when a value in one column must match the value in another column - in either the same table or in a different table.

    What is the CASCADE action in SQL Server?
    Bu default, you can not delete a record or update the referenced column in a referenced table if that record is referenced from the dependent table. If you want to be able to delete or update such records, then you need to set up a CASCADE action for the delete and/or update.
    What is the primary key in SQL Server?
    Primary keys are the unique identifiers for each row in a table. They must contains unique values (and hence can not be NULL). Because of their importance in the relational database, primary keys are the most fundamental of all keys and constraints.

    A table can a maximum of one primary key. A primary key ensures uniqueness within the columns declared as being part of that primary key and that unique value serve as an identifier for each row.

    For Read More Click Here>>