CSE2/4DBF: Queries on a Relational Database System Using SQL - Report Writing IT Assignment Help

Assignment Task:

Task:

AIMS AND OBJECTIVES:
to perform queries on a relational database system using SQL;
to demonstrate an advanced knowledge of stored procedures, stored functions and triggers.

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 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.