SQL queries based on Aggregate functions :
1. List branchid and the number of courses offered by each branch.
2. List branchid and the number of electives offered in each branch.
3. List instructorid and number of different courses offered by those instructors.
4. List the total strength of students staying in each Hostel.
5. List courseid and number of students registered in each course.
6. Count the number of students currently studying in branch B2.
7. Find the details of the total allocated hours for every branch.
8. List the instructorid who has taken at least 2 courses.
Inorder to solve the above queries, we would require several tables and the structure of those required tables to solve problems are as follows :
1. Course(course_id, course_name, semester, branch_id, electives, duration_hrs, all )
2. Instructors( inst_id, course_id )
3. Hostels( hostel_id, stud_id )
4. Registration( course_id, stud_id )
5. Student( stud_id, branch_id )
Solutions :
Query 1 :
List branchid and the number of courses offered by each branch.
Let's populate the 'course' table as follows:
Let's populate the 'course' table as follows:
 |
| Course table |
So, we are asked to list branch_ids and the number of courses associated with it.
e.g. as can be seen in the table,
- B1 offers two courses( i.e. 101 and 103 )
- B2 also offers two courses( i.e. 102 and 104 )
It is pretty much clear that we require to retrieve two columns :
1) branch-id
1) branch-id
2) number of courses
As it is clear by looking at the table, we can't retrieve the 'number of columns' directly from the 'course' table, therefore, in-order to achieve that, we've to use aggregate function COUNT() clause on 'course_id' column, which will count the number of courses associated with one brach_id and give us the number of courses.
Another thing is that, we've to group the retrieved data, in-order to prevent repetition of data.
e.g. 'B1' comes twice in the 'branch_id' column
and 'B2' also comes twice in the 'branch_id' column
Therefore, to prevent this repetition in our retrieved data, we use GROUP BY clause .
SQL code :
SELECT branch_id, COUNT(course_id) AS number_of_students FROM course GROUP BY branch_id;
-------------------------------------------------------------------------------------------------------------------------------
Query 2 :
List branchid and the number of electives offered in each branch.
This case is almost similar to the solution of Query 1, as now, we've to see for electives associated with branch_ids.
|
| Course Table |
CAUTION : the course_id values were unique in every row, but its not case with electives. We can see repeated values under 'electives' columns. Here in-order to prevent repetition of values , we'll use DISTINCT keyword.
SQL code :
SELECT branch_id, COUNT(DISTINCT electives) AS elective_subjects FROM course GROUP BY branch_id;
----------------------------------------------------------------------------------------------------------------------------------
Query 3 :
List instructorid and number of different courses offered by those instructors.
We've 'instructors' table as follows :
 |
| Instructor Table |
As can be clearly seen in the table, an instructor can have more than one course_ids associated with him and that's what we are asked to solve in query. i.e. we've to retrieve the instructor_id and the number of course_ids associated with him.
SQL code:
SELECT inst_id, COUNT( course_id) FROM instructors GROUP BY inst_id;
-----------------------------------------------------------------------------------------------------------------------------------
Query 4 :
List the total strength of students staying in each Hostel.
__________________________________
We've to use table : | hostels( hostel_id, stud_id ) |
---------------------------------------
--------------------------------------
hostel_id | stud_id |
--------------------------------------
H1 | 401 |
H1 | 402 |
H2 | 403 |
H3 | 405 |
H3 | 406 |
--------------------------------------
As can be seen in the table, there can be more than one students in the same hostel, and that's what we are asked to find the number of students in each of the present hostel. Therefore, we'll put the 'stud_id' in the COUNT() aggregate function, which will give us the total number of students. And we'll use GROUP BY clause to group hostels.
SQL code :
SELECT hostel_id, COUNT( stud_id ) AS total_students FROM hostels GROUP BY hostel_id;
-----------------------------------------------------------------------------------------------------------------------------------
Query 5 :
List courseid and number of students registered in each course.
We've to use another table named Registration :
---------------------------------------
course_id | stud_id |
---------------------------------------
101 | 401 |
101 | 402 |
102 | 401 |
103 | 404 |
104 | 405 |
104 | 406 |
---------------------------------------
As can be seen in the above table, more than one student can pursue same course. It means, with single course_id, there can be many stud_id associated with it. And that's what we are asked to find, i.e. group by the course_ids and number of students associated with those course_ids.
Also, it can be seen, that a student can pursue more than one course at a time, as student with stud_id 401 is pursuing two courses with course_id 101 and 102.
SQL code:
SELECT course_id, COUNT( stud_id ) FROM Registration GROUP BY course_id;
------------------------------------------------------------------------------------------------------------------------------------
Query 6 :
Count the number of students currently studying in branch B2.
We've to use table named Student :
---------------------------------------stud_id | branch_id |
---------------------------------------
401 | B1 |
402 | B2 |
403 | B2 |
404 | B4 |
405 | B2 |
406 | B3 |
---------------------------------------
This is almost similar to previous problem, as we are asked to find how many students took branch_id B2. It can be clearly seen from that there are 3 students who took branch_id B2.
SQL code :
SELECT COUNT( stud_id ) FROM Student WHERE branch_id = 'B2';
------------------------------------------------------------------------------------------------------------------------------------
Query 7 :
Find the details of the total allocated hours for every branch.
We've to use table named 'course' as given follows :
 |
| Course Table |
So, we have to use SUM() aggregation function on column duration_hrs.
SQL code :
SELECT branch_id, SUM( duration_hrs ) AS total_duration FROM course GROUP BY branch_id;
------------------------------------------------------------------------------------------------------------------------------------
Query 8 :
List the instructorid who has taken at least 2 courses.
Here, we have to use instructors table :
 |
| Instructor Table |
It can be seen in the table that instructor with inst_id 1001 takes two courses with course_id 101 and 102. And we are asked to find those instructors those who have taken atleast two courses.
NOTE : since we have to use aggregate function COUNT(course_id ) in-order to count the number of courses associated with one instructor. So, it's a fact the counting is done at the run-time of SQL code, which means value is not already present anywhere in the table.
And we have a condition that we must display only those instructors who has taken at-least 2 courses.
This condition can be applied using 'HAVING' clause.
Some of you may argue, Why can't we use 'WHERE' clause as a conditional test ?
'WHERE' clause is always used for the already present columns of the table, whereas, 'HAVING' clause is used for the aggregate functions.
One thing to note is that any column name appearing in the HAVING clause must also appear in the GROUP BY clause.
SQL code :
SELECT inst_id, COUNT( course_id) AS total_courses FROM instructors GROUP BY inst_id HAVING COUNT(course_id) >= 2;
-----------------------------------------------------------------------------------------------------------------------------------
No comments:
Post a Comment