CELKO (10/1/2012)

---

Most of the work in RDBMS (80 to 95%) is done in the DDL; you have never learned how to design data, so your code looks like a loose leaf note book, full of narratives. Sorry you had only one purchase, that you think the customer’s name is an attribute of the purchase and not the customer. We have DATE data types. We have the ANSI/ISO syntax for insertion now, too.

Tables have keys and should be normalized; you did neither. In short, this is not a valid schema. Here is a guess at doing it right:

CREATE TABLE Customer_Purchase_Comments

(purchase_order_nbr INTEGER NOT NULL,

comment_date DATE NOT NULL,

PRIMARY KEY (purchase_order_nbr INTEGER NOT NULL,

comment_date),

purchase_comment VARCHAR(500) NOT NULL);

CREATE TABLE Purchase_Orders

(purchase_order_nbr INTEGER NOT NULL PRIMARY KEY,

po_date DATE NOT NULL,

customer_id CHAR(10) NOT NULL

REFERENCES Customers(customer_id),

...));

CREATE TABLE Customers

(customer_id CHAR(10) NOT NULL PRIMARY KEY,

customer_name VARCHAR(35) NOT NULL,

..

customer_type CHAR(3) NOT NULL,

CHECK (customer_type IN ('LOY', ..)),

);

>> --Now, from the given records [sic: rows are not records], I want to get only the list of customers who are 'Loyalty Customers' and I want to see all of their purchase details entered for that day. <<

SELECT POC.purchase_order_nbr, POC.purchase_comment,

C.customer_id, C.customer_name

FROM Customer_Purchase_Comments AS POC,

Customers AS C,

Purchase_Orders AS PO

WHERE C.customer_type = 'LOY'

AND POC.comment_date = '2012-06-01'

AND PO.customer_id = C.customer_id;

untested