"Fossies" - the Fresh Open Source Software Archive

Member "nocodb-0.83.5/packages/nocodb/tests/sqlite-sakila-db/01-sqlite-sakila-schema.sql" (27 Nov 2021, 17993 Bytes) of package /linux/misc/nocodb-0.83.5.tar.gz:


As a special service "Fossies" has tried to format the requested source page into HTML format using (guessed) PL/SQL source code syntax highlighting (style: standard) with prefixed line numbers. Alternatively you can here view or download the uninterpreted source code file.

    1 /*
    2 
    3 Sakila for SQLite is a port of the Sakila example database available for MySQL, which was originally developed by Mike Hillyer of the MySQL AB documentation team. 
    4 This project is designed to help database administrators to decide which database to use for development of new products
    5 The user can run the same SQL against different kind of databases and compare the performance
    6 
    7 License: BSD
    8 Copyright DB Software Laboratory
    9 http://www.etl-tools.com
   10 
   11 */
   12 
   13 --
   14 -- Table structure for table actor
   15 --
   16 --DROP TABLE actor;
   17 
   18 
   19 select * from sqlite_master where type = 'trigger' and tbl_name='actor'
   20 
   21 
   22 SELECT * FROM sqlite_master WHERE type = 'view'
   23 
   24 
   25 
   26 CREATE TABLE actor (
   27   actor_id numeric NOT NULL ,
   28   first_name VARCHAR(45) NOT NULL,
   29   last_name VARCHAR(45) NOT NULL,
   30   last_update TIMESTAMP NOT NULL,
   31   PRIMARY KEY  (actor_id)
   32   )  ;
   33 
   34 CREATE  INDEX idx_actor_last_name ON actor(last_name)
   35 ;
   36  
   37 CREATE TRIGGER actor_trigger_ai AFTER INSERT ON actor
   38  BEGIN
   39   UPDATE actor SET last_update = DATETIME('NOW')  WHERE rowid = new.rowid;
   40  END
   41 ;
   42  
   43 CREATE TRIGGER actor_trigger_au AFTER UPDATE ON actor
   44  BEGIN
   45   UPDATE actor SET last_update = DATETIME('NOW')  WHERE rowid = new.rowid;
   46  END
   47 ;
   48 
   49  --
   50 -- Table structure for table country
   51 --
   52 
   53 CREATE TABLE country (
   54   country_id SMALLINT NOT NULL,
   55   country VARCHAR(50) NOT NULL,
   56   last_update TIMESTAMP,
   57   PRIMARY KEY  (country_id)
   58 )
   59 ;
   60 
   61 CREATE TRIGGER country_trigger_ai AFTER INSERT ON country
   62  BEGIN
   63   UPDATE country SET last_update = DATETIME('NOW')  WHERE rowid = new.rowid;
   64  END
   65 ;
   66  
   67 CREATE TRIGGER country_trigger_au AFTER UPDATE ON country
   68  BEGIN
   69   UPDATE country SET last_update = DATETIME('NOW')  WHERE rowid = new.rowid;
   70  END
   71 ;
   72 
   73 --
   74 -- Table structure for table city
   75 --
   76 
   77 CREATE TABLE city (
   78   city_id int NOT NULL,
   79   city VARCHAR(50) NOT NULL,
   80   country_id SMALLINT NOT NULL,
   81   last_update TIMESTAMP NOT NULL,
   82   PRIMARY KEY  (city_id),
   83   CONSTRAINT fk_city_country FOREIGN KEY (country_id) REFERENCES country (country_id) ON DELETE NO ACTION ON UPDATE CASCADE
   84 )
   85 ;
   86 CREATE  INDEX idx_fk_country_id ON city(country_id)
   87 ;
   88 
   89 CREATE TRIGGER city_trigger_ai AFTER INSERT ON city
   90  BEGIN
   91   UPDATE city SET last_update = DATETIME('NOW')  WHERE rowid = new.rowid;
   92  END
   93 ;
   94  
   95 CREATE TRIGGER city_trigger_au AFTER UPDATE ON city
   96  BEGIN
   97   UPDATE city SET last_update = DATETIME('NOW')  WHERE rowid = new.rowid;
   98  END
   99 ;
  100 
  101 --
  102 -- Table structure for table address
  103 --
  104 
  105 CREATE TABLE address (
  106   address_id int NOT NULL,
  107   address VARCHAR(50) NOT NULL,
  108   address2 VARCHAR(50) DEFAULT NULL,
  109   district VARCHAR(20) NOT NULL,
  110   city_id INT  NOT NULL,
  111   postal_code VARCHAR(10) DEFAULT NULL,
  112   phone VARCHAR(20) NOT NULL,
  113   last_update TIMESTAMP NOT NULL,
  114   PRIMARY KEY  (address_id),
  115   CONSTRAINT fk_address_city FOREIGN KEY (city_id) REFERENCES city (city_id) ON DELETE NO ACTION ON UPDATE CASCADE
  116 )
  117 ;
  118 
  119 CREATE  INDEX idx_fk_city_id ON address(city_id)
  120 ;
  121 
  122 CREATE TRIGGER address_trigger_ai AFTER INSERT ON address
  123  BEGIN
  124   UPDATE address SET last_update = DATETIME('NOW')  WHERE rowid = new.rowid;
  125  END
  126 ;
  127  
  128 CREATE TRIGGER address_trigger_au AFTER UPDATE ON address
  129  BEGIN
  130   UPDATE address SET last_update = DATETIME('NOW')  WHERE rowid = new.rowid;
  131  END
  132 ;
  133 
  134 --
  135 -- Table structure for table language
  136 --
  137 
  138 CREATE TABLE language (
  139   language_id SMALLINT NOT NULL ,
  140   name CHAR(20) NOT NULL,
  141   last_update TIMESTAMP NOT NULL,
  142   PRIMARY KEY (language_id)
  143 )
  144 ;
  145 
  146 CREATE TRIGGER language_trigger_ai AFTER INSERT ON language
  147  BEGIN
  148   UPDATE language SET last_update = DATETIME('NOW')  WHERE rowid = new.rowid;
  149  END
  150 ;
  151  
  152 CREATE TRIGGER language_trigger_au AFTER UPDATE ON language
  153  BEGIN
  154   UPDATE language SET last_update = DATETIME('NOW')  WHERE rowid = new.rowid;
  155  END
  156 ;
  157 
  158 --
  159 -- Table structure for table category
  160 --
  161 
  162 CREATE TABLE category (
  163   category_id SMALLINT NOT NULL,
  164   name VARCHAR(25) NOT NULL,
  165   last_update TIMESTAMP NOT NULL,
  166   PRIMARY KEY  (category_id)
  167 );
  168 
  169 CREATE TRIGGER category_trigger_ai AFTER INSERT ON category
  170  BEGIN
  171   UPDATE category SET last_update = DATETIME('NOW')  WHERE rowid = new.rowid;
  172  END
  173 ;
  174  
  175 CREATE TRIGGER category_trigger_au AFTER UPDATE ON category
  176  BEGIN
  177   UPDATE category SET last_update = DATETIME('NOW')  WHERE rowid = new.rowid;
  178  END
  179 ;
  180 
  181 --
  182 -- Table structure for table customer
  183 --
  184 
  185 CREATE TABLE customer (
  186   customer_id INT NOT NULL,
  187   store_id INT NOT NULL,
  188   first_name VARCHAR(45) NOT NULL,
  189   last_name VARCHAR(45) NOT NULL,
  190   email VARCHAR(50) DEFAULT NULL,
  191   address_id INT NOT NULL,
  192   active CHAR(1) DEFAULT 'Y' NOT NULL,
  193   create_date TIMESTAMP NOT NULL,
  194   last_update TIMESTAMP NOT NULL,
  195   PRIMARY KEY  (customer_id),
  196   CONSTRAINT fk_customer_store FOREIGN KEY (store_id) REFERENCES store (store_id) ON DELETE NO ACTION ON UPDATE CASCADE,
  197   CONSTRAINT fk_customer_address FOREIGN KEY (address_id) REFERENCES address (address_id) ON DELETE NO ACTION ON UPDATE CASCADE
  198 )
  199 ;
  200 
  201 CREATE  INDEX idx_customer_fk_store_id ON customer(store_id)
  202 ;
  203 CREATE  INDEX idx_customer_fk_address_id ON customer(address_id)
  204 ;
  205 CREATE  INDEX idx_customer_last_name ON customer(last_name)
  206 ;
  207 
  208 CREATE TRIGGER customer_trigger_ai AFTER INSERT ON customer
  209  BEGIN
  210   UPDATE customer SET last_update = DATETIME('NOW')  WHERE rowid = new.rowid;
  211  END
  212 ;
  213  
  214 CREATE TRIGGER customer_trigger_au AFTER UPDATE ON customer
  215  BEGIN
  216   UPDATE customer SET last_update = DATETIME('NOW')  WHERE rowid = new.rowid;
  217  END
  218 ;
  219 
  220 --
  221 -- Table structure for table film
  222 --
  223 
  224 CREATE TABLE film (
  225   film_id int NOT NULL,
  226   title VARCHAR(255) NOT NULL,
  227   description BLOB SUB_TYPE TEXT DEFAULT NULL,
  228   release_year VARCHAR(4) DEFAULT NULL,
  229   language_id SMALLINT NOT NULL,
  230   original_language_id SMALLINT DEFAULT NULL,
  231   rental_duration SMALLINT  DEFAULT 3 NOT NULL,
  232   rental_rate DECIMAL(4,2) DEFAULT 4.99 NOT NULL,
  233   length SMALLINT DEFAULT NULL,
  234   replacement_cost DECIMAL(5,2) DEFAULT 19.99 NOT NULL,
  235   rating VARCHAR(10) DEFAULT 'G',
  236   special_features VARCHAR(100) DEFAULT NULL,
  237   last_update TIMESTAMP NOT NULL,
  238   PRIMARY KEY  (film_id),
  239   CONSTRAINT CHECK_special_features CHECK(special_features is null or
  240                                                            special_features like '%Trailers%' or
  241                                                            special_features like '%Commentaries%' or
  242                                                            special_features like '%Deleted Scenes%' or
  243                                                            special_features like '%Behind the Scenes%'),
  244   CONSTRAINT CHECK_special_rating CHECK(rating in ('G','PG','PG-13','R','NC-17')),
  245   CONSTRAINT fk_film_language FOREIGN KEY (language_id) REFERENCES language (language_id) ,
  246   CONSTRAINT fk_film_language_original FOREIGN KEY (original_language_id) REFERENCES language (language_id)
  247 )
  248 ;
  249 CREATE  INDEX idx_fk_language_id ON film(language_id)
  250 ;
  251 CREATE  INDEX idx_fk_original_language_id ON film(original_language_id)
  252 ;
  253 
  254 CREATE TRIGGER film_trigger_ai AFTER INSERT ON film
  255  BEGIN
  256   UPDATE film SET last_update = DATETIME('NOW')  WHERE rowid = new.rowid;
  257  END
  258 ;
  259  
  260 CREATE TRIGGER film_trigger_au AFTER UPDATE ON film
  261  BEGIN
  262   UPDATE film SET last_update = DATETIME('NOW')  WHERE rowid = new.rowid;
  263  END
  264 ;
  265 
  266 --
  267 -- Table structure for table film_actor
  268 --
  269 
  270 CREATE TABLE film_actor (
  271   actor_id INT NOT NULL,
  272   film_id  INT NOT NULL,
  273   last_update TIMESTAMP NOT NULL,
  274   PRIMARY KEY  (actor_id,film_id),
  275   CONSTRAINT fk_film_actor_actor FOREIGN KEY (actor_id) REFERENCES actor (actor_id) ON DELETE NO ACTION ON UPDATE CASCADE,
  276   CONSTRAINT fk_film_actor_film FOREIGN KEY (film_id) REFERENCES film (film_id) ON DELETE NO ACTION ON UPDATE CASCADE
  277 )
  278 ;
  279 
  280 CREATE  INDEX idx_fk_film_actor_film ON film_actor(film_id)
  281 ;
  282 
  283 CREATE  INDEX idx_fk_film_actor_actor ON film_actor(actor_id) 
  284 ;
  285 
  286 CREATE TRIGGER film_actor_trigger_ai AFTER INSERT ON film_actor
  287  BEGIN
  288   UPDATE film_actor SET last_update = DATETIME('NOW')  WHERE rowid = new.rowid;
  289  END
  290 ;
  291  
  292 CREATE TRIGGER film_actor_trigger_au AFTER UPDATE ON film_actor
  293  BEGIN
  294   UPDATE film_actor SET last_update = DATETIME('NOW')  WHERE rowid = new.rowid;
  295  END
  296 ;
  297 
  298 
  299 --
  300 -- Table structure for table film_category
  301 --
  302 
  303 CREATE TABLE film_category (
  304   film_id INT NOT NULL,
  305   category_id SMALLINT  NOT NULL,
  306   last_update TIMESTAMP NOT NULL,
  307   PRIMARY KEY (film_id, category_id),
  308   CONSTRAINT fk_film_category_film FOREIGN KEY (film_id) REFERENCES film (film_id) ON DELETE NO ACTION ON UPDATE CASCADE,
  309   CONSTRAINT fk_film_category_category FOREIGN KEY (category_id) REFERENCES category (category_id) ON DELETE NO ACTION ON UPDATE CASCADE
  310 )
  311 ;
  312 
  313 CREATE  INDEX idx_fk_film_category_film ON film_category(film_id)
  314 ;
  315 
  316 CREATE  INDEX idx_fk_film_category_category ON film_category(category_id)
  317 ;
  318 
  319 CREATE TRIGGER film_category_trigger_ai AFTER INSERT ON film_category
  320  BEGIN
  321   UPDATE film_category SET last_update = DATETIME('NOW')  WHERE rowid = new.rowid;
  322  END
  323 ;
  324  
  325 CREATE TRIGGER film_category_trigger_au AFTER UPDATE ON film_category
  326  BEGIN
  327   UPDATE film_category SET last_update = DATETIME('NOW')  WHERE rowid = new.rowid;
  328  END
  329 ;
  330 
  331 --
  332 -- Table structure for table film_text
  333 --
  334 
  335 CREATE TABLE film_text (
  336   film_id SMALLINT NOT NULL,
  337   title VARCHAR(255) NOT NULL,
  338   description BLOB SUB_TYPE TEXT,
  339   PRIMARY KEY  (film_id)
  340 )
  341 ;
  342 
  343 --
  344 -- Table structure for table inventory
  345 --
  346 
  347 CREATE TABLE inventory (
  348   inventory_id INT NOT NULL,
  349   film_id INT NOT NULL,
  350   store_id INT NOT NULL,
  351   last_update TIMESTAMP NOT NULL,
  352   PRIMARY KEY  (inventory_id),
  353   CONSTRAINT fk_inventory_store FOREIGN KEY (store_id) REFERENCES store (store_id) ON DELETE NO ACTION ON UPDATE CASCADE,
  354   CONSTRAINT fk_inventory_film FOREIGN KEY (film_id) REFERENCES film (film_id) ON DELETE NO ACTION ON UPDATE CASCADE
  355 )
  356 ;
  357 
  358 CREATE  INDEX idx_fk_film_id ON inventory(film_id)
  359 ;
  360 
  361 CREATE  INDEX idx_fk_film_id_store_id ON inventory(store_id,film_id)
  362 ;
  363 
  364 CREATE TRIGGER inventory_trigger_ai AFTER INSERT ON inventory
  365  BEGIN
  366   UPDATE inventory SET last_update = DATETIME('NOW')  WHERE rowid = new.rowid;
  367  END
  368 ;
  369  
  370 CREATE TRIGGER inventory_trigger_au AFTER UPDATE ON inventory
  371  BEGIN
  372   UPDATE inventory SET last_update = DATETIME('NOW')  WHERE rowid = new.rowid;
  373  END
  374 ;
  375 
  376 --
  377 -- Table structure for table staff
  378 --
  379 
  380 CREATE TABLE staff (
  381   staff_id SMALLINT NOT NULL,
  382   first_name VARCHAR(45) NOT NULL,
  383   last_name VARCHAR(45) NOT NULL,
  384   address_id INT NOT NULL,
  385   picture BLOB DEFAULT NULL,
  386   email VARCHAR(50) DEFAULT NULL,
  387   store_id INT NOT NULL,
  388   active SMALLINT DEFAULT 1 NOT NULL,
  389   username VARCHAR(16) NOT NULL,
  390   password VARCHAR(40) DEFAULT NULL,
  391   last_update TIMESTAMP NOT NULL,
  392   PRIMARY KEY  (staff_id),
  393   CONSTRAINT fk_staff_store FOREIGN KEY (store_id) REFERENCES store (store_id) ON DELETE NO ACTION ON UPDATE CASCADE,
  394   CONSTRAINT fk_staff_address FOREIGN KEY (address_id) REFERENCES address (address_id) ON DELETE NO ACTION ON UPDATE CASCADE
  395 )
  396 ;
  397 CREATE  INDEX idx_fk_staff_store_id ON staff(store_id)
  398 ;
  399 
  400 CREATE  INDEX idx_fk_staff_address_id ON staff(address_id)
  401 ;
  402 
  403 CREATE TRIGGER staff_trigger_ai AFTER INSERT ON staff
  404  BEGIN
  405   UPDATE staff SET last_update = DATETIME('NOW')  WHERE rowid = new.rowid;
  406  END
  407 ;
  408  
  409 CREATE TRIGGER staff_trigger_au AFTER UPDATE ON staff
  410  BEGIN
  411   UPDATE staff SET last_update = DATETIME('NOW')  WHERE rowid = new.rowid;
  412  END
  413 ;
  414 
  415 --
  416 -- Table structure for table store
  417 --
  418 
  419 CREATE TABLE store (
  420   store_id INT NOT NULL,
  421   manager_staff_id SMALLINT NOT NULL,
  422   address_id INT NOT NULL,
  423   last_update TIMESTAMP NOT NULL,
  424   PRIMARY KEY  (store_id),
  425   CONSTRAINT fk_store_staff FOREIGN KEY (manager_staff_id) REFERENCES staff (staff_id) ,
  426   CONSTRAINT fk_store_address FOREIGN KEY (address_id) REFERENCES address (address_id)
  427 )
  428 ;
  429 
  430 CREATE  INDEX idx_store_fk_manager_staff_id ON store(manager_staff_id)
  431 ;
  432 
  433 CREATE  INDEX idx_fk_store_address ON store(address_id)
  434 ;
  435 
  436 CREATE TRIGGER store_trigger_ai AFTER INSERT ON store
  437  BEGIN
  438   UPDATE store SET last_update = DATETIME('NOW')  WHERE rowid = new.rowid;
  439  END
  440 ;
  441  
  442 CREATE TRIGGER store_trigger_au AFTER UPDATE ON store
  443  BEGIN
  444   UPDATE store SET last_update = DATETIME('NOW')  WHERE rowid = new.rowid;
  445  END
  446 ;
  447 
  448 --
  449 -- Table structure for table payment
  450 --
  451 
  452 CREATE TABLE payment (
  453   payment_id int NOT NULL,
  454   customer_id INT  NOT NULL,
  455   staff_id SMALLINT NOT NULL,
  456   rental_id INT DEFAULT NULL,
  457   amount DECIMAL(5,2) NOT NULL,
  458   payment_date TIMESTAMP NOT NULL,
  459   last_update TIMESTAMP NOT NULL,
  460   PRIMARY KEY  (payment_id),
  461   CONSTRAINT fk_payment_rental FOREIGN KEY (rental_id) REFERENCES rental (rental_id) ON DELETE SET NULL ON UPDATE CASCADE,
  462   CONSTRAINT fk_payment_customer FOREIGN KEY (customer_id) REFERENCES customer (customer_id) ,
  463   CONSTRAINT fk_payment_staff FOREIGN KEY (staff_id) REFERENCES staff (staff_id)
  464 )
  465 ;
  466 CREATE  INDEX idx_fk_staff_id ON payment(staff_id)
  467 ;
  468 CREATE  INDEX idx_fk_customer_id ON payment(customer_id)
  469 ;
  470 
  471 CREATE TRIGGER payment_trigger_ai AFTER INSERT ON payment
  472  BEGIN
  473   UPDATE payment SET last_update = DATETIME('NOW')  WHERE rowid = new.rowid;
  474  END
  475 ;
  476  
  477 CREATE TRIGGER payment_trigger_au AFTER UPDATE ON payment
  478  BEGIN
  479   UPDATE payment SET last_update = DATETIME('NOW')  WHERE rowid = new.rowid;
  480  END
  481 ;
  482 
  483 CREATE TABLE rental (
  484   rental_id INT NOT NULL,
  485   rental_date TIMESTAMP NOT NULL,
  486   inventory_id INT  NOT NULL,
  487   customer_id INT  NOT NULL,
  488   return_date TIMESTAMP DEFAULT NULL,
  489   staff_id SMALLINT  NOT NULL,
  490   last_update TIMESTAMP NOT NULL,
  491   PRIMARY KEY (rental_id),
  492   CONSTRAINT fk_rental_staff FOREIGN KEY (staff_id) REFERENCES staff (staff_id) ,
  493   CONSTRAINT fk_rental_inventory FOREIGN KEY (inventory_id) REFERENCES inventory (inventory_id) ,
  494   CONSTRAINT fk_rental_customer FOREIGN KEY (customer_id) REFERENCES customer (customer_id)
  495 )
  496 ;
  497 CREATE INDEX idx_rental_fk_inventory_id ON rental(inventory_id)
  498 ;
  499 CREATE INDEX idx_rental_fk_customer_id ON rental(customer_id)
  500 ;
  501 CREATE INDEX idx_rental_fk_staff_id ON rental(staff_id)
  502 ;
  503 CREATE UNIQUE INDEX   idx_rental_uq  ON rental (rental_date,inventory_id,customer_id)
  504 ;
  505 
  506 CREATE TRIGGER rental_trigger_ai AFTER INSERT ON rental
  507  BEGIN
  508   UPDATE rental SET last_update = DATETIME('NOW')  WHERE rowid = new.rowid;
  509  END
  510 ;
  511  
  512 CREATE TRIGGER rental_trigger_au AFTER UPDATE ON rental
  513  BEGIN
  514   UPDATE rental SET last_update = DATETIME('NOW')  WHERE rowid = new.rowid;
  515  END
  516 ;
  517 --
  518 -- View structure for view customer_list
  519 --
  520 
  521 CREATE VIEW customer_list
  522 AS
  523 SELECT cu.customer_id AS ID,
  524        cu.first_name||' '||cu.last_name AS name,
  525        a.address AS address,
  526        a.postal_code AS zip_code,
  527        a.phone AS phone,
  528        city.city AS city,
  529        country.country AS country,
  530        case when cu.active=1 then 'active' else '' end AS notes,
  531        cu.store_id AS SID
  532 FROM customer AS cu JOIN address AS a ON cu.address_id = a.address_id JOIN city ON a.city_id = city.city_id
  533     JOIN country ON city.country_id = country.country_id
  534 ;
  535 --
  536 -- View structure for view film_list
  537 --
  538 
  539 CREATE VIEW film_list
  540 AS
  541 SELECT film.film_id AS FID,
  542        film.title AS title,
  543        film.description AS description,
  544        category.name AS category,
  545        film.rental_rate AS price,
  546        film.length AS length,
  547        film.rating AS rating,
  548        actor.first_name||' '||actor.last_name AS actors
  549 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
  550         JOIN film_actor ON film.film_id = film_actor.film_id
  551     JOIN actor ON film_actor.actor_id = actor.actor_id
  552 ;
  553 
  554 --
  555 -- View structure for view staff_list
  556 --
  557 
  558 CREATE VIEW staff_list
  559 AS
  560 SELECT s.staff_id AS ID,
  561        s.first_name||' '||s.last_name AS name,
  562        a.address AS address,
  563        a.postal_code AS zip_code,
  564        a.phone AS phone,
  565        city.city AS city,
  566        country.country AS country,
  567        s.store_id AS SID
  568 FROM staff AS s JOIN address AS a ON s.address_id = a.address_id JOIN city ON a.city_id = city.city_id
  569     JOIN country ON city.country_id = country.country_id
  570 ;
  571 --
  572 -- View structure for view sales_by_store
  573 --
  574 
  575 CREATE VIEW sales_by_store
  576 AS
  577 SELECT
  578   s.store_id
  579  ,c.city||','||cy.country AS store
  580  ,m.first_name||' '||m.last_name AS manager
  581  ,SUM(p.amount) AS total_sales
  582 FROM payment AS p
  583 INNER JOIN rental AS r ON p.rental_id = r.rental_id
  584 INNER JOIN inventory AS i ON r.inventory_id = i.inventory_id
  585 INNER JOIN store AS s ON i.store_id = s.store_id
  586 INNER JOIN address AS a ON s.address_id = a.address_id
  587 INNER JOIN city AS c ON a.city_id = c.city_id
  588 INNER JOIN country AS cy ON c.country_id = cy.country_id
  589 INNER JOIN staff AS m ON s.manager_staff_id = m.staff_id
  590 GROUP BY  
  591   s.store_id
  592 , c.city||','||cy.country
  593 , m.first_name||' '||m.last_name
  594 ;
  595 --
  596 -- View structure for view sales_by_film_category
  597 --
  598 -- Note that total sales will add up to >100% because
  599 -- some titles belong to more than 1 category
  600 --
  601 
  602 CREATE VIEW sales_by_film_category
  603 AS
  604 SELECT
  605 c.name AS category
  606 , SUM(p.amount) AS total_sales
  607 FROM payment AS p
  608 INNER JOIN rental AS r ON p.rental_id = r.rental_id
  609 INNER JOIN inventory AS i ON r.inventory_id = i.inventory_id
  610 INNER JOIN film AS f ON i.film_id = f.film_id
  611 INNER JOIN film_category AS fc ON f.film_id = fc.film_id
  612 INNER JOIN category AS c ON fc.category_id = c.category_id
  613 GROUP BY c.name
  614 ;
  615 
  616 --
  617 -- View structure for view actor_info
  618 --
  619 
  620 /*
  621 CREATE VIEW actor_info
  622 AS
  623 SELECT
  624 a.actor_id,
  625 a.first_name,
  626 a.last_name,
  627 GROUP_CONCAT(DISTINCT CONCAT(c.name, ': ',
  628         (SELECT GROUP_CONCAT(f.title ORDER BY f.title SEPARATOR ', ')
  629                     FROM sakila.film f
  630                     INNER JOIN sakila.film_category fc
  631                       ON f.film_id = fc.film_id
  632                     INNER JOIN sakila.film_actor fa
  633                       ON f.film_id = fa.film_id
  634                     WHERE fc.category_id = c.category_id
  635                     AND fa.actor_id = a.actor_id
  636                  )
  637              )
  638              ORDER BY c.name SEPARATOR '; ')
  639 AS film_info
  640 FROM sakila.actor a
  641 LEFT JOIN sakila.film_actor fa
  642   ON a.actor_id = fa.actor_id
  643 LEFT JOIN sakila.film_category fc
  644   ON fa.film_id = fc.film_id
  645 LEFT JOIN sakila.category c
  646   ON fc.category_id = c.category_id
  647 GROUP BY a.actor_id, a.first_name, a.last_name;
  648 */
  649 
  650 -- TO DO PROCEDURES
  651 -- TO DO TRIGGERS
  652