New User Special Price Expires in

Let's log you in.

Sign in with Facebook


Don't have a StudySoup account? Create one here!


Create a StudySoup account

Be part of our community, it's free to join!

Sign up with Facebook


Create your account
By creating an account you agree to StudySoup's terms and conditions and privacy policy

Already have a StudySoup account? Login here


by: Adele Schaden MD


Adele Schaden MD
GPA 3.88

Vassilis Tsotras

Almost Ready


These notes were just uploaded, and will be ready to view shortly.

Purchase these notes here, or revisit this page.

Either way, we'll remind you when they're ready :)

Preview These Notes for FREE

Get a free preview of these Notes, just enter your email below.

Unlock Preview
Unlock Preview

Preview these materials now for free

Why put in your email? Get access to more of this material and other relevant free materials for your school

View Preview

About this Document

Vassilis Tsotras
Class Notes
25 ?




Popular in Course

Popular in ComputerScienence

This 10 page Class Notes was uploaded by Adele Schaden MD on Thursday October 29, 2015. The Class Notes belongs to CS 236 at University of California Riverside taught by Vassilis Tsotras in Fall. Since its upload, it has received 17 views. For similar materials see /class/231742/cs-236-university-of-california-riverside in ComputerScienence at University of California Riverside.

Similar to CS 236 at UCR

Popular in ComputerScienence




Report this Material


What is Karma?


Karma is the currency of StudySoup.

You can buy or earn more Karma at anytime and redeem it for class notes, study guides, flashcards, and more!

