Page 1 of 2 12 LastLast
Results 1 to 15 of 21

Thread: Sql queries

  1. #1
    Join Date
    Jul 2007
    Posts
    9

    Unanswered: Sql queries

    It is given the following schema of relations:

    Donators(Id <<PK>>, Name, Surname, IdCity <<FK(City)>>, Group, Sex, Age);
    Files(IdFile <<PK>>, Donator <<FK(Donators)>>, Date, Type);
    City(IdCity <<PK>>, Name, State);


    I need to find a query for each of the following:

    1. name, surname of the plasma donators(type='plasma'), with Sex='F' and younger than 45 years old.

    My solution:
    SELECT d.Name, d.Surname
    FROM Donators d, Filles f
    WHERE d.Id=d.IdFile AND f.type='plasma' AND d.sex='F' AND d.age<45

    2. average age of the plasma donators

    SELECT AVG(d.age)
    FROM Donators d, Files f
    WHERE f.type='plasma'

    3. the states which have more donators

    I have no idea how to do it

    4. the id's of the plasma donators from Chicago

    SELECT d.id
    FROM Donators d, Files f, City c
    WHERE d.id=f.idfile AND d.idcity=c.idcity AND f.type='plasma' AND c.name='Chicago'

    5. the id's of the cities without plasma donators

    SELECT c.idcity
    FROM City c
    WHERE NOT IN (SELECT c.idcity
    FROM City c, Donators d, Files f
    WHERE f.idfile=d.id AND f.type='plasma' AND d.idcity=c.idcity)

    6. remove the donators older than 80, after having removed the corresponding files

    No idea how to do it

    In conclusion, could you tell me if my queries are correct and help me with the others?

    Thanks

  2. #2
    Join Date
    Jun 2007
    Posts
    4

    My Answers

    I think instead of Filter condition, if we use Joins, it is helpful for improving performance.

    1. name, surname of the plasma donators(type='plasma'), with Sex='F' and younger than 45 years old.
    HTML Code:
    Select D.DName, D.DSurname
    From Donators D
    Inner Join Files F On F.FDID = D.DID
    Where F.FType = 'plasma' 
    And D.DSex = 'F' And D.DAge < 45
    2. average age of the plasma donators
    HTML Code:
    Select Avg(D.DAge) As AvarageAge
    From Donators D
    Inner Join Files F On F.FDID = D.DID
    Where F.FType = 'plasma'
    3. the states which have more donators
    HTML Code:
    Select Top 1 CState, cntDID
    From
    (
    	Select C.CState, Count(DID) As cntDID
    	From City C
    	Left Outer Join Donators D On D.DCID = C.CID
    	Group By C.Cstate
    ) As cntTable
    Order by cntDID Desc
    4. the id's of the plasma donators from Chicago
    HTML Code:
    Select * From Donators D
    Inner Join Files F On F.FDID = D.DID
    Inner Join City C On C.CID = D.DCID 
    Where F.FType = 'plasma' And C.CName = 'Chicago'
    5. the id's of the cities without plasma donators
    HTML Code:
    Select CID From City C
    Left Outer Join Donators D On D.DCID = C.CID
    Left Outer Join Files F On F.FDID = D.DID
    Where F.FType <> 'plasma'
    6. remove the donators older than 80, after having removed the corresponding files
    HTML Code:
    Create Procedure DeleteRecords 
    As
    Begin
    	Delete From Files 
    	Where FDID In (Select DID From Donators Where DAge >= 80) 
    	
    	Delete From Donators Where DAge >= 80
    End

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    dear mister prajkta, please do not just hand people the answers to their homework assignments

    you may help them understand the questions, you may coach them, you may offer suggestions, but please do not just give them the answers

    thank you for your cooperation
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    Quote Originally Posted by prajktaa
    I think instead of Filter condition, if we use Joins, it is helpful for improving performance.
    the query is engine is smarter than that, but there are other reasons to use joins that range from the fact that multiple outer joins become perilous in this approach to general code readability.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  5. #5
    Join Date
    Jul 2007
    Posts
    9
    Thank you for your reply. First of all, I needed to know if my queries are right. And second, I need to write them without left outer join or inner join etc. Is it possible?

    r937, I'm definetely not in search of already solved queries, 'cause I do need to understand, so I agree with you.

    EDIT: I just need to write them in standard SQL, so maybe this section is wrong, dunno. Don't need high performance, just correct and simple syntax. Are mine correct?
    Last edited by TomSawyer; 07-03-07 at 08:24.

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by TomSawyer
    And second, I need to write them without left outer join or inner join etc.
    EDIT: I just need to write them in standard SQL, so maybe this section is wrong, dunno.
    why in the world do you not want to use joins???

    FYI joins are standard SQL
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Jul 2007
    Posts
    9
    Because we ought to write queries without joins. I mean, even if the queries are longer and less efficient. I don't understand almosto all Prajkta's solutions, because I don't know how to use joins.

    For example, how could one write a query above without the left outer join or without the inner join?

  8. #8
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by TomSawyer
    Because we ought to write queries without joins.
    Cooo. Unless that is just to try to get you to use alternative SQL Syntax for the sake of it then you are being taught bad practice. Probably worth bearing in mind when you start doing it for real in a job.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  9. #9
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by TomSawyer
    Because we ought to write queries without joins.
    your teacher is silly, and the course is bogus

    ask for your money back
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  10. #10
    Join Date
    Jul 2007
    Posts
    9
    It isn't an advanced SQL teaching (I'll never have to do this for real in a job), but just some basic concepts, and we were taught to write only few queries without inner joins, left outer joins etc. I know this is "bad SQL" .

  11. #11
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    That's not much different than a .Net course teaching you to program with GO TO statements.
    Whoever is teaching you not to use JOINs is a jackass. If somebody made a statement like that to me during a job interview I guarantee they would not be hired.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  12. #12
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    i think pootle sussed it correctly

    it's an assignment to get them to think in sql, to solve those problems in other ways besides using a join
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  13. #13
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    I doubt it. There are still some stubborn codgers out there who have never learned to use joins, and advise others not to as well. Particularly among the Oracle developer community.
    I see no benefit to teaching people who to write bad code.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  14. #14
    Join Date
    Jul 2007
    Posts
    9
    I understand the use of the inner join, but I can't understand 3. and 5. with the left outer join. How can we write those queries without the left outer join?

  15. #15
    Join Date
    Aug 2006
    Location
    San Francisco, CA
    Posts
    136
    The way that this course might be designed is to start with simple concepts such as select * and adding the where clause to filter the results. I would not be surprised if in two weeks they go back over the old answers and re-work them with proper joins to show how much more efficient joins are.

Posting Permissions

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