SQL III

1. SQL III

2. Pseudocolumns • Rownum (numeric) • A number for each row in a select query • Example: • Branch(bname, bcity) • SELECT rownum ordered_branch_seq, bname, bcity FROM branch ORDER BY bname; • Rowid (char string) • A character string that represents the physical address for a row of a table. • SELECT rowid, bname, bcity FROM branch. • Access by rowid fastest. • However, rowids may change during some table maintenance operations • Split of table partitions. • If you delete and insert the same branch name, the rowids for that row are likely to be different

3. CONNECT-BY (not in book) • Person (father, name) • Find the children of ‘Mike Johnson’ • SELECT a.name FROM Person a where a.father = ‘Mike Johnson’; • What-if you want to find great-great-grandchildren? • Easier way in Oracle: CONNECT-BY • Useful for answering hierarchical queries

4. CONNECT-BY • Find all descendants from Mike Johnson • SELECT a.name FROM Person a START WITH a.name = ‘Mike Johnson’ CONNECT BY PRIOR name = father; • First Level (of recursion) • Mike Johnson • Second Level • All names for which Mike is a father (i.e., children of Mike) • Third Level • All names for which Mike’s children are fathers • ….

5. CONNECT-BY • Find all descendants from Mike Johnson • SELECT a.name FROM Person a START WITH a.name = ‘Mike Johnson’ CONNECT BY PRIOR name = father; • Pseudocolumn: LEVEL SELECT a.name, LEVEL FROM Person a START WITH a.name = ‘Mike Johnson’ CONNECT BY PRIOR name = father; • Ordering among siblings: SELECT a.name, LEVEL FROM Person a START WITH a.name = ‘Mike Johnson’ CONNECT BY PRIOR name = father ORDER SIBLINGS BY name;

6. CONNECT-BY • Find upto grand-children only for Mike Johnson • Restrict the LEVEL to be <=3 (Mike, his children and grand-children) • What if you have “loops” in the data? • Oracle raises an error • To ignore the loops, specify NOCYCLE SELECT a.name, LEVEL FROM Person a START WITH a.name = ‘Mike Johnson’ CONNECT BY NOCYCLE PRIOR name = father ORDER SIBLINGS BY name; Lot more Information: Oracle Help links, or http://www.oracle.com/technology/oramag/webcolumns/2003/techarticles/gennick_connectby.html http://www.stanford.edu/dept/itss/docs/oracle/10g/server.101/b10759/queries003.htm

7. SQL Summary Query Language (DML): SELECT, FROM, WHERE GROUP BY, HAVING ORDER BY Nested Queries (IN, EXISTS, UNIQUE, ALL, SOME, correlation) Aggregates Joins Updates Views PseudoColumns Connect-by hierarchical queries More to come: Inserts, deletes ,…. DDL: CREATE TABLE DROP TABLE ALTER TABLE • Embedded SQL/JDBC • Integrity Constraints • Security, authorization

8. SQL: Modification Commands Deletion: DELETE FROM <relation> [WHERE <predicate>] Example: 1. DELETE FROM account -- deletes all tuples in account 2. DELETE FROM account WHERE bname in (SELECT bname FROM branch WHERE bcity = ‘Bkln’) -- deletes all accounts from Brooklyn branch

9. DELETE • Delete the record of all accounts with balances below the average at the bank. DELETE FROM accountWHEREbalance < (SELECT AVG(balance)FROMaccount) • Problem: as we delete tuples from deposit, the average balance changes • Solution used in SQL: • 1. First, compute avg balance and find all tuples to delete • 2. Next, delete all tuples found above (without recomputing avg or retesting the tuples)

10. SQL: Modification Commands Insertion: INSERT INTO <relation> values (.., .., ...) or INSERT INTO <relation>(att1, .., attn) values( ..., ..., ...) or INSERT INTO <relation> <query expression> Examples: INSERT INTO account VALUES (“Perry”, A-768, 1200) or INSERT INTO account( bname, acct_no, balance) VALUES (“Perry”, A-768, 1200) INSERT INTO account SELECT bname, lno, 200 FROM loan WHERE bname = “Kenmore” gives free $200 savings account for each loan holder at Kenmore

11. SQL: Modification Commands Update: UPDATE <relation> SET <attribute> = <expression> WHERE <predicate> Ex. UPDATE account SET balance = balance * 1.06 WHERE balance > 10000 UPDATE account SET balance = balance * 1.05 WHERE balance <= 10000 Alternative: UPDATE account SET balance = (CASE WHEN balance <= 10000 THEN balance*1.05 ELSE balance*1.06 END)

12. SQL: Modification Commands View Updates: Suppose we have a view: CREATE VIEW branch-loan AS SELECT bname, lno FROM loan And we insert: INSERT INTO branch-loan VALUES( “Perry”, L-308) Then, the system will insert a new tuple ( “Perry”, L-308, NULL) into loan

13. SQL: Modification Commands What about... CREATE VIEW depos-account AS SELECT cname, bname, balance FROM depositor as d, account as a WHERE d.acct_no = a.acct_no INSERT INTO depos-account VALUES( “Smith”, “Perry”, 500) How many relations we need to update? Many systems disallow

14. Built-in Data Types in SQL CREATE TABLE person (name VARCHAR2, age NUMBER); • date: Dates, containing a (4 digit) year, month and date • Example: date ‘2005-7-27’ • time: Time of day, in hours, minutes and seconds. • Example: time ‘09:00:30’ time ‘09:00:30.75’ • timestamp: date plus time of day • Example: timestamp ‘2005-7-27 09:00:30.75’ • interval: period of time • Example: interval ‘1’ day • Subtracting a date/time/timestamp value from another gives an interval value • Interval values can be added to date/time/timestamp values What other data types other than VARCHAR2, NUMBER are allowed?

