CGS 2545: Database Concepts Spring 2012

CGS 2545: Database ConceptsSpring 2012Chapter 7 – Advanced SQLInstructor :Dr. Mark Llewellynmarkl@cs.ucf.eduHEC 236, spr2012Department of Electrical Engineering and Computer ScienceComputer Science DivisionUniversity of Central FloridaCGS 2545: Database Concepts (Chapter 7)Page 1Dr. Mark Llewellyn

Processing Multiple Tables – Joins The ability to combine, or join, tables on commonattributes is one of the most important advantages thatrelational databases have over other types ofdatabases. A join is performed when data are retrieved frommore than one table at a time. There are several different types of join operations asillustrated on the next page. In general, a join operation causes two or more tableswith a common domain to be combined into a singletable or view.CGS 2545: Database Concepts (Chapter 7)Page 2Dr. Mark Llewellyn

Processing Multiple Tables – Joins Theta-join –a join in which the joining condition is based onequality between values in the common columns; common columnsappear redundantly in the result table. If all join conditions areequality, then the operation is known as an equi-join. Natural join – an equi-join in which one of the duplicatecolumns is eliminated in the result table. Outer join –a join in which rows that do not have matchingvalues in common columns are nonetheless included in the resulttable (as opposed to inner join, in which rows must have matchingvalues in order to appear in the result table). Union join – includes all columns from each table in the join,and an instance for each row of each table.The common columns in joined tables are usually the primary key of thedominant table and the foreign key of the dependent table in 1:Mrelationships.CGS 2545: Database Concepts (Chapter 7)Page 3Dr. Mark Llewellyn

Theta-Join and Equijoin OperatorsType: binarySymbol/general form: r predicate sSchema of result relation: concatenation of operand relationsDefinition: r predicate s (predicate)(r s)Examples:r color 'blue' AND size 3 san equijoinr color 'blue' AND size 3 sa theta-join The theta-join operation is a shorthand for a Cartesian product followed by aselection operation. The equijoin operation is a special case of the theta-join operation thatoccurs when all of the conditions in the predicate are equality conditions. Neither a theta-join nor an equijoin operation eliminates extraneous tuplesby default. Therefore, the elimination of extraneous tuples must be handledexplicitly via the predicate.CGS 2545: Database Concepts (Chapter 7)Page 4Dr. Mark Llewellyn

Theta-Join Operator Examplesr R (R.B S.F) S 2545: Database Concepts (Chapter 7)Page 5Dr. Mark Llewellyn

Natural Join OperatorType: binaryr sSymbol/general form:Schema of result relation: concatenation of operand relations withonly one occurrence of commonly named attributesDefinition: r s r (r.commonattributes s.commonattributes) sExamples:s spj p The natural-join operation performs an equijoin over all attributes in the twooperand relations which have the same attribute name. The degree of the result relation of a natural-join is sum of the degrees of thetwo operand relations less the number of attributes which are common toboth operand relations. (In other words, one occurrence of each commonattribute is eliminated from the result relation.) The natural join is probably the most common of all the forms of the joinoperation. It is extremely useful in the removal of extraneous tuples. Thoseattributes which are commonly named between the two operand relations arecommonly referred to as the join attributes.CGS 2545: Database Concepts (Chapter 7)Page 6Dr. Mark Llewellyn

Natural Join Operator ExamplesRr yes34amno6nno56amno6ABCDGHbrno7yes30r TCGS 2545: Database Concepts (Chapter 7)Page 7Dr. Mark Llewellyn

Outer Join OperatorType: binarySymbol/general form: left-outer-join: r sright-outer-join: r sfull outer join: r sSchema of result relation: concatenation of operand relationsDefinition:r s natural join of r and s with tuples from r which do not have a matchin s included in the result. Any missing values from s are set to null.r s natural join of r and s with tuples from s which do not have a matchin r included in the result. Any missing values from r are set to null.r s natural join of r and s with tuples from both r and s which do nothave a match are included in the result. Any missing values are set to null.Examples: Let r(A,B) {(a, b), (c, d), (b,c)} and lets(A,C) {(a, d), (s, t), (b, d)}then r s (A,B,C) {(a,b,d), (b,c,d), (c,d,null)},r s (A,B,C) {(a,b,d), (b,c,d), (s,null,t)}, andr s (A,B,C) {(a,b,d), (b,c,d), (s,null,t), (c,d,null)},CGS 2545: Database Concepts (Chapter 7)Page 8Dr. Mark Llewellyn

