HomeMogDBMogDB StackUqbar
Ecological Tools
Doc Tools
中文
v2.1

Documentation:v2.1

Supported Versions:
Current(v5.0)/
v3.1/
v3.0/
v2.1

Other Versions:
v2.0/
v1.1

Using Sample Dataset Mogila

MogDB provides a sample dataset Mogila, which is a port of the Sakila example database available for MySQL. Sakila was originally developed by Mike Hillyer of the MySQL AB documentation team. It is intended to provide a standard schema that can be used for examples in books, tutorials, articles, samples, etc.

Mogila is a database representing a DVD rental store, containing information about films (like title, category, actresses), rental stores (like address, staff members, customers) and rentals.

Mogila works against MogDB 2.1 and above.


Entity-relationship Model Diagram

The image below shows an overview of the Mogila database tables and views. Here you can see how the tables relate to each other, through each fields. For example, the film table has string columns like title and description. It also relates to the table language with the columns language_id and original_language_id. With that information, you know that you can join both tables to get the language of each film, or to list all films for a specific language.

img


Use Mogila on MogDB Container Edition

The Mogila sample dataset has been built into the MogDB Container Edition, and no additional installation is required.

  1. Install container-based MogDB.

  2. Use mogila:

    docker exec -it mogdb bash
omm@eb7aef3f860f:~$ gsql -d mogila -p5432
gsql ((MogDB x.x.x build 56189e20) compiled at 2022-01-07 18:47:53 commit 0 last mr  )
Non-SSL connection (SSL connection is recommended when requiring high-security)
Type "help" for help.

mogila=# \dt
                          List of relations
 Schema |     Name      | Type  | Owner |             Storage
--------+---------------+-------+-------+----------------------------------
 public | actor         | table | mogdb | {orientation=row,compression=no}
 public | address       | table | mogdb | {orientation=row,compression=no}
 public | category      | table | mogdb | {orientation=row,compression=no}
 public | city          | table | mogdb | {orientation=row,compression=no}
 public | country       | table | mogdb | {orientation=row,compression=no}
 public | customer      | table | mogdb | {orientation=row,compression=no}
 public | film          | table | mogdb | {orientation=row,compression=no}
 public | film_actor    | table | mogdb | {orientation=row,compression=no}
 public | film_category | table | mogdb | {orientation=row,compression=no}
 public | inventory     | table | mogdb | {orientation=row,compression=no}
 public | language      | table | mogdb | {orientation=row,compression=no}
 public | payment       | table | mogdb | {orientation=row,compression=no}
 public | rental        | table | mogdb | {orientation=row,compression=no}
 public | staff         | table | mogdb | {orientation=row,compression=no}
 public | store         | table | mogdb | {orientation=row,compression=no}
(15 rows)

mogila=#

Use Mogila on MogDB Enterprise Edition

  1. Install MogDB。

  2. Create the sample database mogila and user mogdb, and quit.

    # switch to user omm
[root@test ~]# su - omm
# Log in to the postgres database and enter the port number according to the actual situation
[omm@test ~]$ gsql -d postgres -p5432 -r
gsql ((MogDB x.x.x build 56189e20) compiled at 2022-01-07 18:47:53 commit 0 last mr  )
Non-SSL connection (SSL connection is recommended when requiring high-security)
Type "help" for help.
MogDB=#create database mogila DBCOMPATIBILITY='PG';
CREATE DATABASE
MogDB=#\q
[omm@test ~]$

  3. Download mogila, and upload the files to the server and enter the directory where mogila files are located.

  4. Create schema objects then insert data manually:

    4.1 Create all schema objetcs (tables, etc):

    gsql -d mogila -p5432 -f mogila-schema.sql

    4.2. Insert all data:

    gsql -d mogila -p5432 -f mogila-data.sql

    OR create schema objects and insert data by 1 script, if you have done 4.1 and 4.2, you don't need 5!!

  5. Create all schema objetcs (tables, etc) and Insert all data:

    gsql -d mogila -p5432 -f mogila-insert-data.sql

  6. Done! Use:

    [omm@test ~]$ gsql -d mogila -p5432 -r
