if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Employee]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Employee]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Job_Title]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Job_Title]
GO

CREATE TABLE Job_Title
(
[Job_Title_ID] VARCHAR(5)  PRIMARY KEY,
[EEO-1 Classification] VARCHAR(100) NOT NULL,
[Job_Title] VARCHAR(100) NOT NULL,	
[Job Description]  VARCHAR(200) NOT NULL,
[Exempt / Non-Exempt Status] VARCHAR(10) NOT NULL		
 )
GO

CREATE TABLE Employee
(
[Employee_ID] INT IDENTITY(1, 1) PRIMARY KEY,
[Last_Name] VARCHAR(30) NOT NULL,
[First_Name] VARCHAR(30) NOT NULL,        
[Address] VARCHAR(100)	 NOT NULL,	
[City] VARCHAR(50) NOT NULL,
[State] VARCHAR(2) NOT NULL,
[Telephone_Area_Code] VARCHAR(5) NOT NULL,
[Telephone_Number] VARCHAR(15)	NOT NULL,
[EEO-1 Classification] VARCHAR(100) NOT NULL,
[Hire_Date] DATETIME NOT NULL,
[Salary] DECIMAL(10,2),    
[Gender] VARCHAR(6)	NOT NULL,
[Age] INT  NOT NULL,
[Job_Title_ID] VARCHAR(5) CONSTRAINT FK_Employee_Job_Title FOREIGN KEY REFERENCES Job_Title(Job_Title_ID)
)
GO



INSERT INTO Employee ([Last_Name],[First_Name],[Address],[City],[State],[Telephone_Area_Code],[Telephone_Number],[EEO-1 Classification],[Hire_Date],[Salary],[Gender],[Age],[Job_Title_ID]) 
VALUES
('Edelman','Glenn','175 Bishops Lane','La_Jolla','CA','619','555-0199','Sales Workers','2003-10-07',21500.75,'Male',64,'084')

INSERT INTO Employee ([Last_Name],[First_Name],[Address],[City],[State],[Telephone_Area_Code],[Telephone_Number],[EEO-1 Classification],[Hire_Date],[Salary],[Gender],[Age],[Job_Title_ID]) 
VALUES
('McMullen','Eric','763 Church St','Lemon Grove','CA','619','555-0135','Sales Workers','2002-11-01',13500.00,'Male',20,'084')

INSERT INTO Employee ([Last_Name],[First_Name],[Address],[City],[State],[Telephone_Area_Code],[Telephone_Number],[EEO-1 Classification],[Hire_Date],[Salary],[Gender],[Age],[Job_Title_ID]) 
VALUES
('Slentz','Raj','123 Torrey Dr.','North Clairmont','CA','619','555-0123','Officials & Managers','2000-07-01',48000.00,'Male',34,'016')  

INSERT INTO Employee ([Last_Name],[First_Name],[Address],[City],[State],[Telephone_Area_Code],[Telephone_Number],[EEO-1 Classification],[Hire_Date],[Salary],[Gender],[Age],[Job_Title_ID]) 
VALUES
('Broun','Erin','2045 Parkway Apt.2B','Encinitas','CA','760','555-0100','Sales Workers','2003-03-12',10530.00,'Female',24,'053')

INSERT INTO Employee ([Last_Name],[First_Name],[Address],[City],[State],[Telephone_Area_Code],[Telephone_Number],[EEO-1 Classification],[Hire_Date],[Salary],[Gender],[Age],[Job_Title_ID]) 
VALUES
('Carpenter','Donald','927 Second ST.', 'Encinitas','CA','619','555-0154','Office Clerical','2003-11-01', 15000.00,'Male',18,'071')

INSERT INTO Employee ([Last_Name],[First_Name],[Address],[City],[State],[Telephone_Area_Code],[Telephone_Number],[EEO-1 Classification],[Hire_Date],[Salary],[Gender],[Age],[Job_Title_ID]) 
VALUES
('Esquivez','David','10983 N. Coast Hwy Apt 902','Encinitas','CA','760','555-0108','Operatives','2003-07-25',18500.00,'Male',25,'038') 

INSERT INTO Employee ([Last_Name],[First_Name],[Address],[City],[State],[Telephone_Area_Code],[Telephone_Number],[EEO-1 Classification],[Hire_Date],[Salary],[Gender],[Age],[Job_Title_ID]) 
VALUES
('Sharp','Nancy','10793 Monteciono Rd','Ramona','CA','858','555-0135','Sales Workers','2003-07-12',21000.00,'Female',24,'053') 

GO




This is the Administrative and Del Mar Employee data:



INSERT INTO Employee ([Last_Name],[First_Name],[Address],[City],[State],[Telephone_Area_Code],[Telephone_Number],[EEO-1 Classification],[Hire_Date],[Salary],[Gender],[Age]) 
VALUES
('McNamara','Juanita','923 Parkway','Loway','CA','619','555-0200','Office Clerical','1999-10-29',25500.00,'Female',32)

INSERT INTO Employee ([Last_Name],[First_Name],[Address],[City],[State],[Telephone_Area_Code],[Telephone_Number],[EEO-1 Classification],[Hire_Date],[Salary],[Gender],[Age]) 
VALUES
('Stephens','Harvey','7863 High Bluff Drive','La Jolla','CA','619','555-0123','Officials & Managers','1998-03-01','75000.00','Male',51)

