Consider following schema and write SQL for given statements.title (id,designation,DOJ), bonus(id,bonus_date,amount)Create tables with Primary Key, foreign key constraints in given schemas.1)Retrieve the employees who haven't received any bonuses.2)Retrieve the total bonus amount received by each employee.3)Retrieve the highest bonus amount received.4)List out id's whose bonus amount is at most 4000 and designation is admin
Question
Consider following schema and write SQL for given statements.title (id,designation,DOJ), bonus(id,bonus_date,amount)Create tables with Primary Key, foreign key constraints in given schemas.1)Retrieve the employees who haven't received any bonuses.2)Retrieve the total bonus amount received by each employee.3)Retrieve the highest bonus amount received.4)List out id's whose bonus amount is at most 4000 and designation is admin
Solution
Sure, here are the SQL queries for the given tasks:
First, let's create the tables:
CREATE TABLE title (
id INT PRIMARY KEY,
designation VARCHAR(255),
DOJ DATE
);
CREATE TABLE bonus (
id INT,
bonus_date DATE,
amount DECIMAL(10, 2),
FOREIGN KEY (id) REFERENCES title(id)
);
- Retrieve the employees who haven't received any bonuses.
SELECT t.id, t.designation
FROM title t
LEFT JOIN bonus b ON t.id = b.id
WHERE b.id IS NULL;
- Retrieve the total bonus amount received by each employee.
SELECT t.id, SUM(b.amount) as total_bonus
FROM title t
JOIN bonus b ON t.id = b.id
GROUP BY t.id;
- Retrieve the highest bonus amount received.
SELECT MAX(amount) as highest_bonus
FROM bonus;
- List out id's whose bonus amount is at most 4000 and designation is admin.
SELECT t.id
FROM title t
JOIN bonus b ON t.id = b.id
WHERE b.amount <= 4000 AND t.designation = 'admin';
Similar Questions
Consider following schema and write SQL for given statements.title (id,designation,DOJ)bonus(id,bonus_date,amount)1. List out bonus id whose designation is MANAGER .2. List out id's whose bonus amount at most 4000 and designation is admin
Consider following schema and write SQL for given statements.worker (id,firstname,lastname,salary,joining_date,dept)bonus(id,bonus_date,amount)1. Find firstname and lastname of worker whose amount is greater than 2400.2. List out salary of worker with id who got bonus
In PostgreSQL, create a PL/pgSQL block that defines a function named calculate_salary_bonus which takes two parameters:employee_id and bonus_percentage. This function should calculate the bonus amount for the employee with the givenemployee_id based on their current salary and the provided bonus percentage. Assume “employee” table with fields – eid, enameand salary
Consider following schema and write query for given statementEmp (eid,ename,city,dname,salary) Project(eid,pid,pname,location)Create tables with Primary Key, foreign key constraints in given schemas.(1) Display name of employees who belongs to Computer department.(2) Display employee id whose name starts from letter J.(3) Display all details of employees whose salary is from 10000 to 20000.(4) Display name of employees who are having maximum salary.(5) Display name of employees whose salary is higher than average salary of the employee.(6) Display name of employees whose project id is 3 and location is Mumbai
Write SQL QueriesEmployee TableDepartment Table1. Get the list of employees whose salary is more than 20K and are in HR department.2. Get the salary being paid to each Department.3. Get the employees with the third highest salary.4. Name and designation of the employees who don't have any manager
Upgrade your grade with Knowee
Get personalized homework help. Review tough concepts in more detail, or go deeper into your topic by exploring other relevant questions.