Permalink
Find file
Fetching contributors…
Cannot retrieve contributors at this time
62 lines (47 sloc) 2.46 KB
\chapter{Exercise 5: Selecting Data}
Out of the CRUD matrix you only know "Create". You can create tables and you
can create rows in those tables. I'll now show you how to "Read" or in the
case of SQL, \ident{SELECT}:
\begin{code}{ex5.sql}
<< d['code/ex5.sql|pyg|l'] >>
\end{code}
Here's what each of these lines does:
\begin{description}
\item[ex5.sql:1] This says "select all columns from person and return all rows."
The format for \ident{SELECT} is \verb|SELECT what FROM tables(s) WHERE (tests)|
and the \ident{WHERE} clause is optional. The '*' (asterisk) character is
what says you want all columns.
\item[ex5.sql:3] In this one I'm only asking for two columns \ident{name} and
\ident{age} from the \ident{pet} table. It will return all rows.
\item[ex5.sql:5] Now I'm looking for the same columns from the \ident{pet} table
but I'm asking for \emph{only} the rows where \verb|dead = 0|. This gives
me all the pets that are alive.
\item[ex5.sql:7] Finally I'm selecting all columns from \ident{person} just
like in the first line, but now I'm saying only if they do \emph{not}
equal "Zed". That \ident{WHERE} clause is what determines which rows
to return or not.
\end{description}
\section{What You Should See}
When you run this with \program{sqlite3 -echo} you should get something like
the following output:
\begin{code}{ex5.sql Output}
<< d['code/ex5.sh-session|pyg|l'] >>
\end{code}
I say "something like" because if you were doing the extra credit this
whole time you will have different rows in your database. For example,
if you added yourself then you will have some rows listed at the end. In
my example above I have nothing returned for the last query because I'm
the only person in the \ident{person} table, and that means no row
match the last query's \ident{WHERE} clause. Study this carefully.
\section{Extra Credit}
\begin{enumerate}
\item Write a query that finds all pets older than 10 years.
\item Write a query to find all people younger than you. Do one that's older.
\item Write a query that uses more than one test in the \ident{WHERE} clause using
the AND to write it. For example, \verb|WHERE first_name = "Zed" AND age > 30|.
\item Do another query that searches for rows using 3 columns and uses both AND
and OR operators.
\end{enumerate}
\section{Portability Notes}
Some databases have additional operators and boolean logic tests, but just
stick to the regular ones that you find in most programming languages for now.