Re: Relation subset operators

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 Received on Fri Jun 05 2009 - 14:51:40 CEST