1. Create the following View: (make sure you call your view and show the results

Question

1. Create the following View: (make sure you call your view and show the results.)

SELECT     VendorID, SUM(InvoiceTotal) AS [YTD Invoice Total], SUM(PaymentTotal) AS [YTD Payment Total], SUM(CreditTotal) AS [YTD Credit Total] FROM         dbo.Invoices GROUP BY VendorID

1a. Create a query that will call your view, displaying all of your columns

2. Create a procedure with the following characteristics:

Select VendorID, VendorName, InvoiceNumber, InvoiceDate, InvoiceTotal for a given Vendor ID (name the input parameter @VID).

2a. Create a query that calls your procedure, and sends/returns information about vendor ID 34

Explanation / Answer

Both view and procedure with mysql

Create view query:-

CREATE VIEW Invoice_details AS SELECT VendorID, SUM(InvoiceTotal) AS YTD_Invoice_Total, SUM(PaymentTotal) AS YTD_Payment_Total, SUM(CreditTotal) AS YTD_Credit_Total FROM Invoices GROUP BY VendorID

Call view query:-

SELECT * FROM Invoice_details;

Create procedure:-

DELIMITER $$

CREATE PROCEDURE get_invoices (IN id INT(11))

BEGIN

SELECT VendorID,VendorName,InvoiceNumber,InvoiceDate,InvoiceTotal FROM Invoices WHERE VendorID = id;

END$$

DELIMITER ;

Call procedure query:-

call get_invoices(2);

Explain with php script:-

in this script call procedure and we pass venderID 2

if any record match with venderID 2 then show array otherwise show "No record found." message

<?php

// create database concation here

$con = mysql_connect("localhost","root","")or die('mysql not found');

// select database here

mysql_select_db('chegg',$con)or die('db not found');

// run sql with procedure call get_invoices(2)

$result = mysql_query("call get_invoices(2)")or die("invalid sql");

$check = 0;

while($row=mysql_fetch_array($result)){

print_r($row);

$check++;

}

if($check == 0){

echo 'No record found.';

}

?>

Related Questions

Navigate

• Browse (All)
• Browse 1
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.