SQL ASSESSMENT

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 orresponding
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"

TSQL – Find Object Dependencies

Yeah!!.. it is getting pretty much interesting here.. the following query we can use to find the Tables used in the stored procedures (‘Object on which the Stored Procedure depends’).  It is also can be viewed by right click the object and click the ‘View Dependency’ , it will give results.. but the TSQL as follows:
SELECT b.name,a.referencing_id, a.referenced_database_name, a.referenced_entity_name
FROM sys.sql_expression_dependencies a
JOIN sys.procedures b on a.referencing_id = b.object_id

But…regardless of whether it is stored procedure or View,  if we want to find the dependency of all the object in the database use the following script.

SELECT b.name,referencing_id,referenced_database_name, referenced_entity_name
FROM sys.sql_expression_dependencies a
JOIN sys.objects b on a.referencing_id = b.object_id

…Hope It Saves time.  :)

TSQL – Drop/ADD FK constraints in database

There will be some scenario, where we might need to drop the FK constraints and recreate them again while doing truncate in the tables where referential integrity is created between tables. Here is the script that create the the Constraints scripts from the existing relationship between the tables.
Following Query will create the “Alter table ADD Foreign Key constraint” script.
USE [AdventureWorks]
SELECT 'Alter Table  ['+ d.name+'].['+OBJECT_NAME(a.parent_object_id) + ']   With Check Add Constraint ['+ a.name +']  Foreign Key (['+ COL_NAME(b.parent_object_id,b.parent_column_id)+']) References ['+d.name+'].['+OBJECT_NAME (a.referenced_object_id)+'] (['+COL_NAME(b.referenced_object_id,b.referenced_column_id) +'])'
FROM sys.foreign_keys AS a
JOIN sys.foreign_key_columns AS b ON a.OBJECT_ID = b.constraint_object_id
JOIN sys.objects  c on a.name = c.name and c.type = 'F'
JOIN sys.schemas d ON d.schema_id = c.schema_id
Following query will create the “Alter table DROP Foreign Key constraints”  script.
USE [AdventureWorks]
SELECT 'Alter Table  ['+c.name+'].[' + b.name   +']  Drop Constraint [' + a.name +']'
FROM  sys.objects a
JOIN     sys.objects b on a.parent_object_id = b.object_id
JOIN     sys.schemas c on a.schema_id = c.schema_id
WHERE a.parent_object_id IN  (SELECT object_id FROM sys.objects ) AND a.type in ('F ')  
Note: I want you to take  “Alter table ADD Foreign Key constraint” script backup before running the “Alter table DROP Foreign Key constraints”  scripts on the table. Also you can play with the script as you needed. Hope it helps and saves time.

IS– Errors

ERROR: no corresponding output column on the error output
If there is any error on the task that raises an error like "no corresponding output column on the error output"”. there one of the option is Then I want you to drop that particular task and recreate again.