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.

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: