Scribd
Upload
3 views10 pages

SQL Queries for B.Tech Allotment Analysis

The document presents 20 SQL queries focused on analyzing B.Tech college allotments using GROUP BY and WHERE filters. It includes insights on student distributions by city, average tuition fees by group, and statistics on students based on caste and gender. The summary emphasizes the effectiveness of these SQL functions in data analysis.

Uploaded by

Rktech gaming
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PPTX, PDF, TXT or read online on Scribd
3 views10 pages

SQL Queries for B.Tech Allotment Analysis

The document presents 20 SQL queries focused on analyzing B.Tech college allotments using GROUP BY and WHERE filters. It includes insights on student distributions by city, average tuition fees by group, and statistics on students based on caste and gender. The summary emphasizes the effectiveness of these SQL functions in data analysis.

Uploaded by

Rktech gaming
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PPTX, PDF, TXT or read online on Scribd

SQL Queries on B.

Tech College
Allotments
By: Your Name
Date: Your Presentation Date
Introduction
Good [morning/afternoon], everyone.
Today, I will present 20 SQL queries I created using the GROUP BY and WHERE filters
based on a dataset of [Link] college allotments.
The dataset includes information like student IDs, their ranks, the branch or group they
joined, city, college name, tuition fee, caste, and gender
What are GROUP BY and WHERE?
- GROUP BY: Groups rows sharing the same value(s) in specified column(s).
- WHERE: Filters rows based on conditions before grouping.
- Together, they help summarize and analyze data.
Query 1 — Count students in each city
SQL Query:
SELECT City, COUNT(*) AS StudentCount
FROM btech_allotments
GROUP BY City;

Sample Result:
City | StudentCount
-----------------------
Hyderabad | 10
Vizag | 5
Warangal | 6
Guntur | 5
Vijayawada| 4

Insight: Hyderabad has the most students.


Query 2 — Average tuition fee by group
SQL Query:
SELECT GroupName, AVG(TuitionFee) AS AvgFee
FROM btech_allotments
GROUP BY GroupName;

Sample Result:
GroupName | AvgFee
----------------
CSE | 118000
ECE | 98000
IT | 97000
EEE | 92000
MECH | 88000

Insight: CSE has the highest average tuition fee.


Query 3 — Students paying fees above
₹1,00,000 per college
SQL Query:
SELECT CollegeName, COUNT(*) AS Count
FROM btech_allotments
WHERE TuitionFee > 100000
GROUP BY CollegeName;

Sample Result:
CollegeName | Count
-------------------
VNR VJIET | 4
CVR College | 5
Malla Reddy | 2
RVR & JC | 3
Query 4 — Students by caste and gender
SQL Query:
SELECT Caste, Gender, COUNT(*) AS Count
FROM btech_allotments
GROUP BY Caste, Gender;

Sample Result:
Caste | Gender | Count
---------------------
OC | Male | 7
OC | Female | 3
BC-A | Male | 4
BC-A | Female | 3
SC | Male | 2
SC | Female | 3
ST | Male | 1
ST | Female | 2
Query 5 — Average rank for OC students by
group
SQL Query:
SELECT GroupName, AVG(`Rank`) AS AvgRank
FROM btech_allotments
WHERE Caste = 'OC'
GROUP BY GroupName;

Sample Result:
GroupName | AvgRank
-----------------
CSE | 810
ECE | 950
MECH | 900
Summary
- SQL’s GROUP BY helps aggregate data by categories.
- WHERE filters data for specific conditions.
- Combined, they let us analyze student distributions, fees, ranks, and other stats
effectively.
Thank You & Questions
Thank you!
Any questions?

You might also like