CSE2DBF-Database Fundamentals
Assignment
The list of tables available for this assignment is the following:
Task 1
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 jobs).
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 privileges.
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.
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.
Task 3
Provide the implementation of the following trigger. For submission, please include both the PL/SQL code and an insert statement to demonstrate the trigger functionality.
a. A Trigger which automatically stores the information of a job in a backup table called DeletedJobs, when the job is deleted from the job table. Do not use Job ID as the primary key in the backup table, instead, use a database sequence object.
