Insert And Update Data Simple SQL Queries Advanced SQL .
SQL as Data Manipulation Language (DML)Insert and update dataSimple SQL queriesAdvanced SQL queriesViews
SQL / DML: Overview Insert, update, delete data Query data Interactively (Embedded in host language) Data presentation to usersFU-Berlin, DBS I 2006, Hinze / Scholz Output improvements Views2
SQL / DML: Insert data Complete form: Predefined order of valuesINSERT INTO CustomerVALUES (001, 'Müller', 'Tina', NULL,NULL); Incomplete form: Free order of valuesFU-Berlin, DBS I 2006, Hinze / ScholzINSERT INTO Customer(last name, mem no) VALUES ('Müller', 001);3
SQL / DML: Insert data Inserting datesINSERT INTO movieVALUES (95, 'Psycho', 'suspense',TO DATE('1969', 'yyyy'),'Hitchcock', 2.00, NULL); Conversion functions String to dateTO DATE( string [, format ])ORACLE/PostgresFU-Berlin, DBS I 2006, Hinze / Scholz Date to stringTO CHAR( date [, format ]) ORACLE/Postgres4
SQL / DML: Insert data Loading data from files System dependent Oracle: INSERT INTO Bulk load from file: SQL loader Bulk load from other database: export / import tool MySQL:FU-Berlin, DBS I 2006, Hinze / Scholz INSERT INTO Bulk load: LOAD DATA Bulk load from other database:SELECT INTO OUTFILE, LOAD DATA Postgres: INSERT INTO Bulk load: Copy Bulk load from other database: Copy 5
SQL / DML: Implementations of bulk load Oracle SQL loadercontrol fileSQL*Loaderfield processingdiscardedrowsdata filerejectedrowsrecord selectionbad fileFU-Berlin, DBS I 2006, Hinze / Scholz(discard file) log fileOracle server6
SQL / DML: Implementations of bulk load Example:CREATE TABLE loadtest(name varchar(20),num number(10,2));loadtest.dat'four''four' ,,'five''five' ,,'six''six' ,,445566 Oracle Syntax:sqlldr user / password controlfile logfile badfile datafile FU-Berlin, DBS I 2006, Hinze / Scholzloadtest.ctlloadload datadatainfileinfile 'loadtest.dat''loadtest.dat'badfilebadfile 'loadtest.bad''loadtest.bad'discardfilediscardfile 'loadtest.dis''loadtest.dis'APPENDAPPEND INTOINTO tabletable loadtestloadtestfieldsfields terminatedterminated byby "" ,, ""optionallyoptionally enclosedenclosed byby "" '' ""(name(name char,char, numnum integerinteger external)external)7
SQL / DML: Implementations of bulk load MySQL Example:Mysql Mysql LOADLOAD DATADATA INFILEINFILE 'loadtest.dat''loadtest.dat'- IGNORE- IGNORE- INTO- INTO TABLETABLE loadtestloadtest- FIELDS- FIELDS- TERMINATED- TERMINATED BYBY ","","- OPTIONALLY- OPTIONALLY ENCLOSEDENCLOSED BYBY ''''- (name,- (name, numnum ););QueryQuery OK,OK, 33 rowsrows affectedaffected (0.01(0.01 sec)sec)Records:Records: 33 Deleted:0Deleted:0 Skipped:Skipped: 00 Warnings:Warnings: 00FU-Berlin, DBS I 2006, Hinze / Scholz8
SQL / DML: Insert unique data For synthetic keys, e.g. tapeId, Counter-variables in application not sufficient Session dependent Concurrent access problematic Counter relation in database ExpensiveFU-Berlin, DBS I 2006, Hinze / Scholz Proprietary solutions in existing DBMS MySQL: autoincrement keyword SQL-Server: identity keyword PostgreSQL: serial type and sequence Oracle: sequence-object9
SQL / DML: Insert unique data - sequence Abstract sequence-object (Oracle) Creates unique integer values Syntax:CREATE SEQUENCE seqName [START WITH integer ][INCREMENT BY integer ][MAXVALUE integer NOMINVALUE][MINVALUE integer NOMAXVALUE][CYCLE NOCYCLE][CACHE integer NOCACHE]FU-Berlin, DBS I 2006, Hinze / Scholz[ORDER NOORDER]; Example:create sequence tape sequence;10
SQL / DML: Insert unique data - sequence Value Access seqName .NEXTVAL value of last call increment seqName .CURRVAL value of last call SELECT seqName .CURRVAL FROM DUAL; DUAL is Oracle pseudo-table Example:FU-Berlin, DBS I 2006, Hinze / ScholzINSERT INTO tapeVALUES(tape sequence.nextval, 'DVD', 95);11
SQL / DML: Delete data Syntax:DELETE from tableName [WHERE predicate ]; Delete all rows :DELETE from tableName ; Example:FU-Berlin, DBS I 2006, Hinze / ScholzDELETE from tapeWHERE format 'Beta';12
SQL / DML: Update data Syntax:UPDATE tableName SET attr value {, attr value }WHERE predicate Examples:UPDATE CustomerSET telephone 456789WHERE mem no 200;FU-Berlin, DBS I 2006, Hinze / ScholzUPDATE RentalSET until date SYSDATEWHERE tape ID 3AND mem no 200AND TO CHAR(from date,'yyyy-mm-dd') '2002-05-01';13
SQL / DML: Example databaseinsert into customer values (001, 'Müller', 'Tina', NULL,NULL);insert into customer values (007, 'Katz', 'Anna', NULL,NULL);insert into customer values (002, 'Maus', 'Carla', NULL,NULL);.insert into movie values (95, 'Psycho', 'suspense',to date('1969', 'yyyy'), 'Hitchcock', 2.00, NULL);insert into movie values (112, 'ET', 'comedy',to date('1982', 'yyyy'), 'Spielberg', 1.50, NULL);.insert into format values('DVD', '2.00');insert into format values('Beta', '0.00');insert into format values('VHS', '0.00');FU-Berlin, DBS I 2006, Hinze / Scholzcreateinsertinsertinsertsequence tape sequence;into tape values (tape sequence.nextval, 'DVD', 95);into tape values (tape sequence.nextval, 'DVD', 112);into tape values (tape sequence.nextval, 'VHS', 222);14
SQL / DML: Example databaseinsert into rental values (3, 1,to date('2002-05-01','yyyy-mm-dd'), NULL);insert into rental values (4, 1,to date('2002-05-01','yyyy-mm-dd'), NULL);insert into rental values (5, 3,to date('2002-05-01','yyyy-mm-dd'),to date('2002-05-02','yyyy-mm-dd'));insert into actor values ('Hitchcock','Hitchcock', to date(1899-08-13','yyyy-mm-dd'));insert into actor values ('Harrison Ford','Harrison Ford', to date('1942-07-13','yyyy-mm-dd'));FU-Berlin, DBS I 2006, Hinze / Scholzinsert into play values(290,'Harrison Ford');insert into play values(98,'Hitchcock');15
SQL / DML: Querying Language SQL is relational complete Additional query conceptsFU-Berlin, DBS I 2006, Hinze / Scholz Advanced search expressions on stringse.g., find all movies starting with “star wars” Arithmetic in expressions,e.g., number of tapes for each movie Grouping and predicates over setse.g., total receipts of each movie within the last year16
SQL / DML: Basics Basic query pattern:SELECTFROMWHERE[DISTINCT] A1, A2,.,AnR1, R2,.Rmpredicate P; A1 , A2 , ., An attribute names, R1 , R2 , ., Rm relation names, P Boolean predicate on attributes and constants Equivalent to relational algebra expression:FU-Berlin, DBS I 2006, Hinze / ScholzΠA1, A2, ., An( σP ( R1 X R2 X . X Rm )) Projection (RA) SELECT (SQL) Cartesian Product (RA) FROM (SQL) Selection (RA) WHERE (SQL)17
SQL / DML: Basics Query result is relation Query evaluation order:1.FROM-clause2.WHERE-clause3.SELECT-clause No duplicate removal(performance!)SQL SQL SELECTSELECT last namelast name22 FROMCustomer;FROM Customer;FU-Berlin, DBS I 2006, Hinze / ScholzLAST NAMELAST KunzKunzMüllerMüller18
SQL / DML: Basics Eliminating duplicates: Targetlist contains KEY attributeTargetlist constrains UNIQUE attributeTargetliste defined with DISTINCTSELECT mem no, last nameFROM CustomerFU-Berlin, DBS I 2006, Hinze / ScholzMEM NOLAST NAMEMEM NOLAST NAME------------------- ------------------------------11 MüllerMüller77 KatzKatz22 MausMaus1111 HinzHinz2323 KunzKunz111111 MüllerMüllerSELECT DISTINCT last nameFROM CustomerLAST NAMELAST tzKunzKunzMausMausMüllerMüller19
SQL / DML: Basics WHERE-clause structure:FU-Berlin, DBS I 2006, Hinze / Scholz Simple Boolean predicates similar to RA and Calculus Additional simple predicates: attribute BETWEEN value1 AND value2 attribute IS [NOT] NULL attribute LIKE string attribute SIMILAR TO string Advanced predicated with sub-queriesSet-operators (IN, NOT IN, SOME, ALL, EXISTS)20
SQL / DML: Simple queriesExample: All customers named AnnaSQL SQL selectselect mem no,mem no, last name,last name, first namefirst name22 fromfrom customercustomer33 wherewhere first name 'Anna';first name 'Anna';MEM NOFIRST NAMEMEM NO LAST NAMELAST NAMEFIRST NAME------------------- --------- --------------------------77 KatzAnnaKatzAnna23Anna23 KunzKunzAnnaExample: All movies by Lucas from 1999 or laterFU-Berlin, DBS I 2006, Hinze / ScholzSQL SQL selectselect id,id, titletitle22 fromfrom moviemovie33 wherewhere director 'Lucas'director 'Lucas'44 andand to char(year,'yyyy') '1999';to char(year,'yyyy') '1999';IDID TITLETITLE------------------- --------------------------------------345345 StarStar WarsWars II21
SQL / DML: Simple queries More examples:All formats with extra charge between 1 and 2 EuroSELECT *FROM FormatWHERE charge BETWEEN 1.00 and 2.00;FU-Berlin, DBS I 2006, Hinze / ScholzAll tapes currently on loanSELECT tape idFROM RentalWHERE until date IS NULL;22
SQL / DML: Simple queries - expressions LIKE - expression Simple form of regular expression % : any sequence of characters : exactly one character Example:CoreSQL:1999All ‘star wars’ moviesSQL SQL selectselect id,id, title,title, directordirector22 frommoviefrom movie33 wherewhere titletitle likelike 'Star'Star WarsWars %';%';FU-Berlin, DBS I 2006, Hinze / ScholzIDDIRECTORID TITLETITLEDIRECTOR------------------- ----------------------- ----------------------------345StarWarsILucas345 Star Wars I Lucas290290 StarStar WarsWars IVIV LucasLucas23
SQL / DML: Simple queries - expressions SIMILAR - expression Advanced form of regular expressionenhancedSQL:1999 Example:All ‘star wars’ moviesFU-Berlin, DBS I 2006, Hinze / ScholzSELECT id, title, directorFROM movieWHERE title SIMILAR TO'Star Wars (I IV V VI 1 [4-6])';24
SQL / DML: Simple queries Member in set: INAll movies from Spielberg or LukasCoreSQL:1999SELECT title, directorFROM MovieWHERE director IN ('Spielberg','Lucas');FU-Berlin, DBS I 2006, Hinze / ScholzAll movies from Lucas in 1999enhancedSELECT title, directorSQL:1999FROM MovieWHERE (director, year)IN (('Lucas',to date(1999,'yyyy')));25
SQL / DML: Simple queries - expressions Functions Expressions may contain functions Arithmetical and string built-in functions User defined functions on user defined types String function examples: SOUNDEX ( string ), UPPER ( string ) SUBSTRING( string FROM integer FOR integer )FU-Berlin, DBS I 2006, Hinze / ScholzSQL SQL SELECTSELECT title,title, directordirector22 FROMFROM MovieMovie33 WHEREWHERE SOUNDEX(director)SOUNDEX(director) RTITLEDIRECTOR------------------------------- pielberg26
SQL / DML: Simple queries - expressions Arithmetic function examples SQRT( number ) Basic arithmetic expressionsAll tapes, their price and taxSQL SQL SELECTSELECT id,id, pricepday,pricepday,220.16*pricepday0.16*pricepday asas taxtax33 FROMFROM Movie;Movie;FU-Berlin, DBS I 2006, Hinze / ScholzIDTAXID PRICEPDAYPRICEPDAYTAX------------------- ------------------- .5.241001.5.2427
SQL / DML: Simple queries - expressions Date function examples differ heavily between systems Oracle: SYSDATE, MONTHS BETWEEN, ADD MONTHSSQL SQL SELECTSELECT title,title, to char(year,'yyyy')to char(year,'yyyy') asas yearyear22 FROMFROM moviemovie33 WHEREWHERE months BETWEEN(SYSDATE,year) months BETWEEN(SYSDATE,year) 120120 ;;FU-Berlin, DBS I 2006, Hinze / --------------------- 197528
SQL / DML: Simple queries Combination of relations Schema compatible relations UNION, INTERSECT, EXCEPTCoreSQL:1999 Syntax:UNION INTERSECT EXCEPT[DISTINCT ALL][CORRESPONDING [BY attributes ]] Default DISTINCTFU-Berlin, DBS I 2006, Hinze / Scholz Default: all attributes used CORRESPONDING BY: defines used common attributes CORRESPONDING: uses all common attributes29
SQL / DML: Simple queries Example:All movies from Spielberg or Lukas(SELECT title, directorFROM MovieWHERE director like 'Spielberg')UNION(SELECT title, directorFROM MovieWHERE director like 'Lucas');FU-Berlin, DBS I 2006, Hinze / Scholz30
SQL / DML: Simple queries More examples:All movies not by Lucas(SELECT *FROM Movie)EXCEPT(SELECT * from MovieWHERE director 'Lucas');FU-Berlin, DBS I 2006, Hinze / ScholzAll directors and actors in our database(SELECT director as celebrityFROM Movie)UNION DISTINCT(SELECT stage name as celebrityFROM Actor);31
SQL / DML: Implementations combinations Oracle: UNION MINUS implements EXCEPT INTERSECT CORRESPONDING [BY] not implemented PostgreSQL: UNION EXCEPT INTERSECTFU-Berlin, DBS I 2006, Hinze / Scholz CORRESPONDING [BY] not implemented MySQL: UNION, EXCEPT, INTERSECT not implemented32
SQL / DML: Simple queries with joins Simple joins Search predicates and join conditions mixedCoreSQL:1999Example: All Tapes and their corresponding movieSQL SQL SELECTSELECT t.id,t.id, t.format,t.format, m.id,m.id, m.titlem.title22 FROMFROM TapeTape t,t, MovieMovie mm33 WHEREWHERE m.idm.id t.movie id;t.movie id;IDID FORMATFORMATIDID TITLETITLEFU-Berlin, DBS I 2006, Hinze / Scholz------------------- --------- ------------------- ----11 DVD95DVD95 PsychoPsycho22 DVD112ETDVD112 ET33 VHS222VHS222 PsychoPsycho44 DVD345DVD345 StarStar WarsWars II55 VHS345VHS345 StarStar WarsWars II99 VHS345VHS345 StarStar WarsWars II33
SQL / DML: Simple queries with joins Cross join (cross product)enhancedSQL:1999 tableName CROSS JOIN tableName Natural inner join tableName NATURAL [INNER] JOIN tableName Example:FU-Berlin, DBS I 2006, Hinze / ScholzSELECT *FROM Rental NATURAL INNER JOIN Customer;34
SQL / DML: Simple queries with joins Inner join with attribute listenhancedSQL:1999 tableName [INNER] JOIN tableName USING attributList Subset of attributes in common Example:FU-Berlin, DBS I 2006, Hinze / ScholzSELECT *FROM Rental r JOIN Customer cUSING (mem no);35
SQL / DML: Simple queries with joins Inner join with conditionenhancedSQL:1999 tableName [INNER] JOIN tableName ON condition Examples:SELECT *FROM Tape t JOIN Movie mON t.movie id m.id;FU-Berlin, DBS I 2006, Hinze / ScholzSELECT *FROM Rental r JOIN Customer cON r.mem no c.mem no;36
SQL / DML: Simple queries with joinsAll Customers who have rented at least one science fiction filmSELECT c.mem no, c.last name, c.first nameFROM ((Customer cJOIN Rental r ON c.mem no r.mem no)JOIN Tape t ON t.id r.tape id )JOIN Movie m ON t.movie id m.idWHERE m.category 'Scifi';FU-Berlin, DBS I 2006, Hinze / ScholzSELECT c.mem no, c.last name, c.first nameFROM Customer c, Rental r, Tape t, Movie mWHERE c.mem no r.mem noAND t.id r.tape idAND t.movie id m.idAND m.category 'Scifi';37
SQL / DML: Simple queries with joins Natural outer joinenhancedSQL:1999 tableName LEFT RIGHT FULLNATURAL [OUTER] JOIN tableName Outer join with condition tableName LEFT RIGHT FULL [OUTER] JOIN tableName ON condition FU-Berlin, DBS I 2006, Hinze / Scholz Example:SELECT *FROM Rental r RIGHT OUTER JOIN Customer cON r.mem no c.mem no;38
SQL / DML: Simple queries with joins Example (extended)SQL SQL SELECTSELECT r.tape id,r.tape id, r.from date,r.from date, c.mem no,c.first namec.mem no,c.first name22 FROMFROM RentalRental rr RIGHTRIGHT OUTEROUTER JOINJOIN CustomerCustomer cc33 ONON r.mem nor.mem no c.mem no;c.mem no;FU-Berlin, DBS I 2006, Hinze / ScholzTAPE IDMEM NOTAPE ID FROM DATEFROM DATEMEM NO FIRST NAMEFIRST NAME------------------- ----------------- ------------------- --------------------------------------33 01-MAY-0211 Tina01-MAY-02Tina44 01-MAY-0211 Tina01-MAY-02Tina55 01-MAY-0222 Carla01-MAY-02Carla2323 AnnaAnna111111 BertBert1111 FritzFritz77 AnnaAnna39
SQL / DML: Implementations of joins Oracle/Postgres: Simple joinCross join(natural) inner join with attribute list, with condition(natural) Right, left, full outer join with condition recommends ANSI-syntax for compatibility MySQL:FU-Berlin, DBS I 2006, Hinze / Scholz Simple joinCross joinStraight join (left table always read before right one)Inner join with condition(natural) left, right outer join with condition40
SQL / DML: Improving the output Not feature of relational algebra Example: Rename column title for this query Order tuplesSQL SQL SELECTSELECT m.titlem.title asas Movies,Movies, t.id,t.id, t.formatt.format22 FROMFROM MovieMovie m,m, TapeTape tt33 WHEREWHERE m.idm.id t.movie idt.movie id44 ORDERORDER BYBY title;title;FU-Berlin, DBS I 2006, Hinze / ScholzMOVIESIDMOVIESID --------------------------------- --------------- ----------ET22 DVDETDVDPsycho11 DVDPsychoDVDPsycho33 VHSPsychoVHSStar44 DVDStar WarsWars IIDVDStar55 VHSStar WarsWars IIVHSStar99 VHSStar WarsWars IIVHS41
SQL / DML: Improving the output Syntax:ORDER BY orderexpression ASC DESC Ordering expression No advanced expressions (no sub-query, no grouping) At least one attribute reference References in order expression result attributes Multiple sort attributes:FU-Berlin, DBS I 2006, Hinze / Scholz Primary ordering by first attribute Secondary ordering by second attribute, SELECT m.title as Movies, t.id, t.formatFROM Movie m, Tape tWHERE m.id t.movie idORDER BY title, format;42
SQL / DML: Improving the output Advanced features system dependent Oracle SQL Example: Format column title for all queries Don’t repeat identical titlesSQL SQL BREAKBREAK ONON titletitleSQL SQL COLUMNCOLUMN titletitle HEADINGHEADING "Movies""Movies" FORMATFORMAT A15A15SQL SQL SELECTSELECT m.title,m.title, t.id,t.id, t.formatt.format22 FROMFROM MovieMovie m,m, TapeTape tt33 WHEREWHERE m.idm.id t.movie id;t.movie id;FU-Berlin, DBS I 2006, Hinze / ScholzMoviesIDMoviesID FORMATFORMAT----------------------------- ------------------- ----------Psycho11 DVDPsychoDVDET22 DVDETDVDPsycho33 VHSPsychoVHSStar44 DVDStar WarsWars IIDVD55 VHSVHS99 VHSVHS43
SQL / DML: Sub-queries Sub-queries with single results Operators { , , , , , } Expressible without sub-queryCoreSQL:1999 Example:Movies shorter than 'Star Wars I‘ (id 345)FU-Berlin, DBS I 2006, Hinze / ScholzSELECT m.idFROM Movie mWHERE m.length (SELECT m1.lengthFROM Movie m1WHERE m1.id 345);44
SQL / DML: Sub-queries Set Operator INCoreSQL:1999 Independent sub-query example:All Tapes for movie ‘Star Wars ’FU-Berlin, DBS I 2006, Hinze / ScholzSELECT t.id, t.formatFROM Tape tWHERE t.movie idIN (SELECT m.idFROM Movie mWHERE m.title like 'Star Wars %');45
SQL / DML: Sub-queries Set Operator INCoreSQL:1999 Correlated sub-query example:Directors playing in their own moviesFU-Berlin, DBS I 2006, Hinze / ScholzSELECT m.directorFROM Movie mWHERE m.director IN(SELECT p.actor nameFROM Plaries supported52
SQL / DML: Universal quantifiers – Quantification - Quantification (exactly one) Describe counterexample Combine with NOT EXISTSFU-Berlin, DBS I 2006, Hinze / ScholzMovies with only one tapeSELECT m.idFROM Movie mWHERE NOT EXISTS(SELECT *FROM Tape t1, Tape t2WHERE t1.movie id t2.movie idAND t1.id t2.idAND t2.movie id m.id );53
SQL / DML: Universal quantifiersAll Customers whose rented movies all have category “suspense“SELECT c.mem noFROM Customer cWHERE NOT EXISTS(SELECT m.idFROM Movie m, Rental r, Tape tWHERE m.id t.movie idAND r.tape id t.idAND c.mem no r.mem noAND m.category 'suspense');FU-Berlin, DBS I 2006, Hinze / Scholz54
SQL / DML: Universal quantifiersCustomers that had rented all moviesFU-Berlin, DBS I 2006, Hinze / ScholzSELECT c.mem noFROM Customer cWHERE NOT EXISTS(SELECT m.idFROM Movie mWHERE NOT EXISTS(SELECT *FROM Rental r, Tape tWHERE m.id t.movie idAND r.tape id t.idAND c.mem no r.mem no));55
SQL / DML: Universal quantifiersCustomers that rented only one movieFU-Berlin, DBS I 2006, Hinze / ScholzSELECT c.mem noFROM Customer c, Rental r, Tape t, Movie mWHERE c.mem no r.mem noAND r.tape id t.idAND t.movie id m.idAND NOT EXISTS(SELECT m1.idFROM Rental r1, Tape t1, Movie m1WHERE r1.tape id t1.idAND t1.movie id m1.idAND c.mem no r1.mem noAND m1.id m.id);56
SQL / DML: Aggregate functions Mathematical aggregate functions on data sets Example: SUM, AVG, MIN, MAX, COUNT Not in relational algebraSQL SQL 2233SELECTSELECT MIN(pricePDay)MIN(pricePDay) asas MIN,MIN,MAX(pricePDay)MAX(pricePDay) asas MAX,MAX, AVG(pricePDay)AVG(pricePDay)FROMFROM Movie;Movie;MINMAXMINMAX AVG(PRICEPDAY)AVG(PRICEPDAY)------------------- ------------------- 86666667FU-Berlin, DBS I 2006, Hinze / Scholz Target list: only aggregate functions or none Exception: GROUP BY57
SQL / DML: Aggregate functions Comparison using aggregates: sub-queriesMovies with price above averageSQL SQL 22334455SELECTSELECT m.id,m.id, m.Title,m.Title, m.pricepdaym.pricepdayFROMFROM MovieMovie mmWHEREWHERE pricePDaypricePDay (SELECT(SELECT AVG(pricePDay)AVG(pricePDay)FROMFROM Movie);Movie);FU-Berlin, DBS I 2006, Hinze / ----------------PsychoPsychoStarStar WarsWars IIPsychoPsychoStarStar WarsWars 58
SQL / DML: Aggregate functions Examples:Movies with minimal priceSELECT m.Title, m.pricepdayFROM Movie mWHERE pricePDay (SELECT MIN(pricePDay)FROM Movie);FU-Berlin, DBS I 2006, Hinze / ScholzMovie with more than 2 tapesSELECT m.id, m.titleFROM Movie mWHERE 2 (SELECT count(t.id)FROM tape tWHERE t.movie id m.id)59
SQL / DML: Aggregate functions More examples:Movies having tapes in one format onlyFU-Berlin, DBS I 2006, Hinze / ScholzSELECT m.id, m.titleFROM Movie m, Tape t1WHERE m.id t1.movie idAND 0 (SELECT COUNT(*)FROM Tape t2WHERE t1.id t2.idAND t1.format t2.formatAND t2.movie id m.id);60
SQL / DML: Aggregate functions Additional qualification with DISTINCT ALL Example:Movies that are available in all formatsFU-Berlin, DBS I 2006, Hinze / ScholzSELECT DISTINCT t1.movie idFROM Tape t1WHERE(SELECT COUNT(DISTINCT format)FROMTape t2WHERE t2.movie id t1.movie id) (SELECT COUNT(*)FROM Format);61
SQL / DML: Grouping Syntax:SELECT targetlist FROM tablelist [WHERE predicate ]GROUP BY attributelist Groups all rows with same values in attributelist Target list: grouping attributes and aggregates Example:FU-Berlin, DBS I 2006, Hinze / ScholzNumber of tapes in each formatSELECT t.format, count(t.id)FROM Tape tGROUP BY t.format;62
SQL / DML: Grouping Aggregates evaluated over groupsNumber of tapes for each movieSQL SQL 2233SELECTSELECT t.movie id,t.movie id, count(*)count(*)FROMFROM TapeTape ttGROUPGROUP BYBY t.movie id;t.movie id;FU-Berlin, DBS I 2006, Hinze / ScholzMOVIE IDCOUNT(*)MOVIE IDCOUNT(*)------------------- 0112903454434563
SQL / DML: GroupingMovieid095112345222290100.cat. year.Psycho.ET.Star Wars I .Psycho.Star Wars IV .Jaws.titledirectorHitchcockSpielbergLucasVan SantLucasSpielberg.FU-Berlin, DBS I 2006, Hinze / ScholzSELECT sum(price)FROM movie;price leng.2.001.502.002.202.001.50.11.2Implicit group: all tuples in table64
SQL / DML: GroupingMovieid095112345222290100.cat. year.Psycho.ET.Star Wars I .Psycho.Star Wars IV .Jaws.titleFU-Berlin, DBS I 2006, Hinze / ScholzSELECT director, sum(price)FROM movieGroup by director;directorHitchcockSpielbergLucasVan SantLucasSpielberg.price 03.0Lucas4.0Van Sant2.265
SQL / DML: GroupingTotal receipts of each tape within the last yearSQL SQL 22334455SELECTSELECT t.id,t.id, count(*)count(*)FROMFROM TapeTape t,t, RentalRental rrWHEREWHERE t.idt.id r.tape idr.tape idANDAND to char(r.from date,'yyyy')to char(r.from date,'yyyy') 20052005GROUPGROUP BYBY t.id;t.id;FU-Berlin, DBS I 2006, Hinze / ScholzIDCOUNT(*)IDCOUNT(*)------------------- 66
SQL / DML: GroupingTotal receipts of each movie within the last yearSQL SQL 22334455SELECTSELECT t.movie id,t.movie id, count(*)count(*)FROMFROM TapeTape t,t, RentalRental rrWHEREWHERE t.idt.id r.tape idr.tape idANDAND to char(r.from date,'yyyy')to char(r.from date,'yyyy') 20052005GROUPGROUP BYBY t.movie id;t.movie id;FU-Berlin, DBS I 2006, Hinze / ScholzMOVIE IDCOUNT(*)MOVIE IDCOUNT(*)------------------- 0112903453334567
SQL / DML: Grouping Having Qualifying predicate for groupsSQL SQL SELECTSELECT f.name,f.name, sum(charge)sum(charge)22FROMFROM RentalRental r,r, TapeTape t,t, formatformat ff33WHEREWHERE t.idt.id r.tape idr.tape id44ANDAND t.format f.namet.format f.name55GROUPGROUP BYBY f.name;f.name;FU-Berlin, DBS I 2006, Hinze / ScholzNAMENAME--------BetaBetaDVDDVDVHSVHSSQL SQL SELECTSELECT f.name,f.name, FROM RentalRental r,r, TapeTape t,t, formatformat ff--------------------33WHEREWHERE t.idt.id r.tape idr.tape id0044ANDAND t.format f.namet.format f.name8855GROUPGROUP BYBY f.namef.name0066havinghaving count(f.name) 2;count(f.name) E)--------------------880068
SQL / DML: Grouping HavingMovieid095112345222290100.cat. year.Psycho.ET.Star Wars I .Psycho.Star Wars IV .Jaws.titleFU-Berlin, DBS I 2006, Hinze / ScholzSELECT director, sum(price)FROM movieGroup by directorHAVING sum(price) 2.00;directorHitchcockSpielbergLucasVan SantLucasSpielberg.price 03.0Lucas4.0Van Sant2.269
SQL / DML: Grouping HavingMovieid095112345222290100.cat. year.Psycho.ET.Star Wars I .Psycho.Star Wars IV .Jaws.titleFU-Berlin, DBS I 2006, Hinze / ScholzSELECT director, sum(price)FROM movieGroup by directorHAVING max(price) 2.00;directorHitchcockSpielbergLucasVan SantLucasSpielberg.price 03.0max2.01.5Lucas4.02.0Van Sant2.22.270
SQL / DML: Grouping HavingMovieid095112345222290100.cat. year.Psycho.ET.Star Wars I .Psycho.Star Wars IV .Jaws.titleFU-Berlin, DBS I 2006, Hinze / ScholzSELECT director,title, sum(price)FROM movieGroup by director, titleHAVING max(price) 2.00;directorHitchcockSpielbergLucasVan SantLucasSpielberg.price leng.2.001.502.002.202.001.50.Hitchcock, PsychoSpielberg, ET2.01.5max2.01.5Lucas, SW I2.02.0Van Sant, Psycho2.22.2Lucas, SW IV2.02.0Spielberg, Jaws1.51.571
SQL / DML: Grouping Having HAVING without GROUP BY Implicit single group contains all tuplesSQL SQL SELECTSELECT sum(charge)sum(charge)22FROMFROM RentalRental r,r, TapeTape t,t, formatformat ff33WHEREWHERE t.idt.id r.tape idr.tape id44ANDAND t.format f.namet.format f.name55havinghaving count(f.name) 2;count(f.name) 2;FU-Berlin, DBS I 2006, Hinze / 72
SQL / DML: Grouping Having Query evaluation order:1.FROM-clause2.WHERE-clause3.GROUP BY–clause4.HAVING-clause5.SELECT-clauseNumber of rentals for all customers named Anna or Tina,which rented some tapes more than onceFU-Berlin, DBS I 2006, Hinze / ScholzSELECT c.mem no, count(*)FROM Rental r, Customer cWHERE r.mem no c.mem noAND c.first name 'Anna'OR c.first name 'Tina'GROUP BY c.mem noHAVING count(DISTINCT r.tape id) count(*);73
SQL / DML: Nested aggregation with groups Nested aggregation using groupsMost loaned movieFU-Berlin, DBS I 2006, Hinze / ScholzSELECT t.movie id, count(t.movie id)FROM Rental r, Tape tWHERE r.tape id t.idGROUP BY t.movie idHAVING COUNT(t.movie id) ALL(SELECT count(t1.movie id)FROM Rental r1, Tape t1WHERE r1.tape id t1.idGroup BY t1.movie id);74
SQL / DML: Nested aggregation with groupsMovie with maximal number of tapes, show number of tapesSELECT m.id, m.title, t1.t noFROM (SELECT t.movie id, count(*) as t noFROM tape tGROUP BY t.movie id) t1, movie mWHERE m.id t1.movie idAND t1.t no (SELECT max(count(*))FROM tape tGROUP by t.movie id);FU-Berlin, DBS I 2006, Hinze / Scholz75
SQL / DML: Output improvement Select values depending an condition Complete CASE form:CoreSQL:1999CASEWHEN condition1 THEN result1 [ WHEN condition2 THEN result2 [ WHEN condition3 THEN result3 ]][ ELSE elseresult ]END Example:FU-Berlin, DBS I 2006, Hinze / ScholzSELECT length,CASE WHEN length is NULL then 'not defined'WHEN length 90 THEN 'short'ELSE 'long'ENDFROM Movie;76
SQL / DML: Output improvement Simple CASE formCoreSQL:1999CASE operand WHEN value1 THEN result1 [ WHEN value2 THEN result2 [ WHEN value3 THEN result3 ]][ ELSE elseresult ]END Example:FU-Berlin, DBS I 2006, Hinze / Scholzselect f.name,case f.namewhen 'DVD' then 'DISC'when 'Beta' then 'TAPE'when 'VHS' then 'TAPE'else NULLendfrom Format f;77
SQL / DML: Transitive closure Recursive queries Name recursion expression Use name in associated query expressionenhancedSQL:1999 SYNTAX:FU-Berlin, DBS I 2006, Hinze / ScholzWITH RECURSIVE queryname1 AS query1 [, queryname2 AS query2 ,.]SELECT .FROM queryname1 [, queryname2 .]WHERE.78
SQL / DML: Transitive closure Example: All lectures required for lecture XYZenhancedSQL:1999create table lecture(lnr integer primary key
SQL as Data Manipulation Language (DML) Insert and update data Simple SQL queries Advanced SQL queries Views. 2 FU-Berlin, DBS I 2006, Hinze / Scholz SQL / DML: Overview Insert, update, delete data Query data . SQL / DML: Simple queries More examples: All movies not by Lucas (SELECT * FROM Movie)
19-2 INSERT FITTINGS LARSEN SUPPLY CO., INC. NYLON INSERT FITTINGS 03/20/07 Insert Coupling Insert Tee Insert x Male Tee BARB INSERT FITTINGS For use with I.D. Size of most Plastic Tubing DESCRIPTION BULK QTY BULK UPC QTY CARDED QTY. DESCRIPTION BULK QTY BULK UPC QTY CARDED QTY. COUPLING Insert x Insert
Standard Colors Leatherette* Cloth/cloth* Black P4 PL Gray PU PT Blue FE FC Burgundy AA A9 Tan 61 6R Brown N/A 6Q Black with red insert P5 PM Black with gray insert P6 PN Black with tan insert P7 PP Black with blue insert P8 PQ Gray with black insert PX n/a Gray with blue insert PV n/a Gray with red insert PW n/a Blue with gray insert FF FD
larger area, reducing metal wear, and increasing valve and seat life. Cast-N-Place insert design locks insert in groove and eliminates underside insert washes, the most frequent cause of premature insert failures. Cast-N-Place process reduces stress on the valve insert during insert molding and curing, increasing insert life. Ideal .
Insert an iPod into the dock with proper insert (iPod must not be in a skin or case). The iPod will charge while docked. Select the dock insert for your iPod model from the table below and place insert in dock. Never dock an iPod without an insert in place. 4: dock iPod nano Insert iPod nano 2nd generation iPod video (30GB) Insert iPod 5th .
Insert Dividers End view of Insert Divider End Cover Options Note: Extended Insert End Cover is used with plastic adaptors and all illuminated applications. Extended Insert End Cover Part #1698 Access Frame Part #1690 Insert End Cover Part #1696 Access Frame Part #1690 Flush, countersunk stainless steel fasteners hold the Insert End Cover in place
[insert discipline] Resources Specialist will train construction personnel in [insert discipline] Resource identification and protection procedures. Prior to the start of on-site construction work, and at the discretion of a [insert who], a [insert who] will flag and/or fence all [insert discipline or resource] with a buffer of [insert distance]
5.2.1 Insert Barcode in Word 2003 using the Menu 11 5.2.2 Insert Barcode in Word 2003 using the Control Toolbox 12 5.3 Microsoft Office 2007 12 5.3.1 Insert Barcode in Word 2007 12 5.3.2 Insert Barcode in Excel 2007 13 5.3.3 Insert Barcode in Access 2007 14 5.4 Microsoft Office 2010 / Office 2013 / Office 2016 15 5.4.1 Insert Barcode in Word .
Microsoft Word - Space Tourism reading comprehension.docx Created Date: 3/27/2018 9:06:16 AM .
