This count should be one less than the total number of rows in your employees table, it should exclude the lowest employeeID with an order. You'll want to left outer join your employee table with the set of IDs you don't want (the ones with orders in the past 3 months) on the employeeID field, and return rows where the employeeID from the order subquery is null. You can do that in a couple of different ways, depending on what you've learned so far, but typically you're exposed to LEFT OUTER JOINs for this sort of thing. Those are the IDs you want to exclude from the employees table.
Northwind database sql queries how to#
Now you can see all the employee IDs you don't want, the ones that have orders within the last 3 months, and you already know how to limit orders by customers in USA. I think you'll have better luck, though, adding 3 months to orderdate and comparing with getdate():
Then you can experiment with the datediff() function on the orderDates in the orders table: The getdate() function returns the date and time from the server. You need to use the order table's orderdate field with today's date and compare the number of months between them. Now you've asked where/how to apply the criteria for 'sales in the last 3 months.' Note each order has an orderDate, representing the date the order was placed. Since you've posted an example of an inner join, I'm going to assume you could write that one yourself, too: Next, consider how to find the orders for those customers (all of them, for now).
First, let's look at 'customers from USA.' You can probably write this one yourself: You'll need to figure out what's going on for yourself. If you're just learning SQL, please don't take one of these answers and use it to do your homework. Sorry for my question but i'm new in SQL and i'll appreciate any kind of help. Should i use other kind of joins or something else? The second problem is that i don't have an idea for the part 'employees with no sale' How can i find this? Below is a selection from the 'Customers' table in the Northwind sample database: CustomerID CustomerName ContactName Address. SQL Database SQL Create DB SQL Drop DB SQL Backup DB SQL Create Table SQL Drop Table SQL Alter Table SQL Constraints SQL Not Null SQL Unique SQL Primary Key SQL Foreign Key SQL Check SQL. One of the problem is that i don't know where to put this select query (it's for calculating the last 3 months but i'm not sure that this is true):
This is the task:Ĭreate a query that displays the employees with no sale in the last 3 months to customers who are from 'USA'.
0 kommentar(er)
