Creating table from already existing table
Hello,
I am trying to do the following task:
This task uses three sql statements:
Because you will be creating a table, the first sql statement will drop the table if it exists.
Drop table if exists SmallAnimals; This is the first sql statement for this
task
The vet wants a base table that contains the following information which comes from the vets tables we
used at the start of the semester
client id
animal id
animal name- limited to the first 10 characters
animal category either Reptile or Rodent. (Refer back to A04 for the definition of those terms)
the date of their most recent exam if they have any exams; This should be cast to a date. If the
animal has no exams, this will be null
the total of all of their exam fees if they have any exams; if the animal has no exams, this will be
null
The table will contain one row for each Rodent and one row for each Reptile.
The columns names to use in the new table are
CLID
ANID
ANNAME
ANCATEGORY
RECENTEXAMDATE
TOTALFEES
Use a SINGLE sql statement to create and populate that table. If you use more than one statement to do this
part, you will get no points for this task. This is the second sql statement for this task
Use a SINGLE sql statement to display the table SmallAnimals . This is the third sql statement for this
task
I created the table using the following query. It creates a table but only returns two rows, which I suspect is not correct. Any thoughts on where I might be going awry?
Drop table if exists SmallAnimals;
Create table SmallAnimals as
select
cl_id as CLID
, an_id as ANID
, an_name as ANNAME
, an_type as ANCATEGORY
, cast(ex_date AS DATE) as RECENTEXAMDATE
, sum(ex_fee) as TOTALFEES
from a_vets.vt_animals
inner join a_vets.vt_exam_headers using (an_id)
inner join a_vets.vt_exam_details using (ex_id)
Where an_type in ('hamster', 'capybara', 'porcupine', 'dormouse','snake', 'chelonian', 'crocodilian', 'lizard')
And cast(ex_date AS DATE) in(
Select max(cast(ex_date AS DATE))
From a_vets.vt_exam_headers)
Group by an_id;