Student Student_ID Student_Name 1022 John Doe 4123 Jill Maher 3456 Matt Samuels
ID: 3598225 • Letter: S
Question
Student
Student_ID
Student_Name
1022
John Doe
4123
Jill Maher
3456
Matt Samuels
3751
Megan Cramer
Advisor_ID
Advisor_Name
Adv_Room
1
Jones
412
2
Smith
216
3
Taylor
322
Course
Course_ID
Course_Name
101-07
Analytics
107-08
Economics
201-01
Statistics
143-01
Biology
211-02
Java
135-03
Sociology
159-02
Physics
214-01
History
147-11
Chemistry
Student_Advisor
Stu_Adv_ID
Student_ID
Advisor_ID
1
1022
1
2
4123
2
3
3456
1
4
3751
3
Student_Course
Stu_Cr_ID
Student_ID
Course_ID
1
1022
101-07
2
1022
143-01
3
1022
159-02
4
4123
201-01
5
4123
211-02
6
4123
214-01
7
3456
101-07
8
3456
143-01
9
3456
159-02
10
3751
107-08
11
3751
135-03
12
3751
147-11
The above tables show the solution for your normalization HW. The data types in the tables are as follows:
Student: (Student_ID Integer PRIMARY KEY, Student_Name Char(50))
Advisor: (Advisor_ID Integer PRIMARY KEY, Advisor_Name Char(50), Adv_Room Integer)
Course: (Course_ID Char(10) PRIMARY KEY, Course_Name Char(50))
Student_Advisor (Stu_Adv_ID Integer PRIMARY KEY, Student_ID Integer, Advisor_ID Integer)
Student_Course (Stu_Cr_ID Integer PRIMARY KEY, Student_ID Integer, Course_ID Char(10))
Write the SQL statements for the following and show the output of the SQL statements where applicable
(No output necessary for Questions 1, 11 and 12)
Create a new student entry for Sam Mills with Student_ID 1077.
Output the names of all students taking Biology.
Output the unique Advisor_Ids from the Student_Advisor table.
Output the names of all students whose name starts with J.
Output all Student_Ids whose Advisor_ID is not 3.
Output the names of all students whose ID is more than 3000.
Show the number of courses in the Course table.
Show the names of all courses in the Course table sorted by the Course name.
Output the names of all students whose ID is in between 3700 and 4000.
Use a sub-query to show the names of all students whose advisor is Jones.
Change the name of “Matt Samuels” to “Kevin Samuels”.
Delete the entry created in Question 1 (Sam Mills) from the Student table.
Student_ID
Student_Name
1022
John Doe
4123
Jill Maher
3456
Matt Samuels
3751
Megan Cramer
AdvisorAdvisor_ID
Advisor_Name
Adv_Room
1
Jones
412
2
Smith
216
3
Taylor
322
Explanation / Answer
1-INSERT INTO Student(Student_ID,Student_Name)
Values (1077,"Sam Mills");
2-SELECT Student_Name FROM STUDENT WHERE Student_ID IN (SELECT Student_ID FROM Student_Course WHERE Course_ID=(SELECT Course_ID FROM Course WHERE Course_Name="Biology") );
3-SELECT Distinct Advisor_ID from Student_Advisor;
4-SELECT Student_Name fROM Student WHERE Student_Name like "J%"l
5-SELECT Student_ID from Student_Advisor WHERE Advisor_ID <> 3;
6-SELECT Student_Name FROM Student WHERE Student_ID>3000;
7-SELECT Count(Course_ID) as TotalCourses from Course;
8-SELECT Course_Name From Course Order by Course_Name;
9-SELECT Student_Name FROM Student WHERE Student_ID BETWEEN 3700 AND 4000;
10-SELECT Student_Name FROM Student WHERE ID IN(SELECT Student_ID FROM Student_Advisor WHERE Advisor_ID IN (SELECT Advisor_ID FROM Advisor WHERE Advisor_Name="Jones"));
11-UPDATE Student SET Student_Name="Matt Samuels" WHERE Student_Name="Kevin Samuels";
12-DELETE FROM Student WHERE Student_Name="Sam Mills";