June 5, 2013 at 2:14 am
Hi Guys
I am tasked with retrieving certain info from an SQL created database.
There is one particular query I am struggling with.
First here is how the tables were created
Create table customers
(cust_id char(6) not null,
name char(30),
phone char(15),
constraint pk_customers1 Primary Key (cust_id));
CREATE TABLE products1
(prod_id varchar2(8) NOT NULL,
prod_name char(30),
price number(10,2),
on_hand varchar2(10),
supp_id varchar2(8),
constraint pk_products2 Primary Key (prod_id),
constraint fk_products2 Foreign Key (supp_id) references suppliers5(supp_id));
create table sales2
(cust_id varchar2(6) NOT NULL,
prod_id varchar2(8) NOT NULL,
quantity smallint,
date_of_sale varchar(9),
constraint pk_sales PRIMARY KEY (cust_id, prod_id));
CREATE TABLE suppliers5
(supp_id varchar2(6) NOT NULL,
company_name varchar2(15),
town varchar2(15),
phone varchar2(15),
constraint pk_suppliers PRIMARY KEY (supp_id));
The query I am struggling with taks me with the following
some of your products are not selling, you would like a list of sales which also includes those products which have not sold any during the period covered by the tables. Display product ID, the product name, and the date of sale for all products sold. Also include those products which have NOT sold.
The code I am using is
select p.prod_id as product, p.prod_name as "product name", s.date_of_sale
from products1 p, sales67 s
WHERE s.date_of_sale >= '01-Jan-06'
AND p.prod_id = s.prod_id
I now need to add a command to this that will show the products that have not sold.
I have been at this for days. I am prerparing for an exam.
Please can someone help?
June 5, 2013 at 2:20 am
It would be something like this:
....
from products1 p left outer sales67 s
ON p.prod_id = s.prod_id
WHERE s.date_of_sale >= '01-Jan-06'
OR s.date_of_sale IS NULL
June 5, 2013 at 2:29 am
Hi Daniel and welcome to SSC. Thanks for doing an excellent job of posting up ddl.
danielb27 (6/5/2013)
...some of your products are not selling, you would like a list of sales which also includes those products which have not sold any during the period covered by the tables. Display product ID, the product name, and the date of sale for all products sold. Also include those products which have NOT sold...
Is this a homework question? It's not a problem if it is - it's just that folks often answer homework questions in a different way to workplace questions.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
June 5, 2013 at 2:31 am
Hi
I am self preparing for an SQL exam and found a mock test online
I found code that worked.
select p.prod_id as product, p.prod_name as "product name", s.date_of_sale
from products1 p
left join sales67 s
on p.prod_id = s.prod_id
AND s.date_of_sale >= '01-Jan-06'
While you are on I wonder if you could help me one more time.
Hope its not cheeky to ask.
I need to show the most popular selling item in terms of quality sold. I must display the product ID, product name, and quantity sold.
I gave up with this one, if you could help you would make a man very happy.
Thanks in advance kind sir
June 5, 2013 at 2:41 am
Because it is for your learning purposes, I will not post a complte solution :-P. But I will give you some pointers:
You still need to join Products and Sales. Take a look at the COUNT(sales.records) in combination with the GROUP BY function.
Success!!
June 5, 2013 at 2:46 am
lol thats all I can ask for
this is what I went for
SELECT p.Prod_id, prod_name, SUM(s.quantity) TotalSalesperProduct
FROM Products1 p
JOIN Sales2 s ON p.Prod_id = s.prod_id
GROUP BY p.Prod_id, prod_name
ORDER BY TotalSalesperProduct DESC
however this brings back all the products and their quantities sold I just need to return the one product that has sold ther most
June 5, 2013 at 2:50 am
Good effort, almost there....
You'll only need to select a TOP 1 and add an ORDER BY.
June 5, 2013 at 2:51 am
You have lost me now 🙁
I need a drink lol
June 5, 2013 at 2:53 am
HanShi (6/5/2013)
Good effort, almost there....You'll only need to select a TOP 1 and add an ORDER BY.
Oops... I didn't read carefully. You had allready added the ORDER BY.
June 5, 2013 at 2:56 am
SELECT TOP 1 WITH TIES p.Prod_id, prod_name, SUM(s.quantity) TotalSalesperProduct
FROM Products1 p
JOIN Sales2 s ON p.Prod_id = s.prod_id
GROUP BY p.Prod_id, prod_name
ORDER BY TotalSalesperProduct DESC
I do this which looks right to me but it tells me that from keyword not found where expected
June 5, 2013 at 3:02 am
danielb27 (6/5/2013)
keyword not found
Thats probably because "TotalSalesperProduct" is an alias and is not recognized by the parser. Replace it with the original statement will do the trick.
June 5, 2013 at 3:07 am
I am still getting the same error
FROM KEYWORD NOT FOUND WHERE EXPECTED
It may be the version I am using
It is through oracle application express I am using it
dont know if that makes any sense
June 5, 2013 at 3:11 am
SELECT p.Prod_id, prod_name, SUM(s.quantity) TotalSalesperProduct
FROM Products1 p
JOIN Sales2 s ON p.Prod_id = s.prod_id
GROUP BY p.Prod_id, prod_name
ORDER BY TotalSalesperProduct DESC
the above works fine this brings back all products and quantities sold
when I put the below in I get the error message that from keyword not found where expected
SELECT TOP 1 WITH TIES p.Prod_id, prod_name, SUM(s.quantity) TotalSalesperProduct
FROM Products1 p
JOIN Sales2 s ON p.Prod_id = s.prod_id
GROUP BY p.Prod_id, prod_name
ORDER BY TotalSalesperProduct DESC
its something to do with the top 1 with ties command it seems
June 5, 2013 at 3:13 am
SELECT Prod_id, prod_name,TotalSalesperProduct
FROM
(
SELECT DENSE_RANK() OVER (ORDER BY SUM(s.quantity) DESC) AS Rnk,p.Prod_id, prod_name, SUM(s.quantity) AS TotalSalesperProduct
FROM Products1 p
JOIN Sales2 s ON p.Prod_id = s.prod_id
GROUP BY p.Prod_id, prod_name
)t
WHERE rnk=1
this worked.
Thanks for all your help I will be using forum again
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply