Write a query in SQL to list the name, salary, commission and netpay for those employees whose netpay is more than any other employee

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

Write a query in SQL to list the name, salary, commission and netpay for those employees whose netpay is more than any other employee.

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 e.emp_name,
       e.salary,
       e.commission,

  (SELECT sum(salary+commission)
   FROM employees) NETPAY
FROM employees e
WHERE
    (SELECT sum(salary+commission)
     FROM employees) > ANY
    (SELECT salary
     FROM employees
     WHERE emp_id =e.emp_id) ;

Sample Output:

 emp_name | salary  | commission | netpay
----------+---------+------------+---------
 KAYLING  | 6000.00 |            | 8500.00
 BLAZE    | 2750.00 |            | 8500.00
 CLARE    | 2550.00 |            | 8500.00
 JONAS    | 2957.00 |            | 8500.00
 SCARLET  | 3100.00 |            | 8500.00
 FRANK    | 3100.00 |            | 8500.00
 SANDRINE |  900.00 |            | 8500.00
 ADELYN   | 1700.00 |     400.00 | 8500.00
 WADE     | 1350.00 |     600.00 | 8500.00
 MADDEN   | 1350.00 |    1500.00 | 8500.00
 TUCKER   | 1600.00 |       0.00 | 8500.00
 ADNRES   | 1200.00 |            | 8500.00
 JULIUS   | 1050.00 |            | 8500.00
 MARKER   | 1400.00 |            | 8500.00
(14 rows)
سجل الدخول لتستطيع الإجابة
هل كان المحتوى مفيد؟

0 نعم

0 لا

أسئلة مشابهة

Write a query in SQL to list the name, salary, and commission for those employees whose net pay is greater than or equal to the salary of any other employee in the company
Write a query in SQL to list the employees whose salary is less than the salary of his manager but more than the salary of any other manager
Write a query in SQL to list the name, job name, manager id, salary, manager name, manager's salary for those employees whose salary is greater than the salary of their managers
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)

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

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

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

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