15. Build-in Data Types in SQL (Cont.) • Can extract values of individual fields from date/time/timestamp • Example: extract (year from r.starttime) • Can cast string types to date/time/timestamp • Example: cast <string-valued-expression> as date • Example: cast <string-valued-expression> as time

16. Other Built-in Special types • Special Types • XMLType (we will see examples at the end of the course) • SDO_GEOMETRY (spatial type) to store 2-d, 3-d geometries • Media Types • ORDImage • ORDAudio • ORDDoc,…

17. User-Defined Types • create type construct in SQL creates user-defined type create type Dollars as numeric (12,2) final • Can create user-defined object types • Name, attributes • Methods (implemeneted in plsql or other external languages) • Example CREATE TYPE geometry as OBJECT ( type varchar2(10), vertex_ordinates number_array); • REF datatypes • Reference to the OID (object identifier) of an object

18. Other Types • VARRAYs • Ordered set of elements • Each element has an index and can be accessed using that. • Example: • CREATE TYPE number_array as VARRAY(100000) of NUMBER; • VARRAY can be: • The datatype of a column in a table • CREATE TABLE properties ( name varchar2(32), geom NUMBER_ARRAY); • Attribute of an object • PL/SQL variable, parameter, or return type • Query: cast it using the TABLE keyword to a table • SELECT * from TABLE( select geom from properties where name=‘CityHall’));

19. Nested Tables • Nested Table (compared to VARRAY) • An unordered set of elements • CREATE OR REPLACE TYPE NumberTab AS TABLE OF Number;/ • CREATE TABLE properties (name     VARCHAR2(20),property_geometry  NumberTab) NESTED TABLE property_geometry STORE AS geomtab; • Query: • SELECT * from TABLE (select * from properties where name=‘CityHall’) t;

20. Large-Object Types • Large objects (photos, videos, CAD files, etc.) are stored as a large object: • blob: binary large object -- object is a large collection of uninterpreted binary data (whose interpretation is left to an application outside of the database system) • clob: character large object -- object is a large collection of character data • When a query returns a large object, a pointer is returned rather than the large object itself.

21. Conversion and other Functions • TO_CHAR • Converts numbers, date, time, clob args to char strings • Can specify a format for numbers, dates • SELECT TO_CHAR(10000,'L99G999D99MI') "Amount" FROM DUAL • Returns the string: $10,000 • TO_NUMBER • TO_DATE,… • SUBSTR: • SELECT SUBSTR('ABCDEFG',3,4) "Substring" FROM DUAL; • Returns the string starting at pos 3 and of length 4: ‘CDEF’ • INSTR (string, pattern_to_search_for,..) • Checks if patterns is in string and returns in pos • TRIM; trims the strings from either ends

22. SQL as a DDL Using SQL to create logical schemata Example: CREATE TABLE branch ( bname char(15) NOT NULL, bcity char(30), assets int) attrattr-domains What domains available? char(n), varchar(n) int float(p), numeric(p,d) e.g. numeric(3,1) allows 44.5 real date: year-mo-day e.g. ‘2004-02-05’ , time: hr:min:sec e.g. ’09:30:01’ digits precision

23. SQL as DDL Can create new domains: e.g CREATE DOMAIN p-name char(15) NOT NULL Modify tables: DROP TABLE <relation> -- drops both schema and instance e.g. DROP TABLE account ALTER TABLE <relation> ADD ( <attr> <domain>) ex. ALTER TABLE branch ADD numloans int -- new values given default value of NULL ALTER TABLE <relation> DROP (<attr>) ex. ALTER TABLE customer DROP cstreet

24. Forms of authorization on parts of the database: Read - allows reading, but not modification of data. Insert - allows insertion of new data, but not modification of existing data. Update - allows modification, but not deletion of data. Delete - allows deletion of data. Forms of authorization to modify the database schema (covered in Chapter 8): Index - allows creation and deletion of indices. Resources - allows creation of new relations. Alteration - allows addition or deletion of attributes in a relation. Drop - allows deletion of relations. Authorization

25. The grant statement is used to confer authorization grant <privilege list> on <relation name or view name> to <user list> <user list> is: a user-id public, which allows all valid users the privilege granted A role (more on this in Chapter 8) Granting a privilege on a view does not imply granting any privileges on the underlying relations. The grantor of the privilege must already hold the privilege on the specified item (or be the database administrator). Authorization Specification in SQL

26. select: allows read access to relation,or the ability to query using the view Example: grant users U1, U2, and U3select authorization on the branch relation: grant select on branch to U1, U2, U3 insert: the ability to insert tuples update: the ability to update using the SQL update statement delete: the ability to delete tuples. all privileges: used as a short form for all the allowable privileges more in Chapter 8 Privileges in SQL

27. The revokestatement is used to revoke authorization. revoke <privilege list> on <relation name or view name> from <user list> Example: revoke select on branch from U1, U2, U3 <privilege-list> may be all to revoke all privileges the revokee may hold. If <revokee-list> includes public, all users lose the privilege except those granted it explicitly. If the same privilege was granted twice to the same user by different grantees, the user may retain the privilege after the revocation. All privileges that depend on the privilege being revoked are also revoked. Revoking Authorization in SQL

28. SQL Summary Query Language (DML): SELECT, FROM, WHERE GROUP BY, HAVING INTO ORDER BY Nested Queries (IN, EXISTS, UNIQUE, ALL, SOME, correlation) Aggregates Joins Updates Views, Pseudocolumns, Connect-by Built-in Datatypes DDL: CREATE TABLE DROP TABLE CREATE DOMAIN ALTER TABLE Authorization