This database question should be asked of mid level SQL programmers to show the candidate's understanding of relationships between tables in a database and common, simple modeling techniques. SQL programmers with 1 to 2 years of experience should be familiar with these concepts.

Scenario:

You are modeling part of a database for a school. The particular problem you are addressing is the relationship of students to classes. A student can be in more than 1 class and a class will contain more than one student. Your solution should be in the 3rd normal form. The solution must be flexible enough to allow for any number of students taking any number of classes. Some of the classes at the college here get to be quite large.

Show the tables and relationships:

Answer:

You may also choose to ask the candidate how many tables it would take to show the relationships.

Answer:

Three tables are necessary to complete the question in the 3rd normal form and to provide the flexibility required by the question.

To further inquire about the candidate's skills, you could also ask for the SQL to produce a report of students and classes. Here is an example:

SELECT tbl_Student.Name, tbl_Class.ClassName
FROM tbl_Class
INNER JOIN (tbl_Student
INNER JOIN tbl_StudentClass ON tbl_Student.StudentID =        tbl_StudentClass.StudentID)
ON tbl_Class.ClassID = tbl_StudentClass.ClassID;

Another way of expressing this query is:

SELECT tbl_Student.Name, tbl_Class.ClassName
FROM tbl_Class, tbl_Student, tbl_StudentClass
Where
tbl_Student.studentid = tbl_StudentClass.studentid
and
tbl_Class.classid = tbl_StudentClass.classid;

This will show only students who have classes as well as classes with students. Empty classes and students who have not taken any classes will not be shown.

To see empty classes and students who have not taken any classes you could use a left join (also referred to as an "outer join"). These are just guidelines, you can get the same results with more than 1 query and subquerys will work as well. The important concept is that a left join is required to see all records.

An example of a subquery that would show students who has not taken classes:

SELECT tbl_Student.Name
FROM tbl_Student
WHERE tbl_Student.StudentID NOT IN
(SELECT tbl_StudentClass.StudentID FROM tbl_StudentClass);

Another example of the subquery:

SELECT tbl_Student.Name FROM tbl_Student
WHERE NOT EXISTS
(SELECT tbl_StudentClass.StudentID FROM tbl_StudentClass
WHERE tbl_StudentClass.StudentID = tbl_Student.StudentID);

About the author

Mark Horninger, A+, MCSE+I, MCSD, MCDBA is President and founder of Haverford Consultants Inc., located in the suburbs of Philadelphia, PA. He develops custom applications and system engineering solutions, specializing primarily in Microsoft operating systems and Microsoft BackOffice products. He has over 11 years of computer consulting experience and has passed 29 Microsoft Certification Exams. During his career Mark has worked on many extensive and diverse projects including database development, application development, training, embedded systems development and Windows NT and 2000 project rollout planning and implementations. Mark lives with his wife Debbie and two children in Havertown, Pa.