Stavek SELECT z omejitvami

Jezik SQL podpira številne uporabne operaterje, specifične za delo z besedilnimi podatki, kot so na primer primerjava nizov in ujemanje vzorcev z nadomestnimi znaki. Tabela prikazuje nekaj najpogostejših operaterjev za besedilne podatke:

Operator Pogoj Primer
= enakost nizov, loči med velikimi in malimi črkami ime_stolpca = 'abc'
!= ali <> neenakost nizov, loči med velikimi in malimi črkami ime_stolpca != 'abcd'
LIKE enakost nizov, ne loči med velikimi in malimi črkami ime_stolpca LIKE 'ABC'
NOT LIKE neenakost nizov, ne loči med velikimi in malimi črkami ime_stolpca NOT LIKE 'ABCD'


% kjerkoli v nizu za ujemanje z zaporedjem nič ali več znakov
(samo z LIKE ali NOT LIKE)
ime_stolpca LIKE '%ot%'
(se ujema z nizi 'otok', 'pot', 'potok' idr.)
_ (podčrtaj) kjerkoli v nizu za ujemanje z natanko enim znakom
(samo z LIKE ali NOT LIKE)
ime_stolpca LIKE "po_"
(se ujema z nizi 'pot', 'pok' in 'pol'; ne pa s 'po' ali 'polje')
IN (...) besedilo je na seznamu vrednosti ime_stolpca IN ('A', 'B', 'C')
NOT IN (...) besedila ni na seznamu vrednosti ime_stolpca NOT IN ('D', 'E')

Čeprav je večina implementacij podatkov baz precej učinkovita pri uporabi operaterjev za delo z besedilnimi podatki, je iskanje po celotnem besedilu najbolje prepustiti namenskim knjižnicam, kot sta na primer Apache Lucene ali Sphinx. Te knjižnice so zasnovane posebej za iskanje po celotnem besedilu, posledično pa so učinkovitejše in lahko podpirajo več različnih funkcij iskanja, vključno z internacionalizacijo in naprednimi poizvedbami.

