資料庫知識點

1. Single-relation queries
2. Multi-relation queries
3. Subqueries
4. Grouping and Aggregation

（1）SELECT - FROM - WHERE statements 

　　SELECT ... (desired attributes)

　　From ... （one or more tables）

　　WHERE ...（ condition about tuples of the tables）

EX: Using Beers(name, manf), what beers are made by Busch?

SELECT name,

FROM Beers,

WHERE manf = 'Busch'

（2）When there is one relation in the FROM clause, SELECT * clause stands for “all attributes of this relation.”

（3）If you want the result to have different attribute names, use “AS <new name>” to rename an attribute.

EX:Example based on Beers(name, manf):

SELECT name AS beername, manf
FROM  Beers
WHERE manf = ‘Busch’

（4）SQL allows duplicates in relations as well as in query results.

       To force the elimination of duplicates, insert the keyword distinct  after select.

        Find the names of all branches in the loan relations, and remove duplicates

select distinct branch_name
from loan 

  The keyword all specifies that duplicates not be removed.          

select all branch_name
from loan

　(5)Any expression that makes sense can appear as an element of a SELECT clause.

SELECT bar, beer,  price * 6 AS priceInYuan

FROM Sells;

（6）function 查詢全體學生的姓名、出生年份和所有系，要求用小寫字母表示所有系名。

SELECT Sname，2006-Sage AS 'Year of Birth: ' ，

LOWER(Sdept)

FROM Student；

(7)What we can use in select clause :

      expressions 

      constants 

      functions 

      Attribute alias

（8）What you can use in WHERE: 

            attribute names of the relation(s) used in the FROM.

            comparison operators:  =, <>, <, >, <=, >=, between, in           

            apply arithmetic operations:  stockprice*2

            operations on strings (e.g., “||”  for concatenation).

            Lexicographic order on strings.

            Pattern matching:    s LIKE p

            Special stuff for comparing dates and times. 

（9）Range comparison: between

 查詢年齡在20~23歲（包括20歲和23歲）之間的學生的姓名、系別和年齡。

SELECT Sname，Sdept，Sage

FROM   Student

WHERE Sage BETWEEN 20 AND 23；

（10）Set operator: in

使用謂詞： IN <值表>,  NOT IN <值表>                

<值表>：  用逗號分隔的一組取值

[例]查詢信息系（IS）、數學系（MA）和電腦科學系（CS）學生的姓名和性別。

SELECT Sname，Ssex

FROM  Student

WHERE Sdept IN ( 'IS'，'MA'，'CS' );

 （11）Patterns

select distinct customer_name    
from    borrower        
order by customer_name

We may specify desc for descending order or asc for ascending order, for each attribute; ascending order is the default.

[例]  查詢全體學生情況，查詢結果按所在系的系號升序排列，同一系中的學生按年齡降序排列。

SELECT  *
FROM  Student
ORDER BY Sdept，Sage DESC； 

（16）Null Values

Three-Valued Logic

TRUE AND (FALSE OR NOT(UNKNOWN)) = MIN(1, MAX(0, (1 - ½ ))) = MIN(1, MAX(0, ½ ) = MIN(1, ½ ) = ½.

（17）If x=Null then 4*(3-x)/7 is still NULL

 （18）Unexpected behavior:

SELECT *

FROM   Person

WHERE  age < 25  OR  age >= 2

Some Persons are not included !

Can test for NULL explicitly:

SELECT *
FROM     Person
WHERE  age < 25  OR  age >= 25 OR age IS NULL

Now it includes all Persons

 （20）Aggregations

求最大值 MAX（[DISTINCT|ALL] <列名>） 

求最小值 MIN（[DISTINCT|ALL] <列名>） 

 EX:From Sells(bar, beer, price), find the average price of Bud:

SELECT AVG(price)

FROM Sells

WHERE beer = ‘Bud’;

（21）Eliminating Duplicates in an Aggregation

SELECT COUNT(DISTINCT price)
FROM Sells
WHERE beer = ‘Bud’;

（22）NULL’s Ignored in Aggregation

SELECT beer, AVG(price)
FROM Sells
GROUP BY beer;

（24）Restriction on SELECT Lists With Aggregation

SELECT bar, MIN(price)

FROM Sells

WHERE beer = ‘Bud’;