					More programs at www.pranavthegreat.cjb.net


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)
);

SYLLABUS 4 QUERY
************/******

4.3>SELECT NAME,AUTHOR_ID
FROM AUTHOR
WHERE AUTHOR_ID IN (SELECT AUTHOR_ID
FROM CATALOG
WHERE YEAR>2000 AND (SELECT AVG(PRICE) FROM CATALOG)<PRICE
GROUP BY AUTHOR_ID
HAVING COUNT(*)>=2);

VARIATION OF THE SAME
*********************
SELECT  A.AUTHOR_ID,A.NAME
FROM AUTHOR A,CATALOG C
WHERE A.AUTHOR_ID=C.AUTHOR_ID AND
(SELECT AVG(PRICE) FROM CATALOG)<PRICE AND C.YEAR>2000
GROUP BY A.AUTHOR_ID,A.NAME
HAVING COUNT(*)>=2;

5A>
SELECT CNAME 
FROM CUSTOMER
WHERE CNAME IN(SELECT CNAME
FROM DEPOSITOR D,PACCOUNT A
WHERE A.ACCNO=D.ACCNO) AND
CNAME NOT IN(SELECT C.CNAME
FROM CUSTOMER C,BORROWER B,LOAN L
WHERE C.CNAME=B.CNAME AND B.LOAN_NO=L.LOAN_NO);


					More programs at www.pranavthegreat.cjb.net
