SQL Question; CREATE TABLE department (DEPARTMENT_ID NUMBER (4) PRIMARY KEY, DEP

Question

SQL Question;

CREATE TABLE department

(DEPARTMENT_ID          NUMBER (4)        PRIMARY KEY,

DEPARTMENT_NAME       VARCHAR2(20)      NOT NULL UNIQUE,

LOCATION              VARCHAR2(20)      NOT NULL);

CREATE TABLE employee

(EMPLOYEE_ID      NUMBER (4)        PRIMARY KEY,

EMPLOYEE_NAME   VARCHAR2(20)      NOT NULL,

JOB_TITLE       VARCHAR2(50)      NOT NULL,

MANAGER_ID      NUMBER (4)

REFERENCES employee(EMPLOYEE_ID) ON DELETE SET NULL,

HIRE_DATE       DATE             NOT NULL,

SALARY          NUMBER (9, 2)     NOT NULL,

COMMISSION      NUMBER (9, 2),

DEPARTMENT_ID   NUMBER (4) REFERENCES department(DEPARTMENT_ID));

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;

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', 'CHIEF 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', 'BUSINESS ANALYST', 7596, '03-DEC-01', 3000, NULL, 20);

INSERT INTO employee VALUES (7999, 'WOLFE’, ‘TEST ANALYST', 7610, '15-FEB-02', 2500, NULL, 20);

INSERT INTO employee VALUES (7944, 'LEE', 'REPORTING ANALYST', 7610, '04-SEP-06', 2400, NULL, 20);

INSERT INTO employee VALUES (7900, 'FISHER', 'SALES EXECUTIVE', 7603, '06-DEC-01', 3000, 500, 30);

INSERT INTO employee VALUES (7921, 'JACKSON', 'SALES REPRESENTATIVE', 7900, '25-FEB-05', 2500, 400, 30);

INSERT INTO employee VALUES (7952, 'LANCASTER', 'SALES CONSULTANT’, 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;

Write a SQL SELECT statement to display the name and location of all departments (except the departments located in Dallas) with the highest number of employees . You cannot use join operations in your SELECT statement. Hard coding, except the string 'DALLAS', is not allowed in your SELECT statement. Sort your output in ascending order by department name. # of Employees in each Department. No more than one SELECT statement. Hint: Department Name ACCOUNTING EXECUTIVE IT MARKETING RESEARCH SALES Location # of Employees NEW YORK NEW YORK DALLAS CHICAGO DALLAS CHICAGO 3

Explanation / Answer

SELECT department.DEPARTMENT_ID, department.DEPARTMENT_NAME, COUNT(*) FROM department INNER JOIN employee ON department.DEPARTMENT_ID = employee.DEPARTMENT_ID where department.LOCATION != 'DALLAS' GROUP BY deparment.DEPARTMENT_ID,department. DEPARTMENT_NAME HAVING COUNT(*) = (SELECT MAX(COUNT(*))FROM employee where DEPARTMENT_ID not in (select DEPARTMENT_ID from department where LOCATION = 'DALLAS') GROUP BY DEPARTMENT_ID) ORDER BY DEPARTMENT_ID;

Related Questions

Navigate

• Browse (All)
• Browse S
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.