home / study / engineering / computer science / computer science questions and a
ID: 3733308 • Letter: H
Question
home / study / engineering / computer science / computer science questions and answers / sections crn courseid timedays roomno instructor 30101 cist 2341 mw1-5pm f1145 1 30102 cist ...
Question: Sections CRN CourseID TimeDays RoomNo Instructor 30101 CIST 2341 MW1-5pm F1145 1 30102 CI...
StudentSchedule
StudentID CRN 1-30108 1-30116
2-30101 2-30103 2-30109 3-30102 3-30105 3-30111 4-30114 4-30115 5-30114 5-30115 6-30103 6-30107 6-30109 7-30110 8-30110 8-30114 9-30102 9-30109 10-30101 10-30110 11-30106 11-30111 11-30116 12-30105 12-30106 13-30109 13-30110 13-30111 14-30101 15-30103 15-30107 15-30115 16-30101 16-30103 16-30110
For the StudentSchedule table the StudentID numbers are 1-16 and the CRN numbers are the 5 digit numbers ^^^ hope that doesn't confuse anyone
ID FirstName LastName Street City State Zip EMail GPA
1 Larry Jones 200 Larue St. Denver CO 89721 larry@yahoo.com 5.04
2 Debbie Gibson 101 Cambell St. Chicago IL 61721 debra@hotmail.com 4.68
3 James Henry 9019 Par Ln. Atlanta GA 30981 jh@yahoo.com 5.4
4 Tony Danza 100 Main St San Diego CA 90890 tony@yahoo.com 5.28
5 Marie Baker 313 Mockingbid Lane Seattle WA 98711 mbaker@hotmail.com 3.84
6 Mary Weathers 10 King St Denver CO 65334 maryw@bellsouth.net 3.72
7 Biily Wagner 777 Blake St Chicago IL 61900 billyw@comcast.net 4.68
8 Tim Allen 200 South St Detroit MI 54123
9 Gary Stevens 112 Plymouth Ln Ann Arbor MI 54123 garys@hotmail.com 5.04
10 Betsy Cook 101 Freeport St Atlanta GA 31010 bcook@yahoo.com 5.16
11 Susan Jones 200 West Ave Marietta GA 30060 sujones@hotmail.com 3.84
12 Larry Peters 3845 Beckford Ave San Diego CA 95123 frankperters@comcast.net 5.88
13 Terri March 9516 Hale Dr St Louis MO 63321 tmarch@bellsouth.net 4.92
14 Larry Hines 2222 Morningside Dr Marietta GA 30090 jhines@yahoo.com 4.56
15 Phil Gecko 45 East St Montgomery AL 41231 pgecko@yahoo.com 4.44
16 Tony Peters 200 Central Ave Miami FL 75213
Sections CRN CourseID TimeDays RoomNo Instructor 30101 CIST 2341 MW1-5pm F1145 1 30102 CIST 2371 MW6-10pm F1145 3 30103 CIST 1130 TTH6-9pm F1145 2 30104 CIST 1001 TTH6-9pm F1144 5 30105 CIST 1130 MW6-9pm F1144 2 30106 CIST 1130 MW8-11am F1144 2 30107 CIST 1510 TTH1-5pm F1149 6 30108 CIST 1510 MW1-5pm F1149 6 30109 CIST 2371 TTH1-5pm F1147 3 30110 CIST 2341 S8am-2pm F1147 3 30111 CIST 2371 TTH1-5pm F1147 1 30112 CIST 2372 MW1-5pm F1150 1 30113 CIST 1001 MW10am-12pm F1150 4 30114 CIST 1001 S8am-12pm F1150 4 30115 CIST 1305 MW10am-12pm F1148 5 30116 CIST 1220 MW1-5pm F1148 6Spri8Assign9(1) -Compatibility Mode-Saved to my Mac a Search in Document Home Insert Design Layout References Mailings Review View Share Paste Emphasis Heading1 Styles Using the database provided, Write and Execute SELECT statements to get the following information: (Joins are difficult, make sure to Read Chap 7 and check out some examples before you begin this exercise!!!!) SIMPLE JOINS Sections Table 1) What is the name of the Instructor teaching Section 30101? (List all Section information, plus the Instructor's first 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 Page 1 of 2 259 wordsEnglish (United States) E Focus- - + 119%
Explanation / Answer
--1
SELECT (i.FirstName+' '+iLastName) AS Name FROM
instructor i INNER JOIN section s/*Please modify table name if required**/
WHERE s.CRN=30101;
--2
SELECT s.*,c.CourseName FROM Section s
INNER JOIN COURSE c on s.courseid=c.courseid WHERE s.CRN=30108;/*Here there is no course table to join*/
/*course table is assumed. if not available please remove join*/
--3
SELECT c.CourseID,s.CRN,TimeDays,s.romNO,s.instructor
FROM Section s
INNER JOIN COURSE c on s.courseid=c.courseid
WHERE s.instructor=6
--4
--PLease comments with table list, will provide query. some tables are missing
--5
----PLease comments with table list, will provide query. some tables are missing
--6
SELECT s.FirstName,s.ID FROM student s INNER JOIN StudentSchedule ss
ON s.ID=s.StudentID WHERE s.CRN=30101;
--7
SELECT (s.FirstName+' '+s.LastNAme) AS Name,s.ID FROM student s INNER JOIN StudentSchedule ss
ON s.ID=s.StudentID WHERE s.CRN=30115;
--8
SELECT s.* FROM Section s INNER join StudentSchedule ss WHERE s.CRN=ss.CRN WHERE s.StudentID=6;
--9
SELECT * FROM Section WHERE CRN IN (SELECT CRN FROM StudentSchedule);
--10
SELECT c.CourseName,s.CRN,st.FirstName,st.LastNAme FROM section s INNER JOIN StudentSchedule ss ON s.CRN=ss.CRN
INNER JOIN student s ON s.studentid=s.ID
INNER JOIN course c s.courseid=c.courseid
/*course table is assumed. if not available please remove join*/
/*Please comment all table names to solve 4 and 5 questions as i could see creditHourse*/
/*if creditHourse is calculated column please comment so i can provide query*/
/*Thanks*/
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.