1. Create faculty and dependants tables.

CREATE TABLE Faculty (

FacultyID int IDENTITY(1000,1) PRIMARY KEY,

FacFirstname VARCHAR (30) NOT NULL,

FacLastname VARCHAR (30) NOT NULL,

FacAddress VARCHAR (100),

FacCity VARCHAR (30),

FacState CHAR (2),

FacZIP CHAR (12),

FacSex VARCHAR (6),

FacSalary MONEY, /* This allows for US currency to be entered */

FacHireDate DATE NOT NULL DEFAULT GETDATE(),

);

CREATE TABLE Dependants(

DependantID int IDENTITY(1000,1) PRIMARY KEY,

DepFirstname VARCHAR (30) NOT NULL,

DepLastname VARCHAR (30) NOT NULL,

FacultyID int NOT NULL,

CONSTRAINT FK_FacultyID FOREIGN KEY (FacultyID)

REFERENCES Faculty(FacultyID) ON DELETE CASCADE

)

2. Add the data (at least 3 records) into Faculty table using the insert SQL command.

3. Add the data (at least 5 records) into Dependants table using the insert SQL command.

4. Get Faculty names (fistname and last name) and FacultyIds of all faculty members, arranged in alphabetical order by name.

5. Create a view table that retrieve all records of faulty table that the salary is between 50000 and 90000.

6. Find the sum of the salaries of all faculties, as well as the maximum salary, the minimum salary, and the average salary.

7. Change the salary of FacultyID 1004 to 89500.

8. For each faculty, retrieve the faculty's ID, first and last name and the first name and ID of his or her dependants.