outcome2作业.doc

Assessment task 2Outcome(s) covered2 Assessment task instructionsBefore commencing the assessment your attention is drawn to the following:The assessor reserves the right to reject any material that is not deemed the work of the candidate. Further, any material from another candidate that has been used for copying, will also be rejected.Information needs to be extracted from the database. Your task is to develop the SQL SELECT commands to extract that data.As these reports are run on a regular basis, it is vital that the necessary commands are stored in command files. The output from each command file is spooled to a file for subsequent printing.Several reports are needed in order to answer the questions below. Each of these reports will consist of only one SQL SELECT command to obtain the desired result. To assist you in determining whether the structure of your SQL is correct you are provided with the expected results. EvidenceYou are required to provide evidence of the SQL (and SQL*Plus) commands created and the resulting output from executing those SQL (and SQL*Plus) commands.This evidence is provided by submitting to your tutor:1. The completed checklist (provided by your tutor). Simply enter your name, date of submission, candidate number and completed tick () column2. Signed and dated printouts of the command files containing the submitted SQL and SQL*Plus commands3. Signed and dated printouts of spool files containing the results of running the command files.Notes If you prefer you may submit one command file, and one spool file, containing all your evidence Include comments in your command file(s) to show your full name, candidate number and date of creation of the command fileInclude an ECHO ON SQL*Plus command in your command files in order to display the commands as they are executed.Assessment TasksThe following reports are needed by the Healthy Pets Veterinary Surgery.1. Display details of the owners and their pets. The report should show the full name (i.e. title, forename, surname) of the owner together with the name and breed of the pet. The output should be sorted on the owners surname and the pets name select Title,Surname,Forename,Name,Breed from OWNER,PETwhere OWNER.Owner_Id=PET.Owner_Idorder by Surname,Name;2. Show all those pets born between 1999 and 2001 inclusive. The pets id together with its name, type, breed and year of birth is required. The report should list the pets in order of age, i.e. the oldest first select Pet_Id,Name,Type,Breed,Born from PETwhere Born BETWEEN 1999 AND 2001order by Born DESC;3. Produce a list of the different types of pets together with a count of how many there are of each. Ignore any where there is only one of the type. The report should list the highest occurrences first select Type, COUNT (Type) as wang from PET GROUP BY Type HAVING COUNT(*)1 ORDER BY wang DESC4. What is the total salary bill for the junior partners and the senior partners? select sum(Salary) from VET;5. Produce a list of pets whose name starts with or ends with the letter s. Exclude all male pets from the list. Display the pets id together with its name, breed and gender select Pet_Id,Name,Breed,Gender from PET where(Gender=F OR Gender=X) AND (Name LIKE %s OR Name LIKE s%);6. Create a report of all visits made by dogs. Show the pets id, name, breed, date of visit and basic cost. Sort the report by the pets ID and date of visit select PET.Pet_Id,Name,Breed,Visit_Date,Basic_Cost from PET,VISITwhere Type=dogAND PET.Pet_Id=VISIT.Pet_Idorder by PET.Pet_Id,Visit_Date;7. List the number of visits attended by each vet. Include the vets full name details together with the count of visits attended. Sort the report by the vets name details select Surname,COUNT (VISIT.Vet_Id) from VISIT, VETwhere VISIT.Vet_Id=VET.Vet_Idgroup by Surname,VISIT.VET_Idorder by Surname, VISIT.VET_Id;8. List those pets that have had a problem with either an ear or nose. Show the pets id, name, type and symptomselect PET.Pet_Id,Name,Type,Symptom from PET,VISITwhere (Symptom=Bitten left ear OR Symptom=Bitten right ear OR Symptom=nose)AND PET.Pet_Id=VISIT.Pet_Id9. What is the total cost of medications prescribed by each vet each day? Only display the vets id, date of visit and the total cost. Sort the report by the vets id and the date of visitselect MEDICATION.Vet_Id,Visit_Date,SUM(Cost) from MEDICATIONgroup by MEDICATION.Vet_Id,Visit_Dateorder by MEDICATION.Vet_Id,Visit_Date;10. Display a list of those pets that had an injection during May 2004. Show the date of the visit, the pets name, breed and type of treatment. Sort the report on the date of the visit select VISIT.Visit_Date,Treatment,Name,Breed from VISIT,PET where VISIT.Pet_Id=PET.Pet_Id AND Treatment=injection AND Visit_Date BETWEEN 04/01/2004 AND 04/30/2004 order by Visit_Date;11. Find the average, minimum, maximum and total cost of the visits made by Tiddles the cat for those visits attended by the vet Trevor McCafferty. Display the name and type of the pet together with the calculations 12. Make a list of the symptoms, treatments, and medicines for pet whose id is P0001. Display the pets ID, date of the visit together with the symptoms, treatments and medicines. Sort the report on the pets id and date of visitHint: be careful when joining the tables together, as the join between the PK in one table and the FK in the other table consists of more than one columnNote:Within some of the reports, demonstrate your knowledge of using alternative headings for the columns displayed.Assessment ChecklistsUnit Assessment: Candidates Assessment RecordDH3J 34: SQL: IntroductionClassGroupCandidate NameCandidate IDOutcome 2Evidence RequirementsRecord of PerformanceManipulate Data Stored Within a Table StructureSatisfactory/UnsatisfactoryCommentsAssessment Task 21. Report: Owners and pets2. Report: Pets born between 1999 and 20013. Report: Types of pets4. Report: Total salary bill5. Report: Pet names starting / ending with s6. Report: Visits made by dog