########################################################################
# 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.
########################################################################


########################################################################
# Create a new blank database and use it
########################################################################
CREATE DATABASE cs402;
USE cs402;

########################################################################
# See what tables are currently in that database
########################################################################
SHOW TABLES;

########################################################################
# Create a new blank table in the current database
########################################################################
CREATE TABLE salespeople
(
      snum INTEGER NOT NULL,
      sname CHAR(15),
      city CHAR (15),
      comm NUMERIC(3,2),
      PRIMARY KEY (snum)
);


########################################################################
# Insert 5 records into the newly created table
########################################################################

INSERT INTO salespeople
VALUES (1001, 'Peel','London', 0.12);

INSERT INTO salespeople
VALUES (1002, 'Serres', 'San Jose', 0.13);

INSERT INTO salespeople
VALUES (1004, 'Motika', 'London', 0.11);

INSERT INTO salespeople
VALUES (1007, 'Rifkin', 'Barcelona', 0.15);

INSERT INTO salespeople
VALUES (1003, 'Axelrod', 'New York', 0.10);

# Check what tables are currently in that database again
SHOW TABLES;

# Ask MySQL to describe the structure (i.e. schema) of the table
DESCRIBE salespeople;

# Show all current records in this table
SELECT *
FROM salespeople;

########################################################################
# The following is a list of simple SQL queries involving a sigle table
########################################################################


########################################################################
# (I) Three simple SQL queries, 
#	each with a single relational or string operator in the where clause
########################################################################

SELECT *
FROM salespeople
WHERE city = 'London';

SELECT *
FROM salespeople
WHERE city LIKE '%on%';

SELECT *
FROM salespeople
WHERE comm >= 0.13;

SELECT * 
FROM salespeople 
WHERE comm BETWEEN 0.12 and 0.14;

########################################################################
# (II) Three simple SQL queries, 
#	each with a logical operator (AND, OR, NOT ) to combine two
#	logic conditions invovling two relational operators
#	in the where clause
########################################################################
SELECT *
FROM salespeople
WHERE city LIKE '%on%' OR comm >= 0.13;

SELECT *
FROM salespeople
WHERE city LIKE '%on%' AND comm >= 0.13;

SELECT *
FROM salespeople
WHERE NOT (city LIKE '%on%' AND comm >= 0.13);



########################################################################
# (III) Four queries to deomonstrate the use of ORDER BY and DICTINCT
########################################################################
SELECT city
FROM salespeople;

SELECT city
FROM salespeople
ORDER BY city ASC;

SELECT city
FROM salespeople
ORDER BY city DESC;

SELECT DISTINCT city
FROM salespeople;

########################################################################
# (IV) Six queries to deomonstrate the use of aggregate functions
#	such as COUNT,AVG, MAX, MIN
########################################################################
SELECT COUNT(city)
FROM salespeople;

SELECT COUNT(DISTINCT city)
FROM salespeople;

SELECT AVG(comm)
FROM salespeople;

SELECT AVG(comm)
FROM salespeople
WHERE city = 'London';

SELECT MAX(comm)
FROM salespeople;

SELECT MIN(comm)
FROM salespeople;


########################################################################
# (V) Five queries to deomonstrate the use of aggregate functions
#	together with GROUP BY and HAVING
########################################################################

SELECT city, AVG(comm)
FROM salespeople
GROUP BY city;

SELECT city, AVG(comm)
FROM salespeople
GROUP BY city ASC;

SELECT city, AVG(comm)
FROM salespeople
GROUP BY city DESC;

SELECT city, AVG(comm)
FROM salespeople
GROUP BY city ASC
HAVING AVG(comm) < 0.12;

SELECT city, AVG(comm)
FROM salespeople
WHERE comm > 0.11
GROUP BY city ASC;