Outer Join Operator ExamplesRr R SABCABCD1231231045612311789456null789nullnull6712r R SSBCDABCD2310123102311123116712456null789nullCGS 2545: Database Concepts (Chapter 7)Page 9r R SBCDA23101231116712nullDr. Mark Llewellyn

An Example ssnumpnumjnumqtydateThe last three pages of this set of notescontain screen shots for these for tablesfrom a sample database using thesetables. They might help to make some ofthe following examples more clear.CGS 2545: Database Concepts (Chapter 7)Page 10Dr. Mark Llewellyn

Natural Join Example 1Query: List only the names (remove duplicates) of thosesuppliers who have a shipment with a quantity 15.SELECT DISTINCT snameFROM Suppliers NATURAL JOIN ShipmentsWHERE quantity 15;Note that Access does not support the natural join syntax shownabove. In Access this query is expressed as shown on the next page.CGS 2545: Database Concepts (Chapter 7)Page 11Dr. Mark Llewellyn

Natural Join Example 1 In AccessThis comma isthe generic joinoperator in SQL.This condition is called an “implicit join condition”. It specifies thecriteria on which the join is to occur.In this case, the primary key in suppliers and the foreign key (part ofthe primary key) in the shipments table must be the same.CGS 2545: Database Concepts (Chapter 7)Page 12Dr. Mark Llewellyn

SQL query entered inAccess and executedshowing results.CGS 2545: Database Concepts (Chapter 7)Page 13Dr. Mark Llewellyn

Natural Join Example 2Query: List only the names (remove duplicates) of thosecities in which both a supplier and a job are located.SELECT DISTINCT Supplier.cityFROM Suppliers NATURAL JOIN Jobs;Access Version:SELECT DISTINCT Supplier.cityFROM Suppliers, JobsWHERE Suppliers.city Jobs.city;CGS 2545: Database Concepts (Chapter 7)Page 14Dr. Mark Llewellyn

SQL query entered inAccess and executedshowing results.CGS 2545: Database Concepts (Chapter 7)Page 15Dr. Mark Llewellyn

Natural Join Example 3Query: List only the names (remove duplicates) of thosejobs which receive a shipment from supplier number 1.SELECT DISTINCT Jobs.jnameFROM Jobs NATURAL JOIN ShipmentsWHERE Shipments.snum 1;Access Version:SELECT DISTINCT Jobs.jnameFROM Jobs, ShipmentsWHERE Jobs.jnum Shipments.jnumAND Shipments.snum 1;CGS 2545: Database Concepts (Chapter 7)Page 16Dr. Mark Llewellyn

SQL query entered inAccess and executedshowing results.CGS 2545: Database Concepts (Chapter 7)Page 17Dr. Mark Llewellyn

Left Outer Join Example List the supplier numbers and names along with thequantity of each order a supplier has and includesupplier information even for suppliers who have noshipments.Access version:SELECT Suppliers.snum, Suppliers.sname,Shipments.quantityFROM Suppliers LEFT OUTER JOIN ShipmentsON Suppliers.snum Shipments.snum;LEFT OUTER JOIN syntax with ON keyword instead of WHEREcauses supplier information to appear even if there is no correspondingshipment information for that supplier.CGS 2545: Database Concepts (Chapter 7)Page 18Dr. Mark Llewellyn

Left Outer Join ExampleSQL query entered inAccess and executedshowing results.Suppliers 6, 7,8, and 9 haveno shipment.sCGS 2545: Database Concepts (Chapter 7)Page 19Dr. Mark Llewellyn

