Difference between revisions of "PostgreSQL Tutorial"

From LMU BioDB 2013
Jump to: navigation, search
(Quote substitutions.)
(Final cleanup, streamlining.)
 
(9 intermediate revisions by one user not shown)
Line 141: Line 141:
 
This time, you should see a fully-populated '''Data Output''' tab, with...17,770 rows.
 
This time, you should see a fully-populated '''Data Output''' tab, with...17,770 rows.
  
−
Time to play!
+
== Walkthrough: Adding a Few More Tables ==
  
−
== An Introduction to SQL ==
+
To make our database a little more interesting, will create a couple more tables and load them with some data.  The rest of the tutorial will be based on these tables and their content.
  
−
Database activities are triggered via ''SQL'' commands.  The previous ''SQL'' PDF handout gives you more of a reference/nutshell view of SQL; this page walks you through some commands step-by-step, using the kerfuffle database that has been preloaded into the Keck lab for you.  Then, you’ll practice making your own tables within that database.
+
At this stage, you’ve already seen these commands in some form, so we’ll skip the explanation and go right to the commands.  Copy, paste, and run this (you successfully copied and pasted 17,770 ''insert'' commands previously, so this should be no sweat!):
  
−
=== Select Queries ===
+
create table member (id int primary key, name varchar);
 +
create table rating (movie int references movie(id), member int references member(id), rating int);
 +
insert into member(id, name) values (6, 'Natalie');
 +
insert into member(id, name) values (8, 'Boomer');
 +
insert into member(id, name) values (42, 'Doug');
 +
insert into rating(movie, member, rating) values(209, 6, 5);
 +
insert into rating(movie, member, rating) values(2040, 6, 5);
 +
insert into rating(movie, member, rating) values(6908, 6, 2);
 +
insert into rating(movie, member, rating) values(2610, 6, 1);
 +
insert into rating(movie, member, rating) values(8809, 6, 2);
 +
insert into rating(movie, member, rating) values(37, 6, 4);
 +
insert into rating(movie, member, rating) values(113, 6, 2);
 +
insert into rating(movie, member, rating) values(8687, 8, 1);
 +
insert into rating(movie, member, rating) values(9628, 8, 5);
 +
insert into rating(movie, member, rating) values(10877, 8, 2);
 +
insert into rating(movie, member, rating) values(12513, 8, 1);
 +
insert into rating(movie, member, rating) values(15923, 8, 2);
 +
insert into rating(movie, member, rating) values(15532, 8, 3);
 +
insert into rating(movie, member, rating) values(4006, 8, 3);
 +
insert into rating(movie, member, rating) values (30, 42, 4);
 +
insert into rating(movie, member, rating) values (113, 42, 5);
 +
insert into rating(movie, member, rating) values (37, 42, 5);
 +
insert into rating(movie, member, rating) values (4765, 42, 5);
 +
insert into rating(movie, member, rating) values (6762, 42, 3);
 +
insert into rating(movie, member, rating) values (6853, 42, 4);
 +
insert into rating(movie, member, rating) values (10176, 42, 4);
 +
insert into rating(movie, member, rating) values (13847, 42, 5);
 +
insert into rating(movie, member, rating) values (15127, 42, 3);
 +
insert into rating(movie, member, rating) values (15532, 42, 1);
  
−
The ''R'' in CRUD, “retrieve,” is, somewhat inconsistently, performed by the ''select'' command. The ''select'' command is the SQL “kitchen sink” for retrieving information from a database.  It’s general, basic form is:
+
You should see the customary success messages once the commands are done.  Time to play!
 +
 
 +
== An Introduction to SQL Select Queries ==
 +
 
 +
Database activities are triggered via ''SQL'' commands.  The previous ''SQL'' PDF handout gives you more of a reference/nutshell view of SQL; this page walks you through some commands step-by-step, using the sample movie database that you set up earlier in this page.
 +
 
 +
The ''select'' command is the SQL “kitchen sink” for retrieving information from a database.  Its general, basic form is:
  
 
  select '''''columns''''' from '''''tables''''' where '''''conditions'''''
 
  select '''''columns''''' from '''''tables''''' where '''''conditions'''''
Line 155: Line 189:
 
As you will see, ''select'' can do even more, but let’s start simple.
 
As you will see, ''select'' can do even more, but let’s start simple.
  
−
==== Basics ====
+
=== Basics ===
  
 
The simplest type of ''select'' queries involve getting records from an individual table based on relatively simple conditions.  For example:
 
The simplest type of ''select'' queries involve getting records from an individual table based on relatively simple conditions.  For example:
Line 166: Line 200:
  
 
  select title from movie where title like '%Vampire%';
 
  select title from movie where title like '%Vampire%';
−
 
−
'''psql'''’s data display works a lot like '''more''': if there’s more data than can fit in the command window, '''psql''' will pause and recognize the following keys:
 
−
* '''Enter''' moves through the data one line at a time
 
