Show all screenshots using SQL Querying a Relational Database COMPANY database

For Lab$4,$ you use the Company database that you built in Lab$2$ and used for Lab$3$

$1.$ Update the following new changes into the database:

$1)$ Joyce English with SSN $=453453453$ got married with Joe Anderson. $($Joe is not an employee of the Company and do NOT change the last name of Joyce to Anderson$)$

$2)$ Jenifer Wallace with SSN $=987654321$ just had a new daughter named Erica.

$3)$ Jenifer Wallace with $\backslash (\backslash$operatorname$\{$SSN$\}=987654321\backslash )$ is just assigned to a new project number $\text{'}10\text{'}$ to work on with $0$ initial hours.

Add these new entries into Dependent, Works$\_$On tables in your database then Select $*$ from Dependent and Select $*$ from Works$\_$On to show the updated table content.

Note that if you don't insert these data as directed, your query results for Q$1-$ Q$5$ won't be correct.

$2.$ Write SQL Select statements to retrieve data in the followings:

Q$1$:

For each department, list the first and last name of each employee who is working in the department with the first and last name of his or her immediate supervisor with the department number and name together. Include all the departments including the departments that do not have any employee and all the employees including the ones who do not have any supervisors. List the result in the order of each department number and the first name of each employee.

So$,$ your result of Q$1$ will list all the department $1,4,5,7$ and all the related employees with his$/$her supervisors including the ones who do not have supervisors.

Different version of Q$1$:

Q$1\_1$: List the same information as Q$1$ with a change: List all the employees including the ones who do not have any supervisor, but do not include the departments that do not have any employee in the output.

So$,$ your result of Q$1\_1$ will list the department $1,4,5$ and all the related employees with his$/$her supervisors including the ones who do not have supervisors. So it will be the same as Q$1$ except the department $7$ won't be included.

Q$2$: List the name of managers who have no dependents.

Q$2\_1$:

Get SSN and the last name of married female employees who work on three or more projects Q$3$:

List the name of employees who is working for 'Research' department and are married but have no children.

$--$Married $=$ Select ESSN From Dependent Where relationship $=$ 'spouse';

$--$Girls $=$ Select ESSN From Dependent Where relationship $=$ 'daughter';

$--$Boys $=$ Select ESSN From Dependent Where relationship $=$ 'son';

Q$4$:

Get the last name of married employees who only have daughters.

Married $=$ Select ESSN From Dependent Where relationship $=$ 'spouse';

Girls $=$ Select ESSN From Dependent Where relationship $=$ 'daughter';

Boys $=$ Select ESSN From Dependent Where relationship $=$ 'son';

Q$5$: $($Extra Credit for CIS$430$; Required for CIS$530)$

Give the last name and ssn of those employees who work in any project$($s$)$ where there are more female than male employees. Figure $5\mathrm{.}7$

Referential integrity constraints displayed on the COMPANY relational database schema.

EMPLOYEE

PROJECT

WORKS$\_$ON