OraFAQ Forum: SQL & PL/SQL » Additional Query help

Home » SQL & PL/SQL » SQL & PL/SQL » Additional Query help

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Additional Query help [message #7975]

Sat, 19 July 2003 05:24

M.Craighead Jr.
Messages: 1
Registered: July 2003

Junior Member

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') OR (grade = 'F') OR (grade = 'N')));

again thanks for assistance... I'm tring to get this done for a middle school db and am lost and quite confused....

Report message to a moderator