Paper
Christine Wall
IS361
Week 6 homework
5.
Write SQL commands for the following:
a. Create two different forms of the INSERT command to add a student with a student ID of 65798 and last name Lopez to the Student table. (pg 257)
INSERT INTO Student (StudentID, StudentName) VALUES
(65798,’Lopez’);
or
INSERT INTO Student VALUES (65798,’Lopez’);
b. Now write a command that will remove Lopez from the Student table. (pg 259)
DELETE FROM Student
WHERE StudentID = 65798;
c. Create an SQL command that will modify the name of course ISM 4212 from Database to Introduction to Relational Databases. (pg259)
UPDATE Course
SET CourseName = ‘Introduction to Relational Databases’
WHERE CourseID = ‘ISM 4212’;
6.
Write SQL quarries to answer the following questions:
a. Which students have an ID number that is less than 50000? (pg264)
SELECT Student.ID, Student.Name
FROM Student
WHERE (((Student.ID)<50000));
b. What is the name of the faculty member who’s ID is 4756? (pg264)
SELECT Faculty.ID, Faculty.Name
FROM Faculty
WHERE (((Faculty.ID)=4756));
c. What is the smallest section number used in the first semester of 2008? (pg264)
SELECT Section.[No], Section.Semester
FROM [Section]
WHERE (((Section.[No])<=2712));
7.
Write a SQL quarries to answer the following questions:(pg 264)
a. How many students are enrolled in section 2714 in the first semester of 2008?
SELECT Registration.StudentID, Registration.SectionNo
FROM Registration
WHERE (((Registration.SectionNo)=2714));
b. Which faculty members have qualified to teach a course since 1993? List the faculty ID, course, and date of qualification. (pg 271)
SELECT Qualified.[Faculty ID], Qualified.CourseID, Qualified.DateQualified
FROM Qualified
WHERE (((Qualified.DateQualified)>="9/1993"));
or
SELECT Faculty.Name, Qualified.[Faculty ID], Qualified.CourseID, Qualified.DateQualified
FROM Faculty, Qualified
References
Hoffer, J Ramesh, V Topi, H.
IS361
Week 6 homework
5.
Write SQL commands for the following:
a. Create two different forms of the INSERT command to add a student with a student ID of 65798 and last name Lopez to the Student table. (pg 257)
INSERT INTO Student (StudentID, StudentName) VALUES
(65798,’Lopez’);
or
INSERT INTO Student VALUES (65798,’Lopez’);
b. Now write a command that will remove Lopez from the Student table. (pg 259)
DELETE FROM Student
WHERE StudentID = 65798;
c. Create an SQL command that will modify the name of course ISM 4212 from Database to Introduction to Relational Databases. (pg259)
UPDATE Course
SET CourseName = ‘Introduction to Relational Databases’
WHERE CourseID = ‘ISM 4212’;
6.
Write SQL quarries to answer the following questions:
a. Which students have an ID number that is less than 50000? (pg264)
SELECT Student.ID, Student.Name
FROM Student
WHERE (((Student.ID)<50000));
b. What is the name of the faculty member who’s ID is 4756? (pg264)
SELECT Faculty.ID, Faculty.Name
FROM Faculty
WHERE (((Faculty.ID)=4756));
c. What is the smallest section number used in the first semester of 2008? (pg264)
SELECT Section.[No], Section.Semester
FROM [Section]
WHERE (((Section.[No])<=2712));
7.
Write a SQL quarries to answer the following questions:(pg 264)
a. How many students are enrolled in section 2714 in the first semester of 2008?
SELECT Registration.StudentID, Registration.SectionNo
FROM Registration
WHERE (((Registration.SectionNo)=2714));
b. Which faculty members have qualified to teach a course since 1993? List the faculty ID, course, and date of qualification. (pg 271)
SELECT Qualified.[Faculty ID], Qualified.CourseID, Qualified.DateQualified
FROM Qualified
WHERE (((Qualified.DateQualified)>="9/1993"));
or
SELECT Faculty.Name, Qualified.[Faculty ID], Qualified.CourseID, Qualified.DateQualified
FROM Faculty, Qualified
References
Hoffer, J Ramesh, V Topi, H.
References: Hoffer, J Ramesh, V Topi, H. (2010) Modern Database Management, 10th ed. Pearson Education Inc. pp. Chapter 6, pages include 257, 259, 264, 271.