Difference between revisions of "Troque Notes Week 5"
From LMU BioDB 2015
(Added notes for week 5) |
m (Added a hint) |
||
| (2 intermediate revisions by the same user not shown) | |||
| Line 29: | Line 29: | ||
*** "desc" means descending order. | *** "desc" means descending order. | ||
*** you can replace "order by year" with "order by ___" | *** 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" | * insert into movie (id, year, title) values (1, 1999, 'The Matrix') = "insert an item into the table" | ||
** Use single quotes for texts | ** Use single quotes for texts | ||
| − | *update movie set year = 2006 where id = 4; | + | * 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 Abdul''s Get Up & Dance | ||
Latest revision as of 22:53, 1 October 2015
Contents
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
