Question 1)

Create schema named LabAssignment3 in your database

Create a table named Applicant in the schema you created which can store information about MBA applicants to a university. Create all the columns as not null columns

ApplicantID ( integer column) (primary key)

ApplicantName ( varchar column)

Age ( integer column)

WorkExpMonths ( integer column)

GMATScore ( integer column)

HighestDegree ( varchar column)

Enter 5 records into the table

Question 2)

Write an alter statement to add a column named Email to the above table

Question 3)

Write an alter statement to change the data type of the WorkExpMonths into decimal from integer data type

Question 4)

Write an alter statement to change HighestDegree into a null column

Question 5)

Write an alter statement to remove the column Email from the table

Question 6)

Write an alter statement to add a coumn named Gender. It must allow only two values Male and Female into the column. Include the constraint to allow only the two value while adding the column with the alter statement.

Question 7)

Write alter statement to modify the data type of the primary key colum to char data type. Provide any other alter statement you executed to achieve this. Your resulting table must have applicantid as the primary key.

Question 8)

Write an alter statement to remove the check constraint on the gender column

Question 9)

Write an alter statement to add a default constraint to the Gmatscore. The default vale you need to use is 000

Question 10)

Create a table named Dept in the LabAssignment3 schema which can store the departmentid(primary key) and departname of various departments in the college of business in the university.

Write alter statements to Include a foreign key in the Applicants table such that the foreign key references the departmentid in dept table you just created.

Provide all the SQL queries used in this process.

Question 11)

Write create statement to create a clustered index on the column ApplicantName . The clustered index must be sorted in descending order. Provide all other sql queries executed to achieve this. Your resulting table must have Applicantid as the primany key.

Question 12)

Create a non-clustered index on ApplicantAge

Question 13)

Create a non-clustered index on the two columns ApplicantAge and ApplicantName

Question 14)

Create non clustered filtered index on the column GmatScore with GMAT score >500