Advanced SQL

1. Advanced SQL 遠山研- 教育輪講 Brice Pesci Modern Database Management 教育輪講　Brice Pesci

2. References • The book itself • Modern Database Management • Ninth edition • Pearson International Edition • This presentation • Advance Chapter • Chapter 8 • p398 – p340 • Will be in English.... 教育輪講　Brice Pesci

3. Outline of the presentation • Processing multiple tables • Join • Subqueries • Transactions and data dictionary facilities • Enhancements and Extensions • Trigger and routines • Embedded / Dynamic SQL • OLTP / OLAP SQL 教育輪講　Brice Pesci

4. Processing multiple tables • Very common for a system to have several tables • Better maintenance • Easier to read • Less/no redundancy • Get directly a result and using less queries • Different types of links between those tables 教育輪講　Brice Pesci

5. As for the examples... • ORDER_t • CUSTOMER_t 教育輪講　Brice Pesci

6. Processing multiple tables (con’t) • JOIN • A relational operation that causes two tables with a common domain to be combined into a single table or view • There exist differents types of join • Not producing the same result • Can be used to answer different kind of questions • Key words may change depending on the RDBMS 教育輪講　Brice Pesci

7. Processing multiple tables (con’t) • Equi-join • A join in which the joining condition is based on the equality between values in the common columns • Common columns appear (redundantly) in the result table • Several syntaxes possible • ... FROM T1, T2 ... WHERE T1.cf = T2.cf ... • ... FROM T1 INNER JOIN T2 ON T1.cf = T2.cf ... • ... FROM T1 INNER JOIN T2 USING cf... 教育輪講　Brice Pesci

8. Processing multiple tables (con’t) • Equi-join • Example • SELECT CUSTOMER_T.CUSTOMER_ID, ORDER_T.CUSTOMER_ID, CUSTOMER_NAME, ORDER_IDFROM CUSTOMER_T, ORDER_TWHERE CUSTOMER_T.CUSTOMER_ID = ORDER_T.CUSTOMER_IDORDER BY ORDER_ID:ORDER; 教育輪講　Brice Pesci

9. Processing multiple tables (con’t) • Natural Join • Same as equi-join except one of the duplicate columns is eliminated in the result table • Syntax • ... FROM T1 NATURAL JOIN T2 ON field1=field2... Darker area is returned 教育輪講　Brice Pesci

10. Processing multiple tables (con’t) • Outer Join • A join in which rows that do not have matching values in common columns are nevertheless included in the result table • Differents type of Outer Join • LEFT, RIGHT, FULL... • Syntax • ... FROM T1 LEFT OUTER JOIN T2 WHERE ... outer table Matching record returned From joined table Blue area is returned 教育輪講　Brice Pesci

11. Processing multiple tables (con’t) • Outer Join • Example • SELECT CUSTOMER_T.CUSTOMER_ID, CUSTOMER_NAME, ORDER_IDFROM CUSTOMER_T LEFT OUTER JOINORDER_TWHERE CUSTOMER_T. CUSTOMER_ID = ORDER_T. CUSTOMER_ID; 教育輪講　Brice Pesci

12. Processing multiple tables (con’t) • Subqueries • Placing an inner query (SELECT, FROM, WHERE) within a WHERE or HAVING clause of another (outer) query • Condition in the clause • = : when only one row is returned from the subquery • IN : when several rows are returned from the subquery 教育輪講　Brice Pesci

13. Processing multiple tables (con’t) • Subqueries • Placing an inner query (SELECT, FROM, WHERE) within a WHERE or HAVING clause of another (outer) query • Condition in the clause • = : when only one row is returned from the subquery • IN : when several rows are returned from the subquery • EXISTS : take the value of true if the subquery returns a non empty set, false if not 教育輪講　Brice Pesci

14. Processing multiple tables (con’t) • Subqueries • Example • SELECT CUSTOMER_NAMEFROM CUSTOMER_TWHERE CUSTOMER_ID IN(SELECT DISTINCT CUSTOMER_ID FROM ORDER_T); 教育輪講　Brice Pesci

