forked from zedz/lsqlthw-cn
/
ex1.tex
59 lines (45 loc) · 2.33 KB
/
ex1.tex
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
\chapter{Exercise 1: Creating Tables}
In the introduction I said that you can do "Create Read Update Delete" operations to the data
inside tables. How do you make the tables in the first place? By doing CRUD on the database
\emph{schema}, and the first SQL statement to learn is \ident{CREATE}:
\begin{code}{ex1.sql}
<< d['code/ex1.sql|pyg|l'] >>
\end{code}
You could put this all on one line, but I want to talk about each line
so it's on multiple ones. Here's what each line does:
\begin{description}
\item[ex1.sql:1] The start of the "CREATE TABLE" which gives the name of the
table as \ident{person}. You then put the fields you want inside parenthesis
after this setup.
\item[ex1.sql:2] An \ident{id} column which will be used to exactly identify
each row. The format of a column is \ident{NAME TYPE}, and in this case
I'm saying I want an \ident{INTEGER} that is also a \ident{PRIMARY KEY}.
Doing this tells SQLite3 to treat this column special.
\item[ex1.sql:3-4] A \ident{first\_name} and a \ident{last\_name} column
which are both of type \ident{TEXT}.
\item[ex1.sql:5] An \ident{age} column that is just a plain \ident{INTEGER}.
\item[ex1.sql:6] Ending of the list of columns with a closing parenthesis and
then a semi-colon ';' character.
\end{description}
\section{What You Should See}
The easiest way to run this is to simply do: \verb|sqlite3 ex1.db < ex1.sql| and
it should just exit and not show you anything. To make sure it created a
database use \verb|ls -l|:
\begin{code}{ex1.sql Output}
<< d['code/ex1.sh-session|pyg|l'] >>
\end{code}
\section{Extra Credit}
\begin{enumerate}
\item SQL is \emph{mostly} a case-insensitive language. It was created in an
era when case sensitivity was perceived as a major usability problem, so
it has this quirk which can anoy the hell out of programmers from other
languages. Rewrite this so that it's all lowercase and see if it still
works. You'll need to delete ex1.db.
\item Add other \ident{INTEGER} and \ident{TEXT} fields for other things
a person might have.
\end{enumerate}
\section{Portability Notes}
The types used by SQLite3 are usually the same as other databases, but be
careful as one of the ways SQL database vendors differentiated themselves was
to "embrace and extend" certain data types. The worst of these is anything
to do with date and time.