Create a database called University

Create the student table shown in the example, but only specify the StudentName column and dont specify any primary key). Dont add any data yet. Refresh the database so the new table appears. Verify that it only has one column (called Student Name).

Add a new column to the Student Table called StudentID without having to delete the table and start from scratch.

Add a primary key constraint to the Student table for the newly added StudentID column without having to delete the table and start from scratch.

Write the 4 insert statements needed to put the sample data shown in the example into the Student table.

Change the name of the StudentName column to LastName.

Add a column called FirstName to the Student table. Give it the same data type as the LastName column. Refresh the database so the table updates take place. Verify that your changes have been successfully made.

Write 4 record updating statements to give each student in the Student table a first name. Just make up a reasonable (and different) first name for each student. HINT: Dont forget to properly specify your WHERE clause!

Add a new column to the Student table called FullName. It should be at least twice the length of the LastName column.

Write ONE update statement that will set the value of the FullName colum to be a combination of the last name and the first name using this type of format: LastName, FirstName (i.e., Polk, Tom). Refresh the database so the table updates take place. Verify that your changes have been successfully made.

Create the Faculty table as shown in the example. Specify the primary key as part of your table creation. Dont worry about the data at this point.

Create the Course table as shown in the example. Specify the primary key as part of your table creation. Dont worry about the data at this point. Refresh the database so the table updates take place. Verify that your changes have been successfully made.

Create the Qualified table as shown in the example. Specify the compound primary key as part of your table creation and also specify the foreign keys. Specify the foreign leys such that, if a faculty member is deleted, any records in this table are also deleted. Dont worry about the data at this point.

Create the Section table as shown in the example. Specify the compound primary key as part of your table creation and also specify the foreign key. Dont worry about the data at this point.

Create the Registration table as shown in the example. Specify the compound primary key as part of your table creation and also specify the foreign keys. Dont worry about the data at this point. Refresh the database.

Insert data as shown in the example into all of the tables. You can do this either via INSERT statements or you can use the SQL Server GUI (just right click on a table name and select Edit Top 200 Rows).

Type in the following code and execute it:

SELECT Student.FullName, Course.CourseID, Course.CourseName, Section.SectionNo, Section.Semester

FROM Student JOIN Registration ON Student.StudentID = Registration.Student ID

JOIN Section ON Registration.SectionNo = Section.SectionNo

JOIN Course on Section.CourseID = Course.CourseID

What do you see?