students who have a birthday in the same month (was "SQL Query")

[paul83]

Hi everyone,

I'm working on a database in Oracle10G containing several tables to do with
modules, students etc. I'm having some trouble with the following query:

List for each module the names of all students who have a birthday in the same month, showing the results in birthday sequence.

I've had a go at it by connecting up the tables in the query though I'm not sure what to do when it comes to the birthday. Here's my attempt so far:

select student.studfname "First Name", student.studsname "Surname",
module.modulecode "Code", module.moduletitle "Title"
from student, module, attendance, class
where student.studno = attendance.studno and attendance.classcode = class.classcode and class.modulecode = module.modulecode;

Here are my tables (I've left out irrelevant tables):

drop table student cascade constraints;
drop table module cascade constraints;
drop table attendance cascade constraints;
drop table class cascade constraints;

create table module (
modulecode varchar2(10) not null,
moduletitle varchar2(100) not null,
constraint pk_module PRIMARY KEY (modulecode));

insert into module values ('FTR3149','English');
insert into module values ('KPD0142','Maths');
insert into module values ('LXC7509','Computing');
insert into module values ('TIQ1185','History');
insert into module values ('OZM7290','Science');
insert into module values ('IAL3943','French');
insert into module values ('JYH6391','Sport');

create table attendance (
studno varchar2(8) not null,
classcode varchar2(10) not null,
attendance varchar2(1),
constraint ck_attendance CHECK (attendance in ('Y','y','N','n')),
constraint pk_attendance PRIMARY KEY (studno, classcode));

insert into attendance values ('stud01','cl02','Y');
insert into attendance values ('stud02','cl02','Y');
insert into attendance values ('stud06','cl02','N');
insert into attendance values ('stud07','cl02','Y');
insert into attendance values ('stud03','cl04','N');
insert into attendance values ('stud04','cl04','N');
insert into attendance values ('stud05','cl04','Y');

create table class (
classcode varchar2(10) not null,
classtype varchar2(10),
constraint ck_classtype CHECK (classtype in

('Lecture','Practical','Tutorial','lecture','practical','tutorial')),
classdate date,
classtime number(4),
classroom number(3),
constraint ck_classroom CHECK (classroom BETWEEN 1 and 500),
tutorcode varchar2(10) not null,
modulecode varchar2(10) not null,
constraint pk_class PRIMARY KEY (classcode));

insert into class values ('cl01','Lecture','21-SEP-05',1415,32,'tutor04','CIT1340');
insert into class values ('cl02','Practical','25-SEP-
05',1015,18,'tutor09','CIS1365');
insert into class values ('cl03','Tutorial','03-OCT-
05',1715,4,'tutor01','CIP1999');
insert into class values ('cl04','Lecture','10-OCT-
05',0915,18,'tutor07','CIS1317');
insert into class values ('cl05','Tutorial','28-NOV-
05',1215,55,'tutor05','CIS1365');

create table student (
studno varchar2(8) not null,
studfname varchar2(15) not null,
studsname varchar2(15) not null,
sex varchar2(1),
constraint ck_studentsex CHECK (sex in ('M','m','F','f')),
age number(3),
constraint ck_studentage CHECK (age BETWEEN 1 and 150),
birth date,
coursestart date,
coursecode varchar2(10) not null,
constraint pk_studno PRIMARY KEY (studno));

insert into student values ('stud01','Jack','England','M',21,'02-SEP-83','20-SEP-
05','c01');
insert into student values ('stud02','John','Brown','M',20,'23-JAN-84','20-SEP-
03','c01');
insert into student values ('stud03','David','Smith','M',19,'18-APR-80','20-SEP-
04','c02');
insert into student values ('stud04','Jane','Moor','F',22,'13-DEC-83','20-SEP-
02','c02');
insert into student values ('stud05','Joe','Black','M',23,'20-MAY-77','20-SEP-
03','c02');
insert into student values ('stud06','Mike','Owen','M',24,'07-SEP-82','20-SEP-
01','c01');
insert into student values ('stud07','Julie','Lodge','F',20,'28-JAN-84','20-SEP-
04','c01');

Thanks,

Paul

[longneck]

see this thread where i made an idiot of myself and rudy came to my rescue.

http://www.sitepoint.com/forums/show...ight=birthdate

[paul83]

I guess that's an easy mistake to make. How would i apply the query from your thread to the query above?

[r937]

sorry, paul, we're not going to just do your homework assignment for you, you're going to have to work for it

how do you think your query should be changed to include a birthday calculation?

the first step would be to write the query so that it actually shows the students' birthdays, right?

oh, and why is age one of the student columns? you do realize that it will almost certainly contain invalid information as soon as any data is entered, right?

[paul83]

Hi rudy, I hope i'm not giving the impression that I try to get people to do my work for me all the time. I rarely ask for help with things except i'm genuinly stuck on this one. I've had a further look at using dates and have modified my previous query to this:

select student.birth "Birth Date", student.studfname "First Name", student.studsname "Surname"
from student, module, attendance, class
where student.studno = attendance.studno and attendance.classcode = class.classcode and class.modulecode = module.modulecode
and ( DATEPART(month, GETDATE()) == DATEPART(month, GETDATE()) )
group by student.birth;

I realise this query probably wouldn't run but it's the closest thing i have so far. Any guidance would be great.

Thanks,

Paul

[r937]

well, for starters, the following is always going to be true --

.. DATEPART(month, GETDATE()) == DATEPART(month, GETDATE())



second comment: you cannot GROUP BY only some of the non-aggregate columns in the SELECT

in fact, you don't need GROUP BY for this problem at all

finally, i would suggest that you will find queries a lot easier to write and debug if you use JOIN syntax --

select ...
  from student
inner
  join attendance
    on attendance.studno
     = student.studno
inner
  join class
    on class.classcode 
     = attendance.classcode 
inner
  join module
    on module.modulecode 
     = class.modulecode

notice how each join has an ON condition that joins its table to a previously-mentioned table

[Dr John]

As a former lecturer and a current student, I certainly recognise homework when I see it (hint: we were supplied with standard data to work from in the same format as the bit after the words "here are my tables..." drop...drop...drop... ). And I don't do other people's homework either.

I'd suggest start by ignoring the bulk of the question they set you, and just work on the date bit and the student table, ignore the other tables initially. Get a list of students and birthdays first, then consider how to get the month listed instead of the actual birthday. Then perhaps a phrase beginning with Or*** ** might help a bit.