Help: with SQL , i created all the table and insert data in each table in sql ,

Question

Help: with SQL , i created all the table and insert data in each table in sql , but I have the Question under the tables:

Table 1: Employee

Employee_name                 Street                   City

William Gates III     University St                  Redmond
Warren Buffett        Alaskan Way                 Omaha
Lakshmi Mittal        1st Ave                          London
Carlos Slim Helu    1st Ave                          London
Prince Gatlin           2nd Ave                        Birmingham
Ingvar Kamprad      University Ave              Oslo
Paul Allen                First St                         Seattle
Karl Albrecht           Westlake Ave               London
Lawrence Ellison     20717 International Blvd.    Silicon Valley
Mark Zuckerberg     First Avenue                 Silicon Valley
Sheryl Sandberg     Second Avenue             Silicon Valley
Dave Wehner         Third Avenue                 Silicon Valley
Mike Schroepfer     Fourth Avenue              Silicon Valley
Chris Cox               Sixth Avenue                 Silicon Valley
Kenneth Thomson    20717 International Blvd.   Silicon Valley
Liliane Bettencourt    21 Rue de Miroir                Lyon
Bernard Arnault       18 Avenue de Suffren    Paris
Michael Dell            4533 South                         Austin
Sheldon Adelson     4533 South                        Austin
Theo Albrecht          Westlake Ave                   London

Table 2: Works
Employee_name                Company_name                     Salary
William Gates III                 Microsoft                               1400000
Warren Buffett                    Exxon Mobil                            230000
Lakshmi Mittal                    General Electric         60000
Carlos Slim Helu                 General Electric                      400000
Prince Gatlin                       General Electric                       50000
Ingvar Kamprad                      BP                                        80000
Paul Allen                           Microsoft                                  300000
Karl Albrecht                           BP                                       100000
Lawrence Ellison                 Oracle                                      98000
Mark Zuckerberg               Facebook                                   650000
Sheryl Sandberg               Facebook                                   450000
Dave Wehner                    Facebook                                   120000
Mike Schroepfer                Facebook                                    94000
Chris Cox                           Facebook                                  300000
Kenneth Thomson              Oracle                                       120000
Liliane Bettencourt             Chanel                                       500000
Bernard Arnault                 Chanel                                       130000
Michael Dell                         Dell                                          900000
Sheldon Adelson                 Dell                                           80000
Theo Albrecht                      BP                                           56000

Table 3: Company
Company_name                             City
Microsoft                                     Redmond
Exxon Mobil                                 Omaha
General Electric                           London
BP                                                London
Oracle                                       Silicon Valley
Facebook                                 Silicon Valley
Chanel                                           Paris
Dell                                                Austin

Table 4: Manages
Employee_name                        Manager_name
Paul Allen                                   William Gates III
Carlos Slim Helu                         Lakshmi Mittal
Karl Albrecht                               Ingvar Kamprad
Theo Albrecht                             Ingvar Kamprad
Kenneth Thomson                      Lawrence Ellison
Sheryl Sandberg                         Mark Zuckerberg
Dave Wehner                              Mark Zuckerberg
Mike Schroepfer                          Sheryl Sandberg
Chris Cox                                     Sheryl Sandberg
Bernard Arnault                            Liliane Bettencourt
Sheldon Adelson                          Michael Dell

1- Find the name of managers that have their salary smaller than some of the employee they manage.

2- Find the name of the managers and the average salary of the employees they manage.

3 Display the employee names and their corresponding street without any digits in the street name. For

instance for Prince Gatlin display nd Ave. In addition make sure you trim any leading spaces.

4- Give all employees of Facebook a 10 percent raise if their salary is less than $100,000 and 5 percent

raise otherwise.

thank you..

Explanation / Answer

1.
SELECT Manager_name FROM Manages M
INNER JOIN Works W ON M.Employee_Name = W.Employee_Name
INNER JOIN Works M1 ON (M.Manager_name = M1.Employee_Name) AND (M1.Salary < W.Salary)

Output of query:-

2. SELECT Manager_name, Salary = AVG(W.Salary) FROM Manages M
INNER JOIN Works W ON M.Employee_Name = W.Employee_Name
GROUP BY M.Manager_name

Output of query :-

3. select Employee_Name, Street = RTRIM(LTRIM(REPLACE
(REPLACE
(REPLACE
(REPLACE
(REPLACE
(REPLACE
(REPLACE
(REPLACE
(REPLACE
(REPLACE (Street, '0', ''),
'1', ''),
'2', ''),
'3', ''),
'4', ''),
'5', ''),
'6', ''),
'7', ''),
'8', ''),
'9', ''))) FROM Employee

output of Query:-

4.

SELECT Employee_Name, Company_Name, Salary = CASE WHEN Company_Name = 'Facebook' AND Salary < 100000 THEN (Salary + Salary/10) ELSE (Salary + Salary/20) END
FROM Works

Output of Query:-

Related Questions

Navigate

• Browse (All)
• Browse H
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.