How to do a Simple Database Project

This page assumes that you have read Working with the infrastructure and Working with PostgreSQL.

The best way to learn how to work with a database server, how to code SQL, and how to put together datasets for analysis is to actually do it! This page helps you do that.

Getting some data together

You'll need to put together:

• A tab delimited text file of data that contains:
  • A header row
  • At least one text variable (like name, etc)
  • At least one categorical variable (like state, industry, etc.) in text format
  • At least one numeric variable (like an amount)

Another tab delimited text file of data that contains:

• A header row
• The same text variable as in the first file
• Some other variable

These files should be of a reasonable size (say at least say 80 records, but not more than say 1,000 records), and drawn from something that you are working on or are going to be working on. You need to put them together before you get any formal instruction!

Connecting to the server and making a dbase

See Working with PostgreSQL to connect to the server and how to make a dbase.

Building a lookup table

We are going to create:

A table delimited text file of data that contains:

• • A header row
  • The same categorical variable as before as text
  • A numeric coding of that variable