DBMS Lab Cycle 3

 3. Consider the following tables namely “Department” and “Employees”

Their schemas ar e as follows

dept (deptNO, deptName, deptLocation)

emp (empId, empName , deptNo, salary)

Where deptNo is the primary key in dept table and empId is primary key in emp table

Where deptNo in Employees is NOT a foreign key.

i. Display the employee name along with their department name (no need to display the

employees with invalid department details)

ii. There are some invalid department numbers (present in employee table but are not

existing in department table), display all employees (along employee name employee id

and their department number) who are part of such invalid department numbers .

iii. Display all the department numbers available with the department and employee tables

avoiding duplicates

iv. Display department numbers and name of all the departments where no employee exists.

v. Display department number, employee id and employee name of employees working at

location Cochin

vi. Display the details of those who draw the salary greater than the average salary of

employee’s from sales department

vii. The names of employees whose salary is greater than the salary of all the employees in

department 1003

viii. Give all employees in the 'Sales' department a 20% raise i n salary.

ix. Retrieve the ids of employees getting salary greater than the average
salary of their department.

x. For each department that has more than 1 employee, retrieve the
department-No and the number of employees getting salary more
than Rs. 40000.

 

TEST CASES

mysql> select * from emp;

+-------+---------+--------+--------+

| empId | empName | deptNo | salary |

+-------+---------+--------+--------+

|  2001 | Hari    |   1006 |  30000 |

|  2002 | Devi    |   1002 |  40000 |

|  2003 | Gupta  |   1003 |  50000 |

|  2004 | Ravi    |   1004 |  60000 |

|  2005 | Rani    |   1007 |  70000 |

|  2006 | John    |   1003 |  40000 |

+-------+---------+--------+--------+

6 rows in set (0.39 sec)

mysql> select * from dept;

+--------+-------------+--------------+

| deptNo | deptName    | deptLocation |

+--------+-------------+--------------+

|   1001 | HR                   | Calicut      |

|   1002 | Marketing        | Cochin       |

|   1003 | Sales                | Trivandrum   |

|   1004 | Testing             | Bangalore    |

|   1005 | Development   | Bangalore    |

+--------+-------------+--------------+

5 rows in set (0.01 sec)