SQL Assesment

Here is a SQL Server Exercise:

At the beginning of this school year, the school A has
+ 30 teachers
+ 45 classes
+ 500 students
1. A teacher can be assigned to teach multiple classes. However, a teacher may not be assigned to teach any class. Teacher Information is stored in table "TEACHER"
2. A class can only be taught by one teacher. However, a class may not have any teacher assigned to it yet. A class can have multiple students. But a class may not have any student registered to attend yet. Class information is stored in table "CLASS"
3. A student can attend multiple classes. However, a student may not yet attend any class. Student information is stored in table "STUDENT"
4. Information of student registration is stored in table "CLASS_STUDENT"
Below are the examples of school records stored in the above 4 relational tables.
TEACHER:

 
CLASS:

STUDENT:

CLASS_STUDENT:

 
1) Write a SQL script that creates a table called "STUDENT_HOBBIES". This table should contain
a. Hobby ID of type integer. This field should be an IDENTITY column that starts from 100 and increments by 1.
b. Student ID of type integer
c. Hobby of type character. This field could contain up to 50 characters
2) Write a SQL statement that displays the name of all classes in the school A, together with the name of the corresponding teacher that is assigned to teach the class. If no teacher is assigned to a class yet, display "N/A" as teacher name. Below are the sample results

3) Write a SQL statement that displays the class_id, class_name, total students attending that class, and a message saying "Under Size" if there are less than 2 students in the class; otherwise, the message should be "Normal Size". Below are the sample results

4) Write a SQL script that displays the list of all the teachers that have not been assigned to any class.
5) Write a SQL script that checks if table "STUDENT" does not have a column called "gender", adds column "gender" of type integer to the table.
6) Documentation of the database showed that no one had manually created any index on table "TEACHER", but when the DBA attempted to create one using the following SQL statement He got the following error:
Why can’t table "TEACHER" have more than one CLUSTERED INDEX?
7) Consider the following table that is unique on prospect_id, but may have duplicates household_ids. Write SQL code to create another table that is unique by household_id and retain all columns that the original table have. For households from the original table that have more than one prospect_id, retain the record with the lowest model tile.

 
8) There's a flat text file that contains the information of book title, author, and published date. Below is the file format

Below is the sample of the flat text file.

 
a) How would you load this file into a table called "BOOK" using a method other than DTS/SSIS package.
b) There are duplicates on book title , author name . Either write a script or briefly describe the method you would use to remove those duplicates from the table "BOOK" (only keep the record that has the latest published_date )
c) Over time, table "BOOK" has grown to have 120 columns. Write a SQL script that displays the comma separated list of all the columns in table "BOOK"