Question

Using mySQL, tables at the end...

This assignment uses the tables from the vets database. The goal of the assignment is to get you to think about joins- inner join and outer joins.  If you do a join, where you have the names of two or more tables in the From clause, then you must use the condition join syntax or the column name join.

Take care that you do not accidentally do a Cartesian product. If your result set contains a thousand rows ( or even a hundred rows for this assignment) you have probably done a Cartesian product and you need to correct the query.

Do not assume that these will all require outer joins because that was the topic for the week. Use an inner join if that will solve the task; use an outer join only if it is required.

Use the fewest tables possible for the queries. For example if I ask you to find animals with no exams, you need to use the exam headers table. But you do *not* need the exam details table. Sometimes adding extra tables makes your query less efficient; other times it makes your query incorrect.

The use of meaningful table aliases is required in assignments. The table names are fairly long and when you need to qualify a column, that expression gets longer and it can be harder to read. You define the table aliases within in each query. It can help to have a consistent set of table aliases to use. These are the ones I use; you can use them or different aliases that suggest the table.

clients cl

animals an

exam headers eh

exam details ed

Task 04: We want to find animals for which we have no exam records (use the exam header table). Show the client id, and last name and the an_id, name and type for every animal that does not have any exam records in the exam table. Sort by the client id and animal id.

Task 05: For this task use the same logic as in Task 03 except that your results should not display clients who have no animals.

Task 06: Display the client id and client last name and the animal id, name, and date of birth for all animals owned by a client who lives in New York or Massachusetts but do not display rows for any of the dogs, cats or birds.

Task 07: Display data about services we have in the services table that have a list price of $100 or more and that have not been used on any exam. Display the service id, list price, description and service type.

TABLES:

create table vt_staff (

    stf_id          numeric(6,0)

, stf_name_last   char(25)    not null

, stf_name_first char(25)    not null

, stf_job_title   char(25)    not null

);

create table vt_staff_pay (

    stf_id          numeric(6,0)

, stf_ssn         char(9)         not null

, stf_salary      numeric(8,2)     not null

, stf_hire_date   date            not null

);

create table vt_services(

    srv_id          numeric(6,0)

, srv_list_price numeric(6,2)   not null

, srv_desc        char(50)    not null

, srv_type        char(25)    not null

);

create table vt_animal_types(

    an_type         char(25)

);

create table vt_clients(

    cl_id           numeric(6,0)

, cl_name_last    char(25)    not null

, cl_name_first   char(25)    null

, cl_address      char(25)    not null

, cl_city         char(25)    not null

, cl_state        char(2)         not null

, cl_postal_code char(12)    not null

, cl_phone        char(12)    null

);

create table vt_animals(

    an_id           numeric(6,0)    

, an_type         char(25)    not null  

, an_name         char(25)    null

, an_dob          date            not null

, cl_id           numeric(6,0)     not null

);

create table vt_exam_headers(

    ex_id           numeric(6,0)    

, an_id           numeric(6,0)     not null

, stf_id          numeric(6,0)     not null

, ex_date         date            not null

);

create table vt_exam_details(

    ex_id           numeric(6,0)     not null

, line_item       numeric(6,0)     not null

, srv_id          numeric(6,0)     not null

, ex_fee          numeric(6,2)     not null

, ex_desc         char(50)    not null

);

Answer 1

Task 04) select cl.cl_id,cl.cl_name_last,an.an_id,an.an_type,an.an_name
from clients cl,animals an
where cl.cl_id=an.cl_id
and an.an_id not in(select an_id from vt_exam_headers)
order by cl.cl_id,an.an_id;

Task 05)
select cl.cl_id,cl.cl_name_last,an.an_id,an.an_type,an.an_name
from clients cl,animals an
where cl.cl_id=an.cl_id
order by cl.cl_id,an.an_id;

Task 06) select cl.cl_id,cl.cl_name_last, an.an_id,an.an_name,an.an_dob
from clients cl, animals an
where cl.cl_id=an.an_id
and cl.cl_state in('New York','Massachusetts')
and an.an_type not in('dogs','cats','birds');

Task 07) select s.srv_id,s.srv_list_price,s.srv_desc,srv.srv_type
from vt_services s
where s.srv_id not in(select srv_id from vt_exam_details)
and s.srv_list_price>=100;