Sql data definition language ddl
This presentation is the property of its rightful owner.
Sponsored Links
1 / 90

SQL Data Definition Language (DDL) PowerPoint PPT Presentation


  • 139 Views
  • Uploaded on
  • Presentation posted in: General

SQL Data Definition Language (DDL). Defining (Oracle10 g ) database structures, integrity, etc. Structured Query Language. What is it, and where did it come from?. Structured Query Language (Kifer p. 46). Standard language for relational databases: database definition (DDL)

Download Presentation

SQL Data Definition Language (DDL)

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


Sql data definition language ddl

SQL Data DefinitionLanguage (DDL)

Defining (Oracle10g) database structures, integrity, etc.

SFDV3002


Structured query language

Structured Query Language

What is it, and wheredid it come from?

SFDV3002


Structured query language kifer p 46

Structured Query Language(Kifer p. 46)

  • Standard language for relational databases:

    • database definition (DDL)

    • data manipulation (DML)

  • Security control (see SFDV3002 chapter 3)

SFDV3002


Structured query language kifer p 461

Structured Query Language(Kifer p. 46)

  • Used by:

    • database administrator (DBA)

    • programmers

    • SQL literate end-users

    • application programs & middleware(see SFDV3002 ch. 4)

SFDV3002


History date darwen a guide to the sql standard

History(Date & Darwen, A Guide to the SQL Standard)

1970: Relational model (Codd, IBM) ⇒ relational algebra & calculus

1974: SEQUEL (Chamberlin & Boyce)

1975: IBM System R

1981: SQL/DS (IBM)

1986–2003: Various standards (see next slide)

Today: “Standardised”, but still varies across products.

SFDV3002


We are here

We are here…

  • Structured Query Language

  • Aside: Model translation

  • Data Definition Language

  • Data Manipulation Language

  • SQL Query Design

SFDV3002


Relational concepts in sql

Relational concepts in SQL

columns

  • Table, row column vs. relation, tuple attribute

  • PK, FK, domain, etc.

rows

SFDV3002


How does all this fit together

How does all this fit together?

Data &

metadata

“DATABASE”

DBMS

Integrity constraints

SFDV3002


Sql data definition language ddl

Oracle architecture (simplified)(see also Oracle10g Concepts manual, ch. 1 ▷ “Oracle Database Architecture”)

An Oracle

installation

Instance

Database

Contains several

logical “databases”

What we would normally

think of as a “database”

— stuff gets defined here

User/schema

SFDV3002


Sql data definition language ddl

  • Instance, Database and Schema: The distinction between these three is probably one of the most confusing concepts across different RDBMS since different RDBMS have different architectures and different terminologies.  To put it simply in Oracle lingo, a database is a set of files (data files, redo log files, control files and so on).  An instance is a set of processes (SMON, PMON, MMON, DBWR, etc) and a shared memory segment (SGA).  A database may be mounted and opened by many instances (RAC installation) concurrently (this is to do with the shared everything architecture of Oracle RAC).  An instance may mount and open any database — however it may only open a single database at any time.  So, in ERD terms: Database:Instance :: 1:N (a one to many relationship) in Oracle.  A schema is the container of the schema objects so all the tables, views, procedures, packages, views etc. reside in the schema.  There can be multiple schemas per database.

SFDV3002


The catalog kifer 3 3 2

The catalog(Kifer §3.3.2)

  • User data (INSERT) vs. metadata (CREATE <xxx>)

  • Stored in predefined tables: catalog or data dictionary.

  • Read with SQL.

SFDV3002


Sql data definition language ddl

SFDV3002


Sql data definition language ddl

The Oracle10g catalog(Oracle10g Concepts manual ch. 7; Oracle10g Referencemanual chs. 2–3; Table 2–3; Example 2–4)

Data dictionary views

  • All_xxx: details of all <xxx>accessibleby current user.

  • User_xxx: details of all <xxx>created by current user.

  • DBA_xxx: details of all <xxx> in entire Oracle10g database (restricted access).

  • DESCRIBE <tablename>

SFDV3002


Oracle10 g schema objects sql reference ch 2 database objects

Oracle10g schema objects(SQL Reference ch. 2 ▷“Database Objects”)

  • Tables & views

  • Stored functions & procedures

  • Packages

  • Triggers

  • Indexes & some other physical structures

  • Object tables, types & views

    etc.

