[{"name": "actor", "type": "table", "sql": "CREATE TABLE actor (\n  actor_id numeric NOT NULL ,\n  first_name VARCHAR(45) NOT NULL,\n  last_name VARCHAR(45) NOT NULL,\n  last_update TIMESTAMP NOT NULL,\n  PRIMARY KEY  (actor_id)\n  )"}, {"name": "address", "type": "table", "sql": "CREATE TABLE address (\n  address_id int NOT NULL,\n  address VARCHAR(50) NOT NULL,\n  address2 VARCHAR(50) DEFAULT NULL,\n  district VARCHAR(20) NOT NULL,\n  city_id INT  NOT NULL,\n  postal_code VARCHAR(10) DEFAULT NULL,\n  phone VARCHAR(20) NOT NULL,\n  last_update TIMESTAMP NOT NULL,\n  PRIMARY KEY  (address_id),\n  CONSTRAINT fk_address_city FOREIGN KEY (city_id) REFERENCES city (city_id) ON DELETE NO ACTION ON UPDATE CASCADE\n)"}, {"name": "category", "type": "table", "sql": "CREATE TABLE category (\n  category_id SMALLINT NOT NULL,\n  name VARCHAR(25) NOT NULL,\n  last_update TIMESTAMP NOT NULL,\n  PRIMARY KEY  (category_id)\n)"}, {"name": "city", "type": "table", "sql": "CREATE TABLE city (\n  city_id int NOT NULL,\n  city VARCHAR(50) NOT NULL,\n  country_id SMALLINT NOT NULL,\n  last_update TIMESTAMP NOT NULL,\n  PRIMARY KEY  (city_id),\n  CONSTRAINT fk_city_country FOREIGN KEY (country_id) REFERENCES country (country_id) ON DELETE NO ACTION ON UPDATE CASCADE\n)"}, {"name": "country", "type": "table", "sql": "CREATE TABLE country (\n  country_id SMALLINT NOT NULL,\n  country VARCHAR(50) NOT NULL,\n  last_update TIMESTAMP,\n  PRIMARY KEY  (country_id)\n)"}, {"name": "customer", "type": "table", "sql": "CREATE TABLE customer (\n  customer_id INT NOT NULL,\n  store_id INT NOT NULL,\n  first_name VARCHAR(45) NOT NULL,\n  last_name VARCHAR(45) NOT NULL,\n  email VARCHAR(50) DEFAULT NULL,\n  address_id INT NOT NULL,\n  active CHAR(1) DEFAULT 'Y' NOT NULL,\n  create_date TIMESTAMP NOT NULL,\n  last_update TIMESTAMP NOT NULL,\n  PRIMARY KEY  (customer_id),\n  CONSTRAINT fk_customer_store FOREIGN KEY (store_id) REFERENCES store (store_id) ON DELETE NO ACTION ON UPDATE CASCADE,\n  CONSTRAINT fk_customer_address FOREIGN KEY (address_id) REFERENCES address (address_id) ON DELETE NO ACTION ON UPDATE CASCADE\n)"}, {"name": "customer_list", "type": "view", "sql": "CREATE VIEW customer_list\nAS\nSELECT cu.customer_id AS ID,\n       cu.first_name||' '||cu.last_name AS name,\n       a.address AS address,\n       a.postal_code AS zip_code,\n       a.phone AS phone,\n       city.city AS city,\n       country.country AS country,\n       case when cu.active=1 then 'active' else '' end AS notes,\n       cu.store_id AS SID\nFROM customer AS cu JOIN address AS a ON cu.address_id = a.address_id JOIN city ON a.city_id = city.city_id\n    JOIN country ON city.country_id = country.country_id"}, {"name": "film", "type": "table", "sql": "CREATE TABLE film (\n  film_id int NOT NULL,\n  title VARCHAR(255) NOT NULL,\n  description BLOB SUB_TYPE TEXT DEFAULT NULL,\n  release_year VARCHAR(4) DEFAULT NULL,\n  language_id SMALLINT NOT NULL,\n  original_language_id SMALLINT DEFAULT NULL,\n  rental_duration SMALLINT  DEFAULT 3 NOT NULL,\n  rental_rate DECIMAL(4,2) DEFAULT 4.99 NOT NULL,\n  length SMALLINT DEFAULT NULL,\n  replacement_cost DECIMAL(5,2) DEFAULT 19.99 NOT NULL,\n  rating VARCHAR(10) DEFAULT 'G',\n  special_features VARCHAR(100) DEFAULT NULL,\n  last_update TIMESTAMP NOT NULL,\n  PRIMARY KEY  (film_id),\n  CONSTRAINT CHECK_special_features CHECK(special_features is null or\n                                                           special_features like '%Trailers%' or\n                                                           special_features like '%Commentaries%' or\n                                                           special_features like '%Deleted Scenes%' or\n                                                           special_features like '%Behind the Scenes%'),\n  CONSTRAINT CHECK_special_rating CHECK(rating in ('G','PG','PG-13','R','NC-17')),\n  CONSTRAINT fk_film_language FOREIGN KEY (language_id) REFERENCES language (language_id) ,\n  CONSTRAINT fk_film_language_original FOREIGN KEY (original_language_id) REFERENCES language (language_id)\n)"}, {"name": "film_actor", "type": "table", "sql": "CREATE TABLE film_actor (\n  actor_id INT NOT NULL,\n  film_id  INT NOT NULL,\n  last_update TIMESTAMP NOT NULL,\n  PRIMARY KEY  (actor_id,film_id),\n  CONSTRAINT fk_film_actor_actor FOREIGN KEY (actor_id) REFERENCES actor (actor_id) ON DELETE NO ACTION ON UPDATE CASCADE,\n  CONSTRAINT fk_film_actor_film FOREIGN KEY (film_id) REFERENCES film (film_id) ON DELETE NO ACTION ON UPDATE CASCADE\n)"}, {"name": "film_category", "type": "table", "sql": "CREATE TABLE film_category (\n  film_id INT NOT NULL,\n  category_id SMALLINT  NOT NULL,\n  last_update TIMESTAMP NOT NULL,\n  PRIMARY KEY (film_id, category_id),\n  CONSTRAINT fk_film_category_film FOREIGN KEY (film_id) REFERENCES film (film_id) ON DELETE NO ACTION ON UPDATE CASCADE,\n  CONSTRAINT fk_film_category_category FOREIGN KEY (category_id) REFERENCES category (category_id) ON DELETE NO ACTION ON UPDATE CASCADE\n)"}, {"name": "film_list", "type": "view", "sql": "CREATE VIEW film_list\nAS\nSELECT film.film_id AS FID,\n       film.title AS title,\n       film.description AS description,\n       category.name AS category,\n       film.rental_rate AS price,\n       film.length AS length,\n       film.rating AS rating,\n       actor.first_name||' '||actor.last_name AS actors\nFROM category LEFT JOIN film_category ON category.category_id = film_category.category_id LEFT JOIN film ON film_category.film_id = film.film_id\n        JOIN film_actor ON film.film_id = film_actor.film_id\n    JOIN actor ON film_actor.actor_id = actor.actor_id"}, {"name": "film_text", "type": "table", "sql": "CREATE TABLE film_text (\n  film_id SMALLINT NOT NULL,\n  title VARCHAR(255) NOT NULL,\n  description BLOB SUB_TYPE TEXT,\n  PRIMARY KEY  (film_id)\n)"}, {"name": "inventory", "type": "table", "sql": "CREATE TABLE inventory (\n  inventory_id INT NOT NULL,\n  film_id INT NOT NULL,\n  store_id INT NOT NULL,\n  last_update TIMESTAMP NOT NULL,\n  PRIMARY KEY  (inventory_id),\n  CONSTRAINT fk_inventory_store FOREIGN KEY (store_id) REFERENCES store (store_id) ON DELETE NO ACTION ON UPDATE CASCADE,\n  CONSTRAINT fk_inventory_film FOREIGN KEY (film_id) REFERENCES film (film_id) ON DELETE NO ACTION ON UPDATE CASCADE\n)"}, {"name": "language", "type": "table", "sql": "CREATE TABLE language (\n  language_id SMALLINT NOT NULL ,\n  name CHAR(20) NOT NULL,\n  last_update TIMESTAMP NOT NULL,\n  PRIMARY KEY (language_id)\n)"}, {"name": "payment", "type": "table", "sql": "CREATE TABLE payment (\n  payment_id int NOT NULL,\n  customer_id INT  NOT NULL,\n  staff_id SMALLINT NOT NULL,\n  rental_id INT DEFAULT NULL,\n  amount DECIMAL(5,2) NOT NULL,\n  payment_date TIMESTAMP NOT NULL,\n  last_update TIMESTAMP NOT NULL,\n  PRIMARY KEY  (payment_id),\n  CONSTRAINT fk_payment_rental FOREIGN KEY (rental_id) REFERENCES rental (rental_id) ON DELETE SET NULL ON UPDATE CASCADE,\n  CONSTRAINT fk_payment_customer FOREIGN KEY (customer_id) REFERENCES customer (customer_id) ,\n  CONSTRAINT fk_payment_staff FOREIGN KEY (staff_id) REFERENCES staff (staff_id)\n)"}, {"name": "rental", "type": "table", "sql": "CREATE TABLE rental (\n  rental_id INT NOT NULL,\n  rental_date TIMESTAMP NOT NULL,\n  inventory_id INT  NOT NULL,\n  customer_id INT  NOT NULL,\n  return_date TIMESTAMP DEFAULT NULL,\n  staff_id SMALLINT  NOT NULL,\n  last_update TIMESTAMP NOT NULL,\n  PRIMARY KEY (rental_id),\n  CONSTRAINT fk_rental_staff FOREIGN KEY (staff_id) REFERENCES staff (staff_id) ,\n  CONSTRAINT fk_rental_inventory FOREIGN KEY (inventory_id) REFERENCES inventory (inventory_id) ,\n  CONSTRAINT fk_rental_customer FOREIGN KEY (customer_id) REFERENCES customer (customer_id)\n)"}, {"name": "sales_by_film_category", "type": "view", "sql": "CREATE VIEW sales_by_film_category\nAS\nSELECT\nc.name AS category\n, SUM(p.amount) AS total_sales\nFROM payment AS p\nINNER JOIN rental AS r ON p.rental_id = r.rental_id\nINNER JOIN inventory AS i ON r.inventory_id = i.inventory_id\nINNER JOIN film AS f ON i.film_id = f.film_id\nINNER JOIN film_category AS fc ON f.film_id = fc.film_id\nINNER JOIN category AS c ON fc.category_id = c.category_id\nGROUP BY c.name"}, {"name": "sales_by_store", "type": "view", "sql": "CREATE VIEW sales_by_store\nAS\nSELECT\n  s.store_id\n ,c.city||','||cy.country AS store\n ,m.first_name||' '||m.last_name AS manager\n ,SUM(p.amount) AS total_sales\nFROM payment AS p\nINNER JOIN rental AS r ON p.rental_id = r.rental_id\nINNER JOIN inventory AS i ON r.inventory_id = i.inventory_id\nINNER JOIN store AS s ON i.store_id = s.store_id\nINNER JOIN address AS a ON s.address_id = a.address_id\nINNER JOIN city AS c ON a.city_id = c.city_id\nINNER JOIN country AS cy ON c.country_id = cy.country_id\nINNER JOIN staff AS m ON s.manager_staff_id = m.staff_id\nGROUP BY  \n  s.store_id\n, c.city||','||cy.country\n, m.first_name||' '||m.last_name"}, {"name": "staff", "type": "table", "sql": "CREATE TABLE staff (\n  staff_id SMALLINT NOT NULL,\n  first_name VARCHAR(45) NOT NULL,\n  last_name VARCHAR(45) NOT NULL,\n  address_id INT NOT NULL,\n  picture BLOB DEFAULT NULL,\n  email VARCHAR(50) DEFAULT NULL,\n  store_id INT NOT NULL,\n  active SMALLINT DEFAULT 1 NOT NULL,\n  username VARCHAR(16) NOT NULL,\n  password VARCHAR(40) DEFAULT NULL,\n  last_update TIMESTAMP NOT NULL,\n  PRIMARY KEY  (staff_id),\n  CONSTRAINT fk_staff_store FOREIGN KEY (store_id) REFERENCES store (store_id) ON DELETE NO ACTION ON UPDATE CASCADE,\n  CONSTRAINT fk_staff_address FOREIGN KEY (address_id) REFERENCES address (address_id) ON DELETE NO ACTION ON UPDATE CASCADE\n)"}, {"name": "staff_list", "type": "view", "sql": "CREATE VIEW staff_list\nAS\nSELECT s.staff_id AS ID,\n       s.first_name||' '||s.last_name AS name,\n       a.address AS address,\n       a.postal_code AS zip_code,\n       a.phone AS phone,\n       city.city AS city,\n       country.country AS country,\n       s.store_id AS SID\nFROM staff AS s JOIN address AS a ON s.address_id = a.address_id JOIN city ON a.city_id = city.city_id\n    JOIN country ON city.country_id = country.country_id"}, {"name": "store", "type": "table", "sql": "CREATE TABLE store (\n  store_id INT NOT NULL,\n  manager_staff_id SMALLINT NOT NULL,\n  address_id INT NOT NULL,\n  last_update TIMESTAMP NOT NULL,\n  PRIMARY KEY  (store_id),\n  CONSTRAINT fk_store_staff FOREIGN KEY (manager_staff_id) REFERENCES staff (staff_id) ,\n  CONSTRAINT fk_store_address FOREIGN KEY (address_id) REFERENCES address (address_id)\n)"}]