Basic Select Statement

<h1>Basic Select Statement </h1>
<p>The following query can be written in several ways. Write at least two versions of the query. </p>
<ol>
  <li>	<p>List in alphabetical order, the last and first names of all authors who live in NY, NJ or CT and whose last name starts with a letter from 'A' through 'M', Write this query in two different ways (I can think of a number of different ways).  </p></li>
</ol>
<p>The following queries are similar to the first one. You only have to write one version for each of the following. </p>
<ol start="2">
  <li><p>List in alphabetical order, the last and first names of all authors who live in NY, NJ or CT and whose last name starts with a letter from 'N' through 'Z'. </p>
  </li>
  <li><p>List in alphabetical order, the last and first names of all authors who live in any state other than NY, NJ and CT and whose last name starts with a letter from 'A' through 'M'. </p>
  </li>
  <li><p>List in alphabetical order, the last and first names of all authors who live in any state other than NY, NJ and CT and whose last name starts with a letter from 'N' through 'Z'. </p>
  </li>
</ol>

<hr />
<ol start="5">
  <li><p>List in alphabetical order, the last and first names of all authors whose first and last names start with the same letter. Hint: use the substring function.</p>
  </li>
  
  <li><p>List in alphabetical order, the last and first names of all authors whose first and last names are each at least 4 letters long. Hint you can approach this in one of two ways. Either (1) use the len function OR (2) use the LIKE keyword </p>
  </li>
  
  <li><p>Create one column which contains the first 8 letters (all in lowercase) of the concatenation of the first and last names of each author. For example, if an author's name is Joe Schwartzenberg the query should display "joeschwa". Hint: use the + concatenation operator together with the substring and lower functions.</p>
  </li>

  <li>Redo the previous query but only show people whose name gets truncated (i.e. &quot;chopped off&quot;). Hint: use the LEN function.</li>
</ol>
<h1>Group By</h1>  
<ol start="9">
  <li>List only the &quot;last name&quot; of the author that would appear first in alphabetical list of names of all authors. </li>
  <li>Do the same as the previous query but show the first &quot;last name&quot; for authors in NY, NJ and CT.</li>
  <li>List the number of books and the average number of pages for each type of book (e.g. 3 72 history, 5 150 biography, etc. )</li>
  <li>List the number of books for each type of book where there are more than 2 of that type.</li>
  <li>List the longest and shortest page length 
  for books of each type that were published in the year 2000 or later.</li>
</ol>