Using this garden_glory database: use master; go if db_id(\'garden_glory\') is n
ID: 3573833 • 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 with this question?
1. Write SQL statements that returns all columns and rows from each of the tables in the garden_glory database.
Explanation / Answer
select * from employee;
select * from gg_service;
select * from owner;
select * from owned_property;
select * from property_service;
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.