<div id="docs-body">
 <div class="section">
  <div class="titlepage">
   <div>
    <div>
     <h2 class="title">
      <a name="examples">
      </a>
      5.6 Examples of Common Queries
     </h2>
    </div>
   </div>
  </div>
  <div class="toc">
   <dl class="toc">
    <dt>
     <span class="section">
      <a href="example-maximum-column.html">
       5.6.1 The Maximum Value for a Column
      </a>
     </span>
    </dt>
    <dt>
     <span class="section">
      <a href="example-maximum-row.html">
       5.6.2 The Row Holding the Maximum of a Certain Column
      </a>
     </span>
    </dt>
    <dt>
     <span class="section">
      <a href="example-maximum-column-group.html">
       5.6.3 Maximum of Column per Group
      </a>
     </span>
    </dt>
    <dt>
     <span class="section">
      <a href="example-maximum-column-group-row.html">
       5.6.4 The Rows Holding the Group-wise Maximum of a Certain Column
      </a>
     </span>
    </dt>
    <dt>
     <span class="section">
      <a href="example-user-variables.html">
       5.6.5 Using User-Defined Variables
      </a>
     </span>
    </dt>
    <dt>
     <span class="section">
      <a href="example-foreign-keys.html">
       5.6.6 Using Foreign Keys
      </a>
     </span>
    </dt>
    <dt>
     <span class="section">
      <a href="searching-on-two-keys.html">
       5.6.7 Searching on Two Keys
      </a>
     </span>
    </dt>
    <dt>
     <span class="section">
      <a href="calculating-days.html">
       5.6.8 Calculating Visits Per Day
      </a>
     </span>
    </dt>
    <dt>
     <span class="section">
      <a href="example-auto-increment.html">
       5.6.9 Using AUTO_INCREMENT
      </a>
     </span>
    </dt>
   </dl>
  </div>
  <a class="indexterm" name="idm46045324738832">
  </a>
  <a class="indexterm" name="idm46045324737376">
  </a>
  <p>
   Here are examples of how to solve some common problems with MySQL.
  </p>
  <p>
   Some of the examples use the table
   <code class="literal">
    shop
   </code>
   to hold
      the price of each article (item number) for certain traders
      (dealers). Supposing that each trader has a single fixed price per
      article, then (
   <code class="literal">
    article
   </code>
   ,
   <code class="literal">
    dealer
   </code>
   ) is a primary key for the records.
  </p>
  <p>
   Start the command-line tool
   <a class="link" href="mysql.html" title="6.5.1 mysql — The MySQL Command-Line Client">
    <span class="command">
     <strong>
      mysql
     </strong>
    </span>
   </a>
   and select a
      database:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting one-line language-terminal"><div class="docs-select-all right" id="sa33660561"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-terminal"><span class="token prompt">$&gt; </span><span class="token command">mysql</span> <em class="replaceable">your-database-name</em></code></pre>
  </div>
  <p>
   To create and populate the example table, use these statements:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa82947042"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token keyword">CREATE</span> <span class="token keyword">TABLE</span> shop <span class="token punctuation">(</span>
    article <span class="token datatype">INT</span> <span class="token keyword">UNSIGNED</span>  <span class="token keyword">DEFAULT</span> <span class="token string">'0000'</span> <span class="token operator">NOT</span> <span class="token boolean">NULL</span><span class="token punctuation">,</span>
    dealer  <span class="token datatype">CHAR</span><span class="token punctuation">(</span><span class="token number">20</span><span class="token punctuation">)</span>      <span class="token keyword">DEFAULT</span> <span class="token string">''</span>     <span class="token operator">NOT</span> <span class="token boolean">NULL</span><span class="token punctuation">,</span>
    price   <span class="token datatype">DECIMAL</span><span class="token punctuation">(</span><span class="token number">16</span><span class="token punctuation">,</span><span class="token number">2</span><span class="token punctuation">)</span> <span class="token keyword">DEFAULT</span> <span class="token string">'0.00'</span> <span class="token operator">NOT</span> <span class="token boolean">NULL</span><span class="token punctuation">,</span>
    <span class="token keyword">PRIMARY</span> <span class="token keyword">KEY</span><span class="token punctuation">(</span>article<span class="token punctuation">,</span> dealer<span class="token punctuation">)</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token keyword">INSERT</span> <span class="token keyword">INTO</span> shop <span class="token keyword">VALUES</span>
    <span class="token punctuation">(</span><span class="token number">1</span><span class="token punctuation">,</span><span class="token string">'A'</span><span class="token punctuation">,</span><span class="token number">3.45</span><span class="token punctuation">)</span><span class="token punctuation">,</span><span class="token punctuation">(</span><span class="token number">1</span><span class="token punctuation">,</span><span class="token string">'B'</span><span class="token punctuation">,</span><span class="token number">3.99</span><span class="token punctuation">)</span><span class="token punctuation">,</span><span class="token punctuation">(</span><span class="token number">2</span><span class="token punctuation">,</span><span class="token string">'A'</span><span class="token punctuation">,</span><span class="token number">10.99</span><span class="token punctuation">)</span><span class="token punctuation">,</span><span class="token punctuation">(</span><span class="token number">3</span><span class="token punctuation">,</span><span class="token string">'B'</span><span class="token punctuation">,</span><span class="token number">1.45</span><span class="token punctuation">)</span><span class="token punctuation">,</span>
    <span class="token punctuation">(</span><span class="token number">3</span><span class="token punctuation">,</span><span class="token string">'C'</span><span class="token punctuation">,</span><span class="token number">1.69</span><span class="token punctuation">)</span><span class="token punctuation">,</span><span class="token punctuation">(</span><span class="token number">3</span><span class="token punctuation">,</span><span class="token string">'D'</span><span class="token punctuation">,</span><span class="token number">1.25</span><span class="token punctuation">)</span><span class="token punctuation">,</span><span class="token punctuation">(</span><span class="token number">4</span><span class="token punctuation">,</span><span class="token string">'D'</span><span class="token punctuation">,</span><span class="token number">19.95</span><span class="token punctuation">)</span><span class="token punctuation">;</span></code></pre>
  </div>
  <p>
   After issuing the statements, the table should have the following
      contents:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa28971885"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token keyword">SELECT</span> <span class="token operator">*</span> <span class="token keyword">FROM</span> shop <span class="token keyword">ORDER</span> <span class="token keyword">BY</span> article<span class="token punctuation">;</span>

<span class="token output"><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span></span>
<span class="token output"><span class="token punctuation">|</span> article <span class="token punctuation">|</span> dealer <span class="token punctuation">|</span> price <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span></span>
<span class="token output"><span class="token punctuation">|</span>       1 <span class="token punctuation">|</span> A      <span class="token punctuation">|</span>  3.45 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span>       1 <span class="token punctuation">|</span> B      <span class="token punctuation">|</span>  3.99 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span>       2 <span class="token punctuation">|</span> A      <span class="token punctuation">|</span> 10.99 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span>       3 <span class="token punctuation">|</span> B      <span class="token punctuation">|</span>  1.45 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span>       3 <span class="token punctuation">|</span> C      <span class="token punctuation">|</span>  1.69 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span>       3 <span class="token punctuation">|</span> D      <span class="token punctuation">|</span>  1.25 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span>       4 <span class="token punctuation">|</span> D      <span class="token punctuation">|</span> 19.95 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span></span></code></pre>
  </div>
 </div>
 <br/>
</div>
