Objectives contd

1
Objectives - contd
The objectives of this lecture are 1. To
introduce the Data Definition and Data
Manipulation components of SQL (Structured Query
Language) as expressed in Oracle 2. To proceed
through some of the SQL commands such as Select,
From, Where, Having, Group By, Order By 3.
To introduce other components such as Logical
Operators, Arithmetic Operators, Sub-queries and
Views
2
SQL - Data Manipulation
SQL Structured Query Language Originally
designed and implemented by IBM research as the
interface to relational databases. ANSI
Standard 1986. SQL99 Standard is current.
SQL93 in many applications A declarative
language. The DBMS performs retrieval. A
choice of access routines is made to optimise the
query. SQL specifies syntax features for
retrieval update and definition of the
database.
3
SQL Some Variations

• Microsoft Access SQL is generally ANSI-89 Level 1
  compliant.
• Certain ANSI SQL features are not implemented in
  Access SQL
• MS Access SQL includes reserved words and
  features not supported in ANSI SQL
• Some differences
• Matching character MS Access ANSI SQL
• Single character ?
  _
• Zero or more chars
  
• MS Access does not support COMMIT, GRANT, LOCK
• DISTINCT aggregate functions (e.g. SUM(DISTINCT
  att)

4
Why SQL ?

• SQL is the one industry standard language for
  querying databases
• All of the database features can be accessed via
  SQL - which doesnt occur with a Graphics
  interface
• GUI tools frequently do not exploit the
  underlying language functionality
• In client-server applications, an SQL query in
  any application host language will return exactly
  the same results (ODBC)

5
DDL - Data Definition

• CREATE (1) Table - define table name,
  attributes, types
• (2) View - define user view
  of data
• (3) Index - create index on
  nominated attributes
• DROP (1) Table - delete table, attributes
  and values
• (2) View - delete user
  view(s)
• (3) Index - delete index,
  indexes
• NOTE Oracle DB2, MySQL, SQLServer and
  Ingres and support the 'owner' concept.
• Hence only 'owners' can DROP nnnn
  Some DBMS have an ALTER command to vary attribute
  characteristics. Ingres V6.4 does not support
  this feature

6
DML - Data Manipulation Language

• Group 1 - Data Modification
• Insert - Add a single row (interactive)
• - Perform successive INSERTS as a
  'transaction set - interactive
• Copy - From an external file to a database
  table
• - From a table to an external file
• Update - Amend attribute values in rows
• Delete - Delete rows of data from a table
• WHEN IN DOUBT, USE HELP.

7
DML - Data Manipulation Language

• Group 2DATA CONTROL - User control of
  transaction processing
• Commit - Commit or enable changes to the
  database
• Rollback - Rollback and reprocess (or some
  other
  action) transaction which could not be
  COMMITTed .
• Group 3
• DATA SECURITY - Authority over users - generally
  only
  available to the DBA
• Grant - Allow access privileges to
  users (e.g. read,write,update to nominated tables
  or attribute values in tables)
• Revoke - Revoke or cancel access
  privileges

8
CREATE
The syntax is create table lttablenamegt (
columnname format ,columnname format) e.g.
create table wages(name varchar2(10), ID
number(2,0),
Department varchar2(3),
date_comm date) OR create
table wages(name varchar2(10) not null, ID
number(2,0) not null,
Department varchar2(3) not null,
date_comm date) not null

9
Optional CREATE
create table highincome as select name,
salary from wages where
salary gt 75000 This creates an extract file
of only those entries from the table wages
where the salary is in excess of 75,000 (not
all DBMS support Currency designators A, US )
10
INSERT

• This command allows data to be inserted, one row
  at a time, into an existing table
• Syntax Insert into lttablenamegt (list of
  attributes)
• values (list of values)
• Note The list of attribues can be ignored
  providing the order of attribute values, and
  completenes of attribute instances, is as per the
  table list.
• example insert into emp(name, sal, byear)
• values(Jones, Bill,
  45000,1967)
• or(see note)
• insert into emp values(Jones,
  Bill, 45000,1967)

11
INSERT - an extension

• The Insert command can also use a feature similar
  to create, i.e. use data from an existing table
  to populate another table.
• insert into job(jid, jtitle, lowsal, highsal)
• as select job_no, title, lowsal, highsal)
• from newjob
• where title system analyst)
• The attributes of the table newjob comprise at
  least job_no,title, lowsal, highsal

