\documentclass[landscape]{slides}
\usepackage{alltt}

\setlength{\topmargin}{-1.25in}
\addtolength{\textheight}{1.25in}

\def\slist{\list{-}{
  {\setlength{\parsep}{0ex plus0.2ex}
   \setlength{\itemsep}{0.2ex plus0.2ex minus0.2ex}
  }
}}
\let\endslist\endlist

\def\salltt{\vspace{-\baselineskip}\alltt}
\let\endsalltt\endalltt




\def\stitle#1{
\begin{center}
\large #1
\end{center}
}

\begin{document}
\pagestyle{plain}
\special{papersize=11in,8.5in}

\begin{slide}
\stitle{Asking  Questions with Select}

\begin{slist}

\item \textit{select} is used to retrieve data from one or more tables.

\item
\begin{salltt}
select \textit{select-list} from \textit{tables} \end{salltt} \item
The special ``select list \texttt{*}'' selects all columns.  \item
Alternatively, the select list can be any set of SQL expressions
separated by commas.

\item Returns select list evaluated for all rows in table.
\end{slist}

\end{slide}

\begin{slide}
\stitle{Simple SQL Expressions}

\begin{slist}

	\item Column names like \texttt{title} or \texttt{checkouts}
	refer to the data in the current row.


\item Constant expressions like \texttt{2}, \texttt{'foo bar quuux'}
or \texttt{3.14159} refer to themselves.
\item Arithmetic expressions like \texttt{2 * checkouts} or \texttt{position - 1} are also allowed.


\end{slist}

\end{slide}

\begin{slide}
\stitle{Ultra-Simple Select Examples}


\begin{slist}
\item {\tt select * from books;}

{\small
\begin{tabular}{rllrr}
BOOKID & TITLE & SERIES & POSITION & CHECKOUTS \\
\hline
1 & Ender's Game & Ender & 1 & 0 \\
2 & Eon & Eon & 1 & 20 \\
\end{tabular}
}

\item {\tt select 2.4, 2*bookid, position+bookid from books;}

{\small
\begin{tabular}{rrr}
2.4 & 2*BOOKID & BOOKID+POSITION \\
\hline
2.4 & 2 & 2\\
2.4 & 4 & 3 \\
\end{tabular}
}

\end{slist}
\end{slide}

\begin{slide}
\stitle{Constraining Queries}

\begin{slist}

\item The where  clause allows  only a subset of the rows in a table to be retrieved.

\item 
\begin{salltt}
select \textit{select-list} from \textit{tables} where \textit{condition}
\end{salltt}

\item Condition can use simple comparisons like \texttt{series = 'Ender'} or \texttt{position > 4}.

\item Parentheses, Boolean operators \texttt{and} and \texttt{or} also work. 

\item The \texttt{like} operator allows primitive regular expression matching.

\item The \texttt{distinct} modifier eliminates duplicates.
\end{slist}
\end{slide}

\begin{slide}
\stitle{Simple Constrained Example}

\begin{slist}
\item \texttt{select * from books where checkouts > 0;}

{\small
\begin{tabular}{rllrr}
BOOKID & TITLE & SERIES & POSITION & CHECKOUTS \\
\hline
2 & Eon & Eon & 1 & 20 \\
\end{tabular}
}

\item \texttt{select title, series from books where title like '\%SHIP\%' 
    and series is not null;}

{\small
\begin{tabular}{ll}
TITLE & SERIES \\
\hline
TOM SWIFT AND HIS AERIAL WARSHIP        &      TOM SWIFT\\
TOM SWIFT AND HIS AIRSHIP                &     TOM SWIFT\\
TOM SWIFT AND HIS ROCKET SHIP            &     TOM SWIFT JR.\\
PATTON'S SPACESHIP                       &     TIMELINE WARS\\
ALDAIR, MASTER OF SHIPS                  &     ALDAIR\\
\end{tabular}
}

