1. Worksheets

The purpose of this weeks worksheet is to provide additional SQL practice. Please attempt the worksheet without using provided answers. 

2. Questions and Completion

To receive credit for completing the worksheet, you must say ‘Done’ on the worksheet folder.

If you have questions as you go through this worksheet, please feel free to email the instructor.

3. Prerequisites: Oracle SQL Developer

If you have not done so already, download Oracle SQL Developer for your specific OS.

Now connect to the database using the instructions posted on D2L.

Test your connection. If successful, you are good to start.

4. Practice of ALL, ANY, EXISTS, NOT EXISTS 

Use the University database provided here.

(i)  List student groups that have both graduate and undergraduate members. 

Note: Write this query using EXISTS. For this only the studentgroup is to be listed so think how an existential check can be made and is sufficient. 

(ii) List courses that have a unique number. 

Note: Write this query using ANY. 

(iii) For all departments list the highest course number used by that department 

Note: Write this query using ALL; Then write the query without ALL but using MAX. 

5. General Practice 

In this example, we will consider a Zoo database. Please download the Zoo database from here. 

Open the ZooDatabase.sql file using SQLDeveloper. Answer the following questions:

(i) Find the average feeding time for all of the rare animals. 

(ii) Which animal(s) have a `time to feed' larger than every exotic animal? Give the id and name of the animal. 

(iii) Name zookeepers handling at least 4 animals. 

(iv) Find the names of the animals that are not related to the tiger.

(v) List zookeepers earning the most while feeding animals. 

6. Check answers

Answers are here, but look at these answers after you have tried the queries.