"Fossies" - the Fresh Open Source Software Archive

Member "nocodb-0.83.5/packages/nocodb/tests/sql-server-sakila-db/01-sql-server-sakila-schema.sql" (27 Nov 2021, 15401 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 Microsoft SQL Server 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 CREATE DATABASE sakila;
   14 GO
   15 USE sakila;
   16 
   17 --
   18 -- Table structure for table actor
   19 --
   20 
   21 CREATE TABLE actor (
   22   actor_id int NOT NULL IDENTITY ,
   23   first_name VARCHAR(45) NOT NULL,
   24   last_name VARCHAR(45) NOT NULL,
   25   last_update DATETIME NOT NULL,
   26   PRIMARY KEY NONCLUSTERED (actor_id)
   27   )
   28 GO
   29  ALTER TABLE actor ADD CONSTRAINT [DF_actor_last_update] DEFAULT (getdate()) FOR last_update
   30 GO
   31  CREATE  INDEX idx_actor_last_name ON actor(last_name)
   32 GO
   33 
   34 --
   35 -- Table structure for table country
   36 --
   37 
   38 
   39 CREATE TABLE country (
   40   country_id SMALLINT NOT NULL IDENTITY ,
   41   country VARCHAR(50) NOT NULL,
   42   last_update DATETIME,
   43   PRIMARY KEY NONCLUSTERED (country_id)
   44 )
   45 GO
   46  ALTER TABLE country ADD CONSTRAINT [DF_country_last_update] DEFAULT (getdate()) FOR last_update
   47 GO
   48 
   49 --
   50 -- Table structure for table city
   51 --
   52 
   53 CREATE TABLE city (
   54   city_id int NOT NULL IDENTITY ,
   55   city VARCHAR(50) NOT NULL,
   56   country_id SMALLINT NOT NULL,
   57   last_update DATETIME NOT NULL,
   58   PRIMARY KEY NONCLUSTERED (city_id),
   59   CONSTRAINT fk_city_country FOREIGN KEY (country_id) REFERENCES country (country_id) ON DELETE NO ACTION ON UPDATE CASCADE
   60 )
   61 GO
   62  ALTER TABLE city ADD CONSTRAINT [DF_city_last_update] DEFAULT (getdate()) FOR last_update
   63 GO
   64  CREATE  INDEX idx_fk_country_id ON city(country_id)
   65 GO
   66 
   67 --
   68 -- Table structure for table address
   69 --
   70 
   71 CREATE TABLE address (
   72   address_id int NOT NULL IDENTITY ,
   73   address VARCHAR(50) NOT NULL,
   74   address2 VARCHAR(50) DEFAULT NULL,
   75   district VARCHAR(20) NOT NULL,
   76   city_id INT  NOT NULL,
   77   postal_code VARCHAR(10) DEFAULT NULL,
   78   phone VARCHAR(20) NOT NULL,
   79   last_update DATETIME NOT NULL,
   80   PRIMARY KEY NONCLUSTERED (address_id)
   81 )
   82 GO
   83 ALTER TABLE address ADD CONSTRAINT [DF_address_last_update] DEFAULT (getdate()) FOR last_update
   84 GO
   85 CREATE  INDEX idx_fk_city_id ON address(city_id)
   86 GO
   87 ALTER TABLE address ADD  CONSTRAINT fk_address_city FOREIGN KEY (city_id) REFERENCES city (city_id) ON DELETE NO ACTION ON UPDATE CASCADE
   88 GO
   89 
   90 --
   91 -- Table structure for table language
   92 --
   93 
   94 CREATE TABLE language (
   95   language_id TINYINT NOT NULL IDENTITY,
   96   name CHAR(20) NOT NULL,
   97   last_update DATETIME NOT NULL,
   98   PRIMARY KEY NONCLUSTERED (language_id)
   99 )
  100 GO
  101  ALTER TABLE language ADD CONSTRAINT [DF_language_last_update] DEFAULT (getdate()) FOR last_update
  102 GO
  103 
  104 --
  105 -- Table structure for table category
  106 --
  107 
  108 CREATE TABLE category (
  109   category_id TINYINT NOT NULL IDENTITY,
  110   name VARCHAR(25) NOT NULL,
  111   last_update DATETIME NOT NULL,
  112   PRIMARY KEY NONCLUSTERED (category_id)
  113 )
  114 GO
  115  ALTER TABLE category ADD CONSTRAINT [DF_category_last_update] DEFAULT (getdate()) FOR last_update
  116 GO
  117 
  118 --
  119 -- Table structure for table customer
  120 --
  121 
  122 CREATE TABLE customer (
  123   customer_id INT NOT NULL IDENTITY ,
  124   store_id INT NOT NULL,
  125   first_name VARCHAR(45) NOT NULL,
  126   last_name VARCHAR(45) NOT NULL,
  127   email VARCHAR(50) DEFAULT NULL,
  128   address_id INT NOT NULL,
  129   active CHAR(1) NOT NULL DEFAULT 'Y',
  130   create_date DATETIME NOT NULL,
  131   last_update DATETIME NOT NULL,
  132   PRIMARY KEY NONCLUSTERED (customer_id),
  133   CONSTRAINT fk_customer_address FOREIGN KEY (address_id) REFERENCES address (address_id) ON DELETE NO ACTION ON UPDATE CASCADE
  134 )
  135 GO
  136  ALTER TABLE customer ADD CONSTRAINT [DF_customer_last_update] DEFAULT (getdate()) FOR last_update
  137 GO
  138  ALTER TABLE customer ADD CONSTRAINT [DF_customer_create_date] DEFAULT (getdate()) FOR create_date
  139 GO
  140  CREATE  INDEX idx_fk_store_id ON customer(store_id)
  141 GO
  142  CREATE  INDEX idx_fk_address_id ON customer(address_id)
  143 GO
  144  CREATE  INDEX idx_last_name ON customer(last_name)
  145 GO
  146 
  147 --
  148 -- Table structure for table film
  149 --
  150 
  151 CREATE TABLE film (
  152   film_id int NOT NULL IDENTITY ,
  153   title VARCHAR(255) NOT NULL,
  154   description TEXT DEFAULT NULL,
  155   release_year VARCHAR(4) NULL,
  156   language_id TINYINT NOT NULL,
  157   original_language_id TINYINT DEFAULT NULL,
  158   rental_duration TINYINT NOT NULL DEFAULT 3,
  159   rental_rate DECIMAL(4,2) NOT NULL DEFAULT 4.99,
  160   length SMALLINT DEFAULT NULL,
  161   replacement_cost DECIMAL(5,2) NOT NULL DEFAULT 19.99,
  162   rating VARCHAR(10) DEFAULT 'G',
  163   special_features VARCHAR(255) DEFAULT NULL,
  164   last_update DATETIME NOT NULL,
  165   PRIMARY KEY NONCLUSTERED (film_id),
  166   CONSTRAINT fk_film_language FOREIGN KEY (language_id) REFERENCES language (language_id) ,
  167   CONSTRAINT fk_film_language_original FOREIGN KEY (original_language_id) REFERENCES language (language_id)
  168 )
  169 GO
  170 ALTER TABLE film ADD CONSTRAINT CHECK_special_features CHECK(special_features is null or
  171                                                               special_features like '%Trailers%' or
  172                                                               special_features like '%Commentaries%' or
  173                                                               special_features like '%Deleted Scenes%' or
  174                                                               special_features like '%Behind the Scenes%')
  175 GO
  176 ALTER TABLE film ADD CONSTRAINT CHECK_special_rating CHECK(rating in ('G','PG','PG-13','R','NC-17'))
  177 GO
  178 ALTER TABLE film ADD CONSTRAINT [DF_film_last_update] DEFAULT (getdate()) FOR last_update
  179 GO
  180 CREATE  INDEX idx_fk_language_id ON film(language_id)
  181 GO
  182 CREATE  INDEX idx_fk_original_language_id ON film(original_language_id)
  183 GO
  184 
  185 
  186 --
  187 -- Table structure for table film_actor
  188 --
  189 
  190 CREATE TABLE film_actor (
  191   actor_id INT NOT NULL,
  192   film_id  INT NOT NULL,
  193   last_update DATETIME NOT NULL,
  194   PRIMARY KEY NONCLUSTERED (actor_id,film_id),
  195   CONSTRAINT fk_film_actor_actor FOREIGN KEY (actor_id) REFERENCES actor (actor_id) ON DELETE NO ACTION ON UPDATE CASCADE,
  196   CONSTRAINT fk_film_actor_film FOREIGN KEY (film_id) REFERENCES film (film_id) ON DELETE NO ACTION ON UPDATE CASCADE
  197 )
  198 GO
  199  ALTER TABLE film_actor ADD CONSTRAINT [DF_film_actor_last_update] DEFAULT (getdate()) FOR last_update
  200 GO
  201  CREATE  INDEX idx_fk_film_actor_film ON film_actor(film_id)
  202 GO
  203  CREATE  INDEX idx_fk_film_actor_actor ON film_actor(actor_id)
  204 GO
  205 
  206 --
  207 -- Table structure for table film_category
  208 --
  209 
  210 CREATE TABLE film_category (
  211   film_id INT NOT NULL,
  212   category_id TINYINT  NOT NULL,
  213   last_update DATETIME NOT NULL,
  214   PRIMARY KEY NONCLUSTERED (film_id, category_id),
  215   CONSTRAINT fk_film_category_film FOREIGN KEY (film_id) REFERENCES film (film_id) ON DELETE NO ACTION ON UPDATE CASCADE,
  216   CONSTRAINT fk_film_category_category FOREIGN KEY (category_id) REFERENCES category (category_id) ON DELETE NO ACTION ON UPDATE CASCADE
  217 )
  218 GO
  219  ALTER TABLE film_category ADD CONSTRAINT [DF_film_category_last_update] DEFAULT (getdate()) FOR last_update
  220 GO
  221  CREATE  INDEX idx_fk_film_category_film ON film_category(film_id)
  222 GO
  223  CREATE  INDEX idx_fk_film_category_category ON film_category(category_id)
  224 GO
  225 --
  226 -- Table structure for table film_text
  227 --
  228 
  229 CREATE TABLE film_text (
  230   film_id SMALLINT NOT NULL,
  231   title VARCHAR(255) NOT NULL,
  232   description TEXT,
  233   PRIMARY KEY NONCLUSTERED (film_id),
  234 )
  235 
  236 --
  237 -- Table structure for table inventory
  238 --
  239 
  240 CREATE TABLE inventory (
  241   inventory_id INT NOT NULL IDENTITY,
  242   film_id INT NOT NULL,
  243   store_id INT NOT NULL,
  244   last_update DATETIME NOT NULL,
  245   PRIMARY KEY NONCLUSTERED (inventory_id),
  246   CONSTRAINT fk_inventory_film FOREIGN KEY (film_id) REFERENCES film (film_id) ON DELETE NO ACTION ON UPDATE CASCADE
  247 )
  248 GO
  249  ALTER TABLE inventory ADD CONSTRAINT [DF_inventory_last_update] DEFAULT (getdate()) FOR last_update
  250 GO
  251  CREATE  INDEX idx_fk_film_id ON inventory(film_id)
  252 GO
  253  CREATE  INDEX idx_fk_film_id_store_id ON inventory(store_id,film_id)
  254 GO
  255 
  256 --
  257 -- Table structure for table staff
  258 --
  259 
  260 CREATE TABLE staff (
  261   staff_id TINYINT NOT NULL IDENTITY,
  262   first_name VARCHAR(45) NOT NULL,
  263   last_name VARCHAR(45) NOT NULL,
  264   address_id INT NOT NULL,
  265   picture IMAGE DEFAULT NULL,
  266   email VARCHAR(50) DEFAULT NULL,
  267   store_id INT NOT NULL,
  268   active BIT NOT NULL DEFAULT 1,
  269   username VARCHAR(16) NOT NULL,
  270   password VARCHAR(40) DEFAULT NULL,
  271   last_update DATETIME NOT NULL,
  272   PRIMARY KEY NONCLUSTERED (staff_id),
  273   CONSTRAINT fk_staff_address FOREIGN KEY (address_id) REFERENCES address (address_id) ON DELETE NO ACTION ON UPDATE CASCADE
  274 )
  275 GO
  276  ALTER TABLE staff ADD CONSTRAINT [DF_staff_last_update] DEFAULT (getdate()) FOR last_update
  277 GO
  278  CREATE  INDEX idx_fk_store_id ON staff(store_id)
  279 GO
  280  CREATE  INDEX idx_fk_address_id ON staff(address_id)
  281 GO
  282 
  283 --
  284 -- Table structure for table store
  285 --
  286 
  287 CREATE TABLE store (
  288   store_id INT NOT NULL IDENTITY,
  289   manager_staff_id TINYINT NOT NULL,
  290   address_id INT NOT NULL,
  291   last_update DATETIME NOT NULL,
  292   PRIMARY KEY NONCLUSTERED (store_id),
  293   CONSTRAINT fk_store_staff FOREIGN KEY (manager_staff_id) REFERENCES staff (staff_id) ,
  294   CONSTRAINT fk_store_address FOREIGN KEY (address_id) REFERENCES address (address_id)
  295 )
  296 
  297 GO
  298  ALTER TABLE store ADD CONSTRAINT [DF_store_last_update] DEFAULT (getdate()) FOR last_update
  299 GO
  300  CREATE UNIQUE NONCLUSTERED INDEX idx_fk_address_id ON store(manager_staff_id)
  301 GO
  302  CREATE  INDEX idx_fk_store_address ON store(address_id)
  303 GO
  304 
  305 
  306 --
  307 -- Table structure for table payment
  308 --
  309 
  310 CREATE TABLE payment (
  311   payment_id int NOT NULL IDENTITY ,
  312   customer_id INT  NOT NULL,
  313   staff_id TINYINT NOT NULL,
  314   rental_id INT DEFAULT NULL,
  315   amount DECIMAL(5,2) NOT NULL,
  316   payment_date DATETIME NOT NULL,
  317   last_update DATETIME NOT NULL,
  318   PRIMARY KEY NONCLUSTERED (payment_id),
  319   CONSTRAINT fk_payment_customer FOREIGN KEY (customer_id) REFERENCES customer (customer_id) ,
  320   CONSTRAINT fk_payment_staff FOREIGN KEY (staff_id) REFERENCES staff (staff_id)
  321 )
  322 GO
  323  ALTER TABLE payment ADD CONSTRAINT [DF_payment_last_update] DEFAULT (getdate()) FOR last_update
  324 GO
  325  CREATE  INDEX idx_fk_staff_id ON payment(staff_id)
  326 GO
  327  CREATE  INDEX idx_fk_customer_id ON payment(customer_id)
  328 GO
  329 
  330 --
  331 -- Table structure for table rental
  332 --
  333 
  334 CREATE TABLE rental (
  335   rental_id INT NOT NULL IDENTITY,
  336   rental_date DATETIME NOT NULL,
  337   inventory_id INT  NOT NULL,
  338   customer_id INT  NOT NULL,
  339   return_date DATETIME DEFAULT NULL,
  340   staff_id TINYINT  NOT NULL,
  341   last_update DATETIME NOT NULL,
  342   PRIMARY KEY NONCLUSTERED (rental_id),
  343   CONSTRAINT fk_rental_staff FOREIGN KEY (staff_id) REFERENCES staff (staff_id) ,
  344   CONSTRAINT fk_rental_inventory FOREIGN KEY (inventory_id) REFERENCES inventory (inventory_id) ,
  345   CONSTRAINT fk_rental_customer FOREIGN KEY (customer_id) REFERENCES customer (customer_id)
  346 )
  347 GO
  348  ALTER TABLE rental ADD CONSTRAINT [DF_rental_last_update] DEFAULT (getdate()) FOR last_update
  349 GO
  350  CREATE INDEX idx_fk_inventory_id ON rental(inventory_id)
  351 GO
  352  CREATE INDEX idx_fk_customer_id ON rental(customer_id)
  353 GO
  354  CREATE INDEX idx_fk_staff_id ON rental(staff_id)
  355 GO
  356  CREATE UNIQUE INDEX   idx_uq  ON rental (rental_date,inventory_id,customer_id)
  357 GO
  358 
  359 -- FK CONSTRAINTS
  360 ALTER TABLE customer ADD CONSTRAINT fk_customer_store FOREIGN KEY (store_id) REFERENCES store (store_id) ON DELETE NO ACTION ON UPDATE CASCADE
  361 GO
  362 ALTER TABLE inventory ADD CONSTRAINT fk_inventory_store FOREIGN KEY (store_id) REFERENCES store (store_id) ON DELETE NO ACTION ON UPDATE CASCADE;
  363 GO
  364 ALTER TABLE staff ADD CONSTRAINT fk_staff_store FOREIGN KEY (store_id) REFERENCES store (store_id) ON DELETE NO ACTION ON UPDATE CASCADE;
  365 GO
  366 ALTER TABLE payment ADD CONSTRAINT fk_payment_rental FOREIGN KEY (rental_id) REFERENCES rental (rental_id) ON DELETE SET NULL ON UPDATE CASCADE;
  367 GO
  368 
  369 --
  370 -- View structure for view customer_list
  371 --
  372 
  373 CREATE VIEW customer_list
  374 AS
  375 SELECT cu.customer_id AS ID,
  376        cu.first_name + ' ' + cu.last_name AS name,
  377        a.address AS address,
  378        a.postal_code AS zip_code,
  379        a.phone AS phone,
  380        city.city AS city,
  381        country.country AS country,
  382        case when cu.active=1 then 'active' else '' end AS notes,
  383        cu.store_id AS SID
  384 FROM customer AS cu JOIN address AS a ON cu.address_id = a.address_id JOIN city ON a.city_id = city.city_id
  385     JOIN country ON city.country_id = country.country_id
  386 GO
  387 --
  388 -- View structure for view film_list
  389 --
  390 
  391 CREATE VIEW film_list
  392 AS
  393 SELECT film.film_id AS FID,
  394        film.title AS title,
  395        film.description AS description,
  396        category.name AS category,
  397        film.rental_rate AS price,
  398        film.length AS length,
  399        film.rating AS rating,
  400        actor.first_name+' '+actor.last_name AS actors
  401 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
  402         JOIN film_actor ON film.film_id = film_actor.film_id
  403     JOIN actor ON film_actor.actor_id = actor.actor_id
  404 GO
  405 
  406 --
  407 -- View structure for view staff_list
  408 --
  409 
  410 CREATE VIEW staff_list
  411 AS
  412 SELECT s.staff_id AS ID,
  413        s.first_name+' '+s.last_name AS name,
  414        a.address AS address,
  415        a.postal_code AS zip_code,
  416        a.phone AS phone,
  417        city.city AS city,
  418        country.country AS country,
  419        s.store_id AS SID
  420 FROM staff AS s JOIN address AS a ON s.address_id = a.address_id JOIN city ON a.city_id = city.city_id
  421     JOIN country ON city.country_id = country.country_id
  422 GO
  423 --
  424 -- View structure for view sales_by_store
  425 --
  426 
  427 CREATE VIEW sales_by_store
  428 AS
  429 SELECT
  430   s.store_id
  431  ,c.city+','+cy.country AS store
  432  ,m.first_name+' '+ m.last_name AS manager
  433  ,SUM(p.amount) AS total_sales
  434 FROM payment AS p
  435 INNER JOIN rental AS r ON p.rental_id = r.rental_id
  436 INNER JOIN inventory AS i ON r.inventory_id = i.inventory_id
  437 INNER JOIN store AS s ON i.store_id = s.store_id
  438 INNER JOIN address AS a ON s.address_id = a.address_id
  439 INNER JOIN city AS c ON a.city_id = c.city_id
  440 INNER JOIN country AS cy ON c.country_id = cy.country_id
  441 INNER JOIN staff AS m ON s.manager_staff_id = m.staff_id
  442 GROUP BY
  443   s.store_id
  444 , c.city+ ','+cy.country
  445 , m.first_name+' '+ m.last_name
  446 GO
  447 --
  448 -- View structure for view sales_by_film_category
  449 --
  450 -- Note that total sales will add up to >100% because
  451 -- some titles belong to more than 1 category
  452 --
  453 
  454 CREATE VIEW sales_by_film_category
  455 AS
  456 SELECT
  457 c.name AS category
  458 , SUM(p.amount) AS total_sales
  459 FROM payment AS p
  460 INNER JOIN rental AS r ON p.rental_id = r.rental_id
  461 INNER JOIN inventory AS i ON r.inventory_id = i.inventory_id
  462 INNER JOIN film AS f ON i.film_id = f.film_id
  463 INNER JOIN film_category AS fc ON f.film_id = fc.film_id
  464 INNER JOIN category AS c ON fc.category_id = c.category_id
  465 GROUP BY c.name
  466 GO
  467 
  468 --
  469 -- View structure for view actor_info
  470 --
  471 
  472 /*
  473 CREATE VIEW actor_info
  474 AS
  475 SELECT
  476 a.actor_id,
  477 a.first_name,
  478 a.last_name,
  479 GROUP_CONCAT(DISTINCT CONCAT(c.name, ': ',
  480         (SELECT GROUP_CONCAT(f.title ORDER BY f.title SEPARATOR ', ')
  481                     FROM sakila.film f
  482                     INNER JOIN sakila.film_category fc
  483                       ON f.film_id = fc.film_id
  484                     INNER JOIN sakila.film_actor fa
  485                       ON f.film_id = fa.film_id
  486                     WHERE fc.category_id = c.category_id
  487                     AND fa.actor_id = a.actor_id
  488                  )
  489              )
  490              ORDER BY c.name SEPARATOR '; ')
  491 AS film_info
  492 FROM sakila.actor a
  493 LEFT JOIN sakila.film_actor fa
  494   ON a.actor_id = fa.actor_id
  495 LEFT JOIN sakila.film_category fc
  496   ON fa.film_id = fc.film_id
  497 LEFT JOIN sakila.category c
  498   ON fc.category_id = c.category_id
  499 GROUP BY a.actor_id, a.first_name, a.last_name;
  500 */
  501 
  502 -- TO DO PROCEDURES
  503 -- TO DO TRIGGERS
  504