DBMS Lesson 5 SQL Part 4

This post will be a continuation of the previous post.Today our discussion is about Group By clause and Having clause.අද කතා කරන්නේ පෙර පෝස්ටුවේ ඉතිරි කොටසයි.Group By රීතිය සහ Having රීතිය භාවිතය අද පාඩමෙන් කියා දෙනු ලැබේ.
If you haven't read my previous lessons please follow below links.පෙර පාඩම් මාලාව සදහා පිවිසෙන්න 

Lesson 1,Lesson 2,Lesson 3,Lesson 4 Part 1,Lesson 4 Part 2,Lesson 5 Part 1,Lesson 5 Part 2,Lesson 5 Part 3

Firstly we'll start with Group By clause/පලමුව Group By රීතිය ගැන බලමු.

Syntax:
Select < attributes > From < relations > Where < conditions >Group By < attributes > Order By < attributes > asc/desc

Let's take a look at some examples

These examples are based on the tables given in the Lesson 5 Part 3.So before that go through the tables given earlier.පහත උදාහරණ සියල්ල පෙර පෝස්ටුවේ වගු ආශ්‍රිතව දිග හැරේ.එම නිසා පෙර පෝස්ටුව ප්‍රතමයෙන් අධ්‍යනය කරන්න.

Example 1:
Q: Find the total quantity supplied for each project

A: Select JNo,sum(Qty) from Shipment Group by JNo

Example 2:
Q: Find the minimum quantity supplied by each supplier

A: Select SNo,min(Qty) from Shipment Group by SNo

Example 3:
Q: Display the average quantity supplied for each project in descending order of project number

A: Select JNo,avg(Qty) from Shipment Group byJNo Order by JNo desc

Example 4:
Q: Count the number of parts supplied by each supplier and display in ascending order of supplier number

A: Select SNo,count(Distinct PNo) from Shipment Group by SNo order by SNo asc

Example 5:
Q: Find the total quantity supplied in each part and display the results in descending order of part number.For this query Qty more than 100 will not be valid

A: Select PNo,sum(Qty) from Shipment where Qty<=100 Group by PNo Order by PNo desc


Using Having clause

Syntax:
Select < attribute > From < relations > Where < conditions > Group By < attribute > Having < conditions > Order By < attributes > asc/desc

Example 1:
Q: Find the projects having total quantity supplied more than 150

A: Select JNo from Shipment Group by JNo Having sum(Qty)>150

Example 2:
Q: Find the suppliers who supply at least 2 parts

A: Select SNo from Shipment Group by SNo Having count(Distinct PNo)>=2

Example 3:
Q: Display the project numbers in ascending order where more than 3 suppliers supplying parts

A: Select JNo from Shipment Group by JNo Having count(Distinct PNo)>3 order by JNo asc

Example 4:
Q: Display the part numbers in descending order where average quantity supplied is more than 50.For this query, quantity more than 100 are not valid

A: Select PNo From Shipment Where Qty <100 Group by PNo Having avg(Qty) > 50 Order by PNo desc


Click Here To Download This Lesson in PDF Format



So far we have discuss data retrieving.Next Post will be based on data insertion.ඊලග පෝස්ටුව වගුවකට දත්ත ඇතුලත් කිරීම පිලිබදව කතා කෙරේ.Keep in touch...