Interview Questions

IIS:

• • Create a new website called 'mytest.localhost.net'

  • Create a hosts entry to route mytest.localhost.net to this website

SQL:

QBO SQL structure background:

• • Person: each row is a user (e.g. jdoe@mycompany.com, madams@acme.com, etc.)

  • SystemRole: each row represents a Role (e.g. Manager, Administrator, etc.)

  • SystemMember: each row maps a Person to a SystemRole

  • Organization: each row is a company

  • PersonAccess: each row grants a user access to some 'group object', such as Organization

Sample queries:

• • Show users created after 7/1/2014: SELECT * FROM Person WHERE CreatedDate > '7/1/2014'

  • Show all Administrators user names (Person.Person): 

SELECT Person.Person

FROM SystemRole

  INNER JOIN SystemMember ON SystemMember.SystemRoleID = SystemRole.SystemRoleID

  INNER JOIN Person ON Person.PersonID = SystemMember.PersonID

WHERE SystemRole = 'Administrator'

• • Show users that have access to the 'Bank Of US' Organization

SELECT Person.Person

FROM Organization

  INNER JOIN PersonAccess ON PersonAccess.GroupObject = 'Organization' AND PersonAccess.GroupObjectID = Organization.OrganizationID

  INNER JOIN Person ON Person.PersonID = PersonAccess.PersonID

WHERE Organization = 'Bank of US'

• • Show roles with no users

SELECT * 

FROM SystemRole

WHERE NOT EXISTS (

  SELECT * FROM SystemMember WHERE SystemMember.SystemRoleID = SystemRole.SystemRoleID

)

Questions:

• 1. Show users that are members of the 'Security Admin' role

  2. Show users that are members of the 'Security Admin' role and have access to the 'Bank of US' organization

  3. Show Organizations that have no users with access to the Organization

  4. Show Organizations that 'need' a 'Security Admin' user (need means they do not currently have a user fulfilling the role)