Tabela: film
idnaslovreziserstudio_idletodolzinaocena
1The Little MermaidRon ClementsDisney1989837.6
2Beauty and the BeastGary TrousdaleDisney1991848
3AladdinRon ClementsDisney1992908
4The Lion KingRoger AllersDisney1994888.5
5Toy StoryJohn LasseterPixar1995818.3
6PocahontasMike GabrielDisney1995816.7
7The Hunchback of Notre DameKirk WiseDisney1996916.9
8HerculesJohn MuskerDisney1997937.3
9A Bug's LifeJohn LasseterPixar1998957.2
10AntzEric DarnellDreamWorks1998836.6
11The Prince of EgyptBrenda ChapmanDreamWorks1998997.1
12MulanBarry CookDisney1998887.6
13Toy Story 2John LasseterPixar1999927.9
14TarzanChris BuckDisney1999887.3
15Chicken RunPeter LordDreamWorks2000847
16DinosaurRalph Zondag Disney2000826.5
17Monsters, Inc.Pete DocterPixar2001928.1
18ShrekAndrew AdamsonDreamWorks2001907.9
19Ice AgeChris WedgeBlue Sky2002817.5
20Spirit: Stallion of the CimarronKelly AsburyDreamWorks2002837.1
21Lilo & StitchDean DeBloisDisney2002857.2
22Finding NemoAndrew StantonPixar20031008.1
23Sinbad: Legend of the Seven SeasPatrick GilmoreDreamWorks2003866.7
24The IncrediblesBrad BirdPixar20041158
25Shrek 2Andrew AdamsonDreamWorks2004937.2
26RobotsDavid Lindsay-AbaireBlue Sky2005916.3
27MadagascarEric DarnellDreamWorks2005866.9
28Chicken LittleMark DindalDisney2005815.8
29CarsJohn LasseterPixar20061177.1
30Ice Age: The MeltdownCarlos SaldanhaBlue Sky2006916.8
31Over the HedgeKarey KirkpatrickDreamWorks2006836.7
32Flushed AwayDavid BowersDreamWorks2006856.6
33RatatouilleBrad BirdPixar20071118
34Shrek the ThirdChris MillerDreamWorks2007936.1
35Bee MovieSimon J. SmithDreamWorks2007916.1
36Meet the RobinsonsStephen J. AndersonDisney2007956.9
37Wall-EAndrew StantonPixar2008988.4
38Dr. Seuss' Horton Hears a Who!Jimmy HaywardBlue Sky2008866.8
39Kung Fu PandaMark OsborneDreamWorks2008927.6
40Madagascar: Escape 2 AfricaEric DarnellDreamWorks2008896.7
41Monsters vs. AliensRob LettermanDreamWorks2008946.5
42BoltChris WilliamsDisney2008966.9
43UpPete DocterPixar2009968.2
44Ice Age: Dawn of the DinosaursCarlos SaldanhaBlue Sky2009947
45The Princess and the FrogJohn MuskerDisney2009977.1
46Toy Story 3Lee UnkrichPixar20101038.3
47Despicable MePierre CoffinIllumination2010957.7
48How to Train Your DragonDean DeBloisDreamWorks2010988.1
49Shrek Forever AfterMike MitchellDreamWorks2010936.3
50MegamindTom McGrathDreamWorks2010957.3
51TangledNathan GrenoDisney20101007.8
52Cars 2John LasseterPixar20111066.2
53RioCarlos SaldanhaBlue Sky2011966.9
54Kung Fu Panda 2Jennifer Yuh NelsonDreamWorks2011907.2
55Puss in BootsChris MillerDreamWorks2011906.6
56BraveBrenda ChapmanPixar2012937.1
57Dr. Seuss' The LoraxChris RenaudIllumination2012866.4
58Ice Age: Continental DriftSteve MartinoBlue Sky2012886.6
59Madagascar 3: Europe's Most WantedEric DarnellDreamWorks2012936.8
60Rise of the GuardiansPeter RamseyDreamWorks2012977.3
61Wreck-It RalphRich MooreDisney20121017.7
62Monsters UniversityDan ScanlonPixar20131047.3
63Despicable Me 2Pierre CoffinIllumination2013987.4
64EpicChris WedgeBlue Sky20131026.7
65The CroodsKirk DeMiccoDreamWorks2013987.2
66TurboDavid SorenDreamWorks2013966.4
67FrozenChris BuckDisney20131027.5
68Rio 2Carlos SaldanhaBlue Sky20141016.3
69Mr. Peabody & ShermanRob MinkoffDreamWorks2014926.8
70How to Train Your Dragon 2Dean DeBloisDreamWorks20141027.8
71Penguins of MadagascarEric DarnellDreamWorks2014926.7
72Big Hero 6Don HallDisney20141027.8
73Inside OutPete DocterPixar2015958.2
74The Good DinosaurPeter SohnPixar2015936.7
75MinionsPierre CoffinIllumination2015916.4
76The Peanuts MovieSteve MartinoBlue Sky2015887.1
77HomeTim JohnsonDreamWorks2015946.6
78Finding DoryAndrew StantonPixar2016977.3
79The Secret Life of PetsChris RenaudIllumination2016876.5
80SingGarth JenningsIllumination20161087.1
81Ice Age: Collision CourseMike ThurmeierBlue Sky2016945.7
82Kung Fu Panda 3Alessandro CarloniDreamWorks2016957.1
83TrollsMike MitchellDreamWorks2016926.5
84ZootopiaByron HowardDisney20161088
85MoanaJohn MuskerDisney20161077.6
86Cars 3Brian FeePixar20171026.8
87CocoLee UnkrichPixar20171058.4
88Despicable Me 3Pierre CoffinIllumination2017896.3
89FerdinandCarlos SaldanhaBlue Sky20171086.7
90The Boss BabyTom McGrathDreamWorks2017976.3
91Captain Underpants: The First Epic MovieDavid SorenDreamWorks2017896.2
92Incredibles 2Brad BirdPixar20181187.7
93Dr. Seuss' The GrinchScott MosierIllumination2018866.3
94Ralph Breaks the InternetRich MooreDisney20181127.1
95How to Train Your Dragon: The Hidden WorldDean DeBloisDreamWorks20191047.7
96The Secret Life of Pets 2Chris RenaudIllumination2019866.5
97Toy Story 4Josh CooleyPixar20191007.8
98AbominableJill CultonDreamWorks2019977
99Frozen IIChris BuckDisney20191036.9
100Spies in DisguiseNick BrunoBlue Sky20191026.8
101OnwardDan ScanlonPixar20201027.4
102Trolls 2: World TourWalt DohrnDreamWorks2020906.1
103The Croods: A New AgeJoel CrawfordDreamWorks2020957
104SoulPete DocterPixar20201008.1
105Raya and the Last DragonDon HallDisney20211077.5
106Spirit UntamedElaine BoganDreamWorks2021885.5
107LucaEnrico CasarosaPixar2021957.4
108The Boss Baby 2: Family BusinesTom McGrathDreamWorks20211075.8
109EncantoByron HowardDisney20211027.2
110Sing 2Garth JenningsIllumination20211107.4
SELECT * FROM film;
XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX

Vaja 3 – naloge

  1. Izpiši vse filme Svet igrač (ang. Toy Story).
  2. Izpiši vse filme, ki jih je režiral John Lasseter.
  3. Izpiši vse filme, ki jih ni režiral John Lasseter.
  4. Izpiši naslove vseh filmov studia Pixar.
  5. Izpiši naslove vseh filmov vseh studiev, razen studia Pixar.
  6. Izpiši vse filme, katerih naslovi se začnejo na črko A.
  7. Izpiši vse filme, katerih naslovi se končajo na črko A.
  8. Izpiši vse filme, katerih naslovi vsebujejo besedo Age.
Ne gre? Klikni gumb »Rešitev«.