chapter 9

• Relational set operators


  UNION, UNION ALL, INTERSECT, MINUS
• SQL JOIN operators


  • Merge rows from 2 tables and return rows with common values in common columns (natural join), meet a given join condition (equality or inequality), or have common values in common columns or have no matching values
• Inner join


  Only rows that meet a given criterion are selected
• Outer join


  Returns not only matching rows but the rows with unmatched attribute values for one table or both tables to be joined
• UNION


  Excludes duplicate records
• UNION query


  • SELECT CUS_LNAME, CUS_FNAME, CUS_INITIAL, CUS_AREACODE, CUS_PHONE FROM CUSTOMER UNION SELECT CUS_LNAME, CUS_FNAME, CUS_INITIAL, CUS_AREACODE, CUS_PHONE FROM CUSTOMER_2
• UNION ALL


  Retains the duplicate rows
• UNION ALL query


  • SELECT CUS_LNAME, CUS_FNAME, CUS_INITIAL, CUS_AREACODE, CUS_PHONE FROM CUSTOMER UNION ALL SELECT CUS_LNAME, CUS_FNAME, CUS_INITIAL, CUS_AREACODE, CUS_PHONE FROM CUSTOMER_2
• INTERSECT


  Combines rows from two queries, returning only the rows that appear in both sets
• INTERSECT query


  • SELECT CUS_LNAME, CUS_FNAME, CUS_INITIAL, CUS_AREACODE, CUS_PHONE FROM CUSTOMER INTERSECT SELECT CUS_LNAME, CUS_FNAME, CUS_INITIAL, CUS_AREACODE, CUS_PHONE FROM CUSTOMER_2
• MINUS


  Combines rows from two queries and returns only the rows that appear in the first set but not in the second
• MINUS query


  • SELECT CUS_LNAME, CUS_FNAME, CUS_INITIAL, CUS_AREACODE, CUS_PHONE FROM CUSTOMER MINUS SELECT CUS_LNAME, CUS_FNAME, CUS_INITIAL, CUS_AREACODE, CUS_PHONE FROM CUSTOMER_2
• JOIN ON clause


  Used when no common attribute exists in both tables, returns only the rows that meet the indicated join condition
• JOIN ON clause example


  • SELECT INVOICE.INV_NUMBER, PRODUCT.P_CODE FROM INVOICE JOIN PRODUCT ON INVOICE.INV_NUMBER = PRODUCT.INV_NUMBER
• Left outer join


  Returns rows matching the join condition AND rows in the left table with unmatched values
• Right outer join


  Returns rows matching the join condition AND rows in the RIGHT table with unmatched values
• Full outer join


  Returns rows matching the join condition, but also returns all of the rows with unmatched values in the table on either side
• Date and time functions


  • CURRENT_DATE, CURRENT_TIME, CURRENT_TIMESTAMP, EXTRACT, DATEDIFF, DATEADD, DATEDIFF
• Procedural SQL / Transact-SQL


  Persistent stored modules that contain standard SQL statements and procedural extensions, stored and executed at the DBMS server
• Transact-SQL anonymous block with feedback message


  • BEGIN PRINT 'This is a simple feedback message' END
• Triggers


  • Procedural SQL code that is automatically invoked by the RDBMS upon the occurrence of a given data manipulation event, can be invoked before or after a data row is inserted, updated or deleted, associated with a database table
• Statement-level trigger


  Executed once, before or after the triggering statement is completed
• Row-level trigger


  Executed once for each row affected by the triggering statement
• Stored procedures


  • Substantially reduce network traffic and increase performance, help reduce code duplication by means of code isolation and code sharing
• Stored procedure to increase discount


  • CREATE PROCEDURE IncreaseDiscount AS UPDATE PRODUCT SET DISCOUNT = DISCOUNT * 1.05 WHERE QTY_ON_HAND >= 2 * MIN_REORDER_QTY
• Cursors


  Special construct used in procedural SQL to hold the data rows returned by an SQL query, an area of memory reserved for the output
• Subqueries and correlated queries are used when it is necessary to process data based on other processed data
• Most subqueries are executed in serial fashion
• Oracle sequences may be used to generate values to be assigned to a record
• When SQL statements are designed to return more than one value inside the PL/SQL code, cursor is needed
• Embedded SQL


  Use of SQL statements within an application programming language