Question

In the table SERVICE there is an EmployeeNumber attribute. This number is a secondary key in the SERVICE table but is a primary key in the TECHNICIAN table. Create a query that shows a list of service types, the date (can be just the year) the service was performed, and the name of the technician that performed the service. Order by employee name and then by date per employee name.

CREATE TABLE HOUSE
(HouseAddress CHAR(50),
HouseOwner CHAR(30),
Insurance CHAR(40));

INSERT INTO HOUSE
VALUES ('285 Westport Rd', 'John', 'Yes'),
('213 Longboard Ln', 'Josh', 'Yes'),
('321 Surf Rd', 'Marisol', 'No');   

CREATE TABLE HEATINGUNIT
(HeatingUnitID INT,
UnitName CHAR(30),
UnitType CHAR(40),
Manufactory CHAR(40),
DateOfBuilt Date,
Capacity INT,
HouseAddress CHAR(50));
  
INSERT INTO HEATINGUNIT
VALUES (4, 'Large', 'Solar', 'GE', NULL, 3000, '213 Longboard Lne'),
(3, 'Small', 'Electric', 'GE', NULL, 6000, '213 Longboard Ln'),
(1, 'Small', 'Gas', 'LG', NULL, 2000, '285 Westport Rd'),
(2, 'Large', 'Solar', 'GE', NULL, 3500, '285 Westport Rd'),
(6, 'Medium', 'Hybrid', 'LG', NULL, 3000, '321 Surf Rd'),
(5, 'Medium', 'Hybrid', 'LG', NULL, 2000, '321 Surf Rd');   

CREATE TABLE TECHNICIAN
(EmployeeNumber INT,
EmployeeName CHAR(30),
Title CHAR(40),
YearHired INT);
  
INSERT INTO TECHNICIAN
VALUES (1, 'John', 'Tech1', 2004),
(2, 'Jordan', 'Tech2', 2006),
(3, 'JohnAllen', 'Supervisor', 2015);   

CREATE TABLE SERVICE
(HeatingUnitID INT,
ServiceType CHAR(40),
Date Date,
Time TimeSTAMP,
EmployeeNumber INT);

INSERT INTO SERVICE
VALUES (1, 'Repair', NULL, NULL, 1),
(2, 'Repair', NULL, NULL, 2),
(3, 'Maintenance', NULL, NULL, 2),
(4, 'Maintenance', NULL, NULL, 2);

Answer 1

Dear Student ,

As per the requirement submitted above , kindly find the below solution.

This demonstration is using SQL Server 2014.Database and tables are created using SQL Server Management Studio (SSMS).

Question :

SQL Query :

select ServiceType ,year(Date) as 'Date',EmployeeName as 'Technician Name'
from
service, TECHNICIAN
where
service.EmployeeNumber=TECHNICIAN.EmployeeNumber
order by EmployeeName,year(Date);

Screen in SSMS :

NOTE : PLEASE FEEL FREE TO PROVIDE FEEDBACK ABOUT THE SOLUTION.