Department(DeptNum, Descrip, Instname, DeptName, State, Postcode) Academic(AcNum

Question

Department(DeptNum, Descrip, Instname, DeptName, State, Postcode)
Academic(AcNum, DeptNum*, FamName, GiveName, Initials, Title)
Paper(PaNum, Title)
Author(PaNum*, AcNum*)
Field(FieldNum, ID, Title)
Interest(FieldNum*, AcNum*, Descrip)

4. Explain the following SQL query in English;
select deptnum
from academic A1
where title like 'Dr%'
group by DEPTNUM
having count(*)>0.5 * (select count(*)
from academic A2
where A1.deptnum=A2.deptnum
group by deptnum)
and exists (select *
from academic A3
where A3.DEPTNUM=A1.DEPTNUM
group by deptnum
having count(*)>20);

Explanation / Answer

select deptnum from academic A1
   where title like 'Dr%'
   group by DEPTNUM
   having count(*)>0.5 *
   (
       select count(*)
           from academic A2
           where A1.deptnum=A2.deptnum
           group by deptnum
   )
   and exists
   (
       select *
           from academic A3
           where A3.DEPTNUM=A1.DEPTNUM
           group by deptnum
           having count(*)>20
   );
  
   Lets break down the query.
   ->First subquery 'select count(*) from academic A2 where A1.deptnum=A2.deptnum group by deptnum'
   returns half the number of records with the department number = deptnum in Academic table.
   ->Second subquery 'select *   from academic A3   where A3.DEPTNUM=A1.DEPTNUM   group by deptnum having count(*) > 20'
   returns all the records where the number of records with the department number = deptnum is greater than 20 in Academic table
      
       The whole query returns all departments which has atleast half of the titles as Dr and it has atleast 20 records for that department.

Related Questions

Navigate

• Browse (All)
• Browse D
• Subjects

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