Advanced SQL Brice Pesci Modern Database Management Brice

Advanced SQL 遠山研- 教育輪講 Brice Pesci Modern Database Management 教育輪講　Brice Pesci 2009/03/19

References � The book itself ◦ Modern Database Management ◦ Ninth edition ◦ Pearson International Edition � This ◦ ◦ presentation Advance Chapter 8 p 398 – p 340 Will be in English. . 教育輪講　Brice Pesci 2009/03/19

Outline of the presentation � Processing ◦ Join ◦ Subqueries multiple tables � Transactions and data dictionary facilities � Enhancements and Extensions � Trigger and routines � Embedded / Dynamic SQL � OLTP / OLAP SQL 教育輪講　Brice Pesci 2009/03/19

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 2009/03/19

As for the examples. . . � ORDER_t � CUSTOMER_t Order_ID Order_Date Customer_ID 1001 10/21/2008 1 1002 10/21/2008 8 1003 10/22/2008 15 . . Customer_ ID Customer_N ame Customer_A. . . ddress 1 ryuryu Yagami 2 kabuki Toyama ken. . . 3 hayashi Aobeya . . . . 教育輪講　Brice Pesci 2009/03/19 . . .

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 2009/03/19

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 T 1, T 2. . . WHERE T 1. cf = T 2. cf. . . �. . . FROM T 1 INNER JOIN T 2 ON T 1. cf = T 2. cf. . . �. . . FROM T 1 INNER JOIN T 2 USING cf. . . 教育輪講　Brice Pesci 2009/03/19

Processing multiple tables (con’t) � Equi-join ◦ Example �SELECT CUSTOMER_T. CUSTOMER_ID, ORDER_T. CUSTOMER_ID, CUSTOMER_NAME, ORDER_ID FROM CUSTOMER_T, ORDER_T WHERE CUSTOMER_T. CUSTOMER_ID = ORDER_T. CUSTOMER_ID ORDER BY ORDER_ID: ORDER; CUSTOMER_ID CUSTOMER_NA ORDER_ID ME 1 1 ryuryu 1001 8 8 bluemountain 1002 . . . 教育輪講　Brice Pesci 2009/03/19

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 T 1 NATURAL JOIN T 2 ON field 1=field 2. . . Darker area is returned 教育輪講　Brice Pesci 2009/03/19

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 T 1 LEFT OUTER JOIN T 2 WHERE. . . outer table Matching record returned From joined table Blue area is returned 教育輪講　Brice Pesci 2009/03/19

Processing multiple tables (con’t) � Outer Join ◦ Example �SELECT CUSTOMER_T. CUSTOMER_ID, CUSTOMER_NAME, ORDER_ID FROM CUSTOMER_T LEFT OUTER JOIN ORDER_T WHERE CUSTOMER_T. CUSTOMER_ID = ORDER_T. CUSTOMER_ID; CUSTOMER_ID CUSTOMER_NAME ORDER_ID 1 ryuryu 1001 1 ryuryu 1010 . . . 教育輪講　Brice Pesci 2009/03/19

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 2009/03/19

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 2009/03/19

Processing multiple tables (con’t) � Subqueries ◦ Example �SELECT CUSTOMER_NAME FROM CUSTOMER_T WHERE CUSTOMER_ID IN (SELECT DISTINCT CUSTOMER_ID FROM ORDER_T); CUSTOMER_NAME ryuryu bluemountain park 教育輪講　Brice Pesci 2009/03/19

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_PRICE FROM PRODUCT_T PA WHERE STANDARD_PRICE > ALL (SELECT STANDARD_PRICE FROM PRODUCT_T PB WHERE PB. PRODUCT_ID != PA. PRODUCT_ID); 教育輪講　Brice Pesci 2009/03/19

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, AVGPRICE FROM (SELECT AVG(STANDARD_PRICE) AVGPRICE FROM PRODUCT_T), PRODUCT_T WHERE STANDARD_PRICE > AVGPRICE; 教育輪講　Brice Pesci 2009/03/19

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 �QUERY 1 UNION QUERY 2 教育輪講　Brice Pesci 2009/03/19

Processing multiple tables (con’t) � Conditional Expressions ◦ Keywords : CASE, WHEN, THEN, ELSE, END. . . ◦ Example �SELECT CASE WHEN PRODUCT_LINE = 1 THEN PRODUCT_DESC ELSE ‘####’ END AS PRODUCT_DESC FROM PRODUCT_T; ◦ Avoid exceptions ◦ Used for translation ◦. . . PRODUCT_DESC Product 1 ####. . . 教育輪講　Brice Pesci 2009/03/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 transaction INSERT Order_ID, Customer_ID INTO Order_T INSERT Order_ID, Product_ID, Quantity INTO Order_t END transaction � Keywords changes depending on the RDBMS ◦ END TRANSACTION, COMMIT WORK. . . 教育輪講　Brice Pesci 2009/03/19

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_NAME FROM DBA_TABLES WHERE TABLE_NAME = ‘PRODUCT_T’; 教育輪講　Brice Pesci 2009/03/19

Enhancements and extensions � Analytical functions ◦ OLAP : online analytical processing ◦ New functions for SQL: 200 n �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 2009/03/19

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

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 2009/03/19

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 2009/03/19

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_UPDATE AFTER UPDATE OF STD_PRICE ON PROD_T FOR EACH ROW INSERT INTO PRICE_UP_T VALUES (PROD_DESC, SYSDATE, STD_PRICE); 教育輪講　Brice Pesci 2009/03/19

Triggers and routine (con’t) � Triggers ◦ A user is generally unaware that a trigger has fired �Beware of security issues �CREATE TRIGGER DDL_trig. Brice ON DATABASE FOR 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 2009/03/19

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 2009/03/19

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 SET SALE_PRICE = 0. 85 * STD_PRICE WHERE STD_PRICE < 400; END; �EXEC PROD_LINE_SALE 教育輪講　Brice Pesci 2009/03/19

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 2009/03/19

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_t where customer_t. cust_id = order_t. cust_id and order_id = ? » ; ◦ Code to get a proper value in the Order �Exec sql execute getcust into : c_name using the. Order; 教育輪講　Brice Pesci 2009/03/19

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 2009/03/19

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 2009/03/19

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 2009/03/19

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 2009/03/19

OLTP SQL / OLAP SQL (con’t) � Comparison between OLTP and OLAP OLTP OLAP Size As little data as necessary / archives Detailed records for analysis Data sources Usually collect data at Capture data from one source several databases Processing Record complete transactions Users Used by many people Used only y a few with different people background Queries Simple Analyze the transactions Complicated queries 教育輪講　Brice Pesci 2009/03/19

OLTP SQL / OLAP SQL (con’t) � Comparison between OLTP and OLAP OLTP OLAP Normalisation Normalized DB Preserve integrity Work with static data Data warehouse are denormalized (perf) Query tools Standard SQL (portable) Use OLAP SQL and sophisticated statictical packages � Example of OLAP SQL �SELECT P. report_name FROM Promo. Reports as P WHERE : my_date BETWEEN P. start_date and P. end_date; 教育輪講　Brice Pesci 2009/03/19

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: 200 n ◦ Dynamic and embedded SQL 教育輪講　Brice Pesci 2009/03/19

Thank you for your attention � Do not hesitate if you have questions � 日本語でも遠慮しないで下さい。。。。 教育輪講　Brice Pesci 2009/03/19