database - sql

this is figure 5.5

In SQL, specify the following queries on the COMPANY database in Figure 5.5 using the concept of nested queries and other concepts described in this chapter. a. Retrieve the names of all employees who work in the department that has the employee with the highest salary among all employees. b. Retrieve the names of all employees whose supervisor's supervisor has '888665555' for Ssn. c. Retrieve the names of employees who make at least $10,000 more than the employee who is paid the least in the company 2. Specify the following views in SQL on the COMPANY database schema shown in Figure 5.5. A view that has the department name, manager name, and manager salary for every department b. A view that has the employee name, supervisor name, and employee salary for each employee who works in the 'Research' department A view that has the project name, controlling department name, number of employees, and total hours worked per week on the project for each project d. A view that has the project name, controlling department name, number of employees, and total hours worked per week on the project for each project with more than one employee working on it 3. Discuss how NULLs are treated in comparison operators in SQL. How are NULLS treated when aggregate functions are applied in an SQL query? How are NULLS treated if they exist in grouping attributes? EMPLOYEE Fname Minit Lname Ssn Bdate Address Sex Salary Super_ssn Dno DEPARTMENT Dname Dnumber Mgr_ssn Mgr_start_date DEPT_LOCATIONS Dnumber Dlocation PROJECT Pname Pnumber Plocation Dnum WORKS ON Essn Pno Hours DEPENDENT Essn Dependent_name Figure 5.5 Schema diagram for the COMPANY relational database schema. Sex Bdate Relationship 4. Map the below BANK ER schema into a relational schema. Specify all primary keys and foreign keys. BANK BRANCHES BANK BRANCH Code Name Addr Addr Branch no ACCTS LOANS IN Acct_no IN Balance Loan_no Amount ACCOUNT Type LOAN Type M M AC LC N San N Name Phone CUSTOMER Addr