in Databases retagged by
985 views
0 votes
0 votes
Consider the relational database given below.  Give an expression in SQL for each of the following queries.

employee (person-name, street, city)
works (person-name, company-name, salary)
company (company-name, city)
manages (person-name, manager-name)

a. Modify the database so that Jones now lives in Newtown.
b. Give all employees of First Bank Corporation a 10 percent raise.
c. Give all managers of First Bank Corporation a 10 percent raise.
d. Give all managers of First Bank Corporation a 10 percent raise unless the salary becomes greater than $100,000; in such cases, give only a 3 percent raise
e. Delete all tuples in the works relation for employees of Small Bank Corporation.
in Databases retagged by
by
985 views

1 Answer

0 votes
0 votes
  1. update employee

           set city = ’Newton’ where

           person-name = ’Jones’

 

  1. update works

           set salary = salary * 1.1

          where company-name = ’First Bank Corporation’

 

  1. update works

            set salary = salary * 1.1

            where employee-name in (select manager-name from manages)

            and company-name = ’First Bank Corporation’

 

  1. update works T set T.salary = T.salary * 1.03

            where T.employee-name in (select manager-name from manages)

            and T.salary * 1.1 > 100000 and T.company-name = ’First Bank Corporation’

     

          update works T set T.salary = T.salary * 1.1

          where T.employee-name in (select manager-name from manages)

           and T.salary * 1.1 <= 100000 and T.company-name = ’First Bank Corporation’

 

  1. delete works

            where company-name = ’Small Bank Corporation’

Related questions