gsql ((MogDB x.x.x build 56189e20) compiled at 2022-01-07 18:47:53 commit 0 last mr  )
Non-SSL connection (SSL connection is recommended when requiring high-security)
Type "help" for help.

mogila=# \dt
                          List of relations
 Schema |     Name      | Type  | Owner |             Storage
--------+---------------+-------+-------+----------------------------------
 public | actor         | table | mogdb | {orientation=row,compression=no}
 public | address       | table | mogdb | {orientation=row,compression=no}
 public | category      | table | mogdb | {orientation=row,compression=no}
 public | city          | table | mogdb | {orientation=row,compression=no}
 public | country       | table | mogdb | {orientation=row,compression=no}
 public | customer      | table | mogdb | {orientation=row,compression=no}
 public | film          | table | mogdb | {orientation=row,compression=no}
 public | film_actor    | table | mogdb | {orientation=row,compression=no}
 public | film_category | table | mogdb | {orientation=row,compression=no}
 public | inventory     | table | mogdb | {orientation=row,compression=no}
 public | language      | table | mogdb | {orientation=row,compression=no}
 public | payment       | table | mogdb | {orientation=row,compression=no}
 public | rental        | table | mogdb | {orientation=row,compression=no}
 public | staff         | table | mogdb | {orientation=row,compression=no}
 public | store         | table | mogdb | {orientation=row,compression=no}
(15 rows)

mogila=#

Sample Queries

This section shows how to use the sample database Mogila with some queries. All the queries results were limited by the first 10 items.

  • List all the films by ordered by their length

    select
    film_id,
    title,
    length
from
    film
order by
    length desc;
    |film_id|title             |length|
|-------|------------------|------|
|426    |HOME PITY         |185   |
|690    |POND SEATTLE      |185   |
|609    |MUSCLE BRIGHT     |185   |
|991    |WORST BANGER      |185   |
|182    |CONTROL ANTHEM    |185   |
|141    |CHICAGO NORTH     |185   |
|349    |GANGS PRIDE       |185   |
|212    |DARN FORRESTER    |185   |
|817    |SOLDIERS EVOLUTION|185   |
|872    |SWEET BROTHERHOOD |185   |

  • List how many films there are in each film category

    select
    category.name,
    count(category.name) category_count
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
group by
    category.name
order by
    category_count desc;
    |name       |category_count|
|-----------|--------------|
|Sports     |74            |
|Foreign    |73            |
|Family     |69            |
|Documentary|68            |
|Animation  |66            |
|Action     |64            |
|New        |63            |
|Drama      |62            |
|Sci-Fi     |61            |
|Games      |61            |

  • Show the actors and actresses ordered by how many movies they are featured in

    select
    actor.first_name,
    actor.last_name,
    count(actor.first_name) featured_count
from
    actor
left join film_actor on
    actor.actor_id = film_actor.actor_id
group by
    actor.first_name,
    actor.last_name
order by
    featured_count desc;
    |first_name|last_name|featured_count|
|----------|---------|--------------|
|SUSAN     |DAVIS    |54            |
|GINA      |DEGENERES|42            |
|WALTER    |TORN     |41            |
|MARY      |KEITEL   |40            |
|MATTHEW   |CARREY   |39            |
|SANDRA    |KILMER   |37            |
|SCARLETT  |DAMON    |36            |
|VIVIEN    |BASINGER |35            |
|VAL       |BOLGER   |35            |
|GROUCHO   |DUNST    |35            |

  • Get a list of all active customers, ordered by their first name

    select
    first_name,
    last_name
from
    customer
where
    active = 1
order by first_name asc;
    |first_name|last_name|
|----------|---------|
|MARY      |SMITH    |
|PATRICIA  |JOHNSON  |
|LINDA     |WILLIAMS |
|BARBARA   |JONES    |
|ELIZABETH |BROWN    |
|JENNIFER  |DAVIS    |
|MARIA     |MILLER   |
|SUSAN     |WILSON   |
|MARGARET  |MOORE    |
|DOROTHY   |TAYLOR   |

  • See who rented most DVDs - and how many times

    select
    customer.first_name,
    customer.last_name,
    count(customer.first_name) rentals_count
