-- 1a
USE library
SELECT
  lastname,
  firstname,
  ISNULL(a1.street + ' ' + a1.city + ' ' + a1.state, a2.street + ' ' + a2.city + ' ' + a2.state) AS addres,
  COUNT(loan.member_no)                                                                          AS total
FROM member
  LEFT OUTER JOIN loan ON member.member_no = loan.member_no
  LEFT OUTER JOIN adult a1 ON member.member_no = a1.member_no
  LEFT OUTER JOIN juvenile ON member.member_no = juvenile.member_no
  LEFT OUTER JOIN adult a2 ON juvenile.adult_member_no = a2.member_no
GROUP BY lastname, firstname, a1.street, a1.city, a1.state, a2.street, a2.city, a2.state, juvenile.member_no,
  member.member_no

USE library
SELECT
  lastname,
  firstname,
  (SELECT ISNULL(a1.street + ' ' + a1.city + ' ' + a1.state, a2.street + ' ' + a2.city + ' ' + a2.state)
   FROM member m1
     LEFT OUTER JOIN adult a1 ON member.member_no = a1.member_no
     LEFT OUTER JOIN juvenile ON member.member_no = juvenile.member_no
     LEFT OUTER JOIN adult a2 ON juvenile.adult_member_no = a2.member_no
   WHERE m1.member_no = member.member_no
  )                                         AS addres,
  (SELECT COUNT(loan.member_no)
   FROM loan
   WHERE member.member_no = loan.member_no) AS total
FROM member

-- 1b
USE library
SELECT
  lastname,
  firstname,
  ISNULL(a1.street + ' ' + a1.city + ' ' + a1.state, a2.street + ' ' + a2.city + ' ' + a2.state) AS addres,
  COUNT(loan.member_no)                                                                          AS total,
  CASE
  WHEN juvenile.member_no IS NULL
    THEN 'adult'
  ELSE 'kid'
  END                                                                                            AS 'kid/adult'

FROM member
  LEFT OUTER JOIN loan ON member.member_no = loan.member_no
  LEFT OUTER JOIN adult a1 ON member.member_no = a1.member_no
  LEFT OUTER JOIN juvenile ON member.member_no = juvenile.member_no
  LEFT OUTER JOIN adult a2 ON juvenile.adult_member_no = a2.member_no
GROUP BY lastname, firstname, a1.street, a1.city, a1.state, a2.street, a2.city, a2.state, juvenile.member_no,
  member.member_no

USE library
SELECT
  lastname,
  firstname,
  (SELECT ISNULL(a1.street + ' ' + a1.city + ' ' + a1.state, a2.street + ' ' + a2.city + ' ' + a2.state)
   FROM member m1
     LEFT OUTER JOIN adult a1 ON member.member_no = a1.member_no
     LEFT OUTER JOIN juvenile ON member.member_no = juvenile.member_no
     LEFT OUTER JOIN adult a2 ON juvenile.adult_member_no = a2.member_no
   WHERE m1.member_no = member.member_no
  )                                         AS addres,
  (SELECT COUNT(loan.member_no)
   FROM loan
   WHERE member.member_no = loan.member_no) AS total,
  (SELECT CASE
          WHEN juvenile.member_no IS NULL
            THEN 'adult'
          ELSE 'kid'
          END
   FROM member m2 LEFT OUTER JOIN juvenile ON m2.member_no = juvenile.member_no
   WHERE m2.member_no = member.member_no
  )                                         AS 'kid/adult'
FROM member