Classnotes

Database Languages

eXercise #7

---

Questions on the STUDENT Database

The following set of activities will be performed on the STUDENT database, make your own copy with the script your teacher sent you via email so you can TRY your solutions after you have planned them.

Wherever possible, use the DIAGRAM method to plan your solution BEFORE translating to SQL:

where comptype is a text dessie; chip is processorname; RAM is in Gb; video is name of card; HDD is capacity of hard disk in Gb

What follows is a list of activities for you to try ON PAPER. Due to the 'power' and versatility of the SELECT statement, you will need to practise it's use, this exercise set provides some examples.

Avoid the temptation to 'short cut' through the exercises avoiding the function or convention that is meant to be used for each of the exercise sets - you will learn nothing if you do this, and all that will benefit is your typing skills.

---

Creating a Projection

1. list student names
2. list sid along with birth and sibling information
3. list all sports played (with duplicates)
4. list all types of computers owned (with duplicates)

Removing Duplicates

1. list all houses (no duplicates)
2. list all suburbs (no duplicates)
3. list all studied subjects (no duplicates)
4. list all sports (no duplicates)
5. list all hobbies (no duplicates)
6. list all HDD capacities (no duplicates)

---

**NB** ASSUME LISTS ARE DISTINCT FROM NOW ON (but restrict use of the DISTINCT keyword to situations where it does something useful)

Selecting Rows

1. list name of students born first in their families
2. list name and nickname of students born second in a 3 child family
3. list sports associated with someone who has a SID of "s9103456" (or pick another one that gives data)
4. list all sids who study both English and Physics
5. list all sids who study Maths C
6. list Treacy tutors
7. list names of students without computers

Using the Relational and Logical Operators (and, or, not)

1. list all students who were born either first or second
2. list all students not born last
3. list all students who appear alphabetically lower in the class list than yourself
4. list all sids who don't play rugby
5. list all sids who don't study EConomics
6. list all sids that study both Maths B and Maths C
7. list all students whose computers have less that 1 Gb of RAM (is this even possible?)
8. list all Barrett students that were born first

Negated Conditions

1. list all students who are not from Corinda, Indooroopilly or Oxley
2. list all sports that are not rugby (negated form - use a NOT somewhere in your answer)

The Condition IS [NOT] NULL

1. list all students whose nickname is unknown
2. list those students who don't know what chip their computer has

The BETWEEN..AND Construction

1. list all students that are in Windsor tutor groups lettered between A & D
2. list students with sids between s910000 and s920000 (or make up some boundaries that work)
3. list students not born first (trickey, use a between and if you can)
4. list all student but those students not born second

The LIKE Operator

1. list all students who have a surname starting with 'B'
2. list all sports starting with 'S'
3. list all students with a 'W' in their name
4. list all students with a first name exactly 6 letters long
5. list all students with and 'IBM' style computer

The IN Operator

1. list all students living in either Corinda or Aspley (use IN)
2. list all students from a 1, 2 or 3 child family (use IN)
3. list all sids studying either EConomics, Maths B or Modern History (use IN) with no repeat sid values.

Ordering on a single column

1. list all students in alphabetical order
2. list all sports (no repeats) in alphabetical order
3. list all subjects in reverse alphabetical order (no repeats)
4. list all students and suburbs they live with suburb arranged alphabetically
5. list all nicknames in reverse alphabetical order (no nulls)
6. list students arranged in order of their tutor group within their houses (a two column sort)

Banding (or Ordering)

1. list all sids and sports, banded by sport
2. list all surnames, banded by month born

Deriving Simple Statistics

1. derive the number of sids studying ACounting
2. derive the number of siblings for the class
3. derive the number of student not taking Modern History
4. derive the number of students in year 11 IPT

Performing Simple Calculations

1. derive the student name with the largest sid
2. derive the number of rows in each of the five tables (separately)
3. derive the average sibling position for the group (sum of measurements divided by the number of measurements)
4. derive the name of the student with the largest number of brothers and or sisters

Use for Column Numbers

1. list name and suburb arranged so that suburb is reverse alphabetical
2. list name and number of OLDER siblings of all students in descending order of number OLDER of siblings

Grouping

1. list all sids grouped by sport
2. list all names grouped by suburb
3. list distinct subjects and the number of students studying each
4. list distinct hobbies and the number of students in each
5. list year levels and the number in each

Some Extension

1. List those people that admit to sharing at least one hobby with yourself
2. List the names of people that don't play sports
3. List the house with the largest number of year 11 students represented.
4. List the house with the second largest number of year 11's represented.
5. List the computer chip type of students that study Economics, play rugby and are from Windsor house.

The Nasties

1. List the names of those students that enjoy more hobbies than play sports
2. derive the number of people that come from the suburb that has the most people coming from it
3. derive how many more students do the most done subject when compares with the second most done subject
4. list all middle born children (of odd numsib families)

solutions