\begin{slide} \stitle{Count and Max---Simple Group Functions}
\begin{slist} \item \textit{Group} or \textit{set} functions retrieve
statistics about groups of rows.  \item The \texttt{count(*)} function
returns the count of rows that match.
\begin{salltt}
select count(*) from books 
    where  title like '%DRAGON%';
\end{salltt}

\item The \texttt{max} function find the maximum value of a particular column.
\begin{salltt}
select max(checkouts) from books; \end{salltt} \item Other group
functions exist for computing minimum, average and standard deviation.
\end{slist} \end{slide} \begin{slide} \stitle{Group By and Having}
\begin{slist} \item Often you wish to select statistics over
\textit{groups} of rows that share one or more columns.

\item
To calculate the number of books by each author:
\begin{salltt}
select author, count(*) from authors group by author;
\end{salltt}

\item Only columns referenced in \texittt{group by} can be included
directly in the select list.

\item The \texttt{having} clause  restricts the groups that will be returned to those meeting a condition.
\end{slist}

			\end{slide}

\begin{slide}
\stitle{Create View}

\begin{slist}
\item SQL provides a facility to name a select statement.

\item
\begin{salltt}
create view book_authors as 
    select author, title, b.bookid from authors a, books b
    where b.bookid = a.bookid;
\end{salltt}

\item Views can be treated as tables in the from clause of other selects.

\item Views may be updatable; check your particular view to see if it
works.  Group functions and non-equijoins will force a view not to be
updatable.
\end{slist}
\end{slide}


\begin{slide}
\stitle{Hierarchical Queries in Oracle}

\begin{slist}
\item Oracle provides a facility for examining hierarchically structured data.  Use this with care; it often optimizes hierarchical queries very poorly.

\item  The \textit{start with} clause   describes rows that are the root of a hierarchy.  Like the \textit{where} clause, \textit{start with} can contain sub-queries.

\item The \textit{connect by} clause describes how to go down the hierarchy.  It cannot contain sub-queries and must use the \textit{prior} keyword.

\item The query may include rows more than once if a row appears in
the hierarchies more than once.  No loops are allowed.  The special
variable \textit{level} refers to the current depth in the hierarchy.
\end{slist}

\end{slide}

\begin{slide}
\stitle{Oracle Hierarchy Example}

\begin{slist}

\item Construct a query to descend the tree of members based on who
originally sold them their membership.  Include the level in the tree.

\item
\begin{salltt}
select level, first, middle, last
    from members start with mem_lib = \textit{first\_member}
    connect by prior memberid = mem_lib;
\end{salltt}

\end{slist}

\end{slide}