Convert SQL into PSQL Query 1: Produce a list of current salary, monthly salarie

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

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

select fname, lname, salary as "Current Salary", (salary /12) "Monthly Salary", salary + (salary * 20/100) "New Salary" from staff;

2) CREATE TABLE Branch working(
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);

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

3)

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

4).select propertyno, type, city, sum(rent) from property_for_rent;

Related Questions

Navigate

• Browse (All)
• Browse C
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.