US15-Provide Data Manipulation Language (DML) queries
Description
A a developer, I want to create example sql statements used by the web site to illustrate the way in which the data will generally be accessed and related.
Acceptance Criteria
- Team has approved the DDL structure.
- Team has reviwed the DML within this user story.
Tasks
- Team approve -- 30 mins -- Team
- DML statement creations -- 2 hours -- Drew
Notes
select producttype_id, product_type_name, producttype_desc
from producttype
where producttypecat_id = :producttypecat_id;
select p.product_id,
pt.producttype_name,
b.brand_name,
v.vendor_name,
p.prod_model,
p.prod_name,
p.unit_price,
p.prod_desc
from product p, producttype pt, brand b, vendor v
where p.producttype_id = pt.producttype_id
and p.brand_id = b.brand_id
and p.vendor_id = v.vendor_id
and pt.producttype_id = :producttype_id
and b.brand_id = :brand_id
and p.unit_price >= :min_price
and p.unit_price <= :max_price;
insert into customer
(customer_id,
name_first,
name_last,
phone,
address1,
address2,
zipcode_cd)
values
(:customer_id,
:name_first,
:name_last,
:phone,
:address1,
:address2,
:zipcode_cd);
insert into customerorder
(customerorder_id, customer_id, order_date)
values
(:customerorder_id, :customer_id, :order_date);
insert into customerorderitem
(customerorderitem_id,
customerorder_id,
product_id,
quantity,
item_price)
values
(:customerorderitem_id,
:customerorder_id,
:product_id,
:quantity,
:item_price);
update customerorderitem
set quantity = :quantity
where customerorderitem_id = :customerorderitem_id;
delete from customerorderitem
where customerorderitem_id = :customerorderitem_id;