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

    Join Date
    Aug 2012
    Posts
    3
    Rep Power
    0

    First College Assignment


    Hey guys, currently just started a part-time college course for SQL. Its quite intense for the level I'm at anyway, I've had to develop a Database design and then produce 3 SQL statements without any real environment.

    (The Database is for a cinema, its films, customers, show times and actors in the films)

    1. Show all of the films that would have been shown at the cinema between 2 separate dates

    Code:
    SELECT Films.FilmID, Films.Title, Films.Category
    FROM Films
    INNER JOIN Shows
    ON Films.FilmID = Shows.FilmID
    WHERE Shows.Date >= 'Date here' AND <= 'Date Here'
    2. Show all of the films an actor has made an appearance in, in date order the films were/are gonna be shown. This should also group the actors together.

    Code:
    SELECT Films.FilmID, Films.Title, Films.Category
    FROM Films
    INNER JOIN Actors
    ON
    Actors.FilmID = Films.FilmID
    INNER JOIN Shows
    ON Actors.FilmID = Show.FilmID
    WHERE Actors.First_Name = 'First Name here' AND Actors.Last_Name = 'Last Name here'
    GROUP BY Actors.First_Name, Actors.Last_Name
    ORDER BY Shows.Date
    3. ALL of the films with actors of the same nationality.

    Code:
    SELECT Films>FilmID, Films.Title, Films.Category
    FROM Films
    INNER JOIN Actors
    ON Actors.FilmID = Films.FilmID.
    WHERE Nationality = 'Nationality Here'
    Thank you for any help guys
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,026
    Rep Power
    4210
    Originally Posted by BITmixit
    ...without any real environment.
    they specifically want you to write SQL without testing it?

    or, you are allowed to test it, but you don't have a testing environment yourself yet?

    if the former, ditch the course

    if the latter, install something -- there are free versions of mysql, sql server, oracle, etc.

    all 3 of your solutions, by the way, have errors
    r937.com | rudy.ca
    please visit Simply SQL and buy my book
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2012
    Posts
    3
    Rep Power
    0
    Cheers, managed to get them all working in SQL Management studio altho I'm not supposed to.

    Having trouble with the 2nd one however.

    2. Show all of the films an actor has made an appearance in, in date order the films were/are gonna be shown. This should also group the actors together.

    Code:
    SELECT Actors.ActorID, Actfilm.FilmID, Films.Title,  Actors.First_Name, Actors.Last_Name, Actors.Nationality, Show.Date
    FROM Actors
    INNER JOIN ActFilm
    On Actfilm.ActorID = Actors.ActorID
    INNER JOIN Films
    On Actfilm.FilmID = Films.FilmID
    INNER JOIN Show
    On Films.FilmID = Show.FilmID
    GROUP BY ActorID
    ORDER BY Date
    Msg 8120, Level 16, State 1, Line 1
    Column 'ActFilm.FilmID' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

    I've researched Aggregate functions but most of the ones I've found involve mathematics and obviously adding all of the select to group by just produces the same results.
  6. #4
  7. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,026
    Rep Power
    4210
    Originally Posted by BITmixit
    This should also group the actors together.
    ha ha, i see what they did there -- very sneaky

    this means ORDER BY, not GROUP BY
    r937.com | rudy.ca
    please visit Simply SQL and buy my book
  8. #5
  9. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2012
    Posts
    3
    Rep Power
    0
    Originally Posted by r937
    ha ha, i see what they did there -- very sneaky

    this means ORDER BY, not GROUP BY
    Ahhhhh, very sneaky indeed.

    Cheers for your help man. All sorted
  10. #6
  11. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,026
    Rep Power
    4210
    Originally Posted by BITmixit
    All sorted
    pun intended, eh

    r937.com | rudy.ca
    please visit Simply SQL and buy my book

IMN logo majestic logo threadwatch logo seochat tools logo