PLEASE DO ALL IN ONE SQL SCRIPT

1. Create two tables as follows.

tblEmployee (EmployeeID, Fname, Lname, DeptID)

tblDepartment (DeptID, Dname, Dlocation)

Note that EmployeeID and DeptID are primary keys, and the tables are connected through DeptID. [20 points]

2. Create three stored procedures as follows.

spInsertEmployee procedure will insert all fields in tblEmployee by passing the four fields as input parameters.

spInsertDepartment procedure will insert all fields in tblDepartment by passing the three fields as input parameters.

spGetbyDlocation procedure will show the Fname, by passing only the value of Seattle, for Dlocation as input parameter (that is, it will show the first names of all employees located in Seattle).

3. Insert the following data through spInsertEmployee. [20 points]

S10000, John, Smith, 10

D10000, Jerry, Smith, 20

F100, Sam, Diaz, 30

G200, Pete, Johnson, 40

F300, Pretty, Girl, 10

A444, Sweet, Friend, 10

G5555, Nice, Day, 40

4. Insert the following data through spInsertDepartment. [20 points]

10, Production, Seattle

20, Sales, Boston

30, Research, Seattle

40, Accounting, Boston

5. Show that the following data insertion through spInsertEmployee is invalid (because there is no DeptID with value of 50 in tblDepartment). [10 points]

A10111, Serena, Williams, 50

6. Show that the procedure spGetbyDlocation outputs the following. [25 points]

John Sam Pretty Sweet

When you show your result to your teacher, please provide your self-assessment score