practice.sql

CREATE TABLE PERSON (
  Did VARCHAR(10) PRIMARY KEY,
  Pname VARCHAR(10),
  Address VARCHAR(60)
);

CREATE TABLE CAR (
  Regno VARCHAR(10) PRIMARY KEY,
  Model VARCHAR(20),
  Year INT
);

CREATE TABLE ACCIDENT (
  Repno INT PRIMARY KEY,
  Date DATE,
  Loc VARCHAR(20)
);

CREATE TABLE OWNS (
  Odid  VARCHAR(10),
  Oregno VARCHAR(10),
  PRIMARY KEY(Odid,Oregno),
  FOREIGN KEY(Odid) REFERENCES PERSON(Did) ON DELETE CASCADE,
  FOREIGN KEY(Oregno) REFERENCES CAR(Regno) ON DELETE CASCADE
);
 
CREATE TABLE PARTICIPATED (
  Pdid VARCHAR(10),
  Pregno VARCHAR(10),
  Prepno INT,
  Damage INT,
  PRIMARY KEY(Pdid, Pregno, Prepno),
  FOREIGN KEY (Pdid) REFERENCES PERSON(Did) ON DELETE CASCADE, 
  FOREIGN KEY (Pregno) REFERENCES CAR(Regno) ON DELETE CASCADE,
  FOREIGN KEY(Prepno) REFERENCES ACCIDENT(Repno) ON DELETE CASCADE
);

INSERT INTO PERSON VALUES('1','Steve','Frankfurt');
INSERT INTO PERSON VALUES('2','Dustin','Perryridge');
INSERT INTO PERSON VALUES('3','Mike','Brooklyn');
INSERT INTO PERSON VALUES('4','Lucas','Perryridge ');
INSERT INTO PERSON VALUES('5','John',' Brooklyn ');
INSERT INTO PERSON VALUES('6','Antony','Hellington');
 
INSERT INTO CAR VALUES('KA04','BMW',2000);
INSERT INTO CAR VALUES('KA05','Ford',2002);
INSERT INTO CAR VALUES('KA03','Maruthi',1999);
INSERT INTO CAR VALUES('KA02','Tata',2002);
INSERT INTO CAR VALUES('KA01', 'Audi',2003);
INSERT INTO CAR VALUES('KA08', 'Maruthi',2003);
INSERT INTO CAR VALUES('KA06', 'Maruthi',2003);
INSERT INTO CAR VALUES('KA07', 'BMW',2003);
 
INSERT INTO ACCIDENT VALUES(12,'01-Jun-2001','Frankfurt');
INSERT INTO ACCIDENT VALUES(25,'02-Jul-2002','Brooklyn');
INSERT INTO ACCIDENT VALUES(512,'08-Mar-2000',' Brooklyn');
INSERT INTO ACCIDENT VALUES(1024,'25-Oct-2002','AvenueRoad');
INSERT INTO ACCIDENT VALUES(1000,'23-Dec-2003','RichmondCircle');
INSERT INTO ACCIDENT VALUES(1,'25-Dec-2004','ParkStreet');
 
INSERT INTO OWNS VALUES('1', 'KA04');
INSERT INTO OWNS VALUES('1', 'KA06');
INSERT INTO OWNS VALUES('2', 'KA07');
INSERT INTO OWNS VALUES('2', 'KA05');
INSERT INTO OWNS VALUES('3', 'KA03');
INSERT INTO OWNS VALUES('4', 'KA02');
INSERT INTO OWNS VALUES('5', 'KA01');
INSERT INTO OWNS VALUES('6', 'KA08');
 
INSERT INTO PARTICIPATED VALUES('1', 'KA04',12,1000);
INSERT INTO PARTICIPATED VALUES('1', 'KA06',25,1500);
INSERT INTO PARTICIPATED VALUES('2', 'KA05',512,1500);
INSERT INTO PARTICIPATED VALUES('2', 'KA05',1024,2500);
INSERT INTO PARTICIPATED VALUES('3', 'KA03',1000,1700);
INSERT INTO PARTICIPATED VALUES('4', 'KA02',1,100);

-- Display unique car Regnos involved in accidents.

SELECT DISTINCT (Pregno) FROM PARTICIPATED;

-- Display the car Regno and model which has the maximum damage amount.

SELECT  Pregno,Model FROM CAR, PARTICIPATED WHERE Pregno=Regno AND Damage IN (SELECT max(Damage) FROM PARTICIPATED);
-- or
SELECT Regno,Model FROM CAR WHERE Regno IN (SELECT Pregno FROM PARTICIPATED WHERE Damage=(SELECT max(Damage) FROM PARTICIPATED));

-- Display no. of cars owned by each driver.

SELECT  Odid,count(*) AS no_of_cars FROM OWNS GROUP BY Odid;