Garden Glory is a partnership that provides gardening and yard maintenance servi
ID: 3834236 • Letter: G
Question
Garden Glory is a partnership that provides gardening and yard maintenance services to individual and organizations. Assume that Garden Glory designs a database with the following tables: OWNER(OwnerID). Name, Email, Type) PROPERTY(Property ID), Street, City. State, Zip, Owner ID) EMPLOYEE (Initials, Name, CellPhone, ExperienceLevel) Type is either Individual or Corporation, and ExperienceLevel is one of Junior, Senior. Master or SuperMaster. Code SQL statements to answer the questions below: List all columns of all tables List the Name and CellPhone of all employees having an experience level of Master. List the Name and CellPhone of all employees having a Name that begins with J. List the Name of employees who have worked on a property in New York (use subquery) Same as but using a join Show the number of properties of Type Corporation. List the Name of employees who have worked on a property owned by a CorporationExplanation / Answer
1. select COLUMN_NAME from INFORMATION_SCHEMA.COLUMNS;
2. select NAME, CELLPHONE from EMPLOYEE where EXPERIENCELEVEL='Master';
3. select NAME, CELLPHONE from EMPLOYEE where NAME LIKE 'J%';
4.
select NAME
from EMPLOYEE
where INITIALS
in
(
select INITIALS
from SERVICE
where PROPERTYID
in
(
select PROPERTYID
from PROPERTY
where CITY='New York'
)
);
5.
select E.NAME from
SERVICE S
INNER JOIN PROPERTY P ON S.PROPERTYID = P.PROPERTYID
INNER JOIN EMPLOYEE E ON E.INITIALS = S.INITIALS;
6.
select COUNT(PROPERTYID) where OWNERID IN
(
select OWNERID from
OWNER where TYPE = 'Corporation'
);
7.
select NAME
from EMPLOYEE
where INITIALS in
(
select distinct(INITIALS)
from SERVICE
where PROPERTYID in
(
select distinct(PROPERTYID)
from PROPERTY
where OWNERID IN
(
select OWNERID from
OWNER where TYPE = 'Corporation'
)
)
);
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.