Download GROUP BY AND HAVING COMMANDS and more Lab Reports Database Management Systems (DBMS) in PDF only on Docsity!
NUST
Military College of Signals,
Rawalpindi.
DATABASE SYSTEM
LAB#
BATCH-26C
Submitted By:
AYMEN FATIMA
CMS ID:
Submitted To:
Sir Kabeer Ahmed
DEPARTMENT OF COMPUTER SOFTWARE ENGINEERING.
MILITARY COLLEGE OF SIGNALS.
EXPERIMENT 5 – Group by and Having
Lab# 5 EXCERCISE
Problem 5.1:
Display activity wise number of Event planed.
SELECT ACTIVITY, count (*) FROM EVENTPLAN GROUP BY ACTIVITY
Problem 5.2:
Display month wise number of Events Requested in year 2018
SELECT DATEREQ, count (*) FROM EVENTREQUEST GROUP BY DATEREQ HAVING DATEREQ like '%2018'
Problem 5.3:
Count and display the total number of employees in each department and make sure department has
greater than 5 employees
SELECT DEPARTMENT, count () FROM EMPLOYEE GROUP BY DEPARTMENT having count ()> 5
Problem 5.4:
Display status wise number of Events Requested and estimated cost is greater than equal to
SELECT STATUS, count (*) FROM EVENTREQUEST GROUP BY STATUS, ESTCOST having ESTCOST> 5000
Problem 5.5:
List event requested, which has highest number of audience in each month of 2018
SELECT DATEREQ, max (ESTAUDIENCE) FROM EVENTREQUEST GROUP BY DATEREQ having DATEREQ like '%2018' SELECT DATEREQ, max (ESTAUDIENCE) FROM EVENTREQUEST where DATEREQ like '%2018' GROUP BY DATEREQ
Problem 5.6 :
Display all the details of employee, whose name does start with ‘A’ using LIKE operator.
EventPlanLine
EventPlan
Primary and Foreign Keys
The primary and foreign keys are depicted in Figure 1. An event request is related to many (one or
more) event plans but only one customer. An event plan contains many event plan lines but only one supervising employee. An event plan line references a resource and location. A facility has
- P100 1 8:00 17:00 2 L100 R PlanNo LineNo TimeStart TimeEnd NumberFld LocNo ResNo
- P100 2 12:00 17:00 2 L101 R
- P100 3 7:00 16:30 1 L102 R
- P100 4 18:00 22:00 2 L100 R
- P101 1 18:00 20:00 2 L103 R
- P101 2 18:30 19:00 4 L105 R
- P101 3 19:00 20:00 2 L103 R
- P102 1 18:00 19:00 2 L103 R
- P102 2 18:00 21:00 4 L105 R
- P102 3 19:00 22:00 2 L103 R
- P103 1 18:00 21:00 2 L103 R
- P103 2 18:00 21:00 4 L105 R
- P103 3 19:00 22:00 2 L103 R
- P104 1 18:00 22:00 4 L101 R
- P104 2 18:00 22:00 4 L100 R
- P105 1 18:00 22:00 4 L101 R
- P105 2 18:00 22:00 4 L100 R
- P199 1 8:00 12:00 1 L100 R
- P349 1 12:00 15:30 1 L103 R
- P85 1 9:00 17:00 5 L100 R
- P85 2 8:00 17:00 2 L102 R
- P85 3 10:00 15:00 3 L104 R
- P95 1 8:00 17:00 4 L100 R
- P95 2 9:00 17:00 4 L102 R
- P95 3 10:00 15:00 4 L106 R
- P95 4 13:00 17:00 2 L100 R
- P95 5 13:00 17:00 2 L101 R
- P100 E100 25-Oct-2018 Standard operation Operation E planno eventno workdate notes activity empno
- P101 E104 03-Dec-2018 Watch for gate crashers Operation E
- P102 E105 05-Dec-2018 Standard operation Operation E
- P104 E101 26-Oct-2018 Standard cleanup Cleanup E P103 E106 12-Dec-2018 Watch for seat switching Operation
- P105 E100 25-Oct-2018 Light cleanup Cleanup E
- P199 E102 10-Dec-2018 Standard operation Operation E
- P299 E101 26-Oct-2018 Operation E
- P349 E106 12-Dec-2018 Cleanup E
- P85 E100 25-Oct-2018 Standard operation Setup E
- P95 E101 26-Oct-2018 Extra security Setup E
Figure 1: Oracle Relational Database Diagram for the Intercollegiate Athletic Database
