Write a single SQL script to perform the following. Show it to your teacher. Upload it on Moodle. 1.
Create a database named dbQuiz1_name in which “name” is your first name. [5 points]
2. 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]
3. 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).
4. 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
5. Insert the following data through spInsertDepartment. [20 points] 10, Production, Seattle 20, Sales, Boston 30, Research, Seattle 40, Accounting, Boston
6. 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
7. Show that the procedure spGetbyDlocation outputs the following. [25 points] John Sam Pretty Sweet