eLSQL Exercise 'Insert, Delete und Update'
This exercise predominantly addresses the SQL commands INSERT, DELETE, and UPDATE. To complete the exercise however, the SQL
requests from unit "Basic database queries" need to be known as well. During the following exercise you will work with
this
sample database. (please leave this overview open during the
exercise). You can also find the overview in the eLSQL Tool on the tab 'Dataset'.
The exercise is executed through a web interface on a MySQL database. Your tutor will provide you the information needed
(webaddress and registration).
Some remarks about eLSQL interface
-
The semicolon at the end of a SQL query or SQL command is optional.
-
On the tab 'Dataset' you find information about the content of the original database.
-
You changes to the database are saved and are available at your next login.
- On the tab 'Settings' you can change your user information and reset the database to its original state. All your changes
will be deleted. Note that this command is irreversible.
- On the tab 'Protocol' you find a list of all your SQL queries.
-
Please contact your tutor and not the IFI for technical questions.
Exercises
- You have been hired by a company (represented in
the sample
database)
and have to add yourself in the table employee. Add at least
someone of your family in the according table. Also you should
add some hours of work to one of the projects.
Remark:
You don't have to add your birth date since the date format may
be a bit complicated!
- Select the columns you added in the different tables.
- Project 20 has been finished. Please delete all entries related
to project 20.
How did you proceed and why?
- Beate Tell leaves the company. Her position is taken over by
Sonja Maradona. Please make sure that these changes are made
correctly in your company database. Delete all entries that are no longer needed.
How did you proceed and why?
- All employees who's boss is Boris Frisch get a wage raise
of 10'000 Fr.
- Present some other typical database changes (and their solutions)
that might have to be done in this company.