\end{slist}

\end{slide}

\begin{slide}
\stitle{Sorting and the Order By Clause}

\begin{slist}

\item 
Adding a clause like  \texttt{order by \textit{column}} will  sort results by that column.

\item \texttt{select title, series from books where  title like '\%MARS\%'
    order by series;}

{\small
\begin{tabular}{ll}
TITLE & SERIES \\
\hline
MARS ARENA, THE			&	      DEATHLANDS\\
SPEAR OF MARS, THE		&	      FUTURE AT WAR\\
GODS OF MARS, THE		&	      MARS\\
JOHN CARTER OF MARS		&	      MARS\\
\end{tabular}
}

\item More complex order-bys separating multiple keys by comma are
also allowed.

\item A key can be followed by \texttt{desc} to indicate descending
order.

\end{slist}
\end{slide}

\begin{slide}
\stitle{Cartesian Products of Tables}

\begin{slist}
\item  Multiple tables may appear in the from-clause of a select list.

\item
\begin{salltt}
select series, title, author from books, authors;
\end{salltt}

\item  Each row in the books table would be matched with each row in the authors table.

\item If there were 100 rows in the books table and 300 rows in the authors table,  this would produce $100\times 300 = 30000$ rows.

\item This is normally not useful.
\end{slist}

\end{slide}

\begin{slide}
\stitle{Aliases}
\begin{slist}
\item Columns can be selected under different names.\\
\texttt{select bookid as id from books;}

{\small
\begin{tabular}{r}
ID \\ \hline
1 \\
2 \\
\end{tabular}
}

\item Tables can be assigned extra names within queries. \\
\texttt{select b.title, a.author from books b, authors a;}

\item This can be used to disambiguate queries. \\
\texttt{select b.bookid, b.title, a.author from books b, authors a;}

\end{slist}
\end{slide}


\begin{slide}
\stitle{Equijoins}

\begin{slist}
\item A where clause can be used to join two or more tables on  a particular column.

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

\item This query would show each book's title and author(s)---if a book 
has multiple authors, the book would be listed twice.

\item SQL databases optimize such equijoin queries well.

\item Often it helps to specify and explicit \texttt{order by} so related rows appear near each other in output.
\end{slist}


\end{slide}

\begin{slide}
\stitle{Outer Joins}

\begin{slist}

\item A normal equijoin requires that matching rows exist in both tables.

\item Imagine you want all book copies, and the name of the member who
checked them out, but you still want to list books that are checked
in.  You need an outer join---a join that returns null if no match
exists in one table.

\item Under Oracle:
\begin{salltt}
select b.title, m.last  from members m, books b, copies c
   where c.bookid = b.bookid and c.out_to(+) = m.memberid;
\end{salltt}

\item Other SQL (sometimes):
\begin{salltt}
select b.title, m.last from members m, books  b, copies c 
    left outer join on c.out_to = m.memberid;
\end{salltt}

\end{slist}

\end{slide}

\begin{slide}
\stitle{In, Exists and Complex Expressions}

\begin{slist}

\item 
\begin{salltt}
select first, last from members 
    where memberid in (2021, 2228, 1013);
\end{salltt}

\item You can also use a sub-select in an \textit{in}:
\begin{salltt}
select first, last from members
   where memberid in (select out_to from copies
        where due_by < sysdate\footnote{\rm In Oracle, current date})
\end{salltt}

\item The \textit{exists} condition can also be used:
\begin{salltt}
select first, last from members m 
    where exists (select * from copies 
       where due_by < sysdate and memberid = m.memberid);
\end{salltt}

\end{slist}

\end{slide}

\begin{slide}
\stitle{Correlation in Queries}

\begin{slist}
\item Last slide presents multiple ways of finding members with overdue books.

\item The \textit{in} sub-query  was uncorrelated; it didn't depend on any values of the enclosing query.

