Task 2 Provide the implementation of the following stored procedures and function. For submission, please include both
Posted: Thu Jun 02, 2022 8:25 am
Task 2 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.
The list of tables available for this task is the following:
CLIENT (ClientNo, ClientName, ClientAddress, ClientPhone,
ClientEmail)
CORPORATE_CLIENT (ClientNo, CorporationName,
BusinessAddress)
INDIVIDUAL_CLIENT (ClientNo, PropertyOwner)
BUSINESS (ABNNumber, BusinessName, ContactName, ContactNumber,
ContactEmail, BusinessAddress, BusinessPostcode)
FREELANCER_BUSINESS (ABNNumber, EliteMemberID)
CORPORATE_BUSINESS (ABNNumber)
JOB (JobID, JobDescription, UrgencyLevel, JobAddress, Postcode,
SelectedBusinessABNNumber, IndustryID)
CONTRACT_JOB (JobID, StartDate, EndDate, CorporateClientNo)
CASUAL_JOB (JobID, IndividualClientNo)
INDUSTRY (IndustryID, IndustryTitle, UnionID)
TRADE_UNION (UnionID, UnionTitle, UnionContactName,
UnionContactNumber, UnionEmail, UnionAddress, EliteMemberID)
SUBURB (Postcode, SuburbName)
INVOICE (InvoiceNo, Amount, JobID)
SEMINAR (SeminarID, SeminarTitle, SeminarDataTime,
SeminarVenue)
QUOTATION (JobID, ABNNumber, QuoteAmount)
BUSINESS_INDUSTRY (ABNNumber, IndustryID)
ADJACENT_SUBURB (PostCode, AdjacentPostCode)
ELITE_MEMBER (EliteMemberID)
SEMINAR_ATTENDIES (EliteMemberID, SeminarID)
NOTE: PK is printed underlined and FK is printed italic in
italics.
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.
The list of tables available for this task is the following:
CLIENT (ClientNo, ClientName, ClientAddress, ClientPhone,
ClientEmail)
CORPORATE_CLIENT (ClientNo, CorporationName,
BusinessAddress)
INDIVIDUAL_CLIENT (ClientNo, PropertyOwner)
BUSINESS (ABNNumber, BusinessName, ContactName, ContactNumber,
ContactEmail, BusinessAddress, BusinessPostcode)
FREELANCER_BUSINESS (ABNNumber, EliteMemberID)
CORPORATE_BUSINESS (ABNNumber)
JOB (JobID, JobDescription, UrgencyLevel, JobAddress, Postcode,
SelectedBusinessABNNumber, IndustryID)
CONTRACT_JOB (JobID, StartDate, EndDate, CorporateClientNo)
CASUAL_JOB (JobID, IndividualClientNo)
INDUSTRY (IndustryID, IndustryTitle, UnionID)
TRADE_UNION (UnionID, UnionTitle, UnionContactName,
UnionContactNumber, UnionEmail, UnionAddress, EliteMemberID)
SUBURB (Postcode, SuburbName)
INVOICE (InvoiceNo, Amount, JobID)
SEMINAR (SeminarID, SeminarTitle, SeminarDataTime,
SeminarVenue)
QUOTATION (JobID, ABNNumber, QuoteAmount)
BUSINESS_INDUSTRY (ABNNumber, IndustryID)
ADJACENT_SUBURB (PostCode, AdjacentPostCode)
ELITE_MEMBER (EliteMemberID)
SEMINAR_ATTENDIES (EliteMemberID, SeminarID)
NOTE: PK is printed underlined and FK is printed italic in
italics.