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 tutors answer.

SQL Requests

Solutions to the exercises (Click here for more information)

This self assessment uses the same sample databaseas before but now using an interactive SQL tool called WebSQL. This tool was developed by the GITTA-partner IFI (Institute of informatics, University of Zurich). Please contact your tutor if you didn't get your account information or if the login doesn't work.

Some remarks about the WebSQL 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.

The goal of this self assessment is that you are able to form SQL queries that answer the questions below. Write a report where you explain your queries and comment on the solutions and put this document on the discussion board. If you have any questions please post them also on the discussion board. Of course you are welcome to check the other students solutions, comment on them or answer questions from other students.

Aufgaben

Select the names of all employees and sort them in descending order (Z to A).
Find the names 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 (in word form) together with the solution on the discussion board (under the topic 'eLSQL').
Solve some queries that other students have posted.

Possible solutions (Click here for more information)