Solutions for Exercise no. 5

1.  From Formal Languages to SQL

4.4 See the text in the book
Answer:
a:
select distinct A
from r
b:
select *
from r
where B = 17
c:
select distinct *
from r, s
d:
select distinct A, F
from r, s
where C = D


4.5 See the text in the book
Answer:
a:

(select *
from r1)
union
(select *
from r2)
b:
select distinct *
from r1
where (A, B, C) in  (select *
                               from r2)
c:
select distinct *
from r1
where (A, B, C) not in  (select *
                                     from r2)
-- This can also be solved by using the except clause in SQL
d:
select distinct r1.A, r2.B, r2.C
from r1, r2
where r1.B = r2.B
4.6 See the text in the book
Answer:
a:
select distinct A
from r
where B = 17
b:
select distinct r.A, r.B, s.C
from r, s
where r.A = s.A
c:
select distinct  s.A
from s, r e, r m
where s.A = e.A and s.C = m.A and e.B > m.B

2. SQL

a1:

update employees
    set salary = salary * 1.15
    where salary > 30000;

update employees
    set salary = salary * 1.20
    where salary < 30000;
commit;

a2:

select fname, cpr, salary
from  employees
where fname = 'Tina';

a3 and a4: see above.
 

b1:

select avg (salary) from agg;

b2:
insert into agg values ('Lars', 10, 0);

b3: see b1

b4:
update agg
    set salary = NULL
    where Name = 'Lars';
commit;

b5: see b1 and b3.


Best regards,
Kristian Torp