Submitted by : Preetha Rajan

Email: praj016@aucklanduni.ac.nz

library(DBI)
## Warning: package 'DBI' was built under R version 3.5.2
library(RMySQL)
## Warning: package 'RMySQL' was built under R version 3.5.2
drv <- dbDriver("MySQL")
conn <- dbConnect(drv, dbname= "athletics", username="root", password="password")

Practice Problems

Question 1 List the event number, date held, customer number, customer name, facility number, and facility name of 2018 events placed by Boulder customers

SELECT eventrequest.eventno, eventrequest.dateheld, eventrequest.custno, customer.custname, facility.facno, facility.facname

FROM eventrequest, customer, facility

WHERE eventrequest.custno = customer.custno AND eventrequest.facno = facility.facno AND customer.city = "Boulder" AND eventrequest.dateheld BETWEEN "2018-01-01" AND "2018-12-31";
7 records
eventno dateheld custno custname facno facname
E100 2018-10-25 C100 Football F100 Football Stadium
E101 2018-10-26 C100 Football F100 Football Stadium
E102 2018-09-14 C100 Football F100 Football Stadium
E103 2018-09-21 C100 Football F100 Football Stadium
E104 2018-12-03 C101 Men“s Basketball F101 Basketball arena
E105 2018-12-05 C101 Men“s Basketball F101 Basketball arena
E106 2018-12-12 C101 Men“s Basketball F101 Basketball arena 
SELECT eventrequest.eventno, eventrequest.dateheld, eventrequest.custno, customer.custname, facility.facno, facility.facname

FROM eventrequest INNER JOIN customer ON eventrequest.custno = customer.custno INNER JOIN facility ON eventrequest.facno = facility.facno

WHERE customer.city = "Boulder" AND eventrequest.dateheld BETWEEN "2018-01-01" AND "2018-12-31";
7 records
eventno dateheld custno custname facno facname
E100 2018-10-25 C100 Football F100 Football Stadium
E101 2018-10-26 C100 Football F100 Football Stadium
E102 2018-09-14 C100 Football F100 Football Stadium
E103 2018-09-21 C100 Football F100 Football Stadium
E104 2018-12-03 C101 Men“s Basketball F101 Basketball arena
E105 2018-12-05 C101 Men“s Basketball F101 Basketball arena
E106 2018-12-12 C101 Men“s Basketball F101 Basketball arena

Question 2 List the customer number, customer name, event number, date held, facility number, facility name, and estimated audience cost per person (EstCost / EstAudience) for events held on 2018, in which the estimated cost per person is less than $0.2

SELECT eventrequest.eventno, eventrequest.dateheld, eventrequest.custno, customer.custname, facility.facno, facility.facname, (eventrequest.estcost/eventrequest.estaudience) AS estimated_audience_cost

FROM eventrequest, customer, facility

WHERE eventrequest.custno = customer.custno AND eventrequest.facno = facility.facno AND eventrequest.dateheld BETWEEN "2018-01-01" AND "2018-12-31" AND (eventrequest.estcost/eventrequest.estaudience) <0.2;
5 records
eventno dateheld custno custname facno facname estimated_audience_cost
E100 2018-10-25 C100 Football F100 Football Stadium 0.062500
E101 2018-10-26 C100 Football F100 Football Stadium 0.062500
E102 2018-09-14 C100 Football F100 Football Stadium 0.062500
E103 2018-09-21 C100 Football F100 Football Stadium 0.062500
E104 2018-12-03 C101 Men“s Basketball F101 Basketball arena 0.166667 
SELECT eventrequest.eventno, eventrequest.dateheld, eventrequest.custno, customer.custname, facility.facno, facility.facname, (eventrequest.estcost/eventrequest.estaudience) AS estimated_audience_cost

FROM eventrequest INNER JOIN customer ON eventrequest.custno = customer.custno INNER JOIN facility ON eventrequest.facno = facility.facno

