Question

This is for SQL Database (Oracle)

I have three tables MPROPERTY, MEMPLOYEE, and MSERVICE with the following attributes:

MPROPERTY (Property_ID, Owner_ID, Owner_Name, Owner_email, Owner_type)

Property_ID is the id of property owned by an owner (assume one property can be owned by one owner only)

Owner_ID is the ID of Owner

Owner_name is name of the owner

Owner_email is the e-mail of the owner

Owner_type is type of owner (individual, corporate or Partnership)

MEMPLOYEE (Employee_ID, Last_name, First_Name, CellPhone, ExperienceLevel)

EMPLOYEE_ID is the ID of employee

Last_name is last name of the employee

First_name is the first name of employee

CellPhone is cell number of employee

ExperienceLevel is based on number of years of experience and is classified as Master, Junior or Senior

MSERVICE (Property_ID, Employee_ID, Service_Date, Hours_worked)

Service_date is the date that property was serviced

Hours_worked is the number of hours that employee worked on that project

________________________________________________________________

Answer the following by writing SQL queries:

1. Give the last name of all owners

2. Give the total number (count) of properties owned by owner type

3. Give the names of owners whose last names have an R in their name. (ex: MARY, GARY etc.)

4. Give the total hours worked by each employee

Answer 1

1.

Select owner_name from MProperty;

2.

Select owner_type, Count(Property_ID) from MProperty group by owner_type;

3.

Select owner_name from MProperty where owner_name LIKE '%R%';

4.

Select Employee_ID,Sum(Hours_Worked) from MService group by Employee_ID;

Do ask if any doubt. Please up-vote.