*****provide any data model***** Use the Premiere script to create your SQL stat

Question

*****provide any data model*****

Use the Premiere script to create your SQL statements, then attach both SQL statements and query results. List the largest customer balance, use alias, “largest balance.”

List the average part price, rounded to two decimal places, use alias, "average price."

List all part numbers, descriptions, and part prices (rounded to two decimal places) for parts priced between $30 and $300, inclusive.

Remove order numbers between 12491 and 12498, inclusive, in one statement?

Update all customers’ credit limits to $2000, for those customers whose balance is less than or equal to $800

Add three records to the order_line table, in one statement, with the following values: (12489, 'AZ52',10,24.99) (12491, 'AZ52',5,19.99) (12494, 'BA74',3,14.99)

Explanation / Answer

TABLE: CUSTOMER

List the largest customer balance, use alias, “largest balance.”

SELECT MAX(BALANCE_AMT) AS 'LARGEST BALANCE' FROM CUSTOMER;

TABLE: PARTS

List the average part price, rounded to two decimal places, use alias, "average price."

SELECT AVG(PART_PRICE) AS 'AVERAGE PRICE' FROM PARTS;

List all part numbers, descriptions, and part prices (rounded to two decimal places) for parts priced between $30 and $300, inclusive.

SELECT PART_NO, DESCRIPTION, PART_PRICE FROM PARTS WHERE PART_PRICE >= 30 AND PART_PRICE <= 300;

TABLE: ORDER

Remove order numbers between 12491 and 12498, inclusive, in one statement?

DELETE FROM ORDER WHERE ORDER_ID >= 12491 AND ORDER_ID <= 12498;

Update all customers’ credit limits to $2000, for those customers whose balance is less than or equal to $800

UPDATE CUSTOMER SET CREDIT_LIMIT = 2000 WHERE BALANCE_AMT <= 800;

TABLE: ORDER_LINE

Add three records to the order_line table, in one statement, with the following values: (12489, 'AZ52',10,24.99) (12491, 'AZ52',5,19.99) (12494, 'BA74',3,14.99)

  INTO ORDER_LINE (ORDER_ID, DESCRIPTION, QUANTITY, PRICE) VALUES (12494,'BA74',3,14.99)

Please let me know in case of any clarifications required. Thanks!

COLUMN NAME DATA TYPE NAME VARCHAR2(100) CREDIT_LIMIT NUMBER(10,2) BALANCE_AMT NUMBER(10,2)

Related Questions

Navigate

• Browse (All)
• Browse #
• Subjects

---

---

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