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 employee_id of employees not servicing any properties

2. Give the average hours worked by employee whose last name is CORY.

3. Give the maximum, average and minimum working hours of each employee in the current database

4. Give name of owners that own more than 2 properties

Answer 1

Sample Data Inserted For Testing:

Query 1 SQL File 3* 1310 SQL File 4* Sl SQL File 5 SQL File 6* X Limit to 1000 rows - Q 1 8. insert into MPROPERTY values(101,201, 'Lakshmi', 'Lakshmi@gmail.com','individual'); insert into MPROPERTY values(102,202, 'narayana', 'narayana@gmail.com','corporate '); insert into MPROPERTY values(103,203, 'kolli', 'kolli@gmail.com', 'Partnership'); insert into MPROPERTY values(104,204, 'test', 'test@gmail.com', 'corporate'); 11. 13. 14 15 • 16 17. insert into MEMPLOYEE values(401, 'ABC', 'BCD', '1231231231', 'Master'); insert into MEMPLOYEE values(402, 'PQR', 'QRS', '2342342342', 'Junior '); insert into MEMPLOYEE values (403, 'XYZ', 'YZA', '3453453453', 'Senior'); insert into MEMPLOYEE values(404,'CORY', 'CORY', '3453453453', 'Master'); insert into MEMPLOYEE values (405, 'MASS', 'MAN', '3453453453', 'Master'); 19 20 insert into MSERVICE values(101,401, 2009-01-01',10); insert into MSERVICE values(102,402,'2020-03-03', 20); insert into MSERVICE values(103,403,'2019-84-84,30); insert into MSERVICE values (194,404, '2019-05-05',40); 21 22 Result Grid Filter Rows: ort: & Wrap Cell Content: IA

Query1:

select e.Employee_ID from MEMPLOYEE e left join MSERVICE s on e.Employee_ID = s.Employee_ID where s.Employee_ID is null;

output image:

Query 1 SQL File 3* SQL File 4* SQL File 5 SQL File 6 x 12 0 0 S Limit to 1000 rows - Q 1 2 insert into MPROPERTY values(103,203, 'kolli', 'kolli@gmail.com', 'Partnership'); insert into MPROPERTY values(104,204, 'test', 'test@gmail.com','corporate'); 10 • 11. insert into MEMPLOYEE values(401, 'ABC', 'BCD', '1231231231', 'Master'); insert into MEMPLOYEE values(402, 'POR', 'QRS','2342342342', 'Junior '); insert into MEMPLOYEE values(403, 'XYZ', 'YZA', '3453453453', 'Senior'); insert into MEMPLOYEE values (404, 'CORY', 'CORY', '3453453453', 'Master'); insert into MEMPLOYEE values(405, 'MASS', 'MAN', '3453453453', 'Master'); 17 • 19 insert into MSERVICE values(101,401, 2009-01-01',10); insert into MSERVICE values(102,402, 2020-03-03',20); insert into MSERVICE values(103,403, 2019-84-94',30); insert into MSERVICE values (194,404, '2019-65-65',40); 22 24 select e. Employee_ID from MEMPLOYEE e left join MSERVICE s on e. Employee_ID = s. Employee_ID where s. Employee_ID is null; 2 . colect Cmnlaus TN l. Uniin innlend fanm MCMDI AVEC Inft ini. MCCDUTOC - Colours TN -- Emolare n..hone I Filter Rows: Export: Wrap Cell Content: IA Result Grid 2 Employee_ID 405

Query 2)

select e.Employee_ID,avg(s.Hours_worked) from MEMPLOYEE e left join MSERVICE s on e.Employee_ID = s.Employee_ID where e.Last_Name='CORY';

output image:

Query 1 SQL File 3* SQL File 4* SQL File 5 SQL File 6 x 2 1 0 0 Limit to 1000 rows Q 1 insert into MPROPERTY values(104,204, 'test', 'test@gmail.com', 'corporate'); 11. insert into MEMPLOYEE values(401, 'ABC', 'BCD', '1231231231', 'Master'); insert into MEMPLOYEE values (402, 'POR', 'QRS','2342342342', 'Junior '); insert into MEMPLOYEE values (403, 'XYZ', 'YZA', '3453453453', 'Senior'); insert into MEMPLOYEE values(404,'CORY', 'CORY', '3453453453', 'Master'); insert into MEMPLOYEE values(405, 'MASS', 'MAN', '3453453453', 'Master'); insert into MSERVICE values(101,401, 2009-01-01',10); insert into MSERVICE values (102,402, 2020-03-03',20); insert into MSERVICE values(103,403,'2019-04-04',30); insert into MSERVICE values(104,404, '2019-05-05',40); select e. Employee_ID from MEMPLOYEE e left join MSERVICE s on e. Employee_ID = s. Employee_ID where s. Employee_ID is null; select e. Employee ID, avg(s.Hours worked) from MEMPLOYEE e left join MSERVICE s on e. Employee ID = s. Employee ID where e.Last Name='CORY': Export: å Wrap Cell Content: A Result Grid Employee_ID 404 Filter Rows: avg(s.Hours_worked) 40.0000 Result Grid

