Chapter6 SQL:Data Manipulation

Chapter6 SQL:Data Manipulation

Through this and the next two chapters,we use the following extended form of the Backus Naur Form(BNF) notation to define SQL statements:

• uppercase letters are used to represent reserved words and must be
spelled exactly as shown;
• lowercase letters are used to represent user-defined words;
• a vertical bar(|)indicates a choice among alternatives;
• curly brace indicate a required element.For example,{a}
• square brackets indicate an optional element;for example,[a];
• an ellipsis(...)is used to indicate optional repetition of an item zero or more times.

For example:{a|b}(,c...)  

means either a or b followed by zero or more repetitions of c separated bycommas

6.3.1Simple Queries

SELECT

[DISTINCT|ALL]{*|[columnExpression[AS newName]][,...]}

FROM

TableName[alias][,...]

[WHERE

condition]

[GROUP BY

columnList][HAVING condition]

[ORDER BY

columnList]

HAVING

filters the groups shbject to some condition

eg. 1.SELECT staffNo,fName,IName,salary/12 FROM Staff-------------this is an example of the use of a calculated fiedl.

      2.SELECT staffNo,fName,IName,salary/12 AS monthlySalary  FROM Staff;---------The ISO standard allows the column to be named using as AS clause.

Row selection(WHERE calcuse)

The five basic search conditions are follows:

• Comparison

• Range

• Set Membership

• Pattern match : Test whether a string match a special pattern

• Null :Test whether a column has a null(unknow) value

Set Membership:

eg.

SELECT

staffNo,fName,position

FROM

Staff

WHERE

position IN('Manager' , 'Supervisor');

The membership set test(IN) test whether a data value match one of a list of values,there is a negated version(NOT IN)that can be used to check for data values than do not lie in the specific list of values.

Pattern Match:

Sql has two special pattern-match symbols:

• The % character represents any sequences of zero or more charecters
• The _undersore character represents any single character.

• All other characters in the pattern represent themselves.

For exmaple:

• address LIKE 'H%' means the first character must be H ,but the rest of
the string can be anything.
• Address LIKE 'H_ _ _'mean there must be exactly four characters in the
string, the first of which must be an H.
• address LIKE '%e' mean any sequence of characters , of the length at
least 1,with the last character an e.
• address LIKE '%Glasgow%' means a sequence of characters of any
length containing Glasgow.
• Address NOT LIKE 'H%'

Null:

eg.
SELECT
clientNo, viewDate
FROM
Viewing
WHERE
propertyNo='PG4' AND comment IS NULL;

6.3.3Using the SQL Aggregate Functions
we often want to perform som form of summation or aggregation of data.The ISO defines the aggregation function:

• COUNT
• AVG
• MIN
• MAX
• SUM

1.These functions operate on a single column of table and return a single value.
2.COUNT,MAX,MIN apply to both numberic and nonnumberic fields,but SUM and AVG must apply on numberic field.
3.COUNT(*) is a special use of COUNT that counts all the rows of a table, regardless whether nulls or duplicate values occur.
4. Apart from COUNT(*),each function eliminates nulls fiset and operate only on the remaining nonnull values.
5.If we want to elimate the duplicate value before the function is applied,we use the keyword DISTINCT before the column name in the function.The ISO
standards allow the keyword ALL to be specified if we do not want to elimate duplicates.
6. If the SELECT list includes an aggregation function and no GROUP BY clause is being used to group data together,then no item in the SELECT list
can include any reference to a column.

For example,the following query is illegal:
SELECT staffNo,count(salary)
FROM Staff;
because the query does not have a GROUP BY clause and the column staffNo
in the SELECT list is used outside an aggregation function.

Example:

SELECT COUNT(*) AS myCount
FROM PropertyForRent
WHERE rent >350;
SELECT COUNT(DISTINCT property) AS myCount
FROM Viewing
WHERE ViewDate BETWEEN '1-May-08' AND '11-May-08';
SELECT COUNT(staffNo) AS myCount,SUM(salary) AS mysum
FROM staff

WHERE position = 'Manager';

6.3.4Grouping Results(GROUP BY Clause)

A query that include the GROUP BY clause is called the grouped query,because it group the data from the SELECT table and produces a single summary row for each group.
Wnen GROUP BY is used ,each item in the SELECT list must be single-value per group, In addition , the SELECT clause may contain only:

• column names;
• aggregation function
• constants
• an expression involving combinations of these elements

1.All column names in the SELECT list must appear in the GROUP-BY clause unless the name is used only in the aggregation.用中文来讲就是,slect 里面的元素是 group 的体现。
2.When the WHERE clause is uesd with GROUP BY , the WHERE calues is appied first.

Restricting groupings(HAVING clause)

The WHERE clause filter individual row going into the final result table,

The HAVING clause filter group going into the result table.

SELECT branchNo, COUNT(staffNo) AS myCount, SUM(salary) AS mySum
FROM Staff
GROUP BY branchNo
HAVING COUNT(staffNo) >1;

Subqueries

The keyword ANY and ALL may be used with subqueries  that produce a single column of numbers.

If the subqueries is preceded by the keyword ALL,the condition will be true only if it is satisfied all values produced by the subquery.
If the subqueries is preceded by the keyword ANY,the conditioin will be true only if it satisfied any values produced by the subquery.
If the subquery is empty,the ALL condition return true,and ANY condition return false.
The ISO standard also allow the qualifiter SOME to be used in place of ANY.

SELECT
FROM
WHERE
staffNo,fName,position,salary
Staff
salary > SOME(SELECT salary
FROM
Staff
WHERE branchNo = 'B003'
)

6.3.7Multi-table Queries
If the result table contain column from different tables,we must use a join....It is also possible use an alias for a table named in the FROM clauses...

SELECT
FROM
WHERE
c.clientNo,fName,IName,propertyNo,comment
Client c,View v
c.clientNo = v.clientNo;

...

we specify a search condition that compares the primary key and foreign key.

The SQL standards provides the following alternative ways to specify this join:
• FROM Client c JOIN Viewing v ON c.clientNo=v.clientNo
• FROM Client c JOIN Viewing USING clientNO
• FROM Client NATURAL JOIN Viewing

6.3.8EXISTS and NOT EXISTS

The keywords EXIST and NOT EXISTS is designed for use only with subqueries.

They produce a simple true and false.
EXIST is true if and only if there is at least one row in the result table returned by the subquery; it is false if the subquery return empty table.

SELECT staffNo,fName,IName,position
FROM Staff s
WHERE EXIST(SELECT *
FROM Branch b

WHERE s.branchNo = b.branchNo AND city = 'LONDON');

6.3.9Combine Result Table(UNION,INERSECT,EXCEPT)

• The Union of two table ,A and B,is a table containing all row that in either the first table A or the table b.
• The Intersect of two tables,A and B ,is a table containing all rows that are common to both table A and B.
• The Difference of two tables,A and B , is a table containing all rows that in table A but not in tableB .

The most important one being that two table have to be union-compatible,that is ,they have the same structure.This implies that the two tables must contain the same columns and that their corresponding column have the same data type and length.

The format of the set operator clause in each case is operator

[ALL][CORRESPONDING[BY{column1[,...]}]]

If CORRESPONDING BY is specified , then the set operation is performed on the named column;
if CORRESPONDING is specified but not the BY clause,the set operation is perform on the column that are common to both table.
If ALL is specified,the result can include duplicate row.
(SELECT city
FROM Branch
WHERE city IS NOT NULL
)
UNION
(SELECT city
FROM PropertyForRent
WHERE city IS NOT NULL

)

this is equivalent to
(SELECT *
FROM Branch
WHERE city IS NOT NULL
)
UNION CORRESOPONING BY city
(SELECT *
FROM PropertyForRent
WHERE city IS NOT NULL
)