Date Created: 10/29/15
SQL Queries Programming Triggers Chapte r 5 R1 sid m d Example Instances 22 101 10109 58 103 111296 5 We willuse these SI sname rating age t fth 5 me e 22 dustin 7 450 Sailors and Reserves relations 31 lubber 8 555 1 W mmPles 58 rusty 10 350 Reservesrelation 52 sname rating age 8 35 contained 0111 the attributes Sid End yuppy 390 bidhow would the 31 lubber 8 555 semantics differ 44 guppy 5 350 t 10 350 messsmgtmsymte t Mammy em 2 SELECT DETINCT targ e Basic SOL Query FROM relationelist WHERE quali cation 9 relationsli39st A list of relation names possibly with a rangesvariable after each name 9 far etslist A list of attributes of relations in relationslist t gaali cafion Comparisons Attr 017 const or Attrl 0p AttrZ where on is one of lts gts S 2 7 combined using AND OR and NOT 5 DISTINCT is an optional keyword indicating that the answer should not contain duplicates Default is that duplicates are n0t eliminated Conceptual Evaluation Strategy t Semantics of an SQL query defined in terms of the following conceptual evaluation strategy Compute the crossrproduct of relationsli39st Discard resulting tuples if they fail auali cations Delete attributes that are not in targetslist 1f DISTINCT is speci ed eliminate duplicate rows 9 This strategy is probably the least efficient way to ornpute a query An optimizerwill find more c efficient strategies to compute the same answers Example of Conceptual Evaluation SELECT Ssname FROM Sailors S Reserves R WHERE SsidRsid AND Rbid103 sname rating age sid bid A Note on Range Variables 9 Really needed only if the same relation appears twice in the FROM clause The previous query can also be written as SELECT Ssname FROM Sailors S Reserves R 1 1580041 We WHERE SsidRsid AND bid103 hwm r 0 use range variables OR SELECT sname always FRO Sailors Reserves WHERE SailorssidReserVessid Find sailors who ve reserved at least one boa SELECT Ssid FROM Sailors S Reserves R WHERE SSidRsid 9 Would adding DISTINCT to this query make a difference 9 What is the effect of replacing Ssid by Ssnarne in the SELECT clause Would adding DISTINCT to this variant of the query make a difference Expressions and Strings SELECT Sage age1Sage5 2Sage AS ageZ FRO a ors WHERE Ssname LIKE BB t Illustrates use of arithmetic expressions and strin pattern matching Find triples of ages of sailors and two fields defined by expressions for sailors whose names begin and end with B and contain at least three characters 6 AS and are two ways to name fields in result 6 LIKE is used for strin matching 7 stands for any one character and stands for 0 or characters Find sid39s of sailors who39ve reserved a red g a green 0 UNION Can be used to SELECT SsId r 39 39 any FROM 39l N R a two unwniwmpmble sets of WHERE 5s RadAND RbidEbid mples which are AND Equp red OR Realm green themselves the result of L ueries SQ q 1 SELECT Ssid K we replace ORbY AND 1 FROM Sailors 5 Boats E Reserves R the first Version what do WHERE SsIdRsId AND RbIdEbId D Elulup red a we ge UN ION Also BVBJlable EXCEPT SELECT am What do we get If we FROM Sailors 5 Boats E Reserves R replace UNIObe EXCEPT WHERE SsI sid AND RbIdEbId AND Eculur green Find sid39s of sailors who39ve reserved a red m a green SELECT Ssrd ROM SailnrsS Euats E1 Reserves R1 0 INTERSECT Can be used to Euats E2 Reserves R1 compute the intersection WHERE SsrdeRlsrd AND Rlbrde lbrd of any two union AND Ssr strd AND sz39rde zbrd AND E1Dlur red AND EZLDlDP green compatible sets of tuples e Included in the SQL92 SELECT Ssi ey eld EROM 39lvs R tRR H standard but some R Systems dont support it WHERE SsideRsid AND RbideEbid lup red e Contrast symmetry of the WEEK UNION and LNTERSECT SELECT Ssid queue FROM 1 m r w R the other Versions differ WHERE SsideRsid AND Rbide bid AND Emlue green Nested Queries Find names ofsdilors who39ve reserved boat 1 3 SELECT Ssname FROM Sailors S WHERE Ssid IN SELECT Rsid FR M Reserves R WHERE Rbid103 9 A very powerful feature of SQL a WHERE clause can itself contain an SQL query Actually so can FROM and HAVING clauses 9 To find sailors who ve not reserved 103 use NOT IN 9 To understand semantics of nested queries think of a nested 1002s evaluation For each Sailors tnple check the qualification by computing the snqunery Nested Queries with Correlation Find names of sailors who39ve reserved boat 103 SELECT Ssname FRO Sailors S WHERE EXETS SELECT FROM Reserves R WHERE Rbid103 AND S Rsid e EXISTS is another set comparison operator like IN 9 If UNIQUE is used and quot is replaced by erid finds sailors with at most one reservation for boat it 103 UNIQUE checks for duplicate tuples quot denotes all attributes Why do we have to replace quot by erid 9 Illustrates why in general subquery must be re corn uted for each Sailors tuple 9mm amgemmtsysmzed Rammshmnand mm More on SetrCornpurison Operator 9 We ve already seen IN EXISTS and UNIQUE Can also use NOT IN NOT EXISTS and NOT UNIQUE 9 Also available on ANY 0 ALL 0 IN gt 2Si 9 Find sailors whose rating is greater than that of some sailor called Horatio SELECT EROM Sailors S HERE Srating gt ANY SELECT S2rating EROM S ilors S2 WHERE S2sname Horatio Rewriting INTERSECT Queries Using Find sid s of sailors who ve reserved both 11 red11nd11 green boat SELECT Ssid FROM Sailors S Boats B Reserves R WHERE SsidR sid AND RbidBbid AND Bcolor red AND Ssid lN SELECT SZSid FROM Sailors S2 Boats B2 Reserves R2 WHERE S2sidR2sid AND R2bidB2bid AND B2color green 9 Similarly EXCEPT queries rewritten using NOT IN 9 To find names not sid s of Sailors who ve reserved both red and green boats just replace Ssid by Ssname in SELECT clause What about INTERSECT query Division in S QL HERE NOT Exrsrs Find sailors who ve reserved all boats 9 Let s do it the hard way without EXCEPT 2 SELECT Ssname EROM Sailors S WHERE NOT EXISTS SELECT Bbid SELECT Rb39id ROM Resems WHERE RsidSsid 0 Boats B Sailors S such that WHERE NOT EXlSTS SELECT FROM Reserves R there is no bo11t B without WHERE Rl i d his 1 i 1 Reserves tuple showing S reserved B COUNT COUNT DISTIN Aggregate Operators sum serum A AVG DETINCT A 9 Significant extension of MAX A relational algebra MIN A single column SELECT COUNT S FROM Sailors S SELECT Sm me FROM Sailors S SELECT AVG Sage WHERE SJ at mg SELE CT FROM SailorsS FROM Sailors S2 WHERE Srating10 SELECT COUNT DETINCT Sma ng FRO Sailors WHERE SSname Bob39 SELECT AVG DETINCT Sage FROM SailorsS HERE Srating10 Find name and age ofthe oldest sailors SELECT S name MAX Sage t The first query is illegal FROM sailors S We ll look into the reason a bit later when E Tss smrge S Bge i a ors we discuss GROUP BY ERE SB 9 The third query is SELECT MAX S2age equivalent to the second FROM Sailors 52 query and is allowed in SELECT S S the SQL92 standard FROM S 5 age but is not supported in WHERE ailors SELECT MAX S2age FROM Sailors S2 some systems Sage GROUP BYarld HAVING 9 So far we ve applied aggregate operators to all qualifying tuples Sometimes we want to apply them to each of several groups of tuples 9 Consider Find the age of the youngest sailoror each rating level In general we don t know how many rating levels edst and what the rating Values for these levels are Suppose we know that ratin Values go from 1 to 10 we can write 10 queries that look like this I SELECT MIN Sage ROM Sailors S mammxammamm t atmmmhml glil RE Smth i Fori 12 10 t The targetslist contains i attribute names ii terms with aggregate operations eg MIN Sage The attribute list i must be a subset of groupingelist lntuitively each answer tuple corresponds to a group and these attributes must haVe a single Value per group A group is a set of tuples that have the same Value for all attributes in groupingelist Conceptual Evaluation 9 The crossproduct of relationJist is computed tuples that fail qualification are discarded unnecessary39 fields are deleted and the remaining tuples are partitioned into groups by the value of attributes in groupingslist t The g roupsauali cation is then applied to eliminate some groups Expressions in groupsauali cation must have a single value per goug In effect an attribute in groupeauali catibn that is not an rgum of an aggregate op also appears in groupingelist SQL does not aploit primary key semantics here 9 One answer tuple is generated per qualifying group 2a Final the age ofthe youngest sailor with age 2 for each rating with at least 2 such sailors t SELECT SJ Bting MIN Sage ra7mg age FROM SailorsS 8 555 H Sage gt18 10 160 GROUP BY Sratin HAVING COUNT gt 1 7 350 1 330 a Only Srating and Sage are 10 350 mentioned in the SELECT GROUP BY or HAVING clauses other attributes unnecessary 5 2nd column of res t is unnamed Use As to name it Answer relation 21 For each red hoatfind the number of reservations for this boat SELECT Bbid COUNT AS scount FROM Sailors S Boats B Reserves R WHERE SsidRsid AND RbidBbid AND Bcolor red GROUP BY Bbid z Grouping over a join of three relations 9 What do we get if we remove Bcolor red39 from the WHERE clause and add a HAVING clause with this condition 9 What if we drop Sailors and the condition involving Ssid Final the age ofthe youngest sailor with age for each rating with at least 2 sailors ofany age SELECT Srating MIN Sage ors S WHERE S a GROUP BY Srating HAVING 1 lt SELECT COUNT FR M Sailors SZ WHERE Sra ngSZra ng 9 Shows HAVING clause can also contain a subquery 9 Compare this with the query where we considered only ratings with 2 sailors over 18 9 What if HAVING clause is replaced by v HAVING COU NTquot gt1 Find those ratings for which the averag age is the minimum over all ratings 9 Aggregate operations cannot be nested WRONG SELECT Srau ng EROM Sailors S WHERE Sage SELECT MIN AVG SZage EROM Sailors 82 v Correct solution in SQL 92 SE ECT Temprahn anage EROM SELECT Srau ng AVG Sage AS avgage EROM Sailors S GROUP BY Srau ng AS Temp WHERE Tempavgage SELECT MIN Tempavgage EROM Temp Null Values 9 Field values in a tuple are sometimes unknown eg a rating has not been assigned or inapplicable eg no spouse s name SQL provides a special Value M for such situations 9 The presence of null complicates many issues Eg i 11 Special to check it ls ratinggt8 true or false when rating is equal to null What about AND OR and NOT connectives We need a 3Nalued logic true false and unknown Meaning of constructs mustbe defined caretully eg WHERE clause eliminates rows that don39t evaluate to true In r tr r Integrity Constraints Review 9 An IC describes conditions that every legal instance of a relation must satis lnsertsdeleteSupdates that Violate lC s are disallowed Can be used to ensure application sernantics eg Sid is a key or event inconsistendes eg snarne has to be a string age must be lt 200 9 Types 0 IC s Domain constraints primary key constraints foreign key constraints general constraints 39 Field 1 39 L n r r Always enforced CREATE TABLE Sailors INTEGER General Constraints sname mania tin a e REAL 5 Useful when PRIMARY KEY sid more general ECK rating gt 1 le than keys AND rating lt 10 are involved CREATE TABLE Reserves C sname CHARun a an use queries bid INTEGER to express constraint O nolnterlakeRes CHECK lnterlake ltgt s E day DATE PRIMARY KEY bidday Constraints can C NSTRAINT be named LECT Bbname FROM Boats WHERE Bbidbid Constraints Over Multiple Relutio CREATE TABLE Sailors sid INTEGER Number ofbotzts sname CI LAR1IJ plus number of 5 Awkward and rating INTEGER sailors is lt 100 Wrong age REAL 5 HSB OYS is PRIMARY KEY Sid empty 5 CHECK umber goats SELECT COUNT Ssid FROM Sailors S tuples can e I anything SELECT COUNT Bind FROM Boats B lt 100 6 ASSERTION is the n ht 501mm CREATE ASSERTION SmallClub C K with either table SELECT COUNT Ssid FROM Sailors S SELECT COUNT Bbid FROM Boats B lt 10 a Triggers 9 Trigget procedure that starts automatically if specified changes occur to the DBMS 9 Three parts Event activates the trigger Condition tests whether the triggers should run Action what happens it the trigger runs Triggers Example SQL1999 CREATE TRIGGER youngSailorUpdate AFTER INSERT ON SAILORS REFERENCING NEW TABLE NevSailors FOR EACH STATEMENT INSERT INTO YoungSailorssid name age rating SELECT sid name age rating FROM NewSailors N WHERE Nage lt 18


