---

CREATE VIEW [Applicants (by specialty)] AS
SELECT 
    a.LastName + ' ' + SUBSTRING(a.FirstName, 1, 1) + '.' + SUBSTRING(a.MiddleName, 1, 1) + '.' AS FullName,
    s.Name AS Specialty
FROM Applicants a, Applications z, Specialties s
WHERE a.ApplicantID = z.ApplicantID
  AND z.SpecialtyID = s.IDspecialties;