Downloaded 92 times
Uploaded bywelcometofacebook
PPTX, PDF2,431 views
advanced sql(database)
This document discusses advanced SQL topics including joins, subqueries, and ensuring transaction integrity. It provides examples of different types of joins like equi-joins, natural joins, outer joins, and union joins. It also discusses using subqueries in WHERE clauses, FROM clauses, and HAVING clauses, and differentiates between correlated and noncorrelated subqueries. The document concludes by defining transactions and describing SQL commands like BEGIN TRANSACTION, COMMIT, and ROLLBACK that are used to ensure transaction integrity.
Recommended
More Related Content
What's hot
Viewers also liked
Similar to advanced sql(database)
More from welcometofacebook
Recently uploaded
advanced sql(database)
- 1.Lecture 8:Advanced SQLISOM3260,Spring 2014
- 2.2Where we arenow• Database environment– Introduction to database• Database development process– steps to develop a database• Conceptual data modeling– entity-relationship (ER) diagram; enhanced ER• Logical database design– transforming ER diagram into relations; normalization• Physical database design– technical specifications of the database• Database implementation– Structured Query Language (SQL), Advanced SQL• Advanced topics– data and database administration
- 3.3Database development activitiesduring SDLC
- 4.4Advanced SQL• Joins•Subqueries• Ensuring Transaction Integrity
- 5.5Processing Multiple Tables– Joins• Join– a relational operation that causes two or more tables with a common domainto be combined into a single table or view– the common columns in joined tables are usually the primary key of thedominant table and the foreign key of the dependent table• Equi-join– a join in which the joining condition is based on equality between values inthe common columns; common columns appear redundantly in the resulttable• Natural join– an equi-join in which one of the duplicate columns is eliminated in the resulttable• Outer join– a join in which rows that do not have matching values in common columnsare nevertheless included in the result table (as opposed to inner join, inwhich rows must have matching values in order to appear in the result table)• Union join– includes all columns from each table in the join, and an instance for each rowof each table
- 6.6Figure 7-1: SamplePine Valley Furniture dataCustomer_T Order_TOrder_Line_TProduct_T
- 7.7Example: Equi-join• basedon equality between values in the common columnsIf WHERE clause is omitted, the query will return all combinations of customersand orders (10 orders * 15 customers=150 rows).
- 8.8• same asequi-join except that one of the duplicate columns iseliminated in the result table• most commonly used form of join operation• For each customer who has placed an order, what is the customer’sname and order number?SELECT Customer_T.Customer_ID, Customer_Name, Order_IDFROM Customer_T, Order_TWHERE Customer_T.Customer_ID = Order_T.Customer_IDJoin involves multiple tables inFROM clauseExample: Natural JoinWHERE clause performs the equality checkfor common columns of the two tablesIt must be specified from which table theDBMS should pick Customer_ID
- 9.9• Different syntaxwith same outcomeSELECT Customer_T.Customer_ID,Customer_Name,Order_IDFROM Customer_T INNER JOIN Order_TON Customer_T.Customer_ID = Order_T.Customer_ID;SELECT Customer_T.Customer_ID,Customer_Name,Order_IDFROM Customer_T NATURAL JOIN Order_TON Customer_T.Customer_ID = Order_T.Customer_ID;Example: Natural Join
- 10.10• row inone table does not have a matching row in the other table• null values appear in columns where there is no match between tables• List customer name, ID number, and order number for all customers.Include customer information even for customers that do not have anorder.SELECT Customer_T.Customer_ID, Customer_Name, Order_IDFROM Customer_T LEFT OUTER JOIN Order_TON Customer_T.Customer_ID = Order_T.Customer_ID;Example: Outer JoinLEFT OUTER JOIN syntax will cause customer datato appear even if there is no corresponding order data
- 11.
- 12.12• The resultstable will contain all of the columns from each table andwill contain an instance for each row of data included from each table.• Example:– Customer_T has 15 customers and 6 attributes– Order_T has 10 orders and 3 attributes• Result Table– 25 rows and 9 columns– each customer row will contain 3 attributes with assigned null values– each order row will contain 6 attributes with assigned null valuesExample: Union Join
- 13.13• Query: Assembleall information necessary to create aninvoice for order number 1006.SELECT Customer_T.Customer_ID, Customer_Name, Customer_Address,City, State, Postal_Code, Order_T.Order_ID, Order_Date, Quantity,Product_Name, Unit_Price, (Quantity * Unit_Prrice)FROM Customer_T, Order_T, Order_Line_T, Product_TWHERE Customer_T.Customer_ID = Order_T.Customer_IDAND Order_T.Order_ID = Order_Line_T.Order_IDAND Order_Line_T.Product_ID = Product_T.Product_IDAND Order_T.Order_ID = 1006;Four tables involved in this joinExample: Multiple Join of Four TablesEach pair of tables requires an equality-checkcondition in the WHERE clause, matchingprimary keys against foreign keys
- 14.14From CUSTOMER_T tableFromORDER_TtableFrom PRODUCT_T tableFromORDER_LINE_T tableExpressionFigure 7-4: Results from a four-table join
- 15.15Subqueries• Subquery– placingan inner query (SELECT statement) inside an outerquery– result table display data from the table in the outer query only• Options:– In a condition of the WHERE clause– As a “table” of the FROM clause– Within the HAVING clause• Subqueries can be:– Noncorrelated execute inner query once for the entire outer query– Correlated execute inner query once for each row returned by the outer query
- 16.16Example 1: Subqueries•Two equivalent queries– one using a join– one using a subquery
- 17.17• Which customershave placed orders?SELECT Customer_NameFROM Customer_TWHERE Customer_ID IN(SELECT DISTINCT Customer_ID FROM Order_T);Example 2: SubquerySubquery is embedded in parentheses. In this case it returns alist that will be used in the WHERE clause of the outer queryThe IN operator will test to seeif the Customer_ID value of arow is included in the listreturned from the subquery
- 18.18Figure 7-8(a):Processing anoncorrelatedsubqueryNoreference to datain outer query, sosubquery executesonce only
- 19.19Example 3: Subquery•The qualifier NOT may be used in front of IN; while ANY andALL with logical operators such as =, >, and <.A join can be usedin an inner queryNote: Inner query return list of customers who had ordered computer desk.Outer query list customers who were not in the list returned by inner query.
- 20.20Correlated vs. NoncorrelatedSubqueries•Noncorrelated subqueries– do not depend on data from the outer query– execute once for the entire outer query• Correlated subqueries– do make use of data from the outer query– execute once for each row of the outer query– can make use of the EXISTS operator
- 21.21• What arethe order numbers that include furniture finishedin natural ash?SELECT DISTINCT Order_ID FROM Order_Line_TWHERE EXISTS(SELECT * FROM Product_TWHERE Product_ID = Order_Line_T.Product_IDAND Product_Finish = ‘Natural Ash’);Example 4: Correlated SubqueryThe subquery is testing for a valuethat comes from the outer query .The EXISTS operator will return aTRUE value if the subquery resultedin a non-empty set, otherwise itreturns a FALSE.
- 22.22Figure 7-8(b):Processing acorrelatedsubquerySubqueryrefers to outer-query data,so executes once for each rowof outer queryORDER_ID1001100210031006100710081009Result:
- 23.23Example 5: CorrelatedSubqueries
- 24.24• Which productshave a standard price that is higher that theaverage standard price?SELECT Product_Description, Standard_Price, AVGPRICEFROM(SELECT AVG(Standard_Price) AVGPRICE FROM Product_T),Product_TWHERE Standard_Price > AVGPRICE;Example 6: Subquery as Derived TableThe WHERE clause normally cannot include aggregate functions, but because theaggregate is performed in the subquery, its result can be used in the outer query’sWHERE clauseOne column of the subquery isan aggregate function that has analias name. That alias can then bereferred to in the outer querySubquery forms the derivedtable used in the FROM clauseof the outer query
- 25.25Ensuring Transaction Integrity•Transaction– a discrete unit of work that must be completely processed ornot processed at all– may involve multiple DML commands INSERT, DELETE, UPDATE– if any command fails, then all other changes must be cancelled• SQL commands for transactions– BEGIN TRANSACTION/END TRANSACTION marks boundaries of a transaction– COMMIT makes all changes permanent– ROLLBACK cancels changes since the last COMMIT
- 26.26Figure 7-12: AnSQL Transaction sequence (in pseudocode)
- 27.27Review Questions• Whatare the 4 types of join?• What are subqueries?• What is a correlated subquery?• What is a noncorrelated subquery?• What is a transaction?