Description of this paper

Loading

Using the Order Schema, write a script to complete the following-(Answered)

Description

Instant Solution ? Click "Buy button" to Download the solution File


Question

Using the Order Schema, write a script to complete the following tasks.

  • 1. Create a stored procedure that accepts all the attributes of the client table and the stored procedure should be able to:
    • ? ? ? ? ? ?a. Using the IF statements blocks to determine which state? they are from and have the following statement displayed:
    • ? ? ? ? ? ? ? ? ? ? ? i. If it is from "IL" then say "Another IL Customer"
    • ? ? ? ? ? ? ? ? ? ? ?ii. If it is from "CA" then say "The Golden State"
    • ? ? ? ? ? ? ? ? ? ? iii.?If it is from "TX" then say "Another TX client"
    • ? ? ? ? ? ? ? ? ? ? iv. If none of the three above, then say? "New customer, new state"
    • ? ? ? ? ? ? b. The stored procedure should be able to insert the new rows onto the table.
    • ? ? ? ? ? ? c. Execute procedure.
  • 2. Create a procedure block that will return/display the information all the information of client number 2 from the client table.

{\rtf1\ansi\ansicpg1252\cocoartf1404\cocoasubrtf460

 

{\fonttbl\f0\fmodern\fcharset0 Courier;}

 

{\colortbl;\red255\green255\blue255;\red0\green0\blue0;}

 

\margl1440\margr1440\vieww10800\viewh8400\viewkind0

 

\deftab720

 

\pard\pardeftab720\sl280\partightenfactor0

 


 

\f0\fs24 \cf2 \expnd0\expndtw0\kerning0

 

\outl0\strokewidth0 \strokec2 /* Drop tables clauses */\

 

DROP TABLE item_invoice CASCADE CONSTRAINTS;\

 

DROP TABLE invoice CASCADE CONSTRAINTS;\

 

DROP TABLE item CASCADE CONSTRAINTS;\

 

DROP TABLE client CASCADE CONSTRAINTS;\

 

\

 

/* Create tables */\

 

CREATE TABLE client (\

 

clientid NUMBER(3),\

 

clientFName VARCHAR2(30) NOT NULL,\

 

clientLName VARCHAR2(30) NOT NULL,\

 

clientDOB DATE, \

 

clientCity VARCHAR2(30) NOT NULL,\

 

clientSate VARCHAR2(2) NOT NULL, \

 

CONSTRAINT client_cid_pk PRIMARY KEY (clientid)\

 

);\

 

\

 

CREATE TABLE item (\

 

itemID NUMBER(4), \

 

itemName VARCHAR(50) UNIQUE,\

 

itemPrice NUMBER(6,2), \

 

itemRetailPrice NUMBER(6,2), \

 

CONSTRAINT itemID_pk PRIMARY KEY (itemID), \

 

CONSTRAINT itemRP_ck CHECK (itemRetailPrice > itemPrice)\

 

);\

 

\

 

CREATE TABLE invoice (\

 

invoiceID NUMBER(4),\

 

invoiceDate DATE,\

 

clientID NUMBER(3), \

 

CONSTRAINT inv_invid_pk PRIMARY KEY (invoiceid),\

 

CONSTRAINT clientId_fk FOREIGN KEY (clientId) REFERENCES client (clientId)\

 

);\

 

\

 

CREATE TABLE item_invoice (\

 

itemId NUMBER(4),\

 

invoiceId NUMBER(4),\

 

quantity NUMBER(3) NOT NULL,\

 

CONSTRAINT itm_inv_pk PRIMARY KEY (itemid, invoiceid), \

 

CONSTRAINT itm_fk FOREIGN KEY (itemid) REFERENCES item (itemid), \

 

CONSTRAINT inv_fk FOREIGN KEY (invoiceid) REFERENCES invoice (invoiceid)\

 

);\

 

\

 

/* Insert Statements Begin here */\

 

INSERT INTO client VALUES (1, 'John', 'Smith', '01-Jan-1989', 'Chicago', 'IL');\

 

INSERT INTO client VALUES (2, 'Anna', 'McDonald', '', 'Chicago', 'IL');\

 

INSERT INTO client VALUES (3, 'Nancy', 'Lavene', '05-Jul-1984', 'Evanston', 'IL');\

 

INSERT INTO client VALUES (4, 'Peter', 'Cruz', '', 'Skokie', 'IL');\

 

INSERT INTO client VALUES (5, 'Randy', 'Johnson', '27-Jul-1975', 'Dallas', 'TX');\

 

INSERT INTO client VALUES (6, 'Julio', 'Sanchez', '14-Aug-1980', 'Fort Worth', 'TX');\

 

INSERT INTO client VALUES (7, 'Shaun', 'Tang', '', 'Dallas', 'TX');\

 

INSERT INTO client VALUES (9, 'Rina', 'Singh', '15-Oct-1981', 'Houston', 'TX');\

 

INSERT INTO client VALUES (10, 'Gwenn', 'Freedman', '', 'San Antonio', 'TX');\

 

INSERT INTO client VALUES (11, 'David', 'Johnson', '22-Mar-1979', 'Aurora', 'IL');\

 

INSERT INTO client VALUES (12, 'Martha', 'Carr', '25-Mar-1984', 'Chicago', 'IL');\

 

INSERT INTO client VALUES (13, 'Vivian', 'Winters', '22-Oct-1983', 'Plano', 'TX');\

 

INSERT INTO client VALUES (14, 'Carolyn', 'Mares', '', 'Skokie', 'IL');\

 

INSERT INTO client VALUES (15, 'Burim', 'Bakalli', '01-Jul-1983', 'Joliet', 'IL');\

 

INSERT INTO client VALUES (16, 'Marilyn', 'Hammond', '', 'Dallas', 'TX');\

 

INSERT INTO client VALUES (17, 'Tania', 'Smith', '09-Jan-1986', 'Hollywood', 'CA');\

 

INSERT INTO client VALUES (18, 'Ruth', 'Babe', '', 'San Francisco', 'CA');\

 

INSERT INTO client VALUES (19, 'Meghan', 'Irish', '15-Feb-1977', 'San Francisco', 'CA');\

 

INSERT INTO client VALUES (20, 'Conan', 'Obrien', '', 'Los Angeles', 'CA');\

 

INSERT INTO client VALUES (21, 'Jamie', 'Richards', '29-Dec-1986', 'San Diego', 'CA');\

 

INSERT INTO client VALUES (22, 'John', 'Doe', '04-Apr-1972', 'San Diego', 'CA');\

 

INSERT INTO client VALUES (23, 'Tina', 'Randall', '07-Jun-1990', 'Plano', 'TX');\

 

INSERT INTO client VALUES (24, 'Valerie', 'Mata', '20-Jan-1989', 'Chicago', 'IL');\

 

INSERT INTO client VALUES (25, 'Mariska', 'Lunas', '', 'Milwaukee', 'WI');\

 

INSERT INTO item VALUES (1, 'Laptop E5520', 400, 850);\

 

INSERT INTO item VALUES (2, 'Laptop E5540', 450, 900);\

 

INSERT INTO item VALUES (3, 'Laptop E5560', 500, 950);\

 

INSERT INTO item VALUES (4, 'Laptop E5580', 550, 1000);\

 

INSERT INTO item VALUES (5, 'Laptop E6520', 600, 1050);\

 

INSERT INTO item VALUES (6, 'Laptop E6540', 650, 1100);\

 

INSERT INTO item VALUES (7, 'Laptop E6560', 700, 1150);\

 

INSERT INTO item VALUES (8, 'Laptop E6580', 750, 1200);\

 

INSERT INTO item VALUES (9, 'USB Drive 16gb', 15, 32.99);\

 

INSERT INTO item VALUES (10, 'USB Drive 32gb', 10, 37.99);\

 

INSERT INTO item VALUES (11, 'USB Drive 8gb', 5, 20);\

 

INSERT INTO item VALUES (12, 'Desktop 380', 200, 550);\

 

INSERT INTO item VALUES (13, 'Desktop 480', 245, 600);\

 

INSERT INTO item VALUES (14, 'Desktop 580', 260, 650);\

 

INSERT INTO item VALUES (15, 'Desktop 680', 300, 700);\

 

INSERT INTO item VALUES (16, 'Desktop 780', 350, 750);\

 

INSERT INTO item VALUES (17, 'Server E9900' ,4000, 5050.99);\

 

INSERT INTO item VALUES (18, 'Server E8900' ,3500, 4800);\

 

INSERT INTO item VALUES (19, 'Server E7900' ,3300, 4399.99);\

 

INSERT INTO item VALUES (20, 'Monitor 21', 75, 149.99);\

 

INSERT INTO item VALUES (21, 'Monitor 22', 60, 169.99);\

 

INSERT INTO item VALUES (22, 'Monitor 19', 50, 105.99);\

 

INSERT INTO item VALUES (23, 'Printer 8700', 50, 89.99);\

 

INSERT INTO item VALUES (24, 'Printer 9700', 60, 99.89);\

 

INSERT INTO item VALUES (25, 'Printer 7700', 40, 74.99);\

 

INSERT INTO invoice VALUES (6, '9-Mar-2015', 1);\

 

INSERT INTO invoice VALUES (7, '18-Nov-2015', 1);\

 

INSERT INTO invoice VALUES (24, '23-Dec-2014', 1);\

 

INSERT INTO invoice VALUES (14, '25-Feb-2015', 2);\

 

INSERT INTO invoice VALUES (12, '4-Aug-2015', 6);\

 

INSERT INTO invoice VALUES (9, '3-Apr-2014', 1);\

 

INSERT INTO invoice VALUES (10, '3-May-2015', 9);\

 

INSERT INTO invoice VALUES (3, '5-Mar-2016', 10);\

 

INSERT INTO invoice VALUES (16, '11-Oct-2014', 10);\

 

INSERT INTO invoice VALUES (19, '23-Mar-2016', 10);\

 

INSERT INTO invoice VALUES (15, '28-Jun-2015', 11);\

 

INSERT INTO invoice VALUES (25, '12-Jun-2015', 11);\

 

INSERT INTO invoice VALUES (23, '22-Mar-2016', 12);\

 

INSERT INTO invoice VALUES (8, '23-Jan-2016', 13);\

 

INSERT INTO invoice VALUES (17, '23-Mar-2014', 13);\

 

INSERT INTO invoice VALUES (18, '19-Jan-2016', 14);\

 

INSERT INTO invoice VALUES (20, '9-Mar-2016', 15);\

 

INSERT INTO invoice VALUES (11, '1-Oct-2014', 16);\

 

INSERT INTO invoice VALUES (1, '7-Oct-2015', 21);\

 

INSERT INTO invoice VALUES (13, '20-May-2014', 21);\

 

INSERT INTO invoice VALUES (22, '25-Oct-2014', 21);\

 

INSERT INTO invoice VALUES (2, '7-Apr-2015', 22);\

 

INSERT INTO invoice VALUES (4, '5-Feb-2015', 22);\

 

INSERT INTO invoice VALUES (5, '22-Mar-2016', 22);\

 

INSERT INTO invoice VALUES (21, '29-May-2014', 25);\

 

INSERT INTO item_invoice VALUES (1, 23, 5);\

 

INSERT INTO item_invoice VALUES (3, 1, 8);\

 

INSERT INTO item_invoice VALUES (4, 12, 7);\

 

INSERT INTO item_invoice VALUES (4, 1, 4);\

 

INSERT INTO item_invoice VALUES (5, 19, 1);\

 

INSERT INTO item_invoice VALUES (5, 14, 8);\

 

INSERT INTO item_invoice VALUES (6, 9, 1);\

 

INSERT INTO item_invoice VALUES (7, 6, 7);\

 

INSERT INTO item_invoice VALUES (8, 11, 9);\

 

INSERT INTO item_invoice VALUES (8, 10, 4);\

 

INSERT INTO item_invoice VALUES (10, 1, 4);\

 

INSERT INTO item_invoice VALUES (11, 4, 10);\

 

INSERT INTO item_invoice VALUES (11, 17, 4);\

 

INSERT INTO item_invoice VALUES (11, 23, 2);\

 

INSERT INTO item_invoice VALUES (11, 12, 9);\

 

INSERT INTO item_invoice VALUES (11, 6, 3);\

 

INSERT INTO item_invoice VALUES (12, 4, 6);\

 

INSERT INTO item_invoice VALUES (12, 23, 10);\

 

INSERT INTO item_invoice VALUES (12, 1, 2);\

 

INSERT INTO item_invoice VALUES (13, 13, 2);\

 

INSERT INTO item_invoice VALUES (14, 7, 9);\

 

INSERT INTO item_invoice VALUES (14, 11, 3);\

 

INSERT INTO item_invoice VALUES (14, 12, 1);\

 

INSERT INTO item_invoice VALUES (15, 4, 8);\

 

INSERT INTO item_invoice VALUES (16, 13, 5);\

 

INSERT INTO item_invoice VALUES (17, 1, 8);\

 

INSERT INTO item_invoice VALUES (17, 4, 9);\

 

INSERT INTO item_invoice VALUES (17, 22, 2);\

 

INSERT INTO item_invoice VALUES (17, 19, 8);\

 

INSERT INTO item_invoice VALUES (18, 1, 4);\

 

INSERT INTO item_invoice VALUES (18, 16, 3);\

 

INSERT INTO item_invoice VALUES (18, 3, 1);\

 

INSERT INTO item_invoice VALUES (18, 15, 3);\

 

INSERT INTO item_invoice VALUES (19, 12, 4);\

 

INSERT INTO item_invoice VALUES (19, 8, 4);\

 

INSERT INTO item_invoice VALUES (19, 16, 10);\

 

INSERT INTO item_invoice VALUES (20, 21, 1);\

 

INSERT INTO item_invoice VALUES (21, 24, 7);\

 

INSERT INTO item_invoice VALUES (22, 19, 1);\

 

INSERT INTO item_invoice VALUES (23, 22, 9);\

 

INSERT INTO item_invoice VALUES (23, 21, 4);\

 

INSERT INTO item_invoice VALUES (23, 8, 9);\

 

INSERT INTO item_invoice VALUES (23, 20, 9);\

 

INSERT INTO item_invoice VALUES (24, 5, 6);\

 

INSERT INTO item_invoice VALUES (24, 7, 2);\

 

INSERT INTO item_invoice VALUES (25, 16, 4);\

 

INSERT INTO item_invoice VALUES (25, 3, 5);\

 

}

 

Paper#9209362 | Written in 27-Jul-2016

Price : $19
SiteLock