Using the above tables answer the following: 1. Write a SQL code that will gener

Question

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

Explanation / Answer

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.

Related Questions

Navigate

• Browse (All)
• Browse U
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.