SFDV3002


Sql data definition

SQL data definition

  • Structure: tables, columns, data types, views

  • Integrity constraints

  • Stored code

  • Physical-level structures

    etc.

SFDV3002


Create table kifer 3 3 1 example 2 5 sql reference ch 16 create table

CREATE TABLE(Kifer §3.3.1; Example 2–5; SQL Reference ch. 16▷ “CREATE TABLE”)

CREATE TABLE <name>

( <column-name> <data-type>

[<default>]

[<column-constraint>],

<column-name> <data-type>

[<default>]

[<column-constraint>],

...,

[<table-constraint>, ...]

);

SFDV3002


Core sql 2003 data types see also sql reference ch 2 datatypes ansi db2 and sql ds datatypes

Core SQL:2003 data types(see also SQL Reference ch. 2 ▷“Datatypes” ▷“ANSI, DB2 and SQL/DS datatypes”)

  • CHARACTER(n): fixed-length char (CHAR)

  • CHARACTER VARYING(n): variable-length char (VARCHAR)

  • SMALLINT, INTEGER, BIGINT: integers

  • NUMERIC/DECIMAL: fixed point decimal

  • FLOAT/REAL/DOUBLE PRECISION: floating point decimal

  • DATE, TIME, TIMESTAMP, INTERVAL: dates and times

SFDV3002


Additional sql 2003 data types

Additional SQL:2003 data types

  • BINARY LARGE OBJECT: arbitrary binary data (BLOB)

  • NATIONAL CHARACTER: international character sets (NCHAR)

  • BOOLEAN (only since SQL:1999!)

  • Constructed data types: REF, ROW, ARRAY

  • XML

  • User-defined data types (UDTs)

SFDV3002


Oracle10 g character types sql reference ch 2 datatypes char varchar2 datatype

Oracle10g character types(SQL Reference ch. 2 ▷ “Datatypes” ▷ “CHAR/VARCHAR2 Datatype”)

CHAR(n)

  • Fixed-length character (default 1)

  • Exactlyn characters, padded with blanks

  • Max. 2000 bytes

    VARCHAR2(n)

  • Variable-length character

  • Up ton characters, no padding

  • Max. 4000 bytes

SFDV3002


Oracle10 g character types some tips sql reference ch 2 datatypes char varchar2 datatype

Oracle10g character types: some tips(SQL Reference ch. 2 ▷ “Datatypes” ▷ “CHAR/VARCHAR2 Datatype”)

  • String concatenation operator: ||

  • String functions: see Oracle10g docs.

  • Single quotes (' ').

  • Use sensible lengths.

  • Use CHAR if length never changes.

  • String comparisons:

    • case sensitive ('A' ≠ 'a')

    • trailing blanks stripped ('a ' = 'a')

SFDV3002


Oracle10 g numeric types sql reference ch 2 datatypes number datatype

Oracle10g numeric types(SQL Reference ch. 2 ▷ “Datatypes” ▷ “NUMBER Datatype”)

NUMBER(p,s)

  • Number p digits long (precision) including s decimal places (scale).

  • Both floating point and integers.

  • Usual operators (+, –, *, /).

  • INTEGER/FLOAT/etc. ⇒NUMBER

    Some tips

  • Always use NUMBER(p,2) for money (why?)

  • Get the size right!

SFDV3002


Oracle10 g dates sql reference ch 2 datatypes date timestamp datatype

Oracle10g dates(SQL Reference ch. 2 ▷ “Datatypes” ▷ “DATE/TIMESTAMP/… Datatype”)

DATE

  • Date + time accurate to seconds, range 1/1/4712 B.C. ⇒ 31/12/4712 A.D. (!)

  • Default format 'dd-mmm-yy' (e.g., '12-MAY-90')

  • Current date function: SYSDATE.

  • Date arithmetic: subtract dates ⇒ number of days. (see also INTERVAL data type)

SFDV3002


Other oracle10 g data types sql reference ch 2 datatypes

