IT-112 Functions

Create a single SQL script that will do the follow. Comment each step.

Problem 1:

Using the Script for provided with this assignment do the following.

1.Create a function called fn_GetUserName which will return the user name from the TUsers table.

2.Create a function called fn_GetSongs which will return the song title and artist from the TSongs table.

3.Write a Select statement, using the functions, that will pull the user ID, the user name, the song ID and the song and artist from TUserFavoriteSongs table.

4.Create a view from this select statement

5.Write a Select statement to pull all records from the view (you may use * here)

Problem 2:

Using the Script for provided with this assignment do the following.

1.Create a function called fn_GetCourse which will return the course name and description from the TCourses table.

2.Create a function called fn_GetStudents which will return the last name, first name from the TStudents table. Name should be in format of LastName, FirstName.

3.Create a function called fn_GetBook which will return the book name and author from the TBooks table.

4.Create a function called fn_GetInstructor which will return the last name, first name from the TInstructors table. Name should be in format of LastName, FirstName.

5.Write a Select statement, using the functions, that will pull the student ID, the student name, the course ID, name and description from TCourseStudents table.

6.Write a Select statement, using the functions, that will pull the book ID, name, author, the course ID, name and description from TCourseBooks table.

7.Write a Select statement, using the functions, that will pull the instructor ID, name, the course ID, name and description from TCourses table.

8.Create a view from this each of select statements in steps 5, 6 and 7

9.Write a Select statement to pull all records from the views (you may use * here)

Make sure your script runs without any errors from top to bottom.