|
SQL>
SQL> CREATE TABLE rooms (
2 room_id NUMBER(5) PRIMARY KEY,
3 building VARCHAR2(15),
4 room_number NUMBER(4),
5 number_seats NUMBER(4),
6 description VARCHAR2(50)
7 );
Table created.
SQL>
SQL> INSERT INTO rooms (room_id, building, room_number, number_seats, description)
2 VALUES (20001, 'Building 7', 201, 1000, 'Large Lecture Hall');
1 row created.
SQL>
SQL> INSERT INTO rooms (room_id, building, room_number, number_seats, description)
2 VALUES (20002, 'Building 6', 101, 500, 'Small Lecture Hall');
1 row created.
SQL>
SQL> INSERT INTO rooms (room_id, building, room_number, number_seats, description)
2 VALUES (20003, 'Building 6', 150, 50, 'Discussion Room A');
1 row created.
SQL>
SQL> INSERT INTO rooms (room_id, building, room_number, number_seats, description)
2 VALUES (20004, 'Building 6', 160, 50, 'Discussion Room B');
1 row created.
SQL>
SQL> INSERT INTO rooms (room_id, building, room_number, number_seats,description)
2 VALUES (20005, 'Building 6', 170, 50, 'Discussion Room C');
1 row created.
SQL>
SQL> INSERT INTO rooms (room_id, building, room_number, number_seats, description)
2 VALUES (20006, 'Music Building', 100, 10, 'Music Practice Room');
1 row created.
SQL>
SQL> INSERT INTO rooms (room_id, building, room_number, number_seats, description)
2 VALUES (20007, 'Music Building', 200, 1000, 'Concert Room');
1 row created.
SQL>
SQL> INSERT INTO rooms (room_id, building, room_number, number_seats, description)
2 VALUES (20008, 'Building 7', 300, 75, 'Discussion Room D');
1 row created.
SQL>
SQL> INSERT INTO rooms (room_id, building, room_number, number_seats,description)
2 VALUES (20009, 'Building 7', 310, 50, 'Discussion Room E');
1 row created.
SQL>
SQL>
SQL> select * from rooms;
ROOM_ID BUILDING ROOM_NUMBER NUMBER_SEATS DESCRIPTION
-------- --------------- ----------- ------------ --------------------------------------------------
######## Building 7 201.00 1000.00 Large Lecture Hall
######## Building 6 101.00 500.00 Small Lecture Hall
######## Building 6 150.00 50.00 Discussion Room A
######## Building 6 160.00 50.00 Discussion Room B
######## Building 6 170.00 50.00 Discussion Room C
######## Music Building 100.00 10.00 Music Practice Room
######## Music Building 200.00 1000.00 Concert Room
######## Building 7 300.00 75.00 Discussion Room D
######## Building 7 310.00 50.00 Discussion Room E
9 rows selected.
SQL>
SQL> DECLARE
2 v_NumberSeats rooms.number_seats%TYPE;
3 v_Comment VARCHAR2(35);
4 BEGIN
5 SELECT number_seats
6 INTO v_NumberSeats
7 FROM rooms
8 WHERE room_id = 20008;
9 IF v_NumberSeats < 50 THEN
10 v_Comment := 'Fairly small';
11 ELSIF v_NumberSeats < 100 THEN
12 v_Comment := 'A little bigger';
13 ELSE
14 v_Comment := 'Lots of room';
15 END IF;
16 END;
17 /
PL/SQL procedure successfully completed.
SQL>
SQL> select * from rooms;
ROOM_ID BUILDING ROOM_NUMBER NUMBER_SEATS DESCRIPTION
-------- --------------- ----------- ------------ --------------------------------------------------
######## Building 7 201.00 1000.00 Large Lecture Hall
######## Building 6 101.00 500.00 Small Lecture Hall
######## Building 6 150.00 50.00 Discussion Room A
######## Building 6 160.00 50.00 Discussion Room B
######## Building 6 170.00 50.00 Discussion Room C
######## Music Building 100.00 10.00 Music Practice Room
######## Music Building 200.00 1000.00 Concert Room
######## Building 7 300.00 75.00 Discussion Room D
######## Building 7 310.00 50.00 Discussion Room E
9 rows selected.
SQL>
SQL>
SQL> drop table rooms;
Table dropped.
SQL>
|
