SQL Queries For the below schema for a company door: doorlog(eventid,doorid,tim,

Question

SQL Queries

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):

How many users entered through doorid=1

If doorid=2 is bathroom, how many people are currently in the bathroom?

If doorid=1 is front entrance door, and doorid=3 is back entrance door, and these are the only doors in the building, how many people are currently in the building?

How many people were in the building on July 4th, at 10PM? (watching fireworks)

If doorid=7 is for floor 42, what's the daily occupancy of floor 42 for entire 2017 (give a number for every day in 2017)

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

What percentage of the people work on floor 42?

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

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

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

Explanation / Answer

Qu. 1) How many users entered through doorid=1

Ans ) As per the SQL query we have to use count() method
select count(*) from doorlog where doorid='1';
Here we need not to add condition for event column, because we need the total number of users who entered from doorid 1. Even if they exited, then still we need to count those rows.


Qu 2) If doorid=2 is bathroom, how many people are currently in the bathroom?

Ans ) select count(*) from doorlog where doorid=2 and event='E';
So above query first filter all the rows having doorid 2 and it will only take enter event from event column

Qu 3)If doorid=1 is front entrance door, and doorid=3 is back entrance door, and these are the only doors in the building, how many people are currently in the building?

Ans ) select count(*) from doorlog where event='E' and (doorid=1 or doorid=3 );

Qu 4) How many people were in the building on July 4th, at 10PM?
Ans) select count(*) from doorlog where event='E' and tim in ('4thjulyStartDateInMillis' , '4thJuly10PMInMillis');
where 4thjulyStartDateInMillis is the timestamp in millisseconds of 4th July and 4thJuly10PMInMillis is the milliseconds at 10PM.

Qu 5) If doorid=7 is for floor 42, what's the daily occupancy of floor 42 for entire 2017 (give a number for every day in 2017).

Ans) select tim, count(doorid) from doorlog where doorid=7 and tim in ('1JAN_2017', '31DEC_2017') group by tim;

Related Questions

Navigate

• Browse (All)
• Browse S
• Subjects

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