Homework Help is Here – Start Your Trial Now!
Engineering
Computer Science
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

Fundamentals of Information Systems
Fundamentals of Information Systems
8th Edition
ISBN:9781305082168
Author:Ralph Stair, George Reynolds
Publisher:Ralph Stair, George Reynolds
Chapter3: Database Systems And Applications
Section: Chapter Questions
Problem 9SAT: _______________ is a special purpose programming language for accessing and manipulating data stored...
See similar textbooks
icon
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

Trending now

This is a popular solution!

steps

Step by step

Solved in 5 steps

SEE SOLUTIONCheck out a sample Q&A here
Blurred answer
Knowledge Booster
Types of trees
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.
Similar questions
  • SEE MORE QUESTIONS
Recommended textbooks for you
Fundamentals of Information Systems
Fundamentals of Information Systems
Computer Science
ISBN:
9781305082168
Author:
Ralph Stair, George Reynolds
Publisher:
Cengage Learning
SEE MORE TEXTBOOKS