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.00
DepartmentID: 9
InstructorID: The next automatically generated ID
LastName: Adams
FirstName: null
Status: F
DepartmentChairman: 1
HireDate: Today’s date
AnnualSalary: 66000.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,0000, 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. Write a DELETE statement that deletes the row in the Departments table that has an ID of 9. When you execute this statement, it will produce an error since the department has related rows in the Instructors table. To fix that, precede the DELETE statement with another DELETE statement that deletes all instructors in this department.
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.
8. Open the script named CreateGradStudents.sql that’s in the MC Exercise Starts directory. Run this file 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.
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.
10. Open the script named CreateMurachCollege.sql that’s in the MC Exercise Starts directory. Then, run this script. That should restore the data that’s in the database. If an error message is displayed indicating that the database is in use, you’ll need to close and restart the Management Studio and then run the script again