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 create the table structures for the three tables.

2. Having created the table structures for the three tables, write SQL codes to enter the table records shown in the above figures into their respective tables.

3. 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).

4. 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).

5. 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.

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.