[Solved]Question 1 Sql Entries Download Database Bookstoremdb Database Keeps Track Inventory Books Q37121097
Question 1. SQL Entries
Download the database bookstore.mdb. This database keeps trackof the inventory of books, orders, and customers on a bookstore.For each of the queries specified below,
1. Show the isbn, title, author, and publication year of thebooks published by the publisher Doubleday. Sort the books bypublication year from smallest to largest.
2. Mark Twain published books with several different publishers.For each such publisher, show the number of books authored by MarkTwain. The query should show the publisher name and correspondingnumber of such books.
3. Show the orders that have not yet been filled (order_filledis of Yes/No type, to compare simply do order_filled = Yes ororder_filled=No), together with their customer information. Thequery should show the order number, date, the customer ID, andcustomer’s first and last names. Sort the orders by date inascending order.
4. For each customer from New Jersey show the first name, lastname, and total amount spent in books at the bookstore. Show onlythe customers that have spent at least $75 in books. Sort theresults by amount spent in books from largest to smallest.
5. We want to know the books that sell the most. For each book,show the title, author’s name and the total number of copies sold.Sort the results by number of books, with the largest number ofbooks appearing first. We are only interested in seeing the booksthat sold at least 5 copies. Note that more than one book may bebought in each order (see the field quantity in OrderDetails).
6. For each order placed on or after September 1st 2016, showthe order number, order date, and total revenue corresponding tothat order.
7. Show the list of books ordered on or after August 1 st 2016.The query should show the book isbn, title, and author. Make sureeach book shows only once.
Books All Access Obje O « Quer uery2 p Querp3 Query 4gQueyS1 – publication ye- retail_price number on_hand Click to Add Celebrated Jumping Frog of Calaveras Coun Harper t0-124-2999-9 Twain, Mark 0-124-5544-X Dumas, Alexandre 0-124-7989-1 Twain, Mark 0-125-3344-1 Dumas, Alexandre 0-126-3367-2 Dumas, Alexandre P. F. Collier & Son Platt & Munk Black Tulip, The Count of Monte Cristo, The t 0-127-3948-2 Dumas, Alexandre Clavell, James McCaffrey, Anne 0-128-3939-2 Clavell, James E 0-128-3939-X 0-128-4321-1 Clavell, James t 0-129-4567-1 0-129-4912-0 McCaffrey, Anne 0-129-9293-2 Lee, Tanith 0-129-9876-5 Clavell, James Dragonsinger t 0-130-2939-4 McCaffrey, Anne Ballantine Books Ballantine Books Ballantine Books Ballantine Books Dragonflight Feast of All Saints, The 0-130-2943-2 McCaffrey, Anne 0-130-3941-7 Rice, Anne 0-130-9483-1 McCaffrey, Anne t 0-131-1458-9Rice, Anne Interview with the Vampire Tale of the Body Thief, The Vampire Lestat, The 0-131-3021-2 Rice, Anne 0-131-4809-X Rice, Anne 0-131-4912-X Rice, Anne 0-131-4966-9 Rice, Anne t0-131-9456-2 Rice, Anne Cry to Heaven Prince and the Pauper, The Life on the Mississippi James R. Osgood and Co. James R. Osgood and Co. American Publishing Co. 0-132-3949-2 Twain, Mark E 0-132-9876-4 Twain, Mark 0-133-2956-6 Twain, Mark 0-133-5935-2 Twain, Mark Pudd’nhead Wilson t 0-134-3945-7 Stevenson, Robert Loui: Child’s Garden of Verses, A 0-135-2222-2 Stevenson, Robert Loui: Treasure Island 0-136-3956-1 Stevenson, Robert Loui: Kidnapped 0-136-3966-7 Stevenson, Robert Loui: Strange Case of Dr. Jekyll and Mr. HydeDodd, Mead J. W. Lovell Co. H0-137-1293-9 Scott, Sir walter Scott, Sir Walter Scott, Sir Walter 0-138-1379-8 Hart Publishing Co. University of Nebraska Pr E 0-140-3877-0 0-141-9876-4 Bronte, Emily Complete Poems of Emily Jane Bronte, The Columbia University Pres t 0-142-0084-2 Butler, Octavia St. Martin’s Press St. Martin’s Press 0-142-3867-8 Bronte, Emily 0-142-3988-2 Lee, Tanith 0-150-3765-2 Ludlum, Robert Wuthering Heights East of Midnight Aquitaine Progression, The Query eqtp QueySE ostomers last name All Access Obie О Queryl Query2 Query, first name -Click to Add customer ID . state Tables 125 W. 88th Blvd. 4592 Maple Lane Anytown Some City tom smith@this.net Customers e.net OrderDetails 967 Apple Tree Road Anytown emily_jones@somewhere.net er johnsoneanywhere.net edna hayes@this.net mary collins@rural.net peter_collins@anywhere.net anne smith@rural.net peter smith@somewhere.net jerry brown@somewhere.net helen brown@somewhere.net 170 Dogwood Lane Little Town 9745 Main Street Anytown Some City 2588 North Road Query21匣Query3 曱Query4(匣Query5ー困Customers E OrderDetails Query1QuenyeryEE Customers OrderDetails Queryl order numb All Access Obje « Click to Add shippe 1Y 1 Y 1Y quantity isbn 1 0-123-1233-0 1 0-129-4912-0 1 0-136-3966-7 2 0-131-4912-х 2 0-159-3845-3 3 0-131-4966-9 3 0-150-5948-9 3 0-180-4712-X 3 0-191-4934-8 4 0-140-3877-0 5 0-124-2999-9 5 0-140-3877-0 5 0-180-7388-1 6 0-132-3949-2 6 0-142-0084-2 Tables Books Customers EEE OrderDetails Orders 1 Y 1 Y 2 Y 1Y 2 Y 1 Y Quer 3( OrderDetails YT orders Querys i Customers Query! Query2 Que 4 All Access Obie «. order numk키 customer numb . order date-t credit card numb order filled ·credit card exp date· 7/1/2017 7/1/2017 11/1/2016 11/1/2018 11/1/2016 1/2/2017 1/2/2017 4/1/2017 4/1/2017 5/1/2018 12/1/2016 12/1/2018 12/1/2018 12/2/2017 12/2/2017 11/2/2017 11/2/2017 11/2/2017 12/1/2019 10/2/2017 Click to Add · 1 1/5/2016 123 123 123 123 1 7/6/2016 123 123 123 123 2 11/12/2016 234 234 234 234 2 3/18/2016 234 234 234 234 2 7/6/2016 234 234 234 234 3 8/15/2016 345 345 345 345 3 1/2/2016 345 345 345 345 4 1/22/2016 456 456 456 456 1/6/2016 456 456 456 456 5 3/12/2016 567 567 567 567 6 9/19/2016 678 678 678 678 6 3/12/2016 678 678 678 678 6 7/21/2016 678 678 678 678 7 1/13/2016 789 789 789 789 7 1/9/2016 789 789 789 789 8 10/12/2016 890 890 890 890 8 2/22/2016 890 890 890 890 8 5/13/2016 890 890 890 890 97/15/2016 901901 901 901 10 11/15/2016 1000 1000 1000 Books Customers OrderDetails Orders Show transcribed image text Books All Access Obje O « Quer uery2 p Querp3 Query 4gQueyS1 – publication ye- retail_price number on_hand Click to Add Celebrated Jumping Frog of Calaveras Coun Harper t0-124-2999-9 Twain, Mark 0-124-5544-X Dumas, Alexandre 0-124-7989-1 Twain, Mark 0-125-3344-1 Dumas, Alexandre 0-126-3367-2 Dumas, Alexandre P. F. Collier & Son Platt & Munk Black Tulip, The Count of Monte Cristo, The t 0-127-3948-2 Dumas, Alexandre Clavell, James McCaffrey, Anne 0-128-3939-2 Clavell, James E 0-128-3939-X 0-128-4321-1 Clavell, James t 0-129-4567-1 0-129-4912-0 McCaffrey, Anne 0-129-9293-2 Lee, Tanith 0-129-9876-5 Clavell, James Dragonsinger t 0-130-2939-4 McCaffrey, Anne Ballantine Books Ballantine Books Ballantine Books Ballantine Books Dragonflight Feast of All Saints, The 0-130-2943-2 McCaffrey, Anne 0-130-3941-7 Rice, Anne 0-130-9483-1 McCaffrey, Anne t 0-131-1458-9Rice, Anne Interview with the Vampire Tale of the Body Thief, The Vampire Lestat, The 0-131-3021-2 Rice, Anne 0-131-4809-X Rice, Anne 0-131-4912-X Rice, Anne 0-131-4966-9 Rice, Anne t0-131-9456-2 Rice, Anne Cry to Heaven Prince and the Pauper, The Life on the Mississippi James R. Osgood and Co. James R. Osgood and Co. American Publishing Co. 0-132-3949-2 Twain, Mark E 0-132-9876-4 Twain, Mark 0-133-2956-6 Twain, Mark 0-133-5935-2 Twain, Mark Pudd’nhead Wilson t 0-134-3945-7 Stevenson, Robert Loui: Child’s Garden of Verses, A 0-135-2222-2 Stevenson, Robert Loui: Treasure Island 0-136-3956-1 Stevenson, Robert Loui: Kidnapped 0-136-3966-7 Stevenson, Robert Loui: Strange Case of Dr. Jekyll and Mr. HydeDodd, Mead J. W. Lovell Co. H0-137-1293-9 Scott, Sir walter Scott, Sir Walter Scott, Sir Walter 0-138-1379-8 Hart Publishing Co. University of Nebraska Pr E 0-140-3877-0 0-141-9876-4 Bronte, Emily Complete Poems of Emily Jane Bronte, The Columbia University Pres t 0-142-0084-2 Butler, Octavia St. Martin’s Press St. Martin’s Press 0-142-3867-8 Bronte, Emily 0-142-3988-2 Lee, Tanith 0-150-3765-2 Ludlum, Robert Wuthering Heights East of Midnight Aquitaine Progression, The
Query eqtp QueySE ostomers last name All Access Obie О Queryl Query2 Query, first name -Click to Add customer ID . state Tables 125 W. 88th Blvd. 4592 Maple Lane Anytown Some City tom smith@this.net Customers e.net OrderDetails 967 Apple Tree Road Anytown emily_jones@somewhere.net er johnsoneanywhere.net edna hayes@this.net mary collins@rural.net peter_collins@anywhere.net anne smith@rural.net peter smith@somewhere.net jerry brown@somewhere.net helen brown@somewhere.net 170 Dogwood Lane Little Town 9745 Main Street Anytown Some City 2588 North Road
Query21匣Query3 曱Query4(匣Query5ー困Customers E OrderDetails Query1QuenyeryEE Customers OrderDetails Queryl order numb All Access Obje « Click to Add shippe 1Y 1 Y 1Y quantity isbn 1 0-123-1233-0 1 0-129-4912-0 1 0-136-3966-7 2 0-131-4912-х 2 0-159-3845-3 3 0-131-4966-9 3 0-150-5948-9 3 0-180-4712-X 3 0-191-4934-8 4 0-140-3877-0 5 0-124-2999-9 5 0-140-3877-0 5 0-180-7388-1 6 0-132-3949-2 6 0-142-0084-2 Tables Books Customers EEE OrderDetails Orders 1 Y 1 Y 2 Y 1Y 2 Y 1 Y
Quer 3( OrderDetails YT orders Querys i Customers Query! Query2 Que 4 All Access Obie «. order numk키 customer numb . order date-t credit card numb order filled ·credit card exp date· 7/1/2017 7/1/2017 11/1/2016 11/1/2018 11/1/2016 1/2/2017 1/2/2017 4/1/2017 4/1/2017 5/1/2018 12/1/2016 12/1/2018 12/1/2018 12/2/2017 12/2/2017 11/2/2017 11/2/2017 11/2/2017 12/1/2019 10/2/2017 Click to Add · 1 1/5/2016 123 123 123 123 1 7/6/2016 123 123 123 123 2 11/12/2016 234 234 234 234 2 3/18/2016 234 234 234 234 2 7/6/2016 234 234 234 234 3 8/15/2016 345 345 345 345 3 1/2/2016 345 345 345 345 4 1/22/2016 456 456 456 456 1/6/2016 456 456 456 456 5 3/12/2016 567 567 567 567 6 9/19/2016 678 678 678 678 6 3/12/2016 678 678 678 678 6 7/21/2016 678 678 678 678 7 1/13/2016 789 789 789 789 7 1/9/2016 789 789 789 789 8 10/12/2016 890 890 890 890 8 2/22/2016 890 890 890 890 8 5/13/2016 890 890 890 890 97/15/2016 901901 901 901 10 11/15/2016 1000 1000 1000 Books Customers OrderDetails Orders
Expert Answer
Answer to Question 1. SQL Entries Download the database bookstore.mdb. This database keeps track of the inventory of books, orders… . . .
OR
