Thu Jun 21

Introduction to SQL

9 – 12h Teaching

Teacher:

  • Franck

Goals

  • ­I know how to query data with SELECT statements and modify with INSERT, UPDATE, DELETE
  • ­I have joined tables and used ­set operations (UNION, MINUS, SELECT)
  • ­I learned about transactions and ACID properties


Useful online resources:

SQL tutorial and example

PDO Tutorial for MySQL Developers




Slides (course and exercises)

PowerCoders - Database - Day 3

open speakers-note (S) to copy/paste the code

12 – 13.30h Lunch

13.30 – 17h

  • Sylvain

Introduction to SQL Exercise

  • create a PHP page 'routes.php' to list routes between 2 airports
    1. an HTML form to enter source and destination airport
    2. connection to the flightdb2 database
    3. A SELECT query on routes with a filter on src_ap and dst_ap
    4. an HTML table to dynamically display the result
    5. try to improve it as much as you can: for example display the destination airport name by joining ROUTES with AIRPORTS, maybe add a second query to get the number of routes with a query like:
SELECT COUNT(*) from ROUTES WHERE ... GROUP BY ...

Solution

Here is the full code of C:\MAMP\htdocs\routes.php and look at the result at http://localhost/routes.php.

<html>
<head>
 <title>Testing MySQL from PHP</title>
</head>
<body>

<!--
 Some PHP code to test if src_arp and dst_arp are passed in the URL with the GET method
 If not, then set it to % which is the SQL wildcard
-->

<?php
  if ( ! isset ( $_GET["form_src_arp"] ) ) { $_GET["form_src_arp"]="%" ; }
  if ( ! isset ( $_GET["form_dst_arp"] ) ) { $_GET["form_dst_arp"]="%" ; }
 ?>

<!--
 An HTML form to enter source airport and destination airport with search pattern
 default to the value passed in the URL (or % to list all)
-->

<form action="routes.php" method="get">
  Source Airport code:      <input type="text" size="20" name="form_src_arp" value="<?php echo $_GET["form_src_arp"] ?>"> <br>
  Destination Airport code: <input type="text" size="20" name="form_dst_arp" value="<?php echo $_GET["form_dst_arp"] ?>"> <br/>
  <input type="submit" value="Search">
 </form> 

<!--
 Some PHP code connect to the database, run the query, and fetch the result into the $routes array
-->

<?php
  $connection=new PDO('mysql:host=localhost;dbname=flightdb2','root','root');
  $query=$connection->prepare('SELECT airline,src_ap,dst_ap,equipment FROM routes where src_ap like :pattern_src_arp and dst_ap like :pattern_dst_arp order by airline,src_ap,dst_ap');
  $query->execute( [ ':pattern_src_arp'=> $_GET["form_src_arp"] , ':pattern_dst_arp'=>$_GET["form_dst_arp"] ] );
  $routes=$query->fetchAll();
 ?>

<!--
 We start the HTML table and fetch at display the result row by row
-->

<p>
 Result:
 <table border=1>
 <tr><th>Airline</th><th>Source Airport</th><th>Destination Airport</th><th>Equipment</th></tr>

<?php
  foreach( $routes as $route ) {
   echo "<tr><td>" . $route['airline']. "</td><td>" . $route['src_ap']. "</td><td>" . $route['dst_ap']. "</td><td>" . $route['equipment']. "</td></tr>";
      }
 ?>

</body>
</html>


If you want to go further (not required for next course)


  • You can also watch the 'Database for Developers' videos (by Oracle developer advocate Chris Saxon):


Database for Developers videos

  • Tables, Columns and Data Types, Data Modeling
  • Select and Where, Joins, Aggregates and Group By
  • Insert and Commit, Update and Transactions
  • Delete and Truncate, Null, Subqueries
  • Order By & Top-N, Analytic Functions, Pivot & Unpivot