The SELECT Statement Part 2: Multiple Queries, Multiple Tables and More...

     This is the continuation of the previous discussion about the "SELECT" statement. Previously, we discussed using the "SELECT" statement on single query retrieval. Today we will further discuss the "SELECT" statement on how to use it in multiple queries and multiple tables.

NESTING OF QUERIES
     A nested query is a query that occurs if a query is used on another query. That another query is called the 'outer query'. We use nested query if you want to get a more specific data in a database which is only attainable if you get another query from the database.
     Here is sample situation. Suppose you would like to get the maximum price of the albums with the highest possible ratings in a database. You are likely to use this statement "SELECT max(price) from album where rating = max(rating);".
     This statement would result to this error:

     To fix this statement, you would need to use a nested query. Here is an example:

NULL IN SQL QUERIES
     The NULL keyword is another keyword that can be used after the WHERE-clause. The NULL keyword is used to determine if a value is missing or undefined or not applicable in an attribute. To use this you will need to use the IS NULL or IS NOT NULL keywords.
     Here are some examples:

THE JOINED RELATIONS
     Joined relations are used to get data from two different tables at the same time. It is often used on conditions where you would need to combine two separate tables or relations. To use this you would need to use the JOIN function. You can use either LEFT JOIN, INNER JOIN, RIGHT JOIN and OUTER JOIN keywords.
     Here is an example:

AGGREGATE FUNCTION
     From the examples above, there is a COUNT and MAX keywords used. Those keywords are an example of aggregate functions. Aggregate functions are used like mathematical equations to make retrieval of computed data easier. The aggregate functions are the COUNT, SUM, MAX, MIN and AVG. Just like their keywords suggests, the COUNT gets the total number of count on that attribute, the SUM gets the sum of all data in that attribute, the MAX gets the maximum value of the data in that attribute, the MIN gets the minimum value of the data in that attribute and the AVG gets the average of the data in that attribute.
     Here is an example:

GROUP BY-CLAUSE
     GROUP BY-clause is used to specify which will be the grouping attribute. The grouping attribute will be the attribute wherein all value are different. Usually if that attribute has many data with the same value, the one that will be displayed is the first data of that value.
     Here is an example:

HAVING-CLAUSE
     HAVING-clause is usually added at the latter part of the attribute after a GROUP BY-clause. It is used to add condition on the group created by the GROUP BY-clause.
     Here is an example:

ORDER BY-CLAUSE
     ORDER BY is the clause use to sort the data displayed either in ascending or descending order. It is usually added at the last part of the statement. To choose which order would you like to use just add ASC for ascending or DESC for descending at the end of the ORDER BY-clause.
     Here is an example: