Database queries
This self assessment should give you the possibility to test
different SQL queries and get to know them more in depth. We use the following sample
database (please leave this overview open during the
exercise).
Find the correct solution tables for the following SQL queries. Put
your solutions (either Word or PDF format) on the discussion board and check
the other solutions.
You are encouraged to comment on other postings or to ask
questions using the discussion board. Please do not email the tutor directly so
that others can also benefit from your questions and the tutor's answer.
SQL Anfragen
Some remarks about the WebSQL interface
-
Do not use a semicolon at the end of a SQL query.
-
On the bottom left you can reset the database to its original
state.
-
Please contact your tutor and not the IFI for technical questions.
Tasks to do
- Select the names of all employees and sort them in descending order (Z to A).
- Find the surnames of all employees who live in Dübendorf and
earn more than 30'000 Fr.
- Select all children whose parents are employees and live in
Zurich. Sort the result by birth date.
- Find the total amount of time that is spent on all projects in
the research (Forschung) department. Use one query only!
- How many children has the manager of the administration (Verwaltung)
department?
- Find projects of Zurich where the total amount of work is over 50 hours?
- Create your own SQL query and post the query together with the
solution on the discussion board.
- Solve some queries that other students have posted.