12
Oracle - SQLLOAD

• Inserting is a slow process, and the sqlload
  command allows for data to be bulk loaded from an
  existing text file (upload) to a database table.
• The Loader requires a Control File which
• names the file from which data is to be directed
  into tables
• names the table into which the external data is
  to be loaded
• sets the attribute name(s), order and datatype
• provides additional files for logging, bad, and
  discard data into these particular files.
• Also requires a Unix account (on the Monash
  system)

13
Oracle Loader
Similar functions - expects the Input as a text
file. Some features skip - starting point of
load load - default of all log - log of
records bad - log of discards Control file
details of loader file, name of Oracle table to
load into, column and field specifications
14
Import and Export

• These commands are available to the DBA and the
  application developers
• The commands make quick and dependable copies of
  Oracle data
• EXPORT makes a copy of data and data structures
  in a operating system file (external directory
  e.g. Unix)
• IMPORT reads file created by Export and places
  data and data structures into Oracle database
  tables
• Their uses are
• backup and recovery
• moving data between instances of Oracle
• moving data between tablespaces

15
Access Import

• MS-Access offers an Import function which directs
  data from others sources (e.g. other Access
  databases, Excel, and various other sources such
  as SQL server) to an Access database. These
  sources can be local or remote.
• This serves the same purpose which is to use (or
  reuse) existing data and bulk copy to a target.
• Error messages (mismatches, data missing, data
  type mismatch etc) are generated as a by product
  of the process.
• The target database table (or tables) must be
  compatible with the source ( or is that vice -
  versa ?)

16
Copy
There are a number of forms associated with
COPY 1. Copying data from 1 database to
another remote to local local to remote remote
to remote 2. Copy data from one table to another
(single database) Copy data to one table from
another An example copy from
rsimpson_at_cot2180 (to the current
database) create empcopy2 using select
from user.dept
17
General Form of a Query

• SELECT as a function applies algebra in
  developing a result table from a base table, (or
  tables). The result table may have 0 to n rows...
• The Select Command is used to query data in the
  database..
• Its syntax is Select (select-list - attributes
  or derived data)
• From (table name or
  names)
• Where (sets up conditions)
• Group By (attribute names)
• Having (search-conditions)
• Order By (attribute name or
  names)

18
General Form of a Query

• SELECT and FROM are compulsory.
• Other clauses are optional but occur in the order
  shown
• HAVING is normally associated with GROUP BY

19
Select Examples
PART
PNAME NUT BOLT CARAVAN
PARTNO P1 P2 P3
PRICE 0.20 1.00 5000.00
QOH 20 40 3
SELECT FROM PART Selects all column values for
all rows SELECT PARTNO, PNAME FROM PART
WHERE PRICE BETWEEN 0.2 AND
1.0 Selects rows where price gt .2 and lt
1.0 SELECT PARTNO, PNAME FROM PART
WHERE PNAME IN ('NUT','BOLT') Selects rows
where pname has a value in the following list
20
SELECT
SELECT pname, priceqoh AS pvalue FROM
PART WHERE price gt 0.20 AND price qoh gt
30.0 ORDER BY pname desc
PNAME CARAVAN BOLT
PVALUE 15,000.00 40.00
Result Table ---gt
21
Select Example (Oracle)
PART
PNAME NUT BOLT CARAVAN
PARTNO P1 P2 P3
PRICE 0.20 1.00 5000.00
QOH 20 40 3
SELECT pname, priceqoh AS pvalue FROM
PART WHERE price gt 0.20 AND price qoh gt
30 ORDER BY pname desc
pname CARAVAN BOLT
pvalue 15,000.00 40.00
22
Same Data - Oracle Datatypes

