Advanced SQL SELECT Tutorial using Aggegrate Functions by Examples
[info]In case you are beginner with SQL, you are advised to visit the following tutorials:
>SQL SELECT Tutorial : Searching & Querying the database
>Join : SELECT Tutorial using multiple database tables[/info]
The important SQL command is SELECT statement. The SQL SELECT statement is used to query or retrieve data from a table in the database. A query may retrieve information from specified columns or from all of the columns in the table:
Syntax of SQL SELECT Statement:
SELECT column_list FROM table-name [WHERE Clause] [GROUP BY column_name] [HAVING clause] [ORDER BY column_name DESC];
Database Tables Structure
In order to demonstrate the usage of SELECT statement, we will be using the following database during the course of this tutorial . The database is for an ecommerce store with 5 tables as :
Countries, Orders, Clients, Products, Prodcat
SQL Aggregate Functions
The usual SQL functions that are mostly used are listed below:
- AVG( ) – Returns the average value
- COUNT( ) – Returns the number of rows
- SUM( ) – Returns the sum
- FIRST( ) – Returns the first value
- LAST( ) – Returns the last value
- MAX( ) – Returns the largest value
- MIN( ) – Returns the smallest value
[success]Note that aggregate functions are used most of the time with GROUP BY[/success]
1 Find the total number of customers ?
2 Find the product having the largest price ?
3 Find the total revenue for the month of June 2010 ?
4 Find the total revenue for the months of Junes ( ALL Years ) ?
5 Find the three most ordered products ?
6 Find the client having paid the largest amount ?
7 Find the first five countries with the largest number of orders ?
8 Find the most popular product in Canada for the year 2012 ?