Write SQL queries that meet the following specifications. Do not show output. All questions are worth three marks.
a) Display the order number and customer number for all orders. Sort by order number within customer number.
b) Display full details for all rows where the shipping street contains the string MAIN.
c) Display the title for each book and the profit margin for that book (profit = retail - cost). Your query should produce output in the following format. The book
earned a profit of <profit>, for example: The book Easy SQL earned a profit of $30.<br />d) Display full details for all books that cost less than thirty dollars and are in the FITNESS category OR cost more than thirty dollars and are in the SELF HELP category.<br />e) Display the customer id and the average length of time elapsed between an order being placed and the actual shipment of that order (shipment time) for all orders placed by that customer. Display the average length of time in months rounded to the nearest month.<br />f) Display complete details for the order/s with the earliest shipdate.<br />g) Using a set operator, display the customer number for all customers who placed an order during March, 2002 and who were referred by another customer. The column called referred may contain null values.<br />h) For each order item, display the order#, customer last name, item# and quantity.<br />i) Using a set operator, display the order number for all orders that have no items.</p>
<p style="text-align: justify;">j) Display the order number and total quantity ordered for all orders that have a total quantity greater than 120.</p>
<p style="text-align: justify;">Question 2</p>
<p style="text-align: justify;">Write ONE PL/SQL program (anonymous block) that provides a report of all books published by specific publishers that have sold more than one thousand copies. The report should insert into an existing table the publisher name and the total number of copies of all books sold for that publisher (subject to specific business rules). The detailed specification is as follows:</p>
<p style="text-align: justify;">· Using an explicit cursor, retrieve all books from the books table that were published by publishers 2, 6 or 7.<br />· Books with a retail price greater than $1 should be selected for further processing. This operation must be done using the IF statement.<br />· Using a second explicit cursor (with appropriate parameters), count the number of copies that the book has sold. Store the total count in a local variable named BOOKCOUNT and the total count of all books for a particular publisher in PUBCOUNT.<br />· If the book has sold one thousand or less copies, move to the next book. If no book has sold one thousand or more copies, exit the program and display the following message: There are no high sales books for these publishers.<br />· If a book has sold one thousand copies or more, retrieve details about the publisher. Use an explicit cursor.<br />· Into a table named HIGHCOUNT, insert the publisher name and the total count of all books sold for that publisher. This table has three columns as follows: publisher name, pubcount and rundate.<br />· Create an exception handler which fires when no rows are found. The exception handler should output the following message to the screen: No rows found.</p></span>
</div></div></div>
<div id="viewreadmore" class="que_link">
<a id="readmore" href="javascript:void(0);" style="font-size: 14px; color: #ff700c;" class="read-more-trigger mar_top10" onclick="changeheight(this)">View complete question</a>
</div>
<div class="innercanteenouter_inner_center3">
<p>Computer Engineering, <span>Engineering</span> </p>
</div>
<div class="clear"></div>
</div>
</div>
<div class="innercanteenouter_inner_center6">
<div class="innercanteenouter_inner_center6_center7">
<div class="innercanteenouter_inner_center6_center">
<ul>
<li class="innercanteenouter1">
Category:- <span>Computer Engineering</span>
</li>
<li class="innercanteenouter2">
Reference No.:- <span>M91607693 </span>
</li>
</ul>
<div class="clear"></div>
<input type="submit" name="btnASolution" value="Order Now to Get Expert's Answer" id="btnASolution" class="innercanteenouter_inner_center6_center2" />
</div>
<div class="innerjohnanswered1">
<div class="innerjohnanswered1ftti"><h2></h2>
<p></p>
</div>
<div class="clear"></div>
</div>
</div>
</div>
<div class="inneranswer">
<b>Have any Question?</b><span id="RequiredFieldValidator1" style="background-color:Yellow;visibility:hidden;">Write your Review or question?</span>
<br /><br />
<div class="inneranswerhgrows">
<textarea name="txtReview" id="txtReview" class="inneranswerhgrowss" ValidationGroup="Review" placeholder=" Write your message!!"></textarea>
</div>
<div class="inneranswerhgrowsddf">
<input type="submit" name="btnReview" value="Have Question" onclick="javascript:WebForm_DoPostBackWithOptions(new WebForm_PostBackOptions("btnReview", "", true, "Review", "", false, false))" id="btnReview" class="planpageinnerrightcontleftcommetgetquotebt" />
</div>
</div>
<br />
<div class="innerquestions">
<h2>Related Questions in Computer Engineering</h2>
<table id="dlReviewList" cellspacing="0" style="width:100%;border-collapse:collapse;">
<tr>
<td>
<div class="innerquestionsgtr">
<h3>
<span id="dlReviewList_lblTitle_0"><b><a href='http://www.mywordsolution.com/question/does-bmw-have-a-guided-missile-corporate-culture-and/93107955' target='_balnk' class='que_link' >Does bmw have a guided missile corporate culture and</a></b></span></h3>
<p>
<span id="dlReviewList_lblContent_0">Does BMW have a guided missile corporate culture, and incubator corporate culture, a family corporate culture, or an Eiffel tower corporate culture?</span> </p>
</div>
</td>
</tr><tr>
<td>
<div class="innerquestionsgtr">
<h3>
<span id="dlReviewList_lblTitle_1"><b><a href='http://www.mywordsolution.com/question/rebecca-borrows-10000-at-18-compounded-annually-she-pays/93107957' target='_balnk' class='que_link' >Rebecca borrows 10000 at 18 compounded annually she pays</a></b></span></h3>
<p>
<span id="dlReviewList_lblContent_1">Rebecca borrows $10,000 at 18% compounded annually. She pays off the loan over a 5-year period with annual payments, starting at year 1. Each successive payment is $700 greater than the previous payment. (a) How much was ...</span> </p>
</div>
</td>
</tr><tr>
<td>
<div class="innerquestionsgtr">
<h3>
<span id="dlReviewList_lblTitle_2"><b><a href='http://www.mywordsolution.com/question/jeff-decides-to-start-saving-some-money-from-this-upcoming/93107959' target='_balnk' class='que_link' >Jeff decides to start saving some money from this upcoming</a></b></span></h3>
<p>
<span id="dlReviewList_lblContent_2">Jeff decides to start saving some money from this upcoming month onwards. He decides to save only $500 at first, but each month he will increase the amount invested by $100. He will do it for 60 months (including the fir ...</span> </p>
</div>
</td>
</tr><tr>
<td>
<div class="innerquestionsgtr">
<h3>
<span id="dlReviewList_lblTitle_3"><b><a href='http://www.mywordsolution.com/question/suppose-you-make-30-annual-investments-in-a-fund-that-pays/93107961' target='_balnk' class='que_link' >Suppose you make 30 annual investments in a fund that pays</a></b></span></h3>
<p>
<span id="dlReviewList_lblContent_3">Suppose you make 30 annual investments in a fund that pays 6% compounded annually. If your first deposit is $7,500 and each successive deposit is 6% greater than the preceding deposit, how much will be in the fund immedi ...</span> </p>
</div>
</td>
</tr><tr>
<td>
<div class="innerquestionsgtr">
<h3>
<span id="dlReviewList_lblTitle_4"><b><a href='http://www.mywordsolution.com/question/question--under-what-circumstances-is-it-ethical-if-ever-to/93107963' target='_balnk' class='que_link' >Question -under what circumstances is it ethical if ever to</a></b></span></h3>
<p>
<span id="dlReviewList_lblContent_4">Question :- Under what circumstances is it ethical, if ever, to use consumer information in marketing research? Explain why you consider it ethical or unethical.</span> </p>
</div>
</td>
</tr><tr>
<td>
<div class="innerquestionsgtr">
<h3>
<span id="dlReviewList_lblTitle_5"><b><a href='http://www.mywordsolution.com/question/what-are-the-differences-between-four-types-of-economics/93107964' target='_balnk' class='que_link' >What are the differences between four types of economics</a></b></span></h3>
<p>
<span id="dlReviewList_lblContent_5">What are the differences between four types of economics evaluations and their differences with other two (budget impact analysis (BIA) and cost of illness (COI) studies)?</span> </p>
</div>
</td>
</tr><tr>
<td>
<div class="innerquestionsgtr">
<h3>
<span id="dlReviewList_lblTitle_6"><b><a href='http://www.mywordsolution.com/question/what-type-of-economic-system-does-norway-have-explain-some/93107967' target='_balnk' class='que_link' >What type of economic system does norway have explain some</a></b></span></h3>
<p>
<span id="dlReviewList_lblContent_6">What type of economic system does Norway have? Explain some of the benefits of this system to the country and some of the drawbacks,</span> </p>
</div>
</td>
</tr><tr>
<td>
<div class="innerquestionsgtr">
<h3>
<span id="dlReviewList_lblTitle_7"><b><a href='http://www.mywordsolution.com/question/among-the-who-imf-and-wto-which-of-these-governmental/93107969' target='_balnk' class='que_link' >Among the who imf and wto which of these governmental</a></b></span></h3>
<p>
<span id="dlReviewList_lblContent_7">Among the WHO, IMF, and WTO, which of these governmental institutions do you feel has most profoundly shaped healthcare outcomes in low-income countries and why? Please support your reasons with examples and research/doc ...</span> </p>
</div>
</td>
</tr><tr>
<td>
<div class="innerquestionsgtr">
<h3>
<span id="dlReviewList_lblTitle_8"><b><a href='http://www.mywordsolution.com/question/a-real-estate-developer-will-build-two-different-types-of/93108379' target='_balnk' class='que_link' >A real estate developer will build two different types of</a></b></span></h3>
<p>
<span id="dlReviewList_lblContent_8">A real estate developer will build two different types of apartments in a residential area: one- bedroom apartments and two-bedroom apartments. In addition, the developer will build either a swimming pool or a tennis cou ...</span> </p>
</div>
</td>
</tr><tr>
<td>
<div class="innerquestionsgtr">
<h3>
<span id="dlReviewList_lblTitle_9"><b><a href='http://www.mywordsolution.com/question/question-what-some-of-the-reasons-that-evolutionary-models/93108381' target='_balnk' class='que_link' >Question what some of the reasons that evolutionary models</a></b></span></h3>
<p>
<span id="dlReviewList_lblContent_9">Question : What some of the reasons that evolutionary models are considered by many to be the best approach to software development. The response must be typed, single spaced, must be in times new roman font (size 12) an ...</span> </p>
</div>
</td>
</tr><tr>
<td></td>
</tr>
</table>
</div>
<div class="innerlogo">
<ul>
<li><div class="innerlogoback1"></div></li>
<li><div class="innerlogoback2"></div></li>
<li><div class="innerlogoback3"></div></li>
</ul>
</div>
</div>
<div class="innercanteenouter_inner_right">
<div class="innercanteenouter_inner_right1">
<div class="innercanteenouter_inner_right1pou">
<ul>
<li class="red1">
<div class="counterDivTag">4,153,160 Questions Asked</div>
</li>
<li class="oring1"><div class="counterDivTag">13,132 Experts</div></li>
<li class="grine1"><div class="counterDivTag">2,558,936 Questions Answered</div></li>
</ul>
<div class="clear"></div>
<h2>Ask Experts for help!!</h2>
</div>
</div>
<div class="innerchoose">
<h2>Looking for Assignment Help?</h2>
<p>
<b>Start excelling in your Courses,
Get help with Assignment</b><br /><br />
Write us your full requirement for evaluation and you will receive response within 20 minutes turnaround time.
</p>
<a href="http://www.mywordsolution.com/free-quote-job-posting.aspx">Ask Now</a>
<span>Help with Problems, Get a Best Answer</span>
</div>
<div class="innertranspoitation">
<div class="innertranspoitation1">
<ul>
<li><a href="#">Recent Questions </a></li>
</ul>
<div class="clear"></div>
</div>
<table id="LeftSideControl1_RecentQuestion1_dlReviewList" cellspacing="0" style="width:100%;border-collapse:collapse;">
<tr>
<td>
<div class="innertranspoitation2">
<h3><span id="LeftSideControl1_RecentQuestion1_dlReviewList_lblTitle_0"><b><a href='http://www.mywordsolution.com/question/why-might-a-bank-avoid-the-use-of-interest-rate-swaps-even/93137978' target='_balnk' class='que_link' >Why might a bank avoid the use of interest rate swaps even</a></b></span></h3>
<p> <span id="LeftSideControl1_RecentQuestion1_dlReviewList_lblContent_0">Why might a bank avoid the use of interest rate swaps, even when the institution is exposed to significant interest rate</span> </p>
</div>
</td>
</tr><tr>
<td>
<div class="innertranspoitation2">
<h3><span id="LeftSideControl1_RecentQuestion1_dlReviewList_lblTitle_1"><b><a href='http://www.mywordsolution.com/question/describe-the-difference-between-zero-coupon-bonds-and/93137977' target='_balnk' class='que_link' >Describe the difference between zero coupon bonds and</a></b></span></h3>
<p> <span id="LeftSideControl1_RecentQuestion1_dlReviewList_lblContent_1">Describe the difference between zero coupon bonds and coupon bonds. Under what conditions will a coupon bond sell at a p</span> </p>
</div>
</td>
</tr><tr>
<td>
<div class="innertranspoitation2">
<h3><span id="LeftSideControl1_RecentQuestion1_dlReviewList_lblTitle_2"><b><a href='http://www.mywordsolution.com/question/compute-the-present-value-of-an-annuity-of--880-per-year/93137976' target='_balnk' class='que_link' >Compute the present value of an annuity of 880 per year</a></b></span></h3>
<p> <span id="LeftSideControl1_RecentQuestion1_dlReviewList_lblContent_2">Compute the present value of an annuity of $ 880 per year for 16 years, given a discount rate of 6 percent per annum. As</span> </p>
</div>
</td>
</tr><tr>
<td>
<div class="innertranspoitation2">
<h3><span id="LeftSideControl1_RecentQuestion1_dlReviewList_lblTitle_3"><b><a href='http://www.mywordsolution.com/question/compute-the-present-value-of-an-1150-payment-made-in-ten/93137975' target='_balnk' class='que_link' >Compute the present value of an 1150 payment made in ten</a></b></span></h3>
<p> <span id="LeftSideControl1_RecentQuestion1_dlReviewList_lblContent_3">Compute the present value of an $1,150 payment made in ten years when the discount rate is 12 percent. (Do not round int</span> </p>
</div>
</td>
</tr><tr>
<td>
<div class="innertranspoitation2">
<h3><span id="LeftSideControl1_RecentQuestion1_dlReviewList_lblTitle_4"><b><a href='http://www.mywordsolution.com/question/compute-the-present-value-of-an-annuity-of--699-per-year/93137974' target='_balnk' class='que_link' >Compute the present value of an annuity of 699 per year</a></b></span></h3>
<p> <span id="LeftSideControl1_RecentQuestion1_dlReviewList_lblContent_4">Compute the present value of an annuity of $ 699 per year for 19 years, given a discount rate of 6 percent per annum. As</span> </p>
</div>
</td>
</tr><tr>
<td></td>
</tr>
</table>
</div>
</div>
<div class="clear"></div>
</div>
<div class="clear"></div>
</div>
</div>
<div class="footer">
<div class="footerouter">
<div class="footerouter_inner">
<div class="footerouter_left">
<ul>
<li>
<div class="footerouter_left1">
<ul>
<li><a href="http://www.mywordsolution.com/homework-help/science/8">Science</a></li>
<li><a href="http://www.mywordsolution.com/homework-help/english/390">English</a></li>
<li><a href="http://www.mywordsolution.com/homework-help/biology/384">Biology</a></li>
<li><a href="http://www.mywordsolution.com/homework-help/humanities/388">Humanities</a></li>
</ul>
</div>
</li>
<li>
<div class="footerouter_left1">
<ul>
<li><a href="http://www.mywordsolution.com/homework-help/engineering/9">Engineering</a></li>
<li><a href="http://www.mywordsolution.com/homework-help/programming/10">Programming</a></li>
<li><a href="http://www.mywordsolution.com/homework-help/computer-science/11">Computers/IT Courses</a></li>
</ul>
</div>
</li>
<li>
<div class="footerouter_left1">
<ul>
<li><a href="http://www.mywordsolution.com/homework-help/accounting/1">Accounting</a></li>
<li><a href="http://www.mywordsolution.com/homework-help/finance/2">Finance</a></li>
<li><a href="http://www.mywordsolution.com/homework-help/management/7">Management</a></li>
<li><a href="http://www.mywordsolution.com/homework-help/statistics/6">Statistics</a></li>
<li><a href="http://www.mywordsolution.com/homework-help/economics/5">Economics</a></li>
<li><a href="http://www.mywordsolution.com/homework-help/taxation/4">Taxation</a></li>
<li><a href="http://www.mywordsolution.com/homework-help/law/3">LAW Assignment Help</a></li>
</ul>
</div>
</li>
<li>
<div class="footerouter_left1">
<ul>
<li><a href="http://www.mywordsolution.com/homework-help/essay/13">Essay Writing Help</a></li>
<li><a href="http://www.mywordsolution.com/homework-help/dissertation/12">Dissertation Help</a></li>
<li><a href="http://www.mywordsolution.com/homework-help/thesis-writing/117">Thesis Help</a></li>
<li><a href="http://www.mywordsolution.com/homework-help/literature-review/79">Literature Review</a></li>
<li><a href="http://www.mywordsolution.com/homework-help/research-paper/78">Research Paper</a></li>
<li><a href="http://www.mywordsolution.com/homework-help/solved-classroom-assignments/77">Solved Problems</a></li>
<li><a href="http://www.mywordsolution.com/homework-help/popular-courses-and-coursework-help/82 ">Coursework Help</a></li>
</ul>
</div>
</li>
<div class="clear"></div>
</li>
</ul>
</div>
<div class="footerouter_right">
<div class="footerouter_right0">
<div class="footerpayment">
<div class="footerpaymentscur"></div>
<div class="footerpaymentscard">
<ul>
<li class="footerpaymentscard1"></li>
<li class="footerpaymentscard2"></li>
<li class="footerpaymentscard3"></li>
<li class="footerpaymentscard4"></li>
</ul>
</div>
</div>
<div class="footerouter_right1">
<h2>Follow Us</h2>
<ul>
<li class="fb"><a href="http://www.facebook.com/Mywordsolution" rel="nofollow" target="_blank"></a></li>
<li class="twt"><a href="http://www.twitter.com/Mywordsolution" rel="nofollow" target="_blank"></a></li>
</ul>
<div class="clear"></div>
</div>
</div>
</div>
<div class="clear"></div>
</div>
</div>
<div class="footerbottom">
<div class="footerbottom_inner">
<ul>
<li><a href="http://www.mywordsolution.com/">Home</a></li>
<li><a href="http://www.mywordsolution.com/about-us.aspx">About Us</a></li>
<li><a href="http://www.mywordsolution.com/blog/">Blog</a></li>
<li><a href="http://www.mywordsolution.com/faqs.aspx">FAQs</a></li>
<li><a href="http://www.mywordsolution.com/ask-question.aspx">Assignment Help</a></li>
<li><a href="http://www.mywordsolution.com/homework-help.aspx">Homework Help</a></li>
<li><a href="http://www.mywordsolution.com/contactus.aspx">Contact Us</a></li>
<li><a href="http://www.mywordsolution.com/questions/archive/">Q&A</a></li>
<li><a href="http://www.mywordsolution.com/directory.aspx">Directory</a></li>
<li><a href="http://www.mywordsolution.com/sitemap.aspx">Sitemap</a></li>
<li><a href="http://www.mywordsolution.com/refund-policy.aspx">Refund Policy</a></li>
<li><a href="http://www.mywordsolution.com/privacy-policy.aspx">Privacy Policy</a></li>
<li><a href="http://www.mywordsolution.com/terms-and-conditions.aspx">T & C </a></li>
<li><a href="http://www.mywordsolution.com/disclaimer-policy.aspx">Disclaimer Policy</a></li>
<li> <a href="http://www.mywordsolution.com/copyright-notice.aspx">Copyright Notice</a></li>
</ul>
<div class="clear"></div>
<p>© Copyright 2013-14 <span>mywordsolution.com</span> All rights reserved </p>
<script type="text/javascript">
var _gaq = _gaq || [];
_gaq.push(['_setAccount', 'UA-38762144-1']);
_gaq.push(['_trackPageview']);
(function() {
var ga = document.createElement('script'); ga.type = 'text/javascript'; ga.async = true;
ga.src = ('https:' == document.location.protocol ? 'https://ssl' : 'http://www') + '.mywordsolution.com/library/js/ga.js';
var s = document.getElementsByTagName('script')[0]; s.parentNode.insertBefore(ga, s);
})();
</script>
<!--Start of Tawk.to Script-->
<script type="text/javascript">
var $_Tawk_API={},$_Tawk_LoadStart=new Date();
(function(){
var s1=document.createElement("script"),s0=document.getElementsByTagName("script")[0];
s1.async=true;
s1.src='https://embed.tawk.to/5584638c0ed4869955a0dce2/default';
s1.charset='UTF-8';
s1.setAttribute('crossorigin','*');
s0.parentNode.insertBefore(s1,s0);
})();
</script>
<!--End of Tawk.to Script-->
</div>
</div>
</div>
<script type="text/javascript">
//<![CDATA[
var Page_Validators = new Array(document.getElementById("RequiredFieldValidator1"));
//]]>
</script>
<script type="text/javascript">
//<![CDATA[
var RequiredFieldValidator1 = document.all ? document.all["RequiredFieldValidator1"] : document.getElementById("RequiredFieldValidator1");
RequiredFieldValidator1.controltovalidate = "txtReview";
RequiredFieldValidator1.errormessage = "Write your Review or question?";
RequiredFieldValidator1.validationGroup = "Review";
RequiredFieldValidator1.evaluationfunction = "RequiredFieldValidatorEvaluateIsValid";
RequiredFieldValidator1.initialvalue = "";
//]]>
</script>
<script type="text/javascript">
//<![CDATA[
var Page_ValidationActive = false;
if (typeof(ValidatorOnLoad) == "function") {
ValidatorOnLoad();
}
function ValidatorOnSubmit() {
if (Page_ValidationActive) {
return ValidatorCommonOnSubmit();
}
else {
return true;
}
}
//]]>
</script>
</form>
</body>
</html>
<script data-cfasync="false" src="/cdn-cgi/scripts/5c5dd728/cloudflare-static/email-decode.min.js"></script>