Please give PLSQL(Programming Language SQL Statements) Code 1(Step 1) https://co
ID: 3883400 • Letter: P
Question
Please give PLSQL(Programming Language SQL 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
1) select staffno, fname, lname, salary as "Current Salary", (salary /12) as "Monthly Salary", (salary + (salary * 20/100)) as "New Salary" from staff
2) select staffno,fname,lname,salary,(salary-average) as difference from staff where salary > (( select avg(salary) as average from staff where branchno='B010'));
3) select p.fname,p.lname from private_owner p, property_on_rent r where p.ownerno=r.ownerno and r.rent>(select max(rent) from property_on_rent where branchno='B010')
4) select city, sum(rent) as s from property_for_rent group by city order by s desc limit 1;
5) select distinct(p.propertyno) from property_on_rent as p ,branch as b where p.branchno=b.branchno and b.city='Burmingham';