Kmeilak Week 6 - Revision history

Kmeilak at 04:14, 3 October 2013

2013-10-03T04:14:06Z

Kmeilak at 03:00, 3 October 2013

2013-10-03T03:00:19Z

Kmeilak at 03:56, 2 October 2013

2013-10-02T03:56:26Z

Kmeilak: /* Movies from Text File to Tables */

2013-10-02T03:38:38Z

‎Movies from Text File to Tables

Kmeilak: /* Movies from Text File to Tables */

2013-10-02T03:34:44Z

‎Movies from Text File to Tables

Kmeilak: /* Movies from Text File to Tables */

2013-10-02T03:29:16Z

‎Movies from Text File to Tables

Kmeilak: /* Movies from Text File to Tables */

2013-10-02T03:16:16Z

‎Movies from Text File to Tables

Kmeilak: /* Movies from Text File to Tables */

2013-10-02T03:07:23Z

‎Movies from Text File to Tables

Kmeilak: /* Movies from Text File to Tables */

2013-10-02T02:47:53Z

‎Movies from Text File to Tables

Kmeilak at 17:19, 26 September 2013

2013-09-26T17:19:44Z

@@ Line 1,391: / Line 1,391: @@
 . How many movies has member number 42 rated as 1, 2, 3, 4, and 5, respectively?
-Was not able to get a correct answer for this one; only received error messages. The closest command is below.
+rating:number
 command
-  select count(*) from movie inner join rating on (movie.id = rating.movie) inner join member on (member.id = rating.member) where member = 42 order by rating
+  select * from movie inner join rating on (movie.id = rating.movie) inner join member on (member.id = rating.member) where member = 42 order by rating
 . What is the average rating of movies that begin with the word “Star”?

← Older revision		Revision as of 03:00, 3 October 2013
Line 1,391:		Line 1,391:
	8. How many movies has member number 42 rated as 1, 2, 3, 4, and 5, respectively?		8. How many movies has member number 42 rated as 1, 2, 3, 4, and 5, respectively?

		+	Was not able to get a correct answer for this one; only received error messages. The closest command is below.

		+	command
		+
		+	select count(*) from movie inner join rating on (movie.id = rating.movie) inner join member on (member.id = rating.member) where member = 42 order by rating

	9. What is the average rating of movies that begin with the word “Star”?		9. What is the average rating of movies that begin with the word “Star”?

← Older revision		Revision as of 03:56, 2 October 2013
Line 1,414:		Line 1,414:

	select title from movie inner join rating on (movie.id = rating.movie) inner join member on (member.id = rating.member) where member = 6 order by title		select title from movie inner join rating on (movie.id = rating.movie) inner join member on (member.id = rating.member) where member = 6 order by title
		+
		+
		+	{{Kmeilak}}

← Older revision		Revision as of 03:38, 2 October 2013
Line 1,357:		Line 1,357:
	7. Produce a table that lists every movie that has been rated, the rating that the movie got, and the name of the member that gave this rating.		7. Produce a table that lists every movie that has been rated, the rating that the movie got, and the name of the member that gave this rating.

		+	Title;Rating;Member Name
		+
		+	*"Something's Gotta Give";4;"Doug"
		+	*"Zatoichi's Conspiracy";4;"Natalie"
		+	*"Zatoichi's Conspiracy";5;"Doug"
		+	*"Bruce Lee: A Warrior's Journey";2;"Natalie"
		+	*"Bruce Lee: A Warrior's Journey";5;"Doug"
		+	*"Star Trek: Deep Space Nine: Season 5";5;"Natalie"
		+	*"Star Trek: The Next Generation: Season 5";5;"Natalie"
		+	*"Star Wars: Ewok Adventures: Caravan of Courage / The Battle for Endor";1;"Natalie"
		+	*"Sesame Street: Cookie Monster's Best Bites";3;"Boomer"
		+	*"The Legend of Zelda: Ganon's Evil Tower";5;"Doug"
		+	*"Hello Kitty's Paradise";3;"Doug"
		+	*"Sesame Street: Elmo's Sing-Along Guessing Game";4;"Doug"
		+	*"Star Trek V: The Final Frontier";2;"Natalie"
		+	*"Star Wars: Episode II: Attack of the Clones";1;"Boomer"
		+	*"Star Trek: Nemesis";2;"Natalie"
		+	*"Star Wars: Episode VI: Return of the Jedi";5;"Boomer"
		+	*"Weekend at Bernie's";4;"Doug"
		+	*"Star Wars: Episode I: The Phantom Menace: Bonus Material";2;"Boomer"
		+	*"Star Trek: Insurrection";1;"Boomer"
		+	*"Donnie Darko: Director's Cut";5;"Doug"
		+	*"Pokemon Master Quest: Collector's Box: Quest 1";3;"Doug"
		+	*"She's All That";1;"Doug"
		+	*"She's All That";3;"Boomer"
		+	*"Star Trek V: The Final Frontier: Bonus Material";2;"Boomer"

