On Friday, 8 August, 2014 13:46, Petite Abeille <petite.abei...@gmail.com> said:
>On Aug 8, 2014, at 8:35 PM, Errol Emden <eem...@hotmail.com> wrote:
>
>> I am to list the film title and the leading actor for all of the films
>'Julie Andrews' played in.
>
>And another one, for diversity's sake...
>
>Assuming a slightly different data model:
>
>with
>AndrewsMovie
>as
>(
> select movie_cast.movie_id
> from movie_cast
> join person
> on person.id = movie_cast.person_id
> where person.name = 'Andrews, Julie (I)'
>)
>select movie.year,
> movie.title,
> person.name,
> movie_cast.attribute
>from movie_cast
>join movie
>on movie.id = movie_cast.movie_id
>join person
>on person.id = movie_cast.person_id
>where movie_cast.attribute like '%<1>'
>and exists
> (
> select 1
> from AndrewsMovie
> where AndrewsMovie.movie_id = movie_cast.movie_id
> )
>order by movie.year,
> movie.title;
>
>
>Sample for 2010:
>
>2010|"20 to 1" (2005) {Our All Time Favourite Films (#9.11)}|Newton,
>Bert|[Himself - Host] <1>
>2010|"Breakfast" (2000) {(2010-05-24)}|Stayt, Charlie|[Himself -
>Presenter] <1>
>2010|"The Daily Show" (1996) {Julie Andrews (#15.12)}|Stewart, Jon
>(I)|[Himself - Host] <1>
>2010|"The Late Late Show with Craig Ferguson" (2005) {(#6.80)}|Ferguson,
>Craig (I)|[Himself - Host] <1>
>2010|"The Oprah Winfrey Show" (1986) {(2010-10-28)}|Winfrey,
>Oprah|[Herself - Host] <1>
>2010|Despicable Me (2010)|Carell, Steve|(voice) [Gru] <1>
>2010|Shrek Forever After (2010)|Myers, Mike (I)|(voice) [Shrek] <1>
>2010|Tooth Fairy (2010/I)|Johnson, Dwayne (I)|[Derek] <1>
Or perhaps even more complicated from the TMS database:
bindings -> :lineup=0008100:X; :duration=24
select date(schedule.time, 'unixepoch', 'localtime') as showdate,
time(schedule.time, 'unixepoch', 'localtime') as showtime,
map.channel as channel,
callsign,
coalesce(runtime / 60, duration / 60) as runtime,
case hdtv when 1 then 'HD' else ' ' end as ishd,
case new when 1 then 'NEW' else ' ' end as isnew,
showtype,
case when subtitle != '' then title || ': ' || subtitle else title end
as title,
description,
(select group_concat(data, '; ')
from (select rolename || ': ' || group_concat(givenname || ' ' ||
surname, ', ') as data
from crewlink natural join crew
natural join role
where crewlink.program = program.program
group by rolename)
) as cast,
(select group_concat(advisoryname, ', ')
from advlink, advisory
where advlink.program = program.program
and advlink.advisory = advisory.advisory
group by advlink.program) as advisories,
(select group_concat(class, ', ')
from genrlink, genre
where genrlink.program = program.program
and genrlink.genre = genre.genre
group by genrlink.program) as genres,
originalAirDate,
year,
starRating,
syndicatedEpisodeNumber
from schedule, map, mychannels, station, program
where map.lineup = :lineup
and mychannels.lineup = map.lineup
and mychannels.channel = map.channel
and station.station = map.station
and schedule.station = map.station
and schedule.time >= strftime('%s', 'now') - 86400
and schedule.time <= strftime('%s', 'now') + (3600 * :duration)
and schedule.endtime > strftime('%s', 'now')
and program.program = schedule.program
and map.validfrom <= schedule.time
and map.validto >= schedule.endtime
and showtype != 'Paid Programming'
and ( showtype not in ('Series', 'Sports', 'Paid programming')
or series in mySeries
or (substr(schedule.program, 1, 2) = 'EP' and new = 1)
or series in (select distinct series
from schedule join program using (program)
where new
and series is not null
and program like 'EP%')
)
and map.channel between 200 and 314
order by 1,2,9,3;
to produce (after formatting):
----------------------------------------------------------------------------------------------------------------------------------
15:45 280 MCHD 1:34 HD Movie Hyde Park on Hudson
(2012); **+ Historical drama; Adult Situations; In
June 1939, President
Franklin Delano Roosevelt (Bill Murray) receives
Britain's monarchs
(Samuel West) at his home in upstate New York for an
unforgettable
weekend. Actor: Laura Linney, Bill Murray, Elizabeth
Wilson, Martin
McDougall, Samuel West, Olivia Williams, Elizabeth
Marvel, Eleanor Bron,
Olivia Colman; Director: Roger Michell; Executive
Producer: Tessa Ross;
Producer: Roger Michell, David Aukin, Kevin Loader
----------------------------------------------------------------------------------------------------------------------------------
15:50 282 MC3HD 1:15 Movie Emma's Wings: A Bella
Sara Tale (2013); Fantasy, Adventure, Animated; A
girl must defeat an
evil woman who plots to destroy herds of horses in a
mythical land.
Animated.
----------------------------------------------------------------------------------------------------------------------------------
16:00 213 CKALDT 1:00 HD Series CityLine; Talk,
House/garden, How-to; Casual summer looks from Yorkdale;
wearing white into
fall; wedding guest etiquette. Host: Tracy Moore
[2014-07-11]
272 TDCCHD 0:30 HD Series How It's Made; {Ep.
1301} Science; Hammers; Swiss cheese; roller skates;
colored pencils.
Host: Lynne Adams [2009-05-01]
271 SPCECHD 0:30 HD NEW Series InnerSPACE: San Diego
Comic-Con Movie Special; Talk, Entertainment; Ajay
and Teddy learn about
films featured at San Diego Comic-Con.
[2014-08-08]
206 WWJDT 0:30 HD NEW Series Insider, The; {Ep.
5175} Newsmagazine, Entertainment; Ryan Guzman
(''Step Up: All
In''). Guest: Ryan Guzman; Host: Kevin Frazier, Thea
Andrews [2014-08-08]
212 CTV2AHD 1:00 HD NEW Series Marilyn Denis Show,
The; {Ep. BO4081} Talk; Sexy swim wear makeovers;
summer color
combinations; four ways to wear cocktail dresses. Guest:
Peter Papapetrou,
Gretta Monahan, Alexis Honce; Host: Marilyn Denis
[2014-08-08]
273 BRAVCHD 1:00 Series Missing: White Whale;
Crime drama; The prime suspect in a new case is
someone Brooke was
unable to convict previously. Actor: Gloria Reuben,
Caterina Scorsone,
Dean McDermott, Justina Machado [2003-11-22]
256 SHCSCHD 1:00 HD Series Rookie Blue: Messy
Houses; {Ep. 305} Crime drama; Andy answers a
domestic disturbance
call to find the social worker on the scene is her
estranged mother;
Sam, Nick, Chris and Gail search for a mother and her
two children; a stash
of guns recovered by Detective Barber turns up on
the streets. Actor:
Lyriq Bent, Enuka Okuma, Ben Bass, Charlotte
Sullivan, Missy
Peregrym, Gregory Smith, Travis Milne, Melanie Nicholls-
King, Matt Gordon,
Noam Jenkins, Peter Mooney; Director: T.W. Peacocke;
Executive Producer:
Tassie Cameron, Ilana Frank, David Wellington,
Noreen Halpern, John
Morayniss; Guest Star: Krista Bridges, Barbara
Williams, Peter
MacNeill, Jonathan Goad; Writer: Sherry White
[2012-07-05]
258 TLCHDBO 0:30 HD Series Say Yes to the Dress:
Atlanta: Once Upon a Bride; {Ep. 709} Reality,
Fashion; A bride
looks for a fairytale princess gown; a perfect gown for
a vow renewal
ceremony; a bride hopes for her aunt's approval. Actor:
Lori Allen, Monte
Durham, Robin Gibbs, Flori Waters [2014-06-20]
253 LIFECHD 1:36 HD Movie Sight Unseen (2008);
Suspense; A scheming woman (Laura Mennell) draws a
detective into a web
of deceit and murder. Actor: Kevin McNulty, Jerry
Wasserman, David
Orth, David Lewis, Laura Mennell, Andrew Francis, Alvin
Sanders, Tracey Gold;
Director: David Golden
255 AETVHD 0:30 HD Series Storage Wars: Melee
in the Maze; {Ep. 2} Reality, Auction, Collectibles;
Handcrafted bamboo
fishing rods; Brandi outwits her competition. Actor:
Dan Dotson, Laura
Dotson, Darrell Sheets, Jarrod Schulz, Brandi
Passante, Barry
Weiss, Dave Hester; Executive Producer: Robert Sharenow,
Thom Beers, Philip D.
Segal, Elaine Frontain Bryant; Narrator: Thom
Beers [2010-12-08]
285 SCHD02 1:56 HD Movie Virginia (2010);
Drama; Language, Adult Situations; A troubled single
mother (Jennifer
Connelly) carries on an affair with a married sheriff
(Ed Harris) and
struggles to raise her son in their small-minded town.
Actor: Yeardley
Smith, Amy Madigan, Carrie Preston, Ed Harris, Emma
Roberts, Jennifer
Connelly, Toby Jones, Harrison Gilbertson, Alex Frost,
Jo Yang, Meighan
Gerachis, Paul Walter Hauser; Director: Dustin Lance
Black; Executive
Producer: Yeardley Smith, Gus Van Sant, Greg Seuss, Jay
Froberg; Producer:
Christine Vachon, Hopwood Depree XIV, Scott J. Brooks
224 SN1HD 5:00 HD NEW Series WTA Tennis: Rogers
Cup: Women's Quarterfinals; Sports event, Tennis;
From Montreal.
[2014-08-08]
211 CICTDT 0:59 HD NEW Series Young and the
Restless, The; Soap; Actor: Eric Braeden, Melody Thomas
Scott, Peter Bergman,
Michelle Stafford, Jeff Branson, Jessica Collins,
Sharon Case, Doug
Davidson, Daniel Goddard, Michael Graziadei, Jessica
Heap, Amelia Heinle,
Elizabeth Hendrickson, Bryton James, Robert
Adamson, Melissa
Claire Egan, Angell Conwell, Christian LeBlanc, Blake
Hood, Kate Linder
[2014-08-11]
----------------------------------------------------------------------------------------------------------------------------------
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
