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

  RSS

Demon
(@demonguru)
Member Admin
Joined:5 years  ago
Posts: 40
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);


Answer

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


ReplyQuote
Share: