Consider the insurance database given below. The primary keys are underlined and the datatypes are specified.
PERSON (driverid: String, name: String, address: String)
CAR (regno: String, model: String, year: Int)
ACCIDENT (repno: Int, dat: Date, location: String)
OWNS (driverid: String, regno: String)
PARTICIPATED (driverid: String, regno: String, repno: Int, damageamt: Int)
1. Create the above tables by properly specifying the primary keys and foreign keys.
SQL> CREATE TABLE PERSON(
2 DRIVERID VARCHAR(10),
3 NAME VARCHAR(10),
4 ADDRESS VARCHAR(10),
5 PRIMARY KEY(DRIVERID));
Table created.
SQL> CREATE TABLE CAR(
2 REGNO VARCHAR(10),
3 MODEL VARCHAR(10),
4 YEAR INT,
5 PRIMARY KEY(REGNO));
Table created.
SQL>CREATE TABLE ACCIDENT(
2 REPNO INT,
3 DAT DATE,
4 LOCATION VARCHAR(10),
5 PRIMARY KEY(REPNO));
Table created.
SQL>CREATE TABLE OWNS(
2 DRIVERID VARCHAR(10),
3 REGNO VARCHAR(10),
4 PRIMARY KEY(DRIVERID,REGNO),
5 FOREIGN KEY(DRIVERID) REFERENCES PERSON(DRIVERID),
6 FOREIGN KEY(REGNO) REFERENCES CAR(REGNO));
Table created.
SQL> CREATE TABLE PARTICIPATED(
2 DRIVERID VARCHAR(10),
3 REGNO VARCHAR(10),
4 REPNO INT,
5 DAMAGEAMT INT,
6 PRIMARY KEY(DRIVERID,REGNO,REPNO),
7 FOREIGN KEY(DRIVERID) REFERENCES PERSON(DRIVERID),
8 FOREIGN KEY(REGNO) REFERENCES CAR(REGNO),
9 FOREIGN KEY(REPNO) REFERENCES ACCIDENT(REPNO));
Table created.
2. Enter at least 7-8 tuples for each relation.
SQL> INSERT INTO PERSON VALUES('&DRIVERID','&NAME','&ADDRESS');
Enter value for driverid: D1
Enter value for name: JACK
Enter value for address: HBL
old 1: INSERT INTO PERSON VALUES('&DRIVERID','&NAME','&ADDRESS')
new 1: INSERT INTO PERSON VALUES('D1','JACK','HBL')
1 row created.
SQL> INSERT INTO CAR VALUES('®NO','&MODEL',&YEAR);
Enter value for regno: C1
Enter value for model: ALTO
Enter value for year: 2008
old 1: INSERT INTO CAR VALUES('®NO','&MODEL',&YEAR)
new 1: INSERT INTO CAR VALUES('C1','ALTO',2008)
1 row created.
SQL> INSERT INTO ACCIDENT VALUES(&REPNO,'&DAT','&LOCATION');
Enter value for repno: 1
Enter value for dat: 01-JAN-11
Enter value for location: HBL
old 1: INSERT INTO ACCIDENT VALUES(&REPNO,'&DAT','&LOCATION')
new 1: INSERT INTO ACCIDENT VALUES(1,'01-JAN-11','HBL')
1 row created.
SQL> INSERT INTO OWNS VALUES('&DRIVERID','®NO');
Enter value for driverid: D1
Enter value for regno: C2
old 1: INSERT INTO OWNS VALUES('&DRIVERID','®NO')
new 1: INSERT INTO OWNS VALUES('D1','C2')
1 row created.
SQL> INSERT INTO PARTICIPATED VALUES('&DRIVERID','®NO',®NO,&DAMAGEAMT);
Enter value for driverid: D1
Enter value for regno: C6
Enter value for regno: 1
Enter value for damageamt: 20000
old 1: INSERT INTO PARTICIPATED VALUES('&DRIVERID','®NO',®NO,&DAMAGEAMT)
new 1: INSERT INTO PARTICIPATED VALUES('D1','C6',1,20000)
1 row created.
3. Displaying the table contents.
SQL> SELECT * FROM PERSON;
DRIVERID NAME ADDRESS
D1 JACK HBL
D2 JOHN BLG
D3 JAMES BLR
D4 FRED DWD
D5 TINA DWD
SQL> SELECT * FROM CAR;
REGNO MODEL YEAR
C1 ALTO 2008
C2 SANTRO 2009
C3 SWIFT 2008
C4 ALTO 2010
C5 FIGO 2009
C6 FIGO 2008
6 rows selected.
SQL> SELECT * FROM ACCIDENT;
REPNO DAT LOCATION
3 03-MAR-11 DWD
1 01-JAN-11 HBL
2 02-FEB-10 BLR
4 04-APR-10 HBL
5 05-MAY-11 BLR
6 06-JUN-11 BLG
6 rows selected.
SQL> SELECT * FROM OWNS;
DRIVERID REGNO
D1 C2
D1 C4
D1 C6
D2 C1
D2 C3
D2 C5
D3 C6
D4 C2
D5 C1
9 rows selected.
SQL> SELECT * FROM PARTICIPATED;
DRIVERID REGNO REPNO DAMAGEAMT
D1 C6 1 20000
D3 C6 2 10000
D2 C1 3 15000
D5 C1 4 25000
D4 C2 5 25000
D2 C1 6 10000
6 rows selected.
4. Demonstrate how you:
a) Update the damage amount for the car with the specific register number in the accident with report number 2 to Rs.25000.
SQL> UPDATE PARTICIPATED
2 SET DAMAGEAMT=25000
3 WHERE REPNO=2;
1 row updated.
SQL> SELECT * FROM PARTICIPATED;
DRIVERID REGNO REPNO DAMAGEAMT
D1 C6 1 20000
D3 C6 2 25000
D2 C1 3 15000
D5 C1 4 25000
D4 C2 5 25000
D2 C1 6 10000
6 rows selected.
b) Add a new accident to the database.
SQL> INSERT INTO ACCIDENT(REPNO,DAT,LOCATION) VALUES(7,'7-JUL-08','MLR');
1 row created.
SQL> SELECT * FROM ACCIDENT;
REPNO DAT LOCATION
1 01-JAN-11 HBL
2 02-FEB-10 BLR
3 03-MAR-11 DWD
4 04-APR-10 HBL
5 05-MAY-11 BLR
6 06-JUN-11 BLG
7 07-JUL-08 MLR
7 rows selected.
SQL> INSERT INTO PARTICIPATED(DRIVERID,REGNO,REPNO,DAMAGEAMT)
2 VALUES('D1','C4',7,31000);
1 row created.
SQL> SELECT * FROM PARTICIPATED;
DRIVERID REGNO REPNO DAMAGEAMT
D1 C6 1 20000
D3 C6 2 25000
D2 C1 3 15000
D5 C1 4 25000
D4 C2 5 25000
D2 C1 6 10000
D1 C4 7 31000
7 rows selected.
5. Find the total number of people who owned the cars that were involved in the accident in 2008.
SQL> SELECT COUNT(*) AS CAR_ACCIDENTS
2 FROM OWNS
3 WHERE REGNO
4 IN( SELECT REGNO
5 FROM PARTICIPATED
6 WHERE REPNO
7 IN(SELECT REPNO
8 FROM ACCIDENT
9 WHERE DAT LIKE '%11'));
CAR_ACCIDENTS
6
6. Find number of accidents in which cars belonging to a specific model were involved.
SQL> SELECT COUNT(*) AS NO_OF_ACCIDENTS
2 FROM PARTICIPATED
3 WHERE REGNO
4 IN (SELECT REGNO
5 FROM CAR
6 WHERE MODEL LIKE 'ALTO');
NO_OF_ACCIDENTS
4
No comments:
Post a Comment