Query 3)

select Employee_ID,max(Hours_worked),min(Hours_worked),avg(Hours_worked) from MSERVICE group by Employee_ID;

Output Image:

Query 1 B 13. 14 • SQL File 3* SQL File 4* SQL File 50 SQL File 6 x 3 0 SC Limit to 1000 rows $ Q 1 insert into MEMPLOYEE values(401, 'ABC', 'BCD', '1231231231', 'Master'); insert into MEMPLOYEE values (402, 'PQR', 'QRS','2342342342', 'Junior '); insert into MEMPLOYEE values(403, 'XYZ', 'YZA', '3453453453', 'Senior'); insert into MEMPLOYEE values (404, 'CORY', 'CORY', '3453453453', 'Master'); insert into MEMPLOYEE values(405, 'MASS', 'MAN', '3453453453', 'Master'); 15. 16. 17. 20 21. 22 • insert into MSERVICE values(101,401,'2009-01-01',10); insert into MSERVICE values(102,402, '2020-03-03',20); insert into MSERVICE values(103,403,'2019-04-04',30); insert into MSERVICE values(104,404, '2019-05-05',40); select e. Employee_ID from MEMPLOYEE e left join MSERVICE S on e. Employee_ID = s. Employee_ID where s. Employee_ID is null; select e. Employee_ID, avg(s.Hours_worked) from MEMPLOYEE e left join MSERVICE son e. Employee_ID = s. Employee_ID where e.Last_Name: 28 select Employee_ID, max(Hours_worked),min(Hours_worked), avg(Hours_worked) from MSERVICE group by Employee_ID; Result Grid Employee_ID 401 Filter Rows: ort: A Wrap Cell Content: IA max(Hours_worked) min(Hours_worked) avg(Hours_worked) 10.0000 20.0000 30.0000 40.0000 402 403 404 Result 11 x

Query 4)

No Owner is there with me more than 2 properties are assigned to it.

According to the suggestions provided for this case, one owner will have only one property associated with it.

By Self join the table PROPERTY , we will get the properties which are assigned more than two for one owner.

select Owner_ID,count(Propert_ID) from MPROPERTY group by Propert_ID;

output Image:

: Query 1 B SQL File 3* SQL File 4* 100 So 1 SQL File 5 SQL File 6* x Limit to 1000 rows - 2 Q 1 a 25 26. select e. Employee_ID, avg(s.Hours_worked) from MEMPLOYEE e left join MSERVICE s on e. Employee_ID = s. Employee_ID where e.Last_Name='CORY' 28. select Employee_ID, max(Hours_worked), min(Hours_worked), avg(Hours_worked) from MSERVICE group by Employee_ID; 30. select Owner_ID, count(Propert_ID) from MPROPERTY group by Propert_ID; Export: Wrap Cell Content: JA Filter Rows: count(Propert_ID) 1 MITI Result Grid Owner_ID 201 2021 203 204 A Result 14 x O Read - O utput ................................................................................................................................................................................................................................................................................................................................................................................................. o ® Duration / Fetch 0.000 sec 0.000 sec Action Output Time Action Message 125 21:46:59 select Owner_ID.count(Propert_ID) from MPROPERTY where count(Propert_ID)>2... Error Code: 1111. Invalid use of group function 126 21:48:37 select Owner_ID.count(Propert_ID) from MPROPERTY where (select count(Proper... Error Code: 1242. Subquery retums more than 1 row 127 21:49:26 select Owner_ID from MPROPERTY where count(Propert_ID)>2 group by Proper... Error Code: 1111. Invalid use of group function 128 21:49:41 select Owner_ID from MPROPERTY group by Propert_ID LIMIT 0, 1000 4 row(s) retumed 129 21:50:12 select Owner_ID from MPROPERTY where count(Propert_ID) > 2 group by Prope... Error Code: 1111. Invalid use of group function 130 21:50:39 select Owner_ID.count(Propert_ID) from MPROPERTY group by Propert_ID LIMI... 4 row(s) retumed ® 0.000 sec 0.000 sec /0.000 s 0.000 sec 0.000 sec /0.000