T-SQL Interview Questions

Find Nth -4 maximum value in SQL Server

SELECT TOP 1 *

FROM (SELECT TOP 4 *

FROM employee ORDER BY Salary desc)e2 ORDER BY Salary asc

Select * From Employee E1 Where

(n-1) = (Select Count(Distinct(E2.Salary)) From Employee E2 Where

E2.Salary > E1.Salary)

Fetch the employee based on EmpQualification
select a.EmpID,a.EmpFirstName,a.Salary,a.Designation, b.EmpQualification from Employee a
inner join EmployeeDetails b on a.EmpID=b.EmpID

To avoid duplicates we need to used the distinct keyword
select distinct a.EmpID,a.EmpFirstName,a.Salary,a.Designation, b.EmpQualification from Employee a
inner join EmployeeDetails b on a.EmpID=b.EmpID

INSERT INTO EMPLOYEE(EmpID, EmpFirstName,EmpLastName,City,Salary,Designation) VALUES(5,'Santhi','Jayakantham','Dharmapuri',1000,'PM')

To list down all the rows from the left table with matching columns in the right table. This can be achieved using Left outer join.
select distinct a.EmpID,a.EmpFirstName,a.Salary,a.Designation, b.EmpQualification from Employee a
left outer join EmployeeDetails b on a.EmpID=b.EmpID

Appropriate right tables column will be appended with Null value.
What about getting the all the right table row with corresponding matching column from the left table.

select distinct a.EmpID,a.EmpFirstName,a.Salary,a.Designation, b.EmpQualification from Employee a
right outer join EmployeeDetails b on a.EmpID=b.EmpID

Getting the matching data from two tables. Suppose I need to list the employees who is having qualification = 'BE'
select distinct a.EmpID,a.EmpFirstName,a.Salary,a.Designation, b.EmpQualification from Employee a
inner join EmployeeDetails b on a.EmpID=b.EmpID where b.EmpQualification='BE'

8. List down all the managers and their employee ID
select b.ManagerID,a.EmpFirstName as ManagerName from employee a
inner join employee b on a.EmpID=b.ManagerID

List down the employees who is having managers,
select b.EmpID,b.EmpFirstName,b.ManagerID,a.EmpFirstName from employee a
inner join employee b on a.EmpID=b.ManagerID

List down all the employees with managers details(If exists)
select b.EmpID,b.EmpFirstName,b.ManagerID,a.EmpFirstName from employee a
right outer join employee b on a.EmpID=b.ManagerID

Xml

XML:-

Xml is very simple data store. It will occupy very less memory almost like a text file. If you learn to utilize xml features, then you can easily manage your data with the help of xml.

Xml will reduce your programming burden by its simplicity. There is no chance of difficulty in creation and maintenance of an xml database.

All the time, xml gave me a nice experience while handling data. I am very much satisfied with performance of xml. That satisfaction leads me to write this article.

In this Article, we are going to see

• How to create an xml file?
• How to add or write data into an xml file?
• How to read data from an xml file?
• How to update a data which is stored in an xml file?
• How to remove data from an xml file?

We are going to see the answer for above questions briefly. I don't want to go depth. I just want to clarify the basic concepts simply and clearly, by not exceeding few words.

I have not used any mechanism or tool or control. It's similar to read and write of a text file. Here I meant database as a simple Xml file, nothing more than that.

In order to perform those operations we have several classes in C#. But based on my experience, I strongly recommend XmlDocument class for above all operations.

Create

XmlTextWriter class is used to create an Xml file. In this, we just specify the filepath and type of xml encoding.after that, we have to create a root element of xml document.

Ex:

We are going to create an xml file with CustomerDetails as root element

1. XmlTextWriter xtw;
2. xtw = new XmlTextWriter(filepath, Encoding.UTF8);
3. xtw.WriteStartDocument();
4. xtw.WriteStartElement("CustomerDetails");
5. xtw.WriteEndElement();
6. xtw.Close();

Comment for each line

1. //create instance for xmltextwriter.
2. //assign file path and encoding
3. //write start document
4. //create root element
5. //end root element
6. //close file. It is automatically saved.

Note: You have to include System.Xml, System.IO, System.Text Packages in order to use above classes.

Output

Write- [insert into database]

We can add data into an xml file by just adding child nodes to the root element. In this, we just create some xml elements and append those elements to the root as a child element and save the xml document.

