Table Data: CREATE TABLE CUSTOMER ( CUS_CODE int, CUS_LNAME varchar(15), CUS_FNA

Question

Table Data:

CREATE TABLE CUSTOMER (
CUS_CODE int,
CUS_LNAME varchar(15),
CUS_FNAME varchar(15),
CUS_INITIAL varchar(1),
CUS_AREACODE varchar(3),
CUS_PHONE varchar(8),
CUS_BALANCE float(8)
);
INSERT INTO CUSTOMER VALUES('10010','Ramas','Alfred','A','615','844-2573','0');
INSERT INTO CUSTOMER VALUES('10011','Dunne','Leona','K','713','894-1238','0');
INSERT INTO CUSTOMER VALUES('10012','Smith','Kathy','W','615','894-2285','345.86');
INSERT INTO CUSTOMER VALUES('10013','Olowski','Paul','F','615','894-2180','536.75');
INSERT INTO CUSTOMER VALUES('10014','Orlando','Myron','','615','222-1672','0');
INSERT INTO CUSTOMER VALUES('10015','O''Brian','Amy','B','713','442-3381','0');
INSERT INTO CUSTOMER VALUES('10016','Brown','James','G','615','297-1228','221.19');
INSERT INTO CUSTOMER VALUES('10017','Williams','George','','615','290-2556','768.93');
INSERT INTO CUSTOMER VALUES('10018','Farriss','Anne','G','713','382-7185','216.55');
INSERT INTO CUSTOMER VALUES('10019','Smith','Olette','K','615','297-3809','0');

CREATE TABLE INVOICE (
INV_NUMBER int,
CUS_CODE int,
INV_DATE datetime
);
INSERT INTO INVOICE VALUES('1001','10014','2014-01-16');
INSERT INTO INVOICE VALUES('1002','10011','2014-01-16');
INSERT INTO INVOICE VALUES('1003','10012','2014-01-16');
INSERT INTO INVOICE VALUES('1004','10011','2014-01-17');
INSERT INTO INVOICE VALUES('1005','10018','2014-01-17');
INSERT INTO INVOICE VALUES('1006','10014','2014-01-17');
INSERT INTO INVOICE VALUES('1007','10015','2014-01-17');
INSERT INTO INVOICE VALUES('1008','10011','2014-01-17');

***The answer must be typed! Not written! Write a MySQL query to Join CUSTOMER and INVOICE table, and find the info about the customers who recently made some purchases. The result should be as shown in Table-8 TABLE: INVOICE INV NUMBER | CUS_CODE | INV_DATE 1001 10014 2014-01-16 00:00:00 I 1002 10011 2014-01-16 00:00:00 I 1003 10012 2014-01-16 00:00:00 I 1004 10011 2014-01-17 00:00:00 I 1005 100182014-01-17 00:00:00 I 1006 10014 2014-01-17 00:00:00 l 1007 10015 2014-01-17 00:00:00 | 1008 10011| 2014-01-17 00:00:00 I TABLE: CUSTOMER | CUS CODE | CUS LNAME | CUS FNAME | CUS INITIAL CUS AREACODE | CUS PHONE | CUS BALANCE I Alfred Leona 10010 | Ramas | 844-2573 10011 | Dunne 10012 | Smith Kathy W 10013 | olowski Paul 10014 | Orlando Myron I 10015 O'Brian Amy 10016 | Brown James G 10017 I Williams George I 615 713 615 615 615 713 615 615 713 615 | 894-1238 I | 894-2285 I 894-2180 I l 222-1672 I | 442-3381 1 | 297-1228 I l 290-2556 I 382-7185 I | 297-3809 I 345.86 I 536.75 I 221.19 I 768.93 216.55 10018 | Farriss 10019| Smith Anne Olette K Table-8: CUS CODE 1 CUS FNAME CUS LNAME total_purchases(S) Balance(S) 0 345.86 10011 Leona 10012 Kathy 10014 Myron 10015 Amy 10018 Anne Dunne Smith Orlando O'Brian Farriss 479.63 166.16 456.55 37.77 76.08 0 216.55

Explanation / Answer

Query:

select * from CUSTOMER inner join INVOICE on INVOICE.CUS_CODE = CUSTOMER.CUS_CODE group by CUSTOMER.CUS_CODE order by INV_DATE desc limit 1;

Output:

This Query returns the customer that last purchased, you can change the limit 1 to N, to get last N unique customer purchased.

if you want to get the customer who purchased after the certain date then the query is :

select * from CUSTOMER inner join INVOICE on INVOICE.CUS_CODE = CUSTOMER.CUS_CODE where INV_DATE > '2014-01-16' group by CUSTOMER.CUS_CODE order by INV_DATE;

CUS_CODE CUS_LNAME CUS_FNAME CUS_INITIAL CUS_AREACODE CUS_PHONE CUS_BALANCE INV_NUMBER CUS_CODE INV_DATE 10015 O'Brian Amy B 713 442-3381 0 1007 10015 2014-01-17T00:00:00Z

Related Questions

Navigate

• Browse (All)
• Browse T
• Subjects

---

---

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