display all the employees whose hiredate is not the first day of the month or the last day of the month

 select empid  from emp
 where datepart(dd,GETDATE()) != 1
 and datepart(dd,getdate()) !=
  DATEADD(dd,-day(dateadd(mm,1,getdate())), dateadd(mm,1,getdate()))


 select  DATEADD(dd,-day(dateadd(mm,1,getdate())), dateadd(mm,1,getdate()))

select  dateadd(mm,1,getdate())

SQL training Procedure and views

simple view


create view Emp_view
as

select empid,ename,gender from emp

insert into Emp_view values (11,'sssss','M')


Alter view Emp_view as select empid,ename,gender,sal,deptno from emp





multitable view

create view emp_dept
as
select empid,ename,emp.deptno,dept.dname from emp,dept where emp.deptno = dept.deptno

insert into emp_dept values (111,'sdff',1,'marketing')


giving error not allowed to insert records on multiple jointable view




temporory table example procedure

Create Procedure tempororytable
@startWith nvarchar(50)
As
Begin
Declare @EMPCOUNT int
Set nocount on;
Select p.empid, p.ename
into #Temp
 from emp as p
where
p.ename like @startWith + N'%';
Select
t.ename,  COUNT(*) as EMPCOUNT from #Temp as t
group by
t.ename;

--Print EMPCOUNT
Drop table #Temp;
End
GO



Cursor Example

Create procedure CursorDemo
as
Declare @Ename varchar (20)
Declare @Ename1 Varchar(30)

Declare @getEname CURSOR

Set @getename  = CURSOR For

select ename from emp

Open @getEname

Fetch Next from @getEname into @Ename
while @@FETCH_STATUS  = 0
Begin


set @Ename1 = (@Ename + 'AAAA')
Print @Ename1
Fetch Next
From @getEname into @Ename
END
Close @getEname
Deallocate @getEname
GO

EXEC CursorDemo






Procedure @@ERROR and @@rowcount

Create Procedure GetData
(
@DEPTNo int,
@sal int
)
As
begin
Declare @ErrorVar int;
Declare @RowcountVar int;

update emp set sal= sal + 1000 where deptno = @DEPTNo

select @ErrorVar = @@ERROR,
@RowcountVar = @@ROWCOUNT;

if(@ErrorVar <> 0)
Begin
    if @ErrorVar = 547
  BEGIN
     print N'Invalid';
     return 1;
  End
Else
   Begin
   Print N'Error';
   return 2;
   End

End
Else
Begin
if @RowcountVar = 0
Begin
 print 'warning';
End
Else
Begin
print 'updated'
Return 0;
End
end
End



EXEC GetData 15,1000

Linq best example

  protected void lnkGrouptoDeposit_Click(object sender, EventArgs e)
        {

            double TotalAmount = 0.00;

            if (Request.Cookies["GeneralInfo"] != null)
            {
                HttpCookie cookie = Request.Cookies["GeneralInfo"];
                if (cookie != null)
                {
                    string strIndNew = gridAria.SelectedIndexVal.Replace("undefined", "0");
                    List<int> ListSele = (string.IsNullOrWhiteSpace(strIndNew)) ? new List<int>() : strIndNew.Split(',').Select(n => int.Parse(n)).Distinct().ToList();
                    cookie["EntityIdList"] = String.Join(",", ListSele.Select(x => x.ToString()).ToArray());
                    ViewState["EntityIdList"] = gridAria.ListChecked;
                }
                Response.Cookies.Add(cookie);
            }



            List<int> ListCheckedtest = gridAria.ListChecked;
            if (ListCheckedtest.Count > 0)
            {
                clsTransactionBO objDepositBO = new clsTransactionBO();

                int intEntityID = ListCheckedtest.FirstOrDefault();
                clsTransactionBO objTransModel = new clsTransactionBO();
                if (ViewState["ListData"] != null)
                {
                    uclCommonPopup.Visible = true;
                    NextGen.Controls.Common.clsGridBind<clsTransactionBO> objTransGridList = (NextGen.Controls.Common.clsGridBind<clsTransactionBO>)ViewState["ListData"];
                    List<clsTransactionBO> objList = objTransGridList.GridList.ToList();
                    objTransModel = objList.Where(x => x.EntityID == intEntityID).SingleOrDefault();
                }
                uclGroupReceipts.EntityList = String.Join(",", gridAria.ListChecked.Select(x => x.ToString()).ToArray());

                if (objTransModel != null && objTransModel.TransTypeId == 1)
                {

                    listGroupReceiptXML = GetReceiptList();
                    // we get selected list
                    int bankkid = listGroupReceiptXML.Select(b => b.BankAccountID).FirstOrDefault();
                    int countBankAccountID = listGroupReceiptXML.Count(b => b.BankAccountID == bankkid);
                    if (countBankAccountID == listGroupReceiptXML.Count)
                    {
                        int countDepositID = listGroupReceiptXML.Count(b => b.DepositID > 0);
                        if (countDepositID > 0)
                        {
                            uclCommonPopup.Alert("Display Message Box", "One or more Receipts have already been grouped to a deposit");
                        }
                        else
                        {
                            for (int i = 0; i < listGroupReceiptXML.Count; i++)
                            {
                                TotalAmount += Convert.ToDouble(listGroupReceiptXML[i].Amount);
                            }
                            objDepositBO.EntityIdList = XMLConvert(uclGroupReceipts.EntityList);
                            objDepositBO.Amount = TotalAmount.ToString();
                            objDepositBO.NoOfItems = Convert.ToString(ListCheckedtest.Count);
                            uclGroupReceipts.GroupDepositModel = objDepositBO;
                            uclGroupReceipts.ControlStatus = true;
                            mdlGrouptoDeposit.Show();
                        }

                    }
                    else
                    {
                        uclCommonPopup.Alert("Display Message Box", "Please select same bank account");
                    }

                }
                else
                {
                    uclCommonPopup.Alert("Display Message Box", "Selected items must be receipts");
                }
            }
            else
            {
                uclCommonPopup.Alert("Display Message Box", "At least one receipt must be selected before proceeding");
            }

            //CreateCookie();

        }