−
* The '''space bar''' moves through the data one window at a time
 
−
* '''q''' exits the data display
 
  
 
If you want ''select'' to display ''all'' columns of a database record, use the asterisk ('''*'''):
 
If you want ''select'' to display ''all'' columns of a database record, use the asterisk ('''*'''):
  
−
  select * from movie where year like '196%';
+
  select * from movie where title like '19%';
  
−
This will display ''every'' column/field of movie records whose year starts with “196” — effectively, all movies released in the 1960s.
+
This will display ''every'' column/field of movie records whose title starts with “19”—these mostly appear to be films pertaining to a specific year in the 20th century.
  
 
Conditions can be combined via ''and'' and ''or''; for example, retrieving movies whose title contains ''either'' “DNA” or “Bio” can be done with:
 
Conditions can be combined via ''and'' and ''or''; for example, retrieving movies whose title contains ''either'' “DNA” or “Bio” can be done with:
Line 184: Line 213:
 
When using more than two conditions, watch out for how conditions are grouped together; this query, for example, may yield unexpected results:
 
When using more than two conditions, watch out for how conditions are grouped together; this query, for example, may yield unexpected results:
  
−
  select * from movie where title like '%DNA%' or title like '%Bio%' and year like '200%';
+
  select * from movie where title like '%DNA%' or title like '%Bio%' and title like '%:%';
  
−
At face value, the above query may read like “movies whose titles have either DNA or Bio which were released on or after 2000;” in reality, the ''year'' criterion is only ''and''-ed with movies that have “Bio” in the title.  Thus, the database actually interprets this query as “movies whose titles have DNA, ''or'' whose titles have Bio which were released on or after 2000.”
+
At face value, the above query may read like “movies whose titles have either DNA or Bio which also have a colon (''':''');” in reality, the colon criterion is only ''and''-ed with movies that have “Bio” in the title.  Thus, the database actually interprets this query as “movies whose titles have DNA, ''or'' whose titles have Bio and a colon.”
  
 
To eliminate any ambiguities, use parentheses to group conditions together:
 
To eliminate any ambiguities, use parentheses to group conditions together:
  
−
  select * from movie where (title like '%DNA%' or title like '%Bio%') and year like '200%';
+
  select * from movie where (title like '%DNA%' or title like '%Bio%') and title like '%:%';
  
−
These parentheses force the database to pick out movies with either title first, and ''then'' check if these movies have years beginning with “200.”
+
These parentheses force the database to pick out movies with either title first, and ''then'' check if these movies have a colon in their titles.
  
 
The ''like'' comparator can do simple text matches, but it does ''not'' use regular expressions (i.e., the search patterns recognized by '''grep''' and '''sed''').  This area is a little shaky in SQL-land; there is an official ''similar to'' comparator which is the official way to make regular expression comparisons, but the format for those expressions is not the same as the format used by '''grep''' and '''sed'''.
 
The ''like'' comparator can do simple text matches, but it does ''not'' use regular expressions (i.e., the search patterns recognized by '''grep''' and '''sed''').  This area is a little shaky in SQL-land; there is an official ''similar to'' comparator which is the official way to make regular expression comparisons, but the format for those expressions is not the same as the format used by '''grep''' and '''sed'''.
Line 204: Line 233:
 
The caveat here is that '''~''' is a PostgreSQL-specific feature: if you move to other database systems (such as Microsoft Access), that feature may either be done differently or missing completely, since it is not part of the official SQL standard.
 
The caveat here is that '''~''' is a PostgreSQL-specific feature: if you move to other database systems (such as Microsoft Access), that feature may either be done differently or missing completely, since it is not part of the official SQL standard.
  
−
==== Sorting ====
+
Numeric values, such as the ''year'' column, can be compared using ''='', ''<'', ''>'', ''<='' (greater than or equal to), ''>='' (less than or equal to), and ''<>'' (not equal):
 +
 
 +
select * from movie where year = 1960;
 +
select * from movie where year < 1930;
 +
select * from movie where year >= 1960 and year < 1970;
 +
 
 +
=== Sorting ===
  
 
As you play with various queries on the ''movie'' table, you’ve probably noticed that results are returned in no particular order; if you’d like to sort the results in some way, tack on an ''order by'' clause at the end of the ''select'' query:
 
As you play with various queries on the ''movie'' table, you’ve probably noticed that results are returned in no particular order; if you’d like to sort the results in some way, tack on an ''order by'' clause at the end of the ''select'' query:
  
−
  select * from movie where year like '200%' order by year;
+
  select * from movie where year > 2000 order by year
  
−
This will display the records/rows for movies released from 2000 to 2009, sorted by year.  You can add more fields for a very specific sort order:
+
This will display the records/rows for movies released from 2001 onward, sorted by year.  You can add more fields for a very specific sort order:
  
−
  select * from movie where year like '200%' order by year, title;
+
  select * from movie where year > 2000 order by year, title
  
 
