| New to sql and need some help please (merged) [message #612712] |
Wed, 23 April 2014 12:05  |
 |
om357
Messages: 1
Registered: April 2014
Location: United Kingdom
|
Junior Member |
|
|
I want to write a command to list the names and the id's for all people who have bought a specific thing (item1) but haven't bought (item2)
And another command to list the name, year and to list the items if those items that they bought are more than 4 per year
Thanks
Stef
|
|
|
|
|
|
|
|
|
|
| Re: New to sql and need some help please [message #612718 is a reply to message #612712] |
Wed, 23 April 2014 12:44  |
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
|
Senior Member |
|
|
om357 wrote on Wed, 23 April 2014 22:35I want to write a command to list the names and the id's for all people who have bought a specific thing (item1) but haven't bought (item2)
With such minimal information provided by you, nobody could waste their time but might give you a skeleton of the SQL query. Try to implement this and get back with your feedback :-
1. Requirement 1
select name, id
from table_name
where people_bought_item = item_1
and people_bought_item <> item_2
/
Quote:
And another command to list the name, year and to list the items if those items that they bought are more than 4 per year
2. Requirement 2
select * from(
select name, year, items, count(bought_date) cnt
from table_name
where bought_date > sysdate - > (your expresiion for 4 years
group by name, year, items)
)
where cnt > 4
/
Execute the queries in SQL*PLUS .
NOTE : It's just a hint provided to you, so please try yourself, if required modify the queries and come back with what you try.
|
|
|
|
|
|
| Re: New to sql and need some help please [message #612720 is a reply to message #612719] |
Wed, 23 April 2014 13:11 |
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
|
Senior Member |
|
|
Michel Cadot wrote on Wed, 23 April 2014 23:25
Silly answer.
Yes Michel, silly for masters and experts, but not for beginners who are actually new to SQL. Look at OP's topic, he says he is New to sql and need some help please. Without any help or assistance to him how would he ever come back to this forum. I believe OP will try and come back, even if he doesn't come back with feedback(not necessarily), he would at least get a direction to move ahead with his task. May be it seems silly in this forum, but not in all other forums. People do help in their own way.
Not everything is silly literally. What seems silly to you might be an herculean task for others. And that's the reason we get to see such questions in OraFAQ. Even I can post comments to OP like "come with your tries" or "welcome to forum, look at this link". Oh, common, we all were beginners at some point of time.
If you find a silly mistake in my query, then please correct it and help OP. That's what we are all here for. I tried to help him with whatever I could, that too as a hint/help without proper information. I made sure that I abide by all the forum rules. Still if you think all this is useless, then I don't know what else to say.
|
|
|
|
|
|
| Need help with coding [message #612724 is a reply to message #612712] |
Wed, 23 April 2014 15:45 |
 |
djgstef
Messages: 2
Registered: April 2014
Location: United Kingdom
|
Junior Member |
|
|
Hello i'm having some trouble with this coding if you can please help me . Thanks
1. i have to write a command file to list the gard_name and gard_code# of the gardener table which have grown Maris Piper potatoes but have never grown king edwards potatoes. and to order the output in ascending of gard_name.
I wrote this so far
SELECT Gardener.gard_name, Gardener.gard_code# FROM Gardener, Plot_usage
WHERE plot_usage.veg_id
(SELECT Vegetable.veg_id FROM vegetable
WHERE veg_id = 'MARIS PIPER' AND NOT 'KING EDWARD')
ORDER BY gard_name ASC;
2. write a command file to list the veg_id, year and yearly yield of Plot Usage table of each vegetable that has been grown at any time. order your output by veg_id.
I wrote this so far
SELECT plot_usage.veg_id,plot_usage.year, plot_usage.SUM(yield) FROM (((plot_usage
INNER JOIN vegetable
ON plot_usage.veg_id=vegetable.veg_id_)
INNER JOIN period
ON plot_usage.year=period.year)
INNER JOIN yearly
ON plot_usage.sum(yield) = yearly.sum(yield))
GROUP BY veg_id,year
3. write a command file to list the plot#,year and all the generic vegetable descriptions(gnrc_veg_desc) for the plots where the rule that "no more than 4 generic vegetable descriptions can be grown on any plot in any one year" has, in fact been broken. The output should be ordered in ascending sequence of gnrc_veg_desc within year within plot#
I wrote this so far
SELECT plot_usage.plot#, plot_usage.year, plot_usage.veg_id, vegetable.gnrc_veg_desc
FROM plot_usage, vegetable
WHERE plot_usage.veg_id = vegtable.veg_id > = ((select count >4)
FROM gnrc_veg_desc)
ORDER BY gnrc_veg_desc, year, plot ASC;
|
|
|
|
| Re: Need help with coding [message #612725 is a reply to message #612724] |
Wed, 23 April 2014 15:50 |
Bill B
Messages: 1971
Registered: December 2004
|
Senior Member |
|
|
|
Your first query won't work. Read up on the EXISTS and NOT EXISTS clauses for the where clause in the select. As for the other queries, please show us the structures of the vegetable and plot_usage.
|
|
|
|
| Re: Need help with coding [message #612726 is a reply to message #612724] |
Wed, 23 April 2014 15:53 |
John Watson
Messages: 8930
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
Welcome to the forum. Please read our OraFAQ Forum Guide and please read How to use [code] tags and make your code easier to read
None of your queries will run, they all have syntax errors. What were they? No-one cn help unless you provide this information.
Also, you need to provide the CREATE TABLE statements for your problem, and some INSERT statements to provide a bit of data to work with.
People here are pretty good at providing hints to help with homework questions, if you provide suitable information.
|
|
|
|
|
|
|
|
|
|
| Re: Need help with coding [message #612731 is a reply to message #612729] |
Thu, 24 April 2014 00:12 |
 |
Littlefoot
Messages: 21807
Registered: June 2005
Location: Croatia, Europe
|
Senior Member
Account Moderator |
|
|
I, for example, don't have access to online storage services (because of company policy) and can't even see what you put into that Word document. Moreover, not everyone is willing to download and open files that can be harmful for their computers. Therefore, what you really need to do (if you expect any kind of useful hints) is to follow what people keep saying: take a few minutes and create a test case. It looks like this:
CREATE TABLE phone_book
(tel_number varchar2(20),
p_name varchar2(30)
);
INSERT INTO phone_book (tel_number, p_name) values ('1-234-5678', 'Little');
INSERT INTO phone_book (tel_number, p_name) values ('2-987-6543', 'Foot');[Updated on: Thu, 24 April 2014 00:12] Report message to a moderator |
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
