In: Computer Science
DROP TABLE registration;
DROP TABLE sections;
DROP TABLE courses;
DROP TABLE students;
DROP TABLE instructors;
CREATE TABLE courses (
cid varchar2(9) NOT NULL,
cname varchar2(50) NOT NULL,
credits number(1) DEFAULT 3,
prereq varchar2(9),
CONSTRAINT pk_courses PRIMARY KEY (cid)
);
INSERT INTO courses VALUES ('IS 201','Java
Programming',3,null);
INSERT INTO courses VALUES ('IS 202','C++ Programming',3,'IS
201');
INSERT INTO courses VALUES ('IS 301','Web Design',3,null);
INSERT INTO courses VALUES ('IS 331','Business
Applications',3,null);
INSERT INTO courses VALUES ('IS 401','Database Design',3,'IS
331');
INSERT INTO courses VALUES ('IS 413','SQL Programming',3,'IS
401');
CREATE TABLE students (
sid char(9) NOT NULL,
lname varchar(30) NOT NULL ,
fname varchar2(30) NOT NULL ,
gender char(1) NOT NULL ,
addr varchar2(50) NOT NULL ,
city varchar2(20) NOT NULL ,
state char(2) NOT NULL ,
zip varchar2(10) NOT NULL ,
phone varchar2(14) NULL ,
birthdate date NULL ,
tuitionRate number(7, 2) NOT NULL ,
creditsEarned number(3) NOT NULL ,
CONSTRAINT pk_students PRIMARY KEY (sid)
);
INSERT INTO students VALUES ('100000001','Lee','George','M','15
Merchant
Street','Honolulu','HI','96818','808-524-3333','01-MAY-1965',5000.00,47);
INSERT INTO students VALUES
('100000002','Yamamoto','Bill','M','3432 Birch
Street','Honolulu','HI','96814','808-522-2212','03-JUN-1958',5000.00,12);
INSERT INTO students VALUES ('100000003','Carver','Hillary','F','22
Aardvark
Avenue','Washington','DC','10101','800-212-3246','23-AUG-1991',5000.00,69);
INSERT INTO students VALUES ('100000004','King','Linda','F','341
Kaapahu
Road','Paauilo','HI','96776',NULL,'01-SEP-1998',4399.00,6);
INSERT INTO students VALUES
('100000005','Rollings','Willie','M','1221 Ala Moana
Blvd','Honolulu','HI','96814',NULL,NULL,4888.00,0);
INSERT INTO students VALUES
('100000006','Alexander','Wanda','F','93-123 Old Mill
Road','Honokaa','HI','96727','808-776-2313','02-OCT-1997',5000.00,99);
INSERT INTO students VALUES ('100000007','Carver','Bill','M','33
Richards
Street','Honolulu','HI','96813',NULL,'22-OCT-1990',5000.00,0);
INSERT INTO students VALUES ('100000008','DeLuz','Bob','M','102
Orleans Ave','San
Francisco','CA','97745','808-555-3324','01-MAR-1998',5000.00,14);
INSERT INTO students VALUES ('100000009','Lee','Lisa','F','45 Fong
Avenue','San
Francisco','CA','97767','808-333-3432','21-APR-1997',5000.00,26);
INSERT INTO students VALUES ('100000010','Garcia','Sherrie','F','2
S.
Beretania','Honolulu','HI','96817','808-663-4453','03-DEC-1997',5000.00,29);
INSERT INTO students VALUES ('100000011','Kamaka','Oscar','M','34
Kapolani
Blvd','Honolulu','HI','96813','808-533-3332','12-FEB-1998',5000.00,0);
CREATE TABLE instructors (
inId char(9) NOT NULL,
iLname varchar2(30) NOT NULL,
iFname varchar2(30) NOT NULL,
rank varchar2(10) NOT NULL,
office varchar2(10) NULL,
phone varchar2(20) NULL,
salary number(8,2) DEFAULT 0,
CONSTRAINT pk_instructors PRIMARY KEY (inID)
);
INSERT INTO instructors VALUES
('200000001','Souza','Edward','Lecturer','LM101','808-533-4241',5000.00);
INSERT INTO instructors VALUES
('200000002','Tenzer','Laurie','Associate','LM102','808-533-4244',5000.00);
INSERT INTO instructors VALUES
('200000003','Otake','Bill','Assistant','MR101','808-533-4247',5800.00);
CREATE TABLE sections (
crn char(4) NOT NULL,
cid varchar2(9) NOT NULL,
section char DEFAULT 'A',
inId char(9) NOT NULL,
days varchar2(10) DEFAULT 'TBA',
time varchar2(16) DEFAULT 'TBA',
room varchar2(10) NULL,
CONSTRAINT pk_sections PRIMARY KEY (crn),
CONSTRAINT fk_inid_sections FOREIGN KEY (inid) REFERENCES
instructors(inid),
CONSTRAINT fk_cid_sections FOREIGN KEY (cid) REFERENCES
courses(cid)
);
INSERT INTO sections VALUES ('1000','IS
201','A','200000003','MWF','08:00 - 08:50','CL100');
INSERT INTO sections VALUES ('1001','IS
201','B','200000003','MWF','09:00 - 09:50','CL100');
INSERT INTO sections VALUES ('1002','IS
201','C','200000001','TTh','08:00 - 09:15','CL102');
INSERT INTO sections VALUES ('1003','IS
301','A','200000002','TTh','09:30 - 10:45','CL340');
INSERT INTO sections VALUES ('1004','IS
301','B','200000002','MWF','09:00 - 09:50','CL340');
INSERT INTO sections VALUES ('1005','IS
413','A','200000001','MWF','09:00 - 09:50','CL230');
CREATE TABLE registration (
crn char(4) NOT NULL,
sid char(9) NOT NULL,
CONSTRAINT pk_registration PRIMARY KEY (crn,sid),
CONSTRAINT fk_crn_registration FOREIGN KEY (crn) references
sections(crn),
CONSTRAINT fk_sid_registration FOREIGN KEY (sid) references
students(sid)
);
INSERT INTO registration VALUES ('1000','100000001');
INSERT INTO registration VALUES ('1003','100000001');
INSERT INTO registration VALUES ('1005','100000001');
INSERT INTO registration VALUES ('1001','100000002');
INSERT INTO registration VALUES ('1004','100000002');
INSERT INTO registration VALUES ('1005','100000003');
INSERT INTO registration VALUES ('1002','100000004');
INSERT INTO registration VALUES ('1003','100000004');
INSERT INTO registration VALUES ('1005','100000004');
INSERT INTO registration VALUES ('1000','100000005');
INSERT INTO registration VALUES ('1003','100000005');
INSERT INTO registration VALUES ('1002','100000008');
INSERT INTO registration VALUES ('1004','100000008');
INSERT INTO registration VALUES ('1002','100000009');
INSERT INTO registration VALUES ('1005','100000009');
INSERT INTO registration VALUES ('1002','100000010');
INSERT INTO registration VALUES ('1005','100000010');
INSERT INTO registration VALUES ('1000','100000011');
INSERT INTO registration VALUES ('1003','100000011');
INSERT INTO registration VALUES ('1005','100000011');
commit;
Solution
As per the given table structure and populated data, the solution for given problems are as follows:
1. List the course ID, course name, section, day, time, and room for all course sections. Order by course ID and section.
To achieve the above given result, we need to join the tables courses and sections ON matching courseid(course.cid=section.cid). Also, Order by clause is used to make the records order by course ID and section.
SQL QUERY:
SELECT c.cid AS CourseID, c.cname AS CourseName,s.section,s.days AS Day,s.time,s.room from courses c INNER JOIN sections s ON c.cid=s.cid ORDER BY c.cid,s.section;
Screenshot of SQL CODE Running Output
2. Repeat for CRN 1003 only.
To achieve the above given result, we need to join the tables courses and sections ON matching courseid(course.cid=section.cid) and filter the records with a WHERE CLAUSE( crn='1003' ) to match the records from crn='1003' only.
SQL QUERY:
SELECT c.cid AS CourseID, c.cname AS CourseName,s.section,s.days,s.time,s.room from courses c INNER JOIN sections s ON c.cid=s.cid WHERE crn='1003' ORDER BY c.cid,s.section;
Screenshot of SQL CODE Running Output
3. List the course ID, course name, section, instructor name, day, time, and room for all course sections.
To achieve the above given result, we need to join the tables courses and sections ON matching courseid(course.cid=section.cid) and also, join the table Instructor ON matching 'inid'.
SQL QUERY:
SELECT c.cid AS CourseID, c.cname AS
CourseName,s.section,i.iLName AS Instructor_LName,i.iFName AS
Instructor_FName,s.days AS Day,s.time,s.room from
courses c INNER JOIN sections s ON c.cid=s.cid
INNER JOIN instructors i ON s.inid=i.inid
ORDER BY c.cid,s.section;
Screenshot of SQL CODE Running Output
4. List the course ID, course name, section, student ID, and student name for CRN 1003. Display the list in ascending order of student last and first names.
To achieve the above given result, we need to join the tables courses and sections ON matching courseid(course.cid=section.cid) , table registration ON matching 'crn' of sections and registration, and the table students ON matching 'sid' of registration and students.
Also, Order by clause is used to make the records order by student's LName and FName in Ascending order.
We have used a WHERE Condition(WHERE s.crn='1003' ) to filter the records only from crn='1003'
SQL QUERY:
SELECT c.cid AS CourseID, c.cname AS
CourseName,s.section,r.sid,t.lname AS StudentLName,t.fname AS
StudentFName FROM courses c INNER JOIN sections s ON
c.cid=s.cid
INNER JOIN registration r ON s.crn=r.crn
INNER JOIN students t ON r.sid=t.sid
WHERE s.crn='1003' ORDER BY t.lname ASC,t.fname ASC;
Screenshot of SQL CODE Running Output
.