					More programs at www.pranavthegreat.cjb.net

Table 1
--------
CREATE TABLE PPERSON
(DRIVER_ID CHAR(5) PRIMARY KEY,
NAME VARCHAR2(15) NOT NULL,
ADDRESS VARCHAR2(30));

CREATE TABLE PCAR
(REGNO CHAR(10) PRIMARY KEY,
MODEL VARCHAR2(7),
YEAR INT DEFAULT 2005);

CREATE TABLE PACCIDENT
(REPORT_NUMBER INT PRIMARY KEY,
D_ATE DATE,
LOCATION VARCHAR2(10));

CREATE TABLE POWNS
(DRIVER_ID CHAR(5) PRIMARY KEY,
REGNO CHAR(10) NOT NULL,
CONSTRAINT FCK1 FOREIGN KEY(DRIVER_ID) REFERENCES PPERSON(DRIVER_ID),
CONSTRAINT FCK2 FOREIGN KEY(REGNO) REFERENCES PCAR(REGNO));

CREATE TABLE PPART
(DRIVER_ID CHAR(5) PRIMARY KEY,
REGNO CHAR(10) NOT NULL,
REPORT_NO INT,
DAMAGE_AMT INT,
CONSTRAINT FCK3 FOREIGN KEY(DRIVER_ID) REFERENCES PPERSON(DRIVER_ID),
CONSTRAINT FCK4 FOREIGN KEY(REGNO) REFERENCES PCAR(REGNO),
CONSTRAINT FCK5 FOREIGN KEY(REPORT_NO) REFERENCES PACCIDENT(REPORT_NUMBER));

=======================================================================
=======================================================================

INSERT INTO PPERSON
VALUES('&DRIVER_ID','&NAME','&ADDRESS');

INSERT INTO PCAR
VALUES('&REGNO','&MODEL',&YEAR);

INSERT INTO PACCIDENT
VALUES('&REPORT_NUMBER','&D_ATE','&LOCATION');

INSERT INTO POWNS
VALUES('&DRIVER_ID','&REGNO');

INSERT INTO PPART
VALUES('&DRIVER_ID','&REGNO',&REPORT_NO,&DAMAGE_AMT);
=========================================================================
=========================================================================

UPDATE PPART
SET DAMAGE_AMT=25000
WHERE REGNO='KA04EK8087' AND REPORT_NO=12;

INSERT INTO PACCIDENT
VALUES(37,'01-MAR-2005','MG-ROAD');

select o.driver_id,count(*)
from powns o,ppart p,paccident a
where o.driver_id=p.driver_id and
p.report_no=a.report_number and d_ate like '%02'
group by o.driver_id;

SELECT COUNT(*)
FROM PPART P,PCAR C
WHERE C.REGNO=P.REGNO AND C.MODEL='&MODEL'
GROUP BY P.REGNO;

=========================================================================
=========================================================================

			EXTRA QUERIES 1

1A>SELECT COUNT(*) "CARS_THAT_DIDNT_PARTICIPATED"
FROM PCAR
WHERE REGNO NOT IN(SELECT REGNO FROM PPART);

1B>CREATE VIEW ABS
AS SELECT *
FROM PACCIDENT
WHERE D_ATE BETWEEN '1-JAN-2004' AND '31-DEC-2004';

1C>SELECT SUM(DAMAGE_AMT)
FROM PPART,PACCIDENT
WHERE REPORT_NO=REPORT_NUMBER AND D_ATE LIKE'%04';

1D>SELECT LOCATION
FROM PACCIDENT
GROUP BY LOCATION
HAVING COUNT(LOCATION)=(SELECT MAX(COUNT(LOCATION)) FROM PACCIDENT GROUP BY LOCATION);

1E>SELECT DRIVER_ID,NAME
FROM PPERSON
WHERE DRIVER_ID IN (
(SELECT P.DRIVER_ID 
FROM PPERSON P,POWNS O
WHERE O.DRIVER_ID=P.DRIVER_ID
GROUP BY P.DRIVER_ID
HAVING COUNT(O.DRIVER_ID)>2)
INTERSECT
(SELECT P.DRIVER_ID
FROM PPERSON P,PPART T
WHERE P.DRIVER_ID=T.DRIVER_ID
GROUP BY P.DRIVER_ID
HAVING COUNT(T.DRIVER_ID)<3)
);


					More programs at www.pranavthegreat.cjb.net
