Need Helps with SQL Queries for School Assignment

Discussion in 'BlackHat Lounge' started by BTCWorker, Oct 20, 2015.

  1. BTCWorker

    BTCWorker Registered Member

    Joined:
    Apr 8, 2015
    Messages:
    68
    Likes Received:
    3
    Occupation:
    Freelancer - eCommerce - Marketing
    Location:
    Daytona Beach, Florida, U
    If anyone can take a look at this and tell me what I am doing wrong, I would surely appreciate it. I am using my textbook and W3schools SQL Tutorials and can't figure out why I keep having issues with these two specific queries...

    Question: 335
    Write an SQL statement to display the OwnerLastName, OwnerFirstName, and OwnerEmail of any owners of cats. Use a subquery with PET_OWNER and PET_3.

    PET_OWNER
    OwnerID OwnerLastName OwnerFirstName OwnerPhone OwnerEmail
    1 Downs Marsha 555-537-8765 [email protected]
    2 James Richard 555-537-7654 [email protected]
    3 Frier Liz 555-537-6543 [email protected]
    4 Trent Miles [email protected]
    PET_3
    PetID PetName PetType PetBreed PetDOB PetWeight OwnerID
    1 King Dog Std. Poodle 2009-02-27 00:00:00 25.5 1
    2 Teddy Cat Cashmere 2010-02-01 00:00:00 10.5 2
    3 Fido Dog Std. Poodle 2008-07-17 00:00:00 28.5 1
    4 AJ Dog Collie Mix 2009-05-05 00:00:00 20.0 3
    5 Cedro Cat Unknown 2007-06-06 00:00:00 9.5 3
    6 Wooley Cat Unknown 9.5 4
    7 Buster Dog Border Collie 2006-12-11 00:00:00 25.0 4
    QUERY:
    SELECT OwnerLastName, OwnerFirstName, OwnerEmail
    FROM PET_OWNER
    WHERE PET_OWNER.OwnerID= (SELECT PET_3.OwnerID FROM PET_3
    WHERE Pet_3.Pettype='Cat')
    -----
    It Returns: Query failed: Subquery returns more than 1 row

    Screenshot:





    https://gyazo.com/42b58823f433cf1671aef535b3e8e7cf

    and the next question is the same as the above but I also have to include the Cat name 'Teddy'..

    Screenshot:
    https://gyazo.com/a6a13976a6481c0b2fc4256ad5941af2

    I appreciate any help I can get here because I am at a lost. I did think about using a 'View' or a 'Join', but not even sure if those would work. I just cannot understand why what I did try will not work. Seems pretty straight forward, but still returns errors no matter what I try. The thing that is most frustrating is that I WANT it to return more than one row..
     
  2. archon10

    archon10 BANNED BANNED

    Joined:
    Oct 10, 2011
    Messages:
    1,181
    Likes Received:
    8,218
    Your subquery returns more than 1 record and you have an equal sign. Use IN instead.

    SELECT OwnerLastName, OwnerFirstName, OwnerEmail
    FROM PET_OWNER
    WHERE PET_OWNER.OwnerID IN (SELECT PET_3.OwnerID FROM PET_3
    WHERE Pet_3.Pettype='Cat')
     
  3. BTCWorker

    BTCWorker Registered Member

    Joined:
    Apr 8, 2015
    Messages:
    68
    Likes Received:
    3
    Occupation:
    Freelancer - eCommerce - Marketing
    Location:
    Daytona Beach, Florida, U
    Thanks a lot for the help. I was able to just add the AND statement for the cats name for the second query and it worked like a charm too.

    ok,

    I have 3 tables table 1 and table 2 share keys
    Table 2 and table 3 share keys
    I need to query 3 columns from table one that correalate with 1 column from table 3 that meets certain guidelines.

    How do I make the connection between Table 1 and 3 (obviously through table 2) and display the information in a single query?
     
  4. archon10

    archon10 BANNED BANNED

    Joined:
    Oct 10, 2011
    Messages:
    1,181
    Likes Received:
    8,218
    I think by keys you mean primary-foreign key relationships? I'm assuming these three tables are normalized and you have the primary keys from table 1 and 3 in table 2? I'm thinking that's the goal here but not sure.

    It would be something like

    select * from table1 t1
    join table2 t2 on t1.id = t2.id
    join table3 t3 on t2.id = t3.id
    where .......
     
    • Thanks Thanks x 5
  5. BTCWorker

    BTCWorker Registered Member

    Joined:
    Apr 8, 2015
    Messages:
    68
    Likes Received:
    3
    Occupation:
    Freelancer - eCommerce - Marketing
    Location:
    Daytona Beach, Florida, U
    This just doesn't make any sense. Everything I can find, including the last reply says that this should return the data. The column heading I queried coome up, but the data is empty... anyone see what I am doing wrong or is this an issue with the SQL app they have us using?



    Question: 341
    Write an SQL statement to display the OwnerLastName, OwnerFirstName, PetName, PetType, PetBreed, and AverageLifeExpectancy for pets with a known PetBreed. Use PET_3. The PET_OWNER, PET_3, and BREED tables are below.
    ↓ Enter your answer below ↓​

    SELECT OwnerLastName, OwnerFirstName, OwnerEmail from PET_OWNER join PET_3 on PET_OWNER.OwnerID = PET_3.OwnerID join BREED on PET_3.PetType = BREED.BreedName where AverageLifeExpectancy> 15;


    OwnerLastName OwnerFirstName OwnerEmail


    OwnerID OwnerLastName OwnerFirstName OwnerPhone OwnerEmail
    1 Downs Marsha 555-537-8765 [email protected]
    2 James Richard 555-537-7654 [email protected]
    3 Frier Liz 555-537-6543 [email protected]
    4 Trent Miles [email protected]erent.com
    PetID PetName PetType PetBreed PetDOB PetWeight OwnerID
    1 King Dog Std. Poodle 2009-02-27 00:00:00 25.5 1
    2 Teddy Cat Cashmere 2010-02-01 00:00:00 10.5 2
    3 Fido Dog Std. Poodle 2008-07-17 00:00:00 28.5 1
    4 AJ Dog Collie Mix 2009-05-05 00:00:00 20.0 3
    5 Cedro Cat Unknown 2007-06-06 00:00:00 9.5 2
    6 Wooley Cat Unknown 9.5 2
    7 Buster Dog Border Collie 2006-12-11 00:00:00 25.0 4
    BreedName MinWeight MaxWeight AverageLifeExpectancy
    Border Collie 15.0 22.5 20
    Cashmere 10.0 15.0 12
    Collie Mix 17.5 25.0 18
    Std. Poodle 22.5 30.0 18
    Unknown
     
  6. archon10

    archon10 BANNED BANNED

    Joined:
    Oct 10, 2011
    Messages:
    1,181
    Likes Received:
    8,218
    You're joining on the wrong column :)

    Change this PET_3.PetType = BREED.BreedName

    to

    PET_3.PetBreed = BREED.BreedName
     
    • Thanks Thanks x 5