The above query returns the same records, but this time sorted by year first, then by title ''within'' each year.
 
The above query returns the same records, but this time sorted by year first, then by title ''within'' each year.
Line 218: Line 253:
 
Sort order is ascending by default (e.g., A to Z, 0 to 9); for the reverse order, add ''desc'' to the field(s) that you’d like to see sorted in reverse:
 
Sort order is ascending by default (e.g., A to Z, 0 to 9); for the reverse order, add ''desc'' to the field(s) that you’d like to see sorted in reverse:
  
−
  select * from movie where year like '200%' order by year desc, title;
+
  select * from movie where year > 2000 order by year desc, title;
  
 
This will display records with years displayed most recent first; within each year, however, titles will still be sorted in ascending order.
 
This will display records with years displayed most recent first; within each year, however, titles will still be sorted in ascending order.
  
−
==== Aggregate Queries ====
+
=== Aggregate Queries ===
  
 
For large databases, SQL provides ''aggregate'' (a.k.a. “grouping”) queries that summarize multiple records in different ways.
 
For large databases, SQL provides ''aggregate'' (a.k.a. “grouping”) queries that summarize multiple records in different ways.
Line 228: Line 263:
 
The simplest form of summary is counting: ''how many'' records were retrieved?  A simple overall count is done by using ''count(*)'' as the thing to select (or ''project'', in formal relational algebraic terms):
 
The simplest form of summary is counting: ''how many'' records were retrieved?  A simple overall count is done by using ''count(*)'' as the thing to select (or ''project'', in formal relational algebraic terms):
  
−
  select count(*) from movie where year like '200%';
+
  select count(*) from movie where year > 2000
 +
 
 +
This will display the number of movies released from 2001 onward.
  
−
This will display the number of movies whose release years start with “200.”
+
Of course, you can mix and match everything you have learned so far.  To count the movies whose titles begin with a “B” that were released in 1975, you can query:
  
−
=== Creating Tables ===
+
select count(*) from movie where title ~ '^B' and year = 1975
  
−
Now that you’ve logged some time with ''retrieving'' data, you’re probably aware that someone must have ''put'' the data there in the first place.  First, the table(s) holding the data must be set up, or ''created'' &mdash; this is the ''C'' in CRUD.  Half of table creation actually occurs without a computer: you (or the database designer) must determine the ''schema'' for the table independently of the database.  This schema consists of the fields in the table, the kind of data each field will hold (numbers, text, dates, etc.), and any primary or foreign keys in the table.  Ideally, you should have a complete relational schema diagram, such as the one shown in the handout or worked on previously in class.
+
Aggregators other than ''count'' are available, such as ''min'', ''max'', and ''avg'' (average or mean).  Though somewhat odd-sounding, you can ask, for example, for the “average year” of movies with “London” in the title:
  
−
With this table schema, creating the table in SQL is a matter of converting the diagram into a ''create table'' command:
+
select average(year) from movie where title like '%London%'
  
−
create table <tablename> (<columns, their data types, and the primary key, separated by commas>);
+
Finally, you can aggregate ''multiple groups'' of data, so that you get different sets of statistics.  The ''group by'' keyword does this:
  
−
If you’re still connected to the ''kerfuffle'' database, disconnect via '''\q''', then run '''psql''' again, this time all by itself.  This should connect you to your individual database.
+
select year, count(*) from movie where year < 1935 group by year
  
−
Suppose you’ve designed a ''person'' table that looks like this:
+
The main rule with ''group by'' is that the column being grouped should also be part of the ''select'' clause.  This makes sense because otherwise, you wouldn’t be able to tell which group was which!  And of course, you can mix and match.  For example, the query above makes more sense if we arrange the data chronologically:
  
−
:{| style="width: 10em; border: solid 1px"
+
select year, count(*) from movie where year < 1935 group by year order by year
−
| style="background: lightgray; padding: 1ex" | id
+
−
|-
+
−
| style="padding: 1ex" | firstname
+
−
|-
+
−
| style="padding-left: 1ex; padding-right: 1ex" | lastname
+
−
|-
+
−
| style="padding: 1ex" | dob
+
−
|}
+
  
−
...where ''id'' should be an integer, ''firstname'' and ''lastname'' can be any text, and ''dob'' is some date.  In SQL, an “integer” is indicated by ''int'', text is indicated by ''varchar'', and a date is indicated by (for once) ''date''.  Further, based on the shading, the primary key of this table is ''id''.  Putting this all together yields the following commands:
+
See any trends in that data?
  
−
create table person (id int primary key, firstname varchar, lastname varchar, dob date);
+
=== Joins ===
  
−
Once done, you will now have an empty table called ''person'', showing the columns stated above.
+
Thus far, we’ve only been working with one table in the database: ''movie''.  Our other tables, ''member'' and ''rating'', add some further interest.  These tables store ratings, from 1 to 5, made by individual members on movies they have seen.
  
