sql project urgent 20 queries

1036966701/Project 1 F17.docx

Note: While it is understood that groups may share their expertise and communicate with each other, each group’s work must reflect sufficient individual group effort to merit its grade. Providing solutions to other groups is not acceptable. Such blatant collusion will result in a grade reduction and in extreme cases could result in a zero and a charge of academic misconduct.

Using the Project 1 database, populate the database with sufficient data to test your queries (See Note below). Develop and document the SQL queries necessary to satisfy the following. Only show in the output the details required by the specifics of the query. No unnecessary or debug columns.

-- 1. List details for the appointments where the billed amount is greater than the specialty rate for that attending vet

-- 2. List for each owner the total amount they have been billed for all their appointments in 2014 combined. However only show the results if the total exceeds $2000

-- 3. List the owner details for patients who are a “Cat” type of either the "Persian" or "Siamese" breed. Show each owner only once

-- 4. List the id, name and specialty for every Vet that has attended appointments in June 2014. Show each Vet only once. Sequence by name within specialty

-- 5. List the details for the owners that have had appointments in 2013. Only show each owner once and sequence by owner email

-- 6. List the details for all appointments scheduled for March 2015. Sequence the appointments by earliest date first sequence

-- 7. List the youngest date of birth for a patient that is a “Cat” type of the "Persian" breed

-- 8. Show the count of the total number of appointments seen by the vet Mary in 2014

-- 9. List the details of any patients on file that were seen in 2014. Show details only once. Sequence by patient name within breed

-- 10. Show the count for the number of different Specialties for which there is a Vet on staff

-- 11. List details for the owners that have animals of the “Cat’ type of the “Siamese” breed. Show each owner only once and sequence alphabetically by owner name

-- 12. List the patient id for any patient whose average bill has been more than $400 in 2014

-- 13. Show a count of the number of patients by pet type. Order the output by highest count to lowest

-- 14. List owner id for the owners that have more than 3 patients

-- 15. List in alphabetic sequence by name, details for the vets that have “Cat” as a specialty.

--16. List the owner details for those owners whose id starts with a '2' and whose address contains either the characters ‘Markham’ or ‘Scarborough’

--17. Show the total value of billing for each patient that has had at least 3 appointments in 2014

--18. List the details for owners that have had patients seen at appointments with the vet named Thomas. Only show each owner once and sequence by owner name

--19. List the details for vets that do not have a “Snake “specialty but have still had an appointment with either a “Burmese Python” or an “African Boa” breed

--20. Write the following commands. I must be able to test run i), ii) and iii) below in sequence.

-- 20(i). Write a command to add a new specialty to the database

-- 20(ii). Write a command to update that new specialty's rate by 15% (Use a numeric expression to calculate the new rate. Don't directly hard code the new value)

-- 20(iii).Write a command to delete the new specialty

Note: Queries that do not return sufficient data will be marked as wrong

i) All queries must generate some output

ii) Queries with explicit sequencing must return a minimum of 3 rows

iii) An aggregate query must have at least one row of output that is using at least 2 input rows (e.g. a count of 2, not 0 or 1)

iv) Use column aliases where appropriate.

v) Suppress duplicate rows where appropriate for output based on your actual data even if not specifically requested in the question.

Have one group member submit a Zip file (not RAR) thru Blackboard that will contain the following files:

i) A text file containing names and student ids for the group.

ii) The mdf and log files of the database as modified with your test data.

(Note: to copy or zip these database files they must be detached or offline)

iii) The Sql file containing your answers (with the questions) to the queries.