Using the above tables answer the following 1 Write a SQL co

Using the above tables answer the following:

1. Write a SQL code that will generate a combined list of CUSTOMERS from tables CUSTOMERS_1 and CUSTOMERS_2 that do not include the duplicate CUSTOMERS record(s).

2. Write a SQL code that will generate a combined list of CUSTOMERS from tables CUSTOMERS_1 and CUSTOMERS_2 and include the duplicate CUSTOMERS record(s).

3. Write a SQL code that will show CUST_NUM, CUST_LNAME, and CUST_FNAME for the one who has minimal INV_AMOUNT. 6. Write a SQL code that will list INV_DATE, the number of invoices issued during each invoice date, and the average invoice amount for each date.

need help. thanks

Table Name: CUSTOMERS 1 T_FNAME CUS CUST_BAL CUST NUM CUST LNAME CUS 2001 2002 James William $2,999 Crane Frasier $983 Table Name: CUSTOMERS_2 CUST NUMc 1999 2000 2002 2003 CUST LNAME CUST_FNAME CUST_BAL Anderson Bryant Crane Dent Anne Juan Frasier George $510 $21 $983 $1,790 Table Name: CUST_INVOICES 9001 9002 9003 2000 2001 2001 1000 NV DATEINV AMOUNT 23-Mar-16 23-Mar-16 30-Mar-16 10-Apr-16 245 260 275 286

Solution

Here is the solution for question 1, and 2:

CREATE TABLE Customers_1(Cust_Num int, Cust_Lname varchar(80),
Cust_Fname varchar(80),
Cust_Balance decimal(7, 2),
PRIMARY KEY (Cust_NUm));
CREATE TABLE Customers_2(Cust_Num int, Cust_Lname varchar(80),
Cust_Fname varchar(80),
Cust_Balance decimal(7, 2),
PRIMARY KEY (Cust_NUm));

CREATE TABLE Cust_Invoices(Inv_Num int, Cust_Num int, Inv_Date date,
Inv_Amount decimal(7, 2),
PRIMARY KEY (Inv_Num));

INSERT INTO Customers_1 VALUES(2001, \'James\', \'William\', 2999);
INSERT INTO Customers_1 VALUES(2002, \'Crane\', \'Frasier\', 983);
INSERT INTO Customers_2 VALUES(1999, \'Anderson\', \'Anne\', 510);
INSERT INTO Customers_2 VALUES(2000, \'Bryant\', \'Juan\', 21);
INSERT INTO Customers_2 VALUES(2002, \'Crane\', \'Frasier\', 983);
INSERT INTO Customers_2 VALUES(2003, \'Dent\', \'George\', 1790);
INSERT INTO Cust_Invoices VALUES(9000, 2000, \'2016-03-23\', 245);
INSERT INTO Cust_Invoices VALUES(9001, 2001, \'2016-03-23\', 260);
INSERT INTO Cust_Invoices VALUES(9002, 2001, \'2016-03-30\', 275);
INSERT INTO Cust_Invoices VALUES(9003, 1000, \'2016-04-10\', 286);

SELECT * FROM Customers_1 UNION SELECT * FROM Customers_2; will list all the elements with no duplicates.

SELECT * FROM Customers_1 UNION ALL SELECT * FROM Customers_2; will list all the elements with duplicates.