HW 3 data

HW2GardenGlory.accdb

Home >Computer Science homework help >HW 3 data

EmployeeID	LastName	FirstName	CellPhone	ExperienceLevel	Apprenticeship
1	Smith	Sam	206-254-1234	Master	Completed
2	Evanston	John	206-254-2345	Senior	In process
3	Murray	Dale	206-254-3456	Junior	Not started
4	Murphy	Jerry	585-545-8765	Master	Completed
5	Fontaine	Joan	206-254-4567	Senior	In process

ServiceID	ServiceDescription	CostPerHour
1	Mow Lawn	25
2	Plant Annuals	25
3	Weed Garden	30
4	Trim Hedge	45
5	Prune Small Tree	60
6	Trim Medium Tree	100
7	Trim Large Tree	125

PropertyID	PropertyName	PropertyType	Street	City	State	Zip	OwnerID
1	Eastlake Building	Office	123 Eastlake	Seattle	WA	98119	2
2	Elm St Apts	Apartments	4 East Elm	Lynwood	WA	98223	1
3	Jefferson Hill	Office	42 West 7th St	Bellevue	WA	98007	2
4	Lake View Apts	Apartments	1265 32nd Avenue	Redmond	WA	98052	3
5	Kodak Heights Apts	Apartments	65 32nd Avenue	Redmond	WA	98052	4
6	Jones House	Private Residence	1456 48th St	Bellevue	WA	98007	1
7	Douglas House	Private Residence	1567 51st St	Bellevue	WA	98007	3
8	Samuels House	Private Residence	567 151st St	Redmond	WA	98052	5

OwnerID	OwnerName	OwnerEmail	OwnerType
1	Mary Jones	[email protected]	Individual
2	DT Enterprises	[email protected]	Corporation
3	Sam Douglas	[email protected]	Individual
4	UNY Enterprises	[email protected]	Corporation
5	Doug Samuels	[email protected]	Individual
7	Mary Jones	[email protected]	Individual

PropertyServiceID	PropertyID	ServiceID	ServiceDate	EmployeeID	HoursWorked
1	1	2	5/5/19	1	4.5
2	3	2	5/8/19	3	4.5
3	2	1	5/8/19	2	2.75
4	6	1	5/10/19	5	2.5
5	5	4	5/12/19	4	7.5
6	8	1	5/15/19	4	2.75
7	4	4	5/19/19	1	1.0
8	7	1	5/21/19	2	2.5
9	6	3	6/3/19	5	2.5
10	5	7	6/8/19	4	10.5
11	8	3	6/12/19	4	2.75
12	4	5	6/15/19	1	5.0
13	7	3	6/19/19	2	4.0

CREATE TABLE EMPLOYEE( EmployeeID Int NOT NULL, LastName Char(25) NOT NULL, FirstName Char(25) NOT NULL, CellPhone Char(12) NOT NULL, ExperienceLevel Char(15) NOT NULL, CONSTRAINT EMPLOYEE_PK PRIMARY KEY(EmployeeID) );

CREATE TABLE GG_SERVICE( ServiceID Int NOT NULL, ServiceDescription Char(100) NOT NULL, CostPerHour Numeric NULL, CONSTRAINT GG_SERVICE_PK PRIMARY KEY(ServiceID) );

CREATE TABLE OWNED_PROPERTY( PropertyID Int NOT NULL, PropertyName Char(50) NOT NULL, PropertyType Char(50) NOT NULL, Street Char(35) NOT NULL, City Char(35) NOT NULL, State Char(2) NOT NULL, Zip Char(10) NOT NULL, OwnerID Int NOT NULL, CONSTRAINT OWNED_PROPERTY_PK PRIMARY KEY(PropertyID), CONSTRAINT PROPERTY_OWNER_FK FOREIGN KEY (OwnerID) REFERENCES OWNER(OwnerID) );

CREATE TABLE OWNER( OwnerID Int NOT NULL, OwnerName Char(50) NOT NULL, OwerEmail VarChar(100) NULL, OwnerType Char(12) NULL, CONSTRAINT OWNER_PK PRIMARY KEY(OwnerID) );

CREATE TABLE PROPERTY_SERVICE( PropertyServiceID Int NOT NULL, PropertyID Int NOT NULL, ServiceID Int NOT NULL, EmployeeID Int NOT NULL, ServiceDate DateTime NOT NULL, HoursWorked Numeric NULL, CONSTRAINT PROP_SERVICE_PK PRIMARY KEY(PropertyServiceID), CONSTRAINT PROP_SERVICE_PROP_FK FOREIGN KEY (PropertyID) REFERENCES OWNED_PROPERTY(PropertyID), CONSTRAINT PROP_SERVICE_SERVICE_FK FOREIGN KEY (ServiceID) REFERENCES GG_SERVICE(ServiceID), CONSTRAINT PROP_SERVICE_EMP_FK FOREIGN KEY (EmployeeID) REFERENCES EMPLOYEE(EmployeeID) );

