employee (Lastname, FirstName, MidInitial, gender, street, city) works (Lastname
ID: 3857421 • Letter: E
Question
employee (Lastname, FirstName, MidInitial, gender, street, city)
works (Lastname, FirstName, MidInitial, company-name, salary)
company (company-name, city)
manages(Lastname, FirstName, MidInitial, ManagerLastname, MFirstName, MMidInitial
Employee (Note: Employee name consists of three attributes: Last, First, MI):
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
**managers are in bold in the employee table**
Works: (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
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:
company_name city
-----------------------------
First Bank Omaha
FDR Omaha
Lincoln Star Lincoln
Mutual of Omaha Omaha
Union Pacific (UP) 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
**PLEASE USE SQL PLUS QUERIES FOR THE ANSWERS***
Q1. Find all employees in the database who live in the same cities and on the same streets as do their managers.
Q2. Find salaries and company names for employees who are not managers.
Q3. Give all managers a 4% pay raise while give all other employees a 6% pay raise
Explanation / Answer
1.select t1.employee_name from employee t1, employee t2, manages t3 where t1.employee_name = t3.employee_name and t3.manager_name = t2.employee_name and t1.street = t2.street and t1.city = t2.city;
2.select company_name,salary from works where Name not in (select manager_name from manages);
3.update works set salary=salary*1.4 where employee_name in (select manager_name from managers);
update works set salary=salary*1.6 where employee_name not in (select manager_name from managers);
(or)
select employee_name,salary*1.4 as salaries from works where employee_name not in (select manager_name from managers);
union
select employee_name,salary*1.6 as salaries from works where employee_name in (select manager_name from managers);
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.