Working with, Oracle Database 11g Express Edition, With SQL Toys Galore currentl

Question

Working with, Oracle Database 11g Express Edition, With SQL

Toys Galore currently has a credit limit of $7,500. Because Toys Galore has an excellent credit rating, TAL Distributors is increasing the company's credit limit to $10,000. If you run the SQL query in Question 2b after the credit limit has been increased, would Toys Galore be included inthw query results? Why or why not.

Here is the question that was menchined above "Quenstion 2b.

2b. Write and execute the command to retrieve the item number, description, order number, and quoted price for all the orders in ITEM_ORDER view for items with quoted proces that exceed $100.

ORDER LINE ORDERS ORDER NUM ORDER DATE CUSTOMER NUM ORDER NUM ITEM NUM NUM ORDERED QUOTED PRICE 51608 33 5 $86.99 51608 10/12/2015 126 51610 1012/2015 334 51610 KL 78 25 $10.95 516 13 10V 13/2015 3s6 51610 TR40 10 S1399 516 14 1013/2015 260 516 13 DL51 5 4.95 51617 10/15/2015 586 51614 FD11 1 24.05 51619 10/15/2015 126 51617 89 4 $115.99 51623 10/15/2015 556 51617 35 3 S116.5 51625 10/10/2015 796 51619 FD 11 2 S121.05 31623 DR67 S20.05 $36.95 51623 FH24 51623 KD34 10 3.10 51625 MTO3 s S45.79 ITEM ITEM NUM DESCRIPTION ON HAND CATEGORY STOREHOUSE PRICE A 74 Patience 9 GME I3 $22.99 BR 23 skittles 21 GME 2 S29.99 36 TOY sso 49 CD33 Wood Block Set (48 piece) 3 12 TOY Classic Railway Set $107.95 DL51 DR67 Giant Star Brain Teaser 24 PZL 2 S31.95 Mancala 40 GME 3 S50.00 DW23 S TOY 3 S124.95 FD11 Rocking Horse Puzzle Gift Set 65 PZL 1 $38.95 FH24 KA12 Cribbage Set 56 GME 3 $75.00 60 PZL 2 $14.95 KD34 Pentomin oes Brain Teaser 110 GME $1095 KL78 Pick Up Sticks s4579 Zauberkasten Brain Teaser MTO3 45 PZL NLS9 Wood Block Set (62 piece) 32 TOY 3 $119.75 Tic Tac Toe 75 GME 2 si 3.99 TR40 $118.95 Fire Engine TW35 30 TOY

Explanation / Answer

Here is the solution as per the given criteria, please go through it throughly:-

/*================================
CREATE DATABASE toysGaloredb
These Commands used to create
==================================== */
CREATE TABLE ORDERS
(
ORDER_NUM   NUMBER(10) NOT NULL,
ORDER_DATE DATE NOT NULL,
CUSTOMER_NUM NUMBER(10) NOT NULL,
CONSTRAINT PK_ORDERS PRIMARY KEY (ORDER_NUM)
);
/*---------------------------------*/
/*---------------------------------*/
CREATE TABLE ORDER_LINE
(
ORDER_NUM   NUMBER(10) NOT NULL,
ITEM_NUM VARCHAR2(10) NOT NULL,
NUM_ORDERED   NUMBER(5) NOT NULL,
QUOTED_PRICE NUMBER(*,2) NOT NULL,
CONSTRAINT PK_ORDER_LINE PRIMARY KEY (ITEM_NUM),
CONSTRAINT FK_ORDER_NUM FOREIGN KEY (ORDER_NUM) REFERENCES ORDERS (ORDER_NUM)
);
/*---------------------------------*/
/*---------------------------------*/
CREATE TABLE ITEM
(
ITEM_NUM VARCHAR2(10) NOT NULL,
DESCRIPTION VARCHAR2(50) NOT NULL,
ON_HAND NUMBER(5) NOT NULL,
CATEGORY VARCHAR2(10) NOT NULL,
STORE_HOUSE NUMBER(5) NOT NULL,
PRICE NUMBER(*,2) NOT NULL,
CONSTRAINT FK_ITEM_NUM FOREIGN KEY (ITEM_NUM) REFERENCES ORDER_LINE (ITEM_NUM)
);
/*---------------------------------*/
/*---------------------------------*/

CREATE TABLE REP
(
REP_NUM   NUMBER(5) NOT NULL,
LAST_NAME VARCHAR2(50) NOT NULL,
FIRST_NAME VARCHAR2(50) NOT NULL,
STREET VARCHAR2(50) NOT NULL,
CITY VARCHAR2(25) NOT NULL,
STATE VARCHAR2(50) NOT NULL,
POSTAL_CODE NUMBER(10) NOT NULL,
COMMISION NUMBER(10,2) NOT NULL,
RATE NUMBER(5,2) NOT NULL,
CONSTRAINT PK_REP PRIMARY KEY (REP_NUM)
);
/*---------------------------------*/
/*---------------------------------*/
CREATE TABLE CUSTOMER
(
CUSTOMER_NUM   NUMBER(5) NOT NULL,
CUSTOMER_NAME VARCHAR2(50) NOT NULL,
STREET VARCHAR2(50) NOT NULL,
CITY VARCHAR2(25) NOT NULL,
STATE VARCHAR2(50) NOT NULL,
POSTAL_CODE NUMBER(10) NOT NULL,
BALANCE NUMBER(*,2) NOT NULL,
CREDIT_LIMIT NUMBER(*,2) NOT NULL,
REP_NUM   NUMBER(5) NOT NULL,
CONSTRAINT PK_CUSTOMER PRIMARY KEY (CUSTOMER_NUM),
CONSTRAINT FK_REP_NUM FOREIGN KEY (REP_NUM) REFERENCES REP (REP_NUM)
);

/* ==========================================
These command used to insert SOME VALUES the value into created tables
========================================= */

INSERT INTO ORDERS VALUES(51608, to_date('10-12-2015','dd-mm-yyyy'),126)
INSERT INTO ORDERS VALUES(51610,to_date('10-12-2015','dd-mm-yyyy'),334)
INSERT INTO ORDERS VALUES(51613, to_date('10-13-2015','dd-mm-yyyy'),386)
INSERT INTO ORDERS VALUES(51614, to_date('10-13-2015','dd-mm-yyyy'),260)
INSERT INTO ORDERS VALUES(51617, to_date('10-15-2015','dd-mm-yyyy'),586)

==========================================================

/* Now here is the command to create */

CREATE VIEW ITEM_ORDER AS
SELECT ITEM.ITEM_NUM, ITEM.DESCRIPTION,ROUND(ORDER_LINE.QUOTED_PRICE+100,2) "QUOTED-PRICE",ORDER_LINE.QUOTED_PRICE
FROM ITEM
INNER JOIN ORDER_LINE
ON ITEM.ITEM_NUM = ORDER_LINE.ITEM_NUM;

/* To display the view:- */

SELECT * FROM ITEM_ORDER;

Please run these command into your window and checkout the output.

Related Questions

Navigate

• Browse (All)
• Browse W
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.