Gagnasafnsfr 6. kafli

πL(σC(R1×R2×...×Rn)) Here, L is an arbitrary list of attributes, and C is an arbitrary condition. The list of relations R1, R2, ..., Rn may include the same relation repeated several times, in which case appropriate renaming may be assumed applied to the Ri's. Show how to express any query of this form in SQL

A systematic way to handle this problem is to create a tuple variable for every Ri, i=1,2,...,n, whether we need to (because Ri appears more than once) or not. That is, the FROM clause is FROM R1 AS T1, R2 AS T2,...,Rn AS Tn. Now, build the WHERE clause from C by replacing every reference to some attribute A of Ri by Ti.A. Also, build the SELECT clause from list of attributes L by replacing every attribute A of Ri by Ti.A.

Skipanirnar UNION, INTERSECT og DIFFERENCE eyða átómatískt endurtekningum. Til að koma í veg fyrir það er hægt að nota lykilorðið:

ALL dæmi: R INTERSECT ALL S

SQL 3 major standars

ANSI (american national standars Institue) SQL SQL-92 eða SQL2, uppfærð útgafa tekin upp 1992 SQL-99 epa SQL3 extendar SQL2

Hvað færðu úr SELECT * FROM Movies WHERE studioName='Disney' AND year=1990;

Allar myndir framleiddar af Disney árið 1990

Hvað færðu úr SELECT title, length FROM Movies WHERE studioName='Disney' AND year=1990;

Titil og lengd allra mynda framleidda af Disney 1990

Leyfileg tákn

'foo' || 'bar' = 'foobar'

Túlkanir á NULL (3)

(1) Value unknown (2) Value inapplicablem tildæmis í dálk "Spouse" hjá aðila sem er ógiftur (3) Value Withheld. Tildæmis óskráð símanr

MovieStar(name, address, gender, birthdate) MovieExec (name, address, cert#, networth) Nafn og heimilisfang allra kvk stjörnur sem eru líka movieExec og með networth yfir 10.000

(SELECT name, address FROM movieStar WHERE gender='F') INTERSECT (SELECT name, address FROM movieExec WHERE networth>10.000) ss. fáum út tvö vensl og tökum sammengið af þeim sem er svarið. Einnig, taka eftir svigunum í kringum skipanirnar, þarf að hafa sviga ef notað er svona skipun á milli tveggja skipuna (gildir einnig með union) Einnig, intersect virkar hér vegna þess að venslin hafa eins dálka (name og address)

Movies(title, year, length, genre, studioName, producerC#) StarsIn(movieTitle, movieYear, starName) Alla titla og árgang mynda sem koma fram í annaðhvort Movies eða starsIn

(SELECT title, year FROM movie) UNION (SELECT movieTitle AS title, movieYear AS year FROM starsIn); þessi set ættu að vera eins, en þó gæti komið fyrir að til séu myndir án leikara meðal annars

Movie(title, year, length, inColor, studioName, producerC#) Finna myndir sem birtast oftar en einu sinni

1 SELECT title 2 FROM Movie Old 3 WHERE year < ANY 4 (SELECT year 5 FROM Movie 6 WHERE title = Old.title 7 ); Skoðum fyrst hlutfyrirspurnina, 4-7 Vitum ekki hvaða gildi Old.title hefur Fáum það í "ytri" fyrirspurninni þegar við rennum í gegnum hana, hver tuple/nd gefur gildi Old.title Ef mynd kemur fram tvisvar í venslinnu, kemur hún fram 1 sinni í niðurstöðuvenslinu Ef mynd kemur fram 3svar í venslinnu kemur hún fram 2 í niðurstöðuvenslin osfrv. Old her er alias fyrir movie, sést því það er engin komma á milli í FROM

Reglur sem þarf að hafa í huga í sambandi við HAVING lykilorðið

1) Hópvirkjar í HAVING eiga aðeins við þær raðir af hópnum sem er verið að testa 2) Í HAVING má nota hópvirkja á eigindi úr FROM 3) Aðeins þau eigindi sem eru í Group by listanum mega birtast óhópvirkjuð

