help with sakila example database

[LostInMySQL]

Hey guys, I am very new to mysql and trying to learn but I am dead confused with some select statements and views.

I am using the sakila example database

Looking at the schema I can see that in inventory there is a language table with the column names in it which corresponds to the language_id in film.
how would I build a query that displays all documentary films and their languages? 
If anyone can help that would be much appreciated.

 

 

[Ch0cu3r]

You'd perform a join

SELECT film.film_id,
       film.title,
       film.language_id,
       language.name AS language
FROM film
JOIN language ON language.language_id = film.language_id

[Barand]

I hadn't heard of that sample database so I just downloaded it. Although the schema is comprehensive, the data in it leaves a lot to be desired.

 

Doing an analysis by language

mysql> SELECT l.name as language, COUNT(f.film_id) as films
    -> FROM language l
    -> LEFT JOIN sakila.film f USING (language_id)
    -> GROUP BY l.name;
+----------+-------+
| language | films |
+----------+-------+
| English  |  1000 |
| French   |     0 |
| German   |     0 |
| Italian  |     0 |
| Japanese |     0 |
| Mandarin |     0 |
+----------+-------+
6 rows in set (0.01 sec)

and by category

mysql> SELECT c.name as category, l.name as language, COUNT(f.film_id) as films

    -> FROM
    -> category c
    -> INNER JOIN film_category fc USING (category_id)
    -> INNER JOIN film f USING (film_id)
    -> INNER JOIN language l USING (language_id)
    -> GROUP BY c.name,l.name;
+-------------+----------+-------+
| category    | language | films |
+-------------+----------+-------+
| Action      | English  |    64 |
| Animation   | English  |    66 |
| Children    | English  |    60 |
| Classics    | English  |    57 |
| Comedy      | English  |    58 |
| Documentary | English  |    68 |
| Drama       | English  |    62 |
| Family      | English  |    69 |
| Foreign     | English  |    73 |
| Games       | English  |    61 |
| Horror      | English  |    56 |
| Music       | English  |    51 |
| New         | English  |    63 |
| Sci-Fi      | English  |    61 |
| Sports      | English  |    74 |
| Travel      | English  |    57 |
+-------------+----------+-------+
16 rows in set (0.01 sec)

and although there is a many-to-many relationship between film and category there is not a single film in more than one category

mysql> SELECT f.title, COUNT(c.category_id) as numCats,
    ->     GROUP_CONCAT(c.name SEPARATOR ', ') as Categories
    -> FROM film f
    -> INNER JOIN film_category fc USING (film_id)
    -> INNER JOIN category c USING (category_id)
    -> GROUP BY f.film_id
    -> HAVING numCats > 1;
Empty set (0.01 sec)

[jazzman1]

You need to JOIN 4 tables according the sakila Database EER Diagram.

This is mine:

SELECT f.title, f.description,l.name FROM film f
INNER JOIN language l using(language_id)
INNER JOIN film_category fc using(film_id)
WHERE fc.category_id IN (
SELECT category_id FROM category 
WHERE name = 'Documentary')

Edited November 29, 2013 by jazzman1

[jazzman1]

@OP, if you want to learn and improve your skills in SQL, I recommend you to start with SQL exercises in this web site - http://www.sql-ex.ru.

 

@Barand, do you know it? If no..you will be surprise I guess.