Database Management system

Instructions Print the lab assignment and do all the questions given below, on a paper. . Q.1 00 JOB 00 ASSIGNMENT ASSIGN NUM ASSIGN_DATE PROJ_NUM EMP_NUM ASSIGN_JOB ASSIGN_CHG_HR ASSIGN_HOURS ASSIGN_CHARGE JOB_CODE JOB_DESCRIPTION JOB_CHG_HOUR JOB_LAST_UPDATE EMPLOYEE EMP_NUM EMP_L.NAME EMP_FNAME EMP_INITIAL EMP_HIREDATE JOB_CODE PROJECT PROJ_NUM PROJ_NAME PROJ_VALUE PROJ_BALANCE EMP_NUM 00 Making use of the 4 tables with data. Table : Job : job_code I job_desc I job_chg_hr i job_last_update : 500 | Programmer 35.75 | 2009-11-20 501 I system analyst 96.75 | 2009-11-20 502 I database designer 125.00 | 2010-03-20 503 I electrical engineer! 84.50 | 2009-11-20 I 504 I mechanical engineer 67.50 1 2009-11-20 505 | civil engineer 55.00 | 2010-03-20 506 | clerical support 26.87 1 2009-11-20 Table : Employee emp_num 1 emp_lname I emp_fname i emp_initial i emp_hiredate i job_code 101 | News 1 John 102 1 senior I david 103 I adin I june 104 | Ramoras Ianne 105 1 johnson I alice 106 I smithfield I william 107 | alzo I maria IG I TE IK I NULL IE I NULL I 2000-11-08 I 1989-07-12 I 1996-01-09 I 1987-11-15 I 1993-02-10 I 2004-06-22 | 1987-11-15 502 501 I 503 501 I 502 500 500 L I I Table : Project emp_num I proj_num i project_name i proj_value 15 I evergreen 999.9991 18 I amber wave 1 999.9991 22 rolling tide! 999.999 25 I starlight I 999.999 103 I 105 102 101 1 Table : Assignment I assign_num i assign_date | proj_num i emp_num 1 assign_job I assign_chg_hr I a ssign_hours 1 assign_charge 1 18 | 101 1 5021 125.00 I 15 I 1021 501 96.75 I 18 I 103 T 503 84.50 1 I 15 I 103 503 84.50 1001 | 2010-03-22 21 875.000 L 1002 | 2010-01-01 I 5 483.750 L 1003 | 2011-11-21 I 8 626.000 1004 I 2012-10-31 8 626.000 1005 | 2012-10-31 10 L 999.999 I 1006 I 2012-10-31 I 6 214.500 I 1007 1 2010-09-20 I 6 I 214.500 I 22 105 I 502 I 125.00 I 25 106 1 5001 35.75 I 18 I 106 I 500 35.751 Once done, write the sql code for each and every table in the blank A4 provided. 1. Write an SQL code to show all data[complete record] for job code 502 from job table 2. Write an SQL code to show employee first name and last name from the employee table 3. Write an SQL to show all jobs whose job_chg_hr is more than 90 from the job table 4. Write an SQL code to find all employee whose first name starts with 'A' from employee table. 5. Write an SQL code to list only the distinct job codes found in employee table. 6. Write an SQL code to find the product of assign_chg_hr and assign_hours from assignment table 7. Write an SQL code to find the sum total of assign hours from assignment table 8. Write an SQL code to get a count the number jobs from job table 9. Write an SQL code to show output - project name, employee name and the job description 10. Write an SQL to show the project num and the corresponding employees attached to it, output below proj_num 1 group_concat(emp_num) 15 | 102,103 18 | 101,103,106 22 1105 25 | 106 11. Write an SQL to show the project name and the corresponding employee names as shown below - project_nane i group_concat(employee.emp_fname) ! evergreen I june, david anber wave I John, june, willian rolling tide 1 alice starlight I william 12. Write an SQL code to show output to show employee name, employee hire date, job description and job charge hour 13. Write an SQL code to show all the employees (with employee id) in each project. 14. Write an SQL code to show the employees (with employee id) of project - 'evergreen' 15. Write an SQL code to show all the assignments of a System Analyst. 16. Show all the projects where the assignment date is greater than 2009-09-01 17. Write a query to show the employee name, assigned hours, assigned charges and project name for the 'programmer