1. Add a statement to your script to display the empssn and empLastName columns from the employee table and the depName and deprelationship columns from the dependent table for all employees with dependents. Use appropriate column aliases. Use a JOIN statement (not WHERE). You should have 13 rows. Using the statement you wrote in the previous question, create a view called vwEmpDep. Write and execute the statement to display all the records in the vwEmpDep view.

2. Add a statement to your script to list all employees assigned to projects. Include the employee name (first and last concatenated with a space between), pronumber, and proname, from the employee and project tables (refer to the diagram provided in Week 6). Use meaningful column aliases. Use a JOIN statement (not WHERE). You should have 17 rows. Using the statement you wrote in the previous question, create a view called vwEmpPro. Write and execute the statement to display all the records in the vwEmpPro view.

3. Write and execute the statement to show all the tables and views in your Company Database. Write and execute the statement to create a stored procedure named spEmpCity. This procedure should give a list of all the different cities where employees live. Each city should be listed only once. Execute the spEmpCity procedure. You should have 5 rows. Using SHOW CREATE VIEW display the structure of the vwEmpDep view and the vwEmpPro view.

4. Write and execute the statement to create a stored procedure named spSpecificCity that will give a listing of employees (ssn, lastname, address,city, state, zip) living in a specified city. The specific city will be provided in the call statement (hint: use a variable). Use appropriate column labels. Execute the spSpecificCity procedure using the city value of Marina. You should have 2 rows.

- ql describe erployee: l Type Field l> describe department: v char(25 varchar erplastane erpFirstHane rpMiddlelane orpAddress Field l Type Null I Key I Default Extra dptNane dptHor SSN mallint(6) O varchar(20) NO char'9) TES ULL dptHgrStartDate datetine erpsalary erpParkingSpace float(8,2 ND int( describe project; seal Tint(6) ch (9) YES Fiold l Type I Null I Key Default Extra q1> describe deptlocations: l Type proLocation Ivarch 25 proDeptNunbesnallint() YES snallint ( NO PRI NULL varchar(25 YES NULL l Field I Null I Key | DefaulI Extra l PRIN PRINULL dptlocat varchar ql describe equipment l Nul1 I Key I Default Extra ql describe dependent l Type har (4) INu11 1 Key i Default l Extra 111 eqpbescription | varchar(s) YES float(i0,2) YES eqpNo l Field char (9) PRI NULL eqpvalua depEmpSSN depName NO PRI NULL NULL poer atvVES egpTotalvalue float(10,2 YES char (1) depDatoofBirth datetine YES nallint depRelationship varchar(10)YES q1s describe assigreent Field workEmpSSN l Type I Nu11 Ikey I Default Extra 1 PRI NLLL PRI NULL workProNumber decinal workHoursPlanned decimals EMPLOYEE Project Equipment - ql describe erployee: l Type Field l> describe department: v char(25 varchar erplastane erpFirstHane rpMiddlelane orpAddress Field l Type Null I Key I Default Extra dptNane dptHor SSN mallint(6) O varchar(20) NO char'9) TES ULL dptHgrStartDate datetine erpsalary erpParkingSpace float(8,2 ND int( describe project; seal Tint(6) ch (9) YES Fiold l Type I Null I Key Default Extra q1> describe deptlocations: l Type proLocation Ivarch 25 proDeptNunbesnallint() YES snallint ( NO PRI NULL varchar(25 YES NULL l Field I Null I Key | DefaulI Extra l PRIN PRINULL dptlocat varchar ql describe equipment l Nul1 I Key I Default Extra ql describe dependent l Type har (4) INu11 1 Key i Default l Extra 111 eqpbescription | varchar(s) YES float(i0,2) YES eqpNo l Field char (9) PRI NULL eqpvalua depEmpSSN depName NO PRI NULL NULL poer atvVES egpTotalvalue float(10,2 YES char (1) depDatoofBirth datetine YES nallint depRelationship varchar(10)YES q1s describe assigreent Field workEmpSSN l Type I Nu11 Ikey I Default Extra 1 PRI NLLL PRI NULL workProNumber decinal workHoursPlanned decimals EMPLOYEE Project Equipment