SQL query... query. Ok heres my task: Return the salesman first name, last name
ID: 3734455 • Letter: S
Question
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.
Explanation / Answer
CREATE TABLE Departments
(
dept_ID int PRIMARY KEY,
dept_name varchar(50),
address varchar(50),
phone_number int
);
CREATE TABLE salesman
(
salesman_ID int PRIMARY KEY,
firstname varchar(50),
lastname varchar(50),
phone int,
dept_ID int,
CONSTRAINT FK_dep FOREIGN KEY (dept_ID)
REFERENCES Departments(dept_ID)
);
CREATE TABLE products (
product_ID int NOT NULL PRIMARY KEY,
type varchar(20),
size int,
note varchar(50),
price int,
salesman_ID int,
CONSTRAINT FK_sale FOREIGN KEY ( salesman_ID) REFERENCES salesman(salesman_ID)
);
INSERT INTO Departments VALUES (1,'ABC','eeeee',123435);
INSERT INTO Departments VALUES (2,'bcd','rrrr',4567);
INSERT INTO Departments VALUES (3,'efg','yyyy',6789);
INSERT INTO Departments VALUES (4,'yut','uuuuu',4567);
INSERT INTO Departments VALUES (5,'xyz','ttttt',3445);
INSERT INTO salesman VALUES (1,'abc','xyz',123435,1);
INSERT INTO salesman VALUES (2,'cde','xyz',123435,1);
INSERT INTO salesman VALUES (3,'fgr','xyz',123435,2);
INSERT INTO salesman VALUES (4,'tyu','xyz',123435,2);
INSERT INTO salesman VALUES (5,'ytr','xyz',123435,5);
INSERT INTO products VALUES (1,'a',300,'xyz',600,1);
INSERT INTO products VALUES (2,'c',400,'xyz',5000,2);
INSERT INTO products VALUES (3,'f',500,'xyz',6000,2);
INSERT INTO products VALUES (4,'t',600,'xyz',50000,5);
INSERT INTO products VALUES (6,'e',700,'xyz',1000,1);
INSERT INTO products VALUES (7,'tt',700,'xyz',2000,3);
INSERT INTO products VALUES (8,'uu',700,'xyz',9000,3);
select a.firstname as FIRSTNAME,a.lastname as LASTNAME ,b.price as PRICE from salesman as a,(SELECT SUM(price) as price,salesman_ID FROM products GROUP BY salesman_ID HAVING SUM(price) > 10000 ORDER BY SUM(price) DESC ) as b where a.salesman_ID = b.salesman_ID
result:
ytr|xyz|50000
cde|xyz|14000
fgr|xyz|11000
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.