Thu Jun 21
Introduction to SQL
9 – 12h Teaching
Teacher:
- Franck
Goals
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:
Useful online resources:
SQL tutorial and example
PDO Tutorial for MySQL Developers
12 – 13.30h Lunch
13.30 – 17h
- Sylvain
Introduction to SQL Exercise
Introduction to SQL Exercise
- create a PHP page 'routes.php' to list routes between 2 airports
- an HTML form to enter source and destination airport
- connection to the flightdb2 database
- A SELECT query on routes with a filter on src_ap and dst_ap
- an HTML table to dynamically display the result
- 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)
If you want to go further (not required for next course)
- Take the SQL Quizz: https://www.w3schools.com/quiztest/quiztest.asp?qtest=SQL
- You can also watch the 'Database for Developers' videos (by Oracle developer advocate Chris Saxon):
Database for Developers videos
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