########################################################################
# Every thing following # to the end of line is viewed as comments by SQL
# 
# The SQL keywords in the following are capitalized here to make it easier 
#	to recognize them and understand the SQL statements.
# Note that SQL is case-insensitive so it is fine 
#	if you use lower-case letters instead.
########################################################################


########################################################################
# Use the database cs402 
########################################################################
USE cs402;

########################################################################
# The following tables should have already been created in that database
########################################################################

#CREATE TABLE salespeople
#(
#      snum INTEGER NOT NULL,
#      sname CHAR(15),
#      city CHAR (15),
#      comm NUMERIC(3,2),
#      PRIMARY KEY (snum)
#);


#CREATE TABLE customers
#(
#	cnum INTEGER not null,
#	cname CHAR(15),
#	city CHAR(15),
#	rating INTEGER,
#	snum INTEGER,
#	PRIMARY KEY (cnum)
#);


#CREATE TABLE orders
#(
#	onum INTEGER not null,
#	amot NUMERIC(8,2),
#	odate DATE,
#	cnum INTEGER,
#	snum INTEGER,
#	PRIMARY KEY (onum))
#;

SHOW TABLES;

############################################################################
# (A1). Find the comms (commission rates) of the salespeople 
# who got at least one order on ‘2000-10-06’.
############################################################################
SELECT salespeople.comm
FROM salespeople, orders
WHERE  salespeople.snum = orders.snum AND orders.odate = '2000-10-06';

############################################################################
# (A2). Do the same thing as (A1), and also explicitly rename the tables
############################################################################
SELECT X.comm
FROM salespeople AS X, orders AS Y
WHERE  X.snum = Y.snum AND Y.odate = '2000-10-06';

############################################################################
# (A3). Do the same thing as (A2) and also skip the AS's.
#	Some people prefer this style because they can conveniently think of
#	X as some record in the salespeople table and
#	Y as some record in the orders table
#
############################################################################
SELECT X.comm
FROM salespeople X, orders Y
WHERE  X.snum = Y.snum AND Y.odate = '2000-10-06';



############################################################################
# (B1). Find the sname and commission rate of every salespeople whose comm (commission rate) 
# is lower than that of some (i.e. ANY) salesperson 
# who got at least one order on ‘2000-10-06’.
#
# Version 1: using ANY and subqueries
############################################################################
SELECT S.sname, S.comm
FROM salespeople S
WHERE 
 S.comm < ANY
 ( 
  SELECT X.comm
  FROM salespeople X, orders Y
  WHERE  X.snum = Y.snum  AND  Y.odate = '2000-10-06'
 )
;


############################################################################
# (B2). Find the sname and commission rate of every salespeople whose comm (commission rate) 
# is lower than that of some (i.e. ANY) salesperson 
# who got at least one order on ‘2000-10-06’.
#
# Version 2: using EXISTS and subqueries
#	(Does there EXIST salesperson S with a lower commission rate
#	than that of any salesperson who had an order on '2000-10-06'?)
############################################################################
SELECT S.sname, S.comm
FROM salespeople S
WHERE 
 EXISTS
 ( 
  SELECT X.comm
  FROM salespeople X, orders Y
  WHERE  S.comm < X.comm  AND  X.snum = Y.snum  AND  Y.odate = '2000-10-06'
 )
;

############################################################################
# (B3). Find the sname and commission rate of every salespeople whose comm (commission rate) 
# is lower than that of some (i.e. ANY) salesperson 
# who got at least one order on ‘2000-10-06’.
#
# Version 3: without using subqueries
#	(Does there EXIST salesperson S1 with a lower commission rate
#	than some salesperson S@ who had an order on '2000-10-06'?)
############################################################################
SELECT DISTINCT S1.sname, S1.comm
FROM salespeople S1, orders O1, salespeople S2, Orders O2
WHERE 
  S1.comm < s2.comm        AND
  S1.snum = O1.snum        AND 
  S2.snum = O2.snum        AND
  O2.odate = '2000-10-06' 
;


############################################################################
# (C1). Find the sname and commission rate of every salespeople whose comm (commission rate) 
# is lower than that of every salesperson (i.e. ALL) 
# who got at least one order on ‘2000-10-06’.
#
# Version 1: using ALL and subqueries
############################################################################
SELECT S.sname, S.comm
FROM salespeople S
WHERE 
 S.comm < ALL
 ( 
  SELECT X.comm
  FROM salespeople X, orders Y
  WHERE  X.snum = Y.snum AND Y.odate = '2000-10-06'
 )
;

############################################################################
# (C2). Find the sname and commission rate of every salespeople whose comm (commission rate) 
# is lower than that of every salesperson (i.e. ALL) 
# who got at least one order on ‘2000-10-06’.
#
# Version 2: using NOT EXISTS and subqueries
#	(Find every salesperson S where there EXIST NO salesperson X
#	 who has a lower or equl commission rate than that of S
#	 and X had an order Y on '2000-10-06'?)
############################################################################
SELECT S.sname, S.comm
FROM salespeople S
WHERE 
 NOT EXISTS
 ( 
  SELECT X.comm
  FROM salespeople X, orders Y
  WHERE  S.comm >= X.comm  AND  X.snum = Y.snum  AND  Y.odate = '2000-10-06'
 )
;


############################################################################
#(D1). Find the odates when there was at least one order from the customers 
#but none of them was from the customer with the cnum 2006.
#  Version 1
############################################################################
SELECT DISTINCT X.odate
FROM orders AS X
WHERE  
 NOT EXISTS 
 (SELECT DISTINCT Y.odate
  FROM orders AS Y
  WHERE Y.cnum = 2006 AND Y.odate = X.odate
 );


############################################################################
#(D2) Some people prefers skipping the AS's from D1
############################################################################
SELECT DISTINCT X.odate
FROM orders X
WHERE  
 NOT EXISTS 
 (SELECT DISTINCT Y.odate
  FROM orders Y
  WHERE Y.cnum = 2006 AND Y.odate = X.odate
 );


############################################################################
#(D3). Find the odates when there was at least one order from the customers 
#but none of them was from the customer with the cnum 2006.
# Version 2
############################################################################
SELECT DISTINCT X.odate
FROM orders AS X
WHERE  
 0 = 
 (SELECT COUNT(DISTINCT Y.odate)
  FROM orders AS Y
  WHERE Y.cnum = 2006 AND Y.odate = X.odate
 )
;

############################################################################
#(D4) Some people prefers skipping the AS's from D3
############################################################################
SELECT DISTINCT X.odate
FROM orders X
WHERE  
 0 = 
 (SELECT COUNT(DISTINCT Y.odate)
  FROM orders Y
  WHERE Y.cnum = 2006 AND Y.odate = X.odate
 )
;


############################################################################
# (E1) Find the cnames of the customers who DID NOT have order(s) everyday
#	whenever there was an order that day.
# Version 1:
# In other words, for each of such customers, we have to determine
# there does EXIST some date
# when there were orders but NONE was from this customer.
# This portion can be accomplished by adapting example D1 (D2)
############################################################################

SELECT C.cname
FROM customers C
WHERE EXISTS 
(
 SELECT DISTINCT X.odate
 FROM orders X
 WHERE  
  NOT EXISTS 
  (SELECT DISTINCT Y.odate
   FROM orders Y
   WHERE Y.cnum = C.cnum AND Y.odate = X.odate
  )
);

############################################################################
# (E2) Find the cnames of the customers who DID NOT have order(s) everyday.
#	whenever there was an order that day.
# Version 2:
# In other words, for each of such customers, we have to determine
# there does EXIST some date
# when there were orders but NONE was from this customer.
# This portion can ALSO be accomplished by adapting example D3 (D4)
############################################################################

SELECT C.cname
FROM customers C
WHERE EXISTS 
(
 SELECT DISTINCT X.odate
 FROM orders X
 WHERE  
  0 =  
  (SELECT COUNT(DISTINCT Y.odate)
   FROM orders Y
   WHERE Y.cnum = C.cnum AND Y.odate = X.odate
  )
);



############################################################################
# (F1) Find the cnames of the customers who DID have order(s) everyday.
#	whenever there was an order that day.
# Version 1:
# In other words, for each of such customers, we have to determine
# there does NOT EXIST some date
# when there were orders but NONE was from this customer.
# This portion can be accomplished by adapting example D1 (D2)
############################################################################

SELECT C.cname
FROM customers C
WHERE NOT EXISTS 
(
 SELECT DISTINCT X.odate
 FROM orders X
 WHERE  
  NOT EXISTS 
  (SELECT DISTINCT Y.odate
   FROM orders Y
   WHERE Y.cnum = C.cnum AND Y.odate = X.odate
  )
);

############################################################################
# (F2) Find the cnames of the customers who DID have order(s) everyday.
#	whenever there was an order that day.
# Version 2:
# In other words, for each of such customers, we have to determine
# there does EXIST NOT some date
# when there were orders but NONE was from this customer.
# This portion can ALSO be accomplished by adapting example D3 (D4)
############################################################################

SELECT C.cname
FROM customers C
WHERE NOT EXISTS 
(
 SELECT DISTINCT X.odate
 FROM orders X
 WHERE  
  0 =  
  (SELECT COUNT(DISTINCT Y.odate)
   FROM orders Y
   WHERE Y.cnum = C.cnum AND Y.odate = X.odate
  )
);