Re: Relation subset operators

From: --CELKO-- <jcelko212_at_earthlink.net>
Date: Wed, 3 Jun 2009 07:23:19 -0700 (PDT)
Message-ID: <902889ae-7034-4a6a-a619-a911feb0957a_at_o20g2000vbh.googlegroups.com>

>> 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; Received on Wed Jun 03 2009 - 16:23:19 CEST