Database Tutorial

Connecting to Server

  hostname = geog480.cigi.uiuc.edu
  username = netid
  port = 22

Connecting to Database

psql -U username -d database_name

Postgres Commands

\l
\dt
\?
\q

SQL Commands/Functions

Quick Reference

SQL Cheat Sheet

create table REPLACE_ME_your_netid (key int, attr varchar(20),value float);
 insert into your_netid values(1, 'attr0', 100);
 insert into your_netid values(2, 'attr1', 101);
 insert into your_netid values(3, 'attr1', 102);
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 your_netid set attr='attr1' where key=1;
update your_netid set value=105 where key=1;
select * from your_netid Order by key asc;
select * from your_netid Order by key desc;
select count(*) from your_netid;
select count(*) from your_netid where attr like '%1';
select max(value) from your_netid;
select avg(value) from your_netid where attr ilike '%1%';
delete from your_netid where key=1;
\COPY  your_netid FROM 'your_file' with CSV HEADER

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

drop table your_netid;