Programming language: SQL Tool: Oracle SQL Developer   The database schema Employee (Lastname, FirstName, MidInitial, gender, street, city) Works (Lastname, FirstName, MidInitial, company-name, salary) company (company-name, city) company (company-name, city) Manages(Lastname, FirstName, MidInitial, ManagerLastname, MFirstName, MMidInitial, start-date)   Manages(Lastname, FirstName, MidInitial, ManagerLastname, MFirstName, MMidInitial, start-date) employee_table (you should define three attributes for employee and manager names) Name                           street          city           gender -------------------------------------------------------- Anderson, Susan L     108th        Omaha       F Brady, Dan L               P street     Lincoln       M Chen, Peter K             124th         Omaha       M Clemson, Ann M        O              Lincoln      F Dale, Mary K               132th        Omaha       F Gill, Mary L                P street      Lincoln       F Harrison, Susan M      Old Mill          Omaha F Jackson, Kim A           178th         Omaha     F Jason, Pat M                 8th             C.Bluffs    M Kumar, Paul T           Dodge           Omaha     M Orr, Susanne S          Q               Omaha   F Peterson, Mike E      Blondo          Omaha     M Powell, Tom E         Broadway        C.Bluffs  M Quinn, Jacky M        168st           Omaha     F Rand, Pam R           13st            Omaha     F Schreck, Ann P        214st           Elkhorn   F Simon, Eric K         77th            Lincoln   M Smith, John A         8th             C.Bluffs  M Thomson, Ron F        Farnam          Omaha     M Young, Don R          Dodge           Omaha     M Wong, Carole S        Broadway        C.Bluffs  F   works_table: (Again, you should define three attributes for employee name) employee_name         company_name      salary ------------------------------------------------ Anderson, Susan L     Mutual of Omaha   48000 Brady, Dan L          FDR               42000 Chen, Peter K             FDR                 53000 Clemson, Ann M        First Bank        39000 Dale, Mary K          Mutual of Omaha   58000 Gill, Mary L                Union Pacific  48700 Harrison, Susan M      Union Pacific  54320  Jackson, Kim A        FDR               68000 Jason, Pat M          FDR               83000 Kumar, Paul T          FDR               44000 Orr, Susanne S          Lincoln Star      32000 Peterson, Mike E      First Bank        37000 Powell, Tom E         First Bank        54000 Quinn, Jacky M        First Bank        85000 Rand, Pam R           FDR               37000 Schreck, Ann P        First Bank        36000 Simon, Eric K         Mutual of Omaha   45000 Smith, John A         FDR               55000 Thomson, Ron F        First Bank        39000 Young, Don R          Mutual of Omaha   27000 Wong, Carole S         First Bank        44000   company_table: company_name          city ----------------------------- First Bank                   Omaha FDR                            Omaha Lincoln Star                Lincoln Mutual of Omaha       Omaha Union Pacific              Omaha   Manages (Note: both employee and manager names should include three attributes): employee_name         manager_name      start_date ----------------------------------------------------- Anderson, Susan L     Dale, Mary K      15-SEP-2002 Brady, Dan L          Jason, Pat M      05-MAR-2004 Chen, Peter K            Jason, Pat M      12-JUNE-2003 Clemson, Ann M        Powell, Tom E     30-AUG-2001 Gill, Mary L                Harrison, Susan M      25-JAN-2003 Jackson, Kim A        Jason, Pat M      09-MAY-2008 Kumar, Paul T           Jason, Pat M      09-MAY-2001 Rand, Pam R           Smith, John A     15-JAN-2004 Peterson, Mike E      Powell, Tom E     30-AUG-2006 Schreck, Ann P        Quinn, Jacky M    28-FEB-1996 Simon, Eric K         Anderson, Susan L 22-FEB-2004 Smith, John A         Jason, Pat M      05-MAR-2003 Thomson, Ron F        Powell, Tom E     30-AUG-1998 Wong, Carole S        Quinn, Jacky M    28-FEB-2001    Young, Don R          Dale, Mary K      15-SEP-2007 For those employees whose last name initial is not a character “C” and who earn less than the average salary of all employees of their own companies, find their full names and cities they live. Find the average number of employees for all companies. (a) Give all managers a 4% pay raise while give all other employees a 6% pay raise (do not use the view ManagerInfo defined above) and display the result.

Programming language: SQL

Tool: Oracle SQL Developer

