Oracle SQL need help please

Phoenixdancer

Phoenixdancer

Phoenixdancer

Permabanned
Joined
22 Feb 2010
Posts
1,629
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 :)
 
SimonCHere said:
You will need to list all the table structures.
so table name, columns and and relations they have.
Example data would help too.
Click to expand...

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
 
Last edited:
A 1)
Code: 
select m.MODULE_CODE, m.MODULE_TITLE, m.TERM_CODE 
from bon_module m
inner join cf.term t
on t.TERM_CODE=m.TERM_CODE
where getdate() between t.DATESTART and t.DATEFINISH
and ***MODULEMODE? LEVELOFSTUDY?? = OPTIONAL??
order by m.MODULE_CODE

this is MSSQL but I'm sure you can work it out for Oracle.
I don't know what shows whether a module is optional or not so you will have to change that line.
 
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!
 
Last edited:
m is an alias for bom_module saves writing out the whole thing each time.
t is an alias for cf.term.
You can use anything as the alias other then reserved words (and, while, select, etc)

It's going to be bon_module.module_mode = 'O' for optional and 'C' for compulsory from that snippet of data you wrote so you can change the line
Code: 
and ***MODULEMODE? LEVELOFSTUDY?? = OPTIONAL??
to
Code: 
and m.module_mode = 'O'
 
You must log in or register to reply here.
Back
Top Bottom