Asp .net list for loop best example

        protected void lnkGrouptoDeposit_Click(object sender, EventArgs e)
        {
         
            double TotalAmount = 0.00;
            //gridAria.SortCheckBoxList();
         
            if (Request.Cookies["GeneralInfo"] != null)
            {
                HttpCookie cookie = Request.Cookies["GeneralInfo"];
                if (cookie != null)
                {
                    string strIndNew = gridAria.SelectedIndexVal.Replace("undefined", "0");
                    List<int> ListSele = (string.IsNullOrWhiteSpace(strIndNew)) ? new List<int>() : strIndNew.Split(',').Select(n => int.Parse(n)).Distinct().ToList();
                    cookie["EntityIdList"] = String.Join(",", ListSele.Select(x => x.ToString()).ToArray());
                    ViewState["EntityIdList"] = gridAria.ListChecked;
                }
                Response.Cookies.Add(cookie);
            }
            //VerifyCookie();
            //List<int> ListCheckedtest = (ViewState["EntityIdList"] != null || Convert.ToString(ViewState["EntityIdList"]) != "") ? (List<int>)ViewState["EntityIdList"] : new List<int>();



            List<int> ListCheckedtest = gridAria.ListChecked;
            if (ListCheckedtest.Count > 0)
            {
                clsTransactionBO objDepositBO = new clsTransactionBO();

                int intEntityID = ListCheckedtest.FirstOrDefault();
                clsTransactionBO objTransModel = new clsTransactionBO();
                if (ViewState["ListData"] != null)
                {
                    uclCommonPopup.Visible = true;
                    NextGen.Controls.Common.clsGridBind<clsTransactionBO> objTransGridList = (NextGen.Controls.Common.clsGridBind<clsTransactionBO>)ViewState["ListData"];
                    List<clsTransactionBO> objList = objTransGridList.GridList.ToList();
                    objTransModel = objList.Where(x => x.EntityID == intEntityID).SingleOrDefault();
                }
                uclGroupReceipts.EntityList = String.Join(",", gridAria.ListChecked.Select(x => x.ToString()).ToArray());

                if (objTransModel != null && objTransModel.TransTypeId == 1)
                {

                    listGroupReceiptXML = GetReceiptList();
                    bool flag = false;


                    if (ListCheckedtest.Count == 1)
                    {
                        flag = false;
                    }
                    else
                    {
                        for (int i = 0; i < listGroupReceiptXML.Count; i++)
                        {
                            for (int j = 0; j < listGroupReceiptXML.Count; j++)
                            {
                                if (i != j)
                                {
                                    if (Convert.ToDouble(listGroupReceiptXML[i].BankAccountID) != Convert.ToDouble(listGroupReceiptXML[j].BankAccountID))
                                    {
                                        flag = true;
                                        break;
                                    }

                                }

                            }
                            if (flag == true)
                                break;
                        }

                    }
                    if (flag != true)
                    {
                        bool boolDepositalreadyexist = false;
                        for (int i = 0; i < listGroupReceiptXML.Count; i++)
                        {
                            if ( Convert.ToInt32(listGroupReceiptXML[i].DepositID) != 0)
                            {
                                uclCommonPopup.Alert("Display Message Box", "One or more Receipts have already been grouped to a deposit");
                                boolDepositalreadyexist = true;
                                break;
                            }
                        }
                        if (boolDepositalreadyexist != true)
                        {
                            for (int i = 0; i < listGroupReceiptXML.Count; i++)
                            {
                                TotalAmount += Convert.ToDouble(listGroupReceiptXML[i].Amount);
                            }

                            objDepositBO.EntityIdList = XMLConvert(uclGroupReceipts.EntityList);
                            objDepositBO.Amount = TotalAmount.ToString();
                            objDepositBO.NoOfItems = Convert.ToString(ListCheckedtest.Count);
                            uclGroupReceipts.GroupDepositModel = objDepositBO;
                            uclGroupReceipts.ControlStatus = true;
                            mdlGrouptoDeposit.Show();
                        }
                    }
                    else
                    {
                        uclCommonPopup.Alert("Display Message Box", "Please select same bank account");

                    }
                }
                else
                {
                    uclCommonPopup.Alert("Display Message Box", "Selected items must be receipts");
                }
            }
            else
            {
                uclCommonPopup.Alert("Display Message Box", "At least one receipt must be selected before proceeding");
            }

            //CreateCookie();
         

        }

