Query with variables

Yes you can set variables within a query. Your syntax is actually quite close.

To do so you need:

SELECT @YourVariable = ColumnFROM Animals

Note: You cannot use the AS when assigning a field to a variable.

You must ensure that all of the fields in the query are assigned to a variable, otherwise you will get the following error:

A SELECT statement that assigns a value to a variable must not be combined with data- retrieval operations.

To overcome this, simply assign AnimalName to an @AnimalName variable.

Edit:

DECLARE @AnimalName  VARCHAR(20)DECLARE @TallestAnimal  INTDECLARE @SmallestAnimal INTSELECT @AnimalName = animal_name,   @TallestAnimal  = (select top 1 height from animal order by height desc),   @SmallestAnimal = (select top 1 height from  animal order by height asc) FROM animalsWHERE height BETWEEN @SmallestAnimal AND @TallestAnimal 

This code is assuming the height field is of type INT.

No, it is not possible, instead use like this:

DECLARE @tallest_animal int, @smallest_animal intSET @tallest_animal=(SELECT max(height) from animals)SET @smallest_animal=(SELECT min(height) from animals)SELECT animal_name from animals where height between @tallest_animal AND @smallest_animal

Something like this will work but I am not sure about what you are looking for.

You can use derived tables instead of variables.

select A.animal_name, M.tallest, M.smallestfrom animals A  inner join       (        select max(height) as tallest,               min(height) as smallest        from animal      ) M    on A.height between M.smallest and M.tallest