EMPL_NUM	NAME	AGE	REP_OFFICE	TITLE	HIRE_DATE	MANAGER	QUOTA	SALES
105	Bill Adams	37	13	Sales Rep	2006-02-12	104	$350 000.00	$367 911
109	Mary Jones	31	11	Sales Rep	2007-10-12	106	$350 000.00	$392 725
102	Sue Smith	48	21	Sales Rep	2004-12-10	108	$350 000.00	$474 050

MFR_ID	PRODUCT_ID	DESCRIPTION	PRICE	QTY_ON_HAND
REI	2A45C	Ratchet Link	$79.00	210
ACI	4100Y	Widget Remover	$2 750.00	25
QSA	Xk47	Reducer	$355.00	38

ORDER_NUM	ORDER_DATE	CUST	REP	MFR	PRODUCT	QTY	AMOUNT
112961	2007-12-17	2117	106	REI	2A44L	7	$31500
113012	2008-01-11	2111	105	ACI	41003	35	$3745
112989	2008-01-03	2101	106	FEA	114X	6	$1458

OFFICE	CITY	REGION	MGR	TARGET	SALES
22	Denver	Western	108	$300000	$186042
11	New York	Eastern	106	$575000	$692637
12	Chicago	Eastern	104	$800000	$735042

CUST_NUM	COMPANY	CUST_REP	CREDIT_LIMIT
2111	JCP Inc.	103	$50 000
2102	First Corp.	101	$65 000
2103	Acme Mtg.	105	$50 000

Assignment 1

Create a database

SALES with the shown tables in the figure above

Assignment 2

o List the sales offices with their targets and actual sales. SELECT CITY, TARGET, SALES FROM OFFICES;

o List the Eastern region sales offices with their targets and sales. SELECT CITY, TARGET, SALES FROM OFFICES WHERE REGION = 'Eastern';

o List Eastern region sales offices whose sales exceed their targets, sorted in alphabetical order by city. SELECT CITY, TARGET, SALES FROM OFFICES

WHERE REGION = 'Eastern'

AND SALES > TARGET (ORDER BY)CITY;

Assignment 3

o List the names, offices and hire dates of all salespeople. SELECT NAME, REP_OFFICE, HIRE_DATE FROM SALESREPS;

o What are the name, quota, and sales of employee number 107? SELECT NAME, QUOTA, SALES FROM SALESREPS WHERE EMPL_NUM = 107;

o List the name and hire date of anyone with sales over $500,000. SELECT NAME, HIRE_DATE FROM SALESREPS WHERE SALES > 500000.00;

Assignment 4

o Show me the offices where sales exceed target.

o Show me the name, sales, and quota of employee number 105.

o Show me the employees managed by Bob Smith (employee 104).

Assignment 5

o List each salesperson and the city and region where they work. SELECT NAME, CITY, REGION FROM SALESREPS, OFFICES WHERE REP_OFFICE = OFFICE;

o List the offices and the names and titles of their managers. SELECT CITY, NAME, TITLE FROM OFFICES, SALESREPS WHERE MGR = EMPL_NUM;