Question15

Question 15

Determine what the following does and state it in English appended to your text file:

    select city from customers where discnt >=all
        (select discnt from customers where city = �Duluth�)
    union
        select city from agents where percent >any
                (select percent from agents where city like �N%�);

JK's attempt:

Result

Duluth Tokyo New York

Explanation

The first table expression:

**select discnt from customers where city = �Duluth�**

returns the set (A) of customers that are in Duluth. The outer Select then returns the names of cities where customers are based and whose discount entitlement is greater than every value in set A.

The first table expression in the second part:

**(select percent from agents where city like �N%�)**

returns the set (B) percentages for those agents that are based in cities beginning with "N". The outer select then returns the names of the cities where agents are based and have a percentage greater than any of the values in set B.

Finally the Union brings together the two sets, by creating a relation that contains all rows that appear in either or both sets.

In plain English

-Get the names of cities where customers are based whose discount entitlement is greater than that of every other customer from Duluth as well as the names of those cities where agents are based and who earn a higher percentage than any agents that come from Newark.

john_knight (Sep 16, 2001 6:45 pm; Comment #1)

A note of caution from Date for native English speakers:

The fact is, s are seriously error prone. A very natural English formulation of the query would use the word "any" in place of "every", which would easily lead to the incorrect use of >any instead of >all.

john_knight (Sep 16, 2001 6:57 pm; Comment #2)

Oops, sorry - forgot about the format commands taking over!

A note of caution from Date for native English speakers:

The fact is, ALL or ANY condition's are seriously error prone. A very natural English formulation of the query would use the word "any" in place of "every", which would easily lead to the incorrect use of >ANYinstead? of >ALL.

mj (Sep 17, 2001 7:26 pm; Comment #3)

I think we should use 5 letters to descibe the 5 sets involved in this select query:

• A for the first inner select (Duluth customer discounts),
• B for the first outer select (customer cities with greater discounts),
• C for the second inner select (N city agent percentages),
• D for the second outer select (agent cities with greater percentages),
• E for the Uniion of sets B and D.

Note that your "plain English" description should probably use "any agents that come from cities starting with 'N'".