SQL:2003

1. SQL:2003 An Introduction to Some New Features

2. New Data Types • BIT and BIT VARYING removed • BIGINT, MULTISET and XML introduced • First class data types: can be used in all the contexts that any other (existing) SQL data type can be used; e.g. as column types, parameter and return types of SQL-revoked routines, etc

3. MULTISET(1) • MULTISET: unordered collection of elements, all of the same element type, with duplicates permitted. • SQL:1999 only has ARRAY • Element type: any SQL data type, including another collection type, e.g. INTEGER MULTISET • Unbounded

4. MULTISET(2) • Value creation for MULTISET • Enumeration: MULTISET [1,2,3,4] • Query: MULTISET (SELECT grade FROM courses) • A multiset value can be used as a table reference in FROM using UNNEST operator, e.g. SELECT T.A, T.A*2 AS B FROM UNNEST(MULTISET [1,2,3,4]) AS T(A)

5. MULTISET(3) • Operations supported by MULTISET • Casting a multiset into an array or another multiset with a compatible element type • Removing duplicates from a multiset • Returning the number of elements in a given multiset • Returning the only element of a multiset that has exactly one element • Union, intersection, difference • Three new aggregate functions • Predicates: test =, , , , etc

6. New multiset aggregate functions • COLLECT: create a multiset from the value of the argument in each row of a group • FUSION: create a multiset union of a multiset value in all rows of a group • INTERSECTION: create a multiset intersection of a multiset value in all rows of a group

7. Examples SELECT COLLECT(A) AS all_A, FUSION(B) AS all_B, INTERSECTION(B) AS common_B FROM R; R: A B a multiset[1,2] b multiset[1] c multiset[1,3] Result: all_A all_B common_B multiset[a,b,c] multiset[1,1,1,2,3] multiset[1]

8. Table Functions • SQL-invoked function that returns a “table” • Not a real table, but a MULTISET type with ROW type element • Can be queried like a table

9. Table Functions – Example (1) CREATE FUNCTION func( ) RETURNS TABLE (A CHAR(10), B INT) LANGUAGE SQL //body written in SQL READS SQL DATA //read-only access to DB DETERMINISTIC //same input/DB state -> same result RETURN TABLE( SELECT name, age FROM student); To invoke func( ): SELECT T.A FROM TABLE(func( )) AS T WHERE T.B>20;

10. Table Functions – Example(2) CREATE FUNCTION external_func( ) RETURNS TABLE (A VARCHAR(10), B INT) NOT DETERMINISTIC //same input, different results NO SQL //not call back to SQL engine to exec SQL LANGUAGE C EXTERNAL PARAMETER STYLE SQL; //associated with null indicator How is external_func( ) invoked by the SQL engine? • Open call: once. Set up data structures • Fetch call: multiple invocations. One row transmitted per invocation. • Close call: once.

11. CREATE TABLE LIKE (1) • SQL:1999 allows copying table structure into new table – but restricted to column name and type CREATE TABLE T1 ( C1 INTEGER GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 2), C2 VARCHAR(100) NOT NULL DEFAULT ‘test’); CREATE TABLE T2 ( LIKE T1, C3 CHAR(50)); CREATE TABLE T3 ( C1 INTEGER, C2 VARCHAR(100), C3 CHAR(50)); • T2 is equivalent to T3

12. CREATE TABLE LIKE (2) • SQL:2003 introduced additional (optional) options for the LIKE clause for copying more information CREATE TABLE T1 ( C1 INTEGER GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 2), C2 VARCHAR(100) NOT NULL DEFAULT ‘test’); CREATE TABLE T4 ( LIKE T1 INCLUDING COLUMN DEFAULTS INCLUDING INDENTITY); • T4 is equivalent to T1

13. CREATE TABLE AS • Create a table from a query CREATE TABLE T5 (D1, D2, D3, D4) AS ( SELECT T1.C1, T1.C2, T2.C3 FROM T1, T2 WHERE T1.C2 = T2.C2) WITH DATA; • Similar to materialized query tables(MQT) or materialized views? • No dependency

14. MERGE • Transfer data from “transaction table” to a “master table” • TT contains • Updates to the existing rows in the MT • New rows that should be inserted into the MT • MERGE = UPDATE + INSERT

15. MERGE -- Example Inventory(before) Shipment Inventory(after) PART DESC QTY PART DESC QTY PART DESC QTY 1 AAA 10 2 BBB 5 1 AAA 20 2 BBB 15 4 DDD 15 2 BBB 20 3 CCC 20 1 AAA 10 3 CCC 20 4 DDD 15 MERGE INTO Inventory AS I USING (SELECT PART,DESC,QTY FROM Shipment) AS S ON (I.PART = S.PART) WHEN MATCHED THEN UPDATE SET QTY=I.QTY+S.QTY WHEN NOT MATCHED THEN INSERT (PART,DESC,QTY) VALUES (S.PART, S.DESC, S.QTY)

16. Sequence Generator • Used for generating unique values automatically • New kind of DB object • Example CREATE SEQUENCE PartSeq AS INTEGER START WITH 1 INCREMENT BY 1 MINVALUE 1 MAXVALUE 10000 NO CYCLE INSERT INTO Shipment VALUES (NEXT VALUE FOR PartSeq, ‘EEE’, 20);

17. Identity Columns • Similar to sequence generator, but value is automatically generated • Example CREATE TABLE Parts ( PART INTEGER GENERATED ALWAYS AS INDENTITY (START WITH 1 INCREMENT BY 1 MINVALUE 1 MAXVALUE 10000 NO CYCLE), DESC VARCHAR(100), QTY INTEGER); INSERT INTO Parts(DESC,QTY) VALUES(‘FFF’,30);

18. Generated Columns • A generated column is associated with a scalar expression that evaluate the value for that column • Example CREATE TABLE Employees ( EMPID INTEGER, SALARY DECIMAL(7,2), BONUS DECIMAL(7,2), TOTAL_COMP GENERATED ALWAYS AS (SALARY + BONUS)) INSERT INTO Employees(EMPID,SALARY,BONUS) VALUES(501, 65000.00, 5000.00);