| 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/_Archive/Lab05/ |
Upload File : |
<?
// JimMartinson/CST1600/Labs/Lab05/index.php
$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.
include('application.phpinc');
include('Course/courseInfo.phpinc');
include('../../classInfo.phpinc');
$evaluationNumber = 5;
$evaluationType = "Lab";
$evaluationTitle = "Stored Procedure";
if ( !isset($assignmentSuffix) ) $assignmentSuffix = '';
$evaluationName = $evaluationType.twoDigit($evaluationNumber).$assignmentSuffix;
$title = $evaluationType." ".$evaluationNumber.' - '.$evaluationTitle;
$headTitle = '_CST_ - '.$courseNumber.' '.$courseTitle.' - '.$title;
$pageTitle = $courseNumber.' '.$courseTitle.'<br>'.$title;
$pageMenu = 'Course/courseMenu.phpinc';
$smallTitle = true;
include('common/pageHeader.phpinc');
$f_uId = formValue('uId'); if ( $f_uId ) { $userId = $f_uId; $userFullName = username($userId); } else { $userFullName = $_SESSION['userFullName']; $userId = $_SESSION['userId']; } $userFileName = str_replace(" ","",$userFullName);
$sectionTitle = $courseNumber.' '.$courseTitle.' - '.$title.' - '.username($userId);
include('common/sectionHeader.phpinc');
include('Gradebook/EvaluationSubmission_BEGIN.phpinc');
?>
<style type="text/css">b {white-space:nowrap;}</style>
<p>
<fieldset><legend><?=$evaluationType?> <?=$evaluationNumber?> Assignment</legend>
The purpose of this lab is to demonstrate your ability to use MySQL procedures.
<br><b>Note: Use the <a href="http://www.mysqltutorial.org/download/2">MySQL Sample Database</a> `classicmodels` for this lab.</b>
<br><?=$gradeNote?>
<ol>
<li>
Create a directory called <b><?=$evaluationName?></b>. We will use it for all work performed in this lab.
</li>
<li>
Write a procedure called <b>GetAllCustomerLevels</b>that returns the customerNumber, customerName, and cardtype for all customers.
<br>Hint: Use CASE in your select statement. See:
<a href="http://stackoverflow.com/questions/14189216/case-in-select-statement">Case in Select Statement</a> at stackoverflow, or
<a href="http://www.w3resource.com/mysql/control-flow-functions/case-operator.php">MySQL CASE operator</a> from w3resource.
<br>This could also be done using a nested IF. See:
the <a href="../../WebExRecordings/CST1600 wk11 2016-03-24.wrf">WebEx recording from Thursday, March 24th</a>, or
<a href="http://timmurphy.org/2009/08/13/inline-if-and-case-statements-in-mysql/">Inline IF and CASE statements in MySQL</a> from timmurphy.
<br>See the <a href="../../WebExRecordings/CST1600 wk11 2016-04-12.wrf">WebEx recording from Tuesday, April 12th</a> to refresh your memory about Stored Procedures.
<br>Below is an example procedure that returns the customerNumber and customerName for all customers:
<table><tr><td><pre>DROP PROCEDURE IF EXISTS GetAllCustomerLevels;
DELIMITER $$
CREATE PROCEDURE GetAllCustomerLevels()
BEGIN
select customerNumber, customerName
from customers;
END$$
DELIMITER ;</pre></td></tr></table>
</li>
<li>
Save the sql that created the <b>GetAllCustomerLevels</b> procedure as <b>GetAllCustomerLevels.sql</b>.
</li>
<li>
Call <b>GetAllCustomerLevels</b>(); and take a screenshot showing some of the results.
<br>Save it as a png file called: <b><?=$userFullName?>_Results.png</b>.
</li>
<li>Turn in your assignment:
<ol type="a">
<li>Zip up your <b><?=$evaluationName?></b> folder into a file named <b><?=$uploadFilename?></b>.</li>
<li>Browse and select your <b><?=$uploadFilename?></b> file: <input type="file" name="fileUpload"></li>
<li>Then <input type="submit" name="f_subtask" value="Submit"> for grading.</li>
</ol>
</li>
</ol>
<?=$gradeNote?>
</fieldset>
<?
include('Gradebook/EvaluationSubmission_END.phpinc');
$deductionPoints = $evaluationInfo['evaluationPoints'] / 10; if ( $deductionPoints == 1 ) { $deductionPoints = '1 point'; } else { $deductionPoints = $deductionPoints.' points'; }
#include('../../CST1600GradingCriteria.phpinc');
#include('../../Notes/notes.phpinc');
include('common/sectionFooter.phpinc');
include('../../ReferencesResources.phpinc');
include('common/pageFooter.phpinc');
if ($TRACK != '') $_SESSION['TRACK'] .= "<li>End ".basename(__FILE__)."</ol>\n";
?>