Northwind Databse Qurey Questions

  • Hi everyone, Have been ask this question from the query test of NorthWind database which really confused me

    Q:Return the empolyeeID,FirstName + LastName as Fullname for all empoylees that have not made sale since july 1998(Order.OrderDate) (Right join + Sub queries)

    *****imagine in a situation that you need to write answer down on paper without querying the database first, how would you solve it?

    My solution :

    SELECT E.EmployeeID,E.FirstName+ ' '+ E.LastName AS FullName

    FROM Orders AS O

    Right join Employees AS E

    ON E.EmployeeID = O.EmployeeID

    WHERE NOT EXISTS

    (SELECT O.EmployeeID

    FROM Orders O

    WHERE O.OrderDate BETWEEN '1998-01-01' AND GETDATE()) 0 row?

    The result looks susbicoius cause i had also ran these two queries

    SELECT DISTINCT COUNT(*)

    FROM Orders AS O

    Right join Employees AS E

    ON E.EmployeeID = O.EmployeeID Total =830 Row

    SELECT DISTINCT O.EmployeeID

    FROM Orders O

    WHERE O.OrderDate BETWEEN '1998-01-01' AND GETDATE() Total = 9 Row

    shoudn't the result be 830-9 =821 Rows?

    Hope someone can help out, thanks in advance.

  • see if this helps.......as I read your homework, the question relates to employees not number of orders

    SELECT

    O.EmployeeID

    , E.FirstName + ' ' + E.LastName AS Fullanme

    FROM Orders AS O

    INNER JOIN Employees AS E ON O.EmployeeID = E.EmployeeID

    GROUP BY

    O.EmployeeID

    , E.FirstName

    , E.LastName

    HAVING (MAX(O.OrderDate) < '1998-08-01');

    ________________________________________________________________
    you can lead a user to data....but you cannot make them think
    and remember....every day is a school day

  • Hi SS,

    Thanks for your answer, but the question ask specifically to use right join and sub query to solve the problem:-)

  • Eric_Shao (6/21/2015)


    Hi SS,

    Thanks for your answer, but the question ask specifically to use right join and sub query to solve the problem:-)

    well....I think the answer is 9 employees and you seem to suggest the answer is 821...care to explain...cos I dont follow you

    ________________________________________________________________
    you can lead a user to data....but you cannot make them think
    and remember....every day is a school day

  • Hi SS, Sorry for the confusion, what I really struggle was why my solution didn't work, I though at least return something instead of 0. so it would be much appreciated if you can correct my answer.:-)

    Cheers

  • does this make any sense to you........

    SELECT distinct O.EmployeeID

    FROM Orders O

    WHERE O.OrderDate BETWEEN '1998-01-01' AND GETDATE()

    SELECT distinct O.EmployeeID

    FROM Orders O

    ________________________________________________________________
    you can lead a user to data....but you cannot make them think
    and remember....every day is a school day

  • Yep, this make sense, The first one is 9, The second is 830, But what I really want to figure out is how to go about and display this 9 records with associate person's name using right join combined with sub query.

  • In your initial post you said the question was to return the EmployeeID, FirstName + LastName as Fullname for all employees that have not made a sale since July 1998 but there are no orders in the Orders table with OrderDate >= '1998-08-01' as you can see from the following:

    SELECT MIN(OrderDate) AS [MinOrderDate], MAX(OrderDate) AS [MaxOrderDate] FROM Orders

    The wording of the question in your original post would seem to indicate employees who have not made sales later than July 31, 1998 (August 1, 1998 and onward)? Please double-check whether this is what the question is asking, because as you can see there are no orders past May 6, 1998, assuming you are using the same version of the Northwind database that I am.

  • Hi Brain, Yes, i'd checked that we on the same page in terms of database, i suppose what the question really asking is any order placed before 06-05-1998. return those empolyee 's name .

  • If you are only interested in the employees' names then the query from J Livinston SQL is correct. You can amend it to include the MaxOrderDate if you wish. For example,

    SELECT

    O.EmployeeID

    , E.FirstName + ' ' + E.LastName AS Fullname

    , MAX(O.OrderDate) AS MaxOrderDate

    FROM Orders AS O

    INNER JOIN Employees AS E ON O.EmployeeID = E.EmployeeID

    GROUP BY

    O.EmployeeID

    , E.FirstName

    , E.LastName

    HAVING (MAX(O.OrderDate) < '1998-08-01');

    Are you also supposed to show all the orders associated with these employees?

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply