COIT11237 SQL Queries And Query Analysis Assessment Answer

pages Pages: 4word Words: 890

Question :

Assessment item 1


This assessment item relates to unit learning outcomes 1 and 2 in the unit profile.


Students have to complete all the following parts of this assessment taks:

A.SQL Queries

B. Query Analysis


D. Report 


Download the Microsoft Access database, Chemical_Composition_Analysis.mdb, from the unit website and use it to complete this assessment. 

If you get a security warning message when opening the downloaded database, then you need to open/click the options and then choose the option “enable this content”.

Do not modify the structures of the tables and relationships.  Do not create additional tables.  It is not recommended to delete any of the existing records from the downloaded database (hereafter referred as ‘your database’).

You need to write SQL queries in your database and make sure those queries produce the desired results as shown in Part A. Save and name those queries suitably such as Q1, Q2, ......, Q10 respective to the question that you are answering.

You need to create a MS Access-form and also a MS Access-report that are related to Part C and    Part D and save them in your database.

Chemical Composition Analysis.accdb 

The database, Chemical_Composition_Analysis.mdb, contains tables that can store the data related to the appointments made by the clients for analysing the chemical composition of materials collected by the government agencies.  Tests are conducted on materials only when referred by the government agencies. Tests are conducted by the Lab technicians, who have expertise in various chemical tests.  In order for the tests to be conducted, the employee of the respective government agency needs to make an appointment with a particular lab technician.    Only one material/sample is tested by the assigned lab technician in that appointment. If there is more than one sample, then tests are carried out only when there are subsequent appointments.  As per the testing process, similar tests may be conducted on multiple samples in different appointments. 

Part A – SQL Queries    

Using your database, write SQL queries to answer all the questions in this part. Each of the following questions has an information request followed by the expected results when your database has the given sample data.

Hint: To get the desired output, you need to check the column headings; grouping and sorting of data displayed; removal of duplicate data; and other aspects of the query.

List the Government Agency ID, Name, Contact number for all Government agencies, as shown below.A single column result is required. 

Expected result using sample data: 

Government    Agency Details
501- Name: Vic-Police- Contact   Number: 66010
502- Name: Federal agency- Contact   Number: 66600
503- Name: Vic-Health- Contact   Number: 66601
504- Name: Qld-Police- Contact   Number: 66602
505- Name: Qld-Health- Contact   Number: 66603
506- Name: SA-Police- Contact Number:   66604
507- Name: SA-Health- Contact Number:   66605
508- Name: ACT-Police- Contact   Number: 66606
509- Name: ACT-Health- Contact   Number: 66607
510- Name: WA-Police- Contact Number:   66608
511- Name: WA-Health- Contact Number:   66609
512- Name: NT-Police- Contact Number:   66611
513- Name: NT-Health- Contact Number:   66612
514- Name: Tas-Police- Contact   Number: 66613
515- Name: Tas-Health- Contact   Number: 66614

Display the details of all the Lab Technicians and their qualifications.

Expected result using sample data:

Technician IDNameContactQualification
106Adam Williams94025464Bachelor of Biomedicine
107Eve Stark94025463Bachelor of Chemical Engineering
101Kevin Peterson94025467Lab Technician Certificate
101Kevin Peterson94025467Bachelor Biomedicine
102Michael Clarke94025468Bachelor of Chemical Engineering
102Michael Clarke94025468Lab Technician Certificate
103Ricky Ponting94025469Bachelor of Science
103Ricky Ponting94025469Certificate IV
104Sally Jones94025466Certificate IV
104Sally Jones94025466Bachelor of Chemical Engineering
108Tony Stark94025462Master of Science
108Tony Stark94025462Bachelor of Science
108Tony Stark94025462Certificate IV
108Tony Stark94025462Diploma in IT
108Tony Stark94025462Lab Technician Certificate
105Victoria Lannister94025465Bachelor of Science

Display the Technician names and the number of appointments attended by each Technician in April. 

Expected result using sample data:

Technician IDNameTotal no of appointments
108Tony   Stark1
107Eve   Stark1
106Adam   Williams1
105Victoria   Lannister1
104Sally   Jones1
101Kevin   Peterson1

List the details of Lab test(s) that were never performed in any of the appointments till date and time.

Expected result using sample data:

9Advanced confirmation AnalysisIt includes various additional tests9000
10Advanced Trace AnalysisThis is an advanced Trace analysis   and consumes significant amount of time10000
11Advanced Petroleum AnalysisInvolves additional tests15000

Present the details of all lab tests that have been performed in the appointments till now. Display the results in ascending order of Test ID.

Expected result using sample data:

Test IDAppointment    IDAppointment    DateAppointment    TimeLab    Technician

Present the summary of ‘samples’ sent by each Government Agency. 

Expected result using sample data:

Gov_Agency_IDGov_Agency_NameNo of samples    sent
502Federal agency1
  1. Display the details of Lab Technicians who have attended more than one appointment.

Expected result using sample data:

101Kevin Peterson94025467
102Michael Clarke94025468
103Ricky Ponting94025469
104Sally Jones94025466
105Victoria Lannister94025465
106Adam Williams94025464
107Eve Stark94025463
108Tony Stark94025462
  1. Display the details of tests that were never performed in any of the appointments and also contain the word ‘Advanced’ in its name.

Expected result using sample data:

Test IDName
Show More

Answer :

For solution, connect with online professionals NOW!