SQL>
SQL> CREATE TABLE BookBorrow(
2 member CHAR(5)
3 ,book CHAR(5)
4 ,dueDate DATE
5 );
Table created.
SQL> INSERT INTO BookBorrow VALUES ('bb01','bk002',DATE '2005-03-22');
1 row created.
SQL> INSERT INTO BookBorrow VALUES ('bb01','bk002',DATE '2005-09-21');
1 row created.
SQL> INSERT INTO BookBorrow VALUES ('bb02','bk005',DATE '2005-09-21');
1 row created.
SQL> INSERT INTO BookBorrow(member,book,dueDate)VALUES ('bb01','bk002',CURRENT_DATE + INTERVAL '14' DAY );
1 row created.
SQL> INSERT INTO BookBorrow(member,book,dueDate)VALUES ('bb02','bk005',CURRENT_DATE - INTERVAL '1' DAY );
1 row created.
SQL>
SQL>
SQL> CREATE TABLE BookReturn(
2 book CHAR(5)
3 ,member CHAR(5)
4 ,returnDate DATE
5 ,fine DECIMAL(10,2)
6 );
Table created.
SQL>
SQL> CREATE TABLE room(
2 who VARCHAR(20)
3 ,wht VARCHAR(20) NOT NULL
4 ,whn DATE NOT NULL
5 ,PRIMARY KEY (whn,wht)
6 );
Table created.
SQL>
SQL> INSERT INTO BookReturn(book,member,returnDate,fine)
2 VALUES ('bb01','bk002',CURRENT_DATE,(SELECT 0.20 FROM BookBorrow
3 WHERE member='bb01' AND book='bk002'
4 HAVING MAX(dueDate) < CURRENT_DATE));
1 row created.
SQL>
SQL> INSERT INTO BookReturn(book,member,returnDate,fine)
2 VALUES ('bb02','bk005',CURRENT_DATE,(SELECT 0.20 FROM BookBorrow
3 WHERE member='bb02' AND book='bk005'
4 HAVING MAX(dueDate) < CURRENT_DATE));
1 row created.
SQL>
SQL> SELECT * FROM BookReturn;
BOOK MEMBE RETURNDAT FINE
----- ----- --------- ----------
bb01 bk002 26-OCT-09
bb02 bk005 26-OCT-09 .2
2 rows selected.
SQL>
SQL> INSERT INTO room(whn,wht,who)
2 SELECT DATE '2006-07-13','Room 1','Prof. Plum'
3 FROM dual
4 WHERE NOT EXISTS (SELECT who FROM room
5 WHERE whn = DATE '2006-07-13'
6 AND wht='Room 1');
1 row created.
SQL>
SQL> INSERT INTO room(whn,wht,who)
2 SELECT DATE '2006-07-13','Ballroom','Miss. Scarlet'
3 FROM dual
4 WHERE NOT EXISTS (SELECT who FROM room
5 WHERE whn = DATE '2006-07-13'
6 AND wht='Ballroom');
1 row created.
SQL>
SQL> SELECT * FROM room;
WHO WHT WHN
-------------------- -------------------- ---------
Prof. Plum Room 1 13-JUL-06
Miss. Scarlet Ballroom 13-JUL-06
2 rows selected.
SQL>
SQL> DROP TABLE BookBorrow;
Table dropped.
SQL> DROP TABLE BookReturn;
Table dropped.
SQL> DROP TABLE room;
Table dropped.
SQL>
SQL>
SQL>