|
SQL>
SQL> CREATE TABLE server_usage (
2 pro_id NUMBER(4),
3 emp_id NUMBER,
4 time_log_date DATE,
5 hours_logged NUMBER(8,2),
6 dollars_charged NUMBER(8,2),
7 CONSTRAINT server_usage_pk PRIMARY KEY (pro_id, emp_id, time_log_date)
8 );
Table created.
SQL> INSERT INTO server_usage(pro_id, emp_id, time_log_date, hours_logged, dollars_charged)
2 VALUES (1001,101,to_date('4-Apr-2004','dd-mon-yyyy'),1123,222);
1 row created.
SQL> INSERT INTO server_usage(pro_id, emp_id, time_log_date, hours_logged, dollars_charged)
2 VALUES (1002,102,to_date('4-Apr-2005','dd-mon-yyyy'),1124,223);
1 row created.
SQL> INSERT INTO server_usage(pro_id, emp_id, time_log_date, hours_logged, dollars_charged)
2 VALUES (1003,103,to_date('4-Apr-2006','dd-mon-yyyy'),1125,224);
1 row created.
SQL> INSERT INTO server_usage(pro_id, emp_id, time_log_date, hours_logged, dollars_charged)
2 VALUES (1004,104,to_date('4-Apr-2007','dd-mon-yyyy'),1126,225);
1 row created.
SQL> INSERT INTO server_usage(pro_id, emp_id, time_log_date, hours_logged, dollars_charged)
2 VALUES (1005,105,to_date('4-Apr-2008','dd-mon-yyyy'),1127,226);
1 row created.
SQL> INSERT INTO server_usage(pro_id, emp_id, time_log_date, hours_logged, dollars_charged)
2 VALUES (1001,106,to_date('4-Apr-2009','dd-mon-yyyy'),1128,227);
1 row created.
SQL> INSERT INTO server_usage(pro_id, emp_id, time_log_date, hours_logged, dollars_charged)
2 VALUES (1002,107,to_date('4-Apr-2010','dd-mon-yyyy'),1129,228);
1 row created.
SQL>
SQL> SET ECHO ON
SQL> SELECT emp_id, pro_id
2 FROM server_usage
3 GROUP BY emp_id, pro_id
4 HAVING (pro_id = 1001 OR pro_id=1002)
5 AND SUM(hours_logged) > 20;
101 1001
102 1002
106 1001
107 1002
4 rows selected.
SQL>
SQL> drop table server_usage;
Table dropped.
|