sql iii
Download
Skip this Video
Download Presentation
SQL III

Loading in 2 Seconds...

play fullscreen
1 / 28

SQL III - PowerPoint PPT Presentation


  • 134 Views
  • Uploaded on

SQL III . 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.

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
Download Presentation

PowerPoint Slideshow about 'SQL III' - javier


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.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript
pseudocolumns
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
connect by not in book
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
connect by
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
  • ….
connect by5
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;

connect by6
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

sql summary
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
sql modification commands
SQL: Modification Commands

Deletion:

DELETE FROM

[WHERE ]

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

delete
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)
sql modification commands10
SQL: Modification Commands

Insertion: INSERT INTO values (.., .., ...)

or INSERT INTO (att1, .., attn)

values( ..., ..., ...)

or INSERT INTO

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

sql modification commands11
SQL: Modification Commands

Update: UPDATE

SET =

WHERE

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)

sql modification commands12
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

sql modification commands13
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

built in data types in sql
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?

build in data types in sql cont
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 as date
    • Example: cast as time
other built in special types
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,…
user defined types
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
other types
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’));
nested tables
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;
large object types
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.
conversion and other functions
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
sql as a ddl
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

sql as ddl
SQL as DDL

Can create new domains:

e.g CREATE DOMAIN p-name char(15) NOT NULL

Modify tables:

DROP TABLE

-- drops both schema and instance e.g. DROP TABLE account

ALTER TABLE ADD ( )

ex. ALTER TABLE branch ADD numloans int

-- new values given default value of NULL

ALTER TABLE DROP ()

ex. ALTER TABLE customer DROP cstreet

authorization
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
authorization specification in sql
The grant statement is used to confer authorization

grant

on to

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
privileges in sql
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
revoking authorization in sql
The revokestatement is used to revoke authorization.

revoke

on from

Example:

revoke select on branch from U1, U2, U3

may be all to revoke all privileges the revokee may hold.

If 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
sql summary28
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

ad