\item The \textit{exists} query was correlated to the \textit{memberid} in the enclosing query.

\item Often, correlated queries are slower than uncorrelated queries.
\end{slist}

\end{slide}

\begin{slide}
\stitle{Not and Anti-Joins}

\begin{slist}

\item 
\begin{salltt}
select  title from books 
    where bookid not in (
      select  bookid from copies where out_to is null);
\end{salltt}

\item
Reasonable optimization of anti-joins is less common than good optimization of joins.

\item  Other conditions like \texttt{not exists} also exist.

\item Boolean predicates like \texttt{not} can be used to perform
similar tasks; in general using \texttt{not in} and \texttt{not exists} may be faster.

\end{slist}
\end{slide}


\begin{slide}
\stitle{Set operations}

\begin{slist}

\item The \texttt{union operator}  combines two row sources and removes duplicates.  The \texttt{union all} operator keeps results.

\item The  \texttt{intersect}   operation returns the rows common to both sets.

\item The \texttt{minus} operation returns the rows  of the first set but not the  second.

\item Often, \texttt{union all} is faster than \texttt{union} because it avoids a sort.

\end{slist}

\end{slide}

\begin{slide}
\stitle{Set Examples}

\begin{slist}

\item
Supose we want to find all books containing ``dragon'' in the title
but not by Piers Anthony.  We could do this with an anti-join, but we
won't.

\item 
\begin{salltt}
select title from books 
    where title like '\%DRAGON\%'
minus select title from books b, authors a
    where a.bookid = b.bookid 
    and a.author = 'ANTHONY, PIERS';
\end{salltt}

\item Boolean conditionals can often be used to get the same result as
set operations, but set operations may perform better when the
structure of the two parts of the query is significantly different.
\end{slist}

\end{slide}




\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 \texttt{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}


\begin{slide}
\stitle{\texttt{delete} removes rows}
\begin{slist}
\item \texttt{delete from books} drops all data from \texttt{books} unless
foreign key constraints on other tables prevent this.

\item deletes may be constrained: \\
\texttt{delete from books where title like '\%DARK\%';} \\
\texttt{delete from books where bookid in (select bookid from authors
  where author = 'ANTHONY, PIERS');}

\item You can use pretty much anything that you could use in a select.

\end{slist}
\end{slide}

\begin{slide}
\stitle{\texttt{insert} adds rows}
\begin{slist}
\setlength{\itemsep}{.5\itemsep}

\item \texttt{insert into books values(bookid\_seq.nextval\footnote{this syntax
is Oracle-specific}, 'ZOMBIE LOVER', 'XANTH', 23);} \\
With this syntax, columns must be in the same order as the table definition.
Omitted columns (\texttt{checkouts} here) get their default value or 
\texttt{NULL}.

\item \texttt{insert into books (title, series, bookid) values
('SPEAKER FOR THE DEAD', 'ENDER', bookid\_seq.nextval);} \\
Here we explicitly specify what order the column values will be given in.
Position has been left \texttt{NULL}.

\item You can insert multiple rows at once if you use a select: \\
\texttt{insert into authors (select 'ASIMOV, ISAAC' as author,
  b.bookid from books b where b.bookid not in (select bookid from authors));}\\
makes Isaac Asimov the author for every book that doesn't have an author yet.

\end{slist}
\end{slide}

\begin{slide}
\stitle{\texttt{update} changes rows}
\begin{slist}

\item \texttt{update authors set author = 'CARD, ORSON SCOTT'
where author = 'CARD, ORSON'} \\
corrects the author everywhere we forgot his middle name.

\item \texttt{update copies set out\_by = (select memberid from
members where email = 'jsmith') where barcode = 12345;}

\item The form \\
\texttt{update (select ...) = ...} \\
also exists, but it is very rare that you want to use this instead of
simple where clauses; we couldn't come up with a counterexample in
this schema.

\end{slist}
\end{slide}

\end{document}

