Instructions:
Write the sql queries in a file. The filename should be your lastname2.sql,
e.g. Kanoulas2.sql.
To create the file you can use pico,vi,emacs or any other editor you know.
To check the sql statements get in the sql environment, typing sqlplus at the unix prompt,etc. At the SQL prompt type
start <file name>
e.g. start Kanoulas2.sql
What you will send to me is the sql file.

Write in SQL the following questions:

1. List all properties (propertyNo) whose owner does not live in Aberdeen.

2. List all branches (branchNo) that have less than 2 staff members. Include branches that has no staff members.

3. For each of the staff members count the number of clients heshe has registered (staffno, fname,lname,noclientsreg). If a staff member hasn't register any customer his info should be included in the result table where noclientsreg should be 0.

4. Find the property type viewed most.

5. Which is the branch (branchNo, city) with the maximum number of properties.

6. Which of the staff members (staffNo,lname,fname) that has registered some customer is most well paid?

7. List the property with the maximum number of rooms whose rent is less than at least one other property.

8. List all branch offices (branchNo, city) that does not have staff.

9. List all the flats (propertyNo,type) that have never been viewed and their rent is less than the average rent a client is willing to pay (average maxRent)for a flat.

10. List the most well paid staff member (staffno,lname,fname,position) in each position

11. List all the positions in the real estate that are occupied by both sexes (female and male).

12. List all the owners (ownerNo,lname,fname) that live in Glasgow and own all types of properties.

    [BONUS QUESTION]
    

13. List all the branches (branchNo,city) that all their staff members have registered a property (branches with no staff shouldnt appear in the result table).

    [BONUS QUESTION]
    

14. List all the branch offices (branchNo,city) that have less than 3 staff memebers and all of the female staff members have registered some customer. (If the branch has no staff memebers or no female staff memebers then it should not appear in the result table.)