Oracle SQL need help please

[Phoenixdancer]

hey there

I need to do some specific querys to my tables that i created.

But for the life of me i cant seem to string them together to get them to work / give correct data.

Here they are:

A
1. List the module code, title and term code of all the option modules currently run at DBU, in module code order.

2. List the module code and title of all those currently running compulsory modules that were created but not led by DRS, and which run only within the Autumn term.

3. List staff details together with the title, code and term of those modules that they have led (if a staff member has not led a module, then the associated module details should be padded with null values).

4. Find the module code and title of all current and/or old modules that have a known module leader.

There are 5 tables

Cf.staff
cf.term
bon_module
bon_oldmodule
bon_course

I have been pulling my hair out and also purcahsed a book on SQL but i still cant get anything working

Im doing this on the SQL shell online so can input multiple commands

thanks so much

p.s if you need any info please just ask i have all the data on the tables as i did create them

 

[Phoenixdancer]

You will need to list all the table structures.
so table name, columns and and relations they have.
Example data would help too.

Ah i didnt realise. how stupid i am

bon_module
MODULE_CODE NOT NULL VARCHAR2(15)
MODULE_TITLE NOT NULL VARCHAR2(35)
MODULE_LEADER VARCHAR2(4)
MODULE_CREATOR NOT NULL VARCHAR2(4)
MODULE_POINTS NUMBER(3)
MODULE_MODE VARCHAR2(1)
TERM_CODE NUMBER(1)
LEVEL_OF_STUDY NUMBER(1)
MODULE_DEPARTMENT VARCHAR2(20)
COURSE_CODE NOT NULL VARCHAR2(15)


Bon_oldmodule
OMODULE_CODE NOT NULL VARCHAR2(15)
OMODULE_TITLE NOT NULL VARCHAR2(35)
OMODULE_LEADER VARCHAR2(4)
MODULE_CREATOR NOT NULL VARCHAR2(4)
OMODULE_POINTS NUMBER(3)
OMODULE_MODE VARCHAR2(1)
TERM_CODE NUMBER(1)
OLEVEL_OF_STUDY NUMBER(1)
OMODULE_DEPARTMENT VARCHAR2(20)
OCOURSE_CODE NOT NULL VARCHAR2(15)


Bon_course
COURSE_CODE NOT NULL VARCHAR2(8)
COURSE_TITLE NOT NULL VARCHAR2(40)
DATE_VALIDATED DATE
DURATION NUMBER(1)


cf.staff
STAFFCODE NOT NULL VARCHAR2(4)
NAME NOT NULL VARCHAR2(40)
TELEPHONE VARCHAR2(16)
YRSEMPLOYED NOT NULL NUMBER(2)


cf.term
TERMCODE NOT NULL NUMBER(1)
DESCRIPTION NOT NULL VARCHAR2(40)
DATESTART NOT NULL DATE
DATEFINISH NOT NULL DATE


Example data from bon_module
PRO1055 Programming_In_Cs IP IP 111 C 4 1 Technology COMP205
PROC1055 Programming_In_C IP IP 80 C 4 1 Technology COMP205
INTM2288 Introduction_To_Modelling DRS DRS 20 C 1 1 Technology COMP205
INAI4537 Introduction_To_Modelling FG CF 20 C 2 1 Technology COMP205
CCCO1234 Computer_Systems TG TG 60 O 4 3 Computing COMP210

 

[Phoenixdancer]

Thanks for that mate

What is the (m) for infront of module_code and others?

Not fully grasped queries yet creating tables yet! But others no

Im ot sure if that option word is a typo or not ment to be there so i disregarded it, made task one so much simpler....

Now to adapt the code for the rest!

 

[Phoenixdancer]

hey simon

Thanks for all your help!

I have finished it all now

Your code was a real help as it was the starting point, without it i would have sat here depressed lol