To find Second highest salary

To find Second highest salary

select top 2 * from Trainee.dbo.Depart where Salary in(select top 1 Salary from Trainee.dbo.Depart)order by Salary DESC

Coding to get nth record in sql server 10 th record

Coding to get nth record in sql server 10 th record

select top 1 * from (select top 10 * from dEmployeenew order by 1 desc)ac

Trigger in Sql

Create Trigger For Insert
ALTER trigger [dbo].[trinsert]
on [dbo].[emp]

after insert
as
declare @empid int,
@depid int,
@fname varchar(20),
@lname varchar(20),
@gender varchar(10),
@dob datetime,
@doj datetime,
@email varchar(20),
@city varchar(20),
@astatus varchar(20),
@Dateandtime datetime,
@salary int

set @empid=(select empid from inserted)
set @depid=(select depid from inserted)
set @fname=(select fname from inserted)
set @lname=(select lname from inserted)
set @gender=(select gender from inserted)
set @dob=(select dob from inserted)
set @doj=(select doj from inserted)
set @email=(select email from inserted)
set @city=(select city from inserted)
set @astatus='inserted'
set @Dateandtime=getdate()
set @salary=(select salary from inserted)



--INSERT myArchive (type, ID, newName) VALUES('INSERT', @ID, @Name)



INSERT dEmployeenew(empid,depid,fname,lname,gender,dob,doj,email,city,astatus,DateandTime,salary)
values
(@empid,@depid,@fname,@lname,@gender,@dob,@doj,@email,@city,@astatus,@DateandTime,@salary)


Trigger For After Delete

ALTER trigger [dbo].[trdelete]
on [dbo].[emp]

after delete
as
declare @empid int,
@depid int,
@fname varchar(20),
@lname varchar(20),
@gender varchar(10),
@dob datetime,
@doj datetime,
@email varchar(20),
@city varchar(20),
@astatus varchar(20),
@Dateandtime datetime,
@salary int

set @empid=(select empid from deleted)
set @depid=(select depid from deleted)
set @fname=(select fname from deleted)
set @lname=(select lname from deleted)
set @gender=(select gender from deleted)
set @dob=(select dob from deleted)
set @doj=(select doj from deleted)
set @email=(select email from deleted)
set @city=(select city from deleted)
set @astatus='deleted'
set @Dateandtime=getdate()
set @salary=(select salary from deleted)



--INSERT myArchive (type, ID, newName) VALUES('INSERT', @ID, @Name)



INSERT dEmployeenew(empid,depid,fname,lname,gender,dob,doj,email,city,astatus,DateandTime,salary)
values
(@empid,@depid,@fname,@lname,@gender,@dob,@doj,@email,@city,@astatus,@DateandTime,@salary)

Trigger For Update
ALTER trigger [dbo].[trupdate]
on [dbo].[emp]

after update
as
declare @empid int,
@depid int,
@fname varchar(20),
@lname varchar(20),
@gender varchar(10),
@dob datetime,
@doj datetime,
@email varchar(20),
@city varchar(20),
@astatus varchar(20),
@Dateandtime datetime,
@salary int

set @empid=(select empid from inserted)
set @depid=(select depid from inserted)
set @fname=(select fname from inserted)
set @lname=(select lname from inserted)
set @gender=(select gender from inserted)
set @dob=(select dob from inserted)
set @doj=(select doj from inserted)
set @email=(select email from inserted)
set @city=(select city from inserted)
set @astatus='updated'
set @Dateandtime=getdate()
set @salary=(select salary from inserted)



--INSERT myArchive (type, ID, newName) VALUES('INSERT', @ID, @Name)



INSERT dEmployeenew(empid,depid,fname,lname,gender,dob,doj,email,city,astatus,DateandTime,salary)
values
(@empid,@depid,@fname,@lname,@gender,@dob,@doj,@email,@city,@astatus,@DateandTime,@salary)

Stored Procedure For reference

Stored Procedure For reference