Buy Material

Are you sure you want to buy this material for

25 Karma

Buy Material

BOOM! Enjoy Your Free Notes!

We've added these Notes to your profile, click here to view them now.


You're already Subscribed!

Looks like you've already subscribed to StudySoup, you won't need to purchase another subscription to get this material. To access this material simply click 'View Full Document'

Why people love StudySoup

Steve Martinelli UC Los Angeles

"There's no way I would have passed my Organic Chemistry class this semester without the notes and study guides I got from StudySoup."

Jennifer McGill UCSF Med School

"Selling my MCAT study guides and notes has been a great source of side revenue while I'm in school. Some months I'm making over $500! Plus, it makes me happy knowing that I'm helping future med students with their MCAT."

Jim McGreen Ohio University

"Knowing I can count on the Elite Notetaker in my class allows me to focus on what the professor is saying instead of just scribbling notes the whole time and falling behind."


"Their 'Elite Notetakers' are making over $1,200/month in sales by creating high quality content that helps their classmates in a time of need."

Become an Elite Notetaker and start selling your notes online!

Refund Policy


All subscriptions to StudySoup are paid in full at the time of subscribing. To change your credit card information or to cancel your subscription, go to "Edit Settings". All credit card information will be available there. If you should decide to cancel your subscription, it will continue to be valid until the next payment period, as all payments for the current period were made in advance. For special circumstances, please email


StudySoup has more than 1 million course-specific study resources to help students study smarter. If you’re having trouble finding what you’re looking for, our customer support team can help you find what you need! Feel free to contact them here:

Recurring Subscriptions: If you have canceled your recurring subscription on the day of renewal and have not downloaded any documents, you may request a refund by submitting an email to

Satisfaction Guarantee: If you’re not satisfied with your subscription, you can contact us for further help. Contact must be made within 3 business days of your subscription purchase and your refund request will be subject for review.

Please Note: Refunds can never be provided more than 30 days after the initial purchase date regardless of your activity on the site.