Post reply

sql query logics2

  • August 16, 2012 at 1:40 am

    #265103

    hi friends i have smal doubt in sql plz tell me answer whicch query give more perfomance annd how to i find which query give more performnac plz tell me what steps i follow and tell me in the given query

    CREATE TABLE EMP ( EMP_ID INT NOT NULL, NAME VARCHAR(100), SALARY INT, DEPT_ID INT NOT NULL, LOC_ID INT NOT NULL);

    CREATE TABLE RAT ( RAT_ID INT NOT NULL, EMP_ID INT NOT NULL, RAT NUMBER(10,2));

    CREATE TABLE LOC ( LOC_ID INT NOT NULL, CODE CHAR(2), DESCRIPTION VARCHAR (256));

    CREATE TABLE DEPT ( DEPT_ID INT NOT NULL, CODE VARCHAR(10), DESCRIPTION VARCHAR(256));

    CREATE UNIQUE INDEX EMP__EMP_ID__IDX ON EMP(EMP_ID);

    CREATE UNIQUE INDEX RAT__EMP_ID__IDX ON RAT(EMP_ID);

    CREATE UNIQUE INDEX RAT__RAT_ID__IDX ON RAT(RAT_ID);

    CREATE UNIQUE INDEX LOC__LOC_ID__IDX ON LOC(LOC_ID);

    CREATE UNIQUE INDEX DEPT__DEPT_ID__IDX ON DEPT(DEPT_ID);

    Assuming very large data sets for each table defined above, which query is likely to run fastest?

    • SELECT E.name FROM EMP E INNER JOIN DEPT D ON D.dept_id = E.dept_id WHERE E.emp_id = 11;

    • SELECT E.name FROM EMP E INNER JOIN LOC L ON L.loc_id = E.loc_id WHERE E.emp_id = 11;

    • SELECT E.name FROM EMP E INNER JOIN DEPT D ON D.dept_id = E.dept_id INNER JOIN LOC L ON L.loc_id = E.loc_id WHERE E.emp_id = 11;

    • SELECT E.name FROM EMP E INNER JOIN LOC L ON L.loc_id = E.loc_id WHERE E.emp_id = 1;

    • SELECT E.name FROM EMP E INNER JOIN RAT R ON R.emp_id = E.emp_id WHERE E.emp_id = 1;

    plz tell me the anser.plz tell me how we find it

  • August 16, 2012 at 1:59 am

    #1526368

    Why don't you create the tables, add some data and test it out?

    Interview question or homework?

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • August 16, 2012 at 5:01 am

    #1526471

    how can u gues it is interview question? and in case of its interview question why ur not help?ok if u know answer tell me answers its help other wise donot comments unnecessary

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply