Database Exercises 5

You will need to CREATE the database you are using to query! Use the Listing database text file:

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

INSERT INTO HOUSE
VALUES ('285 Westport Rd', 'John', 'Yes'),
('897 Nowhere', 'Jim', 'Yes'),
('891 Hello', 'Bill', 'No');

CREATE TABLE HEATING_UNIT
(HeatingUnitID INT,
UnitName CHAR(30),
UnitType CHAR(40),
Manufactory CHAR(40),
DateOfBuilt Date,
Capacity INT,
HouseAddress CHAR(50),
PRIMARY KEY (HeatingUnitID));

INSERT INTO HEATING_UNIT
VALUES (4, 'Large', 'Solar', 'GE', NULL, 3000, '897 Nowhere'),
(3, 'Small', 'Electic', 'GE', NULL, 5000, '897 Nowhere'),
(1, 'Small', 'Gas', 'LG', NULL, 2000, '285 Westport Rd'),
(2, 'Large', 'Gas', 'GE', NULL, 4500, '285 Westport Rd'),
(6, 'Medium', 'Hybrid', 'LG', NULL, 4000, '891 Hello'),
(5, 'Large', 'Hybrid', 'LG', NULL, 2500, '891 Hello');

CREATE TABLE TECHNICIAN
(EmployeeNumber INT,
EmployeeName CHAR(30),
Title CHAR(40),
YearHired INT,
PRIMARY KEY (EmployeeNumber));

INSERT INTO TECHNICIAN
VALUES (1, 'Vic', 'Tech1', 2005),
(2, 'Tom', 'Tech2', 2010),
(3, 'Greg', 'Manager', 2012);

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

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

There should be four tables: HOUSE, HEATINGUNIT, TECHNICIAN, and SERVICE. The attributes of each table are also given, but you will need to make up the data. Create queries for the below questions:

• Shown as example above - do not need to include it.
• Create a query that will list all technician names, employee numbers, and title in order by employee number (highest to lowest).
• The types of heating units (UnitType) included in the table, HEATINGUNIT, should be a combination of gas, electric, solar, and hybrid, each with a capacity from 2000 up to 5000, and have a variety of manufacturers (Manufactory). (Remember, you are making up the exact data.) Create a query that will show the ID, type, manufacturer, and capacity of all the heating units. List them in order by manufacturer and then by capacity within each manufacturer, order them by capacity from largest to smallest.
• 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 and employee number of the technician that performed the service. Order by employee name and then within each employee, by date, most recent first.
• Create a query that uses both a wildcard character and an aggregate function.
• In the HOUSE table there is an Insurance attribute. This would be either a 'yes' or no entry. In the HEATINGUNIT table there is a DateOfBuilt attribute, you can format this as the year only, for example 2021. Create a query that shows all the house addresses that have no insurance, (please label the column as 'No Insurance'), the unit type, and the DateOfBuilt for the unit. Order the list by DateOfBuilt, from oldest to newest.