simple SQL search - Programmers Heaven

Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

Categories

simple SQL search

zeolitezeolite Posts: 78Member
Suppose I need to find out the maximum number of students taking in class 'VS23' at any one time and suring which time slot this arises in the following schema:

CREATE TABLE Subject (
subject_id INTEGER IDENTITY NOT NULL,
namne VARCHAR (30),
PRIMARY KEY (subject_id));

CREATE TABLE Class (
class_id INTEGER IDENTITY NOT NULL,
subject_id FOREIGN KEY REFERENCES Subject (subject_id) NOT NULL,
teacher_id FOREIGN KEY REFERENCES Teacher (teacher_id) NOT NULL,
room_id INTEGER NOT NULL,
day VARCHAR (10) NOT NULL,
slot INTEGER NOT NULL,
PRIMARY KEY (class_id));

CREATE TABLE TimeTable (
student_id FOREIGN KEY REFERENCES Student (student_id),
class_id IFOREGIN KEY REFERENCES Class (class_id));

CREATE TABLE Student (
student_id INTEGER IDENTITY NOT NULL,
first_name VARCHAR (20) NOT NULL,
surname VARCHAR (20) NOT NULL,
PRIMARY KEY (subject_id));

I know this would probably require use of the MAX and COUNT functions, but have little idea of how to actually implement these. Any suggestions would be greatly appreciated.

Also, is it necessary to specify that a table has no primary key, such as in the Timetable table?


Thanks in advance,
Dan :-)
Sign In or Register to comment.