You will create one script file that contains the SQL syntax of your solution for each query problem. Save the script file as $-$Assignment$04.$sql$.$ Code comments at the top listing the assignment and your name. Also, code comments such as $--$Query $1$ before each query solution. Use MurachCollege database. Submit your script file using the Moodle Asssignment $4$ submission link.

Chapter $7$ Queries

First create the following tables by running a make table query:

Select $*$ into Departments From Departments

Select $*$ into Instructors From Instructors

$$

$1.$ Write an INSERT statement that adds this row to the Departments table:

DepartmentName:$$ History

Code the INSERT statement so SQL Server automatically generates the value for the DepartmentID column.

$2.$ Write a single INSERT statement that adds these rows to the Instructors table:

InstructorID:$$ The next automatically generated ID

LastName:$$ Benedict

FirstName:$$ Susan

Status:$$ P

DepartmentChairman:$0$

HireDate:$$ Today$$s date

AnnualSalary:$34000\mathrm{.}00$

DepartmentID:$9$

InstructorID:$$ The next automatically generated ID

LastName:$$ Adams

FirstName:$$ null

Status:$$ F

DepartmentChairman:$1$

HireDate:$$ Today$$s date

AnnualSalary:$66000\mathrm{.}00$

DepartmentID:$9$

Write this statement without using a column list.

$3.$ Write an UPDATE statement that modifies the first instructor you added in exercise $2.$ This statement should change the AnnualSalary column from $34,000$ to $35,000,$ and it should use the InstructorID column to identify the row.

$4.$ Write a DELETE statement that deletes the second instructor you added in exercise $2.$ This statement should use the InstructorID column to identify the row.

$5.$ Code a DELETE statement that deletes all instructors within the Instructors table that are in a department with an ID of $9.$ Code another DELETE statement that deletes the row in the Departments table that has an ID of $9.$

$6.$ Write an UPDATE statement that increases the annual salary for all instructors in the Education department by $5\%.$ To do that, join the Departments and Instructors tables and then filter the rows by the department name.

$$

$7.$ Write a DELETE statement that deletes instructors that aren$$t teaching any courses. To do that, use a subquery in the WHERE clause OR code an outer join statement.

$8.$ Run the following Create Table statement to create a table named GradStudents. This table has the same columns as the Students table, but the StudentID column isn$$t defined as an identity column.

CREATE TABLE GradStudents

$($StudentID INT PRIMARY KEY,

LastName VARCHAR$(25)$ NOT NULL,

FirstName VARCHAR$(25)$ NOT NULL,

EnrollmentDate DATE NOT NULL,

GraduationDate Date NULL$)$;

$9.$ Write an INSERT statement that inserts rows from the Students table into the GradStudents table. Include only the rows for students that have graduated, and don$$t use a column list.