Question #1 Create a sequence that will be used to generate unique numbers for t

Question

Question #1 Create a sequence that will be used to generate unique numbers for the StudentId in the Student table. The sequence should start at 100 and increment by 10 each time it is used. Submit the SQL statement below.



Question #2 Create the tables described in Appendix A of this document. Be sure to include all constraints described. Use your own judgement regarding the appropriate data types. Show the SQL statements below.



Question #3Write a SQL query that Populate the new Student table with three records and the Enrollment table with three records. Use the sequence created in Problem #1 to populate the StudentId column in the student table. Make the data permanent after you have populated the tables. Submit the statements that were used to populate the tables as well as the statement used to make the data permanent.



Question #4 Write a SQL query that will create a complex view that will show each course name and the number of students enrolled in the course. All courses should appear even if no students are enrolled for that course. Show the SQL statement below.



Question #5 Write a SQL query that creates an index on the Name column of the new Student table. Show the SQL statement below.



Question #6 Write a SQL query that will list the names of all the tables in your schema. Show the SQL statement below.



Question #7 Write a SQL query that will remove the index you created in Question #5 from the Student table. Show the SQL statement below.



Question #8 Write a SQL query that removes all the records from the Enrollment table but allows the records to be recovered if necessary. Show the SQL statement below.




Question #9 Write a SQL query that recovers the records removed in question #8. Show the SQL statement below.



Question #10 Write a SQL query that adds a new column to the Enrollment table called DateEnrolled (date datatype). Show the SQL statement below.



Appendix A - New Tables

You are to create three new tables in your schema (Question #2). Here are the table and column names along with the business rules that need to be enforced (using constraints).

Student (columns StudentID, SSN, GPA)
StudentID is the primary key
Name is required
SSN is not required, but must be unique
GPA is required and must be greater or equal to zero

Course (columns CourseID, Title)
CourseID is the primary key
Title is required

Enrollment (columns CourseID, StudentID, Room)
Columns CourseID and StudentID make up the primary key
Room is required
CourseId must exist on the Course Table
StudentId must exist on the Student Table

Explanation / Answer

Dear, Please post remaining questions on another post 5: CREATE INDEX student_index ON Student (Name) 6: SELECT * FROM tab 7: DROP INDEX Student.student_index 10: ALTER TABLE Enrollment ADD DateEnrolled date

Related Questions

Navigate

• Browse (All)
• Browse Q
• Subjects

---