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

Database System Concepts

7th Edition

ISBN:9780078022159

Author:Abraham Silberschatz Professor, Henry F. Korth, S. Sudarshan

Publisher:Abraham Silberschatz Professor, Henry F. Korth, S. Sudarshan

Chapter1: Introduction

Section: Chapter Questions

Problem 1PE

See similar textbooks

Related questions

Question

/* 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

Expert Solution

Trending now

This is a popular solution!

Step by step

Solved in 5 steps

SEE SOLUTION Check out a sample Q&A here

Knowledge Booster

Learn more about

Need a deep-dive on the concept behind this application? Look no further. Learn more about this topic, computer-science and related others by exploring similar questions and additional content below.