Re: Relation subset operators
Date: Fri, 05 Jun 2009 16:56:17 GMT
Message-ID: <R6cWl.30705$PH1.25658_at_edtnps82>
cimode_at_hotmail.com wrote:
> On 3 juin, 16:23, --CELKO-- <jcelko..._at_earthlink.net> wrote:
>>>> Comments would be welcome to evaluate the usefulness of such an operator. <<
>> Just for comparison, I re-wrote the skeleton schema in SQL and brought
>> the data element names up to ISO-11179 standards. The dates also need
>> to be in ISO-8601 format, but that is not important for this
>> comparison.
>>
>> CREATE TABLE CarSales
>> (invoice_nbr INTEGER NOT NULL PRIMARY KEY,
>> vehicle_make VARCHAR(10) NOT NULL,
>> salesman_name VARCHAR(10) NOT NULL,
>> vehicle_price DECIMAL (8,2) NOT NULL,
>> vehicle_color VARCHAR(10) NOT NULL,
>> sale_date DATE DEFAULT CURRENT_TIMESTAMP NOT NULL);
>>
>>> Question 1: What are the total sales of salesmen who have sold AT LEAST (one) Blue vehicle.
>> SELECT C1.salesman_name, SUM(C1.vehicle_price) AS sales_tot
>> FROM CarSales AS C1
>> WHERE C1.salesman_name
>> IN (SELECT C2.salesman_name
>> FROM CarSales AS C2
>> WHERE C2.vehicle_color = 'Blue') ;
>>
>>> Question 2: What are the total sales of salesmen who have sold AT LEAST (one) blue vehicle AND (one) red vehicle.
>> SELECT C1.salesman_name, SUM(C1.vehicle_price) AS sales_tot
>> FROM CarSales AS C1
>> WHERE C1.salesman_name
>> IN (SELECT C2.salesman_name
>> FROM CarSales AS C2
>> WHERE C2.vehicle_color IN ( 'Blue', 'Red')
>> GROUP BY C2.salesman_name
>> HAVING COUNT(DISTINCT C2.vehicle_color) = 2);
>>
>>> Question 3: What are the total sales of salesmen who have sold AT LEAST (one) blue vehicle OR (one) red vehicle.
>> SELECT C1.salesman_name, SUM(C1.vehicle_price) AS sales_tot
>> FROM CarSales AS C1
>> WHERE C1.salesman_name
>> IN (SELECT C2.salesman_name
>> FROM CarSales AS C2
>> WHERE C2.vehicle_color IN ( 'Blue', 'Red')
>> GROUP BY C2.salesman_name
>> HAVING COUNT(DISTINCT C2.vehicle_color) > 0);
>>
>>> Question 4: What are the total sales of salesmen who have NOT sold ANY blue vehicles
>> SELECT C1.salesman_name, SUM(C1.vehicle_price) AS sales_tot
>> FROM CarSales AS C1
>> WHERE C1.salesman_name
>> NOT IN (SELECT C2.salesman_name
>> FROM CarSales AS C2
>> WHERE C2.vehicle_color = 'Blue');
>>
>>> Question 5: What are the total sales of salesmen who have NOT sold NEITHER a blue vehicle NOR a red vehicle
>> SELECT C1.salesman_name, SUM(C1.vehicle_price) AS sales_tot
>> FROM CarSales AS C1
>> WHERE C1.salesman_name
>> NOT IN (SELECT C2.salesman_name
>> FROM CarSales AS C2
>> WHERE C2.vehicle_color IN ( 'Blue', 'Red')
>> GROUP BY C2.salesman_name
>> HAVING COUNT(DISTINCT C2.vehicle_color) > 0);
>>
>> I did not test these queries and I used the IN() predicate rather than
>> EXISTS() to keep them closer to relational algebra and set notation.
>>
>> However, if the specs are changed a bit to show a zero total for the
>> disqualified salesmen, the queries could have been written with a CASE
>> expressions: Here is Q1:
>>
>> SELECT C1.salesman_name,
>> SUM( CASE WHEN C1.vehicle_color = 'Blue'
>> THEN C1.vehicle_price ELSE 0.00 END)
>> AS blue_sales_tot
>> FROM CarSales AS C1
>> GROUP BY C1.salesman_name;
>>
>> This is very simple SQL and you could nest it inside another query to
>> meet the original specs:
>>
>> SELECT salesman_name, blue_sales_tot
>> FROM ( SELECT C1.salesman_name,
>> SUM( CASE WHEN C1.vehicle_color = 'Blue'
>> THEN C1.vehicle_price ELSE 0.00 END)
>> AS blue_sales_tot
>> FROM CarSales AS C1
>> GROUP BY C1.salesman_name) AS B(salesman_name, blue_sales_tot)
>> WHERE blue_sales_tot > 0;
> > OK I will grant you that you have made an effort to formulate a > possible SQL, I voluntarily skipped. It is however unfortunate missed > the point behind the need to create a new operator. Try to formulate > for instance the following question someone brought up.... > > Question 7: What are the total sales of ALL salesmen who have sold ALL > possible colors...The SQL can become quite cumbersome. OTOH, using > the proposed operator it can simply be written as: > > CARSALE/salesman WITH color /= color >
Not that I admire people who admire SQL (I just admire people who can cope with it, assuming they were forced into that position) but I thought the SQL queries were useful, even though I didn't try them and none of them involved any of the flavours of relational division I've seen (nor did the original examples, which also confused me given that division was mentioned in the original post). Perhaps you were looking for a way to establish division as a generalization of simple restriction, I'm not sure.
The syntax also confuses me, I'm not sure whether '/' is somehow, sometimes, operating on an attribute (projection?) instead of a relation, eg., the "color /= color" part. Probably I wouldn't be able to see the point without more SQL comparisons or perhaps algebra expressions that included a "SUM" operator, then I/we could see where GROUP, DIVIDE and the aggregates come into play.
Another example to compare against division might be "total sales for each salesman who sold all purple cars"! I don't yet know whether optimizing that is easy or hard, but I think one lateral use for shorthands, beyond reducing typos', is to make common optimizations easier to recognize (though the longhand versions are still needed to 'prove' the optimizations).. Received on Fri Jun 05 2009 - 18:56:17 CEST