Database Queries

 You will write SELECT statements that retrieve some information from the following populated tables. This assignment uses intercollegiate athletic database and builds on what you have already created during your course work.

Event Plan Table

EventPlan Line

    

Event Request Table

Resource Table

Facility Table

                                                        facno             Facname

                                                        F100              Football stadium

                                                        F101              Basketball arena

                                                        F102              Baseball field

                                                        F103              Recreation room

Location Table

                                                        locno             facno             locname

                                                        L100              F100              Locker room

                                                        L101              F100              Plaza

                                                        L102              F100              Vehicle gate

                                                        L103              F101              Locker room

                                                        L104              F100              Ticket Booth

                                                        L105              F101              Gate

                                                        L106              F100              Pedestrian gate

a. 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. Thus, the WHERE clause should not have conditions involving the facility number or employee number compared to constant values.

Query Formulation + Result Snapshot

b. List the plan number, line number, resource name, number of resources

( eventplanline.number ), 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. Instead, you should use a condition on the FacName column for the value of “Basketball arena”.

c. Insert a new row into the Facility table with facility name “Swimming Pool”.

d. Insert a new row in the Location table related to the Facility row in modification problem (c). The new row should have “Door” for the location name.

e. Insert a new row in the Location table related to the Facility row in modification problem (c). The new row should have “Locker Room” for the location name.

f. Change the location name of “Door” to “Gate” for the row inserted in modification problem (d).

g. List the city, state, and zip codes in the customer table. Your result should not have duplicates.

Our Official Website : Web Conquerors (https://www.webconquerors.com/) 

Want to get digital services? Contact US

Want to know about our services? Our Services