help

Question

help

deepeyes1980s

SSC Rookie

Points: 35
More actions
February 26, 2009 at 4:31 pm

#207052

i need help
how can i do this
want to pull the table from access 2007
Display all the orders that received a discount of 10% or larger on at least one product in the order.
Display all of the employees that live in the United States.

Viewing 15 posts - 1 through 15 (of 15 total)

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

David Webb-CDS SSCoach Points: 17398 More actions · Answer 1

Are you wanting to run these queries in Access, or do you want to run them inside SQL Server and get the data from the Access database?

Could you please post the definitions of the tables that contain the data, some sample data from each table, and the expected results based on the sample data?

Also, if you have tried to make this work, please post any code you've come up with.

And then again, I might be wrong ...
David Webb

Bevan Keighley SSCommitted Points: 1825 More actions · Answer 2

Hi ,

You will need to post some more information before we can help you. Please provide your table design and some sample data. It is best to provide this as CREATE TABLE statements.

Thanks,

Bevan

deepeyes1980s SSC Rookie Points: 35 More actions · Answer 3

i want toknow how

using the Northwind Access2007 Database Tables to create SQL queries

1.Display number of employees born in or after 1966

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

3.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 london

David Webb-CDS SSCoach Points: 17398 More actions · Answer 4

Since I don't have that database installed on any of the servers I'm working with, without the table definitions, I'm not going to be much help. There may be other folks here who can assist, or you can post the table definitions and I can help.

Also, since this is homework, or an interview question, you'll get a better response if you show us what you've done on your own so far. Most of the folks here are willing to assist in your education, but few are willing to do all the work for you.

And then again, I might be wrong ...
David Webb

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

Are the tables in Access or are they in SQL Server?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

deepeyes1980s SSC Rookie Points: 35 More actions · Answer 6

the qoustion how can pull tables by using

Northwind Access 2007 Database Table to create your SQL queries for each question. .

i have tried many time to create the SQL ,but i get stuck many time

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 7

Lynn Pettis

SSC Guru

Points: 442467

February 26, 2009 at 5:35 pm

#950913

In Access is my guess.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 8

deepeyes1980s (2/26/2009)
the qoustion how can pull tables by using
Northwind Access 2007 Database Table to create your SQL queries for each question. .
i have tried many time to create the SQL ,but i get stuck many time

Please show us what you have tried so far. Also, it would help if you could provide more information about the tables you are accessing.

David Webb-CDS SSCoach Points: 17398 More actions · Answer 9

OK, here's a hint for 15.

The employees table has a city column which can be used to find specific cities.

Give the SQL a try and we'll go from there.

And then again, I might be wrong ...
David Webb

deepeyes1980s SSC Rookie Points: 35 More actions · Answer 10

hi

these are the qoustions ,and tell me how can upload my work because it saved in access 2007 and this program donot from permitted type

For each of the following, determine what query would accomplish the task:

1.Display all of the products in alphabetical order.

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.

Bevan Keighley SSCommitted Points: 1825 More actions · Answer 11

Thanks.... Now can you post your attempted SQL and an explanation of what is not working (or any error codes that were returned)

Bevan

Bevan Keighley SSCommitted Points: 1825 More actions · Answer 12

By the way, access has a built in query builder. In the CREATE menu in access 07 click on QUERY WIZARD.

Bevan

deepeyes1980s SSC Rookie Points: 35 More actions · Answer 13

these are the qoustion with my answer and some

Question i get stuck with them

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

this is my answer

SELECT SupplierName, ProductName, UnitPrice

FROM Products;

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

this is my answer:

SELECT Order

FROM Orders

WHERE OrderDate >=#1-NOV-1994#AND OrderDate<=#31-Jan-1995#;

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

this is my answer:

SELECT AVG(UnitPrice)

FROM Products

ORDER BY SeaFood;

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

this is my answer:

SELECT OrderNumber, count(*)

FROM Orders

GROUP BY OrderNumber;

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

this is my answer:

SELECT supplier

FROM Suppliers

WHERE CompanyName like "k*";

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

this is my answer

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.

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

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

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

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

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

Jeff Moden SSC Guru Points: 1004372 More actions · Answer 14

Looks like a final exam to me... who gets the "A" if we answer them?

It's time to hucker down and do you own work... heh... c'mon, it'll be fun! 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)