CREATE PROCEDURE sp_Insertion1
(@sempId int,@sempname varchar(50),@sgender varchar(50),@sstreet varchar(50),@semail varchar(50),@spincode varchar(50))
as
insert into employeear(fempid,fempname,fgender,fstreet,femail,fpincode)
values
(@sempId,@sempname,@sgender,@sstreet,@semail,@spincode)


private void btnNew_Click(object sender, EventArgs e)
{

string empid,empname, gender, street, email, pincode;
empid = txtEmpId.Text;
empname = txtEmpName.Text;
gender = comboGender.SelectedItem.ToString();
street = rtStreet.Text;
email = txtEmail.Text;
pincode = txtPincode.Text;
int empidint = int.Parse(empid);
SqlCommand dbcmd = new SqlCommand();
SqlConnection conn = new SqlConnection("Server=192.168.1.5;" + "Database=trainee;"+ "User ID=senthilkumar;" + "Password=gtpl;" + "Trusted_Connection=false;");
SqlCommand command = new SqlCommand("sp_Insertion1", conn);
command.CommandType = CommandType.StoredProcedure;
command.Parameters.Add("@sempid", SqlDbType.Int).Value = empidint;
command.Parameters.Add("@sempname",SqlDbType.VarChar).Value = empname;
command.Parameters.Add("@sgender", SqlDbType.VarChar).Value = gender;
command.Parameters.Add("@sstreet", SqlDbType.VarChar).Value = street;
command.Parameters.Add("@semail", SqlDbType.VarChar).Value = email;
command.Parameters.Add("@spincode",SqlDbType.VarChar).Value = pincode;
conn.Open();
int rows = command.ExecuteNonQuery();
conn.Close();
}
Update Stored Procedure
ALTER PROCEDURE sp_updation(@sempId int
,@sempname varchar(50)
,@sgender varchar(50)
,@sstreet varchar(50)
,@semail varchar(50)
,@spincode varchar(50))
as
update employeear

set fempid=@sempId
,fempname=@sempname
,fgender=@sgender
,fstreet=@sstreet
,femail=@semail
,fpincode=@spincode
where fempid=@sempId



Delete data from stored procedure


create procedure sp_address_delete(@sid int)as
delete from address where tid=@sid



Select data from Stored Procedure



create procedure sp_address_show(@sid int)
as
select * from address where tid=@sid



Select stored procedure with data type conversion

ALTER procedure [dbo].[sp_address_show](@sid int)
as
select
ttitle,
tname,
taddress,
tlandmark,
tresident,CONVERT (varchar(10),tdob) from address where tid=@sid




Update the table with type conversion


create procedure sp_address_update
(
@sid int
,@stitle varchar(10)
,@sname varchar(25)
,@saddress varchar(55)
,@slandmark varchar(25)
,@sresident varchar(10)
,@sdob varchar(10)
)
as begin
update address
set
ttitle=@stitle,
tname=@sname,
taddress=@saddress,
tlandmark=@slandmark,
tresident=@sresident,
tdob=CONVERT(datetime,@sdob,103)
where
tid=@sid

end


Stored Procedure for Insertion

CREATE PROCEDURE sp_Insertion1
(@sempId int,@sempname varchar(50),@sgender varchar(50),@sstreet varchar(50),@semail varchar(50),@spincode varchar(50))
as
SET NOCOUNT ON;
insert into employeear(fempid,fempname,fgender,fstreet,femail,fpincode)
values
(@sempId,@sempname,@sgender,@sstreet,@semail,@spincode)
Multiple Queries in stored procedure
alter procedure sp_insertnew(

@empid int,
@depid int,
@depname varchar(20),
@fname varchar(20),
@lname varchar(20),
@gender varchar(6),
@dob datetime,
@doj datetime,
@email varchar(20),
@city varchar(20),
@astatus varchar(20),
@dateandtime datetime)
as begin

insert into Employenew(
empid,depid,fname,lname,gender,dob,doj,email,city)
values
(@empid,@depid,@fname,@lname,@gender,@dob,@doj,@email,@city)

insert into hEmployeenew(
empid,depid,fname,lname,gender,dob,doj,email,city,astatus,dateandtime)
values(
@empid,@depid,@fname,@lname,@gender,@dob,@doj,@email,@city,@astatus,@dateandtime)

