/*  Jim Gear
 POS410    ** Week 3 Individual Assignment **
 July 31, 2008
 Description of Script: This script sets up a working database to be used
 in the coming weeks to build and test SQL statements.
*/




USE Kudler

/*3.1 Inserting additional information into the Employee Table.
	  Author: Jim Gear
*/

INSERT INTO Employee
VALUES('McNamara','Juanita','923 Parkway','Roway','CA',
        '614','555-0206','Accounting Clerk','10/29/1999','$12.75','F',
        'Hispanic','32')
        
INSERT INTO Employee
VALUES('Nguyen','Meredith','10583 Arenas St.','LaJolla','CA',
        '619','555-0102','Computer Support Specialist','9/27/1998','$21.50','M',
        'Caucasian','25')
        
INSERT INTO Employee
VALUES('Stephens','Harvey','7863 High Bluff Drive','LaJolla','CA',
        '619','555-0123','Dir. of Fin. & Acct.','3/1/1998','$75,000.00','M',
        'Caucasian','51')
        
INSERT INTO Employee
VALUES('Vu','Matthew','981 Torrey Pines Road','LaJolla','CA',
        '619','555-0138','Computer Support Specialist','8/16/2000','$18.50','M',
        'Asian','26')
        
INSERT INTO Employee
VALUES('Avery','Ledonna','198 Governor Dr.','Del Mar','CA',
        '619','555-0135','Asst.-Bakery & Pastry','3/28/2003','$10.50','F',
        'African American','23')
        
INSERT INTO Employee
VALUES('Drophos','Craig','','Selano Beach','CA',
        '619','555-0202','Assistant Manager','6/15/2000','$51,000.00','M',
        'Caucasian','32')
        
INSERT INTO Employee
VALUES('Meier','Elaine','9703 Dranis Lane','Del Mar','CA',
        '858','555-0112','Cashier','9/10/2000','$10.25','F',
        'Asian','51')
        
INSERT INTO Employee
VALUES('Quillian','Stanley','98542 Wandering Rd. Apt 2-B','Del Mar','CA',
        '760','555-0198','Asst-Butchers & Seafood Specialist','12/16/1999','$11.50','M',
        'American Indian','29')
        
INSERT INTO Employee
VALUES('Tyink','Thomas','87592 Pacific Heights Blvd','Del Mar','CA',
        '858','555-0159','Asst.-Bakery & Pastry','5/1/2001','$9.50','M',
        'African American','32')
        
INSERT INTO Employee
VALUES('Vance','Brent','927 Cynthia Lane','Roway','CA',
        '858','555-0147','Bagger-30 hours/wk','3/29/2001','$6.75','M',
        'Caucasian','22')
        
/*3.2 Checking that all the information is included.
      Author: Jim Gear
 */
      
 SELECT * FROM Employee
 
 SELECT * FROM Job_title
 
 
 /*3.3 A SQL Query that joins the two tables and uses BETWEEN
       to restrict record selection. Salary restricts the data.
       Author: Jim Gear 
 */
  SELECT *
  FROM Employee 
  INNER JOIN Job_title 
  ON Employee.Job_title = Job_title.Job_title
  WHERE Wage BETWEEN 6.75 and 10.50
  
  
  /*3.4 Joins two tables using BETWEEN to restrict record selection.
        Hire dates restricts the data. 
        Author: Jim Gear
  */
  SELECT *
  FROM Employee
  INNER JOIN Job_title
  ON Employee.Job_title = Job_title.Job_title
  WHERE Hire_date BETWEEN 10-29-1999  and 3-28-2003
  
  
  /*3.5 Joins two tables using LIKE. Telephone are codes restricts the data.
        Author: Jim Gear
  */
  SELECT *
  FROM Employee
  INNER JOIN Job_title
  ON Employee.Job_title = Job_title.Job_title
  WHERE Telephone_area_code LIKE '61%'
  
  
  /*3.6 Joins two tables and uses LIKE to restrict record selection.
        Finding all people in their 20's.
        Author: Jim Gear
  */
  SELECT *
  FROM Employee
  INNER JOIN Job_title
  ON Employee.Job_title = Job_title.Job_title
  WHERE Age LIKE '2%'
  
  
  /*3.7 using UNION with the two tables
        Author: Jim Gear
  */
  SELECT First_name, Last_name, City FROM Employee
  UNION ALL
  SELECT Job_title, EEO_1_Classification, Job_Description FROM Job_title
  
  /*3.8 Average wage displayed grouped by job title.
        Author: Jim Gear
  */
  SELECT Job_title, AVG(Wage)
  FROM Employee
  WHERE  Wage BETWEEN 6.75 AND 21.50 
  GROUP BY Job_title
  
  --3.9 AND AT THIS POINT I GIVE UP. NOT ENOUGH TIME TO FIGURE OUT THE GROUPING STUFF
  
  /*SELECT Wage
  FROM Employee
  INNER JOIN EEO_1_Classification
  ON Employee.Wage=Job_title.Job_title*/