Troque Notes Week 5

User Page        Bio Databases Main Page       

Using PosGreSQL

Some nice acronym:

• Create - insert
• Retrieve - select
• Update - update
• Delete - delete

How to login to posGreSQL

• Open pgAdmin III and type in password
• PostgreSQL 9.2 (localhost:5432)
• Go to Databases
• Go to movie_database
• Go to Schemas
• Go to public
• Go to Tables
• Go to movie

Helpful Hints

• select * from movie = " give me all the data from table "movie" "
  • select title, year from movie; = "give me data with just the title and year"
  • select title, year from movie where year > 1990; = selective retrieval by year
  • select title, year from movie where title = 'Star Wars'; = give me the movie with the title "Star Wars"
  • select title, year from movie where title ~ 's$';
  • select title, year from movie where title like '%a%';
  • select title, year from movie where title like '%a%' order by year desc;
    • "desc" means descending order.
    • you can replace "order by year" with "order by ___"
  • select count(*) from movie where title ~ 's$';
  • select year, count(*) from movie where year > 1985 and year < 1996 group by year; = groups the movies by year and shows the number of movies in that year
• insert into movie (id, year, title) values (1, 1999, 'The Matrix') = "insert an item into the table"
  • Use single quotes for texts
• update movie set year = 2006 where id = 4;
• delete from movie where year = 1999;
• select * from movie, performer where movie.id = performer.movie_id; -> combines 2 tables together

Using movie_titles.txt

[insert into movie (id, year, title) values ([4, 1994, 'Paula Abduls Get Up & Dance