/*
L?sningsforslag IN2090 Ukesoppgavesett 4
*/

-- Oppgave 3
-- a)
/*
Alle filmider og titler i tabellen film. (692361)
*/

SELECT f.filmid, f.title
FROM film f
;

/*
filmid  |  title

----------+----------------------------------------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------------------------------------------------
 	17 | E.R. Sluts
 	18 | Tarantos, Los
 	19 | Privilege
 	20 | Jag en kvinna, II - ?ktenskapet
 	21 | Jack l'谷ventreur
 	23 | Brooklyn Connection, The

..................................................................................................................................

(692361 rows)
*/


-- b)
/*
Filmid og  tittel  for alle  filmer  utgitt  i 1892.  (12)
*/

SELECT f.filmid, f.title
FROM film f
WHERE f.prodyear = 1892
;

/*
 filmid  |          title
---------+-------------------------
  436251 | Clown et ses chiens, Le
  700124 | Clown and His Dogs, The
  954426 | Boxing
 1394843 | Pauvre Pierrot
 1874971 | Un bon bock
 2286076 | Poor Pierrot
 2320346 | Fencing
 2887002 | Hand Shake, A
 3086012 | Good Beer, A
 3111303 | Man on Parallel Bars
 3111751 | Wrestling
 5500730 | Prince de Galles, Le
(12 rows)
*/

-- c)
/*
Alle filmider og titler i tabellen film hvor filmid er mellom 2000 og 2030. (14)
*/

SELECT f.filmid, f.title
FROM film f
WHERE f.filmid >= 2000 AND f.filmid <= 2030
;

/*
filmid | title
--------+--------------------------------------------------------
  2001 | Bowling Balls
  2003 | Musta rakkaus
  2004 | Espantalho
  2006 | Northern Lights
  2010 | 'Cannibal Apocalypse' Redux
  2017 | Chronicles of the Dark Carnival, The
  2018 | Oral Majority 3
  2019 | Nainen on valttia
  2020 | Cidade Oculta
  2021 | Highlander: Endgame
  2022 | Animales devoradores: El hombre
  2023 | Blood Red Planet
  2024 | Pura vida Ibiza
  2026 | 'Capulina contra las momias' (El terror de Guanajuato)
(14 rows)
*/


-- d)
/*
Tittel og filmid p? alle filmer med 'Star Wars' i navnet. (129)
*/

SELECT f.title, f.filmid
FROM Film f
WHERE f.title LIKE '%Star Wars%'
;

/*
title                            	| filmid
--------------------------------------------------------------------+---------
'Star Wars' Holiday Special, The                               	|	7450
'Star Wars': A Musical Journey                                 	|	7466
'Star Wars': Feel the Force                                    	|	7482
Star Wars: Rebel Assault                                       	|   47911
Star Wars: Revelations                                         	|   62417
Star Wars: Jedi Knight - Dark Forces II                        	|  127428
Star Wars: Episode I - The Phantom Menace                      	|  127652
...
(129 rows)
*/


-- e)
/*
Fornavn og etternavn til personid 465221. (1)
*/

SELECT p.firstname, p.lastname
FROM Person p
WHERE p.personid = 465221
;

/*
firstname | lastname
-----------+----------
Johnny	| Depp
(1 row)
*/


-- f)
/*
Alle unike rolletyper(parttype) i tabellen filmparticipation. (7)
*/

SELECT DISTINCT parttype
FROM filmparticipation
;
/*

parttype
------------------
writer
costume designer
director
editor
cast
composer
producer
(7 rows)
*/


-- g)
/*
Tittel og produksjons?r for alle filmer som inneholder ordene "Rush Hour". (15)
*/

SELECT f.title, f.prodyear
FROM film f
WHERE f.title LIKE '%Rush Hour%'
;

/*
                        title                           | prodyear
----------------------------------------------------------+----------
 Rush Hour                                                |   1941
 Rush Hour                                                |   1998
 Reel Comedy: Rush Hour 2                                 |   2001
 Rush Hour 3                                              |   2007
 Rush Hour                                                |   2006
 Rush Hour 2                                              |   2001
 Rush Hour, The                                           |   1974
 Sto?zeit - Rush Hour                                     |   1970
 Rush Hour, The                                           |   1928
 SimCity 4: Rush Hour                                     |   2003
 Rush Hour - Due mine vaganti                             |   2000
 Colpo grosso al drago rosso - Rush Hour 2                |   2002
 Piece of the Action: Behind the Scenes of 'Rush Hour', A |   1999
 Rush Hour                                                |   1970
 Reel Comedy: Rush Hour                                   |   1998
(15 rows)
*/