Other Oracle10g data types(SQL Reference ch. 2 ▷ “Datatypes”)

  • LONG: up to 2 GiB of text

  • RAW(n): n bytes of binary data (n ≤ 2000)

  • LONG RAW: up of 2 GiB binary data

  • ROWID: a physical row address

  • BLOB: binary large object up to 8 TiB

  • CLOB: character large object up to 8 TiB

  • BFILE: pointer to an external binary file (max. 4 GiB)

  • UDTs (objects), arrays, REF, row types, …

SFDV3002


Sql data definition language ddl

Oracle10g data type conversion(SQL Reference ch. 2 ▷ “Datatypes” ▷ “Datatype Comparison Rules” ▷ “Data Conversion”)

Explicit

  • TO_CHAR, TO_NUMBER, TO_DATE, CAST functions

    Implicit

  • Automatic where necessary and possible:

    • '10' = 10 converts '10' to number

    • 'abc' = 10 makes no sense

    • explicit conversion safer

SFDV3002


Deleting a table drop table kifer p 61 sql reference ch 18 drop table

Deleting a table: DROP TABLE(Kifer p. 61; SQL Reference ch. 18▷ “DROP TABLE”)

DROP TABLE <name>

[CASCADE CONSTRAINTS];

  • CASCADE CONSTRAINTS deletes referencing FK constraints

  • DROP commands for most schema objects: tables, views, constraints, indexes, sequences, …

SFDV3002


Defining integrity in sql kifer 3 3 3 3 3 7

Defining integrity in SQL(Kifer §3.3.3–3.3.7)

Constraints enforce rules on tables.

Simple constraints

  • DEFAULT, NOT NULL, UNIQUE

  • Name VARCHAR2(15) NOT NULL

    More complex constraints

  • CHECK allowable values

    Arbitrary business rules

  • e.g. adjust customers’ credit ratings according to sales volume and payment reliability

  • usually very complex (procedural code)

SFDV3002


Types of integrity available in sql see also kifer 3 3 8

Types of integrity available in SQL(see also Kifer §3.3.8)

Declarative

constraints

  • Domain: sort of in Oracle (CHECK)

  • Entity: PRIMARY KEY

  • Referential: FOREIGN KEY, cascade deletion

  • User-defined:

    • DEFAULT, NOT NULL, UNIQUE

    • CHECK

    • triggers (reactive)

    • stored code

Procedural

constraints

SFDV3002


Defining integrity constraints in sql see also kifer 3 3 3 3 3 7

Defining integrity constraints in SQL(see also Kifer §3.3.3–3.3.7)

Column constraints (single column only)

  • salary ≥ $12,000

  • department in {INFO, ACCT}

    Table constraints (single table only)

  • start date ≤ finish date

  • composite primary key

    Database constraints (single schema only)

  • sum of customer’s sales ≤ their credit limit

  • not in Oracle (except FKs)

SFDV3002


Oracle10 g integrity constraints sql reference ch 8 constraint example 2 6

Oracle10g integrity constraints(SQL Reference ch. 8▷ “constraint”; Example 2–6)

[CONSTRAINT <name>]

<constraint-body>

[[NOT] DEFERRABLE]

  • Column or table constraints.

  • Automatic vs. explicit names.

  • Checked immediately (NOT DEFERRABLE) vs. at end of transaction (DEFERRABLE).

SFDV3002


Oracle10 g entity integrity

Oracle10g entity integrity

Column constraint form (single-column PK)

<column-name> <data-type> PRIMARY KEY

DeptId NUMBER (2) CONSTRAINT dept_deptid_pk PRIMARY KEY,

Table constraint form (composite PK)

PRIMARY KEY (<column(s)>)← preferred

CONSTRAINT dept_deptid_pk PRIMARY KEY(DeptId),

SFDV3002


Oracle10 g referential integrity see also kifer 3 3 7

Oracle10g referential integrity(see also Kifer §3.3.7)

FOREIGN KEY (<column(s)>)

REFERENCES <table> [(<column(s)>)]

[ON DELETE CASCADE]

  • Table constraint only.

  • ON DELETE CASCADE: delete “child” rows when “parent” row is deleted (no “dangling” FKs). (see also triggers in SFDV3002 ch. 4)

SFDV3002


Default values

DEFAULT values

<column> <type>DEFAULT<value>

  • City varchar(255) DEFAULT 'Sandnes'

    Single column, technically not a constraint.

SFDV3002


Not null constraint

NOT NULL constraint

