create queries

  • Thread starter Thread starter zahra
  • Start date Start date
Z

zahra

Pleas help me to creat this quires by using the Northwind Access 2003
Database ( use Access tables) to create SQL queries for each question.
i have tried for 4 hours ,but was so difficult to me
i realy need help with these questions

2 Display the supplier name, product name, and unit price for all products.

3.Display number of employees born in or after 1960.

4.Display all the orders taken between November 1, 1994 and January 31, 1995.

5.Display the average price for products in the 'Seafood' category.

6.Display the number of orders. (Hint: Use count.)

7.Display all suppliers that have a 'K' somewhere in their company name.

8.Display the customer name, employee name, and employee ID for all orders
that were handled by the Sales Representatives.

9.Display the order number and order total cost for each order placed.

10.Display the supplier name and the supplier contact name for all suppliers
that provide condiments.

11.Display the category IDs in numerical order. Secondly, sort on product
name in reverse order (descending).

12.Display all of the countries of all the customers with no duplications.
(Hint: Use DISTINCT)

13.Determine the average quantity of a dairy product item.

14.Display all the orders that received a discount of 10% or larger on at
least one product in the order.

15.Display all of the employees that live in the United States.

think you
 
Pleas help me to creat this quires by using the Northwind Access 2003
Database ( use Access tables) to create SQL queries for each question.
i have tried for 4 hours ,but was so difficult to me
i realy need help with these questions
Click to expand...

Zahra, it's extremely rude to just post your assigned homework questions with
no trace of an indication that you have tried anything yourself to answer
them.

Study your course material. Read the textbooks. Read the help file.

Getting answers spoonfed to you will not help you actually learn to use
Access.
 
Please help to answer the following questions using the Northwind Access2007
Database to create your SQL queries for each question. T have tried ,but i
think i have with some questions ,so please help me to fix the SQL to be
able to run the queries

1. Display the supplier name, product name, and unit price for all products.
SELECT SupplierID, ProductName, UnitPrice
FROM Products;

2. Display the average price for products in the 'Seafood' category.
SELECT AVG(UnitPrice)
FROM Products
ORDER BY SeaFood;

3.Display the number of orders. (Hint: Use count.)
SELECT OrderNumber, count(*)
FROM Orders
GROUP BY OrderNumber;

4.Display all suppliers that have a 'K' somewhere in their company name.
SELECT Suppliers.CompanyName
FROM Suppliers
WHERE (("WHERE COMPANY NAME" Like "*k"));

5.Display the customer name, employee name, and employee ID for all orders
that were handled by the Sales Representatives.
SELECT a.ContactName, b.FirstName, b.EmployeeID
FROM Customers AS a, Employees AS b
WHERE Title like'*Seles Representative*';

6.Display the order number and order total cost for each order placed.
I do not know

7. Display the supplier name and the supplier contact name for all suppliers
that provide condiments.
SELECT a.ContactName, b.Supplier
FROM Suppliers AS a, Products AS b
WHERE Category like "Condiment";

8.Determine the average quantity of a dairy product item.
I do not know

9.Display all the orders that received a discount of 10% or larger on at
least one product in the order.
Ido not know

10.Display all of the employees that live in the United States.

I do not know
 
1. The query is fine, except that it does not list the supplier NAME but
the supplier ID. Since the supplier NAME is in another table (an other
table than Products), you need to add that other table, isn't ? I assume
you can take it from there (you will need to add a join between the two
tables, if Access does not do it for you, automatically).

2. Seafood is not a field name, but a value for a field of products (for a
category, to be exact). So, try to impose a condition on the appropriate
field, as criteria. Remove the ORDER BY clause. Note that the category
matching seafood has its categoryID equals to 8, or, better, add the
Categories table, the join, and the right criteria.

3. Remove the GROUP BY since you want the count over the WHOLE table, not
PER GROUP.

4. Missing a *: LIKE "*k*". As you wrote it, the name should have a K
at the end of the word. With a * on either side, K can be anywhere within
the word.

5. You surely need the Orders table, isn't it?

6. Table Orders surely help. But since it does not contains the details,
surely Order Details is also required.

7. You have the two tables, but the required relation between those two
tables is missing (as a join). If you drag the two tables into a new query,
Access probably makes that join for you, already...

8. Dairy, seafood, ... come on... both are categories and you already solve
the same problem about seafood, so...

9. You can read the discount in table Order Details. You can get the records
in this table which have at least the given discount, right? Next, many
possible solution. One would be to add table Orders, the appropriate join,
and use DISTINCT to have no dup.

10, That one is really simple. United States is also known as USA, in case
you were not aware of that fact. From which table do you think you can get
the desired result (about employee) ? What are the fields in that table?



Vanderghast, Access MVP
 
think you
i have tried
but istill i get stuck stuck in these questions
5.Display the average price for products in the 'Seafood' category
8.Display the customer name, employee name, and employee ID for all orders
that were handled by the Sales Representatives.

9.Display the order number and order total cost for each order placed.

10.Display the supplier name and the supplier contact name for all suppliers
that provide condiments.
13.Determine the average quantity of a dairy product item.
 
Ok, assuming you tried...

5. Since you want only the records where category is 'seafood', that would
indicate a first step to perform as long as which tables are implied and
that at least one condition is to be applied to one of its relevant field.
As far as getting the AVERAGE, that is an aggregate, same family as SUM, so
you need a TOTAL query, there will be no group 'per se' since all records
would have already been of the 'required' category, (or make category the
group, if you want) and from there, you only need to average over the field
price, using Access query designer.

9. Orders does not have the prices. but Order Details have. So, the two
tables are required, isn't it? For the rest, it is a TOTAL query, having a
nicely defined GROUP and a nicely defined aggregate, SUM (of prices).


13. As I previously said, very similar to 5.


8. Not evident, Table Orders is intuitively implied, but has nothing about
"sales representative", so, another table is surely implied. That requires
some exploration, but that should be easy to see that table Employee is the
(only) one with a field speaking of "sales representative".. So, standard
business when two tables are implied: a join. You also need a criteria to
limit the result to "sales representative", and once this is done, the
complete answer should be evident.

10. A little bit like 8. Search the relevant tables, make the join, and
don't forget the criteria.




Vanderghast, Access MVP


zahra said:
think you
i have tried
but istill i get stuck stuck in these questions
5.Display the average price for products in the 'Seafood' category
8.Display the customer name, employee name, and employee ID for all orders
that were handled by the Sales Representatives.

9.Display the order number and order total cost for each order placed.

10.Display the supplier name and the supplier contact name for all
suppliers
that provide condiments.
13.Determine the average quantity of a dairy product item.
Click to expand...
 
hi
i do not know why does not work with me
so can you look over ,and tell how can i fix them
how can i send you my file
it is access 2007
 
Back
Top