.
MI NIT SA- LARY FNAME LNAME SSN SEX SUPERSSN DNO HIREDATE DNAME John B Smith 123456789 M 30000 333445555 5 9-Jan-88 Research Franklin T Wong 333445555 M 40000 888665555 5 8-Dec-88 Research Alicia J Zelaya 999887777 F 25000 987654321 4 19-Jul-88 Administration Jennifer s Wallace 987654321 F 43000 888665555 4 20-Jun-89 Administration BDATE ADDRESS 731 Fondren, 9-Jan-55 Houston, TX 638 Voss, 8-Dec-45 Houston, TX 3321 Castle, 19-Jul-58 Spring, TX 291 Berry, 20-Jun-31 Bellaire, TX 975 Fire 15-Sep- Oak, 52 Humble, TX 5631 Rice, 31-Jul-62 Houston, TX 29-Mar- 980 Dallas, 59 Houston, TX 10-Nov- 450 Stone, 27 Houston, TX 23-Mar- 77 NY, NY Ramesh K Narayan 666884444 M 38000 333445555 5 15-Sep-89 Research Joyce A English 453453453 F 25000 333445555 5 31-Jul-89 Research Ahmad V Jabbar 987987987 M 25000 987654321 4 29-Mar-89 Administration James E Borg 888665555 M 55000 1 10-Nov-87 Headquarters New V Lost 777977977 M 20000 987654321 4 23-Mar-88 Administration Q1. Generate an analytic query to view not only staff salaries, but also the salary of the person hired after them as shown below. ANS: OUTPUT: NAME ADDRESS HIREDATE SALARY Next Salary James E Borg 450 Stone, Houston, TX 10-Nov-87 55000 30000 John B Smith 731 Fondren, Houston, TX 9-Jan-88 30000 20000 New V Lost NY, NY 23-Mar-88 20000 25000 Alicia. Zelaya 3321 Castle, Spring, TX 19-Jul-88 25000 40000 Franklin T Wong 638 Voss, Houston TX 8-Dec-88 40000 25000 Ahmad V Jabbar 980 Dallas, Houston, TX 29-Mar-89 25000 43000 Jennifer S Wallace 291 Berry, Bellaire. TX 20-Jun-89 43000 25000 Joyce A English 5631 Rice, Houston, TX 31-Jul-89 25000 38000 Ramesh K Narayan 975 Fire Oak, Humble.TX 15-Sep-89 38000 Q2. Generate an analytic query to compare each employee's salary to the average salary within his/her department as shown below. ANS: OUTPUT: NAME James E Borg Ahmad V Jabbar Alicia. Zelaya Jennifer S Wallace New V Lost Joyce A English Franklin I Wong John B Smith Ramesh K Narayan ADDRESS 450 Stone, Houston, TX 980 Dallas, Houston TX 3321 Castle, Spring, TX 291 Berry, Bellaire TX NY, NY 5631 Rice, Houston, TX 638 Voss, Houston TX 731 Fondren, Houston TX 975 Fire Oak, Humble, TX HIREDATE 10-Nov-87 29-Mar-89 19-Jul-88 20-Jun-89 23-Mar-88 31-Jul-89 8-Dec-88 9-Jan-88 15-Sep-89 SALARY 55000 25000 25000 43000 20000 25000 40000 30000 38000 DNO Dept Average 1 55000 4 28250 4 28250 4 28250 4 28250 5 33250 5 33250 5 33250 5 33250 1/5 HW2 Q3. Generate an analytic query to how the average salary changed by department with each new hire as shown below. ANS: OUTPUT: DNO SALARY 55000 30000 NAME James E Borg John B Smith New V Lost Alicia. Zelaya Franklin T Wong Ahmad V Jabbar Jennifer S Wallace Joyce A English Ramesh K Narayan ADDRESS 450 Stone, Houston TX 731 Fondren, Houston, TX NY, NY 3321 Castle, Spring, TX 638 Voss, Houston, TX 980 Dallas, Houston TX 291 Berry, Bellaice IX 5631 Rice, Houston, TX 975 Fire Oak, Humble.TX HIREDATE 10-Nov-87 9-Jan-88 23-Mar-88 19-Jul-88 8-Dec-88 29-Mar-89 20-Jun-89 31-Jul-89 15-Sep-89 20000 25000 40000 25000 43000 25000 38000 Dept Average 1 55000 5 30000 4 20000 4 22500 5 35000 423333.33 4 28250 531666.67 5 33250 Q4. Generate an analytic query to list all the employees in the company alongside the name of the person hired in their department right before them as shown below ANS: OUTPUT: Previous Hire DNO 1 4 NAME James E Borg New V Lost Alicia. Zelaya Ahmad V Jabbar Jennifer S Wallace John B Smith Franklin T Wong Joyce A English Ramesh K Narayan ADDRESS 450 Stone, Houston, TX NY, NY 3321 Castle, Spring TX 980 Dallas, Houston, TX 291 Berry, Bellaire. TX 731 Fondren, Houston, TX 638 Voss, Houston TX 5631 Rice, Houston, TX 975 Fire Oak, Humble, TX HIREDATE 10-Nov-87 23-Mar-88 19-Jul-88 29-Mar-89 20-Jun-89 9-Jan-88 8-Dec-88 31-Jul-89 15-Sep-89 4 New V Lost 4 Alicia) Zelaya 4 Ahmad V Jabbar 5 5 John B Smith 5 Franklin T Wong 5 Joyce A English MI NIT SA- LARY FNAME LNAME SSN SEX SUPERSSN DNO HIREDATE DNAME John B Smith 123456789 M 30000 333445555 5 9-Jan-88 Research Franklin T Wong 333445555 M 40000 888665555 5 8-Dec-88 Research Alicia J Zelaya 999887777 F 25000 987654321 4 19-Jul-88 Administration Jennifer s Wallace 987654321 F 43000 888665555 4 20-Jun-89 Administration BDATE ADDRESS 731 Fondren, 9-Jan-55 Houston, TX 638 Voss, 8-Dec-45 Houston, TX 3321 Castle, 19-Jul-58 Spring, TX 291 Berry, 20-Jun-31 Bellaire, TX 975 Fire 15-Sep- Oak, 52 Humble, TX 5631 Rice, 31-Jul-62 Houston, TX 29-Mar- 980 Dallas, 59 Houston, TX 10-Nov- 450 Stone, 27 Houston, TX 23-Mar- 77 NY, NY Ramesh K Narayan 666884444 M 38000 333445555 5 15-Sep-89 Research Joyce A English 453453453 F 25000 333445555 5 31-Jul-89 Research Ahmad V Jabbar 987987987 M 25000 987654321 4 29-Mar-89 Administration James E Borg 888665555 M 55000 1 10-Nov-87 Headquarters New V Lost 777977977 M 20000 987654321 4 23-Mar-88 Administration Q1. Generate an analytic query to view not only staff salaries, but also the salary of the person hired after them as shown below. ANS: OUTPUT: NAME ADDRESS HIREDATE SALARY Next Salary James E Borg 450 Stone, Houston, TX 10-Nov-87 55000 30000 John B Smith 731 Fondren, Houston, TX 9-Jan-88 30000 20000 New V Lost NY, NY 23-Mar-88 20000 25000 Alicia. Zelaya 3321 Castle, Spring, TX 19-Jul-88 25000 40000 Franklin T Wong 638 Voss, Houston TX 8-Dec-88 40000 25000 Ahmad V Jabbar 980 Dallas, Houston, TX 29-Mar-89 25000 43000 Jennifer S Wallace 291 Berry, Bellaire. TX 20-Jun-89 43000 25000 Joyce A English 5631 Rice, Houston, TX 31-Jul-89 25000 38000 Ramesh K Narayan 975 Fire Oak, Humble.TX 15-Sep-89 38000 Q2. Generate an analytic query to compare each employee's salary to the average salary within his/her department as shown below. ANS: OUTPUT: NAME James E Borg Ahmad V Jabbar Alicia. Zelaya Jennifer S Wallace New V Lost Joyce A English Franklin I Wong John B Smith Ramesh K Narayan ADDRESS 450 Stone, Houston, TX 980 Dallas, Houston TX 3321 Castle, Spring, TX 291 Berry, Bellaire TX NY, NY 5631 Rice, Houston, TX 638 Voss, Houston TX 731 Fondren, Houston TX 975 Fire Oak, Humble, TX HIREDATE 10-Nov-87 29-Mar-89 19-Jul-88 20-Jun-89 23-Mar-88 31-Jul-89 8-Dec-88 9-Jan-88 15-Sep-89 SALARY 55000 25000 25000 43000 20000 25000 40000 30000 38000 DNO Dept Average 1 55000 4 28250 4 28250 4 28250 4 28250 5 33250 5 33250 5 33250 5 33250 1/5 HW2 Q3. Generate an analytic query to how the average salary changed by department with each new hire as shown below. ANS: OUTPUT: DNO SALARY 55000 30000 NAME James E Borg John B Smith New V Lost Alicia. Zelaya Franklin T Wong Ahmad V Jabbar Jennifer S Wallace Joyce A English Ramesh K Narayan ADDRESS 450 Stone, Houston TX 731 Fondren, Houston, TX NY, NY 3321 Castle, Spring, TX 638 Voss, Houston, TX 980 Dallas, Houston TX 291 Berry, Bellaice IX 5631 Rice, Houston, TX 975 Fire Oak, Humble.TX HIREDATE 10-Nov-87 9-Jan-88 23-Mar-88 19-Jul-88 8-Dec-88 29-Mar-89 20-Jun-89 31-Jul-89 15-Sep-89 20000 25000 40000 25000 43000 25000 38000 Dept Average 1 55000 5 30000 4 20000 4 22500 5 35000 423333.33 4 28250 531666.67 5 33250 Q4. Generate an analytic query to list all the employees in the company alongside the name of the person hired in their department right before them as shown below ANS: OUTPUT: Previous Hire DNO 1 4 NAME James E Borg New V Lost Alicia. Zelaya Ahmad V Jabbar Jennifer S Wallace John B Smith Franklin T Wong Joyce A English Ramesh K Narayan ADDRESS 450 Stone, Houston, TX NY, NY 3321 Castle, Spring TX 980 Dallas, Houston, TX 291 Berry, Bellaire. TX 731 Fondren, Houston, TX 638 Voss, Houston TX 5631 Rice, Houston, TX 975 Fire Oak, Humble, TX HIREDATE 10-Nov-87 23-Mar-88 19-Jul-88 29-Mar-89 20-Jun-89 9-Jan-88 8-Dec-88 31-Jul-89 15-Sep-89 4 New V Lost 4 Alicia) Zelaya 4 Ahmad V Jabbar 5 5 John B Smith 5 Franklin T Wong 5 Joyce A English
