Pekka Alaluukas /Databases

Database Exercises

Sakila database/t2 ratkaisut

Filmin nimi ja kieli

SELECT title, name 
from film inner join language ON film.language_id=language.language_id;

Filmin nimi ja näyttelijän id eli film/title ja film_actor/actor_id

SELECT title,actor_id 
from film inner join film_actor ON film.film_id=film_actor.film_id;

Filmin nimi ja näyttelijän nimi (etunimi ja sukunimi yhdessä)

SELECT title,concat(first_name," " ,last_name) as 'actor name'
from film inner join film_actor ON film.film_id=film_actor.film_id
  inner join actor ON film_actor.actor_id=actor.actor_id
order by title;

Edellinen niin että filmin nimi vain kerran ja sen perässä näyttelijöiden nimet

SELECT title,group_concat(concat(first_name," " ,last_name) order by last_name) as 'actor name'
from film inner join film_actor ON film.film_id=film_actor.film_id
  inner join actor ON film_actor.actor_id=actor.actor_id
group by title
order by title;

Näyttelijän nimi ja filmit joissa esiintynyt ->hakuja vaikka nimellä

SELECT first_name, last_name, title  
from actor inner join film_actor ON actor.actor_id=film_actor.actor_id 
  inner join film on film.film_id=film_actor.film_id
  where first_name='Penelope' and last_name='Guiness' ;

- Edellinen koostettuna group_concatilla

SELECT first_name, last_name, group_concat(title) as 'films'   
from actor inner join film_actor ON actor.actor_id=film_actor.actor_id 
  inner join film on film.film_id=film_actor.film_id
where first_name='Penelope' and last_name='Guiness' 
group by actor.actor_id;

- Maat ja sen kaupungit

SELECT country, city 
from country inner join city on country.country_id=city.country_id;

lisätään Suomi

insert into country(country) values('Suomi');

Näytetään myös ne maat joissa ei cityjä

SELECT country, city, country.country_id 
from country left join city on country.country_id=city.country_id
from city right join country on country.country_id=city.country_id
order by country_id desc;

Näytä kaupungit jotka ovat samassa maassa kuin Hanoi

SELECT city from city where country_id= (
	SELECT country_id from city where city='Hanoi');

Missä filmeissä Penelope Guiness on näytellyt
LIITOKSELLA

SELECT title 
from film inner join film_actor on film.film_id=film_actor.film_id 
	inner join actor on actor.actor_id=film_actor.actor_id
where last_name='Guiness' and first_name='Penelope';

SAMA alikyselynä

SELECT title from film WHERE film_id IN
   (SELECT film_id from film_actor
   WHERE actor_id=
          (SELECT actor_id from actor
          WHERE last_name='Guiness' and first_name='Penelope'
          )
    );

Listaa niiden elokuvien kategoriat, niille elokuville joissa Penelope Guiness on esiintynyt

SELECT category.name
from category inner join film_category on category.category_id=film_category.category_id 
	inner join film on film_category.film_id=film.film_id 
		inner join film_actor on film.film_id=film_actor.film_id 
			inner join actor on film_actor.actor_id=actor.actor_id 
where last_name='Guiness' and first_name='Penelope';

sama ilman film-taulua

SELECT category.name
from category inner join film_category on category.category_id=film_category.category_id 
		inner join film_actor on film_category.film_id=film_actor.film_id 
			inner join actor on film_actor.actor_id=actor.actor_id 
where last_name='Guiness' and first_name='Penelope';

Filmin nimi ja kategoria, joissa Penelope Guiness ...

SELECT film.title, category.name
from category inner join film_category on category.category_id=film_category.category_id 
	inner join film on film_category.film_id=film.film_id 
		inner join film_actor on film.film_id=film_actor.film_id 
			inner join actor on film_actor.actor_id=actor.actor_id 
where last_name='Guiness' and first_name='Penelope';

Kauhufilmit, joissa Penelope Guiness ...

SELECT film.title, category.name
from category inner join film_category on category.category_id=film_category.category_id 
	inner join film on film_category.film_id=film.film_id 
		inner join film_actor on film.film_id=film_actor.film_id 
			inner join actor on film_actor.actor_id=actor.actor_id 
where last_name='Guiness' and first_name='Penelope' and category.name='Horror';

Kauhufilmit ja perhefilmit , joissa Penelope Guiness ...

SELECT film.title, category.name, actor.last_name, actor.first_name
from category inner join film_category on category.category_id=film_category.category_id 
	inner join film on film_category.film_id=film.film_id 
		inner join film_actor on film.film_id=film_actor.film_id 
			inner join actor on film_actor.actor_id=actor.actor_id 
where (last_name='Guiness' and first_name='Penelope') and (category.name='Horror' or category.name='Family');

Toggle Menu