Write a query in SQL to list the department id, name, designation, salary, and net salary (salary+commission) of the SALESMAN who are earning maximum net salary

  • برمجة سي كيو ال sql

Write a query in SQL to list the department id, name, designation, salary, and net salary (salary+commission) of the SALESMAN who are earning maximum net salary.

 

Sample table: employees

 emp_id | emp_name | job_name  | manager_id | hire_date  | salary  | commission | dep_id
--------+----------+-----------+------------+------------+---------+------------+--------
  68319 | KAYLING  | PRESIDENT |            | 1991-11-18 | 6000.00 |            |   1001
  66928 | BLAZE    | MANAGER   |      68319 | 1991-05-01 | 2750.00 |            |   3001
  67832 | CLARE    | MANAGER   |      68319 | 1991-06-09 | 2550.00 |            |   1001
  65646 | JONAS    | MANAGER   |      68319 | 1991-04-02 | 2957.00 |            |   2001
  67858 | SCARLET  | ANALYST   |      65646 | 1997-04-19 | 3100.00 |            |   2001
  69062 | FRANK    | ANALYST   |      65646 | 1991-12-03 | 3100.00 |            |   2001
  63679 | SANDRINE | CLERK     |      69062 | 1990-12-18 |  900.00 |            |   2001
  64989 | ADELYN   | SALESMAN  |      66928 | 1991-02-20 | 1700.00 |     400.00 |   3001
  65271 | WADE     | SALESMAN  |      66928 | 1991-02-22 | 1350.00 |     600.00 |   3001
  66564 | MADDEN   | SALESMAN  |      66928 | 1991-09-28 | 1350.00 |    1500.00 |   3001
  68454 | TUCKER   | SALESMAN  |      66928 | 1991-09-08 | 1600.00 |       0.00 |   3001
  68736 | ADNRES   | CLERK     |      67858 | 1997-05-23 | 1200.00 |            |   2001
  69000 | JULIUS   | CLERK     |      66928 | 1991-12-03 | 1050.00 |            |   3001
  69324 | MARKER   | CLERK     |      67832 | 1992-01-23 | 1400.00 |            |   1001
(14 rows)

الأجوبة

SELECT dep_id,
       emp_name,
       job_name,
       salary,
       salary+commission "Net Salary"
FROM employees
WHERE job_name = 'SALESMAN'
  AND salary+commission IN
    (SELECT max(salary+commission)
     FROM employees
     WHERE commission IS NOT NULL);

Sample Output:

 dep_id | emp_name | job_name | salary  | Net Salary
--------+----------+----------+---------+------------
   3001 | MADDEN   | SALESMAN | 1350.00 |    2850.00
(1 row)
سجل الدخول لتستطيع الإجابة
هل كان المحتوى مفيد؟

0 نعم

0 لا

أسئلة مشابهة

Write a query in SQL to list the department id, name, designation, salary and net salary (salary+commission) of the employees only who gets a commission and earn the second highest earnings (salary + commission)
Write a query in SQL to list the name, designation, and salary of the employees who does not work in the department 1001 but works in same designation and salary as the employees in department 3001
Write a query in SQL to list the name, job name, annual salary, department id, department name and grade of the employees who earn 60000 in a year or not working as an ANALYST
Write a query in SQL to list the employees whose designation is same as either the designation of ADELYN or the salary is more than salary of WADE

القوائم الدراسية التي ينتمي لها السؤال

معلومات ذات صلة

تبحث عن مدرس اونلاين؟

ابحث عن مدرسك
محتاج مساعدة باختيار المدرس الافضل؟ تواصل مع فريقنا الان لمساعدتك بتأمين افضل مدرس
ماهو التخصص الذي تبحث عنه؟
اكتب هنا...