WHERE eventrequest.dateheld BETWEEN "2018-01-01" AND "2018-12-31" AND (eventrequest.estcost/eventrequest.estaudience) <0.2;
5 records
eventno dateheld custno custname facno facname estimated_audience_cost
E100 2018-10-25 C100 Football F100 Football Stadium 0.062500
E101 2018-10-26 C100 Football F100 Football Stadium 0.062500
E102 2018-09-14 C100 Football F100 Football Stadium 0.062500
E103 2018-09-21 C100 Football F100 Football Stadium 0.062500
E104 2018-12-03 C101 Men“s Basketball F101 Basketball arena 0.166667

Question 3 List the customer number, customer name, and total estimated costs for Approved events. The total amount of events is the sum of the estimated cost for each event. Group the results by customer number and customer name. 

SELECT eventrequest.custno, customer.custname, sum(eventrequest.estcost) AS estimated_cost

FROM eventrequest, customer

WHERE eventrequest.custno = customer.custno AND status = "Approved"

GROUP BY eventrequest.custno, customer.custname;
2 records
custno custname estimated_cost
C100 Football 15000
C101 Men“s Basketball 6000 
SELECT eventrequest.custno, customer.custname, sum(eventrequest.estcost) AS estimated_cost

FROM eventrequest INNER JOIN customer ON eventrequest.custno = customer.custno

WHERE status = "Approved"

GROUP BY eventrequest.custno, customer.custname;
2 records
custno custname estimated_cost
C100 Football 15000
C101 Men“s Basketball 6000

Question 4 Insert yourself as a new row in the Customer table.

INSERT INTO Customer(custno, Custname, address, Internal, contact, phone, city, state, zip)

VALUES('C106', 'Throwball', 'Box 352299', 'Y', 'Kathy Referee', 6859100, 'Boulder', 'CO', 80609);
SELECT *
FROM Customer;
6 records
custno Custname address Internal contact phone city state zip
C100 Football Box 352200 Y Mary Manager 6857100 Boulder CO 80309
C101 Men“s Basketball Box 352400 Y Sally Supervisor 5431700 Boulder CO 80309
C103 Baseball Box 352020 Y Bill Baseball 5431234 Boulder CO 80309
C104 Women“s Softball Box 351200 Y Sue Softball 5434321 Boulder CO 80309
C105 High School Football 123 Any Street N Coach Bob 4441234 Louiville CO 80027
C106 Throwball Box 352299 Y Kathy Referee 6859100 Boulder CO 80609

Question 5 Increase the rate by 10 percent of nurse resource in the resource table

SELECT rate, (rate*1.1) AS increased_rate, resname

FROM resourcetbl

WHERE resname = "Nurse";
1 records
rate increased_rate resname
22 24.2 Nurse

Question 6 Delete the new row added to the Customer table.

DELETE FROM Customer

WHERE custno = "C106";

Graded Problems

SELECT eventrequest.eventno, eventrequest.dateheld, count(eventplanline.planno) AS number_of_event_plan_lines, eventplan.workdate

FROM eventrequest, eventplan, eventplanline

WHERE eventrequest.eventno = eventplan.eventno AND eventplan.planno = eventplanline.planno AND eventplan.workdate BETWEEN "2018-12-01" AND "2018-12-31"

GROUP BY eventplanline.planno

HAVING count(eventplanline.planno) >1;
3 records
eventno dateheld number_of_event_plan_lines workdate
E104 2018-12-03 3 2018-12-03
E105 2018-12-05 3 2018-12-05
E106 2018-12-12 3 2018-12-12 
SELECT eventrequest.eventno, eventrequest.dateheld, count(eventplanline.planno) AS number_of_event_plan_lines, eventplan.workdate

FROM eventrequest INNER JOIN eventplan ON eventrequest.eventno = eventplan.eventno INNER JOIN eventplanline ON eventplan.planno = eventplanline.planno

WHERE eventplan.workdate BETWEEN "2018-12-01" AND "2018-12-31"

GROUP BY eventplanline.planno

HAVING count(eventplanline.planno) >1;
3 records
eventno dateheld number_of_event_plan_lines workdate
E104 2018-12-03 3 2018-12-03
E105 2018-12-05 3 2018-12-05
E106 2018-12-12 3 2018-12-12

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

SELECT eventplan.planno, eventreq.eventno, eventplan.workdate, eventplan.activity, facility.facname

