Database Tutorial

Connecting to Server

  • Use openssh client (Start → All Programs → OpenSSH)
  hostname = geog473.cigi.uiuc.edu
  username = group1/group2
  port = 22
  • Enter your group passwd when prompted

Connecting to Database

psql -U username -d database_name
  • username = group1/group2
  • database_name = group1/group2
  • Enter your group passwd when prompted

Postgres Commands

SQL Cheat Sheet

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

SQL Commands

  • Create Table
create table your_first_name (key int, attr varchar(20),value float);
  • Insert a row
 insert into your_first_name values(1, 'attr0', 100);
 insert into your_first_name values(2, 'attr2', 101);
  • Update table contents
update your_first_name set attr='attr1' where key=1;
  • List contents of table
select * from your_first_name;
select * from your_first_name where attr='attr1';
select * from your_first_name where attr='attr0';
  • Delete Rows
delete from your_first_name where key=1;