Page 1 :
SQL QUERIES 3Order By, Group By, Having, Aggregate functions, Made by- Prashant Sharma , Batch IP12 2021
Page 2 :
ORDER BY Clause :-, The ORDER BY keyword is used to sort the result-set in ascending or descending order., The ORDER BY keyword sorts the records in ascending order by default. To sort the records in descending order, use the DESC keyword., Syntax:, SELECT column1, column2, ... FROM table_name ORDER BY column1, column2, ... ASC|DESC;
Page 3 :
ORDER BY Clause Example :-, Example – , , Select * From Customers, ORDER BY AGE;, , In the above Query, , The result set will be arranged in , Ascending order of age by default. Since * is used here so all the columns will be selected.
Page 4 :
ORDER BY Clause Example :-, Example – , , Select ID, Age From Customers, WHERE Age > 22, ORDER BY AGE DESC;, , In the above Query, , The result set will include the ID and AGE of only those records where AGE >22 arranged in Descending order., , *When you need to use where along with Order By or even Group By Where is always placed before ORDER BY or GROUP BY.
Page 5 :
GROUP BY Clause :-, The GROUP BY statement groups rows that have the same values into summary rows, like "find the number of customers in each country"., The GROUP BY statement is often used with Aggregate functions (COUNT(), MAX(), MIN(), SUM(), AVG()) to group the result-set by one or more columns., SYNTAX:-, SELECT column_name(s) FROM table_name WHERE condition GROUP BY column_name(s) ORDER BY column_name(s);
Page 6 :
GROUP BY Example :-, Example :- , , Select Count(ID), PurchaseMonth FROM CUSTOMERS, Where Age > 15, GROUP BY PurchaseMonth;, , Output:, CUSTOMERS, *COUNT()-Counts the total no. of records
Page 7 :
HAVING :-, HAVING is very similar to WHERE, as it is used before putting a condition in an SQL Query., The Question is how to decide , when to use ‘WHERE’ and when to use ‘HAVING’., The HAVING clause was added to SQL because the WHERE keyword cannot be used with aggregate functions(in the condition)., HAVING Syntax, SELECT column_name(s) FROM table_name HAVING condition ORDER BY column_name(s);
Page 8 :
HAVING Example :-, Example :- , , Select Count(ID), PurchaseMonth FROM CUSTOMERS, HAVING Count(ID)>1, GROUP BY PurchaseMonth;, , Output:
Page 9 :
Aggregate Functions, The Aggregate functions that we use in SQL are Count(), Avg(), Sum(), Max(), Min(), Count() – The COUNT() function returns the Total number of rows that matches a specified criterion., Avg() – The AVG() function returns the average value of a numeric column. , Sum()- The SUM() function returns the total sum of a numeric column., Max()- The MAX() function returns the largest value of the selected column., Min()- The MIN() function returns the smallest value of the selected column.