Question

In: Computer Science

List the course ID, course name, section, day, time, and room for all course sections. Order...

  1. List the course ID, course name, section, day, time, and room for all course sections. Order by course ID and section. Note you must join the courses and sections tables for this question.
  1. Repeat for CRN 1003 only.
  2. List the course ID, course name, section, instructor name, day, time, and room for all course sections. Be sure to join the proper tables.
  3. 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. Be sure to join the proper tables.

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;

Solutions

Expert Solution

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

.


Related Solutions

List department name, employee id, and employee name for all employees in department name order. Repeat...
List department name, employee id, and employee name for all employees in department name order. Repeat for department #10 only. List the course ID, course name, section, instructor name, day, time, and room for all course sections. 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. DROP TABLE registration; DROP TABLE sections; DROP TABLE courses; DROP TABLE students; DROP TABLE instructors; CREATE...
List customer id, customer full name (Last name, full name, state) for those customers that have...
List customer id, customer full name (Last name, full name, state) for those customers that have ordered more than once. List customers (order id, customer id, and customer last name) that had more than 2 -- products in their order. Order your result based on customer id followed by order id SQL SERVER DATABASE
Description Write a program that prints out your name, the course ID of this class, what...
Description Write a program that prints out your name, the course ID of this class, what programming/computer courses you've taken. Ask the user for two numbers. Show the sum of the two numbers, the difference, the product and the quotient. For the difference subtract the second number from the first, for the quotient, use the first number as the numerator(dividend) and the second as the denominator(divisor). Sample Output: My name is Jianan Liu, I'm in course CS36. I've taken: C...
Develop an algorithm to implement an employee list with employee ID ,name designation and department using...
Develop an algorithm to implement an employee list with employee ID ,name designation and department using link list and perform the following operation on the list i)add employee details based on department ii)remove employee details iv)count the number of employee in each department
Section A: The Project Charter Populate all sections of this Project Charter Template with content related...
Section A: The Project Charter Populate all sections of this Project Charter Template with content related to the Getta Byte Project Charter and improve its content. There are sections in the template which are not covered in the video for which the student is still responsible to complete by elaborating and adding its missing content. Here is a list of the Project Charter Template sections. Mission or purpose Project and product description Objectives Milestones Budget User acceptance criteria High-Level assumptions...
JAVA PROGRAMMING Part 1 Create a class Student, with attributes id, first name, last name. (All...
JAVA PROGRAMMING Part 1 Create a class Student, with attributes id, first name, last name. (All the attributes must be String) Create a constructor that accepts first name and last name to create a student object. Create appropriate getters and setters Create another class StudentOperationClient, that contains a main program. This is the place where the student objects are created and other activities are performed. In the main program, create student objects, with the following first and last names. Chris...
Change program linkedListClass to the linked list for student items (a student item contains id, name...
Change program linkedListClass to the linked list for student items (a student item contains id, name and score, where the id is used as key) and test it in the main method. You can define a student item using a class of student. given the following codes; import java.util.*; public class linkedListClass {    public Node header;    public linkedListClass()    {        header = null;    }    public final Node Search(int key)    {        Node...
Step 1: Write SQL to List the information delivered (PO number, order date, vendor ID &...
Step 1: Write SQL to List the information delivered (PO number, order date, vendor ID & name, product ID & name, Product quantity) of transaction records, sorted by order date? Step 2: Write SQL For orders that were placed between 9/1/2016 and 9/30/2016, list (product IDs, product names, order dates, PO numbers) sort by order date Step 3: Write SQL and find In the month of August, which vendor received the smallest order for a product in terms of dollars?...
1) Standard MLA Heading. Top-left: Your name, professor's name, course number/section, and date. Double-space your paper...
1) Standard MLA Heading. Top-left: Your name, professor's name, course number/section, and date. Double-space your paper and use 12-pt. font with 1-inch margins all around. 2) Title. Create your own title for your essay. Don't call it "Final Exam" or the source essay title. 3) Opening Paragraph. Write 1 opening (introductory) paragraph. Make sure that this introductory paragraph includes your thesis statement, along with the source essay's title and author, properly formatted and spelled. Use transitional words or phrases to...
REQUIRED (Prepare and Deliver in The Following Order) Prepare a Cover Page as Follows. Name(s), Course,...
REQUIRED (Prepare and Deliver in The Following Order) Prepare a Cover Page as Follows. Name(s), Course, Assignment, Current Date No Bulky Binder or Plastic Covers Please just Pages of Paper stapled together in Order Pictures on Cover or elsewhere are not required but are encouraged and look great Prepare a Table of Contents (Name of Each Report and the page the report starts and ends on) Prepare the Accounting Journal, record all Accounting Entries based on the Transactions Below into...
ADVERTISEMENT
ADVERTISEMENT
ADVERTISEMENT