Database Tutorial

Connecting to Server

  • Use openssh client (Start → All Programs → OpenSSH)
  hostname = geog480.cigi.uiuc.edu
  username = netid
  port = 22
  • Enter your netid passwd when prompted

Connecting to Database

psql -U username -d database_name
  • username = geog480
  • database_name = tutorial
  • Enter passwd when prompted (same as username)

Postgres Commands

  • List all accessible databases
\l
  • List all the tables in current DB
\dt
  • Help
\?
  • Quit
\q

SQL Commands/Functions

Quick Reference

SQL Cheat Sheet

  • Create Table
create table REPLACE_ME_your_netid (key int, attr varchar(20),value float);
  • Insert a row
 insert into your_netid values(1, 'attr0', 100);
 insert into your_netid values(2, 'attr1', 101);
 insert into your_netid values(3, 'attr1', 102);
  • List contents of table (Notice that the select statement allows you to view contents in the table and the where clause allows you to filter what the records you what to view)
select * from your_netid;
select * from your_netid where attr='attr1';
select * from your_netid where key=2;
select key, value from your_netid limit 5;
  • Update table contents
update your_netid set attr='attr1' where key=1;
update your_netid set value=105 where key=1;
  • Sorting
select * from your_netid Order by key asc;
select * from your_netid Order by key desc;
  • Counting
select count(*) from your_netid;
select count(*) from your_netid where attr like '%1';
  • Max/Min/Avg
select max(value) from your_netid;
select avg(value) from your_netid where attr ilike '%1%';
  • Delete Rows
delete from your_netid where key=1;
  • Copying a CSV file (postgres specific)
\COPY  your_netid FROM 'your_file' with CSV HEADER

You may use /srv/cigi/code/test.csv for your_file

  • Drop Table
drop table your_netid;