Skip to content

HTTPS clone URL

Subversion checkout URL

You can clone with
or
.
Download ZIP
Branch: master
Fetching contributors…

Cannot retrieve contributors at this time

653 lines (573 sloc) 14.059 kB
\documentclass[20pt]{beamer}
\usepackage[orientation=landscape,size=custom,width=16,height=9,scale=0.5]{beamerposter}
% http://en.wikibooks.org/wiki/LaTeX/Packages/Listings
\usepackage{listings}
\usetheme{postgres}
\setbeamertemplate{items}[circle]
\title{Introduction to Databases with PostgreSQL}
\author{Gabrielle Roth}
\institute{PDXPUG \& FreeGeek}
\date{Feb 1, 2011}
\begin{document}
\frame{
\titlepage
}
\setbeamertemplate{footline}[postgres body]
\frame
{
\frametitle{Outline}
\begin{itemize}
\item[] Intro to Databases (discussion)
\item[] PostgreSQL (discussion)
\item[] psql (hands-on)
\item[] SQL (hands-on)
\item[] $\ldots$break somewhere in here, 7:30\ldots
\item[] Practice db + more SQL (hands-on)
\item[] Basic Admin + GUI Tools (discussion)
\item[] Questions
\end{itemize}
}
\frame[containsverbatim]
{
\frametitle{Introductions}
\begin{verbatim}
__ __
/ \~~~/ \ . o O ( Hi! )
,----( oo )
/ \__ __/
/| (\ |(
^ \ /___\ /\ |
|__| |__|-"
\end{verbatim}
\tiny
\begin{center}
Thanks to Hayley J Wakenshaw for the Elephant
\end{center}
}
\frame
{
\frametitle{Databases!}
\begin{center}
What is it?\\
Why do I want one?
\end{center}
}
\frame
{
\frametitle{Relational databases}
\begin{itemize}
\item[] Based on Relational Calculus
\item[] Data is stored in "relations"
\item[] "Normalized"
\end{itemize}
}
\frame
{
\frametitle{PostgreSQL}
\begin{itemize}
\item[-] How do you say that?
\item[-] It's the database *server*
\item[-] We think it's the best.
\end{itemize}
}
\frame[containsverbatim]
{
\frametitle{Get connected.}
\lstset{language=sh}
\begin{lstlisting}
chmod 600 /path/to/id_pdxpug
ssh -i /path/to/id_pdxpug pdxpug@207.173.203.228
psql -U [username] -d pdxpug
\end{lstlisting}
}
\frame[containsverbatim]
{
\frametitle{Pg command-line interface}
\begin{itemize}
\item[-] psql
\item[-] \verb!--help!
\item[-] psql -U [username] -d pdxpug
\item[-] \textbackslash h
\item[-] \textbackslash ?
\item[-] other useful \textbackslash commands
\end{itemize}
}
\frame[containsverbatim]
{
\frametitle{This looks like a good place to talk about\ldots}
\begin{verbatim}
__ __
/ \~~~/ \ . o O ( Schemas! )
,----( oo )
/ \__ __/
/| (\ |(
^ \ /___\ /\ |
|__| |__|-"
\end{verbatim}
}
\frame
{
\frametitle{SQL}
\begin{itemize}
\item[-] Declarative programming language
\item[-] $\ldots$let's do "Hello World".
\end{itemize}
}
\frame[containsverbatim]
{
\frametitle{Hello, World.}
\lstset{language=SQL}
\begin{lstlisting}
SELECT 'Hello, World.';
\end{lstlisting}
$\ldots$oh yeah, and there's command-completion, too.
}
\frame[containsverbatim]
{
\frametitle{CREATE TABLE}
\lstset{language=SQL}
\begin{lstlisting}
CREATE TABLE animals
(name varchar(32) primary key,
skin varchar(32),
legs int);
\end{lstlisting}
}
\frame[containsverbatim]
{
\frametitle{INSERT}
\lstset{language=SQL}
\begin{lstlisting}
INSERT INTO animals
VALUES ('cat', 'fur', '4');
SELECT * FROM animals;
\end{lstlisting}
$\ldots$and a word about syntax\ldots
}
\frame[containsverbatim]
{
\frametitle{What happens if\ldots}
\lstset{language=SQL}
\begin{lstlisting}
INSERT INTO animals
VALUES
('centipede', 'chitin', 'too many');
\end{lstlisting}
}
\frame[containsverbatim]
{
\frametitle{multi-valued INSERT}
\lstset{language=SQL}
\begin{lstlisting}
INSERT INTO animals
VALUES
('dog', 'fur', 4),
('bird', 'feathers', 2),
('centipede', 'chitin', 100),
('snake', 'scales', 0);
\end{lstlisting}
}
\frame[containsverbatim]
{
\frametitle{SELECT}
\lstset{language=SQL}
\begin{lstlisting}
SELECT * FROM animals;
SELECT name, legs FROM animals;
ORDER BY name;
SELECT name, legs FROM animals;
ORDER BY legs DESC;
SELECT count(*) FROM animals;
SELECT SUM(legs) FROM animals;
SELECT * FROM animals WHERE name LIKE '%pede';
\end{lstlisting}
}
\frame[containsverbatim]
{
\frametitle{UPDATE}
\lstset{language=SQL}
\begin{lstlisting}
UPDATE animals
SET name = 'kitty' WHERE name = 'cat';
SELECT * FROM animals;
\end{lstlisting}
}
\frame[containsverbatim]
{
\frametitle{What happens if\ldots}
\lstset{language=SQL}
\begin{lstlisting}
UPDATE animals
SET legs = 18;
SELECT * FROM animals;
\end{lstlisting}
$\ldots$oops!
}
\frame[containsverbatim]
{
\frametitle{Transactions}
\lstset{language=SQL}
\begin{lstlisting}
BEGIN;
-- do your stuff...
-- check your stuff with a SELECT...
ROLLBACK or COMMIT as desired
\end{lstlisting}
}
\frame[containsverbatim]
{
\frametitle{DELETE}
\lstset{language=SQL}
\begin{lstlisting}
BEGIN;
DELETE FROM animals WHERE legs=2;
SELECT * FROM animals;
-- [ROLLBACK or COMMIT]
SELECT * FROM animals;
\end{lstlisting}
}
\frame
{
\frametitle{Exercises}
\begin{itemize}
\item[-] add an animal of your choice
\item[-] update its name
\item[-] delete all animals with four legs
\end{itemize}
}
\frame
{
\begin{center}
MOAR TABLEZ\\
\url{www.postgresqlguide.com/postgresql-sample-database.aspx}
\end{center}
}
\frame
{
\frametitle{ERDs}
\begin{itemize}
\item[-] Entity-Relationship Diagrams
\item[-] Graphical representation of the relations
\item[-] $\ldots$and how they're connected (JOINed)
\end{itemize}
}
\frame[containsverbatim]
{
\begin{verbatim}
__ __
/ \~~~/ \ . o O ( Let's look at one. )
,----( oo )
/ \__ __/
/| (\ |(
^ \ /___\ /\ |
|__| |__|-"
\end{verbatim}
}
\frame[containsverbatim]
{
\frametitle{Load 'er up!}
\lstset{language=SQL}
\begin{lstlisting}
\i /home/pdxpug/create_tables.sql
\i /home/pdxpug/populate_tables.sql
\end{lstlisting}
}
\frame
{
\frametitle{Working with the sample db}
\begin{itemize}
\item[-] \textbackslash d
\item[-] what are these \_seq relations?
\item[-] \textbackslash d item
\item[-] \textbackslash s
\item[-] \textbackslash e
\end{itemize}
}
\frame[containsverbatim]
{
\frametitle{DISTINCT}
\lstset{language=SQL}
\begin{lstlisting}
SELECT fname, lname FROM customer
SELECT count(lname) FROM customer;
SELECT DISTINCT lname FROM customer;
SELECT count(DISTINCT(lname)) FROM customer;
\end{lstlisting}
}
\frame
{
\frametitle{Feel the Power}
\begin{itemize}
\item[-] JOINs
\item[-] PRIMARY keys
\item[-] FOREIGN keys
\end{itemize}
}
\frame[containsverbatim]
{
\frametitle{Why we need JOINs}
\lstset{language=SQL}
\begin{lstlisting}
SELECT * FROM item;
SELECT * FROM stock;
\end{lstlisting}
\ldots and add them together ourselves? Nah. Let the database do its job.
}
\frame
{
\frametitle{So this database walks into a bar\ldots}
\begin{center}
Whiteboard!
INNER vs LEFT (OUTER) vs RIGHT (OUTER) vs CARTESIAN
\end{center}
}
\frame[containsverbatim]
{
\frametitle{$\ldots$walks up to two tables and says \ldots}
Compare INNER with LEFT JOIN:
\lstset{language=SQL}
\begin{lstlisting}
SELECT i.description, s.quantity
FROM item i
JOIN stock s ON i.item_id = s.item_id;
SELECT i.description, s.quantity
FROM item i
LEFT JOIN stock s ON i.item_id = s.item_id;
\end{lstlisting}
\small
tip: \\pset null '[null]'
$\ldots$makes the differences more obvious.
}
\frame[containsverbatim]
{
\frametitle{\ldots "Mind if I JOIN you?"}
Look what you can do with the table order:
\lstset{language=SQL}
\begin{lstlisting}
SELECT i.description, s.quantity
FROM item i
RIGHT JOIN stock s ON i.item_id = s.item_id;
SELECT i.description, s.quantity
FROM stock s
LEFT JOIN item i ON i.item_id = s.item_id;
\end{lstlisting}
}
\frame[containsverbatim]
{
\frametitle{$\ldots$and now the dreaded Cartesian JOIN}
\lstset{language=SQL}
\begin{lstlisting}
SELECT * FROM item;
SELECT * FROM stock;
SELECT i.description, s.quantity
FROM item i, stock s;
SELECT i.description, s.quantity
FROM item i
CROSS JOIN stock s;
\end{lstlisting}
}
\frame
{
\frametitle{Exercises: JOINs}
\begin{enumerate}
\item How many items are currently in stock?
\item In which towns do we have customers?
\item What are the barcodes for each item?
\item Experiment with RIGHT JOIN with the original sample queries.
\end{enumerate}
}
\frame[containsverbatim]
{
\frametitle{Building more complex queries: subSELECTs}
\lstset{language=SQL}
\begin{lstlisting}
SELECT date_placed FROM orderinfo
WHERE customer_id IN
(SELECT customer_id FROM customer
WHERE lname = 'Matthew');
SELECT date_placed FROM orderinfo
WHERE customer_id IN
(SELECT customer_id FROM customer
WHERE (fname, lname) = ('Alex','Matthew'));
\end{lstlisting}
}
\frame
{
\frametitle{Exercises - subSELECTs}
\begin{enumerate}
\setcounter{enumi}{4}
\item How much was shipping on each order?
\item What is the barcode for the "Toothbrush" item(s)?
\end{enumerate}
}
\frame[containsverbatim]
{
\frametitle{VIEWs and TEMP TABLEs}
\lstset{language=SQL}
\begin{lstlisting}
CREATE VIEW my_view AS
SELECT field FROM table;
\end{lstlisting}
vs.
\begin{lstlisting}
CREATE TEMP TABLE my_temp_table AS
SELECT field FROM table;
\end{lstlisting}
}
\frame[containsverbatim]
{
\frametitle{VIEWs and TEMP TABLEs}
\lstset{language=SQL}
\begin{lstlisting}
CREATE VIEW view_in_stock AS
SELECT i.item_id, i.description, s.quantity
FROM item i
LEFT JOIN stock s ON i.item_id=s.item_id;
SELECT * FROM view_in_stock;
\end{lstlisting}
\small
try: \\pset null '0'
}
\frame
{
\frametitle{Exercises - VIEWs, TEMP TABLEs, and bonus questions}
\begin{enumerate}
\setcounter{enumi}{6}
\item How much was the total shipping per person?
\item What items were ordered by people with the last name "stones"?
\item What is the name + total "our cost" value of each item currently in stock?
\item What is the name + total "selling cost" value of each item currently in stock?
\item What are the total "our cost" and "sell cost" values of all items in stock?
\end{enumerate}
}
\frame
{
\frametitle{Exercises}
\begin{enumerate}
\small
\item How many items are currently in stock?
\item In which towns do we have customers?
\item What are the barcodes for each item?
\item Experiment with RIGHT JOIN with the original sample queries.
\item How much was shipping on each order?
\item What is the barcode for the "Toothbrush" item(s)?
\item How much was the total shipping per person?
\item What items were ordered by people with the last name "stones"?
\item What is the name + total "our cost" value of each item currently in stock?
\item What is the name + total "selling cost" value of each item currently in stock?
\item What are the total "our cost" and "sell cost" values of all items in stock?
\end{enumerate}
}
\frame
{
\frametitle{Basic Admin}
\begin{itemize}
\item[-] initdb
\item[-] PGDATA
\item[-] postgresql.conf
\item[-] pg\_hba.conf
\item[-] stop/start/restart/reload
\end{itemize}
}
\frame
{
\frametitle{Basic Admin}
\begin{itemize}
\item[-] SELECT version();
\item[-] CREATE ROLE
\item[-] backups and upgrades
\begin{itemize}
\item[-] pg\_dump, pg\_dumpall, pg\_upgrade
\item[-] $\ldots$or replication
\end{itemize}
\item[-] logs
\item[-] TODO: Memory calculation? (Selena)
\end{itemize}
}
\frame
{
\frametitle{GUI Tools}
\begin{itemize}
\item[-] pHpPgAdmin
\item[-] pgAdminIII
\end{itemize}
}
\frame[containsverbatim]
{
\frametitle{Extras}
\begin{itemize}
\item[-] Natural keys vs Surrogate keys
\item[-] Indexes
\item[-] even more \textbackslash commands
\item[-] permissions
\end{itemize}
\lstset{language=SQL}
\begin{lstlisting}
GRANT USAGE ON SCHEMA [schema] TO [otheruser];
GRANT SELECT ON [table] TO [otheruser];
\end{lstlisting}
}
\frame[containsverbatim]
{
\frametitle{Book giveaway! Thank you, O'Reilly!}
\lstset{language=SQL}
\begin{lstlisting}
SELECT (random() * 100);
\end{lstlisting}
}
\frame
{
\frametitle{Where to get help - online}
\begin{itemize}
\item[] Postgres Community
\begin{itemize}
\item[-] \url{www.postgresql.org/community/lists/}
\item[-] \url{archives.postgresql.org/pgsql-novice/}
\item[-] \#postgresql
\end{itemize}
\item[] PDXPUG
\begin{itemize}
\item[-] \url{archives.postgresql.org/pdxpug/}
\item[-] \#pdxpug
\end{itemize}
\end{itemize}
}
\frame
{
\frametitle{PDXPUG}
\begin{itemize}
\item[] PDXPUG
\begin{itemize}
\item[-] Third Thurs, 7pm, FreeGeek
\item[-] \url{pugs.postgresql.org/pdx}
\item[-] @pdxpug
\end{itemize}
\end{itemize}
}
\frame
{
\frametitle{Reading List}
\begin{itemize}
\item[] \underline{Manga Guide to Databases} Takahashi, M
\item[] \underline{Database Design for Mere Mortals} Hernandez, M J
\item[] \underline{SQL for Smarties} Celko, J
\item[] \underline{Introduction to Database Systems} Date, CJ
\item[] \underline{Relational Model for Database Management} Codd, EF
\item[] \url{www.postgresql.org/docs}
\item[] \underline{PostgreSQL 9 Admin Cookbook} Riggs, S \& H Krosing
\item[] \underline{PostgreSQL 9.0 High Performance} Smith, G
\end{itemize}
}
\frame[containsverbatim]
{
\begin{verbatim}
__ __
/ \~~~/ \ . o O ( Thank you! )
,----( oo )
/ \__ __/
/| (\ |(
^ \ /___\ /\ |
|__| |__|-"
\end{verbatim}
}
\end{document}
Jump to Line
Something went wrong with that request. Please try again.