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></p>
</div>
<div id="viewreadmore" class="link">
<a id="readmore" href="javascript:void(0);" class="read-more-trigger mar_top10" onclick="changeheight(this)">View Complete Question</a>
</div>
<div id="DivRequest">
<h4>
Request for Solution File </h4>
<div class="seprator">
</div>
<div class="downloadfiles">
<h5>
Ask an Expert for Answer!!</h5>
<h6>
Basic Computer Science: Display the order number and customer number for all orders</h6>
<h5>
Reference No:- TGS01246131</h5>
<input type="submit" name="btnGetQuote" value="Request for Solution File" id="btnGetQuote" class="btn btnexperts btn-lg btn-block-sm mar_btm20" />
<p>
Expected delivery within 24 Hours</p>
</div>
</div>
</div>
<div class="row">
<div class="col-sm-12 reviewbox">
<div class="row ">
<div class="panel-group review" id="accordion" role="tablist" aria-multiselectable="true">
<div class="panel-heading" role="tab" id="headingTwo">
<h4 class="panel-title">
<a class="collapsed" role="button" data-toggle="collapse" data-parent="#accordion" href="#collapseTwo" aria-expanded="false" aria-controls="collapseTwo">
Have a Question? (oR Write a Review)
</a>
</h4>
</div>
<div id="collapseTwo" class="panel-collapse collapse" role="tabpanel" aria-labelledby="headingTwo">
<div class="panel-body">
<div class="col-sm-12">
<div class="row search searchbg message">
<span id="RequiredFieldValidator1" style="visibility:hidden;">Write atleast 100 words!!</span>
<textarea name="txtcomments" id="txtcomments" maxlength="1000" ValidationGroup="Review" placeholder="Write your review" class="form-control" rows="6"></textarea>
<div class="pull-right mar_top20">
<input type="submit" name="btnReviewSubmit" value="Submit" onclick="javascript:WebForm_DoPostBackWithOptions(new WebForm_PostBackOptions("btnReviewSubmit", "", true, "Review", "", false, false))" id="btnReviewSubmit" class="btn btn-primary pull-right" />
</div>
</div>
</div>
</div>
</div>
</div>
</div>
</div>
</div>
<div class="user-comments-area hidden-xs">
<h4 class="text-uppercase mar_btm20">
<i class="fa fa-question-circle"></i> Recent Questions Asked Basic Computer Science</h4>
<ul class="user-comments-list">
<table id="dlMaterials" cellspacing="0" style="width:100%;border-collapse:collapse;">
<tr>
<td>
<li>
<div class="comment-box">
<h5>
<span class="mar_lft5">Q :</span> <a id="dlMaterials_hypermaterial_0" class="studenthdname" href="https://www.tutorsglobe.com/question/imagine-a-circular-linked-list-of-integers-that-are-sorted-51246127.aspx">Imagine a circular linked list of integers that are sorted</a></h5>
<p class="answer">
<span id="dlMaterials_lblQuestion_0">imagine a circular linked list of integers that are sorted into ascending order as shown in the attached filethe</span></p>
</div>
<!-- /comment-box -->
</li>
</td>
</tr><tr>
<td>
<li>
<div class="comment-box">
<h5>
<span class="mar_lft5">Q :</span> <a id="dlMaterials_hypermaterial_1" class="studenthdname" href="https://www.tutorsglobe.com/question/prioritise-marketing-activities-and-obtain-resources-for-51246128.aspx">Prioritise marketing activities and obtain resources for</a></h5>
<p class="answer">
<span id="dlMaterials_lblQuestion_1">select appropriate marketing activities11access marketing plan and marketing strategy for specific international target</span></p>
</div>
<!-- /comment-box -->
</li>
</td>
</tr><tr>
<td>
<li>
<div class="comment-box">
<h5>
<span class="mar_lft5">Q :</span> <a id="dlMaterials_hypermaterial_2" class="studenthdname" href="https://www.tutorsglobe.com/question/discuss-the-following-with-regard-to-the-role-and-51246129.aspx">Discuss the following with regard to the role and</a></h5>
<p class="answer">
<span id="dlMaterials_lblQuestion_2">discussion question 1- in community and public health nursing the target of care is the community thus the community is</span></p>
</div>
<!-- /comment-box -->
</li>
</td>
</tr><tr>
<td>
<li>
<div class="comment-box">
<h5>
<span class="mar_lft5">Q :</span> <a id="dlMaterials_hypermaterial_3" class="studenthdname" href="https://www.tutorsglobe.com/question/hi-i-need-to-write-essay-about-market-research-however-for-51246130.aspx">Hi i need to write essay about market research however for</a></h5>
<p class="answer">
<span id="dlMaterials_lblQuestion_3">hi i need to write essay about market research however for the analysis i need to consider the information about</span></p>
</div>
<!-- /comment-box -->
</li>
</td>
</tr><tr>
<td>
<li>
<div class="comment-box">
<h5>
<span class="mar_lft5">Q :</span> <a id="dlMaterials_hypermaterial_4" class="studenthdname" href="https://www.tutorsglobe.com/question/display-the-order-number-and-customer-number-for-all-orders-51246131.aspx">Display the order number and customer number for all orders</a></h5>
<p class="answer">
<span id="dlMaterials_lblQuestion_4">write sql queries that meet the following specifications do not show output all questions are worth three marksa </span></p>
</div>
<!-- /comment-box -->
</li>
</td>
</tr><tr>
<td>
<li>
<div class="comment-box">
<h5>
<span class="mar_lft5">Q :</span> <a id="dlMaterials_hypermaterial_5" class="studenthdname" href="https://www.tutorsglobe.com/question/why-is-it-important-to-conduct-research-into-identifying-51246132.aspx">Why is it important to conduct research into identifying</a></h5>
<p class="answer">
<span id="dlMaterials_lblQuestion_5">1why is it important to abide by the provisions of the australian marketing institute code of professional conduct2what</span></p>
</div>
<!-- /comment-box -->
</li>
</td>
</tr><tr>
<td>
<li>
<div class="comment-box">
<h5>
<span class="mar_lft5">Q :</span> <a id="dlMaterials_hypermaterial_6" class="studenthdname" href="https://www.tutorsglobe.com/question/please-submit-the-code-and-output-for-all-programming-51246133.aspx">Please submit the code and output for all programming</a></h5>
<p class="answer">
<span id="dlMaterials_lblQuestion_6">please submit the code and output for all programming questions you will submit this assignment electronically in the</span></p>
</div>
<!-- /comment-box -->
</li>
</td>
</tr><tr>
<td>
<li>
<div class="comment-box">
<h5>
<span class="mar_lft5">Q :</span> <a id="dlMaterials_hypermaterial_7" class="studenthdname" href="https://www.tutorsglobe.com/question/is-noailles-being-unfairly-penalised-if-she-implements-jit-51246134.aspx">Is noailles being unfairly penalised if she implements jit</a></h5>
<p class="answer">
<span id="dlMaterials_lblQuestion_7">just-in-time systems ethicsanna de noailles the plant manager at electro-sons calls pauline eluard the plant controller</span></p>
</div>
<!-- /comment-box -->
</li>
</td>
</tr><tr>
<td>
<li>
<div class="comment-box">
<h5>
<span class="mar_lft5">Q :</span> <a id="dlMaterials_hypermaterial_8" class="studenthdname" href="https://www.tutorsglobe.com/question/please-write-a-summary-of-book-one-pg-1-60-of-the-dead-do-51246135.aspx">Please write a summary of book one pg 1-60 of the dead do</a></h5>
<p class="answer">
<span id="dlMaterials_lblQuestion_8">please write a summary of book one pg 1-60 of the dead do not improve by jay caspian kangguidelines one pagetwo</span></p>
</div>
<!-- /comment-box -->
</li>
</td>
</tr>
</table>
</ul>
<!-- /user-comments-list -->
</div>
</div>
<div class="col-md-4 col-xs-12 login-area innerpage">
<div class="row">
<div class="details col-md-12">
<div class="col-md-4">
<div class="circle orange">
<i class="fa fa-question"></i>
</div>
<p>
1955594
</p>
<p>
Questions<br />
Asked</p>
</div>
<div class="col-md-4">
<div class="circle yellow">
<i class="fa fa-user-secret"></i>
</div>
<p>
3,689</p>
<p>
Active Tutors</p>
</div>
<div class="col-md-4">
<div class="circle green">
<i class="fa fa-thumbs-o-up"></i>
</div>
<p>
1454691</p>
<p>
Questions<br />
Answered</p>
</div>
<p><b>
Start Excelling in your courses, Ask a tutor for help and get answers for your problems !! </b></p>
<a href="https://www.tutorsglobe.com/post-your-job-for-free.aspx" class="btn btn-primary btn-lg mar_top10">ask Question</a>
</div>
</div>
<div class="row">
<div class="user-comments-area hidden-xs">
<hr />
<h4 class="text-uppercase mar_btm20">
<i class="fa fa-question-circle"></i> Asked Questions</h4>
<hr />
<ul class="user-comments-list">
<table id="dlNewReviews" cellspacing="0" style="width:100%;border-collapse:collapse;">
<tr>
<td>
<li>
<div class="comment-box">
<h5>
<a id="dlNewReviews_hyperQues_0" class="studenthdname" href="https://www.tutorsglobe.com/question/discuss-patient-who-is-about-to-receive-a-12-lead-ekg-53436664.aspx">Discuss patient who is about to receive a 12-lead ekg</a></h5>
<p>
<span id="dlNewReviews_lblReviews_0">Question: Which instruction makes the MOST sense to give a patient who is about to receive a 12-lead EKG? </span></p>
</div>
<!-- /comment-box -->
</li>
</td>
</tr><tr>
<td>
<li>
<div class="comment-box">
<h5>
<a id="dlNewReviews_hyperQues_1" class="studenthdname" href="https://www.tutorsglobe.com/question/which-type-of-care-setting-more-preferred-by-rural-patients-53436655.aspx">Which type of care setting more preferred by rural patients</a></h5>
<p>
<span id="dlNewReviews_lblReviews_1">Which type of care setting is more preferred by rural patients receiving hospice care compared to their urban counterparts?</span></p>
</div>
<!-- /comment-box -->
</li>
</td>
</tr><tr>
<td>
<li>
<div class="comment-box">
<h5>
<a id="dlNewReviews_hyperQues_2" class="studenthdname" href="https://www.tutorsglobe.com/question/what-is-your-next-course-of-action-for-mr-hook-53436650.aspx">What is your next course of action for mr. hook</a></h5>
<p>
<span id="dlNewReviews_lblReviews_2"> Mr. hook is a 74 year old male that is 3 days post open heart surgery. He weighs 71 kilograms. He still has a swan Ganz catheter in place. </span></p>
</div>
<!-- /comment-box -->
</li>
</td>
</tr><tr>
<td>
<li>
<div class="comment-box">
<h5>
<a id="dlNewReviews_hyperQues_3" class="studenthdname" href="https://www.tutorsglobe.com/question/how-eligibility-for-palliative-care-differ-from-hospice-care-53436654.aspx">How eligibility for palliative care differ from hospice care</a></h5>
<p>
<span id="dlNewReviews_lblReviews_3"> How does eligibility for palliative care differ from hospice care? Group of answer choices Palliative care is available to anyone with a serious illness</span></p>
</div>
<!-- /comment-box -->
</li>
</td>
</tr><tr>
<td>
<li>
<div class="comment-box">
<h5>
<a id="dlNewReviews_hyperQues_4" class="studenthdname" href="https://www.tutorsglobe.com/question/how-would-you-describe-the-culture-of-safety-53436667.aspx">How would you describe the culture of safety</a></h5>
<p>
<span id="dlNewReviews_lblReviews_4">How would you describe its culture of safety? If it is not congruent with a culture of safety, why does it not meet the criteria for this type of culture?</span></p>
</div>
<!-- /comment-box -->
</li>
</td>
</tr><tr>
<td>
<li>
<div class="comment-box">
<h5>
<a id="dlNewReviews_hyperQues_5" class="studenthdname" href="https://www.tutorsglobe.com/question/identifying-a-child-who-has-decreased-access-to-health-care-53436669.aspx">Identifying a child who has decreased access to health care</a></h5>
<p>
<span id="dlNewReviews_lblReviews_5">Which of the following factors should the nurse consider when identifying a child who has decreased access to health care? </span></p>
</div>
<!-- /comment-box -->
</li>
</td>
</tr><tr>
<td>
<li>
<div class="comment-box">
<h5>
<a id="dlNewReviews_hyperQues_6" class="studenthdname" href="https://www.tutorsglobe.com/question/problem-related-to-schizophrenic-patient-53436656.aspx">Problem related to schizophrenic patient</a></h5>
<p>
<span id="dlNewReviews_lblReviews_6">Rewrite this statement: I have been following a schizophrenic patient over the last three weeks for medication non-compliance and illicit drug use</span></p>
</div>
<!-- /comment-box -->
</li>
</td>
</tr>
</table>
</ul>
</div>
</div>
</div>
</div>
</div>
</div>
</div>
<script>
var url = 'https://www.tutorsglobe.com/include/javascript/watiWidget.js';
var s = document.createElement('script');
s.type = 'text/javascript';
s.async = true;
s.src = url;
var options = {
"enabled":true,
"chatButtonSetting":{
"backgroundColor":"#00e785",
"ctaText":"Whatsapp Support!!",
"borderRadius":"25",
"marginLeft": "0",
"marginRight": "20",
"marginBottom": "20",
"ctaIconWATI":false,
"position":"left"
},
"brandSetting":{
"brandName":"Tutorsglobe",
"brandSubTitle":"Trusted Since 2005",
"brandImg":"https://www.tutorsglobe.com/include/images/chat-logo.svg",
"welcomeText":"Hi there!\nDo you Need help?",
"messageText":"Hello, Tutorsglobe !! I have a question!",
"backgroundColor":"#00e785",
"ctaText":"Chat with Whatsapp",
"borderRadius":"25",
"autoShow":false,
"phoneNumber":"441416286080"
}
};
s.onload = function() {
CreateWhatsappChatWidget(options);
};
var x = document.getElementsByTagName('script')[0];
x.parentNode.insertBefore(s, x);
</script>
<footer class="site-footer">
<div class="container">
<div class="footerlinks">
<a href="https://www.tutorsglobe.com/">Home</a>
|
<a href="https://www.tutorsglobe.com/about-us.aspx">Company Overview</a>
|
<a href="https://www.tutorsglobe.com/services.aspx">Services</a>
|
<a href="https://www.tutorsglobe.com/library/">Discover Q&A</a>
|
<a href="https://www.tutorsglobe.com/sitemap.aspx">Sitemap</a>
|
<a href="https://www.tutorsglobe.com/contact-us.aspx">Contact Us</a>
|
<a href="https://www.tutorsglobe.com/terms-and-conditions.aspx">T & C</a>
|
<a href="https://www.tutorsglobe.com/refundcancelpolicy.aspx">Refund Policy</a>
|
<a href="https://www.tutorsglobe.com/copyright-infringement-policy.aspx">Copyright Policy</a>
|
<a href="https://www.tutorsglobe.com/blog/archive/">Blog</a>
|
<a href="https://www.tutorsglobe.com/library/archive.aspx">Q&A</a>
|
<a href="https://www.tutorsglobe.com/education-directory.aspx">Directory</a>
</div>
<p>©TutorsGlobe</a> All rights reserved 2022-2023. </p>
<script type="application/ld+json">
{
"@context": "http://schema.org/",
"@type": "product",
"name": "Tutorsglobe",
"image": "https://www.tutorsglobe.com/IncludeLib/Images/logo.png",
"description": "elearning Platform - Tutor Service",
"brand": {
"@type": "elearning",
"name": "Tutorsglobe"
},
"aggregateRating": {
"@type": "AggregateRating",
"ratingValue": "4.9",
"ratingCount": "37128"
}
}
</script>
<a href="#" class="settings"><i class="fa fa-angle-up"></i></a>
<ul class="social-icons">
<li><a href="https://www.facebook.com/TutorsGlobe" rel="nofollow" target="_blank"><i class="fa fa-facebook-square"></i></a></li>
<li><a href="https://twitter.com/Tutorsglobe" rel="nofollow" target="_blank"><i class="fa fa-twitter-square"></i></a></li>
<li><a href="#" rel="nofollow"><i class="fa fa-youtube-square"></i></a></li>
<li><a href="https://www.linkedin.com/company/tutorsglobe" target="_blank" rel="nofollow"><i class="fa fa-linkedin-square"></i></a></li>
</ul>
</div>
<script type="text/javascript">
var _gaq = _gaq || [];
_gaq.push(['_setAccount', 'UA-32333066-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') + '.tutorsglobe.com/IncludeLib/js/ga.js';
var s = document.getElementsByTagName('script')[0]; s.parentNode.insertBefore(ga, s);
})();
</script>
<script async src="https://www.googletagmanager.com/gtag/js?id=G-5E9QFMFDJR"></script>
<script>
window.dataLayer = window.dataLayer || [];
function gtag(){dataLayer.push(arguments);}
gtag('js', new Date());
gtag('config', 'G-5E9QFMFDJR');
</script>
</footer>
</div>
<!-- /pageWrap -->
<div class="overlay">
</div>
<!-- JavaScript Files
================================================== -->
<script type="text/javascript" src="../IncludeLib/js/jquery-1.11.2.min.js"></script>
<script type="text/javascript" src="../IncludeLib/js/bootstrap.min.js"></script>
<script type="text/javascript" src="../IncludeLib/js/jquery.mCustomScrollbar.concat.min.js"></script>
<script type="text/javascript" src="../IncludeLib/js/script.js"></script>
<script type="text/javascript" src="../IncludeLib/js/ie10-viewport-bug-workaround.js"></script>
<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 = "txtcomments";
RequiredFieldValidator1.errormessage = "Write atleast 100 words!!";
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>