The database schema

Employee (Lastname, FirstName, MidInitial, gender, street, city)

Works (Lastname, FirstName, MidInitial, company-name, salary) company (company-name, city)

company (company-name, city)

Manages(Lastname, FirstName, MidInitial, ManagerLastname, MFirstName, MMidInitial, start-date)

Manages(Lastname, FirstName, MidInitial, ManagerLastname, MFirstName, MMidInitial, start-date)

employee_table (you should define three attributes for employee and manager names)

Name                           street          city           gender

--------------------------------------------------------

Anderson, Susan L     108th        Omaha       F

Brady, Dan L               P street     Lincoln       M

Chen, Peter K             124th         Omaha       M

Clemson, Ann M        O              Lincoln      F

Dale, Mary K               132th        Omaha       F

Gill, Mary L                P street      Lincoln       F

Harrison, Susan M      Old Mill          Omaha F

Jackson, Kim A           178th         Omaha     F

Jason, Pat M                 8th             C.Bluffs    M

Kumar, Paul T           Dodge           Omaha     M

Orr, Susanne S          Q               Omaha   F

Peterson, Mike E      Blondo          Omaha     M

Powell, Tom E         Broadway        C.Bluffs  M

Quinn, Jacky M        168st           Omaha     F

Rand, Pam R           13st            Omaha     F

Schreck, Ann P        214st           Elkhorn   F

Simon, Eric K         77th            Lincoln   M

Smith, John A         8th             C.Bluffs  M

Thomson, Ron F        Farnam          Omaha     M

Young, Don R          Dodge           Omaha     M

Wong, Carole S        Broadway        C.Bluffs  F

works_table: (Again, you should define three attributes for employee name)

employee_name         company_name      salary

------------------------------------------------

Anderson, Susan L     Mutual of Omaha   48000

Brady, Dan L          FDR               42000

Chen, Peter K             FDR                 53000

Clemson, Ann M        First Bank        39000

Dale, Mary K          Mutual of Omaha   58000

Gill, Mary L                Union Pacific  48700

Harrison, Susan M      Union Pacific  54320 

Jackson, Kim A        FDR               68000

Jason, Pat M          FDR               83000

Kumar, Paul T          FDR               44000

Orr, Susanne S          Lincoln Star      32000

Peterson, Mike E      First Bank        37000

Powell, Tom E         First Bank        54000

Quinn, Jacky M        First Bank        85000

Rand, Pam R           FDR               37000

Schreck, Ann P        First Bank        36000

Simon, Eric K         Mutual of Omaha   45000

Smith, John A         FDR               55000

Thomson, Ron F        First Bank        39000

Young, Don R          Mutual of Omaha   27000

Wong, Carole S         First Bank        44000

company_table:

company_name          city

-----------------------------

First Bank                   Omaha

FDR                            Omaha

Lincoln Star                Lincoln

Mutual of Omaha       Omaha

Union Pacific              Omaha

Manages (Note: both employee and manager names should include three

attributes):

employee_name         manager_name      start_date

-----------------------------------------------------

Anderson, Susan L     Dale, Mary K      15-SEP-2002

Brady, Dan L          Jason, Pat M      05-MAR-2004

Chen, Peter K            Jason, Pat M      12-JUNE-2003

Clemson, Ann M        Powell, Tom E     30-AUG-2001

Gill, Mary L                Harrison, Susan M      25-JAN-2003

Jackson, Kim A        Jason, Pat M      09-MAY-2008

Kumar, Paul T           Jason, Pat M      09-MAY-2001

Rand, Pam R           Smith, John A     15-JAN-2004

Peterson, Mike E      Powell, Tom E     30-AUG-2006

Schreck, Ann P        Quinn, Jacky M    28-FEB-1996

Simon, Eric K         Anderson, Susan L 22-FEB-2004

Smith, John A         Jason, Pat M      05-MAR-2003

Thomson, Ron F        Powell, Tom E     30-AUG-1998

Wong, Carole S        Quinn, Jacky M    28-FEB-2001   

Young, Don R          Dale, Mary K      15-SEP-2007

1. For those employees whose last name initial is not a character “C” and who earn less than the average salary of all employees of their own companies, find their full names and cities they live.
2. Find the average number of employees for all companies.
3. (a) Give all managers a 4% pay raise while give all other employees a 6% pay raise (do not use the view ManagerInfo defined above) and display the result.