# There are two tables stu_dept and dept_cap. stu_dept contains the student name and the department(consider distinct values).

(@demonguru)
Joined: 5 years ago
Posts: 36
25/04/2018 2:21 pm

Full Question:

There are two tables stu_dept and dept_cap. stu_dept contains the student name and the department(consider distinct values). dept_cap contains the capacity for each department. We need to find those departments(DEPT) where the number of students is less than the total capacity of the department.

TABLE

CREATE TABLE stu_dept(stu_name VARCHAR2(30), dept VARCHAR2(30));

INSERT INTO stu_dept VALUES('AAA','D1');

INSERT INTO stu_dept VALUES('BBB','D1');

INSERT INTO stu_dept VALUES('CCC','D1');

INSERT INTO stu_dept VALUES('DDD','D1');

INSERT INTO stu_dept VALUES('EEE','D2');

INSERT INTO stu_dept VALUES('FFF','D2');

INSERT INTO stu_dept VALUES('GGG','D2');

INSERT INTO stu_dept VALUES('HHH','D3');

INSERT INTO stu_dept VALUES('III','D3');

INSERT INTO stu_dept VALUES('JJJ','D3');

INSERT INTO stu_dept VALUES('KKK','D3');

INSERT INTO stu_dept VALUES('LLL','D3');

CREATE TABLE dept_cap(dept VARCHAR2(5),capacity NUMBER);

INSERT INTO dept_cap VALUES('D1',5);

INSERT INTO dept_cap VALUES('D2',5);

INSERT INTO dept_cap VALUES('D3',5);

SELECT  a.dept,a.capacity-t.cap remaining_seats

FROM dept_cap a,

(SELECT dept,COUNT(dept)cap FROM stu_dept

GROUP BY dept)t

WHERE a.dept=t.dept

AND t.cap<a.capacity;

DemonGuru18

Share: