1 / 50

PostgreSQL Customer Order Example

PostgreSQL Customer Order Example. ILS Postgresql server. PHP PGAdmin https://ella.ils.indiana.edu/adm/phppgadmin/ Get your username and password from SLIS IT department. Create Schema. Create Schema. Create Table Schema. Create Tables. M. 1. M. 1. CUSTOMER. order. ORDERINFO. has.

duboisd
Download Presentation

PostgreSQL Customer Order Example

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. PostgreSQLCustomer Order Example

  2. ILS Postgresql server • PHP PGAdmin • https://ella.ils.indiana.edu/adm/phppgadmin/ • Get your username and password from SLIS IT department

  3. Create Schema

  4. Create Schema

  5. Create Table Schema

  6. Create Tables M 1 M 1 CUSTOMER order ORDERINFO has ORDERLINE 1 has 1 M 1 STOCK has ITEM M has 1 BARCODE

  7. Create Tables create table customer ( customer_id serial , title char(4) , fnamevarchar(32) , lnamevarchar(32) not null, addresslinevarchar(64) , town varchar(32) , zipcode char(10) not null, phone varchar(16) , CONSTRAINT customer_pk PRIMARY KEY(customer_id) );

  8. Create Tables create table item ( item_id serial , description varchar(64) not null, cost_price numeric(7,2) , sell_price numeric(7,2) , CONSTRAINT item_pk PRIMARY KEY(item_id) ); create table orderinfo ( orderinfo_id serial , customer_id integer not null, date_placed date not null, date_shipped date , shipping numeric(7,2) , CONSTRAINT orderinfo_pk PRIMARY KEY(orderinfo_id) );

  9. Create Tables create table stock ( item_id integer not null, quantity integer not null, CONSTRAINT stock_pk PRIMARY KEY(item_id) ); create table orderline ( orderinfo_id integer not null, item_id integer not null, quantity integer not null, CONSTRAINT orderline_pk PRIMARY KEY(orderinfo_id, item_id) ); create table barcode ( barcode_ean char(13) not null, item_id integer not null, CONSTRAINT barcode_pk PRIMARY KEY(barcode_ean) );

  10. Insert values -- Customers insert into customer(title, fname, lname, addressline, town, zipcode, phone) values('Miss','Jenny','Stones','27 Rowan Avenue','Hightown','NT2 1AQ','023 9876'); insert into customer(title, fname, lname, addressline, town, zipcode, phone) values('Mr','Andrew','Stones','52 The Willows','Lowtown','LT5 7RA','876 3527'); insert into customer(title, fname, lname, addressline, town, zipcode, phone) values('Miss','Alex','Matthew','4 The Street','Nicetown','NT2 2TX','010 4567'); insert into customer(title, fname, lname, addressline, town, zipcode, phone) values('Mr','Adrian','Matthew','The Barn','Yuleville','YV67 2WR','487 3871'); insert into customer(title, fname, lname, addressline, town, zipcode, phone) values('Mr','Simon','Cozens','7 Shady Lane','Oakenham','OA3 6QW','514 5926'); insert into customer(title, fname, lname, addressline, town, zipcode, phone) values('Mr','Neil','Matthew','5 Pasture Lane','Nicetown','NT3 7RT','267 1232'); insert into customer(title, fname, lname, addressline, town, zipcode, phone) values('Mr','Richard','Stones','34 Holly Way','Bingham','BG4 2WE','342 5982'); insert into customer(title, fname, lname, addressline, town, zipcode, phone) values('Mrs','Ann','Stones','34 Holly Way','Bingham','BG4 2WE','342 5982'); insert into customer(title, fname, lname, addressline, town, zipcode, phone) values('Mrs','Christine','Hickman','36 Queen Street','Histon','HT3 5EM','342 5432'); insert into customer(title, fname, lname, addressline, town, zipcode, phone) values('Mr','Mike','Howard','86 Dysart Street','Tibsville','TB3 7FG','505 5482'); insert into customer(title, fname, lname, addressline, town, zipcode, phone) values('Mr','Dave','Jones','54 Vale Rise','Bingham','BG3 8GD','342 8264'); insert into customer(title, fname, lname, addressline, town, zipcode, phone) values('Mr','Richard','Neill','42 Thatched Way','Winnersby','WB3 6GQ','505 6482'); insert into customer(title, fname, lname, addressline, town, zipcode, phone) values('Mrs','Laura','Hardy','73 Margarita Way','Oxbridge','OX2 3HX','821 2335'); insert into customer(title, fname, lname, addressline, town, zipcode, phone) values('Mr','Bill','O\'Neill','2 Beamer Street','Welltown','WT3 8GM','435 1234'); insert into customer(title, fname, lname, addressline, town, zipcode, phone) values('Mr','David','Hudson','4 The Square','Milltown','MT2 6RT','961 4526');

  11. Insert values -- Items insert into item(description, cost_price, sell_price) values('Wood Puzzle', 15.23, 21.95); insert into item(description, cost_price, sell_price) values('Rubik Cube', 7.45, 11.49); insert into item(description, cost_price, sell_price) values('Linux CD', 1.99, 2.49); insert into item(description, cost_price, sell_price) values('Tissues', 2.11, 3.99); insert into item(description, cost_price, sell_price) values('Picture Frame', 7.54, 9.95); insert into item(description, cost_price, sell_price) values('Fan Small', 9.23, 15.75); insert into item(description, cost_price, sell_price) values('Fan Large', 13.36, 19.95); insert into item(description, cost_price, sell_price) values('Toothbrush', 0.75, 1.45); insert into item(description, cost_price, sell_price) values('Roman Coin', 2.34, 2.45); insert into item(description, cost_price, sell_price) values('Carrier Bag', 0.01, 0.0); insert into item(description, cost_price, sell_price) values('Speakers', 19.73, 25.32);

  12. Insert values -- Barcodes insert into barcode(barcode_ean, item_id) values('6241527836173', 1); insert into barcode(barcode_ean, item_id) values('6241574635234', 2); insert into barcode(barcode_ean, item_id) values('6264537836173', 3); insert into barcode(barcode_ean, item_id) values('6241527746363', 3); insert into barcode(barcode_ean, item_id) values('7465743843764', 4); insert into barcode(barcode_ean, item_id) values('3453458677628', 5); insert into barcode(barcode_ean, item_id) values('6434564564544', 6); insert into barcode(barcode_ean, item_id) values('8476736836876', 7); insert into barcode(barcode_ean, item_id) values('6241234586487', 8); insert into barcode(barcode_ean, item_id) values('9473625532534', 8); insert into barcode(barcode_ean, item_id) values('9473627464543', 8); insert into barcode(barcode_ean, item_id) values('4587263646878', 9); insert into barcode(barcode_ean, item_id) values('9879879837489', 11); insert into barcode(barcode_ean, item_id) values('2239872376872', 11);

  13. Insert values -- Stock insert into stock(item_id, quantity) values(1,12); insert into stock(item_id, quantity) values(2,2); insert into stock(item_id, quantity) values(4,8); insert into stock(item_id, quantity) values(5,3); insert into stock(item_id, quantity) values(7,8); insert into stock(item_id, quantity) values(8,18); insert into stock(item_id, quantity) values(10,1); -- Order info insert into orderinfo(customer_id, date_placed, date_shipped, shipping) values(3,'03-13-2004','03-17-2004', 2.99); insert into orderinfo(customer_id, date_placed, date_shipped, shipping) values(8,'06-23-2004','06-24-2004', 0.00); insert into orderinfo(customer_id, date_placed, date_shipped, shipping) values(15,'09-02-2004','09-12-2004', 3.99); insert into orderinfo(customer_id, date_placed, date_shipped, shipping) values(13,'09-03-2004','09-10-2004', 2.99); insert into orderinfo(customer_id, date_placed, date_shipped, shipping) values(8,'07-21-2004','07-24-2004', 0.00);

  14. Insert values -- Order line insert into orderline(orderinfo_id, item_id, quantity) values(1, 4, 1); insert into orderline(orderinfo_id, item_id, quantity) values(1, 7, 1); insert into orderline(orderinfo_id, item_id, quantity) values(1, 9, 1); insert into orderline(orderinfo_id, item_id, quantity) values(2, 1, 1); insert into orderline(orderinfo_id, item_id, quantity) values(2, 10, 1); insert into orderline(orderinfo_id, item_id, quantity) values(2, 7, 2); insert into orderline(orderinfo_id, item_id, quantity) values(2, 4, 2); insert into orderline(orderinfo_id, item_id, quantity) values(3, 2, 1); insert into orderline(orderinfo_id, item_id, quantity) values(3, 1, 1); insert into orderline(orderinfo_id, item_id, quantity) values(4, 5, 2); insert into orderline(orderinfo_id, item_id, quantity) values(5, 1, 1); insert into orderline(orderinfo_id, item_id, quantity) values(5, 3, 1);

  15. SQL Queries • select town, lname AS "Last Name" from customer order by town DESC, lname ASC; • select distinct town from customer; • select description, cost_price*100 as "Cost Price" from item;

  16. SQL Queries • select title, fname, lname, town from customer where title <> 'Mr' and (town='Bingham' or town='Nicetown'); • select title, fname, lname, town from customer where title <> 'Mr' and town in ('Bingham','Nicetown');

  17. SQL Queries • select distinct town from customer where town between 'B' and 'N'; • select distinct town from customer where town between 'B' and 'Nz';

  18. SQL Queries • select fname, lname from customer where fname like '_a%'; • select customer_id, town from customer limit 5 offset 2;

  19. SQL Queries • select * from orderinfo where date_part('month', date_placed)=9; • select * from orderinfo where date_placed>=cast('2004 07 21' as date); • select date_shipped - date_placed from orderinfo;

  20. More tables • select customer.fname, orderinfo.date_placed from customer, orderinfo where customer.fname='Ann' and customer.lname='Stones' and customer.customer_id=orderinfo.customer_id; • select description, cost_price, barcode_ean from item, barcode where barcode.item_id=item.item_id order by cost_price;

  21. More tables • select cu.fname, oi.date_placed from customer cu, orderinfo oi where cu.fname='Ann' and cu.lname='Stones' and cu.customer_id=oi.customer_id;

  22. More tables • select customer.fname, customer.lname, orderinfo.date_placed, item.description, orderline.quantity from customer, orderinfo, orderline, item where customer.customer_id=orderinfo.customer_id and orderinfo.orderinfo_id=orderline.orderinfo_id and orderline.item_id=item.item_id and customer.fname='Ann' and customer.lname='Stones';

  23. More tables select customer.fname, customer.lname, orderinfo.date_placed, item.description, orderline.quantity from customer join orderinfo on customer.customer_id=orderinfo.customer_id join orderline on orderinfo.orderinfo_id=orderline.orderinfo_id join item on orderline.item_id=item.item_id where customer.fname='Ann' and customer.lname='Stones';

  24. SQL Queries • select count(*) from customer where town='Bingham'; • select count(*), lname, town from customer group by town, lname; • select count(*), lname, town from customer group by town, lname order by town, lname;

  25. SQL Queries • select count(*), town from customer group by town having count(*)>1; • select count(*), lname, town from customer where town <> 'Lincoln' group by lname, town having count(*)>1; • select count(*), lname, town from customer where town <> 'Lincoln' group by lname, town order by town;

  26. SQL queries • select customer_id from customer where phone IS NULL; • select count(phone) from customer; • select count(distinct town) as "distinct", count(town) as "all" from customer;

  27. SQL Queries • select avg(shipping) from orderinfo; • select avg(distinct shipping) from orderinfo;

  28. Subquery • select * from item where cost_price>cast(7.249 as numeric(7,2)); • select * from item where cost_price> (select avg(cost_price) from item); • select * from item where cost_price> (select avg(cost_price) from item) and sell_price < (select avg(sell_price) from item);

  29. Subquery • select * from stock where item_id in (select item_id from item where cost_price > cast(10.0 as numeric(7,2))); • select item.item_id, item.description, stock.quantity from stock join item on stock.item_id=item.item_id where stock.item_id in (select item.item_id from item where cost_price > cast(10.0 as numeric(7,2))); • select oi.date_placed from orderinfo oi where oi.customer_id= (select c.customer_id from customer c where c.customer_id=oi.customer_id and town='Bingham');

  30. Subquery • select i.item_id from item i where i.item_id not in (select i.item_id from item i, stock s where i.item_id=s.item_id);

  31. Join • select i.item_id, s.quantity from item i left outer join stock s on i.item_id=s.item_id; • select i.item_id, i.cost_price, s. quantity from item i left outer join stock s on i.item_id=s.item_id and s.quantity >2 where i.cost_price > cast(5.0 as numeric(7,2));

  32. View • create view item_price as select item_id, description::varchar(10), sell_price as price from item where sell_price <=20.0; • select * from item_price; • Drop view item_price;

  33. Transactions Initial State BEGIN First SQL Second SQL Rollback Initial State BEGIN First SQL Second SQL COMMIT …

  34. Transactions create table ttest1 ( ival1 integer, sval1 varchar(64) ); create table ttest2 ( ival2 integer, sval2 varchar(64) );

  35. Transactions • insert into ttest1(ival1, sval1) values(1, 'David'); • begin; update ttest1 set sval1='Dave' where ival1=1; select sval1 from ttest1 where ival1=1;

  36. Transactions delete from ttest1; insert into ttest1(ival1, sval1) values(1, 'David'); Begin; update ttest1 set sval1='Dave' where ival1=1; savepoint a; update ttest1 set sval1='Ying' where ival1=1; rollback to a; select * from ttest1;

  37. Transactions delete from ttest1; insert into ttest1(ival1, sval1) values(1, 'David'); Begin; update ttest1 set sval1='Dave' where ival1=1; savepoint a; update ttest1 set sval1='Ying' where ival1=1; Savepoint b; rollback to b; select * from ttest1;

  38. Transactions delete from ttest1; delete from ttest2; insert into ttest1(ival1, sval1) values (1, 'David'); begin; insert into ttest2(ival2, sval2) values (42, 'Arthur'); update ttest1 set sval1='Robert' where ival1=1; select * from ttest1;

  39. Transactions delete from ttest1; delete from ttest2; insert into ttest1(ival1, sval1) values (1, 'David'); begin; insert into ttest2(ival2, sval2) values (42, 'Arthur'); update ttest1 set sval1='Robert' where ival1=1; select * from ttest2;

  40. Functions • create language plpgsql; (if you get error, you just delete this line) • create function add_one(int4) returns int4 as 'begin return $1+1; end; 'language 'plpgsql'; • select add_one(2) as answer; • Drop function add_one(int4);

  41. Procedure create table reorders ( item_id integer, message text ); -- reorders -- scan the stock table to raise reorders of item low on stock create function reorders(min_stock int4) returns integer as $$ declare reorder_item integer; reorder_count integer; stock_row stock%rowtype; msg text;

  42. Procedure begin select count(*) into reorder_count from stock where quantity <= min_stock; for stock_row in select * from stock where quantity <= min_stock loop declare item_row item%rowtype; begin select * into item_row from item where item_id = stock_row.item_id; msg = 'order more ' || item_row.description || 's at ' || to_char(item_row.cost_price,'99.99'); insert into reorders values (stock_row.item_id, msg); end; end loop; return reorder_count; end; $$ language plpgsql; select reorders(3); select * from reorders;

  43. Triggers • Trigger: can be used to execute a stored procedure when certain actions are taken, like INSERT, DELETE, UPDATE in a table. • The combination of stored procedures and triggers gives us the power to enforce quite sophisticated business rules (such as defined constraints) • To use a trigger, we need to first define a trigger procedure, then create the trigger which will execute the trigger procedure.

  44. Triggers • A trigger procedure is created with the CREATE FUNCTION command, declaring it as a function with no arguments and a return type of trigger. • The function must be declared with no arguments even if it expects to receive arguments specified in CREATE TRIGGER — trigger, arguments are passed via TG_ARGV,

  45. Triggers create function reorder_trigger() returns trigger AS $$ declare mq integer; item_record record; begin mq := tg_argv[0]; raise notice 'in trigger, mq is %', mq; if new.quantity <= mq then select * into item_record from item where item_id = new.item_id; insert into reorders values (new.item_id, item_record.description); end if; return NULL; end; $$ language plpgsql; create trigger trig_reorder after insert or update ON stock for each row execute procedure reorder_trigger(3); update stock set quantity=3 where item_id=1; select * from reorders;

  46. Triggers • Drop triggers • drop trigger trig_reorder on stock • drop function reorder_trigger()

  47. Triggers create function customer_trigger() returns trigger AS $$ declare order_record record; begin -- about to delete a customer -- disallow if orders pending select * into order_record from orderinfo where customer_id = old.customer_id and date_shipped is NULL; if not found then -- all OK, delete of customer can proceed raise notice 'deletion allowed: no outstanding orders'; raise notice 'old.customer_id is %', old.customer_id; return NULL; -- for referential integrity we have to tidy up -- we will need to delete all completed orders -- but first delete the information about the orders

  48. Triggers for order_record in select * from orderinfo where customer_id = old.customer_id loop delete from orderline where orderinfo_id = order_record.orderinfo_id; end loop; -- now delete the order records delete from orderinfo where customer_id = old.customer_id; -- return the old record to allow customer to be deleted return old; else -- orders present return NULL to prevent deletion raise notice 'deletion aborted: outstanding orders present'; return NULL; end if; end; $$ language plpgsql;

  49. Triggers create trigger trig_customer before delete on customer for each row execute procedure customer_trigger(); update orderinfo set date_shipped=NULL where orderinfo_id=3; select * from orderinfo; delete from customer where customer_id=15; select * from orderinfo; delete from customer where customer_id=3; select * from orderinfo;

  50. Triggers • Drop triggers • drop trigger trig_customer on customer; • drop function customer_trigger()

More Related