Before writing, one thing is very important. All the data stored into the xml file will be treated as String only. So we must convert all data types to string.

Ex:

We just add a customer "abc" with address as "xyz,india"

1. XmlDocument xd = new XmlDocument();
2. FileStream lfile = new FileStream(filepath, FileMode.Open);
3. xd.Load(lfile);
4. XmlElement cl = xd.CreateElement("Customer");
5. cl.SetAttribute("Name", "abc");
6. XmlElement na = xd.CreateElement("Address");
7. XmlText natext = xd.CreateTextNode("xyz,india");
8. na.AppendChild(natext);
9. cl.AppendChild(na);
10. xd.DocumentElement.AppendChild(cl);
11. lfile.Close();
12. xd.Save(filepath);

Comment for each line

1. //object creation for XmlDocument class
2. //open an xml file using file stream
3. //load opened file in xml document
4. //create an xml element
5. //set attribute for that element
6. //create an xml element
7. //create text for xml element
8. //append address text to address node
9. //append address node to root element
10. //append root element to xml document
11. //close the file stream
12. //save the xmldocument content to the file

Output

xyz,india

Read-[fetch data]

We can retrieve data from an xml file using XmlDocument class.here we just retrieve data based on the name of the element.we fetch data as whole and take what we want by navigating through its contents.

This reading will return everything as String only.After reading we must convert the data into its original datatype.

Ex:

Here we just read the address of the customer named as "abc"

