1-- 2-- This is the PostgreSQL version of the SQL DDL for the database used by 3-- all unit tests and examples within the tutorial. 4-- Version: $Id: Query2XML_Tests.psql 228674 2007-02-01 15:49:59Z lukasfeiler $ 5-- 6 7CREATE DATABASE query2xml_tests; 8\c query2xml_tests; 9 10 11CREATE TABLE artist ( 12 artistid INT NOT NULL, 13 name VARCHAR(255), 14 birth_year Int, 15 birth_place VARCHAR(255), 16 genre VARCHAR(255), 17 UNIQUE (artistid), 18 PRIMARY KEY (artistid) 19); 20 21CREATE TABLE customer ( 22 customerid INT NOT NULL, 23 first_name VARCHAR(255), 24 last_name VARCHAR(255), 25 email VARCHAR(255), 26 UNIQUE (customerid), 27 PRIMARY KEY (customerid) 28); 29 30CREATE TABLE album ( 31 albumid INT NOT NULL, 32 artist_id INT NOT NULL, 33 title VARCHAR(255), 34 published_year Int, 35 comment VARCHAR(255), 36 UNIQUE (albumid), 37 PRIMARY KEY (albumid), 38 FOREIGN KEY (artist_id) REFERENCES artist (artistid) 39); 40 41CREATE TABLE employee ( 42 employeeid INT NOT NULL, 43 employeename VARCHAR(255), 44 UNIQUE (employeeid), 45 PRIMARY KEY (employeeid) 46); 47 48CREATE TABLE store ( 49 storeid INT NOT NULL, 50 manager INT NOT NULL, 51 country VARCHAR(255), 52 state VARCHAR(255), 53 city VARCHAR(255), 54 street VARCHAR(255), 55 phone VARCHAR(255), 56 building_xmldata TEXT, 57 UNIQUE (storeid), 58 PRIMARY KEY (storeid), 59 FOREIGN KEY (manager) REFERENCES employee (employeeid) 60); 61 62CREATE TABLE department ( 63 departmentid INT NOT NULL, 64 store_id INT NOT NULL, 65 department_head INT NOT NULL, 66 departmentname VARCHAR(255), 67 UNIQUE (departmentid), 68 PRIMARY KEY (departmentid), 69 FOREIGN KEY (department_head) REFERENCES employee (employeeid), 70 FOREIGN KEY (store_id) REFERENCES store (storeid) 71); 72 73CREATE TABLE employee_department ( 74 employee_id INT NOT NULL, 75 department_id INT NOT NULL, 76 PRIMARY KEY (employee_id,department_id), 77 FOREIGN KEY (employee_id) REFERENCES employee (employeeid), 78 FOREIGN KEY (department_id) REFERENCES department (departmentid) 79); 80 81CREATE TABLE sale ( 82 saleid INT NOT NULL, 83 album_id INT NOT NULL, 84 customer_id INT NOT NULL, 85 employee_id INT NOT NULL, 86 store_id INT NOT NULL, 87 timestamp TIMESTAMP, 88 UNIQUE (saleid), 89 PRIMARY KEY (saleid), 90 FOREIGN KEY (employee_id) REFERENCES employee (employeeid), 91 FOREIGN KEY (album_id) REFERENCES album (albumid), 92 FOREIGN KEY (customer_id) REFERENCES customer (customerid), 93 FOREIGN KEY (store_id) REFERENCES store (storeid) 94); 95 96 97 98INSERT INTO artist (artistid, name, birth_year, birth_place, genre) VALUES(1, 'Curtis Mayfield', 1920, 'Chicago', 'Soul'); 99INSERT INTO artist (artistid, name, birth_year, birth_place, genre) VALUES(2, 'Isaac Hayes', 1942, 'Tennessee', 'Soul'); 100INSERT INTO artist (artistid, name, birth_year, birth_place, genre) VALUES(3, 'Ray Charles', 1930, 'Mississippi', 'Country and Soul'); 101 102INSERT INTO album (albumid, artist_id, title, published_year, comment) VALUES(1, 1, 'New World Order', 1990, 'the best ever!'); 103INSERT INTO album (albumid, artist_id, title, published_year, comment) VALUES(2, 1, 'Curtis', 1970, 'that man''s got somthin'' to say'); 104INSERT INTO album (albumid, artist_id, title, published_year, comment) VALUES(3, 2, 'Shaft', 1972, 'he''s the man'); 105 106INSERT INTO customer (customerid, first_name, last_name, email) VALUES(1, 'Jane', 'Doe', 'jane.doe@example.com'); 107INSERT INTO customer (customerid, first_name, last_name, email) VALUES(2, 'John', 'Doe', 'john.doe@example.com'); 108INSERT INTO customer (customerid, first_name, last_name, email) VALUES(3, 'Susan', 'Green', 'susan.green@example.com'); 109INSERT INTO customer (customerid, first_name, last_name, email) VALUES(4, 'Victoria', 'Alt', 'victory.alt@example.com'); 110INSERT INTO customer (customerid, first_name, last_name, email) VALUES(5, 'Will', 'Rippy', 'will.wippy@example.com'); 111INSERT INTO customer (customerid, first_name, last_name, email) VALUES(6, 'Tim', 'Raw', 'tim.raw@example.com'); 112INSERT INTO customer (customerid, first_name, last_name, email) VALUES(7, 'Nick', 'Fallow', 'nick.fallow@example.com'); 113INSERT INTO customer (customerid, first_name, last_name, email) VALUES(8, 'Ed', 'Burton', 'ed.burton@example.com'); 114INSERT INTO customer (customerid, first_name, last_name, email) VALUES(9, 'Jack', 'Woo', 'jack.woo@example.com'); 115INSERT INTO customer (customerid, first_name, last_name, email) VALUES(10, 'Maria', 'Gonzales', 'maria.gonzales@example.com'); 116 117INSERT INTO employee (employeeid, employeename) VALUES(1, 'Michael Jones'); 118INSERT INTO employee (employeeid, employeename) VALUES(2, 'Susi Weintraub'); 119INSERT INTO employee (employeeid, employeename) VALUES(3, 'Steve Hack'); 120INSERT INTO employee (employeeid, employeename) VALUES(4, 'Joan Kerr'); 121INSERT INTO employee (employeeid, employeename) VALUES(5, 'Marcus Roth'); 122INSERT INTO employee (employeeid, employeename) VALUES(6, 'Jack Mack'); 123INSERT INTO employee (employeeid, employeename) VALUES(7, 'Rita Doktor'); 124INSERT INTO employee (employeeid, employeename) VALUES(8, 'David Til'); 125INSERT INTO employee (employeeid, employeename) VALUES(9, 'Pia Eist'); 126INSERT INTO employee (employeeid, employeename) VALUES(10, 'Hanna Poll'); 127INSERT INTO employee (employeeid, employeename) VALUES(11, 'Jim Wells'); 128INSERT INTO employee (employeeid, employeename) VALUES(12, 'Sandra Wilson'); 129 130INSERT INTO store (storeid, manager, country, state, city, street, phone, building_xmldata) VALUES(1, 1, 'US', 'New York', 'New York', 'Broadway & 72nd Str', '123 456 7890', '<building><floors>4</floors><elevators>2</elevators><square_meters>3200</square_meters></building>'); 131INSERT INTO store (storeid, manager, country, state, city, street, phone, building_xmldata) VALUES(2, 2, 'US', 'New York', 'Larchmont', 'Palmer Ave 71', '456 7890', '<building><floors>2</floors><elevators>1</elevators><square_meters>400</square_meters></building>'); 132 133INSERT INTO department (departmentid, store_id, department_head, departmentname) VALUES(1, 1, 1, 'Sales'); 134INSERT INTO department (departmentid, store_id, department_head, departmentname) VALUES(2, 1, 4, 'Marketing'); 135INSERT INTO department (departmentid, store_id, department_head, departmentname) VALUES(3, 2, 7, 'Sales'); 136INSERT INTO department (departmentid, store_id, department_head, departmentname) VALUES(4, 2, 10, 'Marketing'); 137 138INSERT INTO employee_department (employee_id, department_id) VALUES(1, 1); 139INSERT INTO employee_department (employee_id, department_id) VALUES(2, 1); 140INSERT INTO employee_department (employee_id, department_id) VALUES(3, 1); 141INSERT INTO employee_department (employee_id, department_id) VALUES(4, 2); 142INSERT INTO employee_department (employee_id, department_id) VALUES(5, 2); 143INSERT INTO employee_department (employee_id, department_id) VALUES(6, 2); 144INSERT INTO employee_department (employee_id, department_id) VALUES(7, 3); 145INSERT INTO employee_department (employee_id, department_id) VALUES(8, 3); 146INSERT INTO employee_department (employee_id, department_id) VALUES(9, 3); 147INSERT INTO employee_department (employee_id, department_id) VALUES(10, 4); 148INSERT INTO employee_department (employee_id, department_id) VALUES(11, 4); 149INSERT INTO employee_department (employee_id, department_id) VALUES(12, 4); 150 151INSERT INTO sale (saleid, customer_id, album_id, employee_id, store_id, timestamp) VALUES (1, 1, 1, 1, 1, '2005-05-25 16:32:00'); 152INSERT INTO sale (saleid, customer_id, album_id, employee_id, store_id, timestamp) VALUES (2, 2, 1, 7, 2, '2005-06-05 12:56:00'); 153INSERT INTO sale (saleid, customer_id, album_id, employee_id, store_id, timestamp) VALUES (3, 3, 1, 2, 1, '2005-07-10 11:03:00'); 154INSERT INTO sale (saleid, customer_id, album_id, employee_id, store_id, timestamp) VALUES (4, 4, 1, 8, 2, '2005-07-10 10:03:00'); 155INSERT INTO sale (saleid, customer_id, album_id, employee_id, store_id, timestamp) VALUES (5, 5, 1, 3, 1, '2005-07-10 13:03:00'); 156INSERT INTO sale (saleid, customer_id, album_id, employee_id, store_id, timestamp) VALUES (6, 6, 1, 9, 2, '2005-07-10 14:03:00'); 157INSERT INTO sale (saleid, customer_id, album_id, employee_id, store_id, timestamp) VALUES (7, 7, 1, 1, 1, '2005-07-10 15:03:00'); 158INSERT INTO sale (saleid, customer_id, album_id, employee_id, store_id, timestamp) VALUES (8, 8, 1, 7, 2, '2005-07-10 16:03:00'); 159INSERT INTO sale (saleid, customer_id, album_id, employee_id, store_id, timestamp) VALUES (9, 9, 1, 2, 1, '2005-07-10 18:03:00'); 160INSERT INTO sale (saleid, customer_id, album_id, employee_id, store_id, timestamp) VALUES (10, 10, 1, 8, 2, '2005-07-10 19:03:00'); 161INSERT INTO sale (saleid, customer_id, album_id, employee_id, store_id, timestamp) VALUES (11, 1, 2, 7, 2, '2005-05-25 16:23:00'); 162INSERT INTO sale (saleid, customer_id, album_id, employee_id, store_id, timestamp) VALUES (12, 3, 2, 8, 2, '2005-07-10 11:56:00'); 163INSERT INTO sale (saleid, customer_id, album_id, employee_id, store_id, timestamp) VALUES (13, 5, 2, 9, 2, '2005-07-10 13:12:00'); 164INSERT INTO sale (saleid, customer_id, album_id, employee_id, store_id, timestamp) VALUES (14, 7, 2, 7, 2, '2005-07-10 15:09:00'); 165INSERT INTO sale (saleid, customer_id, album_id, employee_id, store_id, timestamp) VALUES (15, 9, 2, 8, 2, '2005-07-10 18:49:00'); 166INSERT INTO sale (saleid, customer_id, album_id, employee_id, store_id, timestamp) VALUES (16, 2, 3, 1, 1, '2005-06-05 12:56:12'); 167INSERT INTO sale (saleid, customer_id, album_id, employee_id, store_id, timestamp) VALUES (17, 4, 3, 2, 1, '2005-07-10 10:03:32'); 168INSERT INTO sale (saleid, customer_id, album_id, employee_id, store_id, timestamp) VALUES (18, 6, 3, 3, 1, '2005-07-10 14:03:52'); 169INSERT INTO sale (saleid, customer_id, album_id, employee_id, store_id, timestamp) VALUES (19, 8, 3, 1, 1, '2005-07-10 16:03:01'); 170INSERT INTO sale (saleid, customer_id, album_id, employee_id, store_id, timestamp) VALUES (20, 10, 3, 2, 1, '2005-07-10 19:03:50'); 171 172 173