1 / 38

Advanced SQL

Advanced SQL. 遠山研 - 教育輪講 Brice Pesci Modern Database Management. References. The book itself Modern Database Management Ninth edition Pearson International Edition This presentation Advance Chapter Chapter 8 p398 – p340 Will be in English. Outline of the presentation.

acarmen
Download Presentation

Advanced SQL

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  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

More Related