| Server IP : 134.29.175.74 / Your IP : 216.73.216.160 Web Server : nginx/1.10.2 System : Windows NT CST-WEBSERVER 10.0 build 19045 (Windows 10) i586 User : Administrator ( 0) PHP Version : 7.1.0 Disable Function : NONE MySQL : OFF | cURL : ON | WGET : OFF | Perl : OFF | Python : OFF | Sudo : OFF | Pkexec : OFF Directory : C:/nginx/html/JimMartinson/CST1600/Labs/Lab06.5/ |
Upload File : |
<?
// JimMartinson/CST1600/Labs/Lab05/index.php
// Perform multi-table queries.
$TRACK = "<b>".basename(__FILE__).'</b> <span class="pv_fl">('.__FILE__.")</span>\n<ol>\n";
ini_set('include_path',ini_get('include_path').PATH_SEPARATOR.$_SERVER['DOCUMENT_ROOT']."/"); // Add the DOCUMENT_ROOT to the include_path.
require('application.phpinc');
require('Gradebook/EvaluationSubmission_BEGIN.phpinc'); // Replaced.
$disabled = '';
#$disabled = ' disabled'; // Comment this line to enable lab.
$workFilename = fileSafe($userFileName.'-ScheduleTables.txt');
?>
<fieldset><legend>Description</legend>
The purpose of this lab is to demonstrate the ability to perform multi-table queries and sub-queries.
<?=$gradeNote?>
<? if ( $disabled ) { ?><span class="error bold">Notice: Do not perform this assignment unless this notice has been removed.</span><? } ?>
</fieldset>
<fieldset><legend><?=$evaluationType?> <?=$evaluationNumber?> Assignment</legend>
<span class="note normal">Mac users may need to set the global sql_mode variable to allow simple GROUP BY clauses. Add this statement at the top of your .sql files:
<code class="copyText">SET @@sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));</code>
</span>
<ol class="count">
<li>
Create a directory called <b><?=$evaluationName?></b>. We will use it for all work performed in this lab.
</li>
<li>Write a query that determines the largest order placed by a customer.
<br><note>Hint: This query is best solved with a derived table as it requires the use of two GROUP BY clauses. The derived table returned all fields grouped by orderNumber. The outer query is grouped by customerNumber.</note>
<ol>
<li>Write a query that returns the <b class="copyText">customerNumber</b>, <b class="copyText">customerName</b>, <b class="copyText">orderDate</b>, and largest order total as <b class="copyText">MaxOrderTotal</b> for each customer, ordered by <b class="copyText">customerName</b>.</li>
<li>What the query should return is the largest order the customer placed (by order total cost, not # of items).</li>
<li>Save the query as <b class="copyText"><?=$saveFilename?>_MaxOrder.sql</b> in your <?=$evaluationName?> directory.</li>
<li>Run the query.</li>
<li>Adjust the MySQL Workbench window and panel sizes so the query and some of the results are visible.</li>
<li>Take a screenshot of the MySQL Workbench window and save it as <b class="copyText"><?=$saveFilename?>_MaxOrder.png</b> in your <b><?=$evaluationName?></b> folder.
<? showBegin('See MaxOrder query results screenshot'); ?><br>
<img src="images/MaxOrder.png" width="1000" alt="MaxOrder query results screenshot"/><br>
<? showEnd (); ?>
</li>
</ol>
</li>
<li>Turn in your assignment:
<ol type="a">
<li>Make sure the following files are in your <b><?=$evaluationName?></b> folder.
<ol>
<li><?=$saveFilename?>_MaxOrder.png</li>
<li><?=$saveFilename?>_MaxOrder.sql</li>
</ol>
</li>
<li>Zip up your <b><?=$evaluationName?></b> folder into a file named <b class="copyText"><?=$uploadFilename?></b>.
</li>
<li>Browse and select your <b><?=$uploadFilename?></b> file: <input type="file" name="fileUpload" id="inpFileUpload"<?=$disabled?>><div id="divFileploadError"></div></li>
<li>Then <input type="submit" name="f_subtask" id="inpSubmit" value="Submit"<?=$disabled?>> for grading.
</li>
</ol>
</li>
</ol><? if ( $disabled ) { ?><span class="error bold">Notice: Do not perform this assignment unless this notice has been removed.</span><? } ?>
</fieldset>
<?
require('Gradebook/EvaluationSubmission_END.phpinc');
require('common/sectionFooter.phpinc');
#require('../../ReferencesResources.phpinc');
#require($Instructor['Path'].'/ContactInformation.phpinc');
require('common/pageFooter.phpinc');
if ($TRACK != '') $_SESSION['TRACK'] .= "<li>End ".basename(__FILE__)."</ol>\n";
?>