Teradata SQL Test – 4

So you think you know SQL well , try this SQL Test.

We have a table which stores trains Source and Destination information. And the table sample data looks like below:

SOURCE

DESTINATION

DISTANCE

NDLS

LKO

520

LKO

NDLS

520

MUM

BAN

700

BAN

CHE

450

BAN

MUM

700

MUM

LKO

1200

 Now the requirement is that we should have only one [SOURCE-DESTINATION / DESTINATION-SOURCE ] pair in the table. If we have repeating pairs like [NDLS-LKO] & [LKO-NDLS] then remove one entry. Similarly, [MUM-BAN] & [BAN-MUM] is repeated so one row should be deleted. The output should look like below where highlighted rows should be removed.

SOURCE

DESTINATION

DISTANCE

NDLS

LKO

520

LKO

NDLS

520

MUM

BAN

700

BAN

CHE

450

BAN

MUM

700

MUM

LKO

1200

 Now the challenge is to use only one SQL DELETE STATEMENT to get the only required records in the table.

Leave your answer in the comment box below. Answer will be shared this Sunday. Till then leave your guess as comment.

UPDATE:

Check Teradata SQL Test – Answers for the answer.

25 thoughts on “Teradata SQL Test – 4

  1. DEL
    FROM TRN_INF MAIN_TBL
    WHERE (source_1,dest,distance)
    IN
    (SEL source_1,dest,distance
    FROM
    (SEL a.source_1,A.dest,A.distance,
    Row_Number() Over (PARTITION BY a.DISTANCE ORDER BY a.DISTANCE ASC) AS rwm
    FROM TRN_INF a
    QUALIFY rwm1) REF)

  2. CREATE TABLE TRN_INF
    (
    SOURCE VARCHAR(20)
    ,DESTINATION VARCHAR(20)
    ,DISTANCE INT
    );

    INSERT INTO TRN_INF VALUES (‘MUM’,’LKO’,’1200′) ;
    INSERT INTO TRN_INF VALUES (‘BAN’,’CHE’,’450′) ;
    INSERT INTO TRN_INF VALUES (‘BAN’,’MUM’,’700′) ;
    INSERT INTO TRN_INF VALUES (‘MUM’,’BAN’,’700′) ;
    INSERT INTO TRN_INF VALUES (‘LKO’,’NDSL’,’520′) ;
    INSERT INTO TRN_INF VALUES (‘NDSL’,’LKO’,’520′) ;

    DEL FROM TRN_INF WHERE (SOURCE,DESTINATION,DISTANCE ) IN (
    SEL A.SOURCE,A.DESTINATION,A.DISTANCE FROM
    (
    SEL A.*, ROW_NUMBER() OVER (PARTITION BY A.DISTANCE ORDER BY A.SOURCE ) SEQN
    FROM TRN_INF A
    INNER JOIN TRN_INF B
    ON TRIM(A.SOURCE) = TRIM(B.DESTINATION)
    AND TRIM(A.DESTINATION) = TRIM(B.SOURCE)
    QUALIFY SEQN =1 ) A
    )

  3. Del from D_BASEL2DB.train x
    where (x.source1,x.destination) in
    (sel ou.Source1,ou.destination from (sel a.Source1,a.destination,a.distance,row_number() over( partition by a.distance order by a.distance,a.Source1,a.Destination) as rw
    from D_BASEL2DB.train a
    inner join D_BASEL2DB.train b
    on a.source1=b.destination
    and a.destination=b.source1) as ou
    where rw>1)

  4. Select source,destination,distance from table a
    Minus
    Select destination, source,distance from table a

    This will work..

  5. delete from distance d
    where (src,destination) in(
    Select src,destination from (
    select src,destination,row_number() over (partition by hashrow(src,destination) order by src) rownum
    from distance
    ) A
    where rownum=2
    )

  6. del from train_info
    where (src,dstn) in
    (
    sel src,dstn from(
    sel t1.*,
    rank () over (partition by t1.distance order by t1.src) rnk
    from train_info T1, train_info T2
    where t1.distance = t2.distance
    and t1.SRC = t2.DSTN
    and t1.DSTN = t2.SRC
    qualify rnk = 1
    ) DT
    );

  7. hey can we try it this way

    DELETE from table
    WHERE
    (select (trim(source) || ‘-‘ || trim(destination)) as sourcedest from table)
    in
    (select (trim(destination) || ‘-‘ || trim(source)) as destsource from table)

  8. DELETE FROM _sample_tbl a, _sample_tbl b
    WHERE
    (a.source_nm = b.destination_nm AND b.source_nm = a.destination_nm AND a.distance = b.distance)
    AND a.source_nm < b.source_nm;

  9. delete from table where source, destination in (select destination,source from table) , maybe somethin like that ?

  10. We can delete the duplicate record with using rowid. here the query is:
    delete * from T_Name A where A.rowid<(select max(rowid) from T_Name B where A.rowid=B.rowid);

  11. why doesnt the below work? Can anyone tell me?

    delete from table where (source,destination) in (sel destination,source from table)

  12. del from train tr
    where (tr.source,tr.dest,tr.dist)
    in
    (sel max(t1.source),min(t1.dest),t1.dist
    from train t1
    inner join
    train t2
    on t1.dest = t2.source
    and t1.source = t2.dest
    group by t1.dist
    )

  13. Pls execute below 2 queries. This is the only way we can acheive the output.

    CREATE MULTISET VOLATILE TABLE VOLATILE_1 AS
    (SELECT SOURCE,DESTINATION FROM TRAIN_GUJJA QUALIFY ROW_NUMBER() OVER(PARTITION BY DISTANCE ORDER BY DISTANCE)=1)
    WITH DATA ON COMMIT PRESERVE ROWS;

    DELETE FROM TRAIN_GUJJA WHERE (SOURCE,DESTINATION) IN
    (SELECT SOURCE,DESTINATION FROM VOLATILE_1)

  14. Simplest way to get the answer of he above query

    with cte as(
    Select distance,Source,Destination,
    row_number() over( partition by distance order by distance,Source,Destination) as New
    from Location
    )

    delete from cte where new > 1

  15. KEEP IT SIMPLE $%@#%(KISS)! Create Unique rows in a Temp. table delete all duplicate rows from Base table. Insert unique rows from Temp table to Base table. When i have a long list of SQL jobs to complete by EOD i go for the KISS every time. But, i would love to see the “only 1” delete statement answer, please do share..!
    Create table temp1 as
    (
    SELECT
    a.distance,
    max(a.source) AS source,
    min(a.destination) AS destination
    FROM t1 as a, t1 as b
    WHERE a.source = b.destination
    AND a.destination = b.source
    group by a.distance
    ) with data
    For practical purposes this table more than likely would have a PK and this requirement would be much easier to process.. šŸ™‚

  16. Hey above one is incorrect. I tried to write many queries but could not get the result . please share the answer. today is sunday only…

Leave a Reply

Your email address will not be published. Required fields are marked *