Using this garden_glory database: use master; go if db_id(\'garden_glory\') is n
ID: 3573846 • Letter: U
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); Could you help me answer this question? 5. Write a SQL statement that crosstabulates the number of properties by city and property_type (with property type across columns)
Explanation / Answer
select city, property_type, count(*) as No_Of_Properties_per_city from owned_property group by city, property_type
Number of Records: 6
city property_type No_Of_Properties_per_city Bellevue Office 1 Bellevue Private Residence 2 Lynwood Apartments 1 Redmond Apartments 2 Redmond Private Residence 1 Seattle Office 1Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.