#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2012
    Posts
    6
    Rep Power
    0

    Need help with SQL statements


    Since the beggining of me starting to study SQL, I've been having trouble. I need to write statements.
    my relations are :
    SUPPLIER(SupplierID, SupplierName, Phone, Address)
    INVENTORY(ProductID, ProductName, Price, SupplierID)

    So far I've created the 2 tables and put few records in.
    Create table Supplier
    (
    SupplierID Int Not Null,
    SupplierName VarChar (15) Not null,
    Phone Int (10) Not Null,
    Address VarChar (30) Not Null,
    Constraint Supplier_pk Primary Key (SupplierID)
    );

    Create table Inventory
    (
    ProductID Int Not NULL,
    ProductName VarChar(30) NOT NULL,
    Price Char(8) Not NULL,
    SupplierID Int not null,
    Constraint Inventory_pk Primary Key (ProductID),
    Constraint Inventory_fk Foreign Key (SupplierID)
    References Supplier (SupplierID)
    );

    Insert into Supplier Values (1234, 'Costco', 5553338888 , 'Avenue 5');
    Insert into Inventory Values (0987, 'Gum', '4.99', 1234);


    What I need help with is... the statements. I can write simple ones, but these look way above of what I know.
    a) Write an SQL statement to display each SupplierID and beside it, how many different products the store carries by that supplier. Use an alias.
    b)Write an SQL statement to display the supplierID, productID, and price for all products that cost $5 or less. The products should be displayed by increasing supplierID, and within each supplierID, by decreasing productID.
    c) Write an SQL statement to add a column to the INVENTORY relation which will contain the quantity on hand of a product. The column should be named Quantity. Then, populate the column with values.
    d) assume "part c" was done. Write an SQL statement to display ProductID, Name of product, Name of supplier, and InventoryValue for every product. Use an alias.
    e)In the SUPPLIER relation, the Phone attribute contains exactly 10 characters, the first 3 of which are the area code. Write an SQL statement to display the SupplierID and Name(of the supplier) for all suppliers whose phone number doesn't contain a local area code (703,571).

    Please help if you can. Thanks.
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,118
    Rep Power
    4274
    Originally Posted by Armis
    I can write simple ones, but these look way above of what I know.
    those actually are pretty simple

    give them a try, an honest try, and we'll help correct them

    but we aren't going to do your homework for you
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2012
    Posts
    6
    Rep Power
    0
    Yeah I've tried. Gotten 3 done but not sure if they are right.

    B)
    Select SupplierID, ProductID, Price
    from Supplier, Inventory
    where price <= 5
    order by supplierID, productID Desc;

    C)
    alter table Inventory
    Add Quantity Int;
    Update Inventory
    Set Quantity = 10
    where ProductID = 0987;

    E)
    Select SupplierID, SupplierName
    from Supplier
    where phone <> (571,703);
  6. #4
  7. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,118
    Rep Power
    4274
    b) you've got a cross join there, because you forgot the join condition

    c) looks okay

    e) you can't have a column value compared to two values like that
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  8. #5
  9. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2012
    Posts
    6
    Rep Power
    0
    Originally Posted by r937
    b) you've got a cross join there, because you forgot the join condition

    c) looks okay

    e) you can't have a column value compared to two values like that
    Any tips on how to fix them and how to do A and D?
  10. #6
  11. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,118
    Rep Power
    4274
    to fix b), add the join condition that properly joins Supplier and Inventory

    to fix c), you have to figure out a way to isolate the fist few digits of an INTEGER phone number

    i would use a range test --
    Code:
    WHERE NOT phone BETWEEN 5710000000 AND 5719999999
      AND NOT phone BETWEEN 7030000000 AND 7039999999
    for a), use GROUP BY and COUNT

    for d), it's another simple join
    Last edited by r937; December 11th, 2012 at 03:53 AM.
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  12. #7
  13. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2012
    Posts
    6
    Rep Power
    0
    Originally Posted by r937
    to fix b), add the join condition that properly joins Supplier and Inventory

    to fix c), you have to figure out a way to isolate the fist few digits of an INTEGER phone number

    i would use a range test --
    Code:
    WHERE NOT phone BETWEEN 5710000000 AND 5719999999
      AND NOT phone BETWEEN 7030000000 AND 7039999999
    for a), use GROUP BY and COUNT

    for d), it's another simple join
    for A) would I use the alias for the products or supplier? That's what confuses me, doing the alias statements.
    for D) again, alias for inventory value or product?
  14. #8
  15. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,118
    Rep Power
    4274
    the aliases that the questions want you to use are column aliases

    write the queries and you'll see why
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  16. #9
  17. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2012
    Posts
    6
    Rep Power
    0
    B) How i joined the supplier and inventory
    Select SupplierID, ProductID, Price
    from Supplier. SupplierID = Inventory. SupplierID
    where price <= 5
    order by supplierID, productID Desc;


    for C) I did this
    Select SupplierID, SupplierName
    from Supplier
    where not phone >= 703000000 and phone <= 703999999
    and not phone >= 571000000 and phone <= 571999999;


    for A) i came up with this
    select SupplierID
    from Supplier;
    select P as productID
    from Inventory
    group by productID;
    having count(*) > 1000;

    D)
    Select productID, productName, supplierName, InventoryValue
    from Supplier, Inventory;
    Select P AS ProductID
    from Inventory
    order by productName;
  18. #10
  19. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,118
    Rep Power
    4274
    you need to do some studying and/or revising, because your sql has serious errors

    from Supplier. SupplierID = Inventory. SupplierID
    is not a valid FROM clause

    where not phone >= 703000000 and phone <= 703999999
    and not phone >= 571000000 and phone <= 571999999;

    is logically incorrect (see my solution above)

    select SupplierID
    from Supplier;
    select ...

    two consecutive sql statements is not invalid, but it's nowhere near close to a solution for this question
    also, P as productID is not the right way to assign an alias

    your solutions look really slapped together, and like i said, you need to put more work into learning sql
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  20. #11
  21. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2012
    Posts
    6
    Rep Power
    0
    Well I have these statements page and I have to come up with solutions for the ABDCE.

    But Thanks for your help. There's no way I'm gonna understand this.
    Thanks.

IMN logo majestic logo threadwatch logo seochat tools logo