Clauses

GROUP BY clause      

          when there is a need to obtain subtotals in reports,the Group BY clause is used.A query that includes a GROUP BY Clauses a grouped query that includes a GROUP BY Clause is a  grouped query as it groups the data from SELECT tables and produces a single summary row for each group.The columns named in the GROUP BY Clause are the grouping columns.when a GROUP BY Clause is used ,each item in the SELECT list must be single -valued per group.it can contain only column names,aggregate function,constants of expression involving combinations of the above.All column names in the SELECT list must appear in the GROUP BY ,unless the name is used only in aggregate function.if 2 rows have nulls in the same grouping columns and identical values in all the non-null grouping columns,they are combined into the same group.

SELECT COUNT (stuffno), SUM(salay) FROM stuff GROUP BY branchno 

RESTRICTING GROUPS 

                                           HAVING is used the GROUP  BY clause to restrict 

the groups that appear in the final result table.HAVING and WHERE are similar in syntax,but serves different purposes.WHERE filters individual rows,while HAVING filters groups going into the final result table.The  column names used in the HAVING clause must appear in the GROUP BY list or within an aggregate  Function.The condition in  HAVING always includes at least one aggregate function,otherwise that condition could be moved to WHERE clause and applied to individual rows.

Sub queries
          

                                 Here , a complete SELECT statement is embedded within another SELECT statement.The result of the inner SELECT (subSELECT) are used in the outer statement to help determine the contents of the final result.A sub select can be used in the WHERE and HAVING clauses of an outer SELECT statement ,where it is called a sub queries /nested queries.Sub selects may also appear in INSERT,UPDATE,INSERT statements.A sub query might return a single column and a single row (a single value),multiple columns but a single row one or more columns and multiple rows.

Rules applying to sub queries

• The ORDER BY clause may not be used in a sub query ,although it may not be used in the outermost SELECT statement.
• The sub query SELECT list must consits of a single column name or expression ,expect for sub queries that use the keyword EXISTS
• by default ,column names in a sub query refer to the table name in the FROM clause of the sub query .it is possible to ref fer to a table in a FROM clause of an outer query by qualifying the column name.
• when sub query is one of the 2 operands involved in a comparison,the sub query must appear on the right -hand side of the comparison.

Example:This is incorrect as the sub query appears on the left hand side

SELECT staffno,salary 

FROM staff

WHERE (SELECT AVG (SALARY))

FROM staff)<salary;

                  
ANY(or SOME) and ALL

                                      Any (or some) and All may be used with sub queries that produce a single column of numbers.If the sub query is preceded with ALL,the condition will only be true If it is satisfied by all values produced by sub query.If preceded by any(1 or more) values produced by the sub query.If the sub query is empty,All condition return true,and ANY (or SOME) returns false.

Malty-table Queries 

                            All the examples considered so far have a major limitation.That is,the columns that are to appear in the result table must come from a single table.To combine columns from several tables into a result table we need to use a join(or an inner join)operation.The SQL join operation combines information from 2 tables by forming pairs of related rows from the 2 tables.The row pairs that make up that joined table are those where the matching columns in each of the two tables have the same value.
                                                                   
                                                                                                  To perform a join,we simply include more than one table in the FROM clause using a comma separator and including a WHERE clause to specify the join columns

Example:List the client numbers of all clients who have viewed a property together with the viewed  date.

SELECT c.clientNO,PropertyNO,viewDate

From Client c,Viewing v

WHERE c.clientNO=v.clientNO;

            Here ,an alias has been used for the tables named in the FROM clause.The alias is separated from the table name with space.It can be used to qualify a column name whenever there is ambiguity  regarding the source of the columns name and also can be used a short hand notation for the table name.If an alias is provided,It can be used anywhere in place of the table name.
                        
                   In the Above example,we want to display details from both the client and viewing tables,and we have to use a join.The SELECT clause list the columns to be displayed.There is a need to specify from where the clientNO.Therefore,we prefix the alias.to get the required rows,we include  those rows from both tables.that have identical values in the clientNo columns,using the search condition,c.clientNo=v.clientNO.the output follows

                                                   Here the  search condition  compares the primary key
and the foreingn key.The primary key in the client table which is c.clientNo is compared with the foreign key in the viewing table which is v.clientNo.The above join operation can also be expressed in the follwing ways:

FROM client c JOIN viewing v ON c.clientNo= v.clientNo
FROM Client JOIN  viewing  USING clientNo
FROM client Natuaral JOIN viewing 


ORDER BY clause

                               In general, the roes of an SQL Query result table are not Arranged in any particular Order .we could achive this thourght  the Order by clause.it consists of a list of names that the result is to be  sorted on,separated by commas.ORDER BYclause allows the retrieved rows to be orderd in  ascending(ASC) or desending(DESC) order on any column or any combination of columns.it should be the last clause in the SELECT statement.

                                   

                              It possible to include more than 1 element in the OREDER BYclause.The major sort key detemines the overall order of the result table.if the values of the major sort key are unique ,there is no need  for the additional keys to controll sort.if they are not unique ,there may be multiple rows in the result table with the same value for the major sort key.Therefore , a second element could be used to oder rows with the same value for the major sort key,and this is  called  as a minar sort key.