sql data definition language ddl
Download
Skip this Video
Download Presentation
SQL Data Definition Language (DDL)

Loading in 2 Seconds...

play fullscreen
1 / 90

SQL Data Definition Language (DDL) - PowerPoint PPT Presentation


  • 188 Views
  • Uploaded on

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)

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 Data Definition Language (DDL)' - abie


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

slide9
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

slide10
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

slide13
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

slide24
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

slide62
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

what s next
What’s next?

Multi-user database issues

  • Security
  • Transaction control
  • Concurrency control and locking

SFDV3002

ad