Please write a PLSQL code for the following SQL PLSQL Question Please Use the fo
ID: 3882926 • Letter: P
Question
Please write a PLSQL code for the following
SQL PLSQL Question
Please Use the following code and provide both SQL and PLSQL Statements
Code 1(Step 1) https://codeshare.io/2EMK1o
Code 2 (Step 2) https://codeshare.io/5PpZ0d
Questions
Query 1: Produce a list of current salary, monthly salaries, new salaries (monthly with 20% increase) for all staff, showing staff number, first and last names, and salary details.
Query 2: List all staff whose salary is greater or equal than the average salary of staff working in Branch B010, and show by how much.
Query 3: Find name of owners that the rent of their property is larger than all properties handled by branch B010.
Query 4: For total rent collected for properties located in different cities, list the cities and property with the highest rent collected
Query 5: List properties handled by staff at Burmingham.
Explanation / Answer
hi, it is not given if the salary in staff table is daily on monthly, here given to calculate, so assuming its daily
Query1:
select fname,lname, salary as 'current salary' , salary*30 as 'monthly', salary*36 as 'new salary' from staff;
// here monthly is 30 times daily, 20% more is salary*30+ 0.20*30*(salary)
Query 2:
select fname, lname, salary-avg(salary) as 'difference' from staff where brachno='B010' and salary>=avg(salary);
//calculating avg and showing difference
Query 3:
select b.fname,b.lname from property_for_rent a,private_owner b and rent>(select sum(rent) from property_of_rent where branchno='B0101' ) where a.ownerno=b.ownerno;
//finding all properties having rent greater than sum of rents handled by B0101
Query4:
select sum(rent) as total,city from property_for_rent group by city order by total desc;
// calculating total rent by city
Query 5:
Here, staff has to determined by staffno or branchno, please clarify that then we can write the query.
Thumbs up if this was helpful, otherwise let me know in comments.