🚤 Oracle 11g SQL Practical (EDITED BY DISHA SARDAR)
Boat – Sailor – Reserves Database
🚤 BOAT
Stores boat information such as ID, name and color.
⛵ SAILOR
Stores sailor ID, name and rating.
📅 RESERVES
Stores sailor boat reservations and days.
1️⃣ CREATE TABLE Commands
CREATE TABLE boat(
bid VARCHAR2(10) PRIMARY KEY,
bname VARCHAR2(10),
color VARCHAR2(10)
);
CREATE TABLE sailor(
sid VARCHAR2(10) PRIMARY KEY,
sname VARCHAR2(10),
rating NUMBER
);
CREATE TABLE reserves(
sid VARCHAR2(10) REFERENCES sailor,
bid VARCHAR2(10) REFERENCES boat,
day VARCHAR2(10)
);
2️⃣ Insert Records into BOAT
INSERT INTO boat VALUES('001','titanic','red');
INSERT INTO boat VALUES('002','gulliver','green');
INSERT INTO boat VALUES('003','crusoe','red');
INSERT INTO boat VALUES('004','shanok','green');
INSERT INTO boat VALUES('005','paradise','yellow');
OUTPUT SELECT * FROM BOAT;
| BID | BNAME | COLOR |
|---|---|---|
| 001 | titanic | red |
| 002 | gulliver | green |
| 003 | crusoe | red |
| 004 | shanok | green |
| 005 | paradise | yellow |
3️⃣ Insert Records into SAILOR
INSERT INTO sailor VALUES('A01','varshan',6);
INSERT INTO sailor VALUES('A02','ashutosh',7);
INSERT INTO sailor VALUES('A03','anindo',5);
INSERT INTO sailor VALUES('A04','akash',9);
INSERT INTO sailor VALUES('A05','ashutosh',8);
OUTPUT SELECT * FROM SAILOR;
| SID | SNAME | RATING |
|---|---|---|
| A01 | varshan | 6 |
| A02 | ashutosh | 7 |
| A03 | anindo | 5 |
| A04 | akash | 9 |
| A05 | ashutosh | 8 |
4️⃣ Insert Records into RESERVES
INSERT INTO reserves VALUES('A01','001','sunday');
INSERT INTO reserves VALUES('A01','003','wednesday');
INSERT INTO reserves VALUES('A01','002','monday');
INSERT INTO reserves VALUES('A01','004','thurshday');
INSERT INTO reserves VALUES('A02','001','monday');
INSERT INTO reserves VALUES('A03','001','tueday');
INSERT INTO reserves VALUES('A03','003','friday');
INSERT INTO reserves VALUES('A04','004','saturday');
INSERT INTO reserves VALUES('A04','001','wednesday');
INSERT INTO reserves VALUES('A05','001','sunday');
OUTPUT SELECT * FROM RESERVES;
| SID | BID | DAY |
|---|---|---|
| A01 | 001 | sunday |
| A01 | 003 | wednesday |
| A01 | 002 | monday |
| A01 | 004 | thurshday |
| A02 | 001 | monday |
| A03 | 001 | tueday |
| A03 | 003 | friday |
| A04 | 004 | saturday |
| A04 | 001 | wednesday |
| A05 | 001 | sunday |
5️⃣ SELECT Commands
SELECT * FROM boat;
SELECT * FROM sailor;
SELECT * FROM reserves;
💡 Oracle 11g Note:
The foreign keys in the RESERVES table reference the primary keys
of the SAILOR and BOAT tables. Therefore, the referenced records
must exist before inserting the corresponding reservation.
No comments:
Post a Comment