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