########################################################################
# 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 already be 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;

############################################################################
# (I) Four simple SQL queries, 
#	to show the effects of cross-product operations on tables
############################################################################

# Cross product of salespeople, customers
SELECT *
FROM salespeople, customers;

# count the number of records in salespeople
SELECT count(*)
FROM salespeople;

# count the number of records in customers
SELECT count(*)
FROM customers;

# count the number of records in the cross product of salespeople, customers
SELECT count(*)
FROM salespeople, customers;


############################################################################
# (II) Six more simple SQL queries, 
#	to show the effects of cross-product operations on tables
############################################################################

# Cross product of orders, customers
SELECT *
FROM orders, customers;

# count the number of records in orders
SELECT count(*)
FROM orders;

# count the number of records in customers
SELECT count(*)
FROM customers;

# count the number of records in the cross product of orders, customers
SELECT count(*)
FROM orders, customers;


# Cross product of salespeople, orders, customers
SELECT * 
FROM salespeople, orders, customers;


# count the number of records in the cross product of salespeople orders, customers
SELECT count(*)
FROM salespeople, orders, customers;

############################################################################
# (II) Five more simple SQL queries to show how 
#	by using the SQL WHERE clause
#		you can apply the 'selection' operaton 
#		on top of the cross-product operation on tables
#		to filer out unwanted combination of records
#	by listing the column names in the SQL SELECT clause
#		you can apply the 'projection' operaton 
#		to keep only the needed columns.
############################################################################

# Cross product of salespeople, customers
SELECT *
FROM salespeople, customers;

# Only want salesperson-customer pairs 
#	where both the salesperson and the customer
#	have the same snum value
SELECT *
FROM salespeople, customers
WHERE salespeople.snum = customers.snum;

# Only want the names of salespeople and customers 
# out of the salesperson-customer pairs 
#	where both the salesperson and the customer
#	have the same snum value
SELECT salespeople.sname, customers.cname
FROM salespeople, customers
WHERE salespeople.snum = customers.snum;


# Only want the names of customers 
# out of the salesperson-customer pairs 
#	where both the salesperson and the customer
#	have the same snum value AND the salesperon's commission > 0.14
SELECT customers.cname
FROM salespeople, customers
WHERE salespeople.snum = customers.snum
      AND salespeople.comm > 0.14;


# Only want the names of salespeople 
# out of the salesperson-customer pairs 
#	where both the salesperson and the customer
#	have the same snum value AND the customer's rating > 200
SELECT salespeople.sname
FROM salespeople, customers
WHERE salespeople.snum = customers.snum
      AND customers.rating > 200;