PageRenderTime 31ms CodeModel.GetById 21ms RepoModel.GetById 0ms app.codeStats 0ms

/jOOQ-examples/Sakila/db2-sakila-db/db2-sakila-schema.sql

http://github.com/jOOQ/jOOQ
SQL | 430 lines | 364 code | 54 blank | 12 comment | 0 complexity | 6c0274de11d50ebfdf2d931d615c748f MD5 | raw file
Possible License(s): Apache-2.0
  1. /*
  2. Sakila for DB2 is a port of the Sakila example database available for MySQL, which was originally developed by Mike Hillyer of the MySQL AB documentation team.
  3. This project is designed to help database administrators to decide which database to use for development of new products
  4. The user can run the same SQL against different kind of databases and compare the performance
  5. License: BSD
  6. Copyright DB Software Laboratory and Data Geekery GmbH (DB2 port)
  7. http://www.etl-tools.com / http://www.datageekery.com
  8. */
  9. CREATE TABLE actor (
  10. actor_id INTEGER NOT NULL GENERATED BY DEFAULT AS IDENTITY,
  11. first_name VARCHAR(45) NOT NULL,
  12. last_name VARCHAR(45) NOT NULL,
  13. last_update DATE NOT NULL DEFAULT CURRENT_DATE,
  14. CONSTRAINT pk_actor PRIMARY KEY (actor_id)
  15. );
  16. CREATE INDEX idx_actor_last_name ON actor(last_name);
  17. CREATE OR REPLACE TRIGGER actor_before_update
  18. BEFORE UPDATE ON actor
  19. REFERENCING NEW AS new
  20. FOR EACH ROW
  21. BEGIN
  22. SET new.last_update = current_date;
  23. END;
  24. CREATE TABLE country (
  25. country_id SMALLINT NOT NULL GENERATED BY DEFAULT AS IDENTITY,
  26. country VARCHAR(50) NOT NULL,
  27. last_update DATE,
  28. CONSTRAINT pk_country PRIMARY KEY (country_id)
  29. );
  30. CREATE OR REPLACE TRIGGER country_before_update
  31. BEFORE UPDATE ON country
  32. REFERENCING NEW AS new
  33. FOR EACH ROW
  34. BEGIN
  35. SET new.last_update = current_date;
  36. END;
  37. CREATE TABLE city (
  38. city_id INTEGER NOT NULL GENERATED BY DEFAULT AS IDENTITY,
  39. city VARCHAR(50) NOT NULL,
  40. country_id SMALLINT NOT NULL,
  41. last_update DATE NOT NULL DEFAULT CURRENT_DATE,
  42. CONSTRAINT pk_city PRIMARY KEY (city_id),
  43. CONSTRAINT fk_city_country FOREIGN KEY (country_id) REFERENCES country (country_id)
  44. );
  45. CREATE OR REPLACE TRIGGER city_before_update
  46. BEFORE UPDATE ON city
  47. REFERENCING NEW AS new
  48. FOR EACH ROW
  49. BEGIN
  50. SET new.last_update = current_date;
  51. END;
  52. CREATE TABLE address (
  53. address_id INTEGER NOT NULL GENERATED BY DEFAULT AS IDENTITY,
  54. address VARCHAR(50) NOT NULL,
  55. address2 VARCHAR(50) DEFAULT NULL,
  56. district VARCHAR(20) NOT NULL,
  57. city_id INT NOT NULL,
  58. postal_code VARCHAR(10) DEFAULT NULL,
  59. phone VARCHAR(20) NOT NULL,
  60. last_update DATE NOT NULL DEFAULT CURRENT_DATE,
  61. CONSTRAINT pk_address PRIMARY KEY (address_id),
  62. CONSTRAINT fk_address_city FOREIGN KEY (city_id) REFERENCES city (city_id)
  63. );
  64. CREATE INDEX idx_fk_city_id ON address(city_id);
  65. CREATE OR REPLACE TRIGGER address_before_update
  66. BEFORE UPDATE ON address
  67. REFERENCING NEW AS new
  68. FOR EACH ROW
  69. BEGIN
  70. SET new.last_update = current_date;
  71. END;
  72. CREATE TABLE language (
  73. language_id SMALLINT NOT NULL GENERATED BY DEFAULT AS IDENTITY,
  74. name CHAR(20) NOT NULL,
  75. last_update DATE NOT NULL DEFAULT CURRENT_DATE,
  76. CONSTRAINT pk_language PRIMARY KEY (language_id)
  77. );
  78. CREATE OR REPLACE TRIGGER language_before_update
  79. BEFORE UPDATE ON language
  80. REFERENCING NEW AS new
  81. FOR EACH ROW
  82. BEGIN
  83. SET new.last_update = current_date;
  84. END;
  85. CREATE TABLE category (
  86. category_id SMALLINT NOT NULL GENERATED BY DEFAULT AS IDENTITY,
  87. name VARCHAR(25) NOT NULL,
  88. last_update DATE NOT NULL DEFAULT CURRENT_DATE,
  89. CONSTRAINT pk_category PRIMARY KEY (category_id)
  90. );
  91. CREATE OR REPLACE TRIGGER category_before_update
  92. BEFORE UPDATE ON category
  93. REFERENCING NEW AS new
  94. FOR EACH ROW
  95. BEGIN
  96. SET new.last_update = current_date;
  97. END;
  98. CREATE TABLE customer (
  99. customer_id INT NOT NULL GENERATED BY DEFAULT AS IDENTITY,
  100. store_id INT NOT NULL,
  101. first_name VARCHAR(45) NOT NULL,
  102. last_name VARCHAR(45) NOT NULL,
  103. email VARCHAR(50) DEFAULT NULL,
  104. address_id INT NOT NULL,
  105. active CHAR(1) DEFAULT 'Y' NOT NULL,
  106. create_date DATE NOT NULL,
  107. last_update DATE NOT NULL DEFAULT CURRENT_DATE,
  108. CONSTRAINT pk_customer PRIMARY KEY (customer_id),
  109. CONSTRAINT fk_customer_address FOREIGN KEY (address_id) REFERENCES address(address_id)
  110. );
  111. CREATE INDEX idx_customer_fk_store_id ON customer(store_id);
  112. CREATE INDEX idx_customer_fk_address_id ON customer(address_id);
  113. CREATE INDEX idx_customer_last_name ON customer(last_name);
  114. CREATE OR REPLACE TRIGGER customer_before_update
  115. BEFORE UPDATE ON customer
  116. REFERENCING NEW AS new
  117. FOR EACH ROW
  118. BEGIN
  119. SET new.last_update = current_date;
  120. END;
  121. CREATE TABLE film (
  122. film_id int NOT NULL GENERATED BY DEFAULT AS IDENTITY,
  123. title VARCHAR(255) NOT NULL,
  124. description CLOB DEFAULT NULL,
  125. release_year VARCHAR(4) DEFAULT NULL,
  126. language_id SMALLINT NOT NULL,
  127. original_language_id SMALLINT DEFAULT NULL,
  128. rental_duration SMALLINT DEFAULT 3 NOT NULL,
  129. rental_rate DECIMAL(4,2) DEFAULT 4.99 NOT NULL,
  130. length SMALLINT DEFAULT NULL,
  131. replacement_cost DECIMAL(5,2) DEFAULT 19.99 NOT NULL,
  132. rating VARCHAR(10) DEFAULT 'G',
  133. special_features VARCHAR(100) DEFAULT NULL,
  134. last_update DATE NOT NULL DEFAULT CURRENT_DATE,
  135. CONSTRAINT pk_film PRIMARY KEY (film_id),
  136. CONSTRAINT fk_film_language FOREIGN KEY (language_id) REFERENCES language (language_id) ,
  137. CONSTRAINT fk_film_language_original FOREIGN KEY (original_language_id) REFERENCES language (language_id)
  138. );
  139. ALTER TABLE film ADD CONSTRAINT CHECK_special_features CHECK (
  140. special_features IS NULL OR
  141. special_features LIKE '%Trailers%' OR
  142. special_features LIKE '%Commentaries%' OR
  143. special_features LIKE '%Deleted Scenes%' OR
  144. special_features LIKE '%Behind the Scenes%'
  145. );
  146. ALTER TABLE film ADD CONSTRAINT CHECK_special_rating CHECK (rating IN ('G','PG','PG-13','R','NC-17'));
  147. CREATE INDEX idx_fk_language_id ON film (language_id);
  148. CREATE INDEX idx_fk_original_language_id ON film (original_language_id);
  149. CREATE OR REPLACE TRIGGER film_before_update
  150. BEFORE UPDATE ON film
  151. REFERENCING NEW AS new
  152. FOR EACH ROW
  153. BEGIN
  154. SET new.last_update = current_date;
  155. END;
  156. CREATE TABLE film_actor (
  157. actor_id INT NOT NULL,
  158. film_id INT NOT NULL,
  159. last_update DATE NOT NULL DEFAULT CURRENT_DATE,
  160. CONSTRAINT pk_film_actor PRIMARY KEY (actor_id,film_id),
  161. CONSTRAINT fk_film_actor_actor FOREIGN KEY (actor_id) REFERENCES actor (actor_id),
  162. CONSTRAINT fk_film_actor_film FOREIGN KEY (film_id) REFERENCES film (film_id)
  163. );
  164. CREATE INDEX idx_fk_film_actor_film ON film_actor (film_id);
  165. CREATE INDEX idx_fk_film_actor_actor ON film_actor (actor_id);
  166. CREATE OR REPLACE TRIGGER film_actor_before_update
  167. BEFORE UPDATE ON film_actor
  168. REFERENCING NEW AS new
  169. FOR EACH ROW
  170. BEGIN
  171. SET new.last_update = current_date;
  172. END;
  173. CREATE TABLE film_category (
  174. film_id INT NOT NULL,
  175. category_id SMALLINT NOT NULL,
  176. last_update DATE NOT NULL DEFAULT CURRENT_DATE,
  177. CONSTRAINT pk_film_category PRIMARY KEY (film_id, category_id),
  178. CONSTRAINT fk_film_category_film FOREIGN KEY (film_id) REFERENCES film (film_id),
  179. CONSTRAINT fk_film_category_category FOREIGN KEY (category_id) REFERENCES category (category_id)
  180. );
  181. CREATE INDEX idx_fk_film_category_film ON film_category(film_id);
  182. CREATE INDEX idx_fk_film_category_category ON film_category(category_id);
  183. CREATE OR REPLACE TRIGGER film_category_before_update
  184. BEFORE UPDATE ON film_category
  185. REFERENCING NEW AS new
  186. FOR EACH ROW
  187. BEGIN
  188. SET new.last_update = current_date;
  189. END;
  190. CREATE TABLE film_text (
  191. film_id SMALLINT NOT NULL,
  192. title VARCHAR(255) NOT NULL,
  193. description CLOB,
  194. CONSTRAINT pk_film_text PRIMARY KEY (film_id)
  195. );
  196. CREATE TABLE inventory (
  197. inventory_id INT NOT NULL GENERATED BY DEFAULT AS IDENTITY,
  198. film_id INT NOT NULL,
  199. store_id INT NOT NULL,
  200. last_update DATE NOT NULL DEFAULT CURRENT_DATE,
  201. CONSTRAINT pk_inventory PRIMARY KEY (inventory_id),
  202. CONSTRAINT fk_inventory_film FOREIGN KEY (film_id) REFERENCES film (film_id)
  203. );
  204. CREATE INDEX idx_fk_film_id ON inventory(film_id);
  205. CREATE INDEX idx_fk_film_id_store_id ON inventory(store_id,film_id);
  206. CREATE OR REPLACE TRIGGER inventory_before_update
  207. BEFORE UPDATE ON inventory
  208. REFERENCING NEW AS new
  209. FOR EACH ROW
  210. BEGIN
  211. SET new.last_update = current_date;
  212. END;
  213. CREATE TABLE staff (
  214. staff_id SMALLINT NOT NULL GENERATED BY DEFAULT AS IDENTITY,
  215. first_name VARCHAR(45) NOT NULL,
  216. last_name VARCHAR(45) NOT NULL,
  217. address_id INT NOT NULL,
  218. picture BLOB DEFAULT NULL,
  219. email VARCHAR(50) DEFAULT NULL,
  220. store_id INT NOT NULL,
  221. active SMALLINT DEFAULT 1 NOT NULL,
  222. username VARCHAR(16) NOT NULL,
  223. password VARCHAR(40) DEFAULT NULL,
  224. last_update DATE NOT NULL DEFAULT CURRENT_DATE,
  225. CONSTRAINT pk_staff PRIMARY KEY (staff_id),
  226. CONSTRAINT fk_staff_address FOREIGN KEY (address_id) REFERENCES address (address_id)
  227. );
  228. CREATE INDEX idx_fk_staff_store_id ON staff(store_id);
  229. CREATE INDEX idx_fk_staff_address_id ON staff(address_id);
  230. CREATE OR REPLACE TRIGGER staff_before_update
  231. BEFORE UPDATE ON staff
  232. REFERENCING NEW AS new
  233. FOR EACH ROW
  234. BEGIN
  235. SET new.last_update = current_date;
  236. END;
  237. CREATE TABLE store (
  238. store_id INT NOT NULL GENERATED BY DEFAULT AS IDENTITY,
  239. manager_staff_id SMALLINT NOT NULL,
  240. address_id INT NOT NULL,
  241. last_update DATE NOT NULL DEFAULT CURRENT_DATE,
  242. CONSTRAINT pk_store PRIMARY KEY (store_id),
  243. CONSTRAINT fk_store_staff FOREIGN KEY (manager_staff_id) REFERENCES staff (staff_id),
  244. CONSTRAINT fk_store_address FOREIGN KEY (address_id) REFERENCES address (address_id)
  245. );
  246. CREATE INDEX idx_store_fk_manager_staff_id ON store(manager_staff_id);
  247. CREATE INDEX idx_fk_store_address ON store(address_id);
  248. CREATE OR REPLACE TRIGGER store_before_update
  249. BEFORE UPDATE ON store
  250. REFERENCING NEW AS new
  251. FOR EACH ROW
  252. BEGIN
  253. SET new.last_update = current_date;
  254. END;
  255. CREATE TABLE payment (
  256. payment_id int NOT NULL GENERATED BY DEFAULT AS IDENTITY,
  257. customer_id INT NOT NULL,
  258. staff_id SMALLINT NOT NULL,
  259. rental_id INT DEFAULT NULL,
  260. amount DECIMAL(5,2) NOT NULL,
  261. payment_date DATE NOT NULL,
  262. last_update DATE NOT NULL DEFAULT CURRENT_DATE,
  263. CONSTRAINT pk_payment PRIMARY KEY (payment_id),
  264. CONSTRAINT fk_payment_customer FOREIGN KEY (customer_id) REFERENCES customer (customer_id) ,
  265. CONSTRAINT fk_payment_staff FOREIGN KEY (staff_id) REFERENCES staff (staff_id)
  266. );
  267. CREATE INDEX idx_fk_staff_id ON payment(staff_id);
  268. CREATE INDEX idx_fk_customer_id ON payment(customer_id);
  269. CREATE OR REPLACE TRIGGER payment_before_update
  270. BEFORE UPDATE ON payment
  271. REFERENCING NEW AS new
  272. FOR EACH ROW
  273. BEGIN
  274. SET new.last_update = current_date;
  275. END;
  276. CREATE TABLE rental (
  277. rental_id INT NOT NULL GENERATED BY DEFAULT AS IDENTITY,
  278. rental_date DATE NOT NULL,
  279. inventory_id INT NOT NULL,
  280. customer_id INT NOT NULL,
  281. return_date DATE DEFAULT NULL,
  282. staff_id SMALLINT NOT NULL,
  283. last_update DATE NOT NULL DEFAULT CURRENT_DATE,
  284. CONSTRAINT pk_rental PRIMARY KEY (rental_id),
  285. CONSTRAINT fk_rental_staff FOREIGN KEY (staff_id) REFERENCES staff (staff_id) ,
  286. CONSTRAINT fk_rental_inventory FOREIGN KEY (inventory_id) REFERENCES inventory (inventory_id) ,
  287. CONSTRAINT fk_rental_customer FOREIGN KEY (customer_id) REFERENCES customer (customer_id)
  288. );
  289. CREATE INDEX idx_rental_fk_inventory_id ON rental(inventory_id);
  290. CREATE INDEX idx_rental_fk_customer_id ON rental(customer_id);
  291. CREATE INDEX idx_rental_fk_staff_id ON rental(staff_id);
  292. CREATE UNIQUE INDEX idx_rental_uq ON rental (rental_date,inventory_id,customer_id);
  293. CREATE OR REPLACE TRIGGER rental_before_update
  294. BEFORE UPDATE ON rental
  295. REFERENCING NEW AS new
  296. FOR EACH ROW
  297. BEGIN
  298. SET new.last_update = current_date;
  299. END;
  300. ALTER TABLE customer ADD CONSTRAINT fk_customer_store FOREIGN KEY (store_id) REFERENCES store (store_id);
  301. ALTER TABLE inventory ADD CONSTRAINT fk_inventory_store FOREIGN KEY (store_id) REFERENCES store (store_id);
  302. ALTER TABLE staff ADD CONSTRAINT fk_staff_store FOREIGN KEY (store_id) REFERENCES store (store_id);
  303. ALTER TABLE payment ADD CONSTRAINT fk_payment_rental FOREIGN KEY (rental_id) REFERENCES rental (rental_id) ON DELETE SET NULL;
  304. CREATE OR REPLACE VIEW customer_list
  305. AS
  306. SELECT cu.customer_id AS ID,
  307. cu.first_name||' '||cu.last_name AS name,
  308. a.address AS address,
  309. a.postal_code AS zip_code,
  310. a.phone AS phone,
  311. city.city AS city,
  312. country.country AS country,
  313. decode(cu.active, 1,'active','') AS notes,
  314. cu.store_id AS SID
  315. FROM customer cu JOIN address a ON cu.address_id = a.address_id JOIN city ON a.city_id = city.city_id
  316. JOIN country ON city.country_id = country.country_id;
  317. CREATE OR REPLACE VIEW film_list
  318. AS
  319. SELECT film.film_id AS FID,
  320. film.title AS title,
  321. film.description AS description,
  322. category.name AS category,
  323. film.rental_rate AS price,
  324. film.length AS length,
  325. film.rating AS rating,
  326. actor.first_name||' '||actor.last_name AS actors
  327. 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
  328. JOIN film_actor ON film.film_id = film_actor.film_id
  329. JOIN actor ON film_actor.actor_id = actor.actor_id;
  330. CREATE OR REPLACE VIEW staff_list
  331. AS
  332. SELECT s.staff_id AS ID,
  333. s.first_name||' '||s.last_name AS name,
  334. a.address AS address,
  335. a.postal_code AS zip_code,
  336. a.phone AS phone,
  337. city.city AS city,
  338. country.country AS country,
  339. s.store_id AS SID
  340. FROM staff s JOIN address a ON s.address_id = a.address_id JOIN city ON a.city_id = city.city_id
  341. JOIN country ON city.country_id = country.country_id;
  342. CREATE OR REPLACE VIEW sales_by_store
  343. AS
  344. SELECT
  345. s.store_id
  346. ,c.city||','||cy.country AS store
  347. ,m.first_name||' '||m.last_name AS manager
  348. ,SUM(p.amount) AS total_sales
  349. FROM payment p
  350. INNER JOIN rental r ON p.rental_id = r.rental_id
  351. INNER JOIN inventory i ON r.inventory_id = i.inventory_id
  352. INNER JOIN store s ON i.store_id = s.store_id
  353. INNER JOIN address a ON s.address_id = a.address_id
  354. INNER JOIN city c ON a.city_id = c.city_id
  355. INNER JOIN country cy ON c.country_id = cy.country_id
  356. INNER JOIN staff m ON s.manager_staff_id = m.staff_id
  357. GROUP BY
  358. s.store_id
  359. , c.city||','||cy.country
  360. , m.first_name||' '||m.last_name;
  361. CREATE OR REPLACE VIEW sales_by_film_category
  362. AS
  363. SELECT
  364. c.name AS category
  365. , SUM(p.amount) AS total_sales
  366. FROM payment p
  367. INNER JOIN rental r ON p.rental_id = r.rental_id
  368. INNER JOIN inventory i ON r.inventory_id = i.inventory_id
  369. INNER JOIN film f ON i.film_id = f.film_id
  370. INNER JOIN film_category fc ON f.film_id = fc.film_id
  371. INNER JOIN category c ON fc.category_id = c.category_id
  372. GROUP BY c.name;