insert into Departnew(
depid,depname)
values(
@depid,@depname)
end

Queries For reference

Queries For Reference

Creating Table Employee


create table Employe
(Empid int primary key identity,
Empname varchar(20),
Dob datetime,
Doj datetime,
sex varchar(10))

Creating table Depart

create table Depart(
Depid int not null,
Empid int not null,
Dept varchar(15),
Salary int)

Alter Primary Key

alter table Departnew add primary key(depid)


select * from Employe
select * from Employe where Empid=2
select * from Employe where Empid!=2
select Empid,Empname from Employ

Top Queries

Select Top 1 * from Employe
select Top 25 percent * from Employe
select top 25 percent Empid,Empname from Employe
select top 25 percent * from Employe where Empid=1

Using Top query in Descending Order
select top 25 percent * from Employe order by Empid Desc

Distinct Queries

select distinct Empname from Employe
select COUNT(distinct Empname)from Employe

Query Multiple Tables

select Employe.Empname,Employe.Dob,Depart.Depid,Depart.Salary from Employe,Depart where Employe.Empid=Depart.Depid

Sub Queries

select Empid,Empname from Employe where exists(Select Empid from Depart where Employe.Empid=Depart.Empid)

select Depid,Empid,Dept,Salary from Depart where Salary in(Select MAX(Salary)from Depart)


Orderby

select * from Trainee.dbo.Depart order by Empid desc

Aggregate Function

select AVG(Salary)"Average salary"from Trainee.dbo.Depart

select * from Trainee.dbo.Depart where Salary=(select MIN(Salary)from Trainee.Dbo.Depart)

select * from Trainee.dbo.Depart where Salary=(select MAX(Salary)from Trainee.Dbo.Depart)



To create databackup in table without making schema

select * into Worker1 from Employe

Union
select * from Worker1 union Select * from Employe

Union all
select * from Worker1 unionall Select * from Employe

Inner Join
select * from Employe join Depart on Employe.Empid=Depart.Empid

Left Join

LEFT JOIN: Return all rows from the left table, even if there are no matches in the right table
select * from Employe left join Depart on Employe.Empid=Depart.Empid

Right Join

RIGHT JOIN: Return all rows from the right table, even if there are no matches in the left table
select * from Employe Right join Depart on Employe.Empid=Depart.Empid


FULL JOIN

FULL JOIN: Return rows when there is a match in one of the tables

select * from Employe FULL JOIN Depart on Employe.Empid=Depart.Empid

SQL Constraints

Constraints are used to limit the type of data that can go into a table.
NOT NULL,UNIQUE,PRIMARY KEY,FOREIGN KEY,CHECK,DEFAULT
SQL DEFAULT Constraint
CREATE TABLE Persons
(
P_Id int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255) DEFAULT 'Sandnes'
)

Group By function

select Depid,sum(Salary) from Depart group by Depid


Count Function

select COUNT (*) from Employe

Count Disinct Column

SELECT COUNT(DISTINCT Dept) FROM Depart

To find Length

SELECT len(Empname) from Employe

EXCEPT operator
SELECT * from Employe where Empid between 1 AND 8
EXCEPT
SELECT * from Employe where Empid between 5 and 8

INTERSECT operator

The SQL INTERSECT operator takes the results of two queries and returns only rows that appear in both result sets
SELECT * from Employe where Empid between 1 AND 8
intersect
SELECT * from Employe where Empid between 5 and 8
Using the % Wildcard and like operator
select * from Employe where Empname like 'a%'

Dateadd

SELECT Empid,DATEADD(day,45,Doj) AS Doj
FROM Employe
Datediff
DECLARE @StartDate DATETIME
DECLARE @EndDate DATETIME
SET @StartDate ='1984-02-29'
SET @EndDate ='2008-02-29'
SELECT DATEDIFF(Year, @StartDate, @EndDate) AS NewDate
Quarter Difference

