Question 32: What is the result of the following query?

create table shop(
	       shop_id int primary key,
	       shop_name varchar(32));
insert into shop values
	       (1,'shop one'),
	       (2,'shop two'),
	       (3,'shop three');
create table product(
               product_id int primary key,
	       product_name varchar(32),
	       product_type int);
insert into product values
               (1,'product one',1),
	       (2,'product two',1),
	       (3,'product three',2);
create table sale(
               shop_id int,
               sale_dt date,
	       product_id int,
	       type int,
	       amount decimal(10,2),
	       key ix1(shop_id, sale_dt));
insert into sale values
	       (1,'2010-01-01',1,0,100.50),(1,'2010-01-01',2,0,100.50),
	       (3,'2010-02-01',3,1,100.50),(2,'2010-02-01',3,1,100.50);
 
select v2.shop_name,v1.sale_dt,v3.product_type,sum(v1.amount)
  from sale v1, shop v2, product v3
  where v1.shop_id=v2.shop_id and v1.product_id=v3.product_id
  group by v1.shop_id,v1.sale_dt,v3.product_type
  order by 1,2,3;

 
Possible results:
 

a)
+------------+------------+--------------+----------------+
| shop_name  | sale_dt    | product_type | sum(v1.amount) |
+------------+------------+--------------+----------------+
| shop one   | 2010-01-01 |            1 |         201.00 |
| shop two   | 2010-02-01 |            2 |         100.50 |
| shop three | 2010-02-01 |            2 |         100.50 |
+------------+------------+--------------+----------------+
3 rows in set (0.00 sec)
 
b)
+------------+------------+--------------+----------------+
| shop_name  | sale_dt    | product_type | sum(v1.amount) |
+------------+------------+--------------+----------------+
| shop one   | 2010-01-01 |            1 |         201.00 |
| shop three | 2010-02-01 |            2 |         100.50 |
| shop two   | 2010-02-01 |            2 |         100.50 |
+------------+------------+--------------+----------------+
3 rows in set (0.00 sec)
 
c)
+------------+------------+--------------+----------------+
| shop_name  | sale_dt    | product_type | sum(v1.amount) |
+------------+------------+--------------+----------------+
| shop one   | 2010-01-01 |            1 |         201.00 |
| shop three | 2010-02-01 |            1 |         100.50 |
| shop two   | 2010-02-01 |            2 |         100.50 |
+------------+------------+--------------+----------------+
3 rows in set (0.02 sec)

 
[ Querying for Data (15%) - Using SELECT to Retrieve Data ]