locked
SQL Question: RRS feed

  • Question

  • User-1894208811 posted

    Hey, I am looking for help, I am trying to work out this question below, but I am struggling to do it without repeating the data shown. If anyone can help that would be very appreciated!

    QUESTION: Display a list of all sales, by customer, showing the customer ID, the customer’s name, the name of the product bought by the customer and the date of sale.

    The list should be sorted by Customer ID, then by date of sale with the most recent date first.

    The customer’s forename and surname should be merged to form one column in the display.

    Display all data with suitable column headings.

    Thanks!

    Wednesday, June 13, 2018 10:17 PM

All replies

  • User347430248 posted

    Hi OfficerShark,

    Try to refer an example below may help you.

    select 
    c_id as customer_ID,
    first_name + ' ' + surname as Customer_Name,
    product as Product_Name,
    date as Purchase_Date 
    from customer_data 
    WHERE 
    Date in (SELECT MAX(Date) from customer_data group by c_id)

    Output:

    Regards

    Deepak

    Thursday, June 14, 2018 1:31 AM
  • User77042963 posted

    QUESTION: Display a list of all sales, by customer, showing the customer ID, the customer’s name, the name of the product bought by the customer and the date of sale.

    If these data are in different tables, you need to join these tables with the common key in a SELECT statement.(I assume you have relevant tables in your database).

    The list should be sorted by Customer ID, then by date of sale with the most recent date first.

    This is to use ORDER BY for your SELECT statement at the end.

    Order by CustomerID, saleDate DESC

    The customer’s forename and surname should be merged to form one column in the display.

    You concat two columns together. You may use the new function concat(forename ,' ',surname) as fullname

    Display all data with suitable column headings.

    Use column alias for each column to show the heading you need. If you have space inside the heading, you need to use [] to wrap them.

    For example, you have Customer ID column, you can use an alias like this in the final SELECT statement:

    SELECT Customer ID as [Customer ID],concat(forename ,' ',surname) as fullname, salesDate as [Sale Date]

    from Customer Join Sale on Customer.CustomerID=Sale.CustomerID

    Order by CustomerID, saleDate DESC

    You can get start with this tutorial:

    https://www.w3schools.com/sql/default.asp

    Thursday, June 14, 2018 2:16 PM