-- The "demo" database -- Originally from an old Ingres distribution, so this example dates way back -- This version modified latest by -- Thomas Padron-McCarthy (Thomas.Padron-McCarthy@tech.oru.se), September 1, 2003 -- Remove old versions of the tables -- drop table supplier; -- drop table store; -- drop table employee; -- drop table dept; -- drop table item; -- drop table sale; -- drop table parts; -- drop table supply; -- Create the tables create table supplier ( number integer, name varchar(15), city varchar(15), state varchar(6)); create table store ( number integer, city varchar(15), state varchar(6)); create table employee ( number integer, name varchar(20), salary integer, manager integer, birthdate integer, startdate integer); create table dept ( number integer, name varchar(20), store integer, floor integer, manager integer); create table item ( number integer, name varchar(20), dept integer, price integer, qoh integer, supplier integer); create table sale ( number integer, date varchar(8), store integer, dept integer, item integer, quantity integer, employee integer, credit varchar(8)); create table parts ( pnum integer, pname varchar(20), color varchar(8), weight integer, qoh integer); create table supply ( snum integer, pnum integer, jnum integer, shipdate varchar(8), quan integer); -- Insert data into the tables -- Fill table "supplier" insert into supplier (number, name, city, state) values ( 199, 'Koret', 'Los Angeles', 'Calif'); insert into supplier (number, name, city, state) values ( 213, 'Cannon', 'Atlanta', 'Ga'); insert into supplier (number, name, city, state) values ( 33, 'Levi-Strauss', 'San Francisco', 'Calif'); insert into supplier (number, name, city, state) values ( 89, 'Fisher-Price', 'Boston', 'Mass'); insert into supplier (number, name, city, state) values ( 125, 'Playskool', 'Dallas', 'Tex'); insert into supplier (number, name, city, state) values ( 42, 'Whitman''s', 'Denver', 'Colo'); insert into supplier (number, name, city, state) values ( 15, 'White Stag', 'White Plains', 'Neb'); -- Fill table "store" insert into store (number, city, state) values (5, 'San Francisco', 'Calif'); insert into store (number, city, state) values (7, 'Oakland', 'Calif'); insert into store (number, city, state) values (8, 'El Cerrito', 'Calif'); -- Fill table "employee" insert into employee (number, name, salary, manager, birthdate, startdate) values ( 157, 'Jones, Tim', 12000, 199, 1940, 1960); insert into employee (number, name, salary, manager, birthdate, startdate) values ( 1110, 'Smith, Paul', 6000, 33, 1952, 1973); insert into employee (number, name, salary, manager, birthdate, startdate) values ( 35, 'Evans, Michael', 5000, 32, 1952, 1974); insert into employee (number, name, salary, manager, birthdate, startdate) values ( 129, 'Thomas, Tom', 10000, 199, 1941, 1962); insert into employee (number, name, salary, manager, birthdate, startdate) values ( 13, 'Edwards, Peter', 9000, 199, 1928, 1958); insert into employee (number, name, salary, manager, birthdate, startdate) values ( 215, 'Collins, Joanne', 7000, 10, 1950, 1971); insert into employee (number, name, salary, manager, birthdate, startdate) values ( 55, 'James, Mary', 12000, 199, 1920, 1969); insert into employee (number, name, salary, manager, birthdate, startdate) values ( 26, 'Thompson, Bob', 13000, 199, 1930, 1970); insert into employee (number, name, salary, manager, birthdate, startdate) values ( 98, 'Williams, Judy', 9000, 199, 1935, 1969); insert into employee (number, name, salary, manager, birthdate, startdate) values ( 32, 'Smythe, Carol', 9050, 199, 1929, 1967); insert into employee (number, name, salary, manager, birthdate, startdate) values ( 33, 'Hayes, Evelyn', 10100, 199, 1931, 1963); insert into employee (number, name, salary, manager, birthdate, startdate) values ( 199, 'Bullock, J.D.', 27000, 0, 1920, 1920); insert into employee (number, name, salary, manager, birthdate, startdate) values ( 4901, 'Bailey, Chas M.', 8377, 32, 1956, 1975); insert into employee (number, name, salary, manager, birthdate, startdate) values ( 843, 'Schmidt, Herman', 11204, 26, 1936, 1956); insert into employee (number, name, salary, manager, birthdate, startdate) values ( 2398, 'Wallace, Maggie J.', 7880, 26, 1940, 1959); insert into employee (number, name, salary, manager, birthdate, startdate) values ( 1639, 'Choy, Wanda', 11160, 55, 1947, 1970); insert into employee (number, name, salary, manager, birthdate, startdate) values ( 5119, 'Ferro, Tony', 13621, 55, 1939, 1963); insert into employee (number, name, salary, manager, birthdate, startdate) values ( 37, 'Raveen, Lemont', 11985, 26, 1950, 1974); insert into employee (number, name, salary, manager, birthdate, startdate) values ( 5219, 'Williams, Bruce', 13374, 33, 1944, 1959); insert into employee (number, name, salary, manager, birthdate, startdate) values ( 1523, 'Zugnoni, Arthur A.', 19868, 129, 1928, 1949); insert into employee (number, name, salary, manager, birthdate, startdate) values ( 430, 'Brunet, Paul C.', 17674, 129, 1938, 1959); insert into employee (number, name, salary, manager, birthdate, startdate) values ( 994, 'Iwano, Masahiro', 15641, 129, 1944, 1970); insert into employee (number, name, salary, manager, birthdate, startdate) values ( 1330, 'Onstad, Richard', 8779, 13, 1952, 1971); insert into employee (number, name, salary, manager, birthdate, startdate) values ( 10, 'Ross, Stanley', 15908, 199, 1927, 1945); insert into employee (number, name, salary, manager, birthdate, startdate) values ( 11, 'Ross, Stuart', 12067, 0, 1931, 1932); -- Fill table "dept" insert into dept (number, name, store, floor, manager) values ( 35, 'Book', 5, 1, 55); insert into dept (number, name, store, floor, manager) values ( 10, 'Candy', 5, 1, 13); insert into dept (number, name, store, floor, manager) values ( 19, 'Furniture', 7, 4, 26); insert into dept (number, name, store, floor, manager) values ( 20, 'Major Appliances', 7, 4, 26); insert into dept (number, name, store, floor, manager) values ( 14, 'Jewelry', 8, 1, 33); insert into dept (number, name, store, floor, manager) values ( 43, 'Children''s', 8, 2, 32); insert into dept (number, name, store, floor, manager) values ( 65, 'Junior''s', 7, 3, 17); insert into dept (number, name, store, floor, manager) values ( 58, 'Men''s', 7, 2, 129); insert into dept (number, name, store, floor, manager) values ( 60, 'Sportswear', 5, 1, 10); insert into dept (number, name, store, floor, manager) values ( 99, 'Giftwrap', 5, 1, 98); insert into dept (number, name, store, floor, manager) values ( 1, 'Bargain', 5, 0, 7); insert into dept (number, name, store, floor, manager) values ( 26, 'Linens', 7, 3, 157); insert into dept (number, name, store, floor, manager) values ( 63, 'Women''s', 7, 3, 38); insert into dept (number, name, store, floor, manager) values ( 49, 'Toys', 8, 2, 35); insert into dept (number, name, store, floor, manager) values ( 70, 'Women''s', 5, 1, 10); insert into dept (number, name, store, floor, manager) values ( 73, 'Children''s', 5, 1, 10); insert into dept (number, name, store, floor, manager) values ( 34, 'Stationary', 5, 1, 33); insert into dept (number, name, store, floor, manager) values ( 47, 'Junior Miss', 7, 2, 129); insert into dept (number, name, store, floor, manager) values ( 28, 'Women''s', 8, 2, 32); -- Fill table "item" insert into item (number, name, dept, price, qoh, supplier) values (26, 'Earrings', 14, 1000, 20, 199); insert into item (number, name, dept, price, qoh, supplier) values (118, 'Towels, Bath', 26, 250, 1000, 213); insert into item (number, name, dept, price, qoh, supplier) values (43, 'Maze', 49, 325, 200, 89); insert into item (number, name, dept, price, qoh, supplier) values (106, 'Clock Book', 49, 198, 150, 125); insert into item (number, name, dept, price, qoh, supplier) values (23, '1 lb Box', 10, 215, 100, 42); insert into item (number, name, dept, price, qoh, supplier) values (52, 'Jacket', 60, 3295, 300, 15); insert into item (number, name, dept, price, qoh, supplier) values (165, 'Jean', 65, 825, 500, 33); insert into item (number, name, dept, price, qoh, supplier) values (258, 'Shirt', 58, 650, 1200, 33); insert into item (number, name, dept, price, qoh, supplier) values (120, 'Twin Sheet', 26, 800, 750, 213); insert into item (number, name, dept, price, qoh, supplier) values (301, 'Boy''s Jean Suit', 43, 1250, 500, 33); insert into item (number, name, dept, price, qoh, supplier) values (121, 'Queen Sheet', 26, 1375, 600, 213); insert into item (number, name, dept, price, qoh, supplier) values (101, 'Slacks', 63, 1600, 325, 15); insert into item (number, name, dept, price, qoh, supplier) values (115, 'Gold Ring', 14, 4995, 10, 199); insert into item (number, name, dept, price, qoh, supplier) values (25, '2 lb Box, Mix', 10, 450, 75, 42); insert into item (number, name, dept, price, qoh, supplier) values (119, 'Squeeze Ball', 49, 250, 400, 89); insert into item (number, name, dept, price, qoh, supplier) values (11, 'Wash Cloth', 1, 75, 575, 213); insert into item (number, name, dept, price, qoh, supplier) values (19, 'Bellbottoms', 43, 450, 600, 33); insert into item (number, name, dept, price, qoh, supplier) values (21, 'ABC Blocks', 1, 198, 405, 125); insert into item (number, name, dept, price, qoh, supplier) values (107, 'The ''Feel'' Book', 35, 225, 225, 89); insert into item (number, name, dept, price, qoh, supplier) values (121, 'Ski Jumpsuit', 65, 4350, 125, 15); -- Fill table "sale" insert into sale (number, date, store, dept, item, quantity, employee, credit) values (100581, '75-01-15', 7, 26, 118, 5, 157, '00000000'); insert into sale (number, date, store, dept, item, quantity, employee, credit) values (100581, '75-01-15', 7, 26, 120, 1, 157, '00000000'); insert into sale (number, date, store, dept, item, quantity, employee, credit) values (100582, '75-01-15', 8, 14, 26, 1, 1110, '24356540'); insert into sale (number, date, store, dept, item, quantity, employee, credit) values (100586, '75-01-16', 8, 43, 127, 3, 35, '54096831'); insert into sale (number, date, store, dept, item, quantity, employee, credit) values (100586, '75-01-16', 8, 49, 106, 2, 35, '54096831'); insert into sale (number, date, store, dept, item, quantity, employee, credit) values (100592, '75-01-17', 7, 58, 258, 1, 129, '00000000'); insert into sale (number, date, store, dept, item, quantity, employee, credit) values (100593, '75-01-18', 5, 10, 23, 2, 13, '11652133'); insert into sale (number, date, store, dept, item, quantity, employee, credit) values (100594, '75-01-18', 5, 60, 52, 1, 215, '12591815'); -- Fill table "parts" insert into parts (pnum, pname, color, weight, qoh) values ( 1, 'central processor', 'pink', 10, 1); insert into parts (pnum, pname, color, weight, qoh) values ( 2, 'memory', 'gray', 20, 32); insert into parts (pnum, pname, color, weight, qoh) values ( 3, 'disk drive', 'black', 685, 2); insert into parts (pnum, pname, color, weight, qoh) values ( 4, 'tape drive', 'black', 450, 4); insert into parts (pnum, pname, color, weight, qoh) values ( 5, 'tapes', 'gray', 1, 250); insert into parts (pnum, pname, color, weight, qoh) values ( 6, 'line printer', 'yellow', 578, 3); insert into parts (pnum, pname, color, weight, qoh) values ( 7, 'l-p paper', 'white', 15, 95); insert into parts (pnum, pname, color, weight, qoh) values ( 8, 'terminals', 'blue', 19, 15); insert into parts (pnum, pname, color, weight, qoh) values ( 13, 'paper tape reader', 'black', 107, 0); insert into parts (pnum, pname, color, weight, qoh) values ( 14, 'paper tape punch', 'black', 147, 0); insert into parts (pnum, pname, color, weight, qoh) values ( 9, 'terminal paper', 'white', 2, 350); insert into parts (pnum, pname, color, weight, qoh) values ( 10, 'byte-soap', 'clear', 0, 143); insert into parts (pnum, pname, color, weight, qoh) values ( 11, 'card reader', 'gray', 327, 0); insert into parts (pnum, pname, color, weight, qoh) values ( 12, 'card punch', 'gray', 427, 0); -- Fill table "supply" insert into supply (snum, pnum, jnum, shipdate, quan) values ( 475, 1, 1001, '73-12-31', 1); insert into supply (snum, pnum, jnum, shipdate, quan) values ( 475, 2, 1002, '74-05-31', 32); insert into supply (snum, pnum, jnum, shipdate, quan) values ( 475, 3, 1001, '73-12-31', 2); insert into supply (snum, pnum, jnum, shipdate, quan) values ( 475, 4, 1002, '74-05-31', 1); insert into supply (snum, pnum, jnum, shipdate, quan) values ( 122, 7, 1003, '75-02-01', 144); insert into supply (snum, pnum, jnum, shipdate, quan) values ( 122, 7, 1004, '75-02-01', 48); insert into supply (snum, pnum, jnum, shipdate, quan) values ( 122, 9, 1004, '75-02-01', 144); insert into supply (snum, pnum, jnum, shipdate, quan) values ( 440, 6, 1001, '74-10-10', 2); insert into supply (snum, pnum, jnum, shipdate, quan) values ( 241, 4, 1001, '73-12-31', 1); insert into supply (snum, pnum, jnum, shipdate, quan) values ( 62, 3, 1002, '74-06-18', 3); insert into supply (snum, pnum, jnum, shipdate, quan) values ( 475, 2, 1001, '73-12-31', 32); insert into supply (snum, pnum, jnum, shipdate, quan) values ( 475, 1, 1002, '74-07-01', 1); insert into supply (snum, pnum, jnum, shipdate, quan) values ( 5, 4, 1003, '74-11-15', 3); insert into supply (snum, pnum, jnum, shipdate, quan) values ( 5, 4, 1004, '75-01-22', 6); insert into supply (snum, pnum, jnum, shipdate, quan) values ( 20, 5, 1001, '75-01-10', 20); insert into supply (snum, pnum, jnum, shipdate, quan) values ( 20, 5, 1002, '75-01-10', 75); insert into supply (snum, pnum, jnum, shipdate, quan) values ( 241, 1, 1005, '75-06-01', 1); insert into supply (snum, pnum, jnum, shipdate, quan) values ( 241, 2, 1005, '75-06-01', 32); insert into supply (snum, pnum, jnum, shipdate, quan) values ( 241, 3, 1005, '75-06-01', 1); insert into supply (snum, pnum, jnum, shipdate, quan) values ( 67, 4, 1005, '75-07-01', 1); insert into supply (snum, pnum, jnum, shipdate, quan) values ( 999, 10, 1006, '76-01-01', 144); insert into supply (snum, pnum, jnum, shipdate, quan) values ( 241, 8, 1005, '75-07-01', 1); insert into supply (snum, pnum, jnum, shipdate, quan) values ( 241, 9, 1005, '75-07-01', 144); -- List the data select * from supplier; select * from store; select * from employee; select * from dept; select * from item; select * from sale; select * from parts; select * from supply;