−
=== Adding Data ===
+
Examination of the ''rating'' table reveals that it has ''movie'', ''member'', and ''rating''.  Thus, each record consists of a single rating, made by a particular member for a particular movie.  Staying with one table for now, this query will list all of the ratings submitted by member no. 6:
  
−
Now that a table exists to ''receive'' data, the ''data itself'' has to be “handed” to the table. The SQL ''insert'' command accomplishes this:
+
  select * from rating where member = 6
  
−
<pre>insert into <table>(<columns>) values(<specific column values, typically enclosed in apostrophes>);</pre>
+
This returns the expected answer (based on the ''insert''s that you copy-pasted previously), but you may have noticed that the result isn’t quite as meaningful to us, since we get movie ''IDs'' back instead of ''titles''.  These movie titles, however, are in the ''movie'' table, not ''rating''.  We thus need to ''join'' the two tables.  As you might have seen when looking at the schema of the ''rating'' table, the ''movie'' field is a ''foreign key'' to the ''id'' field in the ''movie'' table.  Thus, every ''movie'' value in the ''rating'' table matches some ''id'' in the ''movie'' table, thus leading us to that movie’s title.
  
−
For example, in order to add a person named John Smith to the new person table, with numeric ID 1000 and a birthdate of June 30, 1980, you would perform:
+
An SQL ''join'' uses the same basic ''select'' command, but requires the tables being joined (in this case ''movie'' and ''rating''), as well as the fields/columns to use for “joining” records.  “Joining” records means that their fields are combined to create a new “virtual” record.  All other parts of the ''select'' command retain the same meanings as before:
  
−
<pre>insert into person(id, firstname, lastname, dob) values(1000, 'John', 'Smith', '6/30/1980');</pre>
+
select '''columns''' from '''table1''' inner join '''table2''' on ('''join condition''') where '''conditions'''
  
−
Note how this ''insert'' command adds one record at a time; adding records ''en masse'' in this manner is a matter of forming a bunch of ''insert'' commands and having '''psql''' perform them all, one at a time.
+
Thus, the previous query, modified to retrieve the titles and ratings of the movies rated by member no. 6, looks like this:
  
−
As much as you like, use the '''up''' arrow to retrieve, then edit the previous ''insert'' command so that it adds a new record.
+
select title, rating from movie inner join rating on (movie.id = rating.movie) where member = 6
  
−
Note that listing the columns of the table is not redundant; this listing allows you to change the order used for the succeeding values (e.g., ''lastname'' first, then ''firstname'', ''id'', and ''dob'').  Make sure to change the value of ''id'' to something that has not been added to the record yet &mdash; it has to be unique to the ''entire'' table since it has been designated as the primary key.
+
As in the basic ''select'' command, you can tailor the ''where'' conditions as you need to pull ratings based on other criteria.  For example, ratings for a particular movie, as opposed as for a particular member, can be retrieved by changing the ''where'' clause.  The query below displays all ratings for the movie(s) whose title has an apostrophe (see the last section below for more on this):
  
−
=== Deleting Data ===
+
select rating from movie inner join rating on (movie.id = rating.movie) where title like '%<nowiki>''</nowiki>%'
  
−
Use this last SQL command with caution, because it results in loss of data (which, in this one case, is what you may ''want'' to happen&mdash;but just this time):
+
Note that the ''member'' column of the ''rating'' table is a foreign key as well—it refers to the ''id'' column of the ''member'' table.  The ''member'' table in our sample only includes member names, but in practice it can hold much more information.  We can join more than once; extending the query above, we can now produce all ratings for the movie(s) whose title has an apostrophe, ''and'' list the member names who gave those ratings:
  
−
<pre>delete from <table name>;</pre>
+
select name, rating from movie inner join rating on (movie.id = rating.movie) inner join member on (member.id = rating.member) where title like '%<nowiki>''</nowiki>%'
  
−
This command empties the table of all records, allowing you to start fresh.  To delete records selectively, you may include a <code>where</code> clause just like the one used in <code>select</code>.
+
Note how this approach helps avoid redundancy: instead of copying a member’s name over and over again, we can store the member’s name in one place, and use their ID as a reference to that name.  This elimination of redundancy is called ''normalization''—tables are ''normalized'' if they eliminate data redundancy in their columns (except, of course, for foreign keys).
  
