Answered step by step
Verified Expert Solution
Link Copied!

Question

1 Approved Answer

Hello, I need Help with SQL queries based on database file . Here are the contents of table Here are the questions ISBN Title Stock

Hello,

I need Help with SQL queries based on database file . Here are the contents of table

image text in transcribed

image text in transcribedimage text in transcribedimage text in transcribedimage text in transcribedimage text in transcribed

Here are the questions

image text in transcribed

ISBN Title Stock Used Click to Add Category Art Price Pablo Picasso Fundamentals of Relational Database: Databases Vector Calculus Mathematics of Finance Descriptive Geometry Datbase Design, Application, Develop Databases Modern Database Management Learning MySQL and MariaDB General Topology Essential Computer Mathematics Finite Mathematics Linear Algebra Continuum Mechanics Operations Management Gardner's Art through the Ages Design Basics Fundamentals of Database SystemsDatabases Managerial Economics Fourier Analysis Probability and Statistics Mathematical Handbook Statistics Vector Analysis Concepts of Database Management Databases Operations Research Matrix Operations 23 12 $13.99 136188 185874 Mathematics Mathematics Mathematics H 2652 54 27290 294220 354461 36290 +37988 H 37990 +38002 38007 + 40663 +42764 +507086 Databases Databases Mathematics Mathematics Mathematics Mathematics Physics Management Art Art $11.96 $63.99 $120.00 $49.99 $21.95 $12.95 $15.95 $15.95 $17.98 $19.95 23 12 508377 $18.95 +53090 + 54513 +60219 60220 60224 Management Mathematics Mathematics Mathematics Mathematics Mathematics $15.99 $10.75 $14.95 $13.49 $13.95 $13.80 $33.95 $27.96 $13.95 4 60227 +60228 6462 7977 7978 8107 12 Management Mathematics Art Author AuthorlD FirstName Click to Add LastName Colley Hoffer Ramesh Topi 10100 Susan 10101 Jeffrey 10102 Venkataraman 10103 Heikki 10104 Russell 10105 Murray 10106 Seymour 10107 John 10108 Joseph 10109 Richard 10110 Dominick 10111 George 10112 Richard 10113 Minor 10114 Frank 10115 Fred 10116 Christin 10117 Richard 10118 Carsten 10119 David 10120 Stephen 10121 Robert 10122 Andrew 10123 Philip 10124 Joseph 10125 Ramez 10126 Shamkant 10127 Ramon 10128 Paulin 10129 Michael er Spiegel Lipschutz Schiller Monks Bronson Salvatore Mase Bronson Hawk Ayres Jr Kleiner Mamiya Tansey Warncke Lauer Pentak Gordon Forge Pratt Adamski Elmasri Navathe Mata-Toledo Cushman Mannino Customer CustomerlD CustomerNa CustomerAddresS State C Click to Add 101 John Smith PO 2335 TX 78799 Texas 5102 Quincey Adam PO 4337 DC 20010 hington DC 5103 Jay Leno 5104 Barry Manilow PO 3726 CA 94205 California 5105 Jerry Seinfeld PO 8788 NY 10047 New York 5106 Salem Municip PO 7 WA 98008 5107 Clark Kent PO 10101 NY 10007 New York 5108 Tom Sawyer PO 46213 MS 39532Mississippi 5109 Alejandro Tole PO 85391 CA 94204 California 5110 Ming Zhao PO 45667 OR 97202 Oregon PO 2675 CA 90210 California Washington Order OrderNOrderDate OrderDelive-CustomerID Click to Add 101 4/13/2016 4/20/2016 02 4/13/20164/19/2 103 4/14/2016 4/23/2016 104 4 105 4/18/2016 4/22/2016 06 4/20/2016 4/23/2016 107 4/22/2016 4/25/2016 108 4/25/2016 109 4/27/2016 110 4/29/2016 111 4/30/2016 112 113 114 115 116 5110 5109 5108 5107 5106 5105 5104 5103 5102 5103 5102 5101 5103 5105 5107 5109 5110 5108 5106 5104 5102 5103 5106 5109 5105 /16/2016 4/20/2016 5/1/2016 5/2/2016 5/7/2016 5/6/2016 5/7/2016 5/3/2016 5/15/2016 16 5/10/2016 5/4/2016 5/10/2016 5/5/2016 5/12/2016 /6/20165/13/2016 5/7/2016 5/20/2016 5/9/2016 5/19/2016 5/1/2016 118 119 120 5/11/2016 5/18/2016 21 5/13/2016 5/21/2016 122 5/15/2016 5/20/2016 123 5 124 5/19/2016 5/27/2016 25 5/20/2016 5/30/2017 17/2016 5/23/2016 OrderLine ISBN Quantity Percentagel Click to Add OrderNo OrderRank 101 1 185874 2 27290 3 37988 4 7978 1 507086 2 508377 3 8107 1 54513 2 7977 3 42764 4 6462 5 60220 1 354461 1 354461 1 40663 2 7978 3 60219 4 185874 101 101 2 101 102 102 102 103 103 103 103 103 104 105 106 106 106 106 107 107 107 108 108 108 2 10 10 10 10 10 10 0 2 1 1252 2 8107 3 507086 1 27290 2 37988 3 60228 2 4 7978 1 54513 108 109 2 2652 3 7977 109 109 110 110 110 2 1 36290 2 136188 3 6462 1 40663 2 2 112 112 112 113 113 113 114 1 507086 2 60220 3 54513 1 60227 2 60220 3 38002 18107 2 wrote Authorld ISBN AuthorRank | Click to Add 10100 185874 10101 354461 10102 354461 10103 354461 10104 36290 10105 60219 10105 60220 10105 60224 10105 60227 10105 60228 10106 37988 10106 37990 10106 38002 10106 38007 10107 38002 10108 42764 10109 7978 10110 54513 10111 40663 10112 7977 10113 27290 10114 2652 10115 507086 10116 507086 10117 507086 10118 1252 10119 508377 10120 508377 10121 8107 10122 8107 2 2 2 2 1. Obtain the names and addresses of all customers who ordered a book by Susan Colley. Avoid repetitions. 2. Obtain the titles, categories and discount of all "Mathematics" books that were ordered and given a discount bigger than 5%. Avoid repetitions. 3. Obtain the First Name, Initials and Last Name of all authors which last name begins with the letter S' and whose books were ordered by at least one customer. Avoid repetition:s 4. Obtain the First Name, Initials and Last Name of all second authors whose books were ordered by customers. 5. Obtain the average price before discount of all books that were delivered after May 5, 2016 6. Obtain the minimum, maximum and average price of all ordered books per category Use prices before discounts. Sort the list in descending order of average price. Do not include average prices smaller than S20.00 . Obtain the ranking of all categories of books by the total quantity that was ordered, from the most ordered to the least ordered. 8. Obtain the total of all discounted prices of all books ordered by the Customer "Jerry Seinfeld". Remember that if the percentage of discount is 1, the total discounted price is 0.99 the original price. 9. Obtain the book titles, the last name of their first authors, and the sum of all copies of the book sold. Include only the titles with more than 5 copies ordered by customers in total. Sort the list in descending number of books sold. 10. Obtain the ISBN, title and author last names of all books that were not ordered ever

Step by Step Solution

There are 3 Steps involved in it

Step: 1

blur-text-image

Get Instant Access to Expert-Tailored Solutions

See step-by-step solutions with expert insights and AI powered tools for academic success

Step: 2

blur-text-image

Step: 3

blur-text-image

Ace Your Homework with AI

Get the answers you need in no time with our AI-driven, step-by-step assistance

Get Started

Recommended Textbook for

Introduction To Constraint Databases

Authors: Peter Revesz

1st Edition

1441931554, 978-1441931559

Students also viewed these Databases questions

Question

b. What groups were most represented? Why do you think this is so?

Answered: 1 week ago

Question

3. Describe phases of minority identity development.

Answered: 1 week ago

Question

5. Identify and describe nine social and cultural identities.

Answered: 1 week ago