Use T-SQL to create a view of the simpleco customer and invoice tables, the view

Question

Use T-SQL to create a view of the simpleco customer and invoice tables, the view should select for the customer number, customer last name, customer balance, invoice number, invoice date, and invoice amount. Name the view v_cust_invoices. When the view has been created, write a T-SQL query to execute the v_cust_invoices view to display all columns selected by the view. Using the v_cust_invoices view, write a T-SQL query to display the sum of the customer balances, rounded to two decimals with a column name of SumCustBal, and the sum of the invoice amounts, rounded to two decimals with a column name of SumCustlnvoices. Using the v_cust_invoices view, write a T-SQL alter view query to change the default date format for inv_date from YYYY-MM-DD 00:00:00.000 to MM-DD-YY format, for example: 03-23-2010 instead of 2010-03-23 00:00:00.000. Repeat the select all query on the v_cust_invoices view to verify that you alter view query changed the formatting of the inv_date.

Explanation / Answer

VIEW CREATION:

CREATE OR REPLACE VIEW V_CUST_INVOICES AS SELECT C.CUST_NUM, C.CUST_LNAME,C.CUST_BAL,I.INV_NUM,I/INV_DATE,I.INV_AMOUNT FROM CUSTOMER C,INVOICE I WHERE C.CUST_NUM=I.CUST_NUM;

EXECUTION OF VIEW;

SELECT * FROM V_CUST_INVOICES;

SUM;

SELECT SUM(CUST_BAL ) AS SUMCUSTBAL FROM V_CUST_INVOICES;

Related Questions

Navigate

• Browse (All)
• Browse U
• Subjects

---

---

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