Urgent help with SQL!

[caniballism]

i have created a database and populated it with information but i have to run queries on it without using a query builder and am quite rusty on SQL.

Below are the tables for the database!

Staff : Man no, SName,LName, Address, Tel Mobile.
Students: Driver No, Group, Exp Date, SName, LName, DOB, Adress, Post code, Tel Home, Tel Mobile, Tel Work
Instructor: Instructor ID, Man no~
Examiners: Examiner ID, Man no~
Lessons: Lesson ID, Student ID~, Instructor ID~, date, Amount Due, Date Canceled
Tests: Test ID, Examiner ID~, Test Type ID~, date
Test Type: Test TypeID, Description
Student Attendance:Stu Att Test ID, Test ID~, Student ID~
Student Payments: Stu Les PayID, Payment Amount, Payment Date, Lesson ID~,

The queries that i need to run on this data are

1.produce the total number of tests and lesson sessions for a particular month e.g January
2.find the id and full name of the instructor with the most bookings. note: there can be more than 1 instructor
3.produce a list of all staff who are both instructors and examiners
4. for a given student id, and for all lession sessions that were not taken by the student, list the allocated staff, reason why it wasnt taken and the method of payment. the student id hould be passed as the parameter at run time.

Any Thoughts on this????????????????????

[dickersonka]

have some great ideas on this, but you must show some effort
we will help you, not do the work for you

[caniballism]

Sori i took too long to come back, i have a few ideas but i will express them as normal statements rather than SQL statements.

1. for the first question i woud say...........

SELECT    Testid & Lessonid, COUNT(*) AS  Total Tests & Total Lesson Sessions
     FROM       Tests  & Lesson
     WHERE     Date BETWEEN '1/1/2008' AND '31/1/2008'
     GROUPBY  Testid & Lessonid

the problem is where am putting the '&', the variables are from two different tables and am unable to merge them.

3. for the third question i would say...........

SELECT  SName, LName
      FROM     Staff
      WHERE   manno = instructorid & Examinerid

my problem with my solution is th last part, am not sure of how you use AND in SQL, in programming we simply use '&'.

i will be back, am having problems with the second and third qustion, i hope this can inspire some responses.

[dickersonka]

are you able to run multiple queries on number 1 or need a single one?
the concept is pretty much there though

number 3 you have the concept as well
here's some help, depending upon what db you are using

select sname, lname from staff where
manno in (
select instructor.manno
from instructor
inner join examiners
on instructor.manno = examiners.manno)

[caniballism]

are you able to run multiple queries on number 1 or need a single one?
the concept is pretty much there though

am only supposed to run a single query.