INSERT INTO Employee ([Last_Name],[First_Name],[Address],[City],[State],[Telephone_Area_Code],[Telephone_Number],[EEO-1 Classification],[Hire_Date],[Salary],[Gender],[Age]) 
VALUES
('Vu','Matthew','981 Torrey Pines Road.','La Jolla','CA','619','555-0138','Technician','2000-08-16',37000.00,'Male',26)  

INSERT INTO Employee ([Last_Name],[First_Name],[Address],[City],[State],[Telephone_Area_Code],[Telephone_Number],[EEO-1 Classification],[Hire_Date],[Salary],[Gender],[Age]) 
VALUES
('Nguyen','Meredith','10583 Arenas St','La Jolla','CA','619','555-0102','Technician','1998-09-27',43000.00,'Female',25)

INSERT INTO Employee ([Last_Name],[First_Name],[Address],[City],[State],[Telephone_Area_Code],[Telephone_Number],[EEO-1 Classification],[Hire_Date],[Salary],[Gender],[Age]) 
VALUES
('Avery','Ledonna','198 Governor Dr.', 'Del Mar','CA','619','555-0135','Craft Workers','2003-03-28', 21000.00,'Female',23)

INSERT INTO Employee ([Last_Name],[First_Name],[Address],[City],[State],[Telephone_Area_Code],[Telephone_Number],[EEO-1 Classification],[Hire_Date],[Salary],[Gender],[Age]) 
VALUES
('Quillian','Stanley','98542 Wandering Road Apt 2B','Del Mar','CA','760','555-0198','Operatives','1999-12-16',23000.00,'Male',29) 

INSERT INTO Employee ([Last_Name],[First_Name],[Address],[City],[State],[Telephone_Area_Code],[Telephone_Number],[EEO-1 Classification],[Hire_Date],[Salary],[Gender],[Age]) 
VALUES
('Drohos','Craig','Selano Beach','Del Mar','CA','619','555-0202','Officials & Managers','2000-06-15',51000.00,'Male',32) 

INSERT INTO Employee ([Last_Name],[First_Name],[Address],[City],[State],[Telephone_Area_Code],[Telephone_Number],[EEO-1 Classification],[Hire_Date],[Salary],[Gender],[Age]) 
VALUES
('Tyink','Thomas','87592 Pacific Heights BLVD','Del Mar','CA','858','555-0159','Craft Workers','2001-05-01',19000.00,'Male',32) 

INSERT INTO Employee ([Last_Name],[First_Name],[Address],[City],[State],[Telephone_Area_Code],[Telephone_Number],[EEO-1 Classification],[Hire_Date],[Salary],[Gender],[Age]) 
VALUES
('Meier','Elaine','9703 Orchid Lane','Del Mar','CA','858','555-0112','Sales Workers','2000-09-10',20500.00,'Female',51) 

INSERT INTO Employee ([Last_Name],[First_Name],[Address],[City],[State],[Telephone_Area_Code],[Telephone_Number],[EEO-1 Classification],[Hire_Date],[Salary],[Gender],[Age]) 
VALUES
('Vance','Brent','927 Cynthia Lane','Stowaway','CA','858','555-0147','Sales Workers','2001-03-29',10530.00,'Male',22) 
GO



--Write a SQL query that joins the two tables in the database and uses BETWEEN to restrict record selection. (Use salary to restrict the data.)

select * from Employee join Job_Title on Employee.Job_Title_ID = Job_Title.Job_Title_ID where Salary between 15000 and 25000

--Write a SQL query that joins the two tables in the database and uses BETWEEN to restrict record selection. (Use hire dates to restrict the data.)

select * from Employee join Job_Title on Employee.Job_Title_ID = Job_Title.Job_Title_ID where Hire_Date between '2003-01-31' and '2003-12-31'

--Write a SQL query that joins the two tables in the database and uses LIKE to restrict record selection. (Use telephone area codes to restrict data.)

select * from Employee join Job_Title on Employee.Job_Title_ID = Job_Title.Job_Title_ID where Telephone_Area_Code like '6%'

--Write a SQL query that joins the two tables in the database and uses LIKE to restrict record selection. (Use age to restrict data.)

select * from Employee join Job_Title on Employee.Job_Title_ID = Job_Title.Job_Title_ID where Age like '2%'

--Write a SQL query that uses UNION of the two tables to produce a third table

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[third_table]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[third_table]
GO
select * into [third_table] from 
(select [EEO-1 Classification],Job_Title_ID from Employee 
union 
select [EEO-1 Classification],Job_Title_ID from Job_Title) A




--Group employees by job classification: Select the employees average salary and group them by EEO-1 Classification

SELECT [EEO-1 Classification], AVG(Salary) [Average Salary] FROM Employee GROUP BY [EEO-1 Classification]

--Group employees by salary: Select the employee total (SUM) of the salaries and group them by salary.

SELECT Salary,SUM(Salary) TotalSalary FROM Employee GROUP BY Salary

--Group employees by salary within their job classification: Select the count of employee and group them by salary within their EEO-1 Classification

SELECT Salary,[EEO-1 Classification],COUNT(Employee_ID) CountOfEmployee FROM Employee GROUP BY Salary,[EEO-1 Classification]

--Select the count of employee and group them by salary within job titles that are grouped into exempt and non-exempt

SELECT Employee.Salary, Job_Title.[Exempt / Non-Exempt Status], COUNT(Employee.Employee_ID) CountOfEmployee from Employee INNER JOIN Job_Title ON Employee.Job_Title_ID = Job_Title.Job_Title_ID GROUP BY Employee.Salary,Job_Title.[Exempt / Non-Exempt Status]



select * from Employee 
select * from Job_Title