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 customer number, the name, the phone number, and the city of customers.

SELECT custno, custname, phone, city
FROM Customer;
5 records
custno custname phone city
C100 Football 6857100 Boulder
C101 Men“s Basketball 5431700 Boulder
C103 Baseball 5431234 Boulder
C104 Women“s Softball 5434321 Boulder
C105 High School Football 4441234 Louiville

Question 2 List the customer number, the name, the phone number, and the city of customers who reside in Colorado (State is CO)

SELECT custno, custname, phone, city
FROM Customer
WHERE STATE = "CO";
5 records
custno custname phone city
C100 Football 6857100 Boulder
C101 Men“s Basketball 5431700 Boulder
C103 Baseball 5431234 Boulder
C104 Women“s Softball 5434321 Boulder
C105 High School Football 4441234 Louiville

Question 3 List all columns of the EventRequest table for events costing more than $4000. Order the result by the event date (DateHeld)

SELECT *
FROM eventrequest
WHERE estcost>4000
ORDER BY dateheld;
5 records
eventno dateheld datereq facno custno Dateauth status estcost estaudience budno
E102 2018-09-14 2018-07-28 F100 C100 2018-07-31 Approved 5000 80000 B1000
E103 2018-09-21 2018-07-28 F100 C100 2018-08-01 Approved 5000 80000 B1000
E100 2018-10-25 2018-06-06 F100 C100 2018-06-08 Approved 5000 80000 B1000
E101 2018-10-26 2018-07-28 F100 C100 0000-00-00 Pending 5000 80000 B1000
E107 2018-11-23 2018-07-28 F100 C105 2018-07-31 Denied 10000 5000

Question 4 List the event number, the event date (DateHeld), and the estimated audience number with approved status and audience greater than 9000 or with pending status and audience greater than 7000

SELECT eventno, dateheld, estaudience
FROM eventrequest
WHERE (status = "Approved" AND estaudience > 9000) OR (status = "Pending" AND estaudience >7000);
7 records
eventno dateheld estaudience
E100 2018-10-25 80000
E101 2018-10-26 80000
E102 2018-09-14 80000
E103 2018-09-21 80000
E104 2018-12-03 12000
E105 2018-12-05 10000
E106 2018-12-12 10000

Question 5 List the event number, event date (DateHeld), customer number and customer name of events placed in January 2018 by customers from Boulder

SELECT eventno, dateheld, customer.custno, custname
FROM eventrequest, customer
WHERE city = "Boulder" AND eventrequest.custno = customer.custno AND dateheld BETWEEN '2018-01-01' AND '2018-01-31';

Table: 0 records

eventno dateheld custno custname ——– ——— ——- ———

SELECT eventno, dateheld, customer.custno, custname
FROM eventrequest INNER JOIN customer ON eventrequest.custno = customer.custno
WHERE city = "Boulder" AND dateheld BETWEEN '2018-01-01' AND '2018-01-31';

Table: 0 records

eventno dateheld custno custname ——– ——— ——- ———

Question 6 List the average number of resources used (NumberFld) by plan number. Include only location number L100

SELECT avg(numberfld) AS average_resources
FROM eventplanline
WHERE locno = "L100"
GROUP BY planno;
6 records
average_resources
2
4
4
1
5
3

Question 7 List the average number of resources used (NumberFld) by plan number. Only include location number L100. Eliminate plans with less than two event lines containing location number L100.

SELECT avg(numberfld) AS average_resources
FROM eventplanline
WHERE locno = "L100" AND lineno > 2
GROUP BY planno;
2 records
average_resources
2
2

Graded Problems

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

SELECT DISTINCT city, state, zip
FROM customer;
2 records
city state zip
Boulder CO 80309
Louiville CO 80027

Question 2 List the name, department, phone number, and email address of employees with a phone number beginning with “3-”. 

SELECT empname, department, phone, email
FROM employee
WHERE phone LIKE "3-%";
3 records
empname department phone email
Chuck Coordinator Administration 3-1111 chuck@colorado.edu
Sally Supervisor Planning 3-2222 sally@colorado.edu
Alan Administrator Administration 3-3333 alan@colorado.edu

Question 3 List all columns of the resource table with a rate between $10 and $20. Sort the result by rate.

SELECT *
FROM resourcetbl
WHERE rate BETWEEN 10 AND 20
ORDER BY rate;
6 records
resno Resname rate
R100 Attendant 10
R102 Usher 10
R105 food service 10
R101 Police 15
R104 Janitor 15
R103 Nurse 20

Question 4 List the event requests with a status of “Approved” or “Denied” and an authorized date in July 2018. Include the event number, authorization date, and status in the output. 

SELECT eventno, dateauth, status
FROM eventreq
WHERE (status = "Approved" AND dateauth BETWEEN "2018-07-01" AND "2018-07-31") OR (status = "Denied" AND dateauth BETWEEN "2018-07-01" AND "2018-07-31");
4 records
eventno Dateauth status
E102 2018-07-31 Approved
E104 2018-07-31 Approved
E106 2018-07-31 Approved
E107 2018-07-31 Denied

Question 5 List the location number and name of locations that are part of the “Basketball arena”. Your WHERE clause should not have a condition involving the facility number compared to a constant (“F101”). Instead, you should use a condition on the FacName column for the value of “Basketball arena”.

SELECT location.locno, location.facno, facility.facname
FROM location, facility
WHERE location.facno = facility.facno;
7 records
locno facno facname
L100 F100 Football Stadium
L101 F100 Football Stadium
L102 F100 Football Stadium
L104 F100 Football Stadium
L106 F100 Football Stadium
L103 F101 Basketball arena
L105 F101 Basketball arena 
SELECT location.locno, location.facno, facility.facname
FROM location JOIN facility ON location.facno = facility.facno
WHERE facility.facname = "Basketball arena";
2 records
locno facno facname
L103 F101 Basketball arena
L105 F101 Basketball arena

Question 6 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

SELECT planno, count(planno) AS count_of_plan_lines, sum(numberfld) AS sum_of_number_of_resources
FROM eventplanline
GROUP BY planno;
Displaying records 1 - 10
planno count_of_plan_lines sum_of_number_of_resources
P100 4 7
P101 3 8
P102 3 8
P103 3 8
P104 2 8
P105 2 8
P199 1 1
P349 1 1
P85 3 10
P95 5 16