1. XmlDocument xdoc= new XmlDocument();
2. FileStream rfile = new FileStream(path, FileMode.Open);
3. xdoc.Load(rfile);
4. String address;
5. XmlNodeList list = xdoc.GetElementsByTagName("Customer");
6. for (int i = 0; i <>
7. {
8. XmlElement cl = (XmlElement)xdoc.GetElementsByTagName("Customer")[i];
9. XmlElement add = (XmlElement)xdoc.GetElementsByTagName("Address")[i];
10. if ((cl.GetAttribute("Name")) == "abc")
11. {
12. address = add.InnerText;
13. break;
14. }
15. }
16. rfile.Close();

Comment for each Line

1. //creation of XmlDocument class Instance
2. //create a file stream and open the file to be updated
3. //load file into xmldocument instance
4. //create a string variable to store address
5. //find out the no of elements available in xml file
6. //navigate through each and every nodes
8. //retrieve the customer node
9. //retrieve the addresss node
10. //compare it with node to be read
12. //assign address as innertext of node
13. //break and get away from the for loop
16. //close the file stream

Update- [Updation of field]

Updation of data is the simplest process.we just read the required data and modify its content and save it.we just retrieve a field (element) and change its InnerText or Set Its Attributes and Save the xml file.

Ex:

Here we change the customer name from "abc" to "efgh" and change his address from "xyz,india" to "pqrs,india".

1. XmlDocument xdoc = new XmlDocument();
2. FileStream up = new FileStream(filepath, FileMode.Open);
3. xdoc.Load(up);
4. XmlNodeList list = xdoc.GetElementsByTagName("Customer");
5. for (int i = 0; i <>
6. {
7. XmlElement cu = (XmlElement)xdoc.GetElementsByTagName("Customer")[i];
8. XmlElement add = (XmlElement)xdoc.GetElementsByTagName("Address")[i];
9. if (cu.GetAttribute("Name") == "abc")
10. {
11. cu.SetAttribute("Name", "efgh");
12. add.InnerText = "pqrs,india";
13. break;
14. }
15. }
16. up.Close();
17. xdoc.Save(filepath);

Comments for each line

1. //creation of XmlDocument class Instance
2. //create a file stream and open the file to be updated
3. //load file into xmldocument instance
4. //find out the no of elements available in xml file
5. //navigate through each and every nodes
7. //retrieve the customer node
8. //retrieve the addresss node
9. //compare it with node to be changed
11. //set new attribute value
12. //set new innertext of node
13. //break and get away from the for loop
16. //close the file stream
17. //save the xml file

Output

pqrs,india

Remove

Removing process is also a simple one. We just navigate through all nodes and finds node needs to be removed and remove it. We just remove the child element from the root element and save it

Ex:

We just remove the customer named as "efgh".

1. FileStream rfile = new FileStream(filepath, FileMode.Open);
2. XmlDocument tdoc = new XmlDocument();
3. tdoc.Load(rfile);
4. XmlNodeList list = tdoc.GetElementsByTagName("Customer");
5. for (int i = 0; i <>
6. {
7. XmlElement cl = (XmlElement)tdoc.GetElementsByTagName("Customer")[i];
8. if (cl.GetAttribute("Name") == "efgh")
9. {
10. tdoc.DocumentElement.RemoveChild(cl);
11. }
12. }
13. rfile.Close();
14. tdoc.Save(filepath);

comment for each line

1. //open a file through file stream
2. //create Xml Document Instance
3. //load file
4. //find no of elements available in XmlDocument
5. //Iterate through each element
7. //get customer element
8. //Compare with element to be removed
10. //Remove element from document
13. //close stream
14. //save file

Output

We have seen all the necessary operations that are needed to create and maintain an xml file [database].

Of course, Xml database have some limitations. But up to some level we can run our application with xml as a database for its simplicity. I haven't told anything based on any reference. I said everything based on my own experience.

XML:-

Bssic XML:à

http://www.quackit.com/xml/tutorial/

Using XML in ASP.NET

XML is a cross-platform, hardware and software independent, text based markup language, which enables you to store data in a structured format by using meaningful tags. XML stores structured data in XML documents that are similar to databases. Notice that unlike Databases, XML documents store data in the form of plain text, which can be used across platforms.

In an XML document, you specify the structure of the data by creating a DTD or an XML schema. When you include a DTD in an XML document, the software checks the structure of the XML document against the DTD. This process of checking the structure of the XML document is called validating. The software performing the task of validating is called a validating parser.

The following code defines the structure of an XML document that will store data related to books:

xml version="1.0"?>
<Books>
<Book bid="B001">
<Title> Understanding XML Title>
<Price> $30 Price>
<author>
<FirstName> Lily FirstName>
<LastName>
Hicks <LastName>
author>
Book>
<Book bid="B002">
<Title> .NET Framework Title>
<Price> $45 Price>
<author>
<FirstName> Jasmine FirstName>
<LastName>
Williams <LastName>
author>
Book>
Books>

.NET Support for XML

The .NET Framework has extensive support for working with XML documents. IN the .NET framework, the support for XML documents includes:

• XML namespace
• XML designer
• XML Web Server control
• XML DOM support

XML Namespace

The System.Xml namespace provides a rich set of classes for processing XML data. The commonly used classes for working with XML data are:

• XmlTextReader: Provides forward only access to a stream of XML data and checks whether or not an XML document is well formed. This class neither creates as in-memory structure nor validates the XML document against the DTD. You can declare an object of the XmlTextReader class by including the System.Xml namespace in the application. The syntax to declare an object of this class is as follows:

XmlTextReader reader = new XmlTextReader("XML1.xml");

It is important to note that the .xml file you pass as an argument to the constructor of the XmlTextReader class exists in the \WINNT\System32 folder.

• XmlTextWriter: Provides forward only way of generating streams or files containing XML data that conforms to W3C XML 1.0. If you want to declare an object of the XmlTextWriter class, you must include the System.Xml. The syntax to decare an object of this class is as follows:

XmlTextWriter writer = new XmlTextWriter(Response.Output);

Here Response.Output represents an outgoing HTTP response stream to which you want to send the XML data.

• XmlDocument: Provides navigating and edinting features of the nodes in an XML document tree. XmlDocument is the most frequently used class in ASP.NET applications that use XML documents. It also supports W3C XML DOM. XML DOM is an in-memory representation of an XML document. It represents data in the form of hierarchically organized object nodes and allows you to programmatically access and manipulate the elements and attributes present in an XML document.

XmlDocument doc = new XmlDocument();

• XmlDataDocument: Provides support for XML and relational data in W3C XML DOM. You can use this class with a dataset to provide relational and non-relational views of the same set of data. This class is primarily used when you want to access the functions of ADO.NET. The syntax to declare an object of this class is as follows:

DataSet ds=new DataSet();
XmlDataDocument doc=new XmlDocument(ds);

There are a number of reasons to use XmlDataDocument:

• It gives you the freedom to work with any data by using the DOM.
• There is synchronization between an XmlDatadocument and a DataSet, and any changes in one will be reflected in the other.
• When an XML document is loaded into an XmlDatadocument, the schema is preserved.

You need to include System.Xml namespace.

• XmlPathDocument: Provides a read-only cache for XML document processing by using XSLT. This class is optimizied for XSLT processing and does not check for the conformity of W3C DOM. For this reason, you can create an instance of this class to process an XML document faster. To create an instance of the XPathDocument class, you need to include the System.Xml.XPath namespace in the application. The Syntax to declare an object of this class is as follows:

XmlPathDocument doc=new XmlPathDocument("XML1.xml");

• XmlNodeReader: Provides forward-only access to the data represented by the XmlDocument or XmlDataDocument class. If you want to create an instance of the XmlNodeReader class, you need to include the System.Xml namespace. The syntax to declare an object of this class is as follows:

XmlDocument doc=new XmlPathDocument();
XmlNodeReader reader=new XmlNodeReader(doc);

• XslTransform: Provides support for a XSLT 1.0 style sheet syntax that enables you to transform an XML document by using XSL style sheets. If you want to create an instance of the XslTransform class, you need to include the System.Xml.Xsl namespace in the application. The syntax to declare an object of this class is as follows:

Xsltransform xslt = new XslTransform ();

XML Designer

Visual Studio .NET provides the XML designer that you can use to create and edit XML documents. For example, if you need to create an XML document that contains the details of books available in an online bookstore, you need to perform the following steps by using the XML designer of Visual Studio .NET:

1. Create a new ASP.NET Web application.
2. Select the Add New Item option
3. Select XML File as the template from the right pane. Specify the name as "books.xml" and click open.
4. The XML designer is displayed. The XML designer has automatically generated a line that notifies the browser that the document being processed is an XML document, as displayed in the figure:

5. At the bottom of the designer window, there are two tabs, XML and Data. In the XML tab enter the following lines of code after the first line in the XML designer:

The Data view displays the XML data represented by the XML document. When you switch to the Data view, the data appears, as displayed in the following figure:

In addition to just viewing data in the Data view, you can also add data directly to an existing XML document. For this, just click on the new row below the existing data and enter your values, and shown in the figure:

XML Web Server Control

An XML Web Server control is used to display the contents of an XML document without formatting or using XSL Transformations. You can optionally specify a XSLT style sheet that formats the XML document before it is displayed in an XML server control. The XML Web Server control belongs to the System.Web.UI.WebControls namespace. You can add an XML Web Server control to a Web form by dragging the control from the Web forms tab of the toolbox.

The XML Web Server control has the following properties:

• DocumentSource: Allows you to specify the URL or the path of the XML document to be displayed in the Web form.
• TransformSource: Allows you to specify the URL of the XSLT file, which transforms the XML document into the required format before it is displayed in the Web form.
• Document: Allows you to specify a reference to an object of the XMLDocument class. This property is available only at runtime.
• Transform: Allows you to specify a reference to an object of the XMLTransform class. This property is available only at runtime.

A practical example for the same is shown in the last section of this tutorial.

XML Document Object Model Support

When you want to access and display XML data in Web Applications, you use the XML Web server control and set its properties at design time. In certain situation, you may need to display the XML data based on specific conditions. In such cases, you will have to access the XML data programmatically.

An XML document consists of elements and attributes. For this reason, you can access XML data programmatically. Note that XML DOM allows you to access and manipulate the elements and attributes present in an XML document programmatically.

To implement XML DOM, the .NET framework provides a set of additional classes included in the System.Xml namespace. These classes enable you to access the XML data programmatically. Some of the classes in the System.Xml namespace are as follows:

• XmlDocumentType: Represents the DTD used by an XML document.
• XmlElement: Represents one element from an XML document.
• XmlAttribute: Represents one attribute of an element.

Bind XML Data to Web Form Controls

An XML document cannot be directly bound to server controls. To implement data binding, you first need to load the XML document into a DataSet. Then, you can bind server control with this DataSet.

The Dataset object has a ReadXml method, which is used to read data into the DataSet from an XML file. The ReadXml method can read XML documents from the Stream, File, TextReader, and XmlReader sources. The synatx for the ReadXml method is as follows:

ReadXml (Stream | FileName | textReader | XmlReader , XmlReadMode )

The XmlReadMode parameter can take any of the values listed in the following table:

VALUES	DESCIPTION
Auto	Checks the XML document and selects the action accordingly from the following choices: 1. If the DataSet already has schema or the document contains an inline schema, it sets XmlReadMode to ReadSchema. 2. If the DataSet does not already have a schema and the document does not contain an inline schema, it sets XmlReadMode to InferSchema.
DiffGram	Reads a Diffgram, which is a format that contains both the original and the current values of the data, and applies changes from the DiffGram to the DataSet.
Fragment	The default namespace is read as the inline schema.
IgnoreSchema	Ignores any inline schema and reads data into the existing DataSet schema.
InferSchema	Ignores any inline schema and instead infers schema from the data and loads the data.
ReadSchema	Reads any inline schema and loads the data. If the DataSet already has a schema, new tables may be added to the schema but an exception is thrown if any tables in the inline schema already exist in the DataSet.

Consider that you want to represent "books.xml", with a DataSet. For this you can read the "books.xml" file into a DataSet and bind the XML document control to the DataGrid control by adding the following code to the Load event of the page in the .aspx page.

private void Page_Load(object sender, System.EventArgs e)
{
DataSet ds = new DataSet();
ds.ReadXml (MapPath("books.xml"));
DataGrid1.DataSource = ds;
DataGrid1.DataBind();
}

Working With XML Server Controls - Practical Example

The WebShoppe Web Site needs to create a Web application to store the details of the books available for sale in the XML format. The details of the books should be displayed in a tabular format in a browser. The details include Book Id, Title, price, First Name and Last Name of the author.

Solution: It is pertinent to note that XSLT is the W3C specification for formatting XML documents and displaying the contents in the required format. You need to create an .xsl file by using Visual Studio .NET IDE to display the data stored in the XML document in a tabular format. An XML Web server control is used to display the contents of an XML document in Web application. You need to add an XML Web Server control from the toolbox to the Web form to display the relevant XML data in the Web application.

1. Open your Web application, and add an item XML file, in the same manner as shown before. So, now considering that you have added "books.xml" using the XML designer to your web application, let us move to next step.

2. Select Add New Item option from the project menu to open the Add New Item dialog box.

3. Select XSLT File as the template form the right pane.

4. Add a few lines of code after the second line (since two lines are automatically generated). To see this code you can download the project through the link at the end of this section.

Now that you have created the XML file with the relevant data, and also specified the style sheet for the XML file.

5. Next, you need to apply the style sheet to the XML data by adding an XML server control to the WebForm1.aspx and setting the DocumentSource and TransformSource properties to .xml file and .xsl file respectively.

Build your application and run it. The output should be as follows:

You can download this application through this link.

Converting Relational Data into an XML document

ASP.NET allows you to easily convert the data form a database into an Xml document. It provides the XMLDataDocument class, which allows you to load relational data and the data from an XML document into a DataSet. The data loaded in XMLDataDocument can be manipulated using the W3C DOM.

Let us consider a problem: you need to extract data from a SQL Server and store it as an XML file.

Solution: For this we'll add an XML Web server control and a label to display ant error message. The code for the same is written below:

using System.Data.SqlClient;
using System.Xml;

Now add this code:

//Create a dataset
Dataset ds = new Dataset();
//Create a connection string.
String sqlconnect = "Persist Security Info=False;User ID=sa;Initial Catalog=WebShoppe;Data Source=IRDTEST-D190;";
//Create a connection object to connect to the web shoppe database
try
{
SqlConnection nwconnect = new SqlConnection(sqlconnect);
//Create a command string to select all the customers in the customerDetails table
String scommand = "Select * form customerDetails";
//Create an adapter to load the dataset
SqlDataAdapter da = new SqlDataAdapter(scommand,nwconnect);
//Fill the dataset
da.Fill(ds,"customerDetails");
}
catch
{
Label1.Text = "Error while connecting to database";
}
XmlDataDocument doc = new XmlDataDocument(ds);
Xml1.Document = doc;
doc.Save(MapPath("Customers.xml"));//This is where we are saving the data in an XML file Customers.xml

The above code will display the contents not in a tabular format, since no style sheets are attached. If you want the data to be displayed in some particular format, make your XSL file as myfile.xsl and add the following three lines of code to the above written code:

XslTransform t = new XslTransform();
t.Load(MapPath(("myfile.xsl"));
Xml1.Transform = t;