SELECT * FROM EMPLOYEE;

SELECT * FROM GG_SERVICE;

SELECT * FROM OWNER;

SELECT * FROM OWNED_PROPERTY;

SELECT * FROM PROPERTY_SERVICE;

SELECT EMPLOYEE.LastName, EMPLOYEE.FirstName, EMPLOYEE.CellPhone FROM EMPLOYEE WHERE (((EMPLOYEE.ExperienceLevel)='Master'));

SELECT LastName, FirstName, CellPhone FROM EMPLOYEE WHERE ExperienceLevel='Master' And FirstName Like 'J*';

SELECT LastName, FirstName, CellPhone FROM EMPLOYEE WHERE EmployeeID IN (SELECT EmployeeID FROM PROPERTY_SERVICE WHERE PropertyID IN (SELECT PropertyID FROM OWNED_PROPERTY WHERE City = 'Seattle'));

SELECT E.LastName, E.FirstName, E.CellPhone FROM (EMPLOYEE AS E INNER JOIN PROPERTY_SERVICE AS PS ON E.EmployeeID = PS.EmployeeID) INNER JOIN OWNED_PROPERTY AS OP ON PS.PropertyID = OP.PropertyID WHERE OP.City = 'Seattle';

SELECT LastName, FirstName, CellPhone FROM EMPLOYEE WHERE EmployeeID IN (SELECT EmployeeID FROM PROPERTY_SERVICE WHERE PropertyID IN (SELECT PropertyID FROM OWNED_PROPERTY WHERE OwnerID IN (SELECT OwnerID FROM OWNER WHERE OwnerType = 'Corporation')));

SELECT DISTINCT E.LastName, E.FirstName, E.CellPhone FROM ((EMPLOYEE AS E INNER JOIN PROPERTY_SERVICE AS PS ON E.EmployeeID = PS.EmployeeID) INNER JOIN OWNED_PROPERTY AS OP ON PS.PropertyID = OP.PropertyID) INNER JOIN OWNER AS O ON OP.OwnerID = O.OwnerID WHERE O.OwnerType = 'Corporation';

SELECT LastName, FirstName, CellPhone, SUM(HoursWorked) AS TotalHoursWorked FROM EMPLOYEE INNER JOIN PROPERTY_SERVICE ON EMPLOYEE.EmployeeID = PROPERTY_SERVICE.EmployeeID GROUP BY LastName, FirstName, CellPhone;

SELECT ExperienceLevel, SUM(HoursWorked) AS TotalHoursWorked FROM EMPLOYEE INNER JOIN PROPERTY_SERVICE ON EMPLOYEE.EmployeeID = PROPERTY_SERVICE.EmployeeID GROUP BY ExperienceLevel ORDER BY ExperienceLevel DESC;

SELECT OwnerType, SUM(HoursWorked) AS TotalHoursWorked FROM ((EMPLOYEE INNER JOIN PROPERTY_SERVICE ON EMPLOYEE.EmployeeID = PROPERTY_SERVICE.EmployeeID) INNER JOIN OWNED_PROPERTY ON PROPERTY_SERVICE.PropertyID = OWNED_PROPERTY.PropertyID) INNER JOIN OWNER ON OWNED_PROPERTY.OwnerID = OWNER.OwnerID WHERE ExperienceLevel <> 'Junior' GROUP BY OwnerType;

ALTER TABLE EMPLOYEE ADD Supervisor Int NULL;

ALTER TABLE EMPLOYEE ADD CONSTRAINT EMP_EMP_FK FOREIGN KEY (Supervisor) REFERENCES EMPLOYEE (EmployeeID);

ALTER TABLE EMPLOYEE ADD Apprenticeship Char(20) NULL;

ALTER TABLE EMPLOYEE ALTER COLUMN Apprenticeship Varchar(20) NOT NULL;

SELECT E.FirstName AS EmployeeFirstName, E.LastName AS EmployeeLastName, S.FirstName AS SupervisorFirstName, S.LastName AS SupervisorLastName FROM EMPLOYEE AS E INNER JOIN EMPLOYEE AS S ON E.Supervisor = S.EmployeeID;

ALTER TABLE TOOL_USE ADD EmployeeID Int NULL;

ALTER TABLE TOOL_USE ALTER COLUMN EmployeeID Int NOT NULL;

ALTER TABLE TOOL_USE DROP COLUMN UsedBy;

ALTER TABLE TOOL_USE ADD CONSTRAINT TU_TOOL_FK FOREIGN KEY (ToolID) REFERENCES TOOLS (ToolID);

ALTER TABLE TOOL_USE ADD CONSTRAINT TU_EMP_FK FOREIGN KEY (EmployeeID) REFERENCES EMPLOYEE (EmployeeID);

SELECT * FROM OWNER;

SELECT * FROM EMPLOYEE;

SELECT * FROM GG_SERVICE;

SELECT * FROM OWNED_PROPERTY;

SELECT * FROM PROPERTY_SERVICE;