−
==== The Notorious Apostrophe ====
+
=== The Notorious Apostrophe ===
  
 
You might have noticed that, because the apostrophe or single quote is used to indicate specific values in SQL (e.g., 'The Godfather', 'Smith', '6/30/1980', etc.), we run into a potential problem when the value itself should contain an apostrophe.  This is not as uncommon as one might think; for example, a good number of movie titles have apostrophes (''By Dawn's Early Light'', ''Zatoichi's Conspiracy'', ''Logan's Run'', and ''Dead Men Don't Wear Plaid'', to name a few), as do many names (“O'Malley,” “M'Benga,” “D'Angelo”).  An ''insert'' command such as the one below will result in an error, since the apostrophe will be misinterpreted as ending a piece of text rather than as part of the text itself:
 
You might have noticed that, because the apostrophe or single quote is used to indicate specific values in SQL (e.g., 'The Godfather', 'Smith', '6/30/1980', etc.), we run into a potential problem when the value itself should contain an apostrophe.  This is not as uncommon as one might think; for example, a good number of movie titles have apostrophes (''By Dawn's Early Light'', ''Zatoichi's Conspiracy'', ''Logan's Run'', and ''Dead Men Don't Wear Plaid'', to name a few), as do many names (“O'Malley,” “M'Benga,” “D'Angelo”).  An ''insert'' command such as the one below will result in an error, since the apostrophe will be misinterpreted as ending a piece of text rather than as part of the text itself:

Latest revision as of 05:23, 26 September 2013

This page gives you a tutorial-style walkthrough for using PostgreSQL. The walkthrough assumes that you’ve been set up for PostgreSQL use within the Keck lab infrastructure.

But first, a little leadoff cartoon: http://xkcd.com/327

Contents

[edit] Running PostgreSQL on the Keck Lab Windows Machines

  1. Login to the computer as usual
  2. From the Start/Windows icon menu, launch pgAdmin III
  3. The pgAdmin III window starts with a hierarchical view on the left that starts with three layers:
    • Server Groups
      • Servers (1)
        • PostgreSQL 9.2 (localhost:5432)
  4. Double-click on PostgreSQL 9.2 (localhost:5432) to connect to the database server
  5. The password to start the server is simply keck

[edit] Creating a Database

Once the server is running, the red x disappears from the PostgreSQL 9.2 (localhost:5432) icon, and additional icons appear beneath it. If you click on the + button to the left of the Databases icon, you will see the databases that are currently available. Initially, you will see a single database called postgres.

To do your work and practice some SQL, it is recommended that you work on a database of your own. To create a database, right-click on the Databases icon and choose New Database... from the menu that appears. In the New Database dialog, the only information you need to supply is your new database's name. To avoid confusion in case multiple students use the same computer, use your Keck lab username as the name of your database.

When you click OK, you will return to the main pgAdmin III window and you should see your new database underneath the Databases icon.

Note that you only need to go through this creation process once; that database will remain available until it is explicitly deleted.

[edit] Connecting to a Database

To start using a database, click on its icon. The red x disappears from the database icon and you should now be able to work.

[edit] Walkthrough: Loading the Sample Movie Table Into Your Database

Before we can dive into SQL, we need to set up some information that we can access. In doing this, you will see how data in a plain text file can find its way into a full-fledged relational table.

We will load up the movie_titles.txt file in ~dondi/xmlpipedb/data into your own database. If you cat that file, you will see that it looks like this: 

17761,2003,Levity
17762,1997,Gattaca
17763,1978,Interiors
17764,1998,Shakespeare in Love
17765,1969,Godzilla's Revenge
17766,2002,Where the Wild Things Are and Other Maurice Sendak Stories
17767,2004,Fidel Castro: American Experience
17768,2000,Epoch
17769,2003,The Company
17770,2003,Alien Hunter

(this is from the end of the file)

Looking at the information, we recognize that this file consists of a movie ID, a year, and a title. Thus, we need to prepare a table with these columns in our database.

[edit] Create the Movie Table

Switching back to pgAdmin III, click the SQL button in the toolbar. A new window with an SQL Editor tab appears. The following command will create your movie table; type this into that tab: 

create table movie (id int primary key, year int, title varchar)

As always, watch out for typos! When ready, click on the Execute query button in the toolbar. (its button looks like a green play button)

Upon executing the query, the following should appear in the Messages tab of the Output Pane in the bottom half of the window: 

NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "movie_pkey" for table "movie"
Query returned successfully with no result in 101 ms.

To assure yourself that the movie table is indeed there, type and execute this query: 

select * from movie

The Data Output tab of the Output Pane should now show an empty tabular display with headings for id integer, year integer, and title character varying.

[edit] Prepare Data for Insertion into the Movie Table

At this point, you have a movie table, but no data: that information currently resides in movie_titles.txt over in the Keck lab's my.cs.lmu.edu server. How do we get that data into our movie table?

Recall that the SQL command for adding data has this format: 

insert into table (columns) values (values)

In other words, this text data: 

17761,2003,Levity
17762,1997,Gattaca
17763,1978,Interiors
17764,1998,Shakespeare in Love
17765,1969,Godzilla's Revenge
17766,2002,Where the Wild Things Are and Other Maurice Sendak Stories
17767,2004,Fidel Castro: American Experience
17768,2000,Epoch
17769,2003,The Company
17770,2003,Alien Hunter

...must be made to look like this: 

insert into movie(id, year, title) values (17761,2003,'Levity');
insert into movie(id, year, title) values (17762,1997,'Gattaca');
insert into movie(id, year, title) values (17763,1978,'Interiors');
insert into movie(id, year, title) values (17764,1998,'Shakespeare in Love');
insert into movie(id, year, title) values (17765,1969,'Godzillas Revenge');
insert into movie(id, year, title) values (17766,2002,'Where the Wild Things Are and Other Maurice Sendak Stories');
insert into movie(id, year, title) values (17767,2004,'Fidel Castro: American Experience');
insert into movie(id, year, title) values (17768,2000,'Epoch');
insert into movie(id, year, title) values (17769,2003,'The Company');
insert into movie(id, year, title) values (17770,2003,'Alien Hunter');

Note a few rules here that might not be very obvious:

  • Text values need to be enclosed between single quotes; numbers don't.
  • Note, in the case of movie 17765, Godzilla's Revenge, that the movie title itself contains a single quote (apostrophe). To distinguish an “in-text” apostrophe from a “wrapper” apostrophe, we double it up; that's why the SQL above shows Godzilla's Revenge with the apostrophe converted into two.
  • When performing multiple SQL queries, the semicolon (;) is used to distinguish one query from another. Think of the semicolon as playing the same role that periods (.) do in plain English sentences.

Hmmmmm...do we have a program that can do this? We need to take the lines in the movie_titles.txt file and convert them into valid SQL insert commands. Why yes we do, and you have already used it: sed.

Since this is a tutorial, we won't spend time to explain exactly how we come up with the sed command below. But you should be able to see the pieces:

  • We need to turn all single apostrophes into doubles.
  • We then need to “wrap” the titles at the end around single quotes.
  • Finally, we append the insert command before each line, and
  • end each line with a parenthesis and semicolon.

Finally, we somehow need to get the data into the PostgreSQL server running on your workstation. For this, we will use the built-in web server of the my.cs.lmu.edu host: we will deposit the results of sed into a file that a web browser can then display. From the web browser, we can copy and paste the insert statements into the pgAdmin III SQL window.

All that said (no pun intended), this is the command that you want (invoke it from ~dondi/xmlpipedb/data): 

cat movie_titles.txt | sed "s/'/''/g" | sed "s/,/,'/2" | sed "s/^/insert into movie(id,year,title) values(/g" | sed "s/$/');/g" > ~/public_html/movie.sql.txt

At this point, you can probably work out the sed commands. The portion we will explain just a little bit more is the last section, > ~/public_html/movie.sql.txt. We have not needed to use the > symbol before, but now it is just what we need: it “sends” the result of the prior sed commands into a file. That file is placed in your public_html folder, which, if you recall, is visible on the web as http://my.cs.lmu.edu/~your_username/.

[edit] Insert!

Finally, we can feed these 17,770 insert statements (quick, how did we know this?) into PostgreSQL. Open a new browser tab or window and go to http://my.cs.lmu.edu/~username/movie.sql.txt (remember to substitute username with your Keck lab ssh/PuTTY login. You should see your fresh sed product in the browser. From here, Select All and Copy the commands.

Switch to the pgAdmin III SQL window, empty out the SQL Editor tab, then Paste your insert statements into the tab. Finally, execute the query (the green play button, remember?) and let it work. With 17,770 records, this takes a little bit longer than prior commands that you have run.

In the end, the Messages tab in the Output Pane should say something like: 

Query returned successfully: one row affected, 2325 ms execution time.

(exact execution times will vary)

Once more, check your work; re-execute this: 

select * from movie

This time, you should see a fully-populated Data Output tab, with...17,770 rows.

[edit] Walkthrough: Adding a Few More Tables

To make our database a little more interesting, will create a couple more tables and load them with some data. The rest of the tutorial will be based on these tables and their content.

At this stage, you’ve already seen these commands in some form, so we’ll skip the explanation and go right to the commands. Copy, paste, and run this (you successfully copied and pasted 17,770 insert commands previously, so this should be no sweat!): 

create table member (id int primary key, name varchar);
create table rating (movie int references movie(id), member int references member(id), rating int);
insert into member(id, name) values (6, 'Natalie');
insert into member(id, name) values (8, 'Boomer');
insert into member(id, name) values (42, 'Doug');
insert into rating(movie, member, rating) values(209, 6, 5);
insert into rating(movie, member, rating) values(2040, 6, 5);
insert into rating(movie, member, rating) values(6908, 6, 2);
insert into rating(movie, member, rating) values(2610, 6, 1);
insert into rating(movie, member, rating) values(8809, 6, 2);
insert into rating(movie, member, rating) values(37, 6, 4);
insert into rating(movie, member, rating) values(113, 6, 2);
insert into rating(movie, member, rating) values(8687, 8, 1);
insert into rating(movie, member, rating) values(9628, 8, 5);
insert into rating(movie, member, rating) values(10877, 8, 2);
insert into rating(movie, member, rating) values(12513, 8, 1);
insert into rating(movie, member, rating) values(15923, 8, 2);
insert into rating(movie, member, rating) values(15532, 8, 3);
insert into rating(movie, member, rating) values(4006, 8, 3);
insert into rating(movie, member, rating) values (30, 42, 4);
insert into rating(movie, member, rating) values (113, 42, 5);
insert into rating(movie, member, rating) values (37, 42, 5);
insert into rating(movie, member, rating) values (4765, 42, 5);
insert into rating(movie, member, rating) values (6762, 42, 3);
insert into rating(movie, member, rating) values (6853, 42, 4);
insert into rating(movie, member, rating) values (10176, 42, 4);
insert into rating(movie, member, rating) values (13847, 42, 5);
insert into rating(movie, member, rating) values (15127, 42, 3);
insert into rating(movie, member, rating) values (15532, 42, 1);

You should see the customary success messages once the commands are done. Time to play!

[edit] An Introduction to SQL Select Queries

Database activities are triggered via SQL commands. The previous SQL PDF handout gives you more of a reference/nutshell view of SQL; this page walks you through some commands step-by-step, using the sample movie database that you set up earlier in this page.

The select command is the SQL “kitchen sink” for retrieving information from a database. Its general, basic form is: 

select columns from tables where conditions

As you will see, select can do even more, but let’s start simple.

[edit] Basics

The simplest type of select queries involve getting records from an individual table based on relatively simple conditions. For example: 

select year from movie where title = 'Metropolis';

...will retrieve the release years of movies whose titles are exactly “Metropolis.” If you try this query, you should see two rows: one for 2001 and another for 1927.

In addition to equality, like lends further flexibility. The like comparison allows for pattern matching, similar but not identical to grep. In SQL, the percent sign (“%”) is a “wildcard” that can represent any number of letters and symbols. like and % can be combined for broader queries, such as this one, which retrieves all movie titles that have the word “Vampire” in them: 

select title from movie where title like '%Vampire%';

If you want select to display all columns of a database record, use the asterisk (*): 

select * from movie where title like '19%';

This will display every column/field of movie records whose title starts with “19”—these mostly appear to be films pertaining to a specific year in the 20th century.

Conditions can be combined via and and or; for example, retrieving movies whose title contains either “DNA” or “Bio” can be done with: 

select * from movie where title like '%DNA%' or title like '%Bio%';

When using more than two conditions, watch out for how conditions are grouped together; this query, for example, may yield unexpected results: 

select * from movie where title like '%DNA%' or title like '%Bio%' and title like '%:%';

At face value, the above query may read like “movies whose titles have either DNA or Bio which also have a colon (:);” in reality, the colon criterion is only and-ed with movies that have “Bio” in the title. Thus, the database actually interprets this query as “movies whose titles have DNA, or whose titles have Bio and a colon.”

To eliminate any ambiguities, use parentheses to group conditions together: 

select * from movie where (title like '%DNA%' or title like '%Bio%') and title like '%:%';

These parentheses force the database to pick out movies with either title first, and then check if these movies have a colon in their titles.

The like comparator can do simple text matches, but it does not use regular expressions (i.e., the search patterns recognized by grep and sed). This area is a little shaky in SQL-land; there is an official similar to comparator which is the official way to make regular expression comparisons, but the format for those expressions is not the same as the format used by grep and sed.

Fortunately, PostgreSQL has a specific, PostgreSQL-only comparator that does match the same patterns used by grep and sed: the tilde (~). Comparing with ~ is equivalent to a grep- or sed-like comparison: 

select * from movie where title ~ 'Vamp[iy]re';
select * from movie where title ~ 'End$';
select * from movie where title ~ 'Colou?r';

The caveat here is that ~ is a PostgreSQL-specific feature: if you move to other database systems (such as Microsoft Access), that feature may either be done differently or missing completely, since it is not part of the official SQL standard.

Numeric values, such as the year column, can be compared using =, <, >, <= (greater than or equal to), >= (less than or equal to), and <> (not equal): 

select * from movie where year = 1960;
select * from movie where year < 1930;
select * from movie where year >= 1960 and year < 1970;

[edit] Sorting

As you play with various queries on the movie table, you’ve probably noticed that results are returned in no particular order; if you’d like to sort the results in some way, tack on an order by clause at the end of the select query: 

select * from movie where year > 2000 order by year

This will display the records/rows for movies released from 2001 onward, sorted by year. You can add more fields for a very specific sort order: 

select * from movie where year > 2000 order by year, title

The above query returns the same records, but this time sorted by year first, then by title within each year.

Sort order is ascending by default (e.g., A to Z, 0 to 9); for the reverse order, add desc to the field(s) that you’d like to see sorted in reverse: 

select * from movie where year > 2000 order by year desc, title;

This will display records with years displayed most recent first; within each year, however, titles will still be sorted in ascending order.

[edit] Aggregate Queries

For large databases, SQL provides aggregate (a.k.a. “grouping”) queries that summarize multiple records in different ways.

The simplest form of summary is counting: how many records were retrieved? A simple overall count is done by using count(*) as the thing to select (or project, in formal relational algebraic terms): 

select count(*) from movie where year > 2000

This will display the number of movies released from 2001 onward.

Of course, you can mix and match everything you have learned so far. To count the movies whose titles begin with a “B” that were released in 1975, you can query: 

select count(*) from movie where title ~ '^B' and year = 1975

Aggregators other than count are available, such as min, max, and avg (average or mean). Though somewhat odd-sounding, you can ask, for example, for the “average year” of movies with “London” in the title: 

select average(year) from movie where title like '%London%'

Finally, you can aggregate multiple groups of data, so that you get different sets of statistics. The group by keyword does this: 

select year, count(*) from movie where year < 1935 group by year

The main rule with group by is that the column being grouped should also be part of the select clause. This makes sense because otherwise, you wouldn’t be able to tell which group was which! And of course, you can mix and match. For example, the query above makes more sense if we arrange the data chronologically: 

select year, count(*) from movie where year < 1935 group by year order by year

See any trends in that data?

[edit] Joins

Thus far, we’ve only been working with one table in the database: movie. Our other tables, member and rating, add some further interest. These tables store ratings, from 1 to 5, made by individual members on movies they have seen.

Examination of the rating table reveals that it has movie, member, and rating. Thus, each record consists of a single rating, made by a particular member for a particular movie. Staying with one table for now, this query will list all of the ratings submitted by member no. 6: 

select * from rating where member = 6

This returns the expected answer (based on the inserts that you copy-pasted previously), but you may have noticed that the result isn’t quite as meaningful to us, since we get movie IDs back instead of titles. These movie titles, however, are in the movie table, not rating. We thus need to join the two tables. As you might have seen when looking at the schema of the rating table, the movie field is a foreign key to the id field in the movie table. Thus, every movie value in the rating table matches some id in the movie table, thus leading us to that movie’s title.

An SQL join uses the same basic select command, but requires the tables being joined (in this case movie and rating), as well as the fields/columns to use for “joining” records. “Joining” records means that their fields are combined to create a new “virtual” record. All other parts of the select command retain the same meanings as before: 

select columns from table1 inner join table2 on (join condition) where conditions

Thus, the previous query, modified to retrieve the titles and ratings of the movies rated by member no. 6, looks like this: 

select title, rating from movie inner join rating on (movie.id = rating.movie) where member = 6

As in the basic select command, you can tailor the where conditions as you need to pull ratings based on other criteria. For example, ratings for a particular movie, as opposed as for a particular member, can be retrieved by changing the where clause. The query below displays all ratings for the movie(s) whose title has an apostrophe (see the last section below for more on this): 

select rating from movie inner join rating on (movie.id = rating.movie) where title like '%''%'

Note that the member column of the rating table is a foreign key as well—it refers to the id column of the member table. The member table in our sample only includes member names, but in practice it can hold much more information. We can join more than once; extending the query above, we can now produce all ratings for the movie(s) whose title has an apostrophe, and list the member names who gave those ratings: 

select name, rating from movie inner join rating on (movie.id = rating.movie) inner join member on (member.id = rating.member) where title like '%''%'

Note how this approach helps avoid redundancy: instead of copying a member’s name over and over again, we can store the member’s name in one place, and use their ID as a reference to that name. This elimination of redundancy is called normalization—tables are normalized if they eliminate data redundancy in their columns (except, of course, for foreign keys).

[edit] The Notorious Apostrophe

You might have noticed that, because the apostrophe or single quote is used to indicate specific values in SQL (e.g., 'The Godfather', 'Smith', '6/30/1980', etc.), we run into a potential problem when the value itself should contain an apostrophe. This is not as uncommon as one might think; for example, a good number of movie titles have apostrophes (By Dawn's Early Light, Zatoichi's Conspiracy, Logan's Run, and Dead Men Don't Wear Plaid, to name a few), as do many names (“O'Malley,” “M'Benga,” “D'Angelo”). An insert command such as the one below will result in an error, since the apostrophe will be misinterpreted as ending a piece of text rather than as part of the text itself: 

insert into person(id, firstname, lastname, dob) values(2000, 'Beverly', 'D'Angelo', '8/21/1960');

Fortunately, SQL has a solution: apostrophes inside text should be indicated via two consecutive apostrophes, or ''. When encountered, SQL converts this pair of apostrophes into a single one, and does not interpret these apostrophes as ending a piece of text. Thus, the above command will work if rewritten in this way: 

insert into person(id, firstname, lastname, dob) values(2000, 'Beverly', 'D''Angelo', '8/21/1960');

While the solution does exist, it isn’t automatic: you need to be aware that apostrophes have to be written as “double apostrophes” before passing any text values on to SQL. Keep this in mind when trying to load data from a text file into a database table.

Retrieved from "https://xmlpipedb.lmucs.io/biodb/fall2013/index.php?title=PostgreSQL_Tutorial&oldid=2131"
Personal tools
Namespaces

Variants
Actions
Navigation
Toolbox