← Older revision		Revision as of 03:34, 2 October 2013
Line 1,374:		Line 1,374:

	select avg(rating) from movie inner join rating on (movie.id = rating.movie) where title like 'Star%'		select avg(rating) from movie inner join rating on (movie.id = rating.movie) where title like 'Star%'
		+
		+	10. What movies, in alphabetical order, did member number 6 rate as a 3?
		+
		+	*"Bruce Lee: A Warrior's Journey"
		+	*"Star Trek V: The Final Frontier"
		+	*"Star Trek: Deep Space Nine: Season 5"
		+	*"Star Trek: Nemesis"
		+	*"Star Trek: The Next Generation: Season 5"
		+	*"Star Wars: Ewok Adventures: Caravan of Courage / The Battle for Endor"
		+	*"Zatoichi's Conspiracy"
		+
		+	command
		+
		+	select title from movie inner join rating on (movie.id = rating.movie) inner join member on (member.id = rating.member) where member = 6 order by title

← Older revision		Revision as of 03:29, 2 October 2013
Line 1,362:		Line 1,362:

	select title, rating, name from movie inner join rating on (movie.id = rating.movie) inner join member on (member.id = rating.member)		select title, rating, name from movie inner join rating on (movie.id = rating.movie) inner join member on (member.id = rating.member)
		+
		+	8. How many movies has member number 42 rated as 1, 2, 3, 4, and 5, respectively?
		+
		+
		+
		+	9. What is the average rating of movies that begin with the word “Star”?
		+
		+	0
		+
		+	command
		+
		+	select avg(rating) from movie inner join rating on (movie.id = rating.movie) where title like 'Star%'

← Older revision		Revision as of 03:16, 2 October 2013
Line 1,354:		Line 1,354:

	select title, rating from movie inner join rating on (movie.id = rating.movie) where member = 42 and rating = 5 order by title		select title, rating from movie inner join rating on (movie.id = rating.movie) where member = 42 and rating = 5 order by title
		+
		+	7. Produce a table that lists every movie that has been rated, the rating that the movie got, and the name of the member that gave this rating.
		+
		+
		+
		+	command
		+
		+	select title, rating, name from movie inner join rating on (movie.id = rating.movie) inner join member on (member.id = rating.member)

← Older revision		Revision as of 03:07, 2 October 2013
Line 1,343:		Line 1,343:

	select year, count(*) from movie where year >= 1940 and year < 1950 group by year order by year		select year, count(*) from movie where year >= 1940 and year < 1950 group by year order by year
		+
		+	6. Which movies, in alphabetical order, did member number 42 rate as a “5”?
		+
		+	*Bruce Lee: A Warrior's Journey
		+	*Donnie Darko: Director's Cut
		+	*The Legend of Zelda: Ganon's Evil Tower
		+	*Zatoichi's Conspiracy
		+
		+	command
		+
		+	select title, rating from movie inner join rating on (movie.id = rating.movie) where member = 42 and rating = 5 order by title

← Older revision		Revision as of 02:47, 2 October 2013
Line 1,318:		Line 1,318:

	select * from movie where title ~ '''';		select * from movie where title ~ '''';
		+
		+	4. How many movies have titles that begin with the word “Star”?
		+
		+	88
		+
		+	command
		+
		+	select count(*) from movie where title like 'Star%';
		+
		+	5. How many movies, on a year by year basis, were released in the 1940s (years 1940 to 1949)?
		+
		+	*1940 - 34 movies released
		+	*1941 - 33 movies released
		+	*1942 - 33 movies released
		+	*1943 - 33 movies released
		+	*1944 - 36 movies released
		+	*1945 - 37 movies released
		+	*1946 - 39 movies released
		+	*1947 - 37 movies released
		+	*1948 - 36 movies released
		+	*1949 - 41 movies released
		+
		+	command
		+
		+	select year, count(*) from movie where year >= 1940 and year < 1950 group by year order by year

← Older revision		Revision as of 17:19, 26 September 2013
Line 1,314:		Line 1,314:
	"Elizabeth Taylor: The Last Time I Saw Paris/Father's Little Dividend"		"Elizabeth Taylor: The Last Time I Saw Paris/Father's Little Dividend"
	"Godzilla's Revenge"		"Godzilla's Revenge"
		+
		+	command
		+
		+	select * from movie where title ~ '''';