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

Welcome to the new platform of Programmer's Heaven! We apologize for the inconvenience caused, if you visited us from a broken link of the previous version. The main reason to move to a new platform is to provide more effective and collaborative experience to you all. Please feel free to experience the new platform and use its exciting features. Contact us for any issue that you need to get clarified. We are more than happy to help you.

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.