Sheet 5 - Problem 3

--Creating tables
CREATE TABLE emp_department (DPT_CODE INT PRIMARY KEY, DPT_NAME VARCHAR(10) NOT NULL, DPT_ALLOTMENT MONEY NOT NULL);
CREATE TABLE emp_details (EMP_IDNO INT PRIMARY KEY, EMP_FNAME VARCHAR(30) NOT NULL, EMP_LNAME VARCHAR(30) NOT NULL, emp_dept INT FOREIGN KEY REFERENCES emp_department(DPT_CODE));

--Populating emp_department table:
INSERT INTO emp_department VALUES (57, 'IT', 65000);
INSERT INTO emp_department VALUES (63, 'Finance', 15000);
INSERT INTO emp_department VALUES (47, 'HR', 240000);
INSERT INTO emp_department VALUES (27, 'RD', 55000);
INSERT INTO emp_department VALUES (89, 'QC', 75000);

--Populating emp_details table:
INSERT INTO emp_details VALUES (127323, 'Michale', 'Robbin', 57);
INSERT INTO emp_details VALUES (526689, 'Carlos', 'Snares', 63);
INSERT INTO emp_details VALUES (843795, 'Enric', 'Dosio', 57);
INSERT INTO emp_details VALUES (328717, 'Jhon', 'Snares', 63);
INSERT INTO emp_details VALUES (444527, 'Joseph', 'Dosni', 47);
INSERT INTO emp_details VALUES (659831, 'Zanifer', 'Emily', 47);
INSERT INTO emp_details VALUES (847674, 'Kuleswar', 'Sitaraman', 57);
INSERT INTO emp_details VALUES (748681, 'Henrey', 'Gabriel', 47);
INSERT INTO emp_details VALUES (555935, 'Alex', 'Manuel', 57);
INSERT INTO emp_details VALUES (539569, 'George', 'Mardy', 27);
INSERT INTO emp_details VALUES (733843, 'Mario', 'Saule', 63);
INSERT INTO emp_details VALUES (631548, 'Alan', 'Snappy', 27);
INSERT INTO emp_details VALUES (839139, 'Maria', 'Foster', 57);

--Query a) Write a query in SQL to find the names of departments where more than two employees are working:
SELECT D.DPT_NAME FROM emp_department D, emp_details E WHERE E.emp_dept = D.DPT_CODE GROUP BY D.DPT_NAME HAVING COUNT(D.DPT_NAME) > 2;
SELECT DPT_NAME FROM emp_department D INNER JOIN emp_details E ON E.emp_dept = D.DPT_CODE GROUP BY D.DPT_NAME HAVING COUNT(D.DPT_NAME) > 2;