Answered step by step
Verified Expert Solution
Link Copied!

Question

1 Approved Answer

SQL query... query. Ok heres my task: Return the salesman first name, last name and total of the product price for the listings made by

SQL query... query.

Ok heres my task:

Return the salesman first name, last name and total of the product price for the listings made by each salesman. You need to return only those records, whose total of the product price is more than 10000. Make sure you display your result set in the order of the total of the product price.

I have three tables in this database, salesman, products and departments. salesman structure is: (salesman_ID (INDEX), firstname, lastname, phone, dept_ID)

products structure is: ( product_ID (INDEX), type, size, note, price, salesman_ID).

Departments structure is: (dept_ID (INDEX), dept_name, address, phone_number)

I was having trouble getting the join to work and someone helped me by suggesting this query:

SELECT A.salesman_Firstname, A.salesman_Lastname, B.Products_Price FROM (SELECT salesman_ID, salesman_Firstname, salesman_Lastname FROM salesman) as A, (SELECT salesman_ID, Products_Price FROM Products) WHERE A.salesman_ID = B.salesman_ID and SUM(b.Products_Price) > 10000

but it didnt work ( i may have not given them enought information) and i kept getting the "every derived table must have its own alias" error.

So i modified the query as follows:

SELECT A.Firstname, A.Lastname, B.Price FROM (SELECT salesman_ID, Firstname, Lastname FROM salesman) as A, (SELECT product_ID, Price FROM products) AS B WHERE A.salesman_ID = B.product_ID HAVING SUM(B.Asking) > 10000

Now i get the following result from this query:

Firstname Lastname Price

Jeff Jansen 7560

So now the problem is that i am only getting one row returned ( should be alot more) and the SUM function doest seem to have any effect on the query. I was thinking i need a GROUP BY clause to accomapany the SUM function in order to fix the single row output? but not sure why the sum isnt working in adding up the price totals for each salesman..

Please help, ive spent the last couple of days watching tutorial after tutorial but i cant seem to get this.

Step by Step Solution

There are 3 Steps involved in it

Step: 1

blur-text-image

Get Instant Access to Expert-Tailored Solutions

See step-by-step solutions with expert insights and AI powered tools for academic success

Step: 2

blur-text-image

Step: 3

blur-text-image

Ace Your Homework with AI

Get the answers you need in no time with our AI-driven, step-by-step assistance

Get Started

Recommended Textbook for

Professional SQL Server 2000 Database Design

Authors: Louis Davidson

1st Edition

1861004761, 978-1861004765

More Books

Students also viewed these Databases questions

Question

6. Identify seven types of hidden histories.

Answered: 1 week ago

Question

What is the relationship between humans and nature?

Answered: 1 week ago