Here is the table
SQL> SQL> drop table registrations; Table dropped.-- from w w w . j av a 2 s. c o m SQL> create table registrations( 2 attendee NUMBER(4) not null, 3 course VARCHAR2(6) , 4 begindate DATE not null, 5 evaluation NUMBER(1) check (evaluation in (1,2,3,4,5))) ; SQL> SQL> insert into registrations values (7002,'SQL',date '1999-04-12',4 ); SQL> insert into registrations values (7934,'SQL',date '1999-04-12',5 ); SQL> insert into registrations values (7006,'SQL',date '1999-04-12',4 ); SQL> insert into registrations values (7011,'SQL',date '1999-04-12',2 ); SQL> insert into registrations values (7008,'SQL',date '1999-10-04',NULL); SQL> insert into registrations values (7009,'SQL',date '1999-10-04',3 ); SQL> insert into registrations values (7902,'SQL',date '1999-10-04',4 ); SQL> insert into registrations values (7902,'SQL',date '1999-12-13',NULL); SQL> insert into registrations values (7006,'SQL',date '1999-12-13',NULL); SQL> insert into registrations values (7003,'JSON',date '1999-08-10',4 ); SQL> insert into registrations values (7012,'JSON',date '1999-08-10',4 ); SQL> insert into registrations values (7902,'JSON',date '1999-08-10',5 ); SQL> insert into registrations values (7010,'JSON',date '2000-09-27',5 ); SQL> insert into registrations values (7002,'JAVA',date '1999-12-13',2 ); SQL> insert into registrations values (7007,'JAVA',date '1999-12-13',NULL ); SQL> insert into registrations values (7011,'JAVA',date '1999-12-13',5 ); SQL> insert into registrations values (7008,'JAVA',date '1999-12-13',5 ); SQL> insert into registrations values (7009,'JAVA',date '1999-12-13',4 ); SQL> insert into registrations values (7004,'JAVA',date '2000-02-01',3 ); SQL> insert into registrations values (7008,'JAVA',date '2000-02-01',4 ); SQL> insert into registrations values (7006,'JAVA',date '2000-02-01',5 ); SQL> insert into registrations values (7012,'XML',date '2000-02-03',4 ); SQL> insert into registrations values (7002,'XML',date '2000-02-03',5 ); SQL> insert into registrations values (7004,'PLS',date '2000-09-11',NULL); SQL> insert into registrations values (7002,'PLS',date '2000-09-11',NULL); SQL> insert into registrations values (7011,'PLS',date '2000-09-11',NULL); SQL>
Write SQL to list the course code, begin date, and the number of registrations for all course offerings in 1999 with at least three registrations.
SQL> SQL> select course 2 , begindate-- from w ww .j av a2 s. co m 3 , count(*) 4 from registrations 5 where extract(year from begindate) = 1999 6 group by course 7 , begindate 8 having count(*) >= 3; COURSE BEGINDATE COUNT(*) ------ --------- ---------- JAVA 13-DEC-99 5 JSON 10-AUG-99 3 SQL 12-APR-99 4 SQL 04-OCT-99 3 SQL>