Implement the following tasks using ORACLE SQL*Plus

–a

 

SELECT industryTitle, MAX (jbs.nums) AS Most_Popular_JobType

FROM

(

SELECT j.industryID AS indID, COUNT(*) AS nums

FROM job j

) jbs

INNER JOIN industry i ON j.industryID=i.industryID GROUP BY Most_Popular_JobType

 

 

–b

SELECT jobDescription, cj.EndDate – cj.StartDate As Duration

FROM contract_job cj

INNER JOIN jobs j ON cj.jobID=j.jobID ORDER BY Duration DESC

FETCH FIRST 1 ROWS ONLY;

 

 

 

 

–c

 

SELECT b.ABNNumber,b.BusinessName

FROM job j INNER JOIN industry  ind ON j.IndustryID=ind.IndustryID

INNER JOIN business_industry bi ON ind.IndustryID=bi.IndustryID

INNER JOIN business b ON bi.ABNNumber=b.ABNNumber

LEFT JOIN adjacent_suburb asb ON b.BusinessPostcode=asb.Postcode

LEFT JOIN business b2 ON asb.AdjacentPostCode=b2.BusinessPostcode

WHERE j.jobID=11

 

 

 

 

–d

 

SELECT c.ClientNo,c.ClientName

FROM client c

INNER JOIN equipment_hire eh ON c.ClientNo=eh.ClientNo

WHERE MONTHS_BETWEEN(TRUNC(sysdate),eh.PickUpDate)>3

 

 

 

–e

 

SELECT MAX(ReviewRating) As “Highest rated business”, MAX(ReviewRating)/5 As “Highest rating out of 5”, MIN(ReviewRating) As “Lowest rated business”, MIN(ReviewRating)/5 As “Lowest rating out of 5”

FROM jobs j

INNER JOIN review rv ON j.jobID=rv.jobID

INNER JOIN business b ON j.SelectedBusinessABNNumber=b.ABNNumber

 

 

–f

 

SELECT c.ClientNo,c.ClientName,  SUM(Amount) AS “Total Job Expense”,(DropOffDate-PickUpDate)*150 As “Total Equipment Hiring Expense”

FROM client c

LEFT JOIN equipment_hire eh ON c.ClientNo=eh.ClientNo

LEFT JOIN corporate_client ccl ON c.ClientNo=ccl.ClientNo

LEFT JOIN contract_job cjb ON c.ClientNo=cjb.CorporateClientNo

LEFT JOIN job j ON cjb.JobID=j.JobID

LEFT JOIN invoice inv ON j.JobID=inv.JobID

WHERE DropOffDate IS NOT NULL

GROUP BY c.ClientNo

 

 

–g

 

 

SELECT e.EquipmentID,e.EquipmentName,Count(*) As total_Hires

FROM equipment e

INNER JOIN equipment_hire eh ON e.EquipmentID=eh.EquipmentID

GROUP BY e.EquipmentID

ORDER BY total_Hires DESC

FETCH FIRST 2 ROWS ONLY;

 

 

 

 

–h

 

SELECT b.ABNNumber,b.BusinessName,ContactName

FROM   freelancer_business fbz

INNER JOIN business b ON b.ABNNumber=fbz.ABNNumber

INNER JOIN elite_member em ON fbz.EliteMemberID=em.EliteMemberID

LEFT JOIN seminar_attendies sa ON em.EliteMemberID=sa.EliteMemberID

WHERE SeminarID IS NULL

 

 

–i

SELECT b.ABNNumber,b.BusinessName

FROM business b

INNER JOIN quotation q ON b.ABNNumber=q.ABNNumber

LEFT JOIN job j ON q.JobID=j.JobID

WHERE SelectedBusinessABNNumber IS NULL

 

 

 

–j

 

SELECT SeminarTitle, SeminarDateTime, count(EliteMemberID) As Attendees

FROM seminar s

INNER JOIN seminar_attendies sa ON s.SeminarID =sa.SeminarID

WHERE TO_CHAR(SeminarDateTime)>”12:00″ AND TO_CHAR(SeminarDateTime)<“22:00”

ORDER BY Attendees DESC

 

 

 

 

 

 

2.

 

–a

CREATE OR REPLACE PROCEDURE industry_details(

induID IN VARCHAR2,

o_UnionTitle OUT UnionTitle%RowType,

o_ABNNumber OUT b.ABNNumber%TYPE,

o_BusinessName OUT b.BusinessName%TYPE

 

)

 

IS

BEGIN

SELECT UnionTitle,b.ABNNumber, b.BusinessName

INTO o_UnionTitle,o_ABNNumber, o_BusinessName

FROM trade_union tu

INNER JOIN industry i ON i.UnionID=tu.UnionID

INNER JOIN business_industry bi ON i.IndustryID=bi.IndustryID

INNER JOIN business b ON bi.ABNNumber=b.ABNNumber

WHERE IndustryID= induID;

 

 

End;

/

 

 

 

—-b

CREATE OR REPLACE PROCEDURE industry_details(

CliID IN VARCHAR2,

o_UnionTitle OUT UnionTitle%RowType,

o_ABNNumber OUT b.ABNNumber%TYPE,

o_BusinessName OUT b.BusinessName%TYPE

 

)

 

IS

BEGIN

SELECT UnionTitle,b.ABNNumber, b.BusinessName

INTO o_UnionTitle,o_ABNNumber, o_BusinessName

FROM trade_union tu

INNER JOIN industry i ON i.UnionID=tu.UnionID

INNER JOIN business_industry bi ON i.IndustryID=bi.IndustryID

INNER JOIN business b ON bi.ABNNumber=b.ABNNumber

WHERE IndustryID= induID;

 

 

End;

/

 

 

3.

 

CREATE OR REPLACE TRIGGER restrict_changes

BEFORE DELETE OR INSERT OR UPDATE ON review

FOR EACH ROW

WHEN (NEW.ReviewID > 0)

BEGIN

IF :old.jobID = NULL THEN

raise_application_error(-20015, ‘You can’t Give a review’);

END IF;

 

END;

/

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s


%d bloggers like this: