USING SQL DEVELOPER 1. For each event plan, list the plan number, count of the e

Question

USING SQL DEVELOPER

1. For each event plan, list the plan number, count of the event plan lines, and sum of the number of resources assigned. For example, plan number “P100” has 4 lines and 7 resources assigned. You only need to consider event plans that have at least one line.

2. For event requests, list the event number, event date (eventrequest.dateheld), and count of the event plans. Only include event requests in the result if the event request has more than one related event plan with a work date in December 2018.

3. List the plan number, event number, work date, and activity of event plans meeting the following two conditions: (1) the work date is in December 2018 and (2) the event is held in the “Basketball arena”. Your query must not use the facility number (“F101”) of the basketball arena in the WHERE clause. Pretend that the user knows only the facility name, not the facility number. Text constants in Oracle are case sensitive. Thus, the WHERE clause should not have a condition involving the facility number compared to a constant (“F101”).

4. List the event number, event date, status, and estimated cost of events where there is an event plan managed by Mary Manager and the event is held in the basketball arena in the period October 1 to December 31, 2018. Your query must not use the facility number (“F101”) of the “Basketball arena” or the employee number (“E101”) of “Mary Manager” in the WHERE clause. Pretend that the user knows only the facility name and employee name, not the facility number and employee number. Thus, the WHERE clause should not have conditions involving the facility number or employee number compared to constant values. Text constants in Oracle are case sensitive. (10 points)

5.List the plan number, line number, resource name, number of resources (eventplanline.numberfld), location name, time start, and time end where the event is held at the “Basketball arena”, the event plan has activity of activity of “Operation”, and the event plan has a work date in the period October 1 to December 31, 2018. Your query must not use the facility number (“F101”) of the “Basketball arena” in the WHERE clause. Thus, the WHERE clause should not have a condition involving the facility number compared to a constant (“F101”). Text constants in Oracle are case sensitive. (10 points)

Explanation / Answer

If you have any doubts, please give me comment...

-- 1)

SELECT EP.PlanNo, COUNT(*) AS EventPlanLines, SUM(NumberFld)

FROM EventPlan EP INNER JOIN EventPlanLine EPL ON EP.PlanNo = EPL.PlanNo INNER JOIN ResourceTbl R ON EPL.ResNo = R.ResNo

GROUP BY EPL.PlanNo;

-- 2)

SELECT ER.EventNo, DateHeld, COUNT(EP.PlanNo) AS NUM_EVENT_PLANS

FROM EventRequest ER, EventPlan EP

WHERE (ER.EventNo = EP.EventNo)

AND (DateHeld BETWEEN '1-Dec-2013' AND '31-Dec-2013')

GROUP BY ER.EventNo, DateHeld

HAVING COUNT(EP.PlanNo) > 1;

-- 3)

SELECT PlanNo, EP.EventNo, WorkDate, Activity

FROM EventPlan EP, EventRequest ER, Facility F

WHERE EP.EventNo = ER.EventNo AND ER.FacNo = F.FacNo AND WorkDate BETWEEN '1-Dec-2013' AND '31-Dec-2013' AND FacName = 'Basketball arena';

-- 4)

SELECT EP.EventNo, DateHeld, Status, EstCost

FROM EventRequest ER, EventPlan EP, Employee E, Facility F

WHERE EP.EventNo = ER.EventNo AND EP.EmpNo = E.EmpNo AND ER.FacNo = F.FacNo AND DateHeld BETWEEN '1-Oct-2013' AND '31-Dec-2013' AND FacName = 'Basketball arena' AND EmpName = 'Mary Manager';

-- 5)

SELECT EPL.PlanNo, LineNo, ResName, NumberFld, LocName, TimeStart, TimeEnd

FROM EventPlanLine PL, ResourceTbl R, Location L, Facility F, EventPlan EP

WHERE EPL.ResNo = R.ResNo AND EPL.LocNo = L.LocNo AND EPL.PlanNo = EP.PlanNo AND L.FacNo = F.FacNo AND FacName = 'Basketball arena' AND Activity = 'Operation' AND WorkDate BETWEEN '1-Oct-2013' AND '31-Dec-2013';

Related Questions

Navigate

• Browse (All)
• Browse U
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.