P3Queries.sql

/*  
    CSCI 585  HOMEWORK-3
    
    NAME  : AVINASH AKKA
    USC ID: 3874-5774-01
    
    --QUERIES--

*/


/*----------------------------QUERY A--------------------------*/

SELECT STUDENT.STUDENTID FROM STUDENT
  WHERE sdo_filter(STUDENT.LOCATION,
                   SDO_geometry(2001,NULL,NULL,
                   SDO_elem_info_array(1,2003,3),
                   SDO_ordinate_array(200,200,300,300))
                   ) = 'TRUE';


/*----------------------------QUERY B--------------------------*/

SELECT B.BUILDINGID AS BUILDNGIDS_TRAMSTPIDS_FRM_P1
FROM STUDENT S ,BUILDING B 
WHERE S.STUDENTID = 'p1' AND   
 SDO_WITHIN_DISTANCE(B.SHAPE,S.LOCATION,'distance=300') = 'TRUE'
UNION ALL
SELECT T.TRAMSTOPID
FROM STUDENT S1,TRAMSTOP T 
WHERE S1.STUDENTID = 'p1' AND   
      SDO_WITHIN_DISTANCE(T.COVERAGE,S1.LOCATION,'distance=300')= 'TRUE';

/*----------------------------QUERY C--------------------------*/

SELECT S.STUDENTID AS BUILDINGIDS_AND_STUDENTIDS
FROM STUDENT S,TRAMSTOP T
WHERE T.TRAMSTOPID = 't2ohe' AND SDO_WITHIN_DISTANCE(S.LOCATION,T.COVERAGE,'distance=300')='TRUE'
UNION
SELECT S.STUDENTID
FROM STUDENT S,TRAMSTOP T
WHERE T.TRAMSTOPID = 't5vhe' AND SDO_WITHIN_DISTANCE(S.LOCATION,T.COVERAGE,'distance=300')='TRUE'
UNION
SELECT S.STUDENTID
FROM STUDENT S,TRAMSTOP T
WHERE TRAMSTOPID = 't6ssl' AND SDO_WITHIN_DISTANCE(S.LOCATION,T.COVERAGE,'distance=300')='TRUE'
UNION
SELECT B.BUILDINGID
FROM TRAMSTOP T,BUILDING B
WHERE T.TRAMSTOPID = 't2ohe' AND SDO_WITHIN_DISTANCE(B.SHAPE,T.COVERAGE,'distance=300')='TRUE'
UNION
SELECT B.BUILDINGID
FROM BUILDING B,TRAMSTOP T
WHERE T.TRAMSTOPID = 't5vhe' AND SDO_WITHIN_DISTANCE(B.SHAPE,T.COVERAGE,'distance=300')='TRUE'
UNION
SELECT B.BUILDINGID
FROM BUILDING B,TRAMSTOP T
WHERE TRAMSTOPID = 't6ssl' AND SDO_WITHIN_DISTANCE(B.SHAPE,T.COVERAGE,'distance=300')='TRUE';


/*----------------------------QUERY D--------------------------*/

SELECT S1.STUDENTID, SDO_NN_DISTANCE(1) Distance
  FROM STUDENT S1  
  WHERE SDO_NN(S1.LOCATION,(SELECT LOCATION FROM STUDENT WHERE   
               STUDENTID='p12'),'sdo_num_res =6',1) = 'TRUE' AND   
               S1.STUDENTID <> 'p12'  
  ORDER BY Distance;

/*----------------------------QUERY E--------------------------*/

SELECT * 
FROM (
SELECT B.BUILDINGID,COUNT(B.BUILDINGID) AS StudentCount
FROM STUDENT S,BUILDING B
WHERE SDO_NN(B.SHAPE,S.LOCATION,'sdo_num_res=1') = 'TRUE'
GROUP BY B.BUILDINGID
ORDER BY StudentCount DESC
     )
WHERE ROWNUM<=2;

/*----------------------------QUERY F--------------------------*/

SELECT S.STUDENTID AS STUDENT,B.BUILDINGID AS INSIDE_BUILDING
FROM TABLE(SDO_JOIN('STUDENT', 'LOCATION','BUILDING','SHAPE',
                      'mask=INSIDE')) C,STUDENT S,BUILDING B
WHERE SDO_INSIDE(S.LOCATION,B.SHAPE) = 'TRUE';