Right Outer Join Example List all the information about each shipment and thepart number of every part that is not shipped by anysupplier.Access version:SELECT Shipments.*, Parts.pnumFROM Shipments RIGHT OUTER JOIN PartsON Shipments.pnum Parts.pnum;RIGHT OUTER JOIN syntax with ON keyword instead of WHEREcauses part information to appear even if there is no correspondingshipment information for that part.CGS 2545: Database Concepts (Chapter 7)Page 20Dr. Mark Llewellyn

SQL query entered inAccess and executedshowing results.Parts 5,6,7 and 10 arenot being shipped.CGS 2545: Database Concepts (Chapter 7)Page 21Dr. Mark Llewellyn

Multiple Table Join Example 1 List the supplier name and city for every supplier who has ashipment of a blue part.SQL Version:SELECT Suppliers.sname, Suppliers.cityFROM Suppliers NATURAL JOIN Shipments NATURAL JOIN PartsWHERE Parts.color “blue”;Access Version:SELECT Suppliers.sname, Suppliers.cityFROM Suppliers,Shipments,PartsWHERE Suppliers.snum Shipments.snumAND Shipments.pnum Parts.pnumAND Parts.color “blue”;Each pair of tables requires an implicit join condition in the WHERE clause,matching primary keys against foreign keysCGS 2545: Database Concepts (Chapter 7)Page 22Dr. Mark Llewellyn

SQL query entered inAccess and executedshowing results.CGS 2545: Database Concepts (Chapter 7)Page 23Dr. Mark Llewellyn

Multiple Table Join Example 2 List the supplier names for those suppliers who supply a red part toany job in Tampa in a quantity 20.SQL Version:SELECT Suppliers.snameFROM Suppliers NATURAL JOIN Shipments NATURAL JOIN PartsNATURAL JOIN JobsWHERE Parts.color “red” AND Jobs.city “Tampa”AND Shipments.quantity 20;Access Version: (see next page)CGS 2545: Database Concepts (Chapter 7)Page 24Dr. Mark Llewellyn

SQL query entered inAccess and executedshowing results.CGS 2545: Database Concepts (Chapter 7)Page 25Dr. Mark Llewellyn

Processing Multiple Tables Using Subqueries A subquery is formed by placing a query inside aquery, i.e., placing a SELECT statement (the innerquery) inside a SELECT statement (the outer query). A subquery can occur in several different location:The options are:– In a condition of the WHERE clause.– As a “table” of the FROM clause.– Within the HAVING clause. Subqueries can be:– Noncorrelated – executed once for the entire outer query.– Correlated – executed once for each row returned by theouter query.CGS 2545: Database Concepts (Chapter 7)Page 26Dr. Mark Llewellyn

Correlated vs. Noncorrelated Subqueries Noncorrelated subqueries:– Do not depend on data from the outer query.– Execute once for the entire outer query. Correlated subqueries:– Make use of data from the outer query.– Execute once for each row of the outer query.– Can use the EXISTS operator.CGS 2545: Database Concepts (Chapter 7)Page 27Dr. Mark Llewellyn

Subquery Example 1 (Where clause) List the name of the supplier who shipped shipment number 6.Outer querySQL:SELECT Suppliers.snameFROM SuppliersWHERE snum (SELECT snumFROM ShipmentsWHERE shipment id 6);Inner queryNo reference is madein the inner query toany value in the outerquery, hence this is anoncorrelated query.CGS 2545: Database Concepts (Chapter 7)Page 28Dr. Mark Llewellyn

SQL query entered inAccess and executedshowing results.CGS 2545: Database Concepts (Chapter 7)Page 29Dr. Mark Llewellyn

Subquery Example 2 (Where clause) List the names of those suppliers who at least one shipment.SQL:SELECT Suppliers.snameFROM SuppliersWHERE snum IN (SELECT DISTINCT snumFROM Shipments);The IN operator is a setoperator that checks to see ifthe left-hand operand (a valueor set member instance) iscontained in the right-handoperand (a set).The INoperator returns true or false.CGS 2545: Database Concepts (Chapter 7)Page 30Dr. Mark Llewellyn

SQL query entered inAccess and executedshowing results.CGS 2545: Database Concepts (Chapter 7)Page 31Dr. Mark Llewellyn