Sample GROUP BY query 1: restriction on the SELECT attributes  

  • For each department, show the department NAME, number of employees and average salary paid to the employees in the department

  • Sample input relations to make solution concrete: 

     Employee:                                  Answer:

      ssn       dno         salary     --->   dno   #emps   total Sal
      --------- ----------- ---------        ----- ------- -----------
      123456789           5  20000.00          1      1      55000.00
      333445555           5  40000.00          4      3      93000.00
      666884444           5  38000.00	       5      4     133000.00
      453453453           5  25000.00 
      999887777           4  25000.00 
      987654321           4  43000.00          Missing dept name !
      987987987           4  25000.00 
      888665555           1  55000.00 

 Department:

      dname           dnumber   mgrssn      mgrstartdate  
      --------------  -------   ---------   ------------
      Research              5   333445555   22-MAY-78     
      Administration        4   987654321   01-JAN-85     
      Headquarters          1   888665555   19-JUN-71

  Sample GROUP BY query 1: restriction on the SELECT attributes  

  • For each department, show the department NAME, number of employees and average salary paid to the employees in the department

    (1) First, we join employee ⋈ department to get the department name: 

       select ssn, dno, dname, salary		       
   from employee, department		       
   where dno=dnumber		       
   						  
   +-----------+-----+----------------+----------+
   | ssn       | dno | dname          | salary   |
   +-----------+-----+----------------+----------+
   | 123456789 |   5 | Research       | 20000.00 |
   | 333445555 |   5 | Research       | 40000.00 |
   | 666884444 |   5 | Research       | 38000.00 |
   | 453453453 |   5 | Research       | 25000.00 |
   | 999887777 |   4 | Administration | 25000.00 |
   | 987654321 |   4 | Administration | 43000.00 |
   | 987987987 |   4 | Administration | 25000.00 |
   | 888665555 |   1 | Headquarters   | 55000.00 |
   +-----------+-----+----------------+----------+

  Sample GROUP BY query 1: restriction on the SELECT attributes  

  • For each department, show the department NAME, number of employees and average salary paid to the employees in the department

    (1) First, we join employee ⋈ department to get the department name: 

       select ssn, dno, dname, salary		       
   from employee, department		       
   where dno=dnumber		       
   						  
   +-----------+-----+----------------+----------+
   | ssn       | dno | dname          | salary   |
   +-----------+-----+----------------+----------+
   | 123456789 |   5 | Research       | 20000.00 |
   | 333445555 |   5 | Research       | 40000.00 |
   | 666884444 |   5 | Research       | 38000.00 |
   | 453453453 |   5 | Research       | 25000.00 |
   | 999887777 |   4 | Administration | 25000.00 |
   | 987654321 |   4 | Administration | 43000.00 |
   | 987987987 |   4 | Administration | 25000.00 |
   | 888665555 |   1 | Headquarters   | 55000.00 |
   +-----------+-----+----------------+----------+

    Wrong solution: 

        SELECT   dname, COUNT(ssn), AVG(salary)
    FROM     employee, department
    WHERE    dno = dnumber
    GROUP BY dno   // dname is not a grouping attribute !

  Sample GROUP BY query 1: restriction on the SELECT attributes  

  • For each department, show the department NAME, number of employees and average salary paid to the employees in the department

    (1) First, we join employee ⋈ department to get the department name: 

       select ssn, dno, dname, salary		       
   from employee, department		       
   where dno=dnumber		       
   						  
   +-----------+-----+----------------+----------+
   | ssn       | dno | dname          | salary   |
   +-----------+-----+----------------+----------+
   | 123456789 |   5 | Research       | 20000.00 |
   | 333445555 |   5 | Research       | 40000.00 |
   | 666884444 |   5 | Research       | 38000.00 |
   | 453453453 |   5 | Research       | 25000.00 |
   | 999887777 |   4 | Administration | 25000.00 |
   | 987654321 |   4 | Administration | 43000.00 |
   | 987987987 |   4 | Administration | 25000.00 |
   | 888665555 |   1 | Headquarters   | 55000.00 |
   +-----------+-----+----------------+----------+

    Correct solution: 

        SELECT   dname, COUNT(ssn), AVG(salary)
    FROM     employee, department
    WHERE    dno = dnumber
    GROUP BY dno, dname