##    Brian Platz
##    CS85 Section 1643
##    Professor Geddes
##    May 5, 2012
##    Assignment: 

##        Write a SQL statement for each of the following. 
##        Save your statements in a file called premiereQuery.sql.txt.

##            retrieve data from a table
##            projection - retrieve a subset of the fields of a table
##            selection - i.e., filter the data with a WHERE clause
##            update data in a table
##            delete rows from a table
##            sort (order) the result set of a query
##            delete a table
##            delete the database
##            For each statement, describe what the statement does. For example,
##            This statement retrieves all rows from the customers table where the current balance exceeds the credit limit.

##    salesrep(salesID, lname, fname, address, comtot, comrate)
##    customer(custID, name, address, balance, climit, salesRep)
##    inventory(partID, itemDesc, count, class, whouse, cost)
##    custOrder(orderID, oDate, customer)
##    orderitem(orderNo, lineID, part, quant, quote)

USE premiere;

## this statement retrieves all information (all columns) from the customer table
SELECT * FROM customer;

## this statement retrieves the description, number in inventory, and cost of each item in inventory
SELECT itemDesc, count, cost FROM inventory;

## this statement retrieves all sales reps who receive a commission of greater than 1%.
SELECT * FROM salesrep 
	WHERE comrate > 0.01;

## this statement updates the commission rate to 4% for the salesperson with ID 13.
UPDATE salesrep 
	SET comrate = 0.04
	WHERE salesID = 13;

## this statement deletes the salesperson "Dom Deluise" from the salesrep table.
DELETE FROM customer
	WHERE name = 'Dom DeLuise';

## this statement retrieves all salesreps from the salesrep table, and sorts them by total commission received
##		in ascending order (default).
SELECT * FROM salesrep
	ORDER BY comtot;

## this statement drops the entire inventory table from the database.
DROP TABLE IF EXISTS inventory;

## this statement deletes the entire premiere database.
DROP DATABASE IF EXISTS premiere;