retagged by
4,449 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.
retagged by

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

0 votes
0 votes
1 answer
3
rayhanrjt asked Jan 6, 2023
717 views
Write SQL command to find DepartmentID, EmployeeName from Employee table whose average salary is above 20000.
2 votes
2 votes
1 answer
4
Subhrangsu asked Jun 18, 2022
431 views
Write SQL query to show all employees hired on June 4,1984 (non-default format)emp(empno,ename,job,mgr,hiredate,sal,comm,deptno)