Lesson 1,Lesson 2,Lesson 3,Lesson 4 Part 1,Lesson 4 Part 2,Lesson 5 Part 1,Lesson 5 Part 2
Ordering Of Results
Select < attribute > From < relation> [ Where < conditions > ] [ Order by < attribute > asc/desc ]
asc(ascending) - ආරෝහන( කුඩා අගයක සිට විශාල අගයකට )
desc(descending) - අවරෝහන( විශාල අගයක සිට කුඩා අගයකට )
Example 1:
Q: Display Supplier details in ascending order of name
A: Select * from Supplier order by Name asc
Example 2:
Q: Display cities of Suppliers in descending order
A:Select Distinct City from Supplier order by City desc
Example 3:
Q: Display name and status of Suppliers those who are in Colombo in descending order of names
A: Select Name,Status from Supplier where City='Colombo' order by Name desc
Example 4:
Q: Display details of suppliers in descending order of status of those who are in Colombo and those status>30
A: Select * from Supplier where City='Colombo' and Status>30 order by Status desc
Example 5:
Q: Display branch names of accounts in ascending order where balance between 2000 and 5000
A: Select BrName from Account where Balance between 2000 and 5000 order by BrName asc
Example 6:
Q: Display account numbers of accounts in Galle branch in descending order
A: Select AccNo from Accounts where BrName='Galle' order by AccNo desc
Example 7:
Q: Display details in Account table where balance in ascending order and account names in descending order
A: Select * from Accounts order by AccNames desc,Balance asc
Next we'll talk about Aggregate Value Functions
- SUM() - To find the total/එකතු කිරීම් සදහා
- AVG() - To find the average/සාමාන්ය සෙවීම සදහා
- MAX() - To find the maximum value/වැඩිම අගය සෙවීම සදහා
- MIN() - To find the minimum value/අවම අගය සෙවීම සදහා
- COUNT() - To find the number of instances/ප්රමානය සෙවීම සදහා
- ROUND() - To round off to nearest number/ආසන්නතම අන්කයට වැටයීම සදහා
Lets see these functions through examples/උදාහරණ මගින් විමසමු....
Example 1:
Q: Count the number of projects
A: Select count(JNo) from Project
Example 2:
Q: Find the average status of suppliers
A: Select avg(Status) from Suppliers
Example 3:
Q: Find the minimum quantity supplied for a project
A: Select min(Qty) from Shipment
Example 4:
Q: Find the average quantity supplied by supplier S3
A: Select avg(Qty) from Shipment where SNo='S3'
Example 5:
Q: Count the number of parts supplied by supplier S2 for different projects
A: Select count(Distinct PNo) from Shipment where SNo='S2'
Example 6:
Q: Find the Suppliers who supplied maximum quantity for a project
A: Select SNo from Shipment where Qty=(Select max(Qty) from Shipment)
Query inside the brackets is called as a Sub Query and it works first before the main query gets executed.වරහන් තුල ඇති function එක subquery එකක් වේ.ප්රතමයෙන් එය ක්රියාත්මක වන අතර ඉන් පසු main query එක ක්රියාත්මක වේ.
Click Here To download This Lesson in PDF Format
Next post will be on Grouping and Having clauses.Keep in touch....
No comments:
Post a Comment