Question

CREATE TABLE Customers
(Customer# NUMBER(4),
LastName VARCHAR2(10) NOT NULL,
FirstName VARCHAR2(10) NOT NULL,
Address VARCHAR2(20),
City VARCHAR2(12),
State VARCHAR2(2),
Zip VARCHAR2(5),
Referred NUMBER(4),
Region CHAR(2),
Email VARCHAR2(30),
CONSTRAINT customers_customer#_pk PRIMARY KEY(customer#),
CONSTRAINT customers_region_ck
CHECK (region IN ('N', 'NW', 'NE', 'S', 'SE', 'SW', 'W', 'E')) );

CREATE TABLE Orders
(Order# NUMBER(4),
Customer# NUMBER(4),
OrderDate DATE NOT NULL,
ShipDate DATE,
ShipStreet VARCHAR2(18),
ShipCity VARCHAR2(15),
ShipState VARCHAR2(2),
ShipZip VARCHAR2(5),
ShipCost NUMBER(4,2),
CONSTRAINT orders_order#_pk PRIMARY KEY(order#),
CONSTRAINT orders_customer#_fk FOREIGN KEY (customer#)
REFERENCES customers(customer#));

In a bid to increase its customer retention, JustLee Books would like to know the names of its customers who have spent less than $50.00

Write an SQL query to display the requested list based on the scenario above.

Answer 1

SELECT FirstName,LastName

FROM Customers

WHERE Customer IN {

    SELECT Customer

    FROM Orders

    WHERE ShipCost<50

};

EXPLANATION:

I am using the select keyword to select the first name and the last name of the customer from the database, as we are asked to get the names. As we have a condition that we have to get the customers with expense < 50 $, using the nested queries in  Orders I can find the customers with expense <50 and after that, we can use the customer list from this query to find the name form the Customers list to get the name of Customer with that with the Customer value got.