<column> <type> NOT NULL

  • Name VARCHAR2(15) NOT NULL

  • Column constraint, <column> cannot be null.

  • SFDV3002


    Unique constraint

    UNIQUE constraint

    Column constraint form (single column only)

    <column> <type> UNIQUE

    • no duplicate values in <column>.

    • CONSTRAINT dept_dname_uk UNIQUE(DeptName),

      Table constraint form (one or more columns)

      UNIQUE (<column(s)>)

    • Combination of values in <column(s)> is unique.

    SFDV3002


    Check constraint

    CHECK constraint

    Column constraint form

    <column> <type> CHECK (<condition>)

    • <condition> can only directly refer to <column>

      DeptId NUMBER(2) CONSTRAINT dept_deptid_cc

      CHECK((DeptId >= 10) and (DeptId <= 99)),

      Table constraint form

      CHECK (<condition>)

    • <condition> can directly refer to any column in the table

      CONSTRAINT dept_deptid_cc

      CHECK((DeptId >= 10) and (DeptId <= 99)),

    SFDV3002


    A quick recap

    A quick recap

    • We can now:

      • Define tables with various data types and various declarative integrity constraints.

      • Delete entire tables.

    What about changing existing table structures?

    SFDV3002


    Changing table structures kifer 3 3 10

    Changing table structures(Kifer §3.3.10)

    The simple approach

    • DROP table and re-create.

    • Not always an option. (why not?)

      An alternative

    • Change in place using ALTER TABLE.

    • ADD: columns, table constraints.

    • MODIFY: columns only (data type, DEFAULT, NOT NULL).

    • REMOVE: constraints.

    SFDV3002


    Alter table add form sql reference ch 12 alter table example 2 7

    ALTER TABLE: ADD form(SQL Reference ch. 12▷ “ALTER TABLE”; Example 2–7)

    ALTER TABLE <name>

    ADD ( <new-column-name> <data-type>

    [<default>]

    [<column-constraint>]

    [, ...] );

    ALTER TABLE student

    ADD SocialSecurity CHAR(9);

    SFDV3002


    Alter table modify form example 2 8

    ALTER TABLE: MODIFY form(Example 2–8)

    ALTER TABLE <name>

    MODIFY ( <column-name> [<data-type>]

    [<default>]

    [NOT NULL] );

    ALTER TABLE student

    MODIFY SocialSecurity VARCHAR2(11);

    • Can only add NOT NULL if column contains no nulls!

    SFDV3002


    Alter table drop form example 2 9

    ALTER TABLE: DROP form(Example 2–9)

    ALTER TABLE <name>

    DROP PRIMARY KEY [CASCADE];

    ALTER TABLE major

    DROP PRIMARY KEY CASCADE;

    ALTER TABLE <name>

    DROP UNIQUE (<column> [, ...]) [CASCADE];

    ALTER TABLE <name>

    DROP CONSTRAINT <constraint-name>;

    ALTER TABLE employee

    DROP CONSTRAINT employee_deptid_fk;

    • CASCADE drops all dependent constraints.

    SFDV3002


    Alter table caveats

    ALTER TABLE: Caveats

    ADD

    • New columns contain nulls if no DEFAULT

      MODIFY

    • Limitations changing data types

    • Changing default affects only new rows

    SFDV3002


    Sequences sql reference ch 15 create sequence example 2 10

    Sequences(SQL Reference ch. 15▷ “CREATE SEQUENCE”; Example 2–10)

    Automatically generate unique numeric values

    CREATE SEQUENCE <name>

    [INCREMENT BY <num>]

    [START WITH <num>]

    [MAXVALUE <num>]

    [MINVALUE <num>]

    [[NO]CYCLE];

    SFDV3002


    Using sequences

    Using sequences

    • Defaults: start 1, increment +1, no max.

    • Seq.CURRVAL returns current value of Seq.

    • Seq.NEXTVAL increments Seq and returns value.

    • Note:

      • CURRVAL for current user only

      • NEXTVAL affected by other users

    SFDV3002


    Summary of section 2 3

    Summary of section 2.3

    • Oracle architecture

    • Metadata and the catalog

    • CREATE and DROP TABLE

    • SQL & Oracle10g data types in more detail

    • Integrity constraints (CHECK, PK, FK, etc.)

    • ALTER TABLE

    • Sequences

      Next: SQL data manipulation language (DML)

    SFDV3002


    Sql data manipulation language dml

    SQL Data ManipulationLanguage (DML)

    2.4

    Getting data into and out of(Oracle10g) databases

    SFDV3002


    We are here1

    We are here…

    • Structured Query Language

    • Aside: Model translation

    • Data Definition Language

    • Data Manipulation Language

    • SQL Query Design

    SFDV3002


    C reating rows insert kifer 5 3 1 sql reference ch 18 insert example 2 11

    Creating rows: INSERT(Kifer §5.3.1; SQL Reference ch. 18▷ “INSERT”; Example 2–11)

    Either:← preferred form

    INSERT INTO Employee

    (Employee_ID, Last_name, First_name,

    Middle_initial, ...)

    VALUES (12345, 'Anderson', 'Thomas', 'A', ...);

    or just:

    INSERT INTO Employee

    VALUES (12345, 'Anderson', 'Thomas', 'A', ...);

    SFDV3002


    U pdating rows update kifer 5 3 3 sql reference ch 19 update example 2 12

    Updating rows: UPDATE(Kifer §5.3.3; SQL Reference ch. 19▷ “UPDATE”; Example 2–12)

    UPDATE Employee

    SET Department_ID = 13

    WHERE Department_ID = 5;

    UPDATE Employee

    SET First_name = 'Janet',

    Salary = Salary * 1.5

    WHERE Employee_ID = 12345;

    SFDV3002


    D eleting rows delete kifer 5 3 2 sql reference ch 17 delete example 2 13

    Deleting rows: DELETE(Kifer §5.3.2; SQL Reference ch. 17▷ “DELETE”; Example 2–13)

    DELETE FROM Employee

    WHERE Employee_ID = 12345;

    DELETE FROM Employee

    WHERE Salary >= 75400;

    DELETE FROM Employee

    WHERE Department_ID = 13;

    What will DELETE FROM Employee; do?

    SFDV3002


    The importance of where

    The importance of WHERE

    • Irrelevant for INSERT, usually essential for DELETE, UPDATE, SELECT.

    • WHERE expression:

      • always evaluates to true/false

      • multiple conditions using AND/OR (Boolean logic) (see Tutorial 2)

    SFDV3002


    The story so far

    Note: “table”,

    not “tables”

    The story so far…

    INSERT INTO <table> [(<column(s)>)]

    VALUES (<value list>);

    DELETE FROM <table>

    [WHERE <condition>];

    UPDATE <table>

    SET <column> = <value> [, ...]

    [WHERE <condition>];

    Next:retrieving data from the database (!)

    SFDV3002


    R etrieving data in sql select kifer 5 2 see also 5 1 sql reference ch 19 select

    Retrieving data in SQL: SELECT(Kifer §5.2 (see also §5.1); SQL Reference ch. 19▷ “SELECT”)

    Basic form

    SELECT *|<column(s)>

    FROM <table(s)>

    [WHERE <condition>];

    Examples

    SELECT * FROM Employee;

    SELECT Name, Salary FROM Employee;

    SELECT * FROM Employee

    WHERE Salary > 70000;

    SFDV3002


    Mapping select to relational theory violates relational model

    Mapping SELECT to relational theory( = violates relational model)

    • SQL SELECT supports:

      • project, restrict, join, union, difference, intersection

      • some calculus-derived features

    • SQL SELECT produces results that may or may not be relations.

    • Converting to relational algebra gives some idea of DBMS execution strategy.

    SFDV3002


    Aside qualified column names

    Aside: Qualified column names

    table

    name

    SELECT Demo.Department.Name,

    Demo.Employee.Last_name

    FROM Demo.Department, Demo.Employee

    WHERE Demo.Department.Department_ID =

    Demo.Employee.Department_ID;

    schema

    name

    SFDV3002


    Aside row variables kifer p 148

    Aside: Row variables(Kifer p. 148)

    SELECT D.Name, Emp.Name

    FROM Demo.Department ASD,

    Demo.Employee ASEmp

    WHERE D.Department_ID =

    Emp.Department_ID;

    • AS keyword optional.

    variable for

    Demo.Department

    variable for

    Demo.Employee

    SFDV3002


    Aside other useful features kifer pp 153 154

    Aside: Other useful features(Kifer pp. 153–154)

    Column renaming

    SELECT COUNT(*) AS Total FROM Employee;

    Embedded constants

    SELECT 'Surname:', Last_name FROM Employee;

    Embedded expressions(including SELECT)

    SELECT 2 + 2 FROM Employee;

    SFDV3002


    Aside removing duplicate rows kifer pp 151 152

    Aside: removing duplicate rows(Kifer pp. 151–152)

    • SELECT can produce duplicate rows.

    • DISTINCT removes them:

      SELECT DISTINCT Last_name

      FROM Employee;

      SELECT DISTINCT Salary, Department_ID

      FROM Employee;

    • Inconsistency: UNIONeliminates duplicate rows! (see slide **xx**)

    SFDV3002


    Project in sql kifer pp 148 1 50 example 2 14

    Project (π) in SQL(Kifer pp. 148–150; Example 2–14)

    Retrieve a subset of a table’s columns

    SELECT Last_name, Salary, Department_ID

    FROM Employee;

    SELECT Name

    FROM Department;

    * retrieves all columns

    SELECT *

    FROM Employee;

    πLast_name,Salary,Department_ID(Employee)

    πName(Department)

    π(Employee)

    SFDV3002


    Restrict in sql kifer pp 148 150 example 2 15

    Restrict (σ) in SQL(Kifer pp. 148–150; Example 2–15)

    Retrieve rows that meet certain criteria

    SELECT *

    FROM Employee

    WHERE Salary = 45000;

    SELECT Last_name, Hire_date

    FROM Employee

    WHERE Department_ID = 20

    OR salary < 25000;

    σSalary=45000(Employee)

    πName,Phone(σDept_code=‘MGMT’(Employee) ∪ σSalary<25000(Employee))

    [or: πName,Phone(σDept_code=‘MGMT’∨ Salary<25000(Employee))]

    SFDV3002


    Features of where kifer p 152 example 2 16

    Features of WHERE(Kifer p. 152; Example 2–16)

    • Any boolean expression.

    • Comparison operators:

      • =, <, >, <=, >=, <> (sometimes !=)

      • SOME, ALL(see example 2–20)

    • Logical operators:

      • AND, OR, NOT (boolean algebra)

    • Special operators: (see also example 2–19)

      • BETWEEN, IS NULL, LIKE, IN, EXISTS

    SFDV3002


    Product in sql kifer pp 148 150 example 2 17

    Product (×) in SQL(Kifer pp. 148–150; Example 2–17)

    Combine every row of one table with every row of another

    SELECT ...

    FROM Employee E CROSS JOIN Department D;

    SELECT ...

    FROM Employee AS E, Department AS D;

    • Alternatively: cross join, cartesian product

    • Joins generally more useful.

    Employee × Department

    SFDV3002


    Sql data definition language ddl

    Join (⋈) in SQL(Form 1: SQL:2003 explicit join; see also Kifer §5.2.7;SQL Reference ch. 9 ▷ “Joins”; Example 2–18)

    Either:

    SELECT ...

    FROM Employee E [INNER] JOIN Department D

    USING (Department_ID);

    or:

    SELECT ...

    FROM Employee E [INNER] JOIN Department D

    ON (E.Department_ID = D.Department_ID);

    Employee ⋈Department_ID Department

    join condition

    SFDV3002


    Join in sql form 2 old style implied join see also kifer p 149

    Join (⋈) in SQL(Form 2: “old style” implied join; see also Kifer p. 149)

    Join condition in WHERE clause

    SELECT ...

    FROM Employee E,Department D

    WHERE E.Department_ID =

    D.Department_ID;

    σEmployee.Department_ID=Department.Department_ID(Employee×Department)

    (Equivalent to Employee ⋈Department_ID Department.)

    join condition

    SFDV3002


    Set operations in sql kifer 5 2 2

    Set operations in SQL(Kifer §5.2.2)

    • UNION, INTERSECT, EXCEPT (MINUS in Oracle).

    • Source tables must be compatible.

      • Some DBMSs allow non-compatible tables

    • Inconsistency:SELECT includes duplicate rows✘, UNION eliminates them (vs. UNION ALL) ✔.

    SFDV3002


    Set operations in sql kifer 5 2 21

    Set operations in SQL(Kifer §5.2.2)

    SELECT * FROM Employee_fulltime

    UNION

    SELECT * FROM Employee_parttime;

    Employee_fulltime ∪ Employee_parttime

    • Similar for INTERSECT and EXCEPT (MINUS).

    SFDV3002


    Subqueries kifer 5 2 3 sql reference ch 9 using subqueries example 2 19

    Subqueries(Kifer §5.2.3; SQL Reference ch. 9 ▷ “Using Subqueries”; Example 2–19)

    • SELECT expression (usually) embedded inside WHERE; used with IN, ALL, SOME, EXISTS.

    • Subqueries can refer to data from the “outer” query (correlated subquery).

    • Tricky to write, so design carefully!(see “An Incremental Approach to Developing SQL Queries”)

    • Most joins can be rewritten as subqueries, but not vice versa.

    SFDV3002


    Scope in the select statement example 2 19 d

    Scope in the SELECT statement(example 2–19(d))

    • Programming language variables exist inside some scope (e.g., function).

    • In SELECT, can only refer to items mentioned in SELECT and FROM clauses of:

      • currentSELECT expression; or

      • any enclosingSELECT expressions

    • Particularly important for correlated subqueries.

    SFDV3002


    Summary of the basic select

    Summary of the basic SELECT

    • Derived from relational algebra & calculus:

      • SELECT = project (π), WHERE = restrict (σ), FROM = product and join (×, ⋈), set operations separate

      • EXISTS = ∃, ALL = ∀

    • Order of evaluation: FROM, WHERE, SELECT.

    • Remaining features more practical reporting features.

    SFDV3002


    The full select statement

    The full SELECT statement

    SELECT *|<column(s)> project

    FROM <table(s)> source table(s)

    [WHERE <condition(s)>] restrict by rows

    [GROUP BY <column(s)>] group output

    [HAVING <condition(s)>] restrict by groups

    [ORDER BY <column(s)>]; sort rows

    SFDV3002


    Sorting the output order by kifer p 170 example 2 20

    Sorting the output: ORDER BY(Kifer p. 170; Example 2–20)

    ORDER BY <column>|<num> [ASC|DESC] [,...]

    • <num> refers to column position in output:

      SELECT E.Last_name, E.Phone

      FROM Employee AS E

      ORDER BY 2;

    • Sort on multiple columns:

      SELECT E.Last_name, E.Department_ID, E.Salary

      FROM Employee AS E

      ORDER BY E.Department_ID, E.Salary DESC,

      E.Last_Name;

    SFDV3002


    Aggregate functions kifer 5 2 5 example 2 21

    Aggregate functions(Kifer §5.2.5; Example 2–21)

    COUNT number of rows

    SELECT COUNT(*) FROM Employee;

    SELECT COUNT(DISTINCT Salary) FROM Employee;

    SUM a numeric column

    SELECT SUM(E.Salary) FROM Employee AS E;

    MIN/MAX a column

    SELECT MIN(E.Hire_date) FROM Employee AS E;

    AVG value of a column

    SELECT AVG(E.Salary) FROM Employee AS E;

    SFDV3002


    Grouping the output group by kifer pp 167 168 example 2 22

    Grouping the output: GROUP BY(Kifer pp. 167–168; Example 2–22)

    GROUP BY <column> [, ...]

    • Groups rows by column value(s).

    • Always used with aggregate functions in SELECT clause. (see previous slide)

    • One row in the result for each different value of the grouped column(s).

    SFDV3002


    Grouping the output group by kifer pp 167 168 example 2 221

    Grouping the output: GROUP BY(Kifer pp. 167–168; Example 2–22)

    • All non-calculated columns in SELECTclause must be in GROUP BY, and vice versa:

      SELECT Department_ID, D.Name, COUNT(*) AS Num

      FROM Employee AS E INNER JOIN Department AS D

      USING (Department_ID)

      GROUP BY Department_ID, D.Name

      WHERE D.Location = 20;

    SFDV3002


    Restricting by groups having kifer pp 168 169 example 2 23

    Restricting by groups: HAVING(Kifer pp. 168–169; Example 2–23)

    HAVING <condition>

    • Similar to WHERE, restricts output of GROUP BY.

    • Can include functions (WHERE can’t):

      SELECT E.Department_ID, COUNT(*)

      FROM Employee AS E

      GROUP BY E.Department_ID

      HAVING COUNT(*) < 10; -- only include groups

      -- with 1-9 rows

    SFDV3002


    Views kifer 3 3 9 5 2 8 example 2 24 sql reference ch 17 create view

    Views(Kifer §3.3.9, 5.2.8; Example 2–24; SQL Reference ch. 17 ▷ “CREATE VIEW”)

    • Virtual tables derived from real “base” tables; behave like normal tables.

    • Only definition (SELECT) stored in database:

      CREATE VIEW <name> [(<col. names>)]

      AS <select statement>;

      CREATE VIEW Sales_emp

      AS SELECT E.* FROM Employee AS E

      WHERE E.Department_ID = 30;

    SFDV3002


    Updating data in views kifer 5 3 4

    Updating data “in” views(Kifer §5.3.4)

    • Many views theoretically updateable, but most DBMSs severely restrict.

    • Oracle10g views can be updated if:(see CREATE VIEW doc.: “Notes on Creating Updatable Views”)

      • no JOINs (single base table)

      • no set operators (UNION, INTERSECT, etc.)

      • no GROUP BY, aggregate functions, DISTINCT

        OR: use INSTEAD OF triggers.(see SFDV3002 chapter 4)

    SFDV3002


    View advantages disadvantages

    View advantages/disadvantages

    • Users see what they want to see (conceptual level)

    • Users see only what they need to see(see also SFDV3002 chapter 3)

    • No additional storage or data duplication

    • Not all views can be updated (varies by DBMS)

    • Slower than base tables (usually not critical)

    SFDV3002


    Summary of section 2 4

    Summary of section 2.4

    Manipulating data

    • INSERT, UPDATE, DELETE

      Retrieving data (SELECT)

    • Relational algebra operations

    • Ordering, grouping, aggregate functions, etc.

    • Views

    SFDV3002


    Sql query design

    SQL Query Design

    2.5

    A brief guide tobest practices

    SFDV3002


    We are here2

    We are here…

    • Structured Query Language

    • Aside: Model translation

    • Data Definition Language

    • Data Manipulation Language

    • SQL Query Design

    SFDV3002


    Writing good sql queries see an incremental approach to developing sql queries by jonathon gennick

    Writing good SQL queries(see “An Incremental Approach to Developing SQL Queries” by Jonathon Gennick)

    • Figure out what’s required first.

    • Systematic, incremental approach.

    • Test, test, test!

    • Verify, verify, verify!

    • Consistent coding style.

    SFDV3002


    Step 1 the from clause

    Step 1: The FROM clause

    • Which table(s) do you need to access (i.e., where are the source data)?

    • How should multiple tables be joined?

    • Row variables?

    SFDV3002


    Step 2 the where clause

    Step 2: The WHERE clause

    • All or some rows?

    • What condition(s)?

    • How are multiple conditions combined?

    • Subqueries?

    • Absolutely critical to get this right.

    SFDV3002


    Step 3 the group by clause

    Step 3: The GROUP BY clause

    • Are you aggregating or summarising?

    • Which aggregate function(s)?

    • Which column(s) to group on?

    SFDV3002


    Step 4 the having clause see also step 2

    Step 4: The HAVING clause(see also Step 2)

    • All or some groups?

    • Which groups to eliminate?

    SFDV3002


    Step 5 finishing the select clause

    Step 5: Finishing theSELECT clause

    • All or some columns?

    • Any additional columns (e.g., computed)?

    • Column renaming?

    • Column qualification?

    SFDV3002


    Step 6 the order by clause

    Step 6: The ORDER BY clause

    • Is order important?

    • Which column(s) to sort on?

    • Ascending or descending?

    SFDV3002


    It may not always work this way

    It may not always work this way…

    • Correlated subqueries.

    • Joins based on summarised data (i.e., GROUP BY first).

    • Extremely complex queries of all sorts…

    SFDV3002


    Let s try it

    Let’s try it!

    SFDV3002


    What s next

    What’s next?

    Multi-user database issues

    • Security

    • Transaction control

    • Concurrency control and locking

    SFDV3002


  • Login