DECLARE @StartDate DATETIME
DECLARE @EndDate DATETIME
SET @StartDate ='2007-06-05'
SET @EndDate ='2007-08-05'
SELECT DATEDIFF(quarter, @StartDate, @EndDate) AS NewDate

Month Difference
DECLARE @StartDate DATETIME
DECLARE @EndDate DATETIME
SET @StartDate ='2007-06-05'
SET @EndDate ='2007-08-02'
SELECT DATEDIFF(Month, @StartDate, @EndDate) AS NewDate


DECLARE @StartDate DATETIME
DECLARE @EndDate DATETIME
SET @StartDate ='2007-06-05'
SET @EndDate ='2007-08-02'
SELECT DATEDIFF(dayofyear,@StartDate, @EndDate) AS NewDate
DECLARE @StartDate DATETIME
DECLARE @EndDate DATETIME
SET @StartDate ='2007-06-05'
SET @EndDate ='2007-08-02'
SELECT DATEDIFF(Day, @StartDate, @EndDate) AS NewDate
DECLARE @StartDate DATETIME
DECLARE @EndDate DATETIME
SET @StartDate ='2007-06-05'
SET @EndDate ='2007-08-02'
SELECT DATEDIFF(Week, @StartDate, @EndDate) AS NewDate

DECLARE @StartDate DATETIME
DECLARE @EndDate DATETIME
SET @StartDate ='2007-06-05'
SET @EndDate ='2007-08-02'
SELECT DATEDIFF(Hour, @StartDate, @EndDate) AS NewDate
DECLARE @StartDate DATETIME
DECLARE @EndDate DATETIME
SET @StartDate ='2007-06-05'
SET @EndDate ='2007-08-02'
SELECT DATEDIFF(minute, @StartDate, @EndDate) AS NewDate
DECLARE @StartDate DATETIME
DECLARE @EndDate DATETIME
SET @StartDate ='2007-06-05'
SET @EndDate ='2007-08-02'
SELECT DATEDIFF(second, @StartDate, @EndDate) AS NewDate
DECLARE @StartDate DATETIME
DECLARE @EndDate DATETIME
SET @StartDate ='2007-06-05'
SET @EndDate ='2007-06-06'
SELECT DATEDIFF(millisecond, @StartDate, @EndDate) AS NewDate
Datetime format
Value of current Date Time GETDATE()
SELECT (GETDATE()) = 2007-06-06 23:41:10.153


SELECT CONVERT(varchar,GETDATE(),0)
Return Value = Jun 6 2007 11:07PM
SELECT CONVERT(varchar,GETDATE(),100)
Return Value = Jun 6 2007 11:07PM


SELECT CONVERT(varchar,GETDATE(),1)
Return Value = 06/06/07
SELECT CONVERT(varchar,GETDATE(),101)
Return Value = 06/06/2007


SELECT CONVERT(varchar,GETDATE(),2)
Return Value = 07.06.06
SELECT CONVERT(varchar,GETDATE(),102)
Return Value = 2007.06.06


SELECT CONVERT(varchar,GETDATE(),3)
Return Value = 06/06/07
SELECT CONVERT(varchar,GETDATE(),103)
Return Value = 06/06/2007


SELECT CONVERT(varchar,GETDATE(),4)
Return Value = 06.06.07
SELECT CONVERT(varchar,GETDATE(),104)
Return Value = 06.06.2007


SELECT CONVERT(varchar,GETDATE(),5)
Return Value = 06-06-07
SELECT CONVERT(varchar,GETDATE(),105)
Return Value = 06-06-2007


SELECT CONVERT(varchar,GETDATE(),6)
Return Value = 06 Jun 07
SELECT CONVERT(varchar,GETDATE(),106)
Return Value = 06 Jun 2007


SELECT CONVERT(varchar,GETDATE(),7)
Return Value = Jun 06, 07
SELECT CONVERT(varchar,GETDATE(),107)
Return Value = Jun 06, 2007


SELECT CONVERT(varchar,GETDATE(),8)
Return Value = 23:38:49
SELECT CONVERT(varchar,GETDATE(),108)
Return Value = 23:38:49


