SQL Query Help

Stanlex

Hi, just teaching myself some SQL. I tend to start learning different technologies until I hit a problem. Then I seek help.

Anyway, so I started learning SQL and came across a problem.

It goes like this:

The following are relations for a zoological database.

Zoo(zoo_id, name, address, country)
Section(section_id, name, description, zoo_id)
Animal(animal_id, name, scientific_name,date_of_birth, gender, section_id)
Food(food_id, name, type)
Animal_food(animal_id, food_id)

Find all the male animals.
Find all the animals who eat bananas.
Find all the animals born after 2000 and list in age order (from old to young)
List the number of animals, less than one year old, that eat their vegetables or meat. Show separate total for each food type.

I started learning SQl about a week ago and was just doing basics. Like SELECT * FROM "table here"

I spent too much time reading about it and not doing it and I forgot quite a bit.

Could anyone start me off with the above? they sound pretty straight forward but I cant seem to get my head around them?

Cheers

Greyian

Hey

You'll need to familiarise yourself with the WHERE clause. That's all you'll need for the first one. The 1st one, as I'm sure you've noticed, is far and away the simplest.

For the remaining ones, you'll need to combine multiple operations. It might sound difficult, so just think about it step-by-step (basically narrowing your results on each step).

For the 2nd one, you'll want to look at Joins. I would suggest looking at the various join types (inner, left, right, full), though you can carry it out using just the join in the example shown (which is an inner join) on the linked page.

For the 3rd one, familiarise yourself with Order By.

For the final one, you'll need to use Count, along with some of the other functions used previously.

Stanlex

Greyian wrote: »

Hey

You'll need to familiarise yourself with the WHERE clause. That's all you'll need for the first one. The 1st one, as I'm sure you've noticed, is far and away the simplest.

For the remaining ones, you'll need to combine multiple operations. It might sound difficult, so just think about it step-by-step (basically narrowing your results on each step).

For the 2nd one, you'll want to look at Joins. I would suggest looking at the various join types (inner, left, right, full), though you can carry it out using just the join in the example shown (which is an inner join) on the linked page.

For the 3rd one, familiarise yourself with Order By.

For the final one, you'll need to use Count, along with some of the other functions used previously.

Thanks for that.

Just looked at WHERE and JOINS and came up with this for the second one:

SELECT Animal.name, Food.type
FROM Animal, Food
WHERE Food.type = 'bananas'

That was just a quick mock up. How does it compare to the correct answer? Am I far off? Where am I going wrong?

TonyStark

Stanlex wrote: »

Thanks for that.

Just looked at WHERE and JOINS and came up with this for the second one:

SELECT Animal.name, Food.type
FROM Animal, Food
WHERE Food.type = 'bananas'

That was just a quick mock up. How does it compare to the correct answer? Am I far off? Where am I going wrong?

There is no direct link between the animal and food from your sql. The question is how do you know what food is for what animal.

You need to use the: Animal_food(animal_id, food_id) table.

SELECT NAME
FROM animal
WHERE animal_id IN (
		SELECT animal_id
		FROM animal_food
		WHERE food_id IN (
				SELECT DISTINCT food_id
				FROM food
				WHERE NAME = 'bananas'
				)
		)

You start of with getting the food_id of bananas and then selecting all the animal ids that have that id. Then you pull back a list of animal names that relate to that. The above could have been accomplished equally as well with joins etc.

Colonel Panic

I think left joining Animals to AnimalFood and joining that to Food is a much easier to follow than the subselect code posted above.

Stanlex

TonyStark wrote: »

There is no direct link between the animal and food from your sql. The question is how do you know what food is for what animal.

You need to use the: Animal_food(animal_id, food_id) table.

SELECT NAME
FROM animal
WHERE animal_id IN (
		SELECT animal_id
		FROM animal_food
		WHERE food_id IN (
				SELECT DISTINCT food_id
				FROM food
				WHERE NAME = 'bananas'
				)
		)

You start of with getting the food_id of bananas and then selecting all the animal ids that have that id. Then you pull back a list of animal names that relate to that. The above could have been accomplished equally as well with joins etc.

