Difference between revisions of "Ksherbina Week 6"

Latest revision as of 02:22, 15 October 2013

[edit] Movies from Text Files to Tables

1. Code:

select * from movie where year < 1915

Output:

4975;1909;"D.W. Griffith: Years of Discovery 1909-1913"
7654;1896;"Lumiere Brothers' First Films"
9103;1914;"Tillie's Punctured Romance"
10898;1914;"Cabiria"

2. Code:

select * from movie where year > 1979 and year < 1990 and title ~ '[0-9][0-9][0-9][0-9]'

Output:

217;1988;"Cherry 2000"
1163;1984;"2010: The Year We Make Contact"
1399;1984;"1984"
1480;1988;"Cold Summer of 1953"
4882;1985;"Butthole Surfers: Blind Eye See All: Live 1985"
5482;1989;"Gundam 0080: War in the Pocket"
5890;1985;"Transylvania 6-5000"
7725;1984;"Hong Kong 1941"
15128;1988;"1969"
15269;1989;"Mystery Science Theater 3000: The Crawling Hand"
15390;1987;"Eric Clapton & Friends: Live 1986"
16985;1982;"The 2000 Year Old Man"
17007;1987;"Teenage Mutant Ninja Turtles (1980s)"

3. Code:

select * from movie where title ~ ''''

Output:

4;1994;"Paula Abdul's Get Up & Dance"
9;1991;"Class of Nuke 'Em High 2"
19;2000;"By Dawn's Early Light"
23;2001;"Clifford: Clifford Saves the Day! / Clifford's Fluffiest Friend Cleo"
27;1962;"Sesame Street: Elmo's World: The Street We Live On"
30;2003;"Something's Gotta Give"
32;2004;"ABC Primetime: Mel Gibson's The Passion of the Christ"
34;2003;"Ashtanga Yoga: Beginner's Practice with Nicki Doane"
37;1973;"Zatoichi's Conspiracy"
54;1952;"We're Not Married"

4. Code:

select count(*) from movie where title ~ '^Star '

Output:

61

5. Code:

select count(*) from movie where year > 1939 and year < 1950

Output:

359

Correction:

Code:

select year, count(*) from movie where year > 1939 and year < 1950 group by year

Output:

1942;33
1948;36
1943;33
1946;39
1949;41
1945;37
1947;37
1940;34
1944;36
1941;33

6. Code:

select title from movie inner join rating on (rating.movie = movie.id) where member = '42' and rating = '5' order by title

Output:

"Bruce Lee: A Warrior's Journey"
"Donnie Darko: Director's Cut"
"The Legend of Zelda: Ganon's Evil Tower"
"Zatoichi's Conspiracy"

7. Code:

select title, rating, name from movie inner join rating on (movie.id = rating.movie) inner join member on (member.id = rating.member)

Output:

"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"

8. Code to count for how many movies member 42 gave a rating of 1:

select count(*) from rating where member = '42' and rating = '1'

Output:

1

Code to count for how many movies member 42 gave a rating of 2:

select count(*) from rating where member = '42' and rating = '2'

Output:

0 

Code to count for how many movies member 42 gave a rating of 3:

select count(*) from rating where member = '42' and rating = '3'

Output:

2

Code to count for how many movies member 42 gave a rating of 4:

select count(*) from rating where member = '42' and rating = '4'

Output:

3

Code to count for how many movies member 42 gave a rating of 5:

select count(*) from rating where member = '42' and rating = '5'

Output:

4

Correction:

Code:

select rating, count(*) from rating where member = '42' group by rating

Output:

5;4
1;1
4;3
3;2

9. Code:

select avg(rating) from movie inner join rating on (movie.id = rating.movie) where title ~ '^Star '

Output:

2.6000000000000000

10. Query in plain English: How many movies were not rated a 1 or 2 by member 6 or member 8?

Query in SQL:

select count(*) from movie inner join rating on (movie.id = rating.movie) where (member = '6' or member = '8') and not (rating = '1' or rating = '2')

Answer to the query:

6