A database problem. I found a similar problem in http://www.csee.umbc.edu/~pmund

Question

A database problem. I found a similar problem in http://www.csee.umbc.edu/~pmundur/courses/CMSC661-02/rel-alg.pdf, but it was solved in relational algebra. I need to wrirte them in SQL format.

Consider the following relational database.
employee (person-name, street, city)
works (person-name, BankID, salary)
company (BankID, company-name, city)

Create SQL statements to execute the following query:

a) Find the names of all employees who work for First Bank Corporation.
b) Find the names and cities of residence of all employees who work for First Bank Corporation.
c) Find the names, street address, and cities of residence of all employees who work for First Bank Corporation and earn more than $10,000 per annum.
d) Find the names of all employees in this database who live in the same city as the company for which they work.
e) Assume the companies may be located in several cities. Find all companies located in every city in which First bank is located.
f) Find the employees who work for more than two companies.
g). Give all employees of First Bank Corporation a 10 percent salary raise.
h). Give all employees in this database a 10 percent salary raise, unless the salary would be greater than $100,000. In such cases, give only a 3 percent raise.
i). Delete all tuples in the works relation for employees of Small Bank Corporation.

Explanation / Answer

a. select emp.person-name from employee emp join works wor on emp.person-name = wor.person-name where wor.BankID = 123 (supposing 123 is the ID for First Bank Corporation and here you can give whatever is the ID of First Bank Corporation)

b. select emp.person-name, emp.city from employee emp join works wor on emp.person-name = wor.person-name where wor.BankID = 123 (supposing 123 is the ID for First Bank Corporation and here you can give whatever is the ID of First Bank Corporation)

c. select emp.person-name, emp.street, emp.city from employee emp join works wor on emp.person-name = wor.person-name where wor.BankID = 123 and wor.salary > $10000 (supposing 123 is the ID for First Bank Corporation and here you can give whatever is the ID of First Bank Corporation)

d. select emp.person-name from employee emp join works wor on emp.person-name = wor.person-name join company com on wor.BankID = com.BankID where emp.city = com.city

e. select com.company-name from comapny com join works wor on wor.BankID = com.BankID join employee emp on emp.person-name = wor.person-name where wor.BankID = 123 (supposing 123 is the ID for First Bank Corporation and here you can give whatever is the ID of First Bank Corporation)

f. select emp.person-name, count(com.company-name) from employee emp join works wor on emp.person-name = wor.person-name join company com on wor.BankID = com.BankID where count(com.company-name) > 2 group by emp.person-name

Related Questions

Navigate

• Browse (All)
• Browse A
• Subjects

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