Assignment 2 (20%)
Implement the following tasks using ORACLE SQL*Plus.
Download the file SMatchSchema.sql from the LMS site and run it on ORACLE SQL*Plus. This
file contains all the CREATE and INSERT statements you will need for this assignment.
Run the schema file in Oracle following the same process we followed to run a schema during the lab.
As a reference, you can see the instructional video on LMS on how you can copy a schema file to
latcs7 server and execute it in Oracle.
Task 1 [50 marks]
Using the tables provided above, provide SQL statements for the following queries.
a. Display the ID and title for the most popular industry (industry that has the highest number of
b. Display the ID for the job that has the longest contract duration. (Hint: in SQL, if you subtract
two dates, what you get is a difference in days between those dates)
c. Suppose the manager of ServiceMatch wants to notify the businesses about a new job with job
ID ’11’. List the surrounding businesses (ABN number and business name) in the same industry
for that job. (Hint: business table has an attribute called ‘BusinessPostcode’. Job table has
references to the job suburb and industry).
d. List down the details of those freelance businesses who do not have seminar attendance
e. List all the clients and their assiciated total job expenses. If there is a client who did not post
any job, or no invoice was generated for the posted jobs, the total expense should appear as
zero for that client. Your query should list the details in three columns, ‘Client Number’,
‘Client Name’, ‘Total Job Expense’. (Hint: the invoice table lists the expenses for the jobs).
f. List the seminar titles, and date on which they were held, along with the number of participants,
for the seminars arranged during the afternoon part of the day (between 12 PM and 3 PM).
Count 1 participation for each elite member even if the elite member is a trade union.
[a – d: 8 marks each, e-f: 9 marks each – 50%]
Task 2 [35 marks]
Provide the implementation of the following stored procedures and function. For submission, please
include both the PL/SQL code and an execute procedure (for the procedure)/SQL statement (for the
function) to demonstrate the functionality.
a. Write a stored procedure that takes an industry ID as input and lists down the representing
trade union and all the registered businesses for that industry. The first line of the output should
show the trade union title. The ABN and business name for each business should then be shown
on separate lines.
b. Write a stored function that takes a job ID as input and returns the lowest quote amount for
that job. If the job did not receive any quote, the functions returns zero.
[a: 20 marks, b: 15 marks – 35%]
CSE2/4DBF-Assignment 2-Semester 2-2020 Page 4 of 4
本网站支持淘宝 支付宝 微信支付 paypal等等交易。如果不放心可以用淘宝交易！
E-mail: [email protected] 微信:itcsdx