Using the database provided, Write WHERE clauses to get the following informatio

Question

Using the database provided, Write WHERE clauses to get the following information.

Sections Table:

1.What is the name of the Instructor teaching Section 30101? (List all Section

information, plus the Instructor’s fi

rst and last name)

2.What is the name of the course for Section 30108? (List all Section

information, plus the course name)

3.Give a list of all course names that instructor 6 is teaching. (List course

name, CRN, TimeDays, roomNo and instructor ID) Does

instructor 6 have a

scheduling conflict?

4.How many total credit hours is instructor 6 teaching?

5.Please give the CRN, Course Name, description and credithours, timedays

and roomno for all the courses being taught in room 1147. Are there any

conflicts

StudentSchedule Table:

6. Give a list of Student Names and IDs that are signed up for the section with

CRN=30101. (List Student’s first name, last name, ID and CRN)

7. Please list all the student names(first and last) that are in the section with

CRN=30115.

8. Please list all section information for all the sections that are being

attended by Student with id=6.

9. Please list all section information for all the sections that are being

attended each student.

10. Advanced(Multiple tables):

Please list the name of th

e course, the CRN and

the Names of all the students that are in the section with CRN=30115.

Table Tools Access Sign in File Home Create External Dato Database Tools Fields abe Tell me what you want to do Custom Custom Group Unassigned Objects Courses Instructors sections students CourselD CourseName Description CreditHours Click to Add- r Concepts Intro to Computers Hardware Maintainence Explore how an Operating System works Learning about SQL Intro to Programming Networking Fundamentals Develop Web-pages using HTML Intro to C# Programming Advanced C# Programming Intro to Java Intermediate Java Topics Object Oriented Analysis and Design Capstone Project Class Using Java to build Web Applications IST 1001 CIST 1122 CIST 1130 CIST 1220 CIST 1305 CIST Hardware Maintainence Course Operating Systems SQL Programming Program Design and Development Networking Fundamentals Web Development I Cf Programming I C# ProgrammingI Java Programming I Java Programming lI Analysis and Design - 1401 10 CIST 15 CIST 2341 CIST 2342 CI ST 2371 CIST 2372 CIST 2921 CIST 2931Systems Project ST 3273 Java Programming II Record: l-M 1 of 14 ??1 K: No Filter | Search Datasheet View ? O Type here to search 1:36 PM 2/1/2018

Explanation / Answer

Hi,

Ans 1-
select s.*, i.firstname,i.lastname
from sections s
inner join
instructors i
on s.instructor=i.ID
and s.CRN=30101;

Ans 2-
select s.*,coursename from courses c
join sections s
on c.courseid=s.courseid
and s.CRN=30108;

Ans 3-
select coursename, CRN, TimeDays, roomNo ,i.ID
from courses c
join sections s
on c.courseid=s.courseid
join instructors i
on s.instructor=i.ID
and s.instructor=6;

Ans 4-
select sum(c.credithours) from courses c
join sections s
on c.courseid=s.courseid
join instructors i
on s.instructor=i.ID
and s.instructor=6;

Ans 5-
select CRN, CourseName, description ,credithours, timedays, roomno
from courses c
join sections s
on c.courseid=s.courseid
join instructors i
on s.instructor=i.ID
and roomno="F1147";

Ans 6-
select firstname, lastname, ID ,CRN from studentschedule ss
join students s
on ss.student_id=s.id
and CRN=30101;

Ans 7-
select firstname, lastname, ID ,CRN from studentschedule ss
join students s
on ss.student_id=s.id
and CRN=30115;

Ans 8-
select s.* from studentschedule ss
join sections s
on ss.CRN=s.CRN
and studentid=6;

Ans 9-
select s.* from sections s
where s.CRN in(select distinct CRN from studentschedule);

Ans 10-
select firstname, lastname, ID ,CRN ,coursename
from studentschedule ss
join students s
on ss.student_id=s.id
join sections sec
on ss.CRN=sec.CRN
join courses c
on sec.courseid=c.courseid
and CRN=30115;

Related Questions

Navigate

• Browse (All)
• Browse U
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.