0

need help here's code I'm almost finish I'm trying to find out how can i search down to the student who is taking at least two database classes here's the code

drop table student;  

create table student
	(student_id       varchar(10) primary key not null,
	 student_name     varchar(10),
	 major            varchar(20),
	 gpa		  decimal(10,1));




insert into student values('12345', 'Bob', 'computer science', '4.0');
insert into student values('23456', 'Tim', 'art', '3.0');
insert into student values('54321', 'Rob', 'biology', '3.5');
insert into student values('98765', 'Sue', 'history', '2.5');
insert into student values('87654', 'Cindy', 'chemistry', '3.1');
insert into student values('11111', 'Kenny', 'computer science', '3.2');
insert into student values('22222', 'Renee', 'mathematics', '3.4');

drop table class;

create table class
	(schedule_num		int not null, 
	 semester		varchar(12), 
	 course_name		varchar(25),
	 course_num		varchar(11),
	 credit			int,
	 department		varchar(25),
	 meeting_time		varchar2(10),
	 meeting_place 		varchar(20),
	 enrollment_limit	int,
	 primary key(schedule_num, semester));

insert into class values(1, 'spring2012', 'math','math 101', 3,'mathematics', '09.00.00', 'wilson hall', 50);
insert into class values(2, 'spring2012', 'discrete math','math 112', 3,'mathematics', '010.30.00', 'MLK hall', 30);
insert into class values(14, 'spring2012', 'linear algebra','math 123', 4,'mathematics', '08.00.00', 'DH hall', 25);
insert into class values(34, 'spring2012', 'intro to C','Csci 2130', 3,'computer science', '01.15.00', 'spaugh hall', 30);
insert into class values(7, 'spring2012', 'shakespeare','engl 3323', 2,'english', '03.00.00', 'SF hall', 30);
insert into class values(3, 'spring2012', 'intro to database','csci 6600', 2,'computer science', '03.00.00', 'SF hall', 30);
insert into class values(5, 'spring2012', 'database design','csci 6620', 2,'computer science', '03.00.00', 'SF hall', 30);
insert into class values(6, 'Fall2011', 'intro to education','educ 1101', 2,'education', '03.00.00', 'roger hall', 30);
insert into class values(8, 'Fall2011', 'intro to sociology','psyc 102', 2,'psychology', '03.00.00', 'brees hall', 30);
insert into class values(9, 'Fall2011', 'intro to programming','csci 1102', 3,'computer science', '03.00.00', 'SF hall', 30);
insert into class values(10, 'Fall2011', 'programming logic','csci 1111', 3,'computer science', '04.00.00', 'SF hall', 30);


drop table instructor;

create table instructor
	(name		varchar2(10) primary key not null,
	 department	varchar2(20),
	 office		varchar2(10),
	 title		varchar2(15));

insert into instructor values('Mr.Bill', 'mathematics', 'Rm 1234', 'Professor');
insert into instructor values('Mr.John', 'mathematics', 'Rm 4321', 'Professor');
insert into instructor values('Mr.James', 'computer science', 'Rm 1122', 'Professor');
insert into instructor values('Ms.Jill', 'english', 'Rm 2233', 'Professor');
insert into instructor values('Mr.Drew', 'mathematics', 'Rm 1133', 'Professor');
insert into instructor values('Ms.Sally', 'education', 'Rm 1134', 'Professor');
insert into instructor values('Mr.Jake', 'psychology', 'Rm 5133', 'Professor');
insert into instructor values('Mr.Tucker', 'computer science', 'rm 6465', 'Professor');
	 
drop table enrolls;

create table enrolls
	(student_id	varchar2(7) not null,
	 schedule_num	integer,
	 semester	varchar2(12),
	 grade		varchar2(3), 
	 primary key (student_id, schedule_num, semester));

insert into enrolls values('12345', 1, 'spring2012', '');
insert into enrolls values('23456', 2, 'spring2012', '');
insert into enrolls values('54321', 7, 'spring2012', '');
insert into enrolls values('98765', 14, 'spring2012', '');
insert into enrolls values('87654', 1, 'spring2012', '');
insert into enrolls values('11111', 9, 'spring2012', '');
insert into enrolls values('11111', 10, 'spring2012', '');
insert into enrolls values('54321', 6, 'spring2012', '');
insert into enrolls values('54321', 3, 'spring2012', '');
insert into enrolls values('54321', 5, 'spring2012', '');
insert into enrolls values('11111', 3, 'spring2012', '');
insert into enrolls values('11111', 5, 'spring2012', '');



drop table teaches;

create table teaches
	(name		varchar2(10) primary key not null,
	 schedule_num	int,
	 semester	varchar2(12));

insert into teaches values('Mr.Bill',1, 'spring2012');
insert into teaches values('Mr.John',2, 'spring2012');
insert into teaches values('Mr.Drew',14, 'spring2012');
insert into teaches values('Mr.James',34, 'spring2012');
insert into teaches values('Ms.Jill',7,'spring2012');
insert into teaches values('Ms.Sally',6,'Fall2011');
insert into teaches values('Mr.Joe',9,'Fall2011');
--insert into teaches values('Mr.Jake',8,'Fall2011');
insert into teaches values('Mr.Tucker',10,'spring2012');

here is my query

select student.student_id, student_name from enrolls, class, student where course_num like 'csci_6___' and class.schedule_num =enrolls.schedule_num and student.student_id = enrolls.student_id;

how can i just pull the students that are taking at least two database classes?
the code just above is what i have so far the class is csci 6___ the db class. (above 6 thousand)

3
Contributors
3
Replies
4
Views
5 Years
Discussion Span
Last Post by babyDBA
0

“database classes”, what does it mean?
Just like "csci_6___"? what about csci 7000?

It's just for 6000 level classes so anywhere from csci 6000-csci6999
thanks for your help. If anything else is not clear please let me know.

0

Just add two line on the end of your query

SELECT student.student_id, student_name 
FROM enrolls, class, student 
WHERE course_num LIKE 'csci_6___' 
  AND class.schedule_num =enrolls.schedule_num 
  AND student.student_id = enrolls.student_id
GROUP BY student.student_id, student_name
HAVING COUNT(*) >= 2
This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.