Homework Solution: Convert SQL into PSQL…

    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';

    Expert Answer

     
    1).Staff table CREATE TABLE staff(

    Convert SQL into PSQL

    Query 1: Produce a roll of floating compensation, monthly salaries, innovating salaries (monthly with 20% acception) restraint full staff, pretexting staff enumerate, highest and ultimate names, and compensation details.

    picked fname, lname, compensation as “Floating Compensation”, (compensation /12) “Monthly Compensation”, compensation + (compensation * 20/100) “Innovating Compensation” from staff;

    Query 2: Roll full staff whose compensation is main or similar than the middle compensation of staff afloat in Ramification B010, and pretext by how greatly.        

    picked staffno, fname, lname, lie, compensation – (picked avg(salary) from staff) as Sal from staff where compensation >= (picked avg(salary) from staff) and ramificationno = ‘B010’;

    Query 3: Find name of owners that the rent of their peculiarity is larger than full properties handled by ramification B010.

    picked peculiarity_for_rent.ownerno, fname, lname from private_owner

    inner annex peculiarity_for_rent

    where rupture >= (picked combine(rent) from peculiarity_for_rent) and peculiarity_for_rent.ownerno = private_owner.ownerno class by fname having ramificationno = ‘B010’;

    Query 4: Restraint aggregate rupture calm restraint properties located in differupture cities, roll the cities and peculiarity with the highest rupture calm

    picked peculiarityno, mark, city, combine(rent) from peculiarity_for_rent;

    Query 5: Roll properties handled by staff at Burmingham.

    picked peculiarityno, street, city, mark, fname, lie

    from peculiarity_for_rent

    inner annex staff

    where peculiarity_for_rent.branchno = staff.branchno and city = ‘Burmingham’;

    Expert Tally

     

    1).Staff table

    CREATE TABLE staff(
    ID INT PRIMARY KEY NOT NULL,
    fname TEXT NOT NULL,
    lname INT NOT NULL,
    ADDRESS CHAR(50),
    SALARY REAL
    );

    INSERT INTO staff values (1, ‘Chinnu’, 9000);
    INSERT INTO staff values (2, ‘swetha’, 19000);
    INSERT INTO staff values (3, ‘ram’, 20000);
    INSERT INTO staff values (4, ‘krishna’, 30000);
    INSERT INTO staff values (5, ‘sumith’, 18000);

    picked fname, lname, compensation as “Floating Compensation”, (compensation /12) “Monthly Compensation”, compensation + (compensation * 20/100) “Innovating Compensation” from staff;

    2) CREATE TABLE Ramification afloat(
    ID INT PRIMARY KEY NOT NULL,
    fname TEXT NOT NULL,
    lname INT NOT NULL,
    ADDRESS CHAR(50),
    SALARY REAL
    );

    INSERT INTO staff values (2, ‘swetha’, 19000);
    INSERT INTO staff values (3, ‘ram’, 20000);
    INSERT INTO staff values (4, ‘krishna’, 30000);
    INSERT INTO staff values (5, ‘sumith’, 18000);

    picked staffno, fname, lname, lie, compensation – (picked avg(salary) from staff) as Sal from staff where compensation >= (picked avg(salary) from staff) and ramificationno = ‘B010’;

    3)

    picked peculiarity_for_rent.ownerno, fname, lname from private_owner

    inner annex peculiarity_for_rent

    where rupture >= (picked combine(rent) from peculiarity_for_rent) and peculiarity_for_rent.ownerno = private_owner.ownerno class by fname having ramificationno = ‘B010’;

    4).picked peculiarityno, mark, city, combine(rent) from peculiarity_for_rent;