home / study / engineering / computer science / computer science questions and answers

Using the 2 following scenarios and please help provide the SQL code to use in Oracle. (Use the attached ERD) Let me know if updates are need in the ERD As well! (I have already created tables and such so I just need help with the stored procedures!)

b. Develop a parameterized stored procedure that is used when any seller delivers any product to The companies warehouse.

c. A seller delivers four each of the two new products the selfdriving video camera and the holographic keyboard (already inputted just need updates). Invoke the stored procedure twice to update the inventory of these products for a seller of your choosing.

d. The seller from b above requests a listing of all of its products that have an inventory of 11 or less. Develop and execute a single query that provides this information (the selfdriving video camera and holographic keyboard should be among those listed).

Second Scenario

b. Develop a parameterized stored procedure that is used when any new customer signs up for a new account on the company site.

c. Invoke the stored procedure twice to add Yourself and your Guest/Facilatator as customers.

d. the company requests the last names of customers where there are least 4 accounts associated with the last name. The company would like to see the actual number of accounts associated with those last names. Develop and execute a single query that provides this information. Thank you so Much!

Table Code

CREATE TABLE Sellers( Seller_ID VARCHAR2(50) PRIMARY KEY, (NOT NULL) Seller_first_name VARCHAR2(50), Seller_last_name VARCHAR2(50), Seller_type_code VARCHAR2(50) );

create table category( category_id number, (NOT NULL) category_name varchar2(100), category_desc varchar2(100) );

CREATE TABLE Customer( Customer_ID VARCHAR2(50) PRIMARY KEY, NOT NULL Cus_First_name VARCHAR2(50), Cus_Last_name VARCHAR2(50), Phone_Number NUMBER(20), Email_Address VARCHAR2(50) NOT NULL );

CREATE TABLE Addresses( Address_ID VARCHAR2(50) PRIMARY KEY, NOT NULL number_building VARCHAR2(50), Street_name VARCHAR2(50), Apartment_number VARCHAR2(50), City VARCHAR2(50), zipcode VARCHAR2(50), Country_code VARCHAR2(50) );

CREATE TABLE Product( Product_ID VARCHAR2(50) PRIMARY KEY, NOT NULL Product_category_code VARCHAR2(50), total_product_quantity NUMBER(20), seller_id VARCHAR2(50) REFERENCES Sellers(Seller_Id), product_name VARCHAR2(50), product_desc VARCHAR2(200), product_price NUMBER(10), Product_condition_code VARCHAR2(50) );

CREATE TABLE Customer_Orders( Order_id VARCHAR2(50) UNIQUE, NOT NULL customer_Id VARCHAR2(50) REFERENCES Customer(Customer_ID), Cus_Pay_method VARCHAR2(50), Order_price NUMBER(10), Order_status VARCHAR2(50), Ship_speed_code VARCHAR2(50), Order_placed VARCHAR2(50), PRIMARY KEY(Order_id,customer_id) );

CREATE TABLE Customer_Addresses( Customer_id VARCHAR2(50) REFERENCES Customer(Customer_ID), Address_id VARCHAR2(50) REFERENCES Addresses(Address_id), address_type_code VARCHAR2(50), PRIMARY KEY(Customer_id,Address_id) );

CREATE TABLE Customer_order_products( order_id VARCHAR2(50) REFERENCES Customer_Orders(Order_id), Product_id VARCHAR2(50) REFERENCES Product_Warehouse(Product_ID), Product_quantity VARCHAR2(50), PRIMARY KEY(order_id,Product_id) );

CREATE TABLE Customer_order_delivery( Order_Id VARCHAR2(50) REFERENCES Customer_Orders(Order_id), date_reported DATE, delivery_status_code VARCHAR2(50), PRIMARY KEY(Order_Id) );

CREATE TABLE Bill( Bill_ID VARCHAR2(50) PRIMARY KEY, NOT NULL Order_ID VARCHAR2(50) REFERENCES Customer_Orders(Order_id), Order_price NUMBER(10), date_order_paid DATE, Bill_details VARCHAR2(200) );

Sellers PK Seller Id Seller first name Seller_last_name Seller_ type_code PK Address id number building Street name Apartment_number Customer ID Cus_First_name Cus Last_name Phone number Email address Category Category_ld zipcode Fk product_category_code Category Name Category Descr Country_code Product PK Product ID Product category_code total product quantity seller id product name product desc product_ price Product_conditon_code Customer Orders PK PF PF Customer id Order id customer_id Cus_ Pay_method Order_price Order status Ship speed_code Order_placed PF Address id FK address_type_code BILL PK BIl ID FK Order ID Order_price date_order_paid Bill details Customer_order_products PF order id PF Product_id Customer_ order_delivery PF Order ld date_reported delivery_status_code Product _quantity Sellers PK Seller Id Seller first name Seller_last_name Seller_ type_code PK Address id number building Street name Apartment_number Customer ID Cus_First_name Cus Last_name Phone number Email address Category Category_ld zipcode Fk product_category_code Category Name Category Descr Country_code Product PK Product ID Product category_code total product quantity seller id product name product desc product_ price Product_conditon_code Customer Orders PK PF PF Customer id Order id customer_id Cus_ Pay_method Order_price Order status Ship speed_code Order_placed PF Address id FK address_type_code BILL PK BIl ID FK Order ID Order_price date_order_paid Bill details Customer_order_products PF order id PF Product_id Customer_ order_delivery PF Order ld date_reported delivery_status_code Product _quantity