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.
-
Write the update statements to handle the raises in salaries (do not execute
the update statements yet!).
-
Write a query that retrieves Tina's name, cpr, and current salary.
-
Execute the update statements to implement the salary raises.
-
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);
-
Write a query that calculates the average salary of all persons in the
agg
table.
-
Insert a new tuple that records Lars as being in department 10. His salary
is currently unknown, so we set it to 0.
-
Execute the average salary query again, compare the result to the previous
result.
-
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.
-
Execute the average salary query again, compare the result to the previous
result.
Remove the agg table again.
Best regards,
Kristian Torp