C:\Users\HP\OneDrive\Desktop\BIDM.
sql 1
use BIDM
--- Combining all batches data
SELECT [2006_08].Name, [2006_08].Batch, [2006_08].Status, [2006_08].Designation,
[2006_08].Category, [2006_08].Company, [2006_08].Location, [2006_08].State, CAST
([2006_08].Contact AS NVARCHAR) AS Contact, [2006_08].Email
Into Alumni_data1
FROM [2006_08]
UNION ALL
SELECT MBA_2004_06.Name, MBA_2004_06.Batch, MBA_2004_06.Status,
MBA_2004_06.Designation, MBA_2004_06.Category, MBA_2004_06.Company,
MBA_2004_06.Location, MBA_2004_06.State, CAST(MBA_2004_06.Contact AS NVARCHAR) AS
Contact, MBA_2004_06.Email
FROM MBA_2004_06
UNION ALL
SELECT BBA_2004_07.Name, BBA_2004_07.Batch, BBA_2004_07.Status,
BBA_2004_07.Designation, BBA_2004_07.Category, BBA_2004_07.Company,
BBA_2004_07.Location, BBA_2004_07.State, CAST(BBA_2004_07.Contact AS NVARCHAR) AS
Contact, BBA_2004_07.Email
FROM BBA_2004_07
UNION ALL
SELECT MBA_2005_07.Name, MBA_2005_07.Batch, MBA_2005_07.Status,
MBA_2005_07.Designation, MBA_2005_07.Category, MBA_2005_07.Company,
MBA_2005_07.Location, MBA_2005_07.State, CAST(MBA_2005_07.Contact AS NVARCHAR) AS
Contact, MBA_2005_07.Email
FROM MBA_2005_07
UNION ALL
SELECT [2007_09].Name, [2007_09].Batch, [2007_09].Status, [2007_09].Designation,
[2007_09].Category, [2007_09].Company, [2007_09].Location, [2007_09].State, CAST
([2007_09].Contact AS NVARCHAR) AS Contact, [2007_09].Email
FROM [2007_09]
UNION ALL
SELECT [2008_10].Name, [2008_10].Batch, [2008_10].Status, [2008_10].Designation,
[2008_10].Category, [2008_10].Company, [2008_10].Location, [2008_10].State, CAST
([2008_10].Contact AS NVARCHAR) AS Contact, [2008_10].Email
FROM [2008_10]
UNION ALL
SELECT [2009_11].Name, [2009_11].Batch, [2009_11].Status, [2009_11].Designation,
[2009_11].Category, [2009_11].Company, [2009_11].Location, [2009_11].State, CAST
([2009_11].Contact AS NVARCHAR) AS Contact, [2009_11].Email
FROM [2009_11]
UNION ALL
SELECT [2010_12].Name, [2010_12].Batch, [2010_12].Status, [2010_12].Designation,
[2010_12].Category, [2010_12].Company, [2010_12].Location, [2010_12].State, CAST
([2010_12].Contact AS NVARCHAR) AS Contact, [2010_12].Email
FROM [2010_12]
UNION ALL
SELECT [2011_13].Name, [2011_13].Batch, [2011_13].Status, [2011_13].Designation,
C:\Users\HP\OneDrive\Desktop\BIDM.sql 2
[2011_13].Category, [2011_13].Company, [2011_13].Location, [2011_13].State, CAST
([2011_13].Contact AS NVARCHAR) AS Contact, [2011_13].Email
FROM [2011_13]
UNION ALL
SELECT [2012_14].Name, [2012_14].Batch, [2012_14].Status, [2012_14].Designation,
[2012_14].Category, [2012_14].Company, [2012_14].Location, [2012_14].State, CAST
([2012_14].Contact AS NVARCHAR) AS Contact, [2012_14].Email
FROM [2012_14]
UNION ALL
SELECT [2013_15].Name, [2013_15].Batch, [2013_15].Status, [2013_15].Designation,
[2013_15].Category, [2013_15].Company, [2013_15].Location, [2013_15].State, CAST
([2013_15].Contact AS NVARCHAR) AS Contact, [2013_15].Email
FROM [2013_15]
UNION ALL
SELECT [2014_16].Name, [2014_16].Batch, [2014_16].Status, [2014_16].Designation,
[2014_16].Category, [2014_16].Company, [2014_16].Location, [2014_16].State, CAST
([2014_16].Contact AS NVARCHAR) AS Contact, [2014_16].Email
FROM [2014_16]
UNION ALL
SELECT [2015_17].Name, [2015_17].Batch, [2015_17].Status, [2015_17].Designation,
[2015_17].Category, [2015_17].Company, [2015_17].Location, [2015_17].State, CAST
([2015_17].Contact AS NVARCHAR) AS Contact, [2015_17].Email
FROM [2015_17]
UNION ALL
SELECT [2016_18].Name, [2016_18].Batch, [2016_18].Status, [2016_18].Designation,
[2016_18].Category, [2016_18].Company, [2016_18].Location, [2016_18].State, CAST
([2016_18].Contact AS NVARCHAR) AS Contact, [2016_18].Email
FROM [2016_18]
UNION ALL
SELECT [2017_19].Name, [2017_19].Batch, [2017_19].Status, [2017_19].Designation,
[2017_19].Category, [2017_19].Company, [2017_19].Location, [2017_19].State, CAST
([2017_19].Contact AS NVARCHAR) AS Contact, [2017_19].Email
FROM [2017_19]
UNION ALL
SELECT [2018_20].Name, [2018_20].Batch, [2018_20].Status, [2018_20].Designation,
[2018_20].Category, [2018_20].Company, [2018_20].Location, [2018_20].State, CAST
([2018_20].Contact AS NVARCHAR) AS Contact, [2018_20].Email
FROM [2018_20]
UNION ALL
SELECT [2020_22].Name, [2020_22].Batch, [2020_22].Status, [2020_22].Designation,
[2020_22].Category, [2020_22].Company, [2020_22].Location, [2020_22].State, CAST
([2020_22].Contact AS NVARCHAR) AS Contact, [2020_22].Email
FROM [2020_22];
---Extracting all the data
SELECT * FROM Alumni_data1
---Batch-wise count of Alumni
C:\Users\HP\OneDrive\Desktop\BIDM.sql 3
select Batch, COUNT(name) As "Number of Alumni"
From Alumni_data1
group by Batch
order by COUNT(name) Desc;
---State-wise data
select State, COUNT(name) As "Number of Alumni"
From Alumni_data1
group by State
order by COUNT(name) Desc;
---Category-wise data
select Category, COUNT(name) As "Number of Alumni"
From Alumni_data1
group by Category
order by COUNT(name) Desc;
---Company wise data
select Company, COUNT(name) As "Number of Alumni"
From Alumni_data1
group by Company
order by COUNT(name) Desc;
---State & City wise alumni distribution
SELECT State, location, COUNT(Name) AS "Number of Alumni"
FROM Alumni_data1
GROUP BY State, location
ORDER BY State ASC, COUNT(Name) DESC;
---whole data State & City wise
SELECT State, location, Batch, Name, Designation, Company, Contact, Email
FROM Alumni_data1
ORDER BY State ASC;
