1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

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:
    1,667
    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:
    1,667
    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 .......
     
  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]
    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:
    1,667
    You're joining on the wrong column :)

    Change this PET_3.PetType = BREED.BreedName

    to

    PET_3.PetBreed = BREED.BreedName