SELECT CONVERT(varchar,GETDATE(),9)
Return Value = Jun 6 2007 11:39:17:060PM
SELECT CONVERT(varchar,GETDATE(),109)
Return Value = Jun 6 2007 11:39:17:060PM


SELECT CONVERT(varchar,GETDATE(),10)
Return Value = 06-06-07
SELECT CONVERT(varchar,GETDATE(),110)
Return Value = 06-06-2007


SELECT CONVERT(varchar,GETDATE(),11)
Return Value = 07/06/06
SELECT CONVERT(varchar,GETDATE(),111)
Return Value = 2007/06/06


SELECT CONVERT(varchar,GETDATE(),12)
Return Value = 070606
SELECT CONVERT(varchar,GETDATE(),112)
Return Value = 20070606


SELECT CONVERT(varchar,GETDATE(),13)
Return Value = 06 Jun 2007 23:40:14:577
SELECT CONVERT(varchar,GETDATE(),113)
Return Value = 06 Jun 2007 23:40:14:577


SELECT CONVERT(varchar,GETDATE(),14)
Return Value = 23:40:29:717
SELECT CONVERT(varchar,GETDATE(),114)
Return Value = 23:40:29:717


SELECT CONVERT(varchar,GETDATE(),20)
Return Value = 2007-06-06 23:40:51
SELECT CONVERT(varchar,GETDATE(),120)
Return Value = 2007-06-06 23:40:51


SELECT CONVERT(varchar,GETDATE(),21)
Return Value = 2007-06-06 23:41:10.153
SELECT CONVERT(varchar,GETDATE(),121)
Return Value = 2007-06-06 23:41:10.153


SELECT CONVERT(varchar,GETDATE(),126)
Return Value = 2007-06-06T23:41:10.153


SELECT CONVERT(varchar,GETDATE(),131)
Return Value = 21/05/1428 11:41:10:153PM

How To Link Within A Page.

How To Link Within A Page.

Do you want to people to be able to get to different parts of your page easier or do you just want to add a link so people can get back to the top of the page easily? Then come in and find out how.

Here's How:

1.Create the hook for where you want to go when the main link is clicked on: Title Of The Page
2.Give your link a name (here it is called top) and give it a title.
3.Create the link that will be clicked on. This link tells the browser where to go: Back To The Top
4.Use the same name as used in the hook and give it a title (here the title is Back To The Top).

Tips:

1.Make sure you give the same name to each or it won't work!

How Internet Infrastructure Works

How Internet Infrastructure Works

One of the greatest things about the Internet is that nobody really owns it. It is a global collection of networks, both big and small. These networks connect together in many different ways to form the single entity that we know as the Internet. In fact, the very name comes from this idea of interconnected networks.
Since its beginning in 1969, the Internet has grown from four host computer systems to tens of millions. However, just because nobody owns the Internet, it doesn't mean it is not monitored and maintained in different ways. The Internet Society, a non-profit group established in 1992, oversees the formation of the policies and protocols that define how we use and interact with the Internet.
In this article, you will learn about the basic underlying structure of the Internet. You will learn about domain name servers, network access points and backbones. But first you will learn about how your computer connects to others.


The Internet: Computer Network Hierarchy


Every computer that is connected to the Internet is part of a network, even the one in your home. For example, you may use a modem and dial a local number to connect to an Internet Service Provider (ISP). At work, you may be part of a local area network (LAN), but you most likely still connect to the Internet using an ISP that your company has contracted with. When you connect to your ISP, you become part of their network. The ISP may then connect to a larger network and become part of their network. The Internet is simply a network of networks.
Most large communications companies have their own dedicated backbones connecting various regions. In each region, the company has a Point of Presence (POP). The POP is a place for local users to access the company's network, often through a local phone number or dedicated line. The amazing thing here is that there is no overall controlling network. Instead, there are several high-level networks connecting to each other through Network Access Points or NAPs.

Internet Network Example


Heres an example.

