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