-- h)
/*
Alle etternavn(hvert etternavn kun listet en gang) i tabellen Person i fallende / motsatt rekekf?lge. Bruk limit til ? begrense til 200 rader. (200)
*/

SELECT DISTINCT p.lastname
FROM Person p
ORDER BY p.lastname desc
LIMIT 200
;

/*
lastname
----------------------
?r芍insson
迆r k車r B迆sta?akirkju
谷s a Borz
芍 Neystab?
角 B?le
?邦k邦r
?車r車lfsd車ttir
?車r?arson
...
(200 rows)
*/



-- Oppgave 4
-- a)
/*
Tittel produksjons?r og filmtype for alle filmer som ble produsert i 1894. (82)
*/

SELECT f.title, f.prodyear, fi.filmtype
FROM Film f, Filmitem fi
WHERE f.prodyear = 1894
  AND f.filmid = fi.filmid
;

/*
                     title                      | prodyear | filmtype
------------------------------------------------+----------+----------
 Fancy Club Swinger                             |     1894 | C
 Barbershop, The                                |     1894 | C
 Amateur Gymnast, No. 2                         |     1894 | C
 Caicedo (with Pole)                            |     1894 | C
 Annabelle Butterfly Dance                      |     1894 | C
 Annabelle Sun Dance                            |     1894 | C
 Annie Oakley                                   |     1894 | C
 Barber Shop, The                               |     1894 | C
 Armand D'Ary                                   |     1894 | C
 Cock Fight, The                                |     1894 | C
 ....
(82 rows)
*/


-- b)
/*
Alle kvinnelige skuespillere(cast) i filmen med filmid 357076. Skriv ut navn og pa skuespillerene og filmid. (11)
*/

SELECT DISTINCT p.firstname, p.lastname, fp.filmid
FROM Person p, Filmparticipation fp
WHERE p.gender = 'F'
  AND fp.filmid = 357076
  AND fp.parttype = 'cast'
  AND p.personid = fp.personid
;

/*
 firstname |  lastname   | filmid
-----------+-------------+--------
 Cate      | Blanchett   | 357076
 Elizabeth | Moody       | 357076
 Kate      | O'Rourke    | 357076
 Katie     | Jackson     | 357076
 Liv       | Tyler       | 357076
 Lori      | Dungey      | 357076
 Megan     | Edwards     | 357076
 Sabine    | Crossen     | 357076
 Sarah     | McLeod      | 357076
 Taea      | Hartwell    | 357076
 Victoria  | Beynon-Cole | 357076
(11 rows)
*/


-- c)
/*
BONUS: Hva er tittelen? Legg til en ekstra kolonne med tittelen (krever join med enda en tabell). (11)
*/

SELECT DISTINCT p.firstname, p.lastname, fp.filmid, f.title
FROM Person p, Filmparticipation fp, Film f
WHERE p.gender = 'F'
  AND fp.filmid = 357076
  AND fp.parttype = 'cast'
  AND p.personid = fp.personid
  AND fp.filmid = f.filmid
;

/*
 firstname |  lastname   | filmid |                       title
-----------+-------------+--------+----------------------------------------------------
 Cate      | Blanchett   | 357076 | Lord of the Rings: The Fellowship of the Ring, The
 Elizabeth | Moody       | 357076 | Lord of the Rings: The Fellowship of the Ring, The
 Kate      | O'Rourke    | 357076 | Lord of the Rings: The Fellowship of the Ring, The
 Katie     | Jackson     | 357076 | Lord of the Rings: The Fellowship of the Ring, The
 Liv       | Tyler       | 357076 | Lord of the Rings: The Fellowship of the Ring, The
 Lori      | Dungey      | 357076 | Lord of the Rings: The Fellowship of the Ring, The
 Megan     | Edwards     | 357076 | Lord of the Rings: The Fellowship of the Ring, The
 Sabine    | Crossen     | 357076 | Lord of the Rings: The Fellowship of the Ring, The
 Sarah     | McLeod      | 357076 | Lord of the Rings: The Fellowship of the Ring, The
 Taea      | Hartwell    | 357076 | Lord of the Rings: The Fellowship of the Ring, The
 Victoria  | Beynon-Cole | 357076 | Lord of the Rings: The Fellowship of the Ring, The
(11 rows)
*/