Using this garden_glory database: use master; go if db_id(\'garden_glory\') is n

Question

Using this garden_glory database: use master; go if db_id('garden_glory') is not null begin alter database garden_glory set single_user with rollback immediate; drop database garden_glory; end go create database garden_glory; go /* END SQL SERVER ONLY SECTION */ use garden_glory; create table employee( employee_id int identity not null, last_name varchar(30), first_name varchar(30), cell_phone varchar(12), experience_level varchar(8), constraint pk_employee primary key (employee_id) ); create table gg_service( service_id int identity not null, service_description varchar(255), cost_per_hour money, constraint pk_gg_service primary key (service_id) ); create table owner( owner_id int identity not null, owner_name varchar(40), owner_email varchar(100), owner_type varchar(15), constraint pk_owner primary key (owner_id) ); create table owned_property( property_id int identity not null, property_name varchar(50), property_type varchar(30), street varchar(50), city varchar(30), state char(2), zip varchar(5), owner_id int, constraint pk_owned_property primary key (property_id), constraint fk_op_owner foreign key (owner_id) references owner (owner_id) on delete cascade on update cascade ); create table property_service( property_service_id int identity not null, property_id int, service_id int, service_date datetime, employee_id int, hours_worked numeric(5,2), constraint pk_property_service primary key (property_service_id), constraint fk_ps_owned_property foreign key (property_id) references owned_property (property_id) on delete cascade on update cascade, constraint fk_ps_gg_service foreign key (service_id) references gg_service (service_id) on delete set null on update cascade, constraint fk_ps_employee foreign key (employee_id) references employee (employee_id) on delete set null on update cascade ); use garden_glory; insert into employee (last_name, first_name, cell_phone, experience_level) values ('Smith', 'Sam', '206-254-1234', 'Master'), ('Evanston', 'John', '206-254-2345', 'Senior'), ('Murray', 'Dale', '206-254-3456', 'Junior'), ('Murphy', 'Jerry', '585-545-8765', 'Master'), ('Fontaine', 'Joan', '206-254-4567', 'Senior'); insert into gg_service (service_description, cost_per_hour) values ('Mow Lawn', 25.0000), ('Plant Annuals', 25.0000), ('Weed Garden', 30.0000), ('Trim Hedge', 45.0000), ('Prune Small Tree', 60.0000), ('Trim Medium Tree', 100.0000), ('Trim Large Tree', 125.0000); insert into owner (owner_name, owner_email, owner_type) values ('Mary Jones', 'Mary.Jones@somewhere.com', 'Individual'), ('DT Enterprises', 'DTE@dte.com', 'Corporation'), ('Sam Douglas', 'Sam.Douglas@somewhere.com', 'Individual'), ('UNY Enterprises', 'UNYE@unye.com', 'Corporation'), ('Doug Samuels', 'Doug.Samuels@somewhere.com', 'Individual'); insert into owned_property (property_name, property_type, street, city, state, zip, owner_id) values ('Eastlake Building', 'Office', '123 Eastlake', 'Seattle', 'WA', '98119', 2), ('Elm St Apts', 'Apartments', '4 East Elm', 'Lynwood', 'WA', '98223', 1), ('Jefferson Hill', 'Office', '42 West 7th St', 'Bellevue', 'WA', '98007', 2), ('Lake View Apts', 'Apartments', '1265 32nd Avenue', 'Redmond', 'WA', '98052', 3), ('Kodak Heights Apts', 'Apartments', '65 32nd Avenue', 'Redmond', 'WA', '98052', 4), ('Jones House', 'Private Residence', '1456 48th St', 'Bellevue', 'WA', '98007', 1), ('Douglas House', 'Private Residence', '1567 51st St', 'Bellevue', 'WA', '98007', 3), ('Samuels House', 'Private Residence', '567 151st St', 'Redmond', 'WA', '98052', 5); insert into property_service (property_id, service_id, service_date, employee_id, hours_worked) values (1, 2, '2014-05-05', 1, 4.50), (3, 2, '2014-05-08', 3, 4.50), (2, 1, '2014-05-08', 2, 2.75), (6, 1, '2014-05-10', 5, 2.50), (5, 4, '2014-05-12', 4, 7.50), (8, 1, '2014-05-15', 4, 2.75), (4, 4, '2014-05-19', 1, 1.00), (7, 1, '2014-05-21', 2, 2.75), (6, 3, '2014-06-03', 5, 2.50), (5, 7, '2014-06-08', 4, 10.50), (8, 3, '2014-06-12', 4, 2.75), (4, 5, '2014-06-15', 1, 5.00), (7, 3, '2014-06-19', 2, 4.00); How would you answer Question N? N. Write an SQL statement to show all properties and the services performed at those properties. The output from this statement should include any properties that have not had any service performed at them. The SQL statement output should list PropertyID, PropertyName, PropertyType, PropertyServiceID, ServiceID, ServiceDate, and ServiceDescription. (Hint: Use JOIN ON syntax.)

Explanation / Answer

SELECT PROPERTY_ID, PropertyName, PropertyType, PropertyServiceID, ServiceID, ServiceDate, ServiceDescription FROM gg_service AS G owner AS O owned_property AS OP
  
property_service AS P WHERE G.SERVICE_ID=P.SERVICE_ID AND P.PROPERTY_ID=OP.PROPERTY_ID ANF O.OWNER_ID=OP.OWNER_ID;

Related Questions

Navigate

• Browse (All)
• Browse U
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.