Exercise 5

1.  From Formal Languages to SQL

Exercises 4.4, 4.5, and 4.6 from the book (do these exercises first).

2. SQL

a:

Consider the Company database from Exercise 1. The company what to give all employees that earn less than 30K a 20% raise and the employees that 30K or more a 15% raise in salary. Do the following.
  1. Write the update statements to handle the raises in salaries (do not execute the update statements yet!).
  2. Write a query that retrieves Tina's name, cpr, and current salary.
  3. Execute the update statements to implement the salary raises.
  4. Find Tina's salary (is it what you expect it to be?)

b:

Create the following new table.
CREATE TABLE agg (name   VARCHAR2 (30) PRIMARY KEY,
                                    dno     NUMERIC (3,0)    NOT NULL,
                                    salary  NUMERIC (10,0));
Populate the table agg with the following tuples:
    INSERT INTO agg VALUES ('Jens',     10, 10000);
    INSERT INTO agg VALUES ('Hanne', 12, 21000);
    INSERT INTO agg VALUES ('Pia',      11, 35000);
    INSERT INTO agg VALUES ('Gitte',    11, 26000);
     
  1. Write a query that calculates the average salary of all persons in the agg table.
  2. Insert a new tuple that records Lars as being in department 10. His salary is currently unknown, so we set it to 0.
  3. Execute the average salary query again, compare the result to the previous result.
  4. Now, the tuple with Lars was wrong. The salary attribute should not be 0 but the special value NULL. Write an SQL statement that corrects this error and execute the statement.
  5. Execute the average salary query again, compare the result to the previous result.
Remove the agg table again.

Best regards,
Kristian Torp