IN2090-ukesoppgaver: Uke 10
Ytre joins og mengdeoperatorer i SQL
Filmdatabasen
Bruk filmdatabasen for å løse følgende oppgaver.
Oppgave 1
Finn ut hvor mange produksjoner (altså alt som forekommer I
filmparticipation
-tabellen) hver person med etternavn
'Abbott'
har deltatt i (husk å få med de som har deltatt i
0
filmer). (243 rader)
Løsningsforslag
SELECT p.personid, p.firstname, p.lastname, count(fp.personid) AS count
FROM person AS p
LEFT OUTER JOIN filmparticipation AS fp
USING (personid)
WHERE p.lastname = 'Abbott'
GROUP BY p.personid, p.firstname, p.lastname;
-- personid | firstname | lastname | count
-- ----------+-------------------+----------+-------
-- 2488 | Chris | Abbott | 17
-- 4617 | Frank | Abbott | 3
-- 6514 | Paris | Abbott | 4
-- 966 | Lee | Abbott | 17
-- 5922 | Esther | Abbott | 1
-- 2471 | Michael W. | Abbott | 4
-- 14401 | Rob | Abbott | 1
-- 5970 | Georganne | Abbott | 1
-- 14481 | Shawn | Abbott | 6
-- 2375 | Leigh | Abbott | 13
-- 13329 | David | Abbott | 3
-- 13489 | Frankie | Abbott | 6
-- 13153 | Billy | Abbott | 1
-- 6642 | Susan | Abbott | 5
-- 5129 | Odin | Abbott | 0
-- 2616 | Jess | Abbott | 1
-- ...
-- 2536 | Eleanor Halliwell | Abbott | 3
-- 6370 | Mishael | Abbott | 0
-- 5682 | Carly | Abbott | 2
-- 6498 | Pamela | Abbott | 11
-- (243 rows)
Oppgave 2
Finn tittel på alle Western-filmer laget etter 2007 som ikke har en rating. (14 rader)
Løs oppgaven på ved å bruke:
NOT IN
LEFT OUTER JOIN
EXCEPT
NOT EXISTS
Løsningsforslag
a)
SELECT f.title
FROM film AS f
INNER JOIN filmgenre AS g USING (filmid)
WHERE f.filmid NOT IN (SELECT filmid FROM filmrating) AND
= 'Western' AND
g.genre > 2007; f.prodyear
b)
SELECT f.title
FROM film AS f
INNER JOIN filmgenre AS g USING (filmid)
LEFT OUTER JOIN filmrating AS r USING (filmid)
WHERE r.filmid IS NULL AND
= 'Western' AND
g.genre > 2007; f.prodyear
c)
SELECT title
FROM film
WHERE prodyear > 2007 AND
IN (
filmid SELECT filmid
(FROM filmgenre
WHERE genre = 'Western')
EXCEPT
SELECT filmid
(FROM filmrating));
d)
SELECT f.title
FROM film AS f
INNER JOIN filmgenre AS fg
USING (filmid)
WHERE f.prodyear > 2007 AND
= 'Western' AND
fg.genre NOT EXISTS (
SELECT *
FROM filmrating AS r
WHERE r.filmid = f.filmid);
-- title
-- -----------------------------------
-- Hard Ride, The
-- Vultures
-- Wild Michigan
-- Manhunt
-- Saxon
-- Blood Meridian
-- Boone's Lick
-- Busted Down on Bourbon Street
-- Brigands of Rattleborge, The
-- Dead West
-- Westworld
-- Good, the Bad, and the Weird, The
-- Moses Taite's War
-- Last Horseman, The
-- (14 rows)
Oppgave 3
Finn antall filmer som enten er komedier, eller som Jim Carrey har spilt i. (1 rad)
Løsningsforslag
SELECT count(DISTINCT filmid) AS nr_movies
FROM film
WHERE filmid IN (
SELECT filmid
(FROM filmgenre
WHERE genre = 'Comedy')
UNION
SELECT fp.filmid
(FROM person AS p
INNER JOIN filmparticipation AS fp
USING (personid)
WHERE p.firstname = 'Jim' AND p.lastname = 'Carrey'));
-- eller
SELECT count(*) AS nr_movies
FROM (SELECT DISTINCT filmid
FROM film
WHERE filmid IN (
SELECT filmid
(FROM filmgenre
WHERE genre = 'Comedy')
UNION
SELECT fp.filmid
(FROM person AS p
INNER JOIN filmparticipation AS fp
USING (personid)
WHERE p.firstname = 'Jim' AND p.lastname = 'Carrey'))) AS t;
-- nr_movies
-- -------
-- 81098
-- (1 row)
Oppgave 4
Finn tittel på alle filmer som som Jim Carrey har spilt i, men som ikke er komedier. (62 rader)
Løsningsforslag
SELECT title
FROM film
WHERE filmid IN (
SELECT fp.filmid
(FROM person AS p
INNER JOIN filmparticipation AS fp
USING (personid)
WHERE p.firstname = 'Jim' AND p.lastname = 'Carrey')
EXCEPT
SELECT filmid
(FROM filmgenre
WHERE genre = 'Comedy'));
-- eller
SELECT f.title
FROM film AS f
INNER JOIN filmparticipation AS fp USING (filmid)
INNER JOIN person AS p USING (personid)
WHERE p.firstname = 'Jim' AND
= 'Carrey' AND
p.lastname NOT IN
f.filmid SELECT filmid
(FROM filmgenre
WHERE genre = 'Comedy');
-- title
-- --------------------------------------------------------------------------------------------------------------
-- Eternal Sunshine of the Spotless Mind
-- Ripley's Believe It or Not!
-- Horton Hears a Who
-- AFI's 100 Years... 100 Stars
-- Itsy Bitsy Spider, The
-- 50 Greatest Comedy Films, The
-- Mo' Funny: Black Comedy in America
-- Inside the Mind of Michel Gondry
-- In My Life
-- Andy Kaufman's Really Big Show
-- Forbes Celebrity 100: Who Made Bank?
-- ¿De qué te ríes?
-- Majestic, The
-- 68th Annual Academy Awards, The
-- When Stand-Up Comics Ruled the World
-- Teen Choice Awards 2003, The
-- 71st Annual Academy Awards, The
-- Junket Whore
-- Playboy: Inside the Playboy Mansion
-- 1999 MTV Movie Awards
-- Return to Edge City
-- 76th Annual Academy Awards, The
-- Lemony Snicket's A Series of Unfortunate Events
-- Terrible Tragedy: Alarming Evidence from the Making of the Film - A Woeful World, A
-- Concert for New York City, The
-- Masters of Illusion: The Wizards of Special Effects
-- Celebrity Debut
-- Number 23, The
-- 30th Annual People's Choice Awards, The
-- Tribute to Sam Kinison, A
-- Truman Show, The
-- Nickelodeon Kids' Choice Awards '04
-- Riddle Me This: Why Is Batman Forever?
-- Playboy: Playmate Pajama Party
-- Look Inside 'Eternal Sunshine of the Spotless Mind', A
-- Award Show Awards Show, The
-- Doing Time on Maple Drive
-- Batman Forever
-- American Film Institute Salute to Clint Eastwood, The
-- AFI's 100 Years, 100 Laughs: America's Funniest Movies
-- Nickelodeon Kids' Choice Awards '03
-- How's It Going to End? The Making of 'The Truman Show'
-- AFI Tribute to Meryl Streep
-- Mike Hammer: Murder Takes All
-- American Film Institute Salute to Steven Spielberg, The
-- 62nd Annual Golden Globe Awards, The
-- Moving Image Salutes Ron Howard
-- Still Dumb After All These Years
-- Christmas from Hollywood
-- Jim Carrey Spotlight
-- Hollywood Salutes Nicolas Cage: An American Cinematheque Tribute
-- MTV Video Music Awards 2000
-- America: A Tribute to Heroes
-- Jim Carrey Uncensored
-- 69th Annual Academy Awards, The
-- 1995 MTV Movie Awards
-- 2000 Blockbuster Entertainment Awards
-- Terrible Tragedy: Alarming Evidence from the Making of the Film - Costumes and Other Suspicious Disguises, A
-- Cartoon Logic
-- Introducing Cameron Diaz
-- Dead Pool, The
-- Shadows of the Bat: The Cinematic Saga of the Dark Knight - Reinventing a Hero
-- (62 rows)
Northwind
Bruk Northwind-databasen for å løse følgende oppgaver.
Oppgave 5
Finn navnet på alle firmaer (Customers og Suppliers) som kommer fra Norge eller Sverige. (6 rader)
Løsningsforslag
SELECT company_name
(FROM customers
WHERE country = 'Sweden' OR
= 'Norway')
country UNION
SELECT company_name
(FROM suppliers
WHERE country = 'Norway' OR
= 'Sweden');
country
-- company_name
-- --------------------
-- Norske Meierier
-- Berglunds snabbköp
-- Svensk Sjöföda AB
-- Santé Gourmet
-- Folk och fä HB
-- PB Knäckebröd AB
-- (6 rows)
Oppgave 6
Bruk EXISTS
for å finne navnet på alle kunder som har
kjøpt Pavlova. (31 rader)
Løsningsforslag
SELECT c.company_name
FROM customers AS c
WHERE EXISTS (
SELECT *
FROM orders AS o
INNER JOIN order_details AS d USING (order_id)
INNER JOIN products AS p USING (product_id)
WHERE o.customer_id = c.customer_id AND
= 'Pavlova');
p.product_name --
-- company_name
-- --------------------------------
-- Berglunds snabbköp
-- Bon app'
-- Ernst Handel
-- Frankenversand
-- France restauration
-- Furia Bacalhau e Frutos do Mar
-- Hungry Owl All-Night Grocers
-- Königlich Essen
-- La maison d'Asie
-- Lehmanns Marktstand
-- LILA-Supermercado
-- LINO-Delicateses
-- Mère Paillarde
-- Morgenstern Gesundkost
-- Old World Delicatessen
-- Ottilies Käseladen
-- Piccolo und mehr
-- QUICK-Stop
-- Rancho grande
-- Rattlesnake Canyon Grocery
-- Ricardo Adocicados
-- Richter Supermarkt
-- Santé Gourmet
-- Save-a-lot Markets
-- Seven Seas Imports
-- Simons bistro
-- Spécialités du monde
-- The Big Cheese
-- Wartian Herkku
-- White Clover Markets
-- Wilman Kala
-- (31 rows)
Oppgave 7
Finn ut hvor mange kunder som befinner seg i samme land som hver leverandør (Supplier). Resultatet skal være to kolonner, en med leverandørnavnet, og en kolonne med antall kunder fra samme land. Sorter resultatet etter antall kunder I synkende rekkefølge. (29 rader)
Løsningsforslag
SELECT s.company_name, count(c.customer_id) AS num_customers
FROM suppliers AS s
LEFT OUTER JOIN customers AS c
USING (country)
GROUP BY s.supplier_id
ORDER BY num_customers DESC;
-- company_name | num_customers
-- ----------------------------------------+---------------
-- New England Seafood Cannery | 13
-- Bigfoot Breweries | 13
-- New Orleans Cajun Delights | 13
-- Grandma Kelly's Homestead | 13
-- Heli Süßwaren GmbH & Co. KG | 11
-- Plutzer Lebensmittelgroßmärkte AG | 11
-- Gai pâturage | 11
-- Nord-Ost-Fisch Handelsgesellschaft mbH | 11
-- Aux joyeux ecclésiastiques | 11
-- Escargots Nouveaux | 11
-- Refrescos Americanas LTDA | 9
-- Exotic Liquids | 7
-- Specialty Biscuits, Ltd. | 7
-- Cooperativa de Quesos 'Las Cabras' | 5
-- Formaggi Fortini s.r.l. | 3
-- Ma Maison | 3
-- Pasta Buttini s.r.l. | 3
-- Forêts d'érables | 3
-- PB Knäckebröd AB | 2
-- Lyngbysild | 2
-- Svensk Sjöföda AB | 2
-- Karkki Oy | 2
-- Norske Meierier | 1
-- Pavlova, Ltd. | 0
-- Leka Trading | 0
-- Zaanse Snoepfabriek | 0
-- Tokyo Traders | 0
-- G'day, Mate | 0
-- Mayumi's | 0
-- (29 rows)
Bonusoppgave
Oppgaven under er hentet fra boken Database Systems - The Complete Book, Second Edition (Garcia-Molina, Ullman, Widom).
Let a
and b
be integer-valued attributes
that may be NULL
in some tuples. For each of the following
conditions (as may appear in a WHERE
clause), describe
exactly the set of (a, b)
tuples that satisfy the
condition, including the case where a
and/or b
is NULL
.
a < 50 OR a >= 50
a = 0 OR b = 10
a = 20 AND b = 10
a = b
a > b
Løsningsforslag
a < 50 OR a >= 50
: Alle tupler hvora
ikke erNULL
.a = 0 OR b = 10
: Alle tupler hvora=0
ogb
enten erNULL
eller har en verdi OG alle tupler hvorb=10
oga
enten erNULL
eller har en verdi.a = 20 AND b = 10
: Hvisa
ogb
er de eneste attributtene er(a,b)=(20,10)
eneste mulige kandidat.a = b
: Hvis en ava
ellerb
erNULL
, er resultatet av beregningena=b
ogsåNULL
. Denne verdien er ikke lik'true'
. Eneste tupler som tilfredstiller denne er derfor de hvora
ogb
er ikke-NULL
og har samme verdi.a > b
: Tilsvarende. For at verdien skal kunne bli sann ('true'
), må bådea
ogb
ikke væreNULL
. Dessuten må verdien ia
være større enn den ib
.