SQL server training

create database swapnil
use swapnil

create table emp(empid int,ename varchar(30),eadd varchar(30),city varchar(20),
pin int , join_dt datetime, mobileno int
)

Alter table emp add constraint Pk_empno  primary key (empid)

Alter table emp alter column empid int not null
ALTER TABLE Po_detail  column Po_Pono TO Pd_Pono

Alter table Po_detail Add constraint Pk_Pono_Itemcd primary key(Po_Pono,Pd_itemcd)

Alter table Vendor alter column vn_vendcd int not null
Alter table Vendor Add constraint Pk_vencd primary key(vn_vendcd)

ALTER TABLE Vendor
DROP CONSTRAINT  Pk_vencd
drop table Vendor primary constraint key Pk_vencd

Alter table Item alter column Im_itemcd numeric(18,2) not null
Alter table Item Add constraint Pk_itemcd primary key(Im_itemcd)
Alter table Vendor alter column vn_vendcd numeric(18,2) not null
Alter table PO_Head alter column Ph_vendcd numeric(18,2) not null

Alter table PO_Head add constraint fk_vendc foreign key (Ph_vendcd) references vendor (vn_vendcd)

Alter table emp alter column mobileno int not null

Alter table emp add constraint Uk_mobileno  unique (mobileno)
Alter table emp alter column city varchar(20) not null


ALTER TABLE emp
Add constraint Df_city DEFAULT 'city' for city

Alter table Vendor alter column Im_rate numeric(18,2) not null

insert into Vendor values(1,'Mahalaxmi tradors')
insert into Vendor values(2,'Vikhroli tradors')
insert into Vendor values(3,'bhandup tradors')
insert into Vendor values(4,'Elphiston tradors')
insert into Vendor values(5,'kanjur tradors')

insert into Item values (1,'item1',15)
insert into Item values (2,'item2',20)
insert into Item values (3,'item3',21)
insert into Item values (4,'item4',22)
insert into Item values (5,'item5',23)
insert into Item values (6,'item6',14)
insert into Item values (7,'item7',13)
insert into Item values (8,'item8',12)
insert into Item values (9,'item9',17)
insert into Item values (10,'item10',18)

Alter table Po_detail alter column Pd_value numeric(18,0)  null
insert into Po_detail values(1,1,10,null,null )
insert into Po_detail values(1,2,20 ,null,null)
insert into Po_detail values(1,3,30,null,null )
insert into Po_detail values(2,4,10,null,null )
insert into Po_detail values(2,5,10 ,null,null)
insert into Po_detail values(2,6,10 ,null,null)
insert into Po_detail values(3,7,50,null,null )
insert into Po_detail values(3,8,60,null,null )
insert into Po_detail values(3,9,70,null,null )

insert into Po_detail values(4,10,50,null,null )
insert into Po_detail values(4,11,60,null,null )
insert into Po_detail values(4,12,70,null,null )

insert into Po_detail values(5,13,50 ,null,null)
insert into Po_detail values(5,14,60 ,null,null)
insert into Po_detail values(5,15,70,null,null )


Alter table PO_Head alter column Ph_TaxAmt numeric(18,0)  null
insert into PO_Head values(1,GETDATE(),1,null,null )
insert into PO_Head values(2,GETDATE(),2,null,null )
insert into PO_Head values(3,GETDATE(),3,null,null )
insert into PO_Head values(4,GETDATE(),4,null,null )
insert into PO_Head values(5,GETDATE(),5,null,null )


select * from PO_Head
select * from Vendor
select * from Item
select * from Po_detail

Update Po_detail set Pd_rate =( select im_rate from Item where Im_itemcd = Pd_itemcd)
Update Po_detail set Pd_value = Pd_qty * Pd_rate
Update PO_Head set Ph_Poamnt = (select sum(pd_value)from po_detail  where Ph_Pono = po_pono)

Function in sql

create function third_func
(
@item int)
RETURNs int
as
begin
Declare @rate int
  select @rate =   Im_rate*(Im_rate + 10) from Item where Im_itemcd = @item
   RETURN @rate
End


----------------
select dbo.third_func(1)

sql server login

after create login need to restart sql service

foreign key allow null

foreign key only allow null values if primary key also allow the null data to insert
it can have null values but have to satisfied this condition

Normalization and denormalization

Normalization:- create relation between the table
Denormalization :- we add the required column to the table to retrive records 

Types of view

1)simple view:- refresh and update data every time view run.
2)materialized view :- used to set time base updation of view data. we set particular time to refresh the data of view.