That's a little too complicated for a beginner. Is there a way to do that for a beginner to understand?

Skrynesaver

SELECT ANIMAL.Name 
   from ANIMAL, ANIMAL_FOOD, FOOD 
 where ANIMAL.Animal_id=FOOD.Animal_id 
    and FOOD.Food_id=ANIMAL_FOOD.Food_id 
    and lower(FOOD.Name)='bananas';

jpb1974

Quick whizz... not fully verified:


-- Find all the male animals.

SELECT * FROM Animal WHERE UPPER(gender) = 'M'

-- Find all the animals who eat bananas.

SELECT
a.animal_id,
a.name
FROM
Animal a
JOIN Animal_food af ON a.animal_id = af.animal_id
JOIN Food f ON f.food_id = af.food_id
WHERE
UPPER(f.name) = 'BANANAS'

-- Find all the animals born after 2000 and list in age order (from old to young)

SELECT
a.*
FROM
Animal a
WHERE
a.date_of_birth >= '2000-01-01'
ORDER BY
a.date_of_birth

-- List the number of animals, less than one year old, that eat their vegetables or meat. Show separate total for each food type.

SELECT
f.type,
COUNT(0) AS Count
FROM
Animal a
JOIN Animal_food af ON a.animal_id = af.animal_id
JOIN Food f ON f.food_id = af.food_id
WHERE
a.date_of_birth > (GETDATE() - 365)
AND UPPER(f.type) in ('MEAT','VEGETABLE')
GROUP BY
f.type

BrokenArrows

Skrynesaver wrote: »

SELECT ANIMAL.Name 
   from ANIMAL, ANIMAL_FOOD, FOOD 
 where ANIMAL.Animal_id=FOOD.Animal_id 
    and FOOD.Food_id=ANIMAL_FOOD.Food_id 
    and lower(FOOD.Name)='bananas';

I personally hate those types of joins. Very hard to follow.

neil_hosey

TonyStark wrote: »

There is no direct link between the animal and food from your sql. The question is how do you know what food is for what animal.

You need to use the: Animal_food(animal_id, food_id) table.

SELECT NAME
FROM animal
WHERE animal_id IN (
		SELECT animal_id
		FROM animal_food
		WHERE food_id IN (
				SELECT DISTINCT food_id
				FROM food
				WHERE NAME = 'bananas'
				)
		)

You start of with getting the food_id of bananas and then selecting all the animal ids that have that id. Then you pull back a list of animal names that relate to that. The above could have been accomplished equally as well with joins etc.

what ever you do .. DO NOT use this shítty nested queries.. they are so much slower and complex than using joins. They are a pretty bad habit to get from the start when learning.

All animals who eat bananas:

select * from animals a
join animal_food af on a.animal_id = af.animal_id
join Food f on af.food_id = f.food_id
where f.Name = 'Banana'

use the above as a base to answer the rest of the questions.

jpb1974

TonyStark wrote: »

There is no direct link between the animal and food from your sql.

Animal_food is an intersection table that creates a many to many relationship between animal and food ->

An animal can eat many foods. A food can be eaten by many animals.

TonyStark

neil_hosey wrote: »

what ever you do .. DO NOT use this shítty nested queries.. they are so much slower and complex than using joins. They are a pretty bad habit to get from the start when learning.

All animals who eat bananas:

select * from animals a
join animal_food af on a.animal_id = af.animal_id
join Food f on af.food_id = f.food_id
where f.Name = 'Banana'

use the above as a base to answer the rest of the questions.

Agreed, also try to avoid using *. It lends itself to sloppiness as well :-)

Beano

Stanlex wrote: »

That's a little too complicated for a beginner. Is there a way to do that for a beginner to understand?

Its not that its too complicated. Its just a terrible way to get that set of results.

Beano

BrokenArrows wrote: »

I personally hate those types of joins. Very hard to follow.

Ah the joys of Non-ANSI joins. trying to figure out where to put the * for left and right outer joins was always fun*



*may be slightly sarcastic.