SQL

All the SQL related posts are under this category

Sibasis Jena

UNION,INTERSECT and EXCEPT

I will explain these by taking an example:

CREATE TABLE OrderDetail
(
ID INT Primary Key NOT NULL IDENTITY(1,1),
NAME NVARCHAR(100),
Price INT,
Quantity INT
)
Insert some records:
INSERT INTO OrderDetail VALUES('Biscuit','30',2)
INSERT INTO OrderDetail VALUES('Pen','10',1)
INSERT INTO OrderDetail VALUES('physics Book','100',1)
INSERT INTO OrderDetail VALUES('Chemistry Book','120',1)
INSERT INTO OrderDetail VALUES('Math Book','90',1)
INSERT INTO OrderDetail VALUES('Note Book','20',5)
INSERT INTO OrderDetail VALUES('Mechanics Book','20',5)
INSERT INTO OrderDetail VALUES('Mechanics Book','20',6)

UNION:
We can union two result set with same structure.
(SELECT ID,Name FROM OrderDetail where Quantity=1) UNION (SELECT ID,Name FROM OrderDetail where Quantity=5)
Result :

ID name
2 Pen
3 physics Book
4 Chemistry Book
5 Math Book
6 Note Book
7 Mechanics Book

INTERSECT:
We can union two result set with same structure.
(SELECT Name FROM OrderDetail where Quantity=6) INTERSECT (SELECT Name FROM OrderDetail where Quantity=5)
Result :

name
Mechanics Book

Sibasis Jena

Data Table to excel c#

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.Reflection;

namespace CallCodeBehindMethodFromJavascript
{
public class Student
{
public string Name { get; set; }
public int StudentId { get; set; }
public int Age { get; set; }
}
public class ListtoDataTableConverter
{
public DataTable ToDataTable(List items)
{
DataTable dataTable = new DataTable(typeof(T).Name);
//Get all the properties
PropertyInfo[] Props = typeof(T).GetProperties(BindingFlags.Public | BindingFlags.Instance);
foreach (PropertyInfo prop in Props)
{
//Setting column names as Property names
dataTable.Columns.Add(prop.Name);
}

foreach (T item in items)
{
var values = new object[Props.Length];
for (int i = 0; i < Props.Length; i++)
{
//inserting property values to datatable rows
values[i] = Props[i].GetValue(item, null);
}

dataTable.Rows.Add(values);

}
//put a breakpoint here and check datatable
return dataTable;
}
}
public partial class Import : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{}

protected void btnGetXls_Click(object sender, EventArgs e)
{

List Students = new List(){
new Student() { Name = "Jack", Age = 15, StudentId = 100 },
new Student() { Name = "Smith", Age = 15, StudentId = 101 },
new Student() { Name = "Smit", Age = 15, StudentId = 102 }
};
ListtoDataTableConverter converter = new ListtoDataTableConverter();
DataTable dt = new DataTable();
dt = converter.ToDataTable(Students);
DataTableToExcel(dt);
}

Sibasis Jena

Delete duplicate record from Table SQL

Create Table :

CREATE Table [Order]
(
     ID INT Primary key NOT NULL IDENTITY(1,1),
     NAME NVARCHAR(MAX),
     Quantity INT,
      Price INT
)

Insert duplicate record into the table:

INSERT INTO [ORDER](NAME,Quantity,Price) VALUES(‘SIBASIS’,1,10)

INSERT INTO [ORDER](NAME,Quantity,Price) VALUES(‘SIBASIS’,1,1)
INSERT INTO [ORDER](NAME,Quantity,Price) VALUES(‘SIBASIS’,1,2)
INSERT INTO [ORDER](NAME,Quantity,Price) VALUES(‘SIBASIS’,1,3)
INSERT INTO [ORDER](NAME,Quantity,Price) VALUES(‘SIBASIS’,1,11)
 
INSERT INTO [ORDER](NAME,Quantity,Price) VALUES(‘Manua’,1,10)
INSERT INTO [ORDER](NAME,Quantity,Price) VALUES(‘Manua’,1,10)
INSERT INTO [ORDER](NAME,Quantity,Price) VALUES(‘Manua’,1,10)
 
INSERT INTO [ORDER](NAME,Quantity,Price) VALUES(‘Kalia’,1,1)
INSERT INTO [ORDER](NAME,Quantity,Price) VALUES(‘Kalia’,1,1)
INSERT INTO [ORDER](NAME,Quantity,Price) VALUES(‘Tiki’,1,1)
INSERT INTO [ORDER](NAME,Quantity,Price) VALUES(‘Tiki’,1,1)

Delete Duplicate record:

DELETE FROM [Order] WHERE ID NOT IN (SELECT MAX(ID) FROM [Order] GROUP BY NAME)