from
    customer
left join rental on
    customer.customer_id = rental.customer_id
group by
    customer.first_name,
    customer.last_name
order by rentals_count desc;
    |first_name|last_name|rentals_count|
|----------|---------|-------------|
|ELEANOR   |HUNT     |46           |
|KARL      |SEAL     |45           |
|CLARA     |SHAW     |42           |
|MARCIA    |DEAN     |42           |
|TAMMY     |SANDERS  |41           |
|WESLEY    |BULL     |40           |
|SUE       |PETERS   |40           |
|MARION    |SNYDER   |39           |
|RHONDA    |KENNEDY  |39           |
|TIM       |CARY     |39           |

  • See the total revenue of each rental store

    select
    store.store_id,
    sum(payment.amount) as "total revenue"
from
    store
left join inventory on
    inventory.store_id = store.store_id
left join rental on
    rental.inventory_id = inventory.inventory_id
left join payment on
    payment.rental_id = rental.rental_id
where
    payment.amount is not null
group by
    store.store_id
order by
    sum(payment.amount) desc;
    |store_id|total revenue|
|--------|-------------|
|       2|     33726.77|
|       1|     33689.74|

  • List the top 5 film genres by their gross revenue

    select
    category.name,
    film.title,
    sum(payment.amount) as "gross revenue"
from
    film
left join film_category on
    film_category.film_id = film.film_id
left join category on
    film_category.category_id = category.category_id
left join inventory on
    inventory.film_id = film.film_id
left join rental on
    rental.inventory_id = inventory.inventory_id
left join payment
    on payment.rental_id = rental.rental_id
where
    payment.amount is not null
group by
    category.name,
    film.title
order by
    sum(payment.amount) desc
limit 5;
    |   name     |       title       | gross revenue|
|------------|-------------------|--------------|
|Music       | TELEGRAPH VOYAGE  |        231.73|
|Documentary | WIFE TURN         |        223.69|
|Comedy      | ZORRO ARK         |        214.69|
|Sci-Fi      | GOODFELLAS SALUTE |        209.69|
|Sports      | SATURDAY LAMBS    |        204.72|

  • The film.description has the text type, allowing for full text search queries, select all descriptions with the words "documentary" and "robot"

    select
    film.title,
    film.description
from
    film
where
    to_tsvector(film.description) @@ to_tsquery('documentary & robot');
    |  title          |                                                    description                                                     |
|-----------------|--------------------------------------------------------------------------------------------------------------------|
|CASPER DRAGONFLY | A Intrepid Documentary of a Boat And a Crocodile who must Chase a Robot in The Sahara Desert                       |
|CHAINSAW UPTOWN  | A Beautiful Documentary of a Boy And a Robot who must Discover a Squirrel in Australia                             |
|CONTROL ANTHEM   | A Fateful Documentary of a Robot And a Student who must Battle a Cat in A Monastery                                |
|CROSSING DIVORCE | A Beautiful Documentary of a Dog And a Robot who must Redeem a Womanizer in Berlin                                 |
|KANE EXORCIST    | A Epic Documentary of a Composer And a Robot who must Overcome a Car in Berlin                                     |
|RUNNER MADIGAN   | A Thoughtful Documentary of a Crocodile And a Robot who must Outrace a Womanizer in The Outback                    |
|SOUTH WAIT       | A Amazing Documentary of a Car And a Robot who must Escape a Lumberjack in An Abandoned Amusement Park             |
|SWEDEN SHINING   | A Taut Documentary of a Car And a Robot who must Conquer a Boy in The Canadian Rockies                             |
|VIRGIN DAISY     | A Awe-Inspiring Documentary of a Robot And a Mad Scientist who must Reach a Database Administrator in A Shark Tank |

Clean Up

To clean up the environment and destroy the database, run the following commands:

\c postgres;
DROP DATABASE mogila;
HOME

Issue
Copyright © 2011-2024 www.enmotech.com All rights reserved.