SQL developer Query

Question

QUESTION: Write a SQL SELECT statement to display the name and address of all departments (except the departments in Dallas) having maximum number of employees. Sort your output in ascending order by department name.

Creating Department Table

CREATE TABLE department
( DEPARTMENT_ID         NUMBER(4)       PRIMARY KEY,
DEPARTMENT_NAME     VARCHAR2(20)    NOT NULL UNIQUE,  
ADDRESS             VARCHAR2(20)    NOT NULL);

Populating Department Table

INSERT INTO department VALUES(10, 'ACCOUNTING', 'NEW YORK');
INSERT INTO department VALUES(20, 'RESEARCH', 'DALLAS');
INSERT INTO department VALUES(30, 'SALES', 'CHICAGO');
INSERT INTO department VALUES(40, 'IT', 'DALLAS');
INSERT INTO department VALUES(50, 'EXECUTIVE', 'NEW YORK');
INSERT INTO department VALUES(60, 'MARKETING', 'CHICAGO');
COMMIT;

Creating Employee Table

CREATE TABLE employee 
( EMPLOYEE_ID   NUMBER(4)       PRIMARY KEY,
EMPLOYEE_NAME   VARCHAR2(20)    NOT NULL,
JOB         VARCHAR2(50)    NOT NULL,
MANAGER_ID  NUMBER(4),
HIRE_DATE       DATE            NOT NULL,
SALARY      NUMBER(9, 2)    NOT NULL,
COMMISSION  NUMBER(9, 2),
DEPARTMENT_ID   NUMBER(4) REFERENCES department(DEPARTMENT_ID));

Populating Employee Table

INSERT INTO employee 
VALUES(7839, 'KING',  'PRESIDENT', NULL, '20-NOV-01', 5000, NULL, 50);
INSERT INTO employee 
VALUES(7596, 'JOST',  'VICE PRESIDENT', 7839, '04-MAY-01', 4500, NULL, 50);
INSERT INTO employee  
VALUES(7603, 'CLARK', 'VICE PRESIDENT', 7839, '12-JUN-01', 4000, NULL, 50);
INSERT INTO employee 
VALUES(7566, 'JONES', 'PUBLIC ACCOUNTANT', 7596, '05-APR-01', 3000, NULL,         10);
INSERT INTO employee 
VALUES(7886, 'STEEL', 'PUBLIC ACCOUNTANT', 7566, '08-MAR-03', 2500, NULL, 10);
INSERT INTO employee 
VALUES(7610, 'WILSON', 'ANALYST', 7596, '03-DEC-01', 3000, NULL, 20);
INSERT INTO employee  
VALUES(7999, 'WOLFE',  'ANALYST', 7610, '15-FEB-02', 2500, NULL, 20);
INSERT INTO employee  
VALUES(7944, 'LEE', 'ANALYST', 7610, '04-SEP-06', 2400, NULL, 20);
INSERT INTO employee 
VALUES(7900, 'FISHER', 'SALESMAN', 7603, '06-DEC-01', 3000, 500, 30);
INSERT INTO employee 
VALUES(7921, 'JACKSON', 'SALESMAN', 7900, '25-FEB-05', 2500, 400, 30);
INSERT INTO employee 
VALUES(7952, 'LANCASTER', 'SALESMAN',  7900, '06-DEC-06', 2000, 150, 30);
INSERT INTO employee  
VALUES(7910, 'SMITH', 'DATABASE ADMINISTRATOR', 7596, '20-DEC-01', 2900,     NULL, 40);
INSERT INTO employee  
VALUES(7788, 'SCOTT', 'PROGRAMMER', 7910, '15-JAN-03', 2500, NULL, 40);
INSERT INTO employee 
VALUES(7876, 'ADAMS', 'PROGRAMMER', 7910, '15-JAN-03', 2000, NULL, 40);
INSERT INTO employee 
VALUES(7934, 'MILLER','PROGRAMMER', 7876, '25-JAN-02', 1000, NULL, 40);
INSERT INTO employee 
VALUES(8000, 'BREWSTER',  'TBA',  NULL,   '22-AUG-13', 2500, NULL, NULL);
COMMIT;

The Query That i wrote is below and it works perfectly in finding the maximum number of employees in DALLAS department but I want to eliminate DALLAS and find the other department which has the MAXIMUM number of employee's working in it which is by the data are (CHICAGO and NEW YORK). Can anyone please help me out?

SELECT  d.department_id, d.department_name, COUNT(*)
FROM        department d
INNER JOIN  employee e
ON      d.department_id = e.department_id
GROUP BY    d.department_id, d.department_name
HAVING  COUNT(*) = (SELECT  MAX(COUNT(*))
FROM        employee
GROUP BY    department_id) 
ORDER BY    department_id;

dcieslak · Accepted Answer · 2016-04-07 06:00:46Z

1

Using your query:

SELECT  d.department_id, d.department_name, COUNT(*)
FROM        department d
INNER JOIN  employee e
ON      d.deptartment_id = e.department_id
where d.address != 'DALLAS'
GROUP BY    d.department_id, d.department_name
HAVING  COUNT(*) = (SELECT  MAX(COUNT(*))
FROM        employee
where department_id not in (select department_id from department where address = 'DALLAS')
GROUP BY    department_id) 
ORDER BY    department_id;

;

answered Apr 7, 2016 at 6:00

dcieslak

2,7151 gold badge15 silver badges20 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Florin Ghita · Accepted Answer · 2016-04-07 05:52:08Z

0

You can use a rank analytic function:

select *
from (
    select department_id, department_name, rank() over (order by cnt desc) rnk
    from(
        SELECT  d.department_id, d.department_name, COUNT(*) cnt
        FROM        department d
        INNER JOIN  employee e
        ON      d.department_id = e.department_id
        GROUP BY    d.department_id, d.department_name
    )
)
where rnk = 2 --here you may change to what position you want
;

answered Apr 7, 2016 at 5:52

Florin Ghita

17.7k6 gold badges60 silver badges78 bronze badges

Collectives™ on Stack Overflow

SQL developer Query

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related