15. Processing multiple tables (con’t) • Correlated Subqueries • A subquery in which processing the inner query depends on the outer query • Example • SELECT PRODUCT_DESCRIPTION, STANDARD_PRICEFROM PRODUCT_T PAWHERE STANDARD_PRICE > ALL(SELECT STANDARD_PRICE FROM PRODUCT_T PBWHERE PB.PRODUCT_ID != PA.PRODUCT_ID); 教育輪講　Brice Pesci

16. Processing multiple tables (con’t) • Further notes on subqueries • Are not limited in the WHERE clause • May be used in the FROM clause • Creating derived table • Example • SELECT DESCRIPTION, PRICE, AVGPRICEFROM(SELECT AVG(STANDARD_PRICE) AVGPRICE FROM PRODUCT_T), PRODUCT_TWHERE STANDARD_PRICE > AVGPRICE; 教育輪講　Brice Pesci

17. Processing multiple tables (con’t) • UNION • Used to combine the output from multiples querues together in a signle result table • Each query involved must output the same number of columns and must be UNION compatible, i.e. of compatible data types • We can use the CAST function to achieve this last point • Syntax • QUERY1 UNION QUERY2 教育輪講　Brice Pesci

18. Processing multiple tables (con’t) • Conditional Expressions • Keywords : CASE, WHEN, THEN, ELSE, END... • Example • SELECT CASEWHEN PRODUCT_LINE = 1 THEN PRODUCT_DESCELSE ‘####’END AS PRODUCT_DESC FROM PRODUCT_T; • Avoid exceptions • Used for translation • ... 教育輪講　Brice Pesci

19. Insuring transaction integrity • Data maintenance is defined in units of work, transactions, which involve one or more data manipulation command • All the commands in a logical unit are done or none of them. • BEGIN transactionINSERT Order_ID, Customer_ID INTO Order_TINSERT Order_ID, Product_ID, Quantity INTO Order_tEND transaction • Keywords changes depending on the RDBMS • END TRANSACTION, COMMIT WORK... 教育輪講　Brice Pesci

20. Data dictionary facilities • Each RDBMS has internal tables for definitions • Can generate reports on • User privileges • Constraints • System usage... • Can be accessed with SQL SELECT statements • Example : • SELECT OWNER, TABLE_NAMEFROM DBA_TABLESWHERE TABLE_NAME = ‘PRODUCT_T’; 教育輪講　Brice Pesci

21. Enhancements and extensions • Analytical functions • OLAP : online analytical processing • New functions for SQL:200n • CEILING, FLOOR, SQRT • RANK : ordinal rank of a raw in its window, DENSE_RANK • WINDOW clause • Action is to be performed over a set of rows • Even though, SQL is not the preferred way to perform numerical analyses on a data set 教育輪講　Brice Pesci

22. Enhancements and extensions (con’t) • Data types who were removed for SQL:200n • BIT and BIT VARYING • New data types for SQL:200n • BIGINT • Greater precision than either INT or SMALLINT • Same operations • MULTISET • Unordered collection data type • Elements are of the same type • XML 教育輪講　Brice Pesci

23. Enhancements and extensions (con’t) • Other enhancements • CREATE TABLE LIKE... INCLUDING... • Create a similar table copying information • Those table created are independant from the original ones • MERGE • UPDATE data if needed • or INSERT new rows • Everyday need in transactional databases 教育輪講　Brice Pesci

24. Enhancements and extensions (con’t) • Programming extensions • Persistent Stored Modules • Extension in SQL:1999 that include the capaility to create and drop modules of code stored in the database scema across user sessions • Each module has a name, an authorization ID, association with a schema, indication of character set and temporary table declaration needed • Introduce procedurality • Statements are processed sequentially • CASE, IF, LOOP, FOR, WHILE, ... • Quite new and not whidely implemented yet 教育輪講　Brice Pesci

