Sunday, April 10, 2011

I definitively don't like SQL.


  • I downloaded and installed in my postgresql, the example database called pagila that we're using in our datawarehouse-bi course from here


To import a sql file into postgresql is as easy as this:

[sourcecode langugae="bash"]

psql -U username database -f file.sql

[/sourcecode]

This database depicts a small video club business and is used to train people  in SQL. The schema is normalized to some extend (though some parts aren't normalized at all) and almost every attribute has its own table.

  • So I started to practice and realized how tough is to get the reports you need, in a schema structured this way. I thought of a simple query: "give me a list of movie names, quantity in stock, category, in which store I can find them, the principal actor and their language". I came up with this:


[sourcecode language="sql"]

select f.title as TITLE, ac.name as ACTOR,count(f.film_id) as in_stock,n.name as CATEGORY,l.name as LANGUAGE,i.store_id as STORE

from film as f  left join film_category as c on f.film_id = c.film_id left join language as l on l.language_id = f.language_id

left join category as n on c.category_id = n.category_id left join film_actor as a on a.film_id = f.film_id

left join actor as ac on ac.actor_id = a.actor_id left join inventory as i on f.film_id = i.film_id

group by f.title,n.name,l.name,i.store_id,a.actor_id,ac.name

order by in_stock desc;

[/sourcecode]

The output:



Something so simple becomes a rather complex thing. So it's not that hard to imagine the reason why many people are looking for alternatives to sql (like nosql)  and to the relational model. Or I don't know, maybe its just that I don't like databases that much =-D.

 

No comments:

Post a Comment