Consider the following relations with their attributes: EMPLOYEE(fname,Iname.ssn.sex,salary,super_ssn.dno,address,birth_date) DEPENDENT(essn.dependent name,sex, relationship,birth_date) DEPARTMENT(Dname, Dnumber, Mgr_ssn.Mgr_start_date) PROJECT(Pname Pnumber,Plocation,...

 

Transcribed Image Text:

Consider the following relations with their attributes: EMPLOYEE(fname,Iname.ssn.sex,salary,super_ssn.dno,address,birth_date) DEPENDENT(essn.dependent name,sex, relationship,birth_date) DEPARTMENT(Dname, Dnumber, Mgr_ssn.Mgr_start_date) PROJECT(Pname Pnumber,Plocation, Dnum) WORKS ON(Esso.Png Hours) ssn stands for social security number of an employee, super_ssn stands for supervisor's ssn, essn stands for employee's ssn. ssn is the primary key of EMPLOYEE relation and essn is the foreign key of DEPENDENT relation referring to the EMPLOYEE relation. Mgr ssn is manager ssn which is a Foreign Key(FK) in DEPARTMENT table and referring to EMPLOYEE table, Dnum is FK in PROJECT table referring to DEPARTMENT table. Pname stands for project name. Essn is FK in WORKS ON table referencing primary key of EMPLOYEE table and Pno is also FK in WORKS ON table referencing Pnumber in PROJECT table. Mgr_start_year is the year when a manager started managing the corresponding department. Write the queries in SQL to achieve the following: a. Find out the number of employees working under each department. [2] b. Retrieve names of such employees who earn in between 60000 to 80000 and have male dependents. [3] c. Find the salary differences between the highest paid employee and the lowest paid one. [2] d. Select those projects where number of employees working are more than five. [2] e. Write the sql statement to define table DEPENDENT as mentioned above. [3] Note: Consider that the database has a proper database instance to process the above queries. Consider the following relations with their attributes: EMPLOYEE(fname,Iname.ssn.sex,salary,super_ssn.dno,address,birth_date) DEPENDENT(essn.dependent name,sex, relationship,birth_date) DEPARTMENT(Dname, Dnumber, Mgr_ssn.Mgr_start_date) PROJECT(Pname Pnumber,Plocation, Dnum) WORKS ON(Esso.Png Hours) ssn stands for social security number of an employee, super_ssn stands for supervisor's ssn, essn stands for employee's ssn. ssn is the primary key of EMPLOYEE relation and essn is the foreign key of DEPENDENT relation referring to the EMPLOYEE relation. Mgr ssn is manager ssn which is a Foreign Key(FK) in DEPARTMENT table and referring to EMPLOYEE table, Dnum is FK in PROJECT table referring to DEPARTMENT table. Pname stands for project name. Essn is FK in WORKS ON table referencing primary key of EMPLOYEE table and Pno is also FK in WORKS ON table referencing Pnumber in PROJECT table. Mgr_start_year is the year when a manager started managing the corresponding department. Write the queries in SQL to achieve the following: a. Find out the number of employees working under each department. [2] b. Retrieve names of such employees who earn in between 60000 to 80000 and have male dependents. [3] c. Find the salary differences between the highest paid employee and the lowest paid one. [2] d. Select those projects where number of employees working are more than five. [2] e. Write the sql statement to define table DEPENDENT as mentioned above. [3] Note: Consider that the database has a proper database instance to process the above queries.