Oracle does not support a money datatype as in
some DBMSs nor a currency datatype (as in
Microsoft). The Oracle datatype numeric has
many forms and some of these are shown below
using the value 7456123.89 number
results in 7456123.89 being stored number(9)
results in 7456123 being stored number(9,2)
results in 7456123.89 being stored number(9,1)
results in 7456123.9 being stored number(15,1)
results in 7456123.9 being stored To represent
values in an output table column (attribute)
format 99.999.00 heading (new name)
23
Format of a Query Script
Select (attribute list ) From
(tables list) Where (conditions for
joins) Group By (selected groupings
Having (condition for grouping) Order By
(Attribute(s) order Asc or Desc
24
Expressions in Select
Arithmetic operators are - /
Comparison operators are ! ltgt gt lt
gt lt Logical operators are AND OR NOT
Parentheses may be used to alter order of
evaluation - unary, , /, - Wildcard
any string of zero or more character
_ any one character
any of the characters enclosed in
brackets A range of numeric, string, date
and other functions are available.
25
SELECT Vocabulary
ALL AND ANY BETWEEN BY DISTINCT EXISTS FROM GR
OUP HAVING IS IN LIKE NOT NULL OR ORDER SELECT
UNION WHERE
Arithmetic Operators -
/ Relational Operators
! lt gt lt gt Logical
Operators AND OR NOT
Parentheses ( )
Special Operators BETWEEN IN LIKE
NULL Existential Operator EXISTS
26
Arithmetic Operators

• List the name, birth year and year of death of
  each prime minister who
• was born in New South Wales. List in order of
  birth year.
• SELECT PM_NAME, BIRTH_YR, BIRTH_YR
  DEATH_AGE
• FROM PRIME_MINISTER
• WHERE STATE_BORN NSW
• ORDER BY BIRTH_YR
• PM_NAME BIRTH_YR BIRTH_YR
  DEATH_AGE
• Barton E 1849
  1920
• Page E C G 1880
  1961
• Chifley J 1885
  1951
• Holt H E 1908
  1967
• McMahon W 1908
  ?
• Whitlam E G 1916
  ?

27
Logical Operators
28
Combining Logical Operators
Which prime ministers were born in NSW and then
represented Victoria or have simply not served
less than two years? SELECT PM_NAME,
STATE_BORN, STATE_REP, YRS_SERVED FROM
PRIME _MINISTER WHERE STATE_REP VIC AND
STATE_BORN NSW OR NOT
YRS_SERVED lt 2
PM_NAME STATE_BORN STATE_REP
YRS_SERVED Holt H E NSW
VIC
1.88 Gorton J G VIC
VIC
3.17 Whitlam E G NSW
NSW 2.92 Fraser
J M VIC VIC
7.33
29
Select Examples - PART table
SELECT PNAME FROM PART WHERE QOH IS
NULL Selects those rows where qoh has a null
value SELECT FROM PART WHERE PNAME LIKE
'_ _T' or PNAME LIKE 'LT' Selects rows where
pname has three letters the last of which is a T
or PNAME ends in LT
30
Use of COUNT and Distinct operators
How many liberal prime ministers were
commissioned between 1970 and 1980?
SELECT Liberal PMs, COUNT(), COUNT(DISTINCT
PM_NAME) FROM MINISTRY WHERE PARTY
Liberal AND YR_COMM BETWEEN 1970 AND
1980
COUNT() COUNT(DISTINCT PM_NAME)
Liberal PMs 5
2
31
Union Operator

• Combines the contents of two identical tables
• create table compress as
• select name, identity, date_of_birth, gender
• from personnel1
• union
• select name, perscode, birthdate, m_or_f
• from personnel2
• Identical means that attribute types, size,
  domains of the Union attributes must be the same.
  Arttibute names do not.

32
Union

• Assume that the following Names data is
  contained in 2 tables
• Longtime Prospect
• Adah Talbot Adah Talbot
• Dick Jones Dory Kenson
• Donald Rollo Elbert Talbot
• Elbert Talbot George Phepps
• George Oscar Jed Hopkins
• Pat Lavay Pat Lavay
• Peter Lawson Ted Butcher
• Wilfred Lowell Wilfred
• (8 names)
  (8 names)

33
Union

• The statement select name from longtime
• union
• select name from prospect would
  give this result
• Adah Talbot
• Dick Jones
• Donald Rollo
• Dory Kenson
• Elbert Talbot
• George Oscar
• George Phepps note that there
  are NO duplicates
• Jed Hopkins
• Pat Lavay
• Peter Lawson
• Ted Butcher
• Wilfred Lowell
• (12 names)

34
UNION Operator - Special Case

• PART
• PartNo Pname Price
  QOH
• P1 Nut 0.2 21
• P2 Bolt 1 40
• P3 Caravan 5000 3
• SELECT pname, 'q1' AS Query FROM PART WHERE QOH
  lt 22
• UNION
• SELECT pname, 'q2' AS Query FROM PART WHERE QOH
  gt 20
• Pname Query
• Nut q1
• 
  Caravan q1
• Nut
  q2
• Bolt
  q2

The table PART is in UNION with itself
35
DELETE
DELETE FROM tablename corr-name
WHERE search-condition Delete one or
many rows in a table. Without the search
condition, all rows will be deleted DELETE FROM
PART WHERE qoh lt 4.00
36
UPDATE
UPDATE tablename corr-name FROM
tablename corr-name , tablename corr-name
SET colname expression ,
colname expression WHERE
search_condition Replaces values of the
specified columns with expression values for all
rows satisfying the search-condition. Expressions
in the set clause may be constants or column
values from the UPDATE tablename or FROM
tablename UPDATE PART SET price price
1.1 WHERE price lt 20
37
SET Funtions

• A SET Function is one which operates on an entire
  column of values, not just a single value
• The SET functions supported are
• Name Format(Result)
  Description
• count integer Count of Occurrences
• sum integer,float,money Summation
• avg float,money Average (sum/count)
• max same as the argument Maximum value
• min same as the argument Minimum value

38
Use of SET functions
PART PartNo Pname Price
QOH P1 NUT 1.00
20 P2 BOLT 1.00
20 P3 CARAVAN 5000.00
3
SELECT count(partno) AS Part_count, avg(price) AS
Av_price, count(distinct price)
AS Price_count FROM part
Part_count Av_Price Price_count 3
1667.33 2
Set functions supported avg count max min
sum Set functions may not be used directly in a
search condition
39
Use of GROUP BY
List the number of prime ministers from each
party.
SELECT PARTY, COUNT() FROM
MINISTRY GROUP BY PARTY
PARTY
COUNT() Country
3 Free Trade
1 Labor
15 Liberal
17 National Labor
1 Nationalist
3 Protectionist
4 United Australia
5
40
Grouping by More than One Attribute
Group prime ministers by their state born and by
the state they represented. Give the numbers of
prime ministers and the total numbers of years
served.
SELECT STATE_BORN, STATE_REP, COUNT(),
SUM(YRS_SERVED) FROM PRIME_MINISTER GROUP
BY STATE_BORN, STATE_REP
41
Grouping with the WHERE Clause
For Prime Ministers born after 1900, list the
number of Prime Ministers born in each state and
the total number of years served.
SELECT STATE_BORN, COUNT(),
SUM(YRS_SERVED) FROM PRIME_MINISTER WHERE
BIRTH_YR gt 1900 GROUP BY STATE_BORN
STATE_BORN COUNT()
SUM(YRS_SERVED) WA
1
? VIC
2 10.50 NSW
3
6.52
42
Grouping with the HAVING Clause
For each state where the total years served by
Prime Ministers born in that state is less than
10 years, give the number of Prime Ministers
born in that state and the total number of years
served.
SELECT STATE_BORN, COUNT(),
SUM(YRS_SERVED) FROM PRIME_MINISTER GROUP
BY STATE_BORN HAVING SUM(YRS_SERVED) lt 10
STATE_BORN COUNT()
SUM(YRS_SERVED) TAS
1
7.25 QLD
2 0.13
43
Grouping with the HAVING Clause
PART_SUPPLIER PARTNO SUPPNO QTY_SUPP P1
S1 20 P2 S1
30 P1 S2 20 P3
S2 10
SELECT partno,Count(suppno) AS
Supp_count,Sum(qty_supp) AS Total_qty FROM
part_supplier GROUP BY partno HAVING
count(suppno) gt 1
PARTNO Supp_count Total_qty P1
2 40
44
SubQueries
Provides the facility of a query supplying
dynamic values to another query for use in the
search conditions of the main query. Example
Give the name and age at which death occurred for
each Prime Minister who died at
an age less than the average .
List in order of age at
death. SELECT PM_NAME, DEATH_AGEFROM
PRIME_MINISTER WHERE DEATH_AGE lt ( SELECT
AVG(DEATH_AGE)
FROM PRIME_MINISTER) The subquery
computes the average age at death. The main query
then selects the appropriate names and ages based
on the values supplied by the sub-query. (in this
case where the age at death is less than the
average)
45
SubQueries
Give the name and death age for each prime
minister who died at an age less than the
average death age of prime ministers. List in
ascending order of death age.
SELECT PM_NAME, DEATH_AGE FROM
PRIME_MINISTER WHERE DEATH_AGE lt (SELECT
AVG(DEATH_AGE)
FROM PRIME_MINISTER) ORDER BY
DEATH AGE
PM_NAME DEATH_AGE Holt H E
59 Lyons J A
60 Curtin J
60 Deakin A
63
46
SubQueries
Which prime minister died the oldest? Show the
name and age.
SELECT PM_NAME, DEATH_AGE FROM
PRIME_MINISTER WHERE DEATH_AGE (SELECT
MAX(DEATH_AGE)
FROM PRIME_MINISTER)
PM_NAME DEATH_AGE Forde F M
93
47
SubQuery with ANY Operator
PART_SUPPLIER
PARTNO P1 P2 P1 P3
SUPPNO S1 S1 S2 S2
QTY_SUPP 20 30 25 10
SELECT partno, suppno, qty_supp FROM
Part_supplier WHERE qty_supp gt ANY
(SELECT avg(qty_supp) FROM Part-supplier)
PARTNO P2 P1
SUPPNO S1 S2
QTY_SUPP 30 25
48
Multiple Nested SubQueries
Give the name and birth year of each prime
minister who was commissioned after Bob Hawke had
turned 21. Order by birth year.
SELECT PM_NAME, BIRTH_YR FROM
PRIME_MINISTER WHERE PM_NAME ANY
(SELECT PM_NAME
FROM
MINISTRY
WHERE YR_COMM gt
(SELECT BIRTH_YR 21

FROM PRIME_MINISTER
WHERE PM_NAME
Hawke R J L))
AND PM_NAME ltgt Hawke R
J L
ORDER BY BIRTH_YR
49
Result of Previous Query

Give the name and birth year of each prime
minister who was commissioned after Bob Hawke
had turned 21. Order by birth year.
PM_NAME BIRTH_YR Menzies R
G 1894 McEwan J 1900
Holt H E 1908 McMahon
W 1908 Gorton J G 1911
Whitlam E G 1916
50
SubQueries

• SELECT partno, suppno, qty_supp FROM
  Part_supplier
• WHERE qty_supp gt ANY
• (SELECT avg(qty_supp) FROM
  Part-supplier)
• Subqueries may be used in a number of SQL
  statements.
• select, update, insert, delete, create
  table,
• create view, create permit, create
  integrity
• Subqueries may be nested to several levels.
• Special comparison operators are used in
  additional to , ltgt, gt, lt, etc to indicate
  comparison to a set of values
• IN equals one of the values returned by
  the subquery
• ANY true if any value returned meets the
  condition
• ALL true if all values returned meet the
  condition

51
Correlated SubQueries
PART_SUPLIER PARTNO SUPPNO QTY_SUPP P1
S1 20 P2
S1 30 P1 S2
25 P2 S2
20 P3 S2 10

• SELECT partno, suppno, qty_supp
• FROM Part_supplier PS1
• WHERE qty_supp gt ANY
• (SELECT avg(qty_supp) FROM
  Part-supplier PS2
• WHERE PS2.partno PS1.partno)

PARTNO SUPPNO QTY_SUPP P1 S2
25 P2 S1 30
52
Correlated SubQueries
Which suppliers are supplying more than the
average for a part and how much of that part do
they supply?

• SELECT partno, suppno, qty_supp
• FROM Part_supplier PS1
• WHERE qty_supp gt ANY
• (SELECT avg(qty_supp) FROM
  Part-supplier PS2
• WHERE PS2.partno PS1.partno)
• Subqueries (inner queries) are generally executed
  once and return a value or a set of values to the
  outer query.
• With a correlated subquery, the outer query
  passes values to the inner query which then
  evaluates and returns a set of values to the
  outer query. This process repeats until the
  outer query terminates.

53
Joining Tables
EMP
DEP
ENAME RED BLUE BROWN
MGRNO E1 E1 E1
DEPTNO D1 D1 D2
DEPTNO D1 D2 D3
DNAME TAX PAY LEAVE
EMPNO E1 E2 E3
SELECT e.empno AS Number, e.ename AS Name,

d.dname AS Department FROM emp e,
dep d WHERE e.deptno d.deptno
Number Name Department E1 RED
TAX E2 BLUE
TAX E3 BROWN PAY
54
A Search and Join Condition
For each prime minister born in or after 1900,
give his name, birth year and party.
SELECT P.PM_NAME, BIRTH_YR, PARTY FROM
PRIME_MINISTER P, MINISTRY M WHERE
P.PM_NAME M.PM_NAME AND BIRTH_YR gt 1900
PM_NAME BIRTH_YR PARTY Holt H E
1908 Liberal Holt
H E 1908
Liberal McEwen J 1900
Country Gorton J G 1911
Liberal Gorton J G
1911 Liberal Gorton J G
1911 Liberal
55
A Search and Join Condition

• For each prime minister born in or after 1900,
  give his name, birth year and party.
• This is the same as the previous query, but uses
  the DISTINCT operator
• SELECT DISTINCT P.PM_NAME, BIRTH_YR, PARTY
• FROM PRIME_MINISTER P, MINISTRY M
• WHERE P.PM_NAME M.PM_NAME AND BIRTH_YR gt
  1900

PM_NAME BIRTH_YR PARTY Holt H E
1908
Liberal McEwen J 1900
Country Gorton J G 1911
Liberal
56
Multiple Joins
Give the name, birth year, party and the year of
marriage of prime ministers born in or after 1900.
SELECT DISTINCT P.PM_NAME, BIRTH_YR, MAR_YR,
PARTY FROM PRIME_MINISTER P, PM_MARRIAGE
W, MINISTRY M WHERE P.PM_NAME W.PM_NAME AND
P.PM_NAME M.PM_NAME AND
BIRTH_YR gt 1900
PM_NAME BIRTH_YR MAR_YR
PARTY Fraser J M 1930
1956 Liberal Gorton J G
1911 1935
Liberal Hawke R J L 1929
1956 Labor Holt H E
1908 1946
Liberal McEwen J 1900
1921 Country McEwen J
1900 1968 Country

57
Mirroring Tables

• Base tables are held on disk. Consequently when
  a query is executed, it is a copy of the base
  table which is used to satisfy the query.
• Where a single table query is run and internal
  joining is required, a procedure of mirroring
  or creating another copy of the table is used.
• This is done by using the correlation feature,
  and an example is shown on the next slide.
• If the base table values are altered, a new copy
  is written to disk after the successful
  completion of the query (commit)

58
Joining a Table to Itself
EMP
EMPNO E1 E2 E3
ENAME RED BLUE BROWN
MGRNO E1 E1 E1
DEPTNO D1 D1 D2
SELECT X.empno AS Number, X.ename AS Name,
Y.ename AS Manager FROM emp X, emp Y (we now
have two copies of the same table, x X and
Y) WHERE X.mgrno Y.empno
Number E1 E2 E3
Name RED BLUE BROWN
Manager RED RED RED
59
SubQuery with IN Operator
List the name and party of each deputy prime
minister who was also prime minister.
SELECT DISTINCT DEPUTY_NAME, PARTY FROM
DEPUTY_PM WHERE DEPUTY_NAME IN (SELECT
PM_NAME
FROM PRIME_MINISTER)
DEPUTY_NAME PARTY Chifley J B
Labor Cook J
Free Trade Deakin A
Protectionist
60
The EXISTS Operator
List the name, birth year and state represented
for prime ministers who were also deputy prime
ministers.
SELECT PM_NAME, BIRTH_YR, STATE_REP FROM
PRIME_MINISTER WHERE EXISTS (SELECT
FROM
DEPUTY_PM
WHERE DEPUTY_NAME PM_NAME)
PM_NAME BIRTH_YR
STATE_REP Deakin A
1856 VIC Cook J
1860 NSW Hughes W M 1862
NSW
61
The NOT EXISTS Operator
Give the name, state represented and death age of
each prime minister who has no recreational
interests.
SELECT PM_NAME, BIRTH_YR, DEATH_AGE FROM
PRIME_MINISTER P WHERE NOT EXISTS
(SELECT
FROM PM_RECREATION
WHERE PM_NAME
P.PM_NAME)
PM_NAME STATE_REP
DEATH_AGE Reid G H NSW
73 Fisher A
QLD
66 Cook J NSW
87
62
Create View

• CREATE VIEW view-name (colname , colname )
  AS subselect
• WITH CHECK OPTION
• Creates a virtual table by storing the view
  definition in the catalog.
• Updates, inserts and deletes are not permitted
  if
• the subselect accesses more than one base table
  
• the view was created from a non-updatable view
• any columns in the view are derived.
• Additionally inserts are not allowed if the
  view contains a where clause with the check
  option or if any not null not default column of
  the base table is not part of the view.

63
Create View

• The with check option will not allow update of
  columns that are part of the view's where clause.
• Provides logical data independence when base
  table structure changes.
• Same data may be seen in different ways by
  different users.
• Users perception may be simplified and views
  provide automatic security.

64
Create View
Emp (empno, empname, salary_pa, deptno) Dept
(deptno, dname) CREATE VIEW empdetails (empno,
empname, dname, salary_fn) AS SELECT
e.empno, e.empname, d.dname,
e.salary / 26 FROM emp e, dept d WHERE e.deptno
d.deptno
65
To Execute a View

• Views are made active (executed) by running a
  select operation.
• To run the previous View the command structure
  would be
• Select from empdetails
• Conditions (where) could be applied to further
  limit the output (result table) scope.
• And just to test you out, what would be the
  outline content of the result of the above select
  command ?

66
The Purpose of SQL... and so ?

• SQL is a database access language which allows
  applications (and users) to enter, retrieve,
  update and delete data in a database.
• Ingres DBMS does NOT have a programming language
  which can be used to develop powerful database
  applications
• To achieve this, there must be a procedural
  language which interacts with SQL.
• Ingres has an embedded SQL feature which
  interacts with Cobol,C, PL/1, ...
• Ingres has a Knowledge Management module which
  allows for the building of Rules and Procedures

67
The Purpose of SQL... and so ?

• Oracle has a procedural language - PL/SQL
• It also supports
• packages
• procedures
• snapshots
• triggers
• which are developed to provide specific
  application logic

68
(No Transcript)