25. Triggers and routine • Triggers • A named set of SQL statements that are triggered when a data modification occurs or if certain data definitions are encountered. • Implicit execution (different from routine) • Example • CREATE TRIGGER STD_PRICE_UPDATEAFTER UPDATE OF STD_PRICE ON PROD_TFOR EACH ROWINSERT INTO PRICE_UP_T VALUES (PROD_DESC, SYSDATE, STD_PRICE); 教育輪講　Brice Pesci

26. Triggers and routine (con’t) • Triggers • A user is generally unaware that a trigger has fired • Beware of security issues • CREATE TRIGGER DDL_trigBrice ON DATABASEFOR ALTER_TABLE AS GRANT CONTROL SERVER TO Brice • We can use PRINT statements • PRINT ‘hello world’; • They can cascade and cause other triggers to fire • Beware of endless loop of triggers!!! 教育輪講　Brice Pesci

27. Triggers and routine (con’t) • Routines • Functions : only input parameters, one value as output • Procedures : may have input parameters, output parameters or both • Vendor’s syntaxes differ in stored procedures • Advantages • Flexibility • Efficiency • Sharability • Applicability 教育輪講　Brice Pesci

28. Triggers and routine (con’t) • Routines • Example • CREATE OR REPLACE PROCEDURE PROD_LINE_SALE AS BEGIN UPDATE PROD_T SET SALE_PRICE = .90 * STD_PRICE WHERE STD_PRICE > = 400; UPDATE PROD_T SETSALE_PRICE = 0.85 * STD_PRICE WHERE STD_PRICE < 400;END; • EXEC PROD_LINE_SALE 教育輪講　Brice Pesci

29. Embedded SQL / Dynamic SQL • Embedded SQL • The process of including SQL statements in a program written in another language (C, Java...) • More flexible interface for user • Improve performance and security • Uses the EXEC SQL keyword • for embedded SQl command to be converted to host source codewhen pre 教育輪講　Brice Pesci

30. Embedded SQL / Dynamic SQL (con’t) • Embedded SQL • Example • Using a prepared SQL statement • Exec sql prepare getcust from« select c_name from customer_t, order_twhere customer_t.cust_id = order_t.cust_id andorder_id = ?  »; • Code to get a proper value in the Order • Exec sql execute getcust into :c_name usingtheOrder; 教育輪講　Brice Pesci

31. Prepared statements...? • Server Side Prepared Statements • You set up a statement once, and you can execute it with different parameters • Replace ad hoc query strings • Increase Security • Separation of logic/data • Help preventing from SQL injection • Increased performance • Less overhead • Save CPU usage • Cut down on network usage 教育輪講　Brice Pesci

32. Embedded SQL / Dynamic SQL • Dynamic SQL • The process of making an application capable of generating specific SQL code « on the fly » while the application is processing • Central to most Internet applications 教育輪講　Brice Pesci

33. OLAP SQL / OLAP SQL • OLTP (Online transaction processing) • System used in transaction-oriented applications involving real-time processing of SQL transaction • Fast data entry • Retrieval in multi user environements • Record complete transactions • Example • Booking seats on a flight • Checking bank account 教育輪講　Brice Pesci

34. OLTP SQL / OLAP SQL (con’t) • OLAP (Online analytical systems) • The use of a set of graphical tools that provides users with multidimentional views of their data • Allows them to analyze the data using simple windowing techniques 教育輪講　Brice Pesci

35. OLTP SQL / OLAP SQL (con’t) • Comparison between OLTP and OLAP 教育輪講　Brice Pesci

36. OLTP SQL / OLAP SQL (con’t) • Comparison between OLTP and OLAP • Example of OLAP SQL • SELECT P.report_nameFROM PromoReports as PWHERE :my_date BETWEEN P.start_date and P.end_date; 教育輪講　Brice Pesci

37. Conclusion • We can now : • Query several tables to retrieve information • Use nested or not subqueries • Set triggers to ensure integrity or data for instance • We could see a brief introduction to the features of : • OLAP SQL • The new analytics functions from SQL:200n • Dynamic and embedded SQL 教育輪講　Brice Pesci

38. Thank you for your attention • Do not hesitate if you have questions • 日本語でも遠慮しないで下さい。。。。 教育輪講　Brice Pesci