Imagine that Company A is a large ISP. In each major city, Company A has a POP. The POP in each city is a rack full of modems that the ISP's customers dial into. Company A leases fiber optic lines from the phone company to connect the POPs together (see, for example, this UUNET Data Center Connectivity Map).
Imagine that Company B is a corporate ISP. Company B builds large buildings in major cities and corporations locate their Internet server machines in these buildings. Company B is such a large company that it runs its own fiber optic lines between its buildings so that they are all interconnected.
In this arrangement, all of Company A's customers can talk to each other, and all of Company B's customers can talk to each other, but there is no way for Company A's customers and Company B's customers to intercommunicate. Therefore, Company A and Company B both agree to connect to NAPs in various cities, and traffic between the two companies flows between the networks at the NAPs.
In the real Internet, dozens of large Internet providers interconnect at NAPs in various cities, and trillions of bytes of data flow between the individual networks at these points. The Internet is a collection of huge corporate networks that agree to all intercommunicate with each other at the NAPs. In this way, every computer on the Internet connects to every other.
All of these networks rely on NAPs, backbones and routers to talk to each other. What is incredible about this process is that a message can leave one computer and travel halfway across the world through several different networks and arrive at another computer in a fraction of a second!
The routers determine where to send information from one computer to another. Routers are specialized computers that send your messages and those of every other Internet user speeding to their destinations along thousands of pathways. A router has two separate, but related, jobs:
• It ensures that information doesn't go where it's not needed. This is crucial for keeping large volumes of data from clogging the connections of "innocent bystanders."
• It makes sure that information does make it to the intended destination.
In performing these two jobs, a router is extremely useful in dealing with two separate computer networks. It joins the two networks, passing information from one to the other. It also protects the networks from one another, preventing the traffic on one from unnecessarily spilling over to the other. Regardless of how many networks are attached, the basic operation and function of the router remains the same. Since the Internet is one huge network made up of tens of thousands of smaller networks, its use of routers is an absolute necessity. For more information,

Internet Backbone

The National Science Foundation (NSF) created the first high-speed backbone in 1987. Called NSFNET, it was a T1 line that connected 170 smaller networks together and operated at 1.544 Mbps (million bits per second). IBM, MCI and Merit worked with NSF to create the backbone and developed a T3 (45 Mbps) backbone the following year.
Backbones are typically fiber optic trunk lines. The trunk line has multiple fiber optic cables combined together to increase the capacity. Fiber optic cables are designated OC for optical carrier, such as OC-3, OC-12 or OC-48. An OC-3 line is capable of transmitting 155 Mbps while an OC-48 can transmit 2,488 Mbps (2.488 Gbps). Compare that to a typical 56K modem transmitting 56,000 bps and you see just how fast a modern backbone is.
Today there are many companies that operate their own high-capacity backbones, and all of them interconnect at various NAPs around the world. In this way, everyone on the Internet, no matter where they are and what company they use, is able to talk to everyone else on the planet. The entire Internet is a gigantic, sprawling agreement between companies to intercommunicate freely.
Internet Protocol: IP Addresses
Every machine on the Internet has a unique identifying number, called an IP Address. The IP stands for Internet Protocol, which is the language that computers use to communicate over the Internet. A protocol is the pre-defined way that someone who wants to use a service talks with that service. The "someone" could be a person, but more often it is a computer program like a Web browser.
A typical IP address looks like this:
216.27.61.137
To make it easier for us humans to remember, IP addresses are normally expressed in decimal format as a dotted decimal number like the one above. But computers communicate in binary form. Look at the same IP address in binary:
11011000.00011011.00111101.10001001
The four numbers in an IP address are called octets, because they each have eight positions when viewed in binary form. If you add all the positions together, you get 32, which is why IP addresses are considered 32-bit numbers. Since each of the eight positions can have two different states (1 or zero), the total number of possible combinations per octet is 28 or 256. So each octet can contain any value between zero and 255. Combine the four octets and you get 232 or a possible 4,294,967,296 unique values!
Out of the almost 4.3 billion possible combinations, certain values are restricted from use as typical IP addresses. For example, the IP address 0.0.0.0 is reserved for the default network and the address 255.255.255.255 is used for broadcasts