Saturday, April 22, 2023

Sample Database

 USE [LearnSP]

GO

/****** Object:  Table [dbo].[tblStudent]    Script Date: 2/11/2023 9:42:30 AM ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE TABLE [dbo].[tblStudent](

[StudentId] [int] IDENTITY(1,1) NOT NULL,

[Name] [nvarchar](50) NULL,

[Gender] [nvarchar](50) NULL,

[City] [nvarchar](50) NULL,

[RegularFees] [int] NULL,

[HourlyRate] [int] NULL,

[CourseHours] [int] NULL,

[StudentType] [int] NULL

) ON [PRIMARY]

GO

SET IDENTITY_INSERT [dbo].[tblStudent] ON 


INSERT [dbo].[tblStudent] ([StudentId], [Name], [Gender], [City], [RegularFees], [HourlyRate], [CourseHours], [StudentType]) VALUES (1, N'Nitu', N'F', N'Agra', 2000, NULL, NULL, 1)

INSERT [dbo].[tblStudent] ([StudentId], [Name], [Gender], [City], [RegularFees], [HourlyRate], [CourseHours], [StudentType]) VALUES (2, N'Yadu', N'M', N'Gaya', NULL, 10, 200, 2)

INSERT [dbo].[tblStudent] ([StudentId], [Name], [Gender], [City], [RegularFees], [HourlyRate], [CourseHours], [StudentType]) VALUES (3, N'Aman', N'M', N'Agra', 2000, NULL, NULL, 1)

INSERT [dbo].[tblStudent] ([StudentId], [Name], [Gender], [City], [RegularFees], [HourlyRate], [CourseHours], [StudentType]) VALUES (4, N'Indu', N'F', N'Delhi', 3000, NULL, NULL, 1)

INSERT [dbo].[tblStudent] ([StudentId], [Name], [Gender], [City], [RegularFees], [HourlyRate], [CourseHours], [StudentType]) VALUES (5, N'Bhushan', N'M', N'Delhi', NULL, 20, 120, 2)

INSERT [dbo].[tblStudent] ([StudentId], [Name], [Gender], [City], [RegularFees], [HourlyRate], [CourseHours], [StudentType]) VALUES (6, N'Jaya', N'F', N'Pune', 3000, NULL, NULL, 1)

INSERT [dbo].[tblStudent] ([StudentId], [Name], [Gender], [City], [RegularFees], [HourlyRate], [CourseHours], [StudentType]) VALUES (7, N'Anand', N'M', N'Agra', 3000, NULL, NULL, 1)

INSERT [dbo].[tblStudent] ([StudentId], [Name], [Gender], [City], [RegularFees], [HourlyRate], [CourseHours], [StudentType]) VALUES (8, N'AnandiBen', N'F', N'Pune', NULL, 11, 110, 2)

INSERT [dbo].[tblStudent] ([StudentId], [Name], [Gender], [City], [RegularFees], [HourlyRate], [CourseHours], [StudentType]) VALUES (9, N'Hari', N'M', N'Pune', 4000, NULL, NULL, 1)

INSERT [dbo].[tblStudent] ([StudentId], [Name], [Gender], [City], [RegularFees], [HourlyRate], [CourseHours], [StudentType]) VALUES (10, N'Mira', N'F', N'Delhi', NULL, 22, 120, 2)

INSERT [dbo].[tblStudent] ([StudentId], [Name], [Gender], [City], [RegularFees], [HourlyRate], [CourseHours], [StudentType]) VALUES (11, N'Suraj', N'M', N'Mumbai', 60000, NULL, NULL, 1)

INSERT [dbo].[tblStudent] ([StudentId], [Name], [Gender], [City], [RegularFees], [HourlyRate], [CourseHours], [StudentType]) VALUES (12, N'Sunny', N'M', N'Agra', NULL, 12, 110, 2)

INSERT [dbo].[tblStudent] ([StudentId], [Name], [Gender], [City], [RegularFees], [HourlyRate], [CourseHours], [StudentType]) VALUES (13, N'Rajan', N'M', N'Patna', 2000, NULL, NULL, 1)

INSERT [dbo].[tblStudent] ([StudentId], [Name], [Gender], [City], [RegularFees], [HourlyRate], [CourseHours], [StudentType]) VALUES (14, N'Raman', N'M', N'Ara', NULL, 20, 100, 2)

INSERT [dbo].[tblStudent] ([StudentId], [Name], [Gender], [City], [RegularFees], [HourlyRate], [CourseHours], [StudentType]) VALUES (15, N'Rambha', N'F', N'Nke', 2002, NULL, NULL, 1)

INSERT [dbo].[tblStudent] ([StudentId], [Name], [Gender], [City], [RegularFees], [HourlyRate], [CourseHours], [StudentType]) VALUES (16, N'Devi', N'F', N'Siwan', NULL, 10, 111, 2)

INSERT [dbo].[tblStudent] ([StudentId], [Name], [Gender], [City], [RegularFees], [HourlyRate], [CourseHours], [StudentType]) VALUES (17, N'Tarun', N'M', N'Agra', 20000, NULL, NULL, 1)

INSERT [dbo].[tblStudent] ([StudentId], [Name], [Gender], [City], [RegularFees], [HourlyRate], [CourseHours], [StudentType]) VALUES (18, N'Dhiraj', N'M', N'Agra', 7222, NULL, NULL, 1)

INSERT [dbo].[tblStudent] ([StudentId], [Name], [Gender], [City], [RegularFees], [HourlyRate], [CourseHours], [StudentType]) VALUES (19, N'Raju', N'M', N'Delhi', 2000, NULL, NULL, 1)

INSERT [dbo].[tblStudent] ([StudentId], [Name], [Gender], [City], [RegularFees], [HourlyRate], [CourseHours], [StudentType]) VALUES (20, N'Reena', N'F', N'Delhi', NULL, 10, 123, 2)

INSERT [dbo].[tblStudent] ([StudentId], [Name], [Gender], [City], [RegularFees], [HourlyRate], [CourseHours], [StudentType]) VALUES (21, N'Rupa', N'F', N'Patna', 7000, NULL, NULL, 1)

INSERT [dbo].[tblStudent] ([StudentId], [Name], [Gender], [City], [RegularFees], [HourlyRate], [CourseHours], [StudentType]) VALUES (22, N'Seema', N'F', N'Patna', 6778, NULL, NULL, 1)

INSERT [dbo].[tblStudent] ([StudentId], [Name], [Gender], [City], [RegularFees], [HourlyRate], [CourseHours], [StudentType]) VALUES (23, N'Bhuvan', N'M', N'Patna', NULL, 10, 100, 2)

INSERT [dbo].[tblStudent] ([StudentId], [Name], [Gender], [City], [RegularFees], [HourlyRate], [CourseHours], [StudentType]) VALUES (24, N'Jivan', N'M', N'Agra', 3000, NULL, NULL, 1)

INSERT [dbo].[tblStudent] ([StudentId], [Name], [Gender], [City], [RegularFees], [HourlyRate], [CourseHours], [StudentType]) VALUES (25, N'Vishesh', N'M', N'Patna', NULL, 11, 120, 2)

INSERT [dbo].[tblStudent] ([StudentId], [Name], [Gender], [City], [RegularFees], [HourlyRate], [CourseHours], [StudentType]) VALUES (26, N'Top', N'M', N'Nagpur', 8790, NULL, NULL, 1)

INSERT [dbo].[tblStudent] ([StudentId], [Name], [Gender], [City], [RegularFees], [HourlyRate], [CourseHours], [StudentType]) VALUES (27, N'Tripti', N'F', N'Nagpur', 8000, NULL, NULL, 1)

INSERT [dbo].[tblStudent] ([StudentId], [Name], [Gender], [City], [RegularFees], [HourlyRate], [CourseHours], [StudentType]) VALUES (28, N'Rani', N'F', N'Nagpur', NULL, 11, 100, 2)

INSERT [dbo].[tblStudent] ([StudentId], [Name], [Gender], [City], [RegularFees], [HourlyRate], [CourseHours], [StudentType]) VALUES (29, N'Garv', N'M', N'Juhu', 7699, NULL, NULL, 1)

INSERT [dbo].[tblStudent] ([StudentId], [Name], [Gender], [City], [RegularFees], [HourlyRate], [CourseHours], [StudentType]) VALUES (30, N'Tara', N'F', N'Juhu', 8900, NULL, NULL, 1)

INSERT [dbo].[tblStudent] ([StudentId], [Name], [Gender], [City], [RegularFees], [HourlyRate], [CourseHours], [StudentType]) VALUES (31, N'Venu', N'M', N'Juhu', 1888, NULL, NULL, 1)

INSERT [dbo].[tblStudent] ([StudentId], [Name], [Gender], [City], [RegularFees], [HourlyRate], [CourseHours], [StudentType]) VALUES (32, N'Hans', N'M', N'Juhu', NULL, 12, 120, 2)

INSERT [dbo].[tblStudent] ([StudentId], [Name], [Gender], [City], [RegularFees], [HourlyRate], [CourseHours], [StudentType]) VALUES (33, N'Ajeet', N'M', N'Jaipur', 2000, NULL, NULL, 1)

SET IDENTITY_INSERT [dbo].[tblStudent] OFF

GO

USE [LearnSP]

GO

/****** Object:  StoredProcedure [dbo].[usp_getGenderCount]    Script Date: 2/11/2023 9:42:30 AM ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

create proc [dbo].[usp_getGenderCount]

@sex nvarchar(2)

,@total int output

as

begin

select count(1) from tblStudent

where Gender =@sex 

return @total

end

GO

USE [LearnSP]
GO
/****** Object:  StoredProcedure [dbo].[usp_StudentDetails]    Script Date: 2/11/2023 9:42:30 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE procedure [dbo].[usp_StudentDetails]
As
begin
select name, gender, City
from tblStudent
where gender='M'
end
GO
USE [LearnSP]
GO
/****** Object:  StoredProcedure [dbo].[usp_StudentGenderCityDetails]    Script Date: 2/11/2023 9:42:30 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create procedure [dbo].[usp_StudentGenderCityDetails]
@sex nvarchar(2)
,@city nvarchar(20)
As
begin
select name, gender, City
from tblStudent
where gender=@sex and City=@city
end
GO
USE [LearnSP]
GO
/****** Object:  StoredProcedure [dbo].[usp_StudentGenderDetails]    Script Date: 2/11/2023 9:42:30 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create procedure [dbo].[usp_StudentGenderDetails]
@sex nvarchar(2)
As
begin
select name, gender, City
from tblStudent
where gender=@sex
end
GO



By -
Labels:

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)

Hot Topics

  • SQL Server Stored Procedures
    In this post we will learn what is stored procedure, how it is different from query and what are its advantages over queries. Procedures I...
  • Excel VBA- ListBox
    Objectives To provide detailed information about ListBox Types of ListBox Using ListBox in VBA applications Please read the post till end...
  • SQL Server ACID Properties of Transaction
    ACID Properties To understand transaction in depth, we must know all about its properties. We will look at ACID properties which are rela...