Database Systems, 8th Ed., Rob/Coronel */ /* Type of SQL : SQL Server */ CREATE TABLE CUSTOMER ( CUST_NUM int, CUST_LNAME varchar(20), CUST_FNAME varchar(20), CUST_BALANCE float(8) ); INSERT INTO CUSTOMER VALUES('1000','Smith','Jeanne','1050.11'); INSERT INTO CUSTOMER VALUES('1001','Ortega','Juan','840.92'); /* -- */ CREATE TABLE CUSTOMER_2 ( CUST_NUM int, CUST_LNAME varchar(20), CUST_FNAME varchar(20) ); INSERT INTO CUSTOMER_2 VALUES('2000','McPherson','Anne'); INSERT INTO CUSTOMER_2 VALUES('2001','Ortega','Juan'); INSERT INTO CUSTOMER_2 VALUES('2002','Kowalski','Jan'); INSERT INTO CUSTOMER_2 VALUES('2003','Chen','George'); /* -- */ CREATE TABLE INVOICE ( INV_NUM int, CUST_NUM int, INV_DATE datetime, INV_AMOUNT float(8) ); INSERT INTO INVOICE VALUES('8000','1000','3/23/2010','235.89'); INSERT INTO INVOICE VALUES('8001','1001','3/23/2010','312.82'); INSERT INTO INVOICE VALUES('8002','1001','3/30/2010','528.10'); INSERT INTO INVOICE VALUES('8003','1000','4/12/2010','194.78'); INSERT INTO INVOICE VALUES('8004','1000','4/23/2010','619.44'); 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 SumCustInvoices. 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 your alter view query changed the form

/* Database Systems, 8th Ed., Rob/Coronel */
/* Type of SQL : SQL Server */

CREATE TABLE CUSTOMER (
CUST_NUM int,
CUST_LNAME varchar(20),
CUST_FNAME varchar(20),
CUST_BALANCE float(8)
);
INSERT INTO CUSTOMER VALUES('1000','Smith','Jeanne','1050.11');
INSERT INTO CUSTOMER VALUES('1001','Ortega','Juan','840.92');

/* -- */

CREATE TABLE CUSTOMER_2 (
CUST_NUM int,
CUST_LNAME varchar(20),
CUST_FNAME varchar(20)
);
INSERT INTO CUSTOMER_2 VALUES('2000','McPherson','Anne');
INSERT INTO CUSTOMER_2 VALUES('2001','Ortega','Juan');
INSERT INTO CUSTOMER_2 VALUES('2002','Kowalski','Jan');
INSERT INTO CUSTOMER_2 VALUES('2003','Chen','George');

/* -- */

CREATE TABLE INVOICE (
INV_NUM int,
CUST_NUM int,
INV_DATE datetime,
INV_AMOUNT float(8)
);
INSERT INTO INVOICE VALUES('8000','1000','3/23/2010','235.89');
INSERT INTO INVOICE VALUES('8001','1001','3/23/2010','312.82');
INSERT INTO INVOICE VALUES('8002','1001','3/30/2010','528.10');
INSERT INTO INVOICE VALUES('8003','1000','4/12/2010','194.78');
INSERT INTO INVOICE VALUES('8004','1000','4/23/2010','619.44');

• 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 SumCustInvoices.
• 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 your alter view query changed the form