FROM eventplan, eventreq, facility

WHERE eventreq.eventno = eventplan.eventno AND eventreq.facno = facility.facno AND eventplan.workdate BETWEEN "2018-12-01" AND "2018-12-31" AND facility.facname = "Basketball arena";
4 records
planno eventno workdate activity facname
P101 E104 2018-12-03 Operation Basketball arena
P102 E105 2018-12-05 Operation Basketball arena
P103 E106 2018-12-12 Operation Basketball arena
P349 E106 2018-12-12 Cleanup Basketball arena 
SELECT eventplan.planno, eventreq.eventno, eventplan.workdate, eventplan.activity, facility.facname

FROM eventplan INNER JOIN eventreq ON eventreq.eventno = eventplan.eventno INNER JOIN facility ON eventreq.facno = facility.facno

WHERE eventplan.workdate BETWEEN "2018-12-01" AND "2018-12-31" AND facility.facname = "Basketball arena";
4 records
planno eventno workdate activity facname
P101 E104 2018-12-03 Operation Basketball arena
P102 E105 2018-12-05 Operation Basketball arena
P103 E106 2018-12-12 Operation Basketball arena
P349 E106 2018-12-12 Cleanup Basketball arena

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

SELECT eventrequest.eventno, eventrequest.dateheld, eventrequest.status, eventrequest.estcost, customer.contact, facility.facname

FROM eventrequest, customer, facility

WHERE eventrequest.custno = customer.custno AND eventrequest.facno = facility.facno AND customer.contact = "Mary Manager" AND eventrequest.dateheld BETWEEN "2018-10-01" AND "2018-12-31" AND facility.facname = "Basketball arena";

Table: 0 records

eventno dateheld status estcost contact facname ——– ——— ——- ——– ——– ——–

SELECT eventrequest.eventno, eventrequest.dateheld, eventrequest.status, eventrequest.estcost, customer.contact, facility.facname

FROM eventrequest INNER JOIN customer ON eventrequest.custno = customer.custno INNER JOIN facility ON eventrequest.facno = facility.facno 

WHERE customer.contact = "Mary Manager" AND eventrequest.dateheld BETWEEN "2018-10-01" AND "2018-12-31" AND facility.facname = "Basketball arena";

Table: 0 records

eventno dateheld status estcost contact facname ——– ——— ——- ——– ——– ——–

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

SELECT eventplanline.planno, eventplanline.lineno, resourcetbl.resname, eventplanline.numberfld, location.locname, eventplanline.timestart, eventplanline.timeend, eventplan.activity, eventplan.workdate, facility.facname

FROM eventplanline, eventplan, resourcetbl, location, facility

WHERE eventplanline.resno = resourcetbl.resno AND eventplanline.planno = eventplan.planno AND eventplanline.locno = location.locno AND location.facno = facility.facno AND eventplan.activity = "Operation" AND eventplan.workdate BETWEEN "2018-10-01" AND "2018-12-31" AND facility.facname = "Basketball arena";
9 records
planno lineno resname numberfld locname timestart timeend activity workdate facname
P101 1 Attendant 2 Locker room 18:00:00 20:00:00 Operation 2018-12-03 Basketball arena
P101 3 Nurse 2 Locker room 19:00:00 20:00:00 Operation 2018-12-03 Basketball arena
P102 1 Attendant 2 Locker room 18:00:00 19:00:00 Operation 2018-12-05 Basketball arena
P102 3 Nurse 2 Locker room 19:00:00 22:00:00 Operation 2018-12-05 Basketball arena
P103 1 Attendant 2 Locker room 18:00:00 21:00:00 Operation 2018-12-12 Basketball arena
P103 3 Nurse 2 Locker room 19:00:00 22:00:00 Operation 2018-12-12 Basketball arena
P101 2 Attendant 4 Gate 18:30:00 19:00:00 Operation 2018-12-03 Basketball arena
P102 2 Attendant 4 Gate 18:00:00 21:00:00 Operation 2018-12-05 Basketball arena
P103 2 Attendant 4 Gate 18:00:00 21:00:00 Operation 2018-12-12 Basketball arena