Movie(title, year, length, inColor, studioName, producerC#) StarsIn(movieTitle, movieYear, starName) Viljum sameina þessi tvö vensl, með það skilyrði að einu ndirnar(raðirnar) sem eru sameinuð eiga við um sömu myndina. Ss. title og year er eins

Movies JOIN starsIn ON title = movieTitle AND year= movieYear; Útkoman er 9 dálka vensl. Röð úr movies og röð úr starIn sameinast aðeins ef þau eru sammála um bæði titil og ár sem þýðir að tveir dálkar eru endurteknir (title og movietitle eru eins og year og movieYear eru eins)

Reikningar með NULL NULL + gildi NULL x gildi NULL + NULL

NULL + gildi NULL x gildi NULL + NULL =NULL

Samanburður með NULL NULL = gildi NULL > NULL

NULL = gildi NULL > NULL =UNKNOWN (boolean gildi líkt og True og False)

Þó það sé hægt að reikna með breytu sem hefur gildið NULL verðum við að muna að

NULL sjálft er ekki breyta og því ekki hægt að nota sem opernad (þolandi)

Hópvirkjar SUM, AVG, MIN, MAX, COUNT

Nýttir í SELECT SELECT COUNT(*) telur allar raðirnar SELECT COUNT(starname) telur öll gildinn í starname dálkinum. Ef við viljum ekki telja endurtekinn gildi: SELECT COUNT(DISTINCT starname)

Hver er munurinn á left og right outer join? MovieStar(name, gender) MovieExec(name, cert#) Outerjoin myndi gefa: name gender cert# Mary F 1234 Tom M NULL George NULL 2133

Right outer join myndi gefa: name gender cert# Mary F 1234 George NULL 2133 ss. með hægri join er tekið í burtu þá röð með NULL í hægri dálkanum af þeim sem voru ekki joinaðir (Sorry Tom) (það er að segja þá dálka úr MovieExec sem voru ekki joinaðir = cert#) Left outer join myndi gefa name gender cert# Mary F 1234 Tom M NULL Öfugt við hin þá tekur þessi vinstri dálkinn sem fékk ekki að vera með (það er að segja þá dálka úr Moviestar sem voru ekki joinaðir=gender)

MovieStar(name, address, gender, birthdate) MovieExec (name, address, cert#, networth) Viljum finna movieExec og stjörnu með sama heimilisfang

SELECT MovieStar.name, MovieExec.name FROM movieStar, MovieExec WHERE movieStar.address = movieExec.address; (vel gert) note, má alltaf nota . eitthvað jafnvel þó enginn miskilningur gæti átt sér stað ef honum væri sleppt

MovieStar(name, address, gender, birthdate) Viljum finna tvær stjörnur með sama heimilisfang

SELECT Star1.name, Star2.name FROM movieStar WHERE Star1.name < Star1.name AND Star1.address = Star2.address; Til að passa að ekki sé verið að tala um sömu stjörunna tökum við fram að star1 þarf að vera á undann í stafrófinu Ef við myndum nota <> myndum við fá stjörnur sem eru giftar tvisvar.

SELECT title FROM Movies WHERE title LIKE 'Star _ _ _ _' ;

Titil allra mynda sem byrja á Star og hafa svo 4 stafa orð eftir á (td. Star Wars, Star Trek) ss. "Star something"

Þegar raðir innihalda NULL þarf aðeins að passa nokkra bluti með grouping og hópvirkja

1) Null er hunsað af öllum hópvirkjum. 2) við hópmyndun (i.e. GROUP BY) er NULL eins og hvaða gildi sem er. 3) Ef notað er hópvirkja á tómt vensl/tóman dálk þá er niðurstaðan alltaf null NEMA með Count, count á tómt vensl er 0 Dæmi: Venslið R(A,B): A B NULL NULL SELECT A, COUNT(B) FROM R GROUP BY A; Niðurstaðan er einn röð/nd = (NULL, 0) Afturámóti gefur: SELECT A, SUM(B) FROM R GROUP BY A; Útkomuna (NULL, NULL)

Natural join er öðruvísi en theta join, hvernig?

1) the join condition is that all pairs of attributes from the two relations having a common name are equated, and there are no other conditions 2) one of each pair of equated attributes is projected out 3) á manna máli: Segjum við séum með R(A,B) og S(B, C) nátturulegt join væri hægt ef R.B=S.B og myndi útkoman vera V(A, B, C) (ss. B'in tvo hafa verið sameinuð í eitt flott B)

Movie(title, year, length, inColor, studioName, producerC#) Moviestar(name, address, gender, birthday) Hvað gefur skipunnin: Movie CROSS JOIN Moviestar

10 dálka vensl með öll eigindi úr Movie og moviestar Dæmi: Pretty Woman|1990|119|1|Disney|999|Jane Fonda|Turner Av.|F|7/7/77 Pretty Woman|1990|119|1|Disney|999|Alec Baldwin|Baldwin Av.|M|6/7/77 Pretty Woman|1990|119|1|Disney|999|Kim Basinger|Baldwin Av.|F|5/7/79 Pretty Woman|1990|119|1|Disney|999|Harrison Ford|Prefect Rd.|M|5/5/55 Pretty Woman|1990|119|1|Disney|999|Debra Winger|A way|F|5/6/78 Pretty Woman|1990|119|1|Disney|999|Jack Nicholson|X path|M|5/5/49

Lestu úr SELECT * FROM Movies WHERE studioName='Disney' AND year= 1990 ORDER BY length, title;

Allar myndir framleiddar af Disney árið 1990 raðað eftir length svo title. (ASC er gefið, DESC væri öfug röð)

(SELECT name, address FROM movieStar) EXCEPT (SELECT name, address FROM movieExec) Skilar hverju?

Allar þær stjörnur sem eru ekki movieexec

TRUE, FALSE og UNKNOWN Hvað byrtir SELECT skipun?

Bara TRUE, FALSE og UNKNWON ekki birt

If a query has a SELECT clause SELECT A B How do we know whether A and B are two different attributes or B is an alias of A

Check if there is a comma between A and B, if there is one, B is an attribute, if not, then B is an alias. If they are two different attributes, there will be a comma between them. Remember, in SQL, two names with no punctuation between them usually indicates that the second is an alias for the first

Date,time og timestamp

DATE '1948-05-14' TIME '15:00:02.5' TIME '12:00:00-8:00' = PST (8 tímum á eftir GMT) TIMESTAMP '1948-05-14 12:00:00'

Viljum eyða öllum movie execs sem hafa lágt networth (minna en 10 milljónir)

DELETE FROM MovieExec WHERE networth<10000000;

Hvernig eyðiru gildum úr venslum?

DELETE FROM R WHERE <condition>

StarsIn(movieTitle, movieYear, starName) Viljum eyða greyinu Sydney Greenstreet úr The Maltese Falcon

DELETE FROM starsIn WHERE movieTitle='The Maltese Falcon' AND movieYear=1942 AND starName = 'Sydney Greenstreet';

Ef við viljum ekki endurtekningar í niðurstöðu vensli er hægt að nota lykilorðið

DISTINCT dæmi: SELECT DISTINCT name (SELECT geymir default endurtekningar og eyðir þeim aðeins ef skipað fyrir af DISTINCT) Ekki sniðug skipun uppá að hún tekur soldin tíma að keyra

Með strengja samanburð 'fodder' < 'foo'

Fer eftir stafaröð. Fodder er minni (efra á listanum) en foo vegna d er ofarlegra í stafrófinu en o

Um hlutfyrirspurnir

Geta skilað einni breytu sem hægt er að bera saman við annað gildi í WHERE Geta skilið vensl sem hægt er að nota í WHERE Geta komið í FROM ásamt nd breytu sem representar ndirnar í niðurstöðu hlutfyrirspurnarinar

Hvernig bætiru við gildinn (v1,...,vn) í venslið R(A1,...,An)?

INSERT INTO R(A1,...,An) VALUES (v1,...,vn); Ef vantar gildi í suma dálka er sett default gildi (oftast NULL) Best er að nefna alltaf alla dálkanna, því annars gæti gildi farið á villt (ef þú td. ruglast á röðinni)

Hvernig myndiru fara að því ef þú vildir fá allar myndir (sem eru ekki nú þegar í studio) úr Movie(title, length, year, genre, studioname, producerc) í venslið studio(name, address, presc) ?

INSERT INTO studio(name) SELECT DISTINCT studioname FROM Movie WHERE studioname NOT IN (SELECT name FROM studio); DISTINCT gegnir því hlutverki að framleiðslan birtist bara einu sinni sama hversu margar myndir hún hefur framleitt

Hægt er að bæta við skorður á GROUP BY með lykilorðinu HAVING. Hvað færðu úr fyrirspurninni: SELECT name, SUM(length) FROM MovieExec, Movie WHERE producerC = cert GROUP BY name HAVING MIN(year) < 1930;

Lengd allra mynda til samans sem framleiðandi sem gerði allavega eina mynd fyrir 1930 Having hérna fer í gegnum og passar að til er year sem er minna in 1930 í movies (hef ekki betri útskýringu sowry orz)

Hvað færðu úr SELECT length*0.016667 AS lenghtHours FROM Movies WHERE studioName='Disney' AND year=1990;

Lengd í klst allra mynda framleidda af DIsney árið 1990

SELECT length*0.016667 AS lenghtHours, 'hrs.' AS inHours FROM Movies WHERE studioName='Disney' AND year=1990;

Lengd í klst allra mynda framleidda af Disney árið 1990 nema bætt er við dálkur sem segir hrs. (auðveldar lestur)

Movie(title, year, length, inColor, studioName, producerC#) StarsIn(movieTitle, movieYear, starName) MovieStar(name, address, gender, birthdate) MovieExec(name, address, cert#, netWorth) Studio(name, address, presc#) Which stars appeared in Movies produced by MGM in 1995?

SELECT name FROM Movie, MovieStar, StarsIn WHERE year=1995 AND studioName= 'MGM' AND title=movieTitle AND year=movieYear AND name=starName

Movie(title, year, length, inColor, studioName, producerC#) StarsIn(movieTitle, movieYear, starName) MovieStar(name, address, gender, birthdate) MovieExec(name, address, cert#, netWorth) Studio(name, address, presc#) Who were the male stars in Terms of Endearment?

SELECT name FROM MovieStar, StarsIn WHERE gender = 'M' AND name = starName AND movieTitle = 'Terms of Endearment';

Movies(title, year, length, genre, studioName, producerC#) MovieExec(name, address, cert#, netWorth) Viljum vita nafnið á prod f. StarWars

SELECT name FROM Movies, MovieExec WHERE title='Star Wars' AND producerC#= cert#;

Movie(title, year, length, inColor, studioName, producerC#) StarsIn(movieTitle, movieYear, starName) MovieStar(name, address, gender, birthdate) MovieExec(name, address, cert#, netWorth) Studio(name, address, presc#) Framleiðendur bíómynda sem Harrison Ford leikur í

SELECT name FROM movieExec WHERE cert#= (SELECT producerC# FROM Movie WHERE (title, year) IN (SELECT movieTitle, movieYear FROM starsIn WHERE starName = 'Harrison Ford' ) ); Ath. við túlkun á svona fyrirspurnum er gott að byrja á "innstu" hlutfyrirspurninni Ath. hægt að einfalda: SELECT name FROM movieExec, Movies, starsIn WHERE cert#= producer# AND title = movieTitle AND year = movieYear AND starName = 'Harrison Ford';

Movies (title,year, length, genre, studioName, producerC) MovieExec(name, address, cert, netWorth) Finndu framleiðandann fyrir StarWars með hlutfyrirspurn

SELECT name FROM movieExec WHERE cert= (SELECT producer FROM Movies WHERE title='Star Wars' );

Movie(title, year, length, inColor, studioName, producerC#) StarsIn(movieTitle, movieYear, starName) MovieStar(name, address, gender, birthdate) MovieExec(name, address, cert#, netWorth) Studio(name, address, presc#) Framleiðendur bíómynda sem Harrison Ford leikur í með hlutfyrirspurn í FROM

SELECT name FROM movieExec, (SELECT producerC# FROM Movies, StarsIn WHERE title = movieTitle AND year=movieYear AND starname='Harrison Ford' ) Prod WHERE cert#= Prod.producerC#; Prod er alias hlutfyrirspurnarinnar

Movie(title, year, length, inColor, studioName, producerC#) StarsIn(movieTitle, movieYear, starName) MovieStar(name, address, gender, birthdate) MovieExec(name, address, cert#, netWorth) Studio(name, address, presc#) Which movies are longer than Gone With the Wind?

SELECT title FROM movie WHERE length> (SELECT length FROM movie WHERE title= 'Gone With the Wind') or: SELECT M1.title FROM Movie AS M1, Movie AS M2 WHERE M2.title = 'Gone With the Wind' AND M1.length > M2.length;

Movies JOIN starsIn ON title = movieTitle AND year= movieYear; útkoman inniheldur ma. 4 dálka (movietitle, title, year og movieyear) sem mætti minnka niður í tvo (bara title og year) Hvernig gerum við það?

SELECT title, year, length, genre, studioName, producerC#, starName FROM Movies JOIN StarsIn ON title = movieTitle AND year = movieYear;

SQL stendur fyrir

Structured Query Language

Munurinn á TRUE, FALSE og UNKNOWN

TRUE er 1 (alveg satt) FALSE er 0 (alls ekki satt) UNKNOWN er 1/2 (einhverstaðar á milli)

SELECT title FROM Movies WHERE title LIKE '%''s%' ;

Til að fá táknið ' er notað tvö '' % passar við hvaða stren sem er. Titil allra mynda með 's í titil

Hvað færðu úr SELECT title AS name FROM Movies WHERE studioName='Disney' AND year=1990;

Titil allra mynda framleidda af DIsney 1990 nema dálkheitið er nú Name

Innerjoin

Tökum dæmi. Höfum töfluna Company: ID NAME 1 Paul 2 Allen 3 Teddy 7 James og töfluna Department ID DEPT EMP_ID 1 IT Billing 1 2 Enginee 2 3 Finance 7 SELECT EMP_ID, NAME, DEPT FROM COMPANY INNER JOIN DEPARTMENT ON COMPANY.ID = DEPARTMENT.EMP_ID; Gefur okkur töfluna EMP_ID NAME DEPT 1 Paul IT Billing 2 Allen Enginee 7 James Finance ss. v. að Teddy uppfylti ekki skilyrðið að vera með EMP_ID = 1, 2, eða 7 þá var honum hent út

OUTERJOIN Sqlite styður aðeins left outer join (þó til líka right og full outer join)

Tökum dæmi. Höfum töfluna Company: ID NAME 1 Paul 2 Allen 3 Teddy 7 James og töfluna Department ID DEPT EMP_ID 1 IT Billing 1 2 Enginee 2 3 Finance 7 SELECT EMP_ID, NAME, DEPT FROM COMPANY LEFT OUTER JOIN DEPARTMENT ON COMPANY.ID = DEPARTMENT.EMP_ID; Gefur okkur töfluna EMP_ID NAME DEPT 1 Paul IT Billing 2 Allen Engine Teddy 7 James Finance Þrátt fyrir að Teddy greyið uppfyllt ekki skilyrðið að vera með 1,2 eða 7 í EMP_ID þá fær hann samt að malla með í niðurstöðutöflunni, í staðinn fær hann NULL í þá dálka sem voru joinaðir (ss EMP_ID og DEPT)

SELECT title FROM Movies WHERE ( year > 1970 OR length < 90) AND studioName = 'MGM';

Tökum eftir að OR ið er innan sviga til að rugla ekki með AND Titil á þeim myndum framleiddar af MGM sem voru annaðhvort framleidd eftir 1970 eða eru styttir en 90 min

Hvernig updataru gagnagrunn?

UPDATE R SET <new-value assignments> WHERE <condition>;

Segjum að við séum með venslin MovieExec(name, address, cert, networth) og viljum bæta við Pres. fyrir framan þá movieExec sem eru líka presidentar einhvers studio.

UPDATE movieExec SET name= 'Pres.' || name WHERE cert IN (SELECT presc FROM studio); || concattar sem sagt Pres. við nöfn í name dálkinum

TRUE, FALSE og UNKNOWN X AND Y

X er TRUE Y er FALSE = FALSE X er UNKNOWN Y er FALSE = FALSE X er TRUE Y er UNKNOWN = UNKNOWN X er TRUE Y er TRUE = TRUE X er FALSE Y er FALSE = FALSE

TRUE, FALSE og UNKNOWN X OR Y

X er TRUE Y er FALSE = TRUE X er UNKNOWN Y er FALSE = UNKNOWN X er TRUE Y er UNKNOWN = TRUE X er TRUE Y er TRUE = TRUE X er FALSE Y er FALSE = FALSE

Dæmi með NULL reikninga X er NULL a) x + 3 b) NULL + 3 c) x = 3 d) NULL = 3

a) NULL b) ekki gilt SQL c) UNKNOWN d) Ekki gilt SQL

A og B eru int sem gætu verið NULL Hvað myndi næga? a) a=10 OR b=20 b) a=10 AND b=20 c) a<10 OR a>=10 d) a=b

a) a=10 og b getur verið hvað sem er, jafnvel NULL b) a=10 og b=20 c) a<10 eða a>=10 d) a er ekki null og b er ekki null (því null er aldrei sama sem eitthvað, ekki einu sinni null)

Annað dæmi um GROUP BY Hvað prentar SELECT studioName FROM Movies GROUP BY studioname; út?

Þar sem þetta er hópað þá prentast studioname aðeins einu sinni sama hve mörg gildi/raðir eru í "hópnum"