Help with SQL Queries - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

Database Design and Application Architecture

Help with SQL Queries

Author

Topic

kingkhan
Starting Member

6 Posts

Posted - 2008-11-27 : 07:34:24

Hi there,

Im new to this forum, friend recommended me to this site...said it was the best!!!

I hope this right as i have a major problem:

i have created the following tables, with the fields all set and filled in...

i have uploaded and created all the tables in SQL Plus that im using..everything works fine (thank god!!)

The Tables:

CANTEACH
----------------------------------------- -------- -------
MODULID CHAR(4)
MODPARTID CHAR(1)
LECTURERID CHAR(4)

COURSE
----------------------------------------- -------- --------
COURSEID CHAR(4)
COURSESHORT CHAR(10)
COURSELONG CHAR(30)

ENROL
----------------------------------------- -------- -------
STUDENTID CHAR(8)
COURSEID CHAR(4)
ENROLDATE DATE

FOUNDIN
----------------------------------------- -------- -------
RSRCEID CHAR(3)
ROOM CHAR(7)

LECTURER
----------------------------------------- -------- --------
LECTURERID CHAR(4)
SURNAME CHAR(10)
FORENAME CHAR(10)
BOSSID CHAR(4)

MODOCCUR
----------------------------------------- -------- -------
MODULID CHAR(4)
ACYEAR CHAR(2)
SEMESTER CHAR(1)
OCCLETTER CHAR(1)

MODPART
----------------------------------------- -------- --------
MODULID CHAR(4)
MODPARTID CHAR(1)
MODPARTDESC CHAR(10)

MODPARTOCCUR
----------------------------------------- -------- ---------
MODULID CHAR(4)
ACYEAR CHAR(2)
SEMESTER CHAR(1)
OCCLETTER CHAR(1)
MODPARTID CHAR(1)
ROOM CHAR(7)
LECTURERID CHAR(4)
DOW CHAR(1)
TIMESTART NUMBER(3)
DURATION NUMBER(3)

MODPARTRES
----------------------------------------- -------- --------
MODULID CHAR(4)
MODPARTID CHAR(1)
RSRCEID CHAR(3)
WHATFOR CHAR(10)

MODUL
----------------------------------------- -------- --------
MODULID CHAR(4)
MODULNAME CHAR(10)
MODULEVEL CHAR(1)

MODULCOURSE
----------------------------------------- -------- -------
COURSEID CHAR(4)
MODULID CHAR(4)
COMPOPT CHAR(1)

PREREQ
----------------------------------------- -------- -------
MODNEEDS CHAR(4)
MODNEEDED CHAR(4)

ROOM
----------------------------------------- -------- ---------
ROOM CHAR(7)
SEATS NUMBER(3)

RSRCE
----------------------------------------- -------- --------
RSRCEID CHAR(3)
RSRCEDESC CHAR(20)

STUDENT
----------------------------------------- -------- --------
STUDENTID CHAR(8)
SURNAME CHAR(15)
INITS CHAR(15)
SEX CHAR(1)
PHONE CHAR(12)
EMAIL CHAR(20)
LOGON CHAR(8)

STUDREGOCCUR
----------------------------------------- -------- -------
MODULID CHAR(4)
ACYEAR CHAR(2)
SEMESTER CHAR(1)
OCCLETTER CHAR(1)
STUDENTID CHAR(8)
RESULT CHAR(1)

STUDTT
----------------------------------------- -------- -------
MODULID CHAR(4)
ACYEAR CHAR(2)
SEMESTER CHAR(1)
OCCLETTER CHAR(1)
MODPARTID CHAR(1)
STUDENTID CHAR(8)

I have recently been asked to query the tables, and the queries are just mind boggling...cant seem to understand them...

here are the queries asked to be produced

a)A list of students (surname, initials and ID number) along with the modules (names) and the result of the module, for all modules where they have a result.
b)A list of students who are enrolled on modules for semester 2. The list should include student surname and initials, and should be in the order of the module number.
c)A list of rooms that only have one facility.

d)A list of facilities that only exist in one room.
e)A list of students along with the times and dates of the sessions that they are registered to attend, in the sequence of the student.
f)A list of each lecturer and the modules they are teaching in semester 2, arranged by lecturer.
g)A list of courses and their modules being delivered in semester 2, including the names of the lecturers who will deliver them.
h)A list of lecturers who are presenting a class in semester 2 that they do not know about.
i)A list of module parts that require a facility that only exists in one room.

j)A list of rooms that were used for a class that had too many students for the room in semester 1, along with the details of the class that was too large.

k)A list of module parts that require a facility that did not exist in the room in which it was delivered during Semester 1.
l)A list of all the lecturers that deliver a part of a module that only they can teach, along with the name of the module and a description of the part.

m)A list of students who have failed a module that was delivered in semester 1 by a lecturer who did not know their subject.
n)A count of module parts being delivered in Semester 2 where the module parts were being delivered by a lecturer who knew how to teach the module part.
o)A list of module part tutors who can deliver a part of a module that nobody else can, with a count of the number of module parts that can only be taught by them.

if anyone can help me i would be very grateful...actually i appreciate you even reading the whole of this post..as i know its pretty LONG!!

anywayz...look forward to hearing from your solutions...

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-11-27 : 07:53:43

Homework?

E 12°55'05.63"
N 56°04'39.26"

kingkhan
Starting Member

6 Posts

Posted - 2008-11-27 : 07:57:21

Yess(lol)....can you help me??
is there any other info that i can post to make this a little more easier to understand and read???

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-27 : 08:39:38

quote:
Originally posted by kingkhan

Yess(lol)....can you help me??
is there any other info that i can post to make this a little more easier to understand and read???

Suggest you try these out yourself taking help of books online and post if you face any difficulty. DOnt expect answers for these to be spoon feeded to you..

kingkhan
Starting Member

6 Posts

Posted - 2008-11-28 : 06:37:34

quote:
Originally posted by visakh16
Suggest you try these out yourself taking help of books online and post if you face any difficulty. DOnt expect answers for these to be spoon feeded to you..

Books online???...if you know of any that are easy (i.e reader friendly)...then please share...as any help would be much appreciated thanx..

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2008-11-28 : 07:51:09

books on-line ins't a reference to some sql library of books -- it's the Microsoft help for sql server.

I assume this is sql server 2005. In this case go to:
http://www.microsoft.com/downloads/details.aspx?familyid=BE6A2C5D-00DF-4220-B133-29C1E0B6585F&displaylang=en

and download. All the reference you could want.

Books on line if generally referred to as BOL here and in other places.

Make a stab at doing this for yourself. People here will help but don't expect them to do your work for you.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

kingkhan
Starting Member

6 Posts

Posted - 2008-11-28 : 08:22:05

quote:
Originally posted by Transact Charlie

books on-line ins't a reference to some sql library of books -- it's the Microsoft help for sql server.

I assume this is sql server 2005. In this case go to:
http://www.microsoft.com/downloads/details.aspx?familyid=BE6A2C5D-00DF-4220-B133-29C1E0B6585F&displaylang=en

and download. All the reference you could want.

Books on line if generally referred to as BOL here and in other places.

Make a stab at doing this for yourself. People here will help but don't expect them to do your work for you.

Charlie

Thanx Charlie...will check the site and see if i can make heads or tails of it...
i will post codes that i think will work for the resolve of the queries....i only ask that you keep an eye on this post,(in case im doing something completely wrong), please...

thanx again

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2008-11-28 : 08:42:34

No problem. I check frequently (though people like visakh16 usually manage to get there first!) grrrr.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2008-11-28 : 08:54:20

If this is homework for a class then here's a thought: maybe just read the book that the class assigns you to read?

And consider asking the teacher for help?

- Jeff
http://weblogs.sqlteam.com/JeffS

kingkhan
Starting Member

6 Posts

Posted - 2008-11-28 : 10:05:32

quote:
Originally posted by jsmith8858

If this is homework for a class then here's a thought: maybe just read the book that the class assigns you to read?

And consider asking the teacher for help?

- Jeff
http://weblogs.sqlteam.com/JeffS

Cheers Jeff,

i already tried the "getting as much info off ur lecturer" approach......but as its a final assignment, he's not giving up too many hints..

I will however, read up on all the past lecture notes...and see if i can dig anything out...

Thanx again Jeff

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-28 : 11:09:06

quote:
Originally posted by kingkhan

quote:
Originally posted by jsmith8858

If this is homework for a class then here's a thought: maybe just read the book that the class assigns you to read?

And consider asking the teacher for help?

- Jeff
http://weblogs.sqlteam.com/JeffS

Cheers Jeff,

i already tried the "getting as much info off ur lecturer" approach......but as its a final assignment, he's not giving up too many hints..

I will however, read up on all the past lecture notes...and see if i can dig anything out...

Thanx again Jeff

Dont hesitate to ask if you've any doubts...but only after you've tried out first yourself.

kingkhan
Starting Member

6 Posts

Posted - 2008-12-01 : 09:58:56

quote:

Dont hesitate to ask if you've any doubts...but only after you've tried out first yourself.

I will do that thanx...Visakh16

hopefully i will have some code by midday thursday, as i will be in practical at uni...
have a 2000 word essay to type by friday in the meantime tho...
its about "The Rise and Fall of Digital Rights Management"....yippeee
takecare and please keep an eye on this post...thanx

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources