|
| Docs |
| What is it? |
| What it isn't? |
| Example |
| How to run |
| Installation |
| Credits |
| BSD License |
| Gallery |
| Subselects |
| Fan and Chasm Traps |
| Sakila |
| Big Examples |
| Links |
| Home Page |
| Code |
| Online Demo |
| Feedback |
| Email (reCAPTCHA) |
| Powered by |
 Python
|
|
|
| Pyparsing |
|
|
| Logo |
| Daniel Saarimäki |
| Updated 27 Jul 2009 |
Sakila
Sakila is a sample database from MySQL. Here is the underlying SELECT from the film_list view, and it's diagram (using "neato" algorithm):
SELECT
film.film_id AS FID,
film.title AS title,
film.description AS description,
category.name AS category,
film.rental_rate AS price,
film.length AS length,
film.rating AS rating,
GROUP_CONCAT(CONCAT(actor.first_name, _utf8' ', actor.last_name) SEPARATOR ', ') AS actors
FROM
category LEFT JOIN
film_category ON
category.category_id = film_category.category_id
LEFT JOIN film ON
film_category.film_id = film.film_id
JOIN film_actor ON
film.film_id = film_actor.film_id
JOIN actor ON
film_actor.actor_id = actor.actor_id
GROUP BY film.film_id;
Here is another example, with a sub-select, from the actor_info view. The original SQL had the same table aliases in the subselect as in the main select, and that confused the parser. The inner aliases were renamed as "_1". For subselects always use the "one-pass" algorithm.
SELECT
a.actor_id,
a.first_name,
a.last_name,
GROUP_CONCAT(DISTINCT CONCAT(c.name, ': ',
(SELECT GROUP_CONCAT(f_1.title ORDER BY f_1.title SEPARATOR ', ')
FROM sakila.film f_1
INNER JOIN sakila.film_category fc_1
ON f_1.film_id = fc_1.film_id
INNER JOIN sakila.film_actor fa_1
ON f_1.film_id = fa_1.film_id
WHERE fc_1.category_id = c.category_id
AND fa_1.actor_id = a.actor_id
)
)
ORDER BY c.name SEPARATOR '; ')
AS film_info
FROM sakila.actor a
LEFT JOIN sakila.film_actor fa
ON a.actor_id = fa.actor_id
LEFT JOIN sakila.film_category fc
ON fa.film_id = fc.film_id
LEFT JOIN sakila.category c
ON fc.category_id = c.category_id
GROUP BY a.actor_id, a.first_name, a.last_name;
The Select from the view "Sales_By_Store"
SELECT
CONCAT(c.city, _utf8',', cy.country) AS store,
CONCAT(m.first_name, _utf8' ', m.last_name) AS manager,
SUM(p.amount) AS total_sales
FROM payment AS p
INNER JOIN rental AS r ON p.rental_id = r.rental_id
INNER JOIN inventory AS i ON r.inventory_id = i.inventory_id
INNER JOIN store AS s ON i.store_id = s.store_id
INNER JOIN address AS a ON s.address_id = a.address_id
INNER JOIN city AS c ON a.city_id = c.city_id
INNER JOIN country AS cy ON c.country_id = cy.country_id
INNER JOIN staff AS m ON s.manager_staff_id = m.staff_id
GROUP BY s.store_id
ORDER BY cy.country, c.city;
