Using SQL, answer these questions (write a SQL query that answers these question

Question

Using SQL, answer these questions (write a SQL query that answers these questions): For the below schema for a company door: doorlog(eventid,doorid,tim,username,event) Where doorid represents the door for this event. e.g. Front door may be doorid=1, and bathroom may be doorid=2, etc. tim is timestamp, username is the user who is opening or closing the door. event is "E" for entry, and "X" for exit. Using SQL, answer these questions (write a SQL query that answers these questions):

1. What is the daily average (and standard deveation) occupancy of floor 42 for 2017? (single number)

2. What percentage of the people work on floor 42?

3. What's the average number of times per day that people use the bathroom? (bathroom is doorid=2).

4. What percentage of employees stayed after 5:15PM on July 3rd, 2017?

5. List all employees who left work before 1PM on July 3rd, 2017.

Explanation / Answer

1. daily average occupancy of floor 42 for 2017

SELECT Day, AVG(CountPerDay) AS AvgPerDay
FROM (
SELECT Date(tim) AS Day, COUNT(eventid) AS CountPerDay
FROM Doorlog
WHERE event='E' and doorid=1 and Floor=42 and YEAR(tim)=2017
GROUP BY Date(tim)
) AS CountSummary
GROUP BY Day

2. percentage of the people work on floor 42

SELECT o.floor,count(o.eventid) / (
SELECT Count(eventid) AS total
FROM Doorlog WHERE Event='E' and doorid=1
) * 100 AS Percentage
FROM Doorlog o
WHERE o.Floor=42
GROUP BY o.Floor

3.average number of times per day that people use the bathroom

SELECT Day, AVG(CountPerDay) AS AvgPerDay
FROM (
SELECT Date(tim) AS Day, COUNT(eventid) AS CountPerDay
FROM Doorlog
WHERE event='E' and doorid=2
GROUP BY Date(tim)
) AS CountSummary
GROUP BY Day

4.percentage of employees stayed after 5:15PM on July 3rd, 2017

SELECT o.floor,count(o.eventid) / (
SELECT Count(eventid) AS total
FROM Doorlog WHERE Event='X' and doorid=1
) * 100 AS Percentage
FROM Doorlog o
WHERE tim >=TIMESTAMP("2017-07-03", "17:15:00") and Event='X' and doorid=1
GROUP BY o.Floor

5.all employees who left work before 1PM on July 3rd, 2017

SELECT Username
FROM Doorlog
WHERE tim < TIMESTAMP("2017-07-03", "13:00:00") and Event='X' and doorid=1

Please comments

Related Questions

Navigate

• Browse (All)
• Browse U
• Subjects

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