Query Help for middle school project DB

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

Other Forums

Other Topics

Query Help for middle school project DB

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2003-07-22 : 07:41:25

Milton writes "This is not a SQL Server issue...Sorry to bother but I'm desperate with a solution.... Sorry to be a bother.Any help will be well appreciated....

First I'd like to thank all for your assistance.... I'm desperatly tring to learn as much SQL as I can as quickly as I can. I have 3 more questions if it is not to much of a bother. Also could you point me in the direction of a good book that will be straight forward for a novice...
1) create a script to add a new faculty member and change all student advises currently assigned to f_id 1 to the new faculty member
2) list the course call id, section number, term description, and grade for every course taken by Sarah Miller.... I came up with the following
select course.call_id,course_section.sec_num,term.term_id,grade,s_last
from course,course_section,term,enrollment,student
where course_section.course_id = course.course_id
and student.s_id = enrollment.s_id
the tables will be at end of post....
3) calculate the total number of students taught by john blanchard during the spring 2000 term.
CREATE TABLE LOCATION
2 (loc_id NUMBER(6),
3 bldg_code VARCHAR2(10),
4 room VARCHAR2(6),
5 capacity NUMBER(5),
6 CONSTRAINT location_loc_id_pk PRIMARY KEY (loc_id));
Table created.
SQL> CREATE TABLE faculty
2 (f_id NUMBER(6),
3 f_last VARCHAR2(30),
4 f_first VARCHAR2(30),
5 f_mi CHAR(1),
6 loc_id NUMBER(5),
7 f_phone VARCHAR2(10),
8 f_rank VARCHAR2(8),
9 f_pin NUMBER(4),
10 f_image BLOB,
11 CONSTRAINT faculty_f_id_pk PRIMARY KEY(f_id),
12 CONSTRAINT faculty_loc_id_fk FOREIGN KEY (loc_id) REFERENCES location(loc_id));
Table created.
SQL> CREATE TABLE student
2 (s_id NUMBER(6),
3 s_last VARCHAR2(30),
4 s_first VARCHAR2(30),
5 s_mi CHAR(1),
6 s_add VARCHAR2(25),
7 s_city VARCHAR2(20),
8 s_state CHAR(2),
9 s_zip VARCHAR2(9),
10 s_phone VARCHAR2(10),
11 s_class CHAR(2),
12 s_dob DATE,
13 s_pin NUMBER(4),
14 f_id NUMBER(6),
15 CONSTRAINT student_s_id_pk PRIMARY KEY (s_id),
16 CONSTRAINT student_f_id_fk FOREIGN KEY (f_id) REFERENCES faculty(f_id));
Table created.
SQL> CREATE TABLE TERM
2 (term_id NUMBER(6),
3 term_desc VARCHAR2(20),
4 status VARCHAR2(20),
5 CONSTRAINT term_term_id_pk PRIMARY KEY (term_id),
6 CONSTRAINT term_status_cc CHECK ((status = 'OPEN') OR (status = 'CLOSED')));
Table created.
SQL> CREATE TABLE COURSE
2 (course_id NUMBER(6),
3 call_id VARCHAR2(10),
4 course_name VARCHAR2(25),
5 credits NUMBER(2),
6 CONSTRAINT course_course_id_pk PRIMARY KEY(course_id));
Table created.
SQL> CREATE TABLE COURSE_SECTION
2 (c_sec_id NUMBER(6),
3 course_id NUMBER(6) CONSTRAINT course_section_courseid_nn NOT NULL,
4 term_id NUMBER(6) CONSTRAINT course_section_termid_nn NOT NULL,
5 sec_num NUMBER(2) CONSTRAINT course_section_secnum_nn NOT NULL,
6 f_id NUMBER(5),
7 day VARCHAR2(10),
8 time DATE,
9 loc_id NUMBER(6),
10 max_enrl NUMBER(4) CONSTRAINT course_section_maxenrl_nn NOT NULL,
11 CONSTRAINT course_section_csec_id_pk PRIMARY KEY (c_sec_id),
12 CONSTRAINT course_section_cid_fk FOREIGN KEY (course_id) REFERENCES course(course_id),
13 CONSTRAINT course_section_loc_id_fk FOREIGN KEY (loc_id) REFERENCES location(loc_id),
14 CONSTRAINT course_section_termid_fk FOREIGN KEY (term_id) REFERENCES term(term_id),
15 CONSTRAINT course_section_fid_fk FOREIGN KEY (f_id) REFERENCES faculty(f_id));
Table created.
SQL> CREATE TABLE ENROLLMENT
2 (s_id NUMBER(6),
3 c_sec_id NUMBER(6),
4 grade CHAR(1),
5 CONSTRAINT enrollment_pk PRIMARY KEY (s_id, c_sec_id),
6 CONSTRAINT enrollment_sid_fk FOREIGN KEY (s_id) REFERENCES student(s_id),
7 CONSTRAINT enrollment_csecid_fk FOREIGN KEY (c_sec_id) REFERENCES course_section (c_sec_id),
8 CONSTRAINT enrollment_grade_cc
9 CHECK ((grade = 'A') OR (grade = 'B')
10 OR (grade = 'C') OR (grade = 'D')

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2003-07-23 : 12:57:41

2)
select course.call_id, course_section.sec_num, term.term_desc, grade
from course, course_section, term, enrollment, student
where student.s_first='Sarah' and student.s_last='Miller' and
enrollment.s_id=student.s_id and
enrollment.c_sec_id=course_section.c_sec_id and
course.term_id=term.term_id and course_section.course_id=course.course_id

- Vit

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2003-07-23 : 13:34:39

insert into faculty f_id, f_last, f_first, f_mi, loc_id, f_phone, f_rank, f_pin

236, 'Lenin', 'Vladimir', 'I', 34, '234-567-kremlin (can I speak to Volodia?)',

'rank - democratic tzar', 112

- Vit

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources