Convert SQL into PSQL Query 1: Produce a list of current salary, monthly salarie
ID: 3882785 • Letter: C
Question
Convert SQL into PSQL
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.
select fname, lname, salary as "Current Salary", (salary /12) "Monthly Salary", salary + (salary * 20/100) "New Salary" from staff;
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.
select staffno, fname, lname, position, salary - (select avg(salary) from staff) as Sal from staff where salary >= (select avg(salary) from staff) and branchno = 'B010';
Query 3: Find name of owners that the rent of their property is larger than all properties handled by branch B010.
select property_for_rent.ownerno, fname, lname from private_owner
inner join property_for_rent
where rent >= (select sum(rent) from property_for_rent) and property_for_rent.ownerno = private_owner.ownerno group by fname having branchno = 'B010';
Query 4: For total rent collected for properties located in different cities, list the cities and property with the highest rent collected
select propertyno, type, city, sum(rent) from property_for_rent;
Query 5: List properties handled by staff at Burmingham.
select propertyno, street, city, type, fname, position
from property_for_rent
inner join staff
where property_for_rent.branchno = staff.branchno and city = 'Burmingham';
Explanation / Answer
Query 1
------------
------------------- Procedure Creation --------------------------
CREATE OR REPLACE PROCEDURE staff_1
IS
fname1 varchar2(20);
lname1 varchar2(10);
salary1 NUMBER;
month_salary NUMBER;
new_salary NUMBER;
BEGIN
select fname,lname,salary into fname1,lname1,salary1 from staff;
month_salary := (salary1 /12);
new_salary := salary1 + (salary1 * 20/100);
DBMS_OUTPUT.PUT_LINE('"First Name " = ' || fname1 || '", Last Name " = ' || lname1 || '", Current Salary" = '
|| salary1 || '" ,Monthly Salary" = ' || month_salary || ', "New Salary" = ' || new_salary );
EXCEPTION
WHEN others
THEN
DBMS_OUTPUT.put_line('MAYA_INSERT error: '||sqlerrm);
END staff_1;
------------------------------- Procedure creation End-----------------
------------------ Procedure Invoked ---------------
exec staff_1;
------------------ Procedure Invoked End ---------------
Query 2 :
---------------
CREATE OR REPLACE PROCEDURE query_2
IS
staffno1 NUMBER;
fname1 varchar2(20);
lname1 varchar2(10);
position1 varchar2(20);
avg_salary NUMBER;
salary1 NUMBER;
BEGIN
select avg(salary) into avg_salary from staff;
select 101,fname,lname,'position',salary from staff where salary >= 60000 ;
select 101,fname,lname,'position11',salary into staffno1,fname1,lname1,position1,salary1 from staff where salary >= 5000 ;
DBMS_OUTPUT.PUT_LINE('"staffno " = ' || staffno1 || ',"First Name " = ' || fname1 || '", Last Name " = ' || lname1 || ', "position " = ' || position1 || '", Sal" = ' || (salary1 - avg_salary ));
EXCEPTION
WHEN others
THEN
DBMS_OUTPUT.put_line('MAYA_INSERT error: '||sqlerrm);
END query_2;