employee (Lastname, FirstName, MidInitial, gender, street, city) works (Lastname
ID: 3917837 • 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, start-date)
Using the above tables answer the queries in SQL
Q1. Find the names of companies which have employees living in Lincoln and Omaha.(Note the meaning of the query: For any company returned from the query, it has some employees live in Omaha, and some other employees live in Lincoln – none of these two sets could be empty.]
Q2. Find the streets and cities of residence of all managers who work for Union Pacific.
Q3. Find the salaries of all managers who work for First Bank and live in Omaha.
Q4. Find salaries of employees in the database who live in the same cities as the companies for which they work.
Q5. Find all employees in the database who live in the same cities and on the same streets as do their managers.
Q6. Find salaries and company names for employees who are not managers.
Q7. Find the name and the company name of the employee who has the highest salary of all employees.
Q8. (a) Define a view ManagerInfo consisting of names, streets and cities of managers and (b) then execute this view.
Q9. Find the names and the company names of managers who earn more than the average salary of all managers. (You should use the view ManagerInfo defined earlier.)
Q10. Find the names of the companies which have employees residing in every city where employees of Mutual of Omaha live.
Q11. Find all employees who earn less than the average salary of all employees of their own companies.
Q12. (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. (b) Undo this update (so that database is actually not changed – for future use
Explanation / Answer
I am working on this. I will post the complete answers once i am done.
#1)
select C.company_name from company as C
inner join employee as E on C.city=E.city
where E.city='Lincoln' and E.city='Omaha';
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.