👉 To give a table, or a column in a table, a temporary name we can use Alias:
Syntax: SELECT "column_name" AS "column_Alias" FROM "table_name";
For example we have a table with column_name as “SNo” , “Country” and “Capital” and we want to update it as “Serial Number” , “State” and “Country_Capital then we will run the following command:
👉 To count the number of rows in the table:
Syntax: SELECT COUNT(column_name) AS (alias_name) FROM table_name;
For example we want to count number of orders placed by a particular customer from customer_table:
This will show the count of numbers of order places by the customer id “CG-1234′ and the products ordered.
👉 To add the values in a columns we use Sum command:
Syntax: SELECT SUM (column_name) FROM table_name;
For example we want to add the profit amount from the sales table:
👉 To find out an average for a column in the table:
Syntax: SELECT AVG (column_name) FROM table_name;
For example we need to find the average age of the customers from customer table:
👉To find the minimum and the maximum value in a table:
SELECT MIN (column_name) FROM table_name;
For example we want to find the minimum order sale made for a product from sales table:
SELECT MAX (column_name) FROM table_name;
For example we want to find the maximum order sale made for a product from sales table:
👉 To groups rows that have the same values into summary rows, like “find the number of customers in each region” we use GROUP BY statement. 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","function-type" (column_name) FROM table_name Group By "column-name";
For example we need to find the number orders places in each region in the table:
👉 To make conditions for aggregate functions we use HAVING clause. The difference between Having clause and Where clause is that we cannot make conditions in aggregate functions whereas we can make conditions in aggregate functions using Having clause.
Syntax: SELECT column_name, AGGREGATE FUNCTION column_name FROM tables GROUP BY (column1) HAVING condition;
For example we have some set of customers in four regions and we want to see in which region the count of customers is more than 200. In this scenario we will use the HAVING clause and set a condition of count more than 200.
This command will show the count of customers in the regions where it is more than 200.
👉 To go through a condition and return a value when the first condition is met (like an if-then-else statement) “CASE” statement is used. So, once a condition is true, it will stop reading and return the result. If no conditions are true, it returns the value in the ELSE clause.
Syntax: SELECT * CASE WHEN condition THEN result ELSE result END;
Tagged : alias / Case / count / having / maximum / minimum / sql / Update
