Transcript SQL - Pravin Shetty > Resume

Lecture No. 4
CSE3180 Summer 2005. Lect 4 / 1
Lecture 4
Structured Query Language - SQL
Commands
Statements
Queries
Views
CSE3180 Summer 2005. Lect 4 / 2
PowerPoint Overheads
• The Overheads and other materials are accessible via
http://www.csse.monash.edu.au/courseware/cse3180s
• Files should be saved and copied to A:\ or D:\ (filename)
• Then you can browse these via PowerPoint97/2000
• or you can display them on the terminal screen (in the labs
this will be Office 2000 software).
CSE3180 Summer 2005. Lect 4 / 3
Doctor’s Diary
1. I examined your patient today who is still under our car
for physical therapy
2. Discharge status : Alive, but without my permission
CSE3180 Summer 2005. Lect 4 / 4
Objectives
• This lecture we will be looking at some of the aspects and
functions of SQL - Structured Query Language, which
provides the means of accessing data in a Relational Data
Base.
• Microsoft Access provides users with a Graphics User
Interface which in turn is translated into (non standard)
SQL
• When you have developed part of your assignment in
Access, run a query, and then display the query in its SQL
statement form. You should be easily able to recognise the
table names and the functions you have set up.
CSE3180 Summer 2005. Lect 4 / 5
Objectives - cont’d
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
CSE3180 Summer 2005. Lect 4 / 6
Objectives
As we work through the material and the examples, I will
point out the functions or facilities which Access provides,
and there will be some SQLServer2000 references.
CSE3180 Summer 2005. Lect 4 / 7
SQL - Data Manipulation
SQL
Structured Query Language
Originally designed and implemented by IBM research as
the interface to a relational database.
ANSI Standard 1986. SQL99 Standard released. (SQL3)
A declarative language.
The DBMS performs retrieval.
A choice of access routines is made to optimise the query.
SQL specifies syntax features for definition of the
database tables, retrieval, and update
Summer Semester 2000. Lect 4 / 8
An Introduction to SQL
Firstly some plusses for SQL.
1. SQL is the one industry standard for querying
databases
2. Other ‘tools’ such as front enders don’t allow the
developer to use all of the features of a database
3. Tools provided invariably do not exploit the full
functionality of the underlying language
4. An SQL query in a client-server environment can be
run in any application language and the result will
always be the same
CSE3180 Summer 2005. Lect 4 / 9
Procedural and Non-Procedural Languages
SQL requires a different approach from that used in
other programming languages
C, Fortran, Basic, Cobol, Pascal, PL/1 are procedural
languages. They are characterised by statements
which tell the executing computer what to do, and in
a structured step-by-step way (even when loops are
used).
SQL is a declarative language - the computer is told
what the user wants to achieve and the computer
‘decides’ on how to achieve this requirement, and
correctly.
The user sees the results.
CSE3180 Summer 2005. Lect 4 / 10
Some SQL Basics
SQL acts as a bridge between
– the user
– the database management system (DBMS)
– the data tables
– the transactions which involve the previous
3 items
SQL also allows the ‘system’ to be administered and
managed by a database administrator using the
same format : procedural commands and data in
tables.
SQL can be embedded into source code (C, Cobol,
C++, Java)
CSE3180 Summer 2005. Lect 4 / 11
SQL Some Variations
• Microsoft Access SQL is mostly ANSI-89 Level 1
compliant. (watch SQLServer as a replacement)
• Some 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 (eg SUM(DISTINCT att)
CSE3180 Summer 2005. Lect 4 / 12
Some Aspects of SQL
The language consists of commands which allow users
(with appropriate privileges) to
– create database and table structures
– perform data manipulations
– perform database administration
– query a database to extract information
All relational Database Management Systems support SQL
Many software vendors have developed extensions to the
basic SQL command set
CSE3180 Summer 2005. Lect 4 / 13
Some Aspects of SQL
SQL is a non-procedural language
The user specifies what is to be done, but not how (which
invariably irritates and confuses ‘normal’ software language
developers
Complex activities required to perform a process are the
function of SQL, not detailed user code software
Some of the advanced features of SQL include
– stored procedures and triggers
– conditions and looping
CSE3180 Summer 2005. Lect 4 / 14
Some Aspects of SQL
It is not meant to ‘stand alone’ in the applications area
It is not intended to create menus, special report forms,
overlays, pop-ups,..
These features are normally supplied as vendorenhancements
It is a data management and extraction tool
CSE3180 Summer 2005. Lect 4 / 15
SQL and its ANSI Support
• There is a National Committee for Information Technology
Standards - NCITS.
• There is a H2 Technical Committee which reports to
NCITS on matters of database - and in particular database
processing capabilities. This was previously ANSI X3H2
• Successful recommendations are then proposed to and
invariably accepted by the American National Standards
Institute
• Another ‘working group’ is the Object Management Group
(OMJ) which also has a Technical Committee
• These 2 Committees are responsible for the framework for
co-operation between SQL and Java, and SQL and XML
CSE3180 Summer 2005. Lect 4 / 16
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: Ingres and Oracle support the 'owner' concept.
Hence only 'owners' can DROP nnnn
Oracle 8i has an ALTER command to vary attribute
characteristics. Ingres V6.4 does not support this feature
CSE3180 Summer 2005. Lect 4 / 17
DML - Data Manipulation Language
Group 1 - Data Modification
INSERT - Add a single row (interactive)
COPY
- Perform successive INSERTS as a
'transaction ‘set’ - interactive
- 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 or DOCUMENTATION.
CSE3180 Summer 2005. Lect 4 / 18
Group 2
DML - Data Manipulation Language
DATA 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
- Allow access privileges to users (e.g.
read,write,update to nominated tables or attribute values in
tables)
GRANT
REVOKE
- Revoke or cancel access privileges
CSE3180 Summer 2005. Lect 4 / 19
CREATE
The Oracle based SQL syntax is :
create table <tablename> ( columnname format
{,columnname format})
e.g. create table wages(name varchar2(10),
identity number(2,0),
Department varchar2(3),
date_comm date);
The same table but with more constraints :create table wages(name varchar2(10) not null,
identity number(2,0) not null,
Department varchar2(3) not null,
date_comm date) not null;
CSE3180 Summer 2005. Lect 4 / 20
Optional CREATE
create table highincome
as select name, salary
from wages
where salary > 75000; (attribute properties are copied to the new
table)
This creates an ‘extract file’ of only those entries from the
table ‘wages’ where the salary is in excess of 75,000 (what
currency ?)
It is NOT automatically updated unless there is either
(a) cascade update set with the referential integrity
constraint
or (b) a trigger and constraint is developed for the 2 tables
CSE3180 Summer 2005. Lect 4 / 21
INSERT
This command allows data to be inserted, one row at a time,
into an existing table
Syntax: Insert into <tablename> (list of attributes)
values (list of values)
Note: The list of attributes can be ignored providing the order
of attribute values, and completeness 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,1968);
CSE3180 Summer 2005. Lect 4 / 22
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)
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
CSE3180 Summer 2005. Lect 4 / 23
Oracle - SQLLOAD
Inserting is a slow process, and the sqlload command allows
for data to be bulk loaded form 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)
CSE3180 Summer 2005. Lect 4 / 24
Oracle Loader
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
CSE3180 Summer 2005. Lect 4 / 25
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
CSE3180 Summer 2005. Lect 4 / 26
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
a sample:
copy from rsimpson@cse2180
(to the current database)
create empcopy2
using select * from user.dept
CSE3180 Summer 2005. Lect 4 / 27
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 ?)
CSE3180 Summer 2005. Lect 4 / 28
Database Queries
The commands which you have just seen are not normally
available to ‘users’ as in Administration staff, Analytical
staff, Review and Audit staff members.
They are invariably restricted to Designers, Software
Application and Database Administration members.
The most frequent use of a database is associated with
querying and retrieving data - and in many organisations
these activities are embedded in host programs with menu
options for individual user interaction - and here also there
are privileges or restrictions on who can do what.
CSE3180 Summer 2005. Lect 4 / 29
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)
CSE3180 Summer 2005. Lect 4 / 30
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
CSE3180 Summer 2005. Lect 4 / 31
Select Examples
PART
PartID
P1
P2
P3
Pname
Nut
Bolt
Caravan
Price
0.2
1
5000
QOH
20
40
3
SELECT * FROM PART;
Selects all column values for all rows
SELECT PARTID, PNAME FROM PART
WHERE PRICE BETWEEN 0.2 AND 1.0;
Selects rows where price >= .2 and <= 1.0
SELECT PARTID, PNAME FROM PART
WHERE PNAME IN ('NUT','BOLT');
Selects rows where pname has a value in the following list
CSE3180 Summer 2005. Lect 4 / 32
SELECT
PART
PartID
P1
P2
P3
Pname
Nut
Bolt
Caravan
Price
0.2
1
5000
QOH
20
40
3
SELECT pname, price*qoh AS Pvalue
FROM PART
WHERE price > 0.20 AND price * qoh > 30.0
ORDER BY pname desc;
Result Table --->
Pname
Caravan
Bolt
Pvalue
15000
40
CSE3180 Summer 2005. Lect 4 / 33
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]
CSE3180 Summer 2005. Lect 4 / 34
Expressions in Select
Arithmetic operators are + - ** * /
Comparison operators are = != <> ^= > < >= <=
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.
CSE3180 Summer 2005. Lect 4 / 35
SELECT Vocabulary
ALL
BY
GROUP
LIKE
ORDER
AND
DISTINCT
HAVING
NOT
SELECT
ANY
EXISTS
IS
NULL
UNION
BETWEEN
FROM
IN
OR
WHERE
Arithmetic Operators
+
-
*
/
Relational Operators
=
!=
Logical Operators
AND
OR
Parentheses
(
)
Special Operators
BETWEEN IN
Existential Operator
EXISTS
<
>
<= >=
NOT
LIKE
NULL
CSE3180 Summer 2005. Lect 4 / 36
SQL - Sample Database Schema
PRIME _MINISTER
PM_NAME BIRTH_YR YRS_SERVED DEATH_AGE STATE_BORN STATE_REP
MARRIAGE
PM_NAME SPOUSE_NAME MAR_YR PM_AGE NR_CHILDREN
MINISTRY
MIN_NR PM_NAME PARTY DAY_COMM MTH_COMM YR_COMM
CSE3180 Summer 2005. Lect 4 / 37
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
Barton E
Page E C G
Chifley J
Holt H E
McMahon W
Whitlam E G
BIRTH_YR
1849
1880
1885
1908
1908
1916
BIRTH_YR + DEATH_AGE
1920
1961
1951
1967
?
?
CSE3180 Summer 2005. Lect 4 / 38
Logical Operators
Which prime ministers were born in Victoria before the turn of the century?
SELECT PM_NAME, BIRTH_YR, STATE_BORN
FROM
PRIME_MINISTER
WHERE STATE_BORN=‘VIC’ AND BIRTH_YR < 1900;
PM_NAME
Deakin A
Bruce S M
Scullin J H
Menzies R G
Curtin J
BIRTH_YR
1856
1883
1876
1894
1885
STATE_BORN
VIC
VIC
VIC
VIC
VIC
CSE3180 Summer 2005. Lect 4 / 39
Combining Logical Operators
Which prime ministers were born in NSW and then represented Victoria
or have simply not served less than two years?
SELECT
FROM
WHERE
AND
OR
PM_NAME, STATE_BORN, STATE_REP, YRS_SERVED
PRIME _MINISTER
STATE_REP = ‘VIC’
STATE_BORN = ‘NSW’
NOT YRS_SERVED < 2;
(or Yrs_Served >1.99)
PM_NAME
Holt H E
Gorton J G
Whitlam E G
Fraser J M
STATE_BORN
NSW
VIC
NSW
VIC
STATE_REP
VIC
VIC
NSW
VIC
YRS_SERVED
1.88
3.17
2.92
7.33
CSE3180 Summer 2005. Lect 4 / 40
Select Examples - ‘PART’ table
SELECT PNAME FROM PART
WHERE QOH IS NULL;
Selects those rows where qoh has a null value (is this
different from a 0 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
And the answer to the question above is ‘Definitely YES’
CSE3180 Summer 2005. Lect 4 / 41
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(*)
Liberal PMs
5
COUNT(DISTINCT PM_NAME)
2
CSE3180 Summer 2005. Lect 4 / 42
UNION Operator
Normally used with the SELECT command to form a single
table from two or more tables. This can be a real or virtual
table.
The attributes used in a UNION must be identical - in data
type and size.
example:
create table names as select name from master
union
select newnames from newmaster;
CSE3180 Summer 2005. Lect 4 / 43
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 Lowell
(8 names)
(8 names)
CSE3180 Summer 2005. Lect 4 / 44
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
Jed Hopkins
Pat Lavay
Peter Lawson
Ted Butcher
Wilfred Lowell
(12 names)
note that there are NO duplicates
CSE3180 Summer 2005. Lect 4 / 45
DELETE
DELETE FROM tablename [corr-name]
[ WHERE search-condition ]
Delete one or many rows in a table.
In general search-condition and qualification may
not involve a sub select on tablename.
DELETE FROM PART WHERE qoh < 4.00;
CSE3180 Summer 2005. Lect 4 / 47
UPDATE
UPDATE tablename [corr-name]
[ FROM tablename [corr-name] {, tablename [corrname]}]
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 < 20;
CSE3180 Summer 2005. Lect 4 / 48
SET Functions
• 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)
count
integer
sum
integer,float,money
avg
float,money
max
same as the argument
min
same as the argument
Description
Count of Occurrences
Summation
Average (sum/count)
Maximum value
Minimum value
CSE3180 Summer 2005. Lect 4 / 49
Use of SET functions
PART
PartID
P1
P2
P3
Pname
Nut
Bolt
Caravan
Price
1.00
1.00
5000
QOH
20
20
3
SELECT count(PartID) AS Part_count, avg(price) AS Av_price,
count(distinct price) AS Price_count
FROM part;
Part_count
3
Av_price
1667.33
Price_count
2
Set functions supported: avg count max min sum
Set functions may not be used directly in a search
condition
CSE3180 Summer 2005. Lect 4 / 50
Use of GROUP BY
List the number of prime ministers from each party.
SELECT
PARTY, COUNT(*)
FROM
MINISTRY
GROUP BY PARTY;
PARTY
Country
Free Trade
Labor
Liberal
National Labor
Nationalist
Protectionist
United Australia
COUNT(*)
3
1
15
17
1
3
4
5
CSE3180 Summer 2005. Lect 4 / 51
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;
STATE_BORN
?
?
NSW
NSW
QLD
TAS
VIC
VIC
STATE_REP
NSW
QLD
NSW
VIC
QLD
TAS
VIC
WA
COUNT(*)
4
1
5
1
2
1
7
1
SUM(YRS_SERVED)
9.67
4.81
11.83
1.88
0.13
7.25
42.69
3.75
CSE3180 Summer 2005. Lect 4 / 52
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 > 1900
GROUP BY STATE_BORN;
STATE_BORN
WA
VIC
NSW
COUNT(*)
1
2
3
SUM(YRS_SERVED)
?
10.50
6.52
CSE3180 Summer 2005. Lect 4 / 53
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) < 10;
STATE_BORN
TAS
QLD
COUNT(*)
1
2
SUM(YRS_SERVED)
7.25
0.13
CSE3180 Summer 2005. Lect 4 / 54
Grouping with the HAVING Clause
PART_SUPPLIER
PartID
P1
P2
P1
P3
SuppID
S1
S1
S2
S2
Qty_Supp
20
30
20
10
SELECT PartID, Count(SuppID) AS Supp_count,
Sum(qty_supp) Total_qty
FROM part_supplier
GROUP BY PartID
HAVING count(SuppID) > 1;
PartID
P1
Supp_count Qty_Supp
2
40
CSE3180 Summer 2005. Lect 4 / 55
SubQueries
Provide 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_AGE
FROM PRIME_MINISTER
WHERE DEATH_AGE < ( 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)
CSE3180 Summer 2005. Lect 4 / 56
SubQueries
Give the name and death age for each prime minister who died
at an age less than the average death age of all prime ministers.
List in ascending order of death age.
SELECT
FROM
WHERE
PM_NAME, DEATH_AGE
PRIME_MINISTER
DEATH_AGE < (SELECT AVG(DEATH_AGE)
FROM PRIME_MINISTER);
ORDER BY DEATH AGE
PM_NAME
Holt H E
Lyons J A
Curtin J
Deakin A
DEATH_AGE
59
60
60
63
CSE3180 Summer 2005. Lect 4 / 57
SubQueries
Which prime minister died the oldest?
Give the name and that age.
SELECT
FROM
WHERE
PM_NAME, DEATH_AGE
PRIME_MINISTER
DEATH_AGE = (SELECT MAX(DEATH_AGE)
FROM PRIME_MINISTER);
PM_NAME
Forde F M
DEATH_AGE
93
CSE3180 Summer 2005. Lect 4 / 58
SubQuery with IN Operator
List the name and party of each deputy prime minister who was
also a Prime Minister.
SELECT
FROM
WHERE
DISTINCT DEPUTY_NAME, PARTY
DEPUTY_PM
DEPUTY_NAME IN (SELECT PM_NAME
FROM PRIME_MINISTER);
DEPUTY_NAME
Chifley J B
Cook J
Cook
Deakin A
PARTY
Labor
Free Trade
Nationalist
Protectionist
CSE3180 Summer 2005. Lect 4 / 59
SubQuery with ANY Operator
PART_SUPPLIER
PartID
P1
P2
P1
P3
SuppID
S1
S1
S2
S2
Qty_Supp
20
30
20
10
SELECT PartID, SuppID, qty_supp FROM Part_supplier
WHERE qty_supp > ANY
(SELECT avg(qty_supp) FROM Part-supplier);
PartID SuppID Qty_Supp
P2
S1
30
P1
S2
25
CSE3180 Summer 2005. Lect 4 / 60
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
FROM
WHERE
PM_NAME, BIRTH_YR
PRIME_MINISTER
PM_NAME = ANY
(SELECT PM_NAME
FROM MINISTRY
WHERE YR_COMM >
(SELECT BIRTH_YR + 21
FROM PRIME_MINISTER
WHERE PM_NAME = ‘Hawke R J L’))
AND PM_NAME <> ‘Hawke R J L’
ORDER BY BIRTH_YR;
CSE3180 Summer 2005. Lect 4 / 61
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
Menzies R G
McEwan J
Holt H E
McMahon W
Gorton J G
Whitlam E G
BIRTH_YR
1894
1900
1908
1908
1911
1916
CSE3180 Summer 2005. Lect 4 / 62
A Search and Join Condition
For each prime minister born in or after 1900, give the name,
birth year and party.
SELECT
FROM
WHERE
P.PM_NAME, BIRTH_YR, PARTY
PRIME_MINISTER P, MINISTRY M
P.PM_NAME = M.PM_NAME AND BIRTH_YR >= 1900;
PM_NAME
Holt H E
Holt H E
McEwen J
Gorton J G
Gorton J G
Gorton J G
BIRTH_YR
1908
1908
1900
1911
1911
1911
PARTY
Liberal
Liberal
Country
Liberal
Liberal
Liberal
CSE3180 Summer 2005. Lect 4 / 63
SubQueries
SELECT PartID, SuppID, qty_supp FROM Part_supplier
WHERE qty_supp > 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 =, <>, >, <, 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
CSE3180 Summer 2005. Lect 4 / 64
Correlated SubQueries
PART_SUPPLIER (it’s been updated)
PartID
SuppID
Qty_Supp
P1
S1
20
P2
S1
30
P1
S2
20
P2
S2
20
P3
S2
10
SELECT PartID, SuppID, qty_supp FROM Part_supplier PS1
WHERE qty_supp > ANY
(SELECT avg(qty_supp) FROM Part-supplier PS2
WHERE PS2.SuppID = PS1.SuppID);
PartID
P1
P2
SuppID
S2
S1
Qty_Supp
25
30
CSE3180 Summer 2005. Lect 4 / 65
Correlated SubQueries
Which suppliers are supplying more than the average for a part and
how much of that part do they supply?
SELECT PartID, SuppID, qty_supp
FROM Part_supplier PS1
WHERE qty_supp > ANY
(SELECT avg(qty_supp) FROM Part-supplier PS2
WHERE PS2.PartID = PS1.PartID);
·
Subqueries (inner queries) are generally executed once and return 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.
CSE3180 Summer 2005. Lect 4 / 66
Joining Tables
EMP
EmpID
E1
E2
E3
DEP
Ename
Red
Blue
Brown
MgrID
E1
E1
E1
DeptID
D1
D1
D2
DeptID
D1
D2
D3
Dname
Tax
Pay
Leave
SELECT e.empid AS Number, e.ename AS Name,
d.dname AS Department
FROM emp e, dep d
WHERE e.deptid = d.deptid;
Number
E1
E2
E3
Name
Red
Blue
Brown
Department
Tax
Tax
Pay
CSE3180 Summer 2005. Lect 4 / 67
A Search and Join Condition
For each prime minister born in or after 1900, give the name,
birth year and party.
SELECT
FROM
WHERE
P.PM_NAME, BIRTH_YR, PARTY
PRIME_MINISTER P, MINISTRY M
P.PM_NAME = M.PM_NAME AND BIRTH_YR >= 1900;
PM_NAME
Holt H E
Holt H E
McEwen J
Gorton J G
Gorton J G
Gorton J G
BIRTH_YR
1908
1908
1900
1911
1911
1911
PARTY
Liberal
Liberal
Country
Liberal
Liberal
Liberal
CSE3180 Summer 2005. Lect 4 / 68
A Search and Join Condition
With ‘distinct’
For each prime minister born in or after 1900, give the name,
birth year and party.
SELECT
FROM
WHERE
DISTINCT P.PM_NAME, BIRTH_YR, PARTY
PRIME_MINISTER P, MINISTRY M
P.PM_NAME = M.PM_NAME AND BIRTH_YR >= 1900;
PM_NAME
Fraser J M
Gorton J G
Hawke R J L
Holt H E
McEwan J
McMahon W
Whitlam E G
BIRTH_YR
1930
1911
1929
1908
1900
1908
1916
PARTY
Liberal
Liberal
Labor
Liberal
Country
Liberal
Labor
CSE3180 Summer 2005. Lect 4 / 69
Multiple Joins
Give the name, birth year, party and the year of marriage of
prime ministers born in or after 1900.
SELECT
FROM
WHERE
AND
AND
DISTINCT P.PM_NAME, BIRTH_YR, MAR_YR, PARTY
PRIME_MINISTER P, PM_MARRIAGE W, MINISTRY M
P.PM_NAME = W.PM_NAME
P.PM_NAME = M.PM_NAME
BIRTH_YR >= 1900;
PM_NAME
Fraser J M
Gorton J G
Hawke R J L
Holt H E
BIRTH_YR
1930
1911
1929
1908
MAR_YR
1956
1935
1956
1946
PARTY
Liberal
Liberal
Labor
Liberal
CSE3180 Summer 2005. Lect 4 / 70
Anatomy of the Previous Query
SELECT
FROM
WHERE
AND
AND
DISTINCT P.PM_NAME, BIRTH_YR, MAR-YR, PARTY
PRIME_MINISTER P, PM_MARRIAGE W, MINISTRY M
P.PM_NAME = W.PM_NAME
P.PM_NAME = M.PM_NAME
BIRTH_YEAR >= 1900 ;
CSE3180 Summer 2005. Lect 4 / 71
Joining a Table to Itself
EMP
EmpID
E1
E2
E3
Ename
Red
Blue
Brown
MgrID
E1
E1
E1
DeptID
D1
D1
D2
SELECT X.empno AS Number, X.ename AS Name,
Y.ename AS Manager
FROM emp X, emp Y
WHERE X.mgrno = Y.empno;
Number
E1
E2
E3
Name
Red
Blue
Brown
Manager
Red
Red
Red
CSE3180 Summer 2005. Lect 4 / 72
The Existential Operator
This is a test for Existence (or non-existence)
It differs from the IN operator in that it does not match a
column or columns used in a correlated sub-query
A query such as this : select name from workerskill
group by name having count (skill) > 1;
would give a list of workers who had more than 1 skill
from a table named workerskill.
CSE3180 Summer 2005. Lect 4 / 73
The Existential Operator
However if we attempt to find both the Name and Skill, the
query will fail as the count is on the Primary Key, in this
example, skill.
There is only one Primary Key per row, and therefore the
COUNT > 1 cannot exceed 1, and the query must fail.
In using the Existential operator, we use a subterfuge and
that is to have the query think that the inspected table
occurs twice. (by correlation).
CSE3180 Summer 2005. Lect 4 / 74
The Existential Operator
How is this done ?
Let’s look at this ‘solution’.
Select name, skill from workerskill ws
where exists (select * from workerskill
where ws.name = name
group by name
having count(skill) > 1);
as an exercise, you might like to try a solution with the
subquery such as ‘where name IN…... and then use a
similar construction to the above
CSE3180 Summer 2005. Lect 4 / 75
The EXISTS Operator
List the name, birth year and state represented of prime
ministers who were also deputy prime ministers.
SELECT
FROM
WHERE
PM_NAME, BIRTH_YR, STATE_REP
PRIME_MINISTER
EXISTS (SELECT *
FROM DEPUTY_PM
WHERE DEPUTY_NAME = PM_NAME);
PM_NAME
Deakin A
Cook J
Hughes W M
BIRTH_YR
1856
1860
1862
STATE_REP
VIC
NSW
NSW
CSE3180 Summer 2005. Lect 4 / 76
The NOT EXISTS Operator
Give the name, state represented and death age of each
prime minister who has no recreational interests.
SELECT
FROM
WHERE
PM_NAME, BIRTH_YR, DEATH_AGE
PRIME_MINISTER P
NOT EXISTS (SELECT *
FROM PM_RECREATION
WHERE PM_NAME = P.PM_NAME);
PM_NAME
Reid G H
Fisher A
Cook J
STATE_REP
NSW
QLD
NSW
DEATH_AGE
73
66
87
CSE3180 Summer 2005. Lect 4 / 77
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.
The with check option will not allow update of columns that are part of
the view's where clause.
Provide 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.
CSE3180 Summer 2005. Lect 4 / 78
Create View
Emp (empid, empname, salary_pa, deptid)
Dept (deptid, deptname)
CREATE VIEW empdetails (empid, empname,
deptname, salary_fn) AS
SELECT e.empid, e.empname, d.deptname,
e.salary_pa / 26
FROM emp e, deptname d
WHERE e.deptid = d.deptid;
CSE3180 Summer 2005. Lect 4 / 79
To Run a VIEW
Select * from (View Name);
• Other conditions may be applied at runtime which to
supplement those already contained in the View construct
e.g. Select * from empdetails
where deptname = ‘Finance’ ;
There is an ‘updatable view’ process which is possible, but
with much constraint. There is a paper on this at the
website.
CSE3180 Summer 2005. Lect 4 / 80