Database Queries

Some useful Queries

1. Query for find n th highest salary

SELECT TOP 1 salary

FROM (

SELECT DISTINCT TOP 3 salary

FROM dbo.empsalary

ORDER BY salary DESC) a

ORDER BY salary

2. To find the size of database

SELECT * FROM SYSFILE

USE URDBNAME

EXEC SP_HELPDB

EXEC SP_SPACEUSED

3. To generate test data for generating load on database with create tables and insert record in it

Creation of employee table

USE [tempdb]

GO

/****** Object: Table [dbo].[Emp] Script Date: 03/06/2009 17:09:08 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

SET ANSI_PADDING ON

GO

CREATE TABLE [dbo].[Emp](

[no] [numeric](18, 0) NOT NULL,

[Temp1] [varchar](50) NOT NULL,

[Temp2] [varchar](50) NOT NULL,

[Temp3] [varchar](50) NOT NULL,

[Temp4] [varchar](50) NOT NULL,

[Temp5] [varchar](50) NOT NULL,

[Temp6] [varchar](50) NOT NULL,

[number] [numeric](18, 0) NOT NULL,

[date] [datetime] NOT NULL,

CONSTRAINT [PK_Emp] PRIMARY KEY CLUSTERED

(

[no] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY]

GO

SET ANSI_PADDING OFF

GO

Creation of Employee child table

USE [Test]

GO

/****** Object: Table [dbo].[EmpChild] Script Date: 03/06/2009 17:12:34 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

SET ANSI_PADDING ON

GO

CREATE TABLE [dbo].[EmpChild](

[No] [numeric](18, 0) NOT NULL,

[Temp1] [varchar](50) NOT NULL,

[Temp2] [varchar](50) NOT NULL,

[date] [datetime] NOT NULL,

CONSTRAINT [PK_EmpChild] PRIMARY KEY CLUSTERED

(

[No] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY]

GO

SET ANSI_PADDING OFF

GO

ALTER TABLE [dbo].[EmpChild] WITH CHECK ADD CONSTRAINT [FK_EmpChild_Emp] FOREIGN KEY([No])

REFERENCES [dbo].[Emp] ([no])

GO

ALTER TABLE [dbo].[EmpChild] CHECK CONSTRAINT [FK_EmpChild_Emp]

GO

Insert Query for 50000 records (n could be any number)

-- Driver:

DECLARE

@i INTEGER,

@i1 INTEGER

BEGIN

SET @i = 0

SET @i1 = 1

WHILE @i1 <= 50

BEGIN

INSERT INTO emp

VALUES (@i1, 'Temp1', 'Temp2', 'Temp3', 'Temp4', 'Temp5', 'Temp6',@i1,getdate())

SET @i1 = @i1 + 1

END

END

GO

-- Child

DECLARE

@i1 INTEGER

BEGIN

SET @i1 = 1

WHILE @i1 <= 50

BEGIN

INSERT INTO empchild

VALUES (@i1, 'Temp1', 'Temp2', getdate())

SET @i1 = @i1 + 1

END

END

GO

select count(*) from emp;

select count(*) from empchild;

select * from emp;

select * from empchild;