DATABASEchw33
2020-03-01 167浏览
- 1.DATABASE Principles, Programming, and Performance Patrick O’Neil and Elizabeth O’Neil Lectured by Dr. Lican HuangEmail:licanhuang@zist.edu.cn huang_lican@yahoo.co.ukhttp://virgo.sourceforge.net/licanhuang.htmlZhejiang Sci-Tech University Lican Huang 1
- 2.3 Basic SQL Query Language 3.7 Handling NULL values, UNKNOWN Is null Handling Null Values. Recall that a null value appears as a column value in a row when the value is either unknown (discnt for new customer) or inapplicable (employee manag er for company president). In passing, note that there is a proposal to have two different kinds of null va lues for these two cases. If insert a row (insert statement, to come) without specifying some column v alues, nulls will be placed for those values Unless column definition in create table specifies not null as for cid in Appen dix A, pg. 724 — Then the insert statement will not work. Ex 3.7.7: insert into customers (cid, cname, city) values ('c007', 'Windix', 'Dallas'); Zhejiang Sci-Tech University Lican Huang 2
- 3.3 Basic SQL Query Language 3.7 Handling NULL values, The discnt value is not specified, so will be placed as null value. Note that it is NOT usually possible w ith current products to specify null as value for discnt (OK in SQL-92 standard). A null value has IMPORTANT implications. Two following aredifferent:select count(*) from customers; select count(*) from customers where (discnt < 8 or discnt >= 8); Why? Because null values for discnt will not be selected in second statement, even though the condit ion seems to be exhaustive. A null value in any comparison expression evaluates to UNKNOWN, rather than TRUE or FALSE. In a S elect statement, only rows for which the where condition is TRUE are retrieved. (See pg. 143 for reas on for UNKNOWN.) This means that the null value of an integer type variable cannot be kept simply as some value patter n, because all patterns are taken up by real integer values. Need special FLAG byte for a column to s ee when it is null. Some older products didn't have null, represented unknown numeric values by zero and unknown cha r values by '' (null string). Obviously that doesn't have the right properties because of set functions. Note another importantproperty:the set functions IGNORE null values. If wewrite:select avg(dollars) from orders where aid = 'a01'; or select sum(dollars)/count(dollars) from orders where aid = 'a02'; and some rows have UNKNOWN dollars values, then the count, sum, and avg functions will all skip ov er those values. If the values were zero instead of null, clearly the avg would be lower. Zhejiang Sci-Tech University Lican Huang 3
- 4.3 Basic SQL Query Language 3.8 Groups of Rows in SQL 3.8.1 select pid, sum(qty) as total from orders group by pid; New GROUP BY clause. Print out as if following logic was followed:FOR EACH DISTINCT VALUE v OF pid IN orders; select pid, sum(qty) from orders where pid = v; END FOR; Zhejiang Sci-Tech University Lican Huang 4
- 5.3 Basic SQL Query Language Example 3.8.1 This has the effect of theloop:FOR EACH DISTINCT pair of values (v, w) equal to (pid, aid) in orders select pid, aid, sum(qty) from orders where pid = v and aid = w; END FOR; Zhejiang Sci-Tech University Lican Huang 5
- 6.3 Basic SQL Query Language Example 3.8.2 Example 3.8.3 Example 3.8.4 Example 3.8.5 Zhejiang Sci-Tech University Lican Huang 6
- 7.3 Basic SQL Query Language Figure 3.16 a Figure 3.16.b Figure 3.17 Figure 3.18 Figure 3.19 Figure 3.20 Zhejiang Sci-Tech University Lican Huang 7
