Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 5 of 5
  1. #1
    Regular Coder adarshakb's Avatar
    Join Date
    Jun 2009
    Location
    Silicon valley of india
    Posts
    247
    Thanks
    11
    Thanked 1 Time in 1 Post

    Output mystery??

    select order# from order_item o
    where not exists
    ((select warehouse# from warehouse where city='Bidar')
    minus
    (select warehouse# from shippment s where s.order#=o.order#));

    select * from order_item;
    SELECT * FROM warehouse;
    SELECT * FROM shippment;


    ORDER#
    5

    ORDER# ITEM# QTY
    1...........2...........5
    1...........5...........4
    2...........5...........3
    3...........1...........4
    4...........2...........8
    5...........3...........2

    6 rows selected.

    WAREHOUSE# CITY
    1......................Bidar
    2......................Bagalkot
    3 ......................Bijapur
    4 ......................Bidar
    5 ......................Bannerghatta
    6 ......................Bidar

    6 rows selected.

    ORDER# WAREHOUSE# SHIP_DATE
    1......................2 ...........02-JAN-90
    1......................4 ...........02-JAN-90
    2......................1...........03-JAN-90
    3......................5...........04-JAN-90
    4 ......................2...........05-JAN-90
    5......................4...........06-JAN-90
    5 ......................1...........06-JAN-90
    5......................6...........06-JAN-90


    -----------------------------------------------------------------

    My question is:

    Why/How 5 come in the 1st table as answer
    (select warehouse# from warehouse where city='Bidar')
    minus
    (select warehouse# from shippment s where s.order#=o.order#)
    return NULL

    But still we get output as 5

    and not as 1 2 3 4 5
    Last edited by adarshakb; 09-27-2010 at 07:37 AM.
    Two things are infinite: the universe and human stupidity; and I'm not sure about the universe.

    Albert Einstein
    -----------------------------------------------------
    My Blog songs

  • #2
    Super Moderator guelphdad's Avatar
    Join Date
    Mar 2006
    Location
    St. Catharines, Ontario Canada
    Posts
    2,634
    Thanks
    4
    Thanked 148 Times in 139 Posts
    What database are you using? Your question doesn't appear to be about MySQL. I can move the question to the general database forum, but want to specify the database application in the thread title as required.

  • #3
    Regular Coder adarshakb's Avatar
    Join Date
    Jun 2009
    Location
    Silicon valley of india
    Posts
    247
    Thanks
    11
    Thanked 1 Time in 1 Post
    Quote Originally Posted by guelphdad View Post
    What database are you using? Your question doesn't appear to be about MySQL. I can move the question to the general database forum, but want to specify the database application in the thread title as required.
    Oracle 9i

    Here is the SQL code for any one who likes to solve the question posed as to Retrive the order number which is in a specific city's ALL warehouse

    i.e. if warehouse 1,4,6 are in A and order X is located in 1,4 and order Y in 1,4,6 then the answer is Y
    Code:
    CREATE TABLE customer (
    cust# NUMBER(10) CONSTRAINT cc1 PRIMARY KEY,
    cname VARCHAR(20) CONSTRAINT cc2 NOT NULL,
    city VARCHAR(30)
    );
    
    CREATE TABLE ord (
    order# NUMBER(10) CONSTRAINT o1 PRIMARY KEY,
    odate DATE CONSTRAINT o2 NOT NULL,
    cust# NUMBER(10) CONSTRAINT o3 REFERENCES customer(cust#),
    ord_amt NUMBER(10,3) CONSTRAINT o4 CHECK(ord_amt>0)
    );
    
    CREATE TABLE item (
    item# NUMBER(10) CONSTRAINT i1 PRIMARY KEY,
    unitPrice NUMBER(10,3) CONSTRAINT i2 CHECK(unitPrice>0)
    );
    
    CREATE TABLE order_item (
    order# NUMBER(10) CONSTRAINT oi1 REFERENCES ord(order#) ON DELETE CASCADE,
    item# NUMBER(10) CONSTRAINT oi2 REFERENCES item(item#) ON DELETE CASCADE,
    qty NUMBER(6) CONSTRAINT oi3 CHECK(qty>0),
    CONSTRAINT oi4 PRIMARY KEY(order#,item#)
    );
    
    CREATE TABLE warehouse (
    warehouse# NUMBER(10) CONSTRAINT w1 PRIMARY KEY,
    city VARCHAR(30)
    );
    
    CREATE TABLE shippment (
    ORDER# NUMBER(10) CONSTRAINT s1 REFERENCES ord(order#) ON DELETE CASCADE,
    warehouse# NUMBER(10) CONSTRAINT s2 REFERENCES warehouse(warehouse#) ON DELETE CASCADE,
    ship_date DATE CONSTRAINT s3 NOT NULL,
    CONSTRAINT s4 PRIMARY KEY(warehouse#,order#)
    );
    
    commit;
    
    INSERT INTO customer  VALUES(1,'Adarsha','Bangalore');
    INSERT INTO customer  VALUES(2,'Ashish','Bangalore');
    INSERT INTO customer  VALUES(3,'Bhavesh','Bangalore');
    INSERT INTO customer  VALUES(4,'Anirudh','Bangalore');
    INSERT INTO customer  VALUES(5,'Abhishek','Bangalore');
    
    
    INSERT INTO ord  VALUES(1,'1-Jan-1990',1,2500);
    INSERT INTO ord  VALUES(2,'2-Jan-1990',1,4500);
    INSERT INTO ord  VALUES(3,'3-Jan-1990',2,6955);
    INSERT INTO ord  VALUES(4,'4-Jan-1990',4,1778);
    INSERT INTO ord  VALUES(5,'5-Jan-1990',5,2345);
    
    
    INSERT INTO item  VALUES(1,89);
    INSERT INTO item  VALUES(2,87);
    INSERT INTO item  VALUES(3,45);
    INSERT INTO item  VALUES(4,47);
    INSERT INTO item  VALUES(5,56);
    
    
    INSERT INTO order_item  VALUES(1,2,5);
    INSERT INTO order_item  VALUES(1,5,4);
    INSERT INTO order_item  VALUES(2,5,3);
    INSERT INTO order_item  VALUES(3,1,4);
    INSERT INTO order_item  VALUES(4,2,8);
    INSERT INTO order_item  VALUES(5,3,2);
    
    INSERT INTO warehouse  VALUES(1,'Bidar');
    INSERT INTO warehouse  VALUES(2,'Bagalkot');
    INSERT INTO warehouse  VALUES(3,'Bijapur');
    INSERT INTO warehouse  VALUES(4,'Bidar');
    INSERT INTO warehouse  VALUES(5,'Bannerghatta');
    INSERT INTO warehouse  VALUES(6,'Bidar');
    
    INSERT INTO shippment  VALUES(1,2,'2-Jan-1990');
    INSERT INTO shippment  VALUES(1,4,'2-Jan-1990');
    INSERT INTO shippment  VALUES(2,1,'3-Jan-1990');
    INSERT INTO shippment  VALUES(3,5,'4-Jan-1990');
    INSERT INTO shippment  VALUES(4,2,'5-Jan-1990');
    INSERT INTO shippment  VALUES(5,4,'6-Jan-1990');
    INSERT INTO shippment  VALUES(5,1,'6-Jan-1990');
    INSERT INTO shippment  VALUES(5,6,'6-Jan-1990');
    
    SELECT * FROM customer;
    SELECT * FROM ord;
    SELECT * FROM item;
    SELECT * FROM order_item;
    SELECT * FROM warehouse;
    SELECT * FROM shippment;
    Last edited by adarshakb; 09-28-2010 at 02:00 AM.
    Two things are infinite: the universe and human stupidity; and I'm not sure about the universe.

    Albert Einstein
    -----------------------------------------------------
    My Blog songs

  • #4
    Super Moderator guelphdad's Avatar
    Join Date
    Mar 2006
    Location
    St. Catharines, Ontario Canada
    Posts
    2,634
    Thanks
    4
    Thanked 148 Times in 139 Posts
    In future post questions about non-mysql databases in the general database forums, and mark it similar to

    Oracle: subjectname

    there are various features that work differently between the databases.

  • #5
    Regular Coder adarshakb's Avatar
    Join Date
    Jun 2009
    Location
    Silicon valley of india
    Posts
    247
    Thanks
    11
    Thanked 1 Time in 1 Post
    any one has any answers to this???

    I am confused
    Two things are infinite: the universe and human stupidity; and I'm not sure about the universe.

    Albert Einstein
    -----------------------------------------------------
    My Blog songs


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •