OraFAQ Forum: SQL & PL/SQL » decrease 20% for the highest salary paid in every dept.

Home » SQL & PL/SQL » SQL & PL/SQL » decrease 20% for the highest salary paid in every dept.

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

decrease 20% for the highest salary paid in every dept. [message #1869]

Thu, 06 June 2002 12:25

James
Messages: 120
Registered: June 2000

Senior Member

Hi,

I created 2 tables: Dept, and Employee. as Dept_emp.sql(attached)
I want to check all secretaries in all department.
but because some dept does not have secretary. eg, the Marketing has no secretary.
1. How can I get" NULL" of secretary in that deptment?
(Marketing NULL)

2. How can I decrease 20% for the highest salary paid in every department?

any procedure (PL/SQL or T-SQL) is needed.

your help will be appreciated.

James

Drop table dept;
drop table employee;
CREATE TABLE DEPT(
DEPTID NUMBER(10) NOT NULL,
NAME VARCHAR2(30),
LOCATION VARCHAR2(30)
);

CREATE TABLE EMPLOYEE(
EMPID NUMBER(10) NOT NULL,
NAME VARCHAR2(30),
TITLE VARCHAR2(30),
SALARY NUMBER(10),
DEPTID NUMBER(10) NOT NULL
);

INSERT INTO DEPT VALUES (1,'MARKETING','New York');
INSERT INTO DEPT VALUES (2,'SALES','Boston');
INSERT INTO DEPT VALUES (3,'ACCOUNTING','Chicago');
INSERT INTO DEPT VALUES (4,'DEVELOPMENT','TORONTO');

INSERT INTO EMPLOYEE VALUES (101,'David Suan','Manager', 75000,1);
INSERT INTO EMPLOYEE VALUES (102,'Andrew BBB','Director', 70000,1);

INSERT INTO EMPLOYEE VALUES (103,'qqq WWW','Manager', 80000,2);
INSERT INTO EMPLOYEE VALUES (104,'bbb AAA','Secretary', 42000,2);
INSERT INTO EMPLOYEE VALUES (105,'ccc DDD', 'Secretary',40000,2);

INSERT INTO EMPLOYEE VALUES (106,'Bob Bush','Manager', 90000,3);
INSERT INTO EMPLOYEE VALUES (107,'Brend BBB','Secretary', 41000,3);
INSERT INTO EMPLOYEE VALUES (108,'Liz DDD', 'analyst', 45000,3);

INSERT INTO EMPLOYEE VALUES (109,'James Bond','Manager', 95000,4);
INSERT INTO EMPLOYEE VALUES (110,'Lisa TTT','Secretary', 40000,4);

COMMIT;

Report message to a moderator

Re: decrease 20% for the highest salary paid in every dept. [message #1870 is a reply to message #1869]

Thu, 06 June 2002 13:32

Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille

Senior Member
Account Moderator

SQL> get 1
  1  select a.name, count(decode(b.title,'Secretary','Yes')) NumberOfSecretary
  2   from dept a, employee b
  3  where a.deptid=b.deptid
  4* group by a.name
SQL> /

NAME                           NUMBEROFSECRETARY
------------------------------ -----------------
ACCOUNTING                                     1
DEVELOPMENT                                    1
MARKETING                                      0
SALES                                          2

SQL> get 2
  1  select a.name,max(b.salary) original_Salaray,max(b.salary)-(max(b.salary)*.20) Decreased_salary
  2  where a.deptid=b.deptid
  3* group by a.name
SQL> /

NAME                           ORIGINAL_SALARAY DECREASED_SALARY 
------------------------------ ---------------- --------------------------
ACCOUNTING                                90000                      72000
DEVELOPMENT                               95000                      76000
MARKETING                                 75000                      60000
SALES                                     80000                      64000

SQL>

Report message to a moderator

Re: decrease 20% for the highest salary paid in every dept. [message #1873 is a reply to message #1870]

Thu, 06 June 2002 18:57

James
Messages: 120
Registered: June 2000

Senior Member

Hi,

Thank you very much. but:
1. need to show tha name of secretary "Marketing NULL", not the total number. Can we do that? somebody told me we can't do that.
2. need to update the table "employee", not just display on the screen the 80%.

for one deptment, sth like that, but I do not know how to update all dept.

Update employee
set salary = salary*0.8
where salary = (select max(salary) from employee )

Thanks a lot,

James

Report message to a moderator

Re: decrease 20% for the highest salary paid in every dept. [message #1887 is a reply to message #1870]

Fri, 07 June 2002 09:37

Todd Barry
Messages: 4819
Registered: August 2001

Senior Member

sql>select d.name dept_name, nvl(e.name, 'NULL') employee_name
  2    from dept d, employee e
  3   where e.deptid (+)= d.deptid
  4     and e.title (+)= 'Secretary'
  5   order by d.name;
 
DEPT_NAME                      EMPLOYEE_NAME
------------------------------ ------------------------------
ACCOUNTING                     Brend BBB
DEVELOPMENT                    Lisa TTT
MARKETING                      NULL
SALES                          bbb AAA
SALES                          ccc DDD

To decrease the highest salary in each department by 20% (I am doing the update to new_salary so you can see the old and new values):

sql>update employee
  2     set new_salary = salary * (1 - .20)
  3   where (deptid, salary) in
  4           (select deptid, max(salary)
  5              from employee
  6          group by deptid);
 
4 rows updated.
 
sql>select deptid, salary, new_salary
  2    from employee
  3   order by deptid, salary desc;
 
   DEPTID    SALARY NEW_SALARY
--------- --------- ----------
        1     75000      60000
        1     70000
        2     80000      64000
        2     42000
        2     40000
        3     90000      72000
        3     45000
        3     41000
        4     95000      76000
        4     40000

Report message to a moderator

Re: decrease 20% for the highest salary paid in every dept. [message #1898 is a reply to message #1870]

Sun, 09 June 2002 08:00

James
Messages: 120
Registered: June 2000

Senior Member

Thanks a lot,

I wonder if we can use "exists" or "(+)" to d othe same job as "in" does in (2)?

James

Report message to a moderator

Previous Topic:	Hierarchical List
Next Topic:	pl/sql

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Wed Apr 24 03:56:32 CDT 2024