Results 1 to 6 of 6
  1. #1
    Join Date
    Oct 2015
    Posts
    1

    Unanswered: help on complex query

    can anyone help with these questions they are so so hard all of my queries don't even work
    Attached Thumbnails Attached Thumbnails uuuuuuuuuuuuuu.PNG  
    Last edited by sozener1; 10-13-15 at 03:48.

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    so its down to how you define form the JOINS between each relevant table, how navigate the table design to get tot he data you need.

    Id suggest you start with question 1 and work your way towards question 4 which is more complex.

    so what have you tried for question 1?

    you may need to use a sub query to do some of the grunt work
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    Once you show some of the work that you have come up with from your assignment here, plenty of us will be more than happy to give you hints and tips, but we won't be doing it for you.
    Dave

  4. #4
    Join Date
    Oct 2015
    Posts
    2
    Quote Originally Posted by sozener1 View Post
    Click image for larger version. 

Name:	uuuuuuuuuuuuuu.PNG 
Views:	6 
Size:	162.1 KB 
ID:	16576

    from the attachement i have some questions,

    Which films has customer Sam Raimi not watched? List the films along with how
    many times everyone else has watched the films

    which genres of films have been watched the least in postcode
    3052? List the bottom 5 genres for that postcode.

    Which films are not making a profit for NetFilms? (Profit is the net takings from
    viewings minus the wholesale cost of the film.)

    List who has acted in the most films and who has acted in the least films?

    the fourth question ive wrote a query to get the most films out

    select FirstName, LastName, count(*) as totalcount
    from CastAndCrew group by FirstName, LastName
    having count() = (select max(totalcount) from (select FirstName, LastName, count() as totalcount from CastAndCrew
    where Id in
    (select CastAndCrew from Production where Role in
    (select Id from Role where Name = 'Actor')) group by FirstName, LastName)CastAndCrew);

    the questions are those above

    and im given the tables shown in the figure

    can anyone help with these questions they are so so hard all of my queries don't even work
    You do know that this constitutes to cheating, and if Mitchell Harrop (our lecturer) or the head tutor (Jonathan) finds out, this would be noted against your academic record at The University of Melbourne.

    This assignment isn't due until Monday, 19/10. If you've any questions, you should post them in the discussion forum of INFO 20003 in the LMS.

    For Question 11 (the one with post code 3052), make use of count and several inner joins to filter your results. Remember to use Count(distinct x) as well.
    Last edited by yjchua95; 10-12-15 at 21:53.

  5. #5
    Join Date
    Oct 2015
    Posts
    2

    4436

    Quote Originally Posted by dav1mo View Post
    Once you show some of the work that you have come up with from your assignment here, plenty of us will be more than happy to give you hints and tips, but we won't be doing it for you.
    And rightly so. This is a university assignment from the OP's uni (and mine as well). Questions regarding the assignment should be posted within the subject's internal forums.

  6. #6
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Quote Originally Posted by yjchua95 View Post
    And rightly so. This is a university assignment from the OP's uni (and mine as well). Questions regarding the assignment should be posted within the subject's internal forums.
    I'm glad to see someone from the Uni posting about this. DBForums has a long tradition of helping users, but since 2004 when I became the Administrator here DBForums hasn't permitted posting of homework solutions wholesale. If students have specific questions, we'll provide answers and often the sources for more information. If the student attempts to get us to do the assignment for them, we have a number of creative ways for helping them to understand the error of their ways!

    Welcome, and thank you for helping DBForums to keep things "on track" for another student!

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

Posting Permissions

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