CODEWITHSUNDEEP
sql
Paddy_Power
Paddy_Power

Reputation: 33

Finding customer with smallest order

I have used W3school and my in class notes. Was wondering if someone could point me in the right direction.

I am working on an SQL peoject.

I have a database that made from three tables employees, customers, and customer orders. 

CUSTOMER (
    CUST_NBR NUMBER(10) NOT NULL ,
    FNAME NVARCHAR2(20) NULL,
    LNAME NVARCHAR2(20) NULL,
    PRIMARY KEY(CUST_NBR)
);



EMPLOYEE (
    EMP_ID NUMBER(10) NOT NULL ,
    FNAME NVARCHAR2(20) NULL,
    LNAME NVARCHAR2(20) NULL,
    MANAGER_EMP_ID NUMBER(10) NULL,
    PRIMARY KEY(EMP_ID),
    FOREIGN KEY(MANAGER_EMP_ID)
    REFERENCES EMPLOYEE(EMP_ID)
);

CUST_ORDER (
    ORDER_NBR NUMBER(10) NOT NULL ,
    CUST_NBR NUMBER(10) NOT NULL,
    SALES_EMP_ID NUMBER(10) NOT NULL,
    SALE_PRICE NUMBER(10, 2) NULL,
    PRIMARY KEY(ORDER_NBR),
    FOREIGN KEY(SALES_EMP_ID)
    REFERENCES EMPLOYEE(EMP_ID),
    FOREIGN KEY(CUST_NBR)
    REFERENCES CUSTOMER(CUST_NBR)
);

I have filled in sample data for all tables. I am looking to write a query that will allow me to return information fname, lname, order_nbr for the item with the lowest sale price.

I have tried several variations of 

Select *
FROM CUST_ORDERS
WHERE SALE_PRICE = min(Sale_price):

Any help would be greatly appreciated.

Upvotes: 0

Views: 819

Answers (2)

Quassnoi
Quassnoi

Reputation: 425673

SELECT  TOP 1
        fname, lname, order_nbr
FROM    cust_order co
JOIN    customer с
ON      c.cust_nbr = co.cust_nbr
ORDER BY
        co.sale_price

Upvotes: 1

Robert Harvey
Robert Harvey

Reputation: 180868

SELECT TOP 1 * FROM CUST_ORDERS ORDER BY SALE_PRICE;

Upvotes: 1

Related Questions