Homework Help is Here – Start Your Trial Now!
Engineering
Computer Science
1. What are all the months that retail orders were made but we only want the first 3 letters of each month. 2. What are the warehouse cities where the size is greater than 130,000 square feet. In your output ensure the results are all in uppercase and please rename the warehouse cities to "CapitalCity". 3. Return the number of rows in the 2013 catalog that have page numbers in them.

1. What are all the months that retail orders were made but we only want the first 3 letters of each month. 2. What are the warehouse cities where the size is greater than 130,000 square feet. In your output ensure the results are all in uppercase and please rename the warehouse cities to "CapitalCity". 3. Return the number of rows in the 2013 catalog that have page numbers in them.

Database System Concepts
Database System Concepts
7th Edition
ISBN:9780078022159
Author:Abraham Silberschatz Professor, Henry F. Korth, S. Sudarshan
Publisher:Abraham Silberschatz Professor, Henry F. Korth, S. Sudarshan
Chapter1: Introduction
Section: Chapter Questions
Problem 1PE
See similar textbooks
icon
Related questions
Question

Can someone please help me with my SQL practice?

CATALOG SKU 2013 INVENTORY ORDER_ITEM Catalod- SKU - SKU_Description - QuantityOnH- QuantityOnO- CatalogP- DateOnWeb- 1/1/2013 Warehous SKU - SKU_Description 100100 Std. Souba Tank, Yellow 100200 Std. Souba Tank, Magenta 101100 Dive Mask, Small Clear 101200 Dive Mask, Med Clear OrderNumt - SKU Quantity - ExtendedP - Department Water Sports 100500 Std. Souba Tank, Light Green Water Sports 20130003 100600 Std. Scuba Tank, Dark Green Water Sports Water Sports Water Sports Camping Camping Climbing Climbing - Price 20130001 100100 Std. Scuba Tank, Yellow 23 100 250 3000 100200 1 300 300 20130002 NULL 2013ורו? 100 200 30 2000 101100 4 50 200 NULL 7/1/2013 100 500 3000 101100 2 50 100 20130004 101100 Dive Mask, Small Clear 24 1/1/2013 100 100 500 2000 101200 50 100 20130005 101200 Dive Mask, Med Clear 24 1/1/2013 100 201000 Half-dome Tent 2 100 3000 101200 1 50 50 201000 202000 20130006 201000 Half-dome Tent 45 1/1/2013 100 202000 Half-dome Tent Vestibule 10 250 1000 1 300 300 100 301000 Light Fly Climbing Harness 302000 Locking Carabiner, Oval 100100 Std. Souba Tank, Yellow 100200 Std. Souba Tank, Magenta 20130007 202000 Half-dome Tent Vestibule 47 1/1/2013 300 250 1000 1 130 130 1/1/2013 1/1/2013 20130008 301000 Light Fly Climbing Harness 20130009 302000 Locking Carabiner, Oval 76 100 1000 78 200 100 50 RETAIL_ORDER OrderNumt- StoreNumber - Storezip - OrderMonth 98110 December OrderYear -OrderTo- 2014 200 75 75 CATALOG SKU 2014 200 101100 Dive Mask, Small Clear 500 1000 10 445 Catalod- SKU - SKU_Description - Department Water Sports CatalogP- DateOnWeb- 1/1/2014 200 101200 Dive Mask, Med Clear 50 500 2000 20 2335 December 2014 310 20140001 100100 Std. Scuba Tank, Yellow 23 200 201000 Half-dome Tent 10 250 3000 10 98110 January 2015 480 20140002 100300 Std. Scuba Tank, Light Blue 20140003 100400 Std. Scuba Tank, Dark Blue 101100 Dive Mask, Small Clear Water Sports 23 1/1/2014 200 202000 Half-dome Tent Vestibule 1 250 Water Sports Water Sports NULL 8/1/2014 200 301000 Light Fly Climbing Harness 302000 Locking Carabiner, Oval 100100 Std. Scuba Tank, Yellow 100200 Std. Souba Tank, Magenta 250 250 SKU_DATA 20140004 26 1/1/2014 200 1250 SKU - SKU Description Department Buyer Water Sports Camping 100100 Std. Scuba Tank, Yellow 100200 Std. Souba Tank, Magenta Water Sports Water Sports Water Sports Water Sports Cаmping Сamping Climbing Climbing 20140005 101200 Dive Mask, Med Clear 26 1/1/2014 300 100 Pete Hansen Pete Hansen Nancy Meyers Nancy Meyers Cindy Lo Cindy Lo Jerry Martin 20140006 201000 Half-dome Tent 46 1/1/2014 300 100 100 20140007 202000 Half-dome Tent Vestibule Camping 46 1/1/2014 300 101100 Dive Mask, Small Clear 101200 Dive Mask, Med Clear 300 200 101100 Dive Mask, Small Clear 20140008 301000 Light Fly Climbing Harness Climbing Climbing 77 1/1/2014 300 475 101200 Dive Mask, Med Clear 201000 Half-dome Tent 20140009 302000 Locking Carabiner, Oval 79 1/1/2014, 300 201000 Half-dome Tent 250 300 202000 Half-dome Tent Vestibule 100 202000 Half-dome Tent Vestibule CATALOG SKU 2015 300 301000 Light Fly Climbing Harness 302000 Locking Carabiner, Oval 100100 Std. Scuba Tank, Yellow 100200 Std. Scuba Tank, Magenta 250 Catalor- SKU - SKU_Description 301000 Light Fly Climbing Harness 302000 Locking Carabiner, Oval - Department Water Sports Water Sports Water Sports Water Sports CatalogP- DateOnWeb:- 300 500 500 Jerry Martin 20150001 100100 Std. Scuba Tank, Yellow 23 1/1/2015 400 200 100200 Std. Souba Tank, Magenta WAREHOUSE WarehouseSta Manager 20150002 23 1/1/2015 400 250 Varehouse- WarehouseCity 100 Atlanta 200 Chicago 300 Bangor SquareFee 125000 20150003 101100 Dive Mask, Small Clear 27 1/1/2015 400 101100 Dive Mask, Small Clear 450 20150004 101200 Dive Mask, Med Clear 27 1/1/2015 400 101200 Dive Mask, Med Clear 250 250 GA Dave Jones 201000 Half-dome Tent Camping Camping 20150007 203000 Half-dome Tent Vestibule - Wic Camping Climbing 45 1/1/2015 400 201000 Half-dome Tent 250 IL Lucille Smith 100000 20150006 202000 Half-dome Tent Vestibule 45 1/1/2015 400 202000 Half-dome Tent Vestibule 200 ME Bart Evans 150000 400 301000 Light Fly Climbing Harness Dale Rogers Grace Jefferson NULL 4/1/2015 250 400 Seattle WA 130000 20150008 301000 Light Fly Climbing Harness 500 San Francisco 200000 76 1/1/2015 400 302000 Locking Carabiner, Oval 1000 CA
Transcribed Image Text:CATALOG SKU 2013 INVENTORY ORDER_ITEM Catalod- SKU - SKU_Description - QuantityOnH- QuantityOnO- CatalogP- DateOnWeb- 1/1/2013 Warehous SKU - SKU_Description 100100 Std. Souba Tank, Yellow 100200 Std. Souba Tank, Magenta 101100 Dive Mask, Small Clear 101200 Dive Mask, Med Clear OrderNumt - SKU Quantity - ExtendedP - Department Water Sports 100500 Std. Souba Tank, Light Green Water Sports 20130003 100600 Std. Scuba Tank, Dark Green Water Sports Water Sports Water Sports Camping Camping Climbing Climbing - Price 20130001 100100 Std. Scuba Tank, Yellow 23 100 250 3000 100200 1 300 300 20130002 NULL 2013ורו? 100 200 30 2000 101100 4 50 200 NULL 7/1/2013 100 500 3000 101100 2 50 100 20130004 101100 Dive Mask, Small Clear 24 1/1/2013 100 100 500 2000 101200 50 100 20130005 101200 Dive Mask, Med Clear 24 1/1/2013 100 201000 Half-dome Tent 2 100 3000 101200 1 50 50 201000 202000 20130006 201000 Half-dome Tent 45 1/1/2013 100 202000 Half-dome Tent Vestibule 10 250 1000 1 300 300 100 301000 Light Fly Climbing Harness 302000 Locking Carabiner, Oval 100100 Std. Souba Tank, Yellow 100200 Std. Souba Tank, Magenta 20130007 202000 Half-dome Tent Vestibule 47 1/1/2013 300 250 1000 1 130 130 1/1/2013 1/1/2013 20130008 301000 Light Fly Climbing Harness 20130009 302000 Locking Carabiner, Oval 76 100 1000 78 200 100 50 RETAIL_ORDER OrderNumt- StoreNumber - Storezip - OrderMonth 98110 December OrderYear -OrderTo- 2014 200 75 75 CATALOG SKU 2014 200 101100 Dive Mask, Small Clear 500 1000 10 445 Catalod- SKU - SKU_Description - Department Water Sports CatalogP- DateOnWeb- 1/1/2014 200 101200 Dive Mask, Med Clear 50 500 2000 20 2335 December 2014 310 20140001 100100 Std. Scuba Tank, Yellow 23 200 201000 Half-dome Tent 10 250 3000 10 98110 January 2015 480 20140002 100300 Std. Scuba Tank, Light Blue 20140003 100400 Std. Scuba Tank, Dark Blue 101100 Dive Mask, Small Clear Water Sports 23 1/1/2014 200 202000 Half-dome Tent Vestibule 1 250 Water Sports Water Sports NULL 8/1/2014 200 301000 Light Fly Climbing Harness 302000 Locking Carabiner, Oval 100100 Std. Scuba Tank, Yellow 100200 Std. Souba Tank, Magenta 250 250 SKU_DATA 20140004 26 1/1/2014 200 1250 SKU - SKU Description Department Buyer Water Sports Camping 100100 Std. Scuba Tank, Yellow 100200 Std. Souba Tank, Magenta Water Sports Water Sports Water Sports Water Sports Cаmping Сamping Climbing Climbing 20140005 101200 Dive Mask, Med Clear 26 1/1/2014 300 100 Pete Hansen Pete Hansen Nancy Meyers Nancy Meyers Cindy Lo Cindy Lo Jerry Martin 20140006 201000 Half-dome Tent 46 1/1/2014 300 100 100 20140007 202000 Half-dome Tent Vestibule Camping 46 1/1/2014 300 101100 Dive Mask, Small Clear 101200 Dive Mask, Med Clear 300 200 101100 Dive Mask, Small Clear 20140008 301000 Light Fly Climbing Harness Climbing Climbing 77 1/1/2014 300 475 101200 Dive Mask, Med Clear 201000 Half-dome Tent 20140009 302000 Locking Carabiner, Oval 79 1/1/2014, 300 201000 Half-dome Tent 250 300 202000 Half-dome Tent Vestibule 100 202000 Half-dome Tent Vestibule CATALOG SKU 2015 300 301000 Light Fly Climbing Harness 302000 Locking Carabiner, Oval 100100 Std. Scuba Tank, Yellow 100200 Std. Scuba Tank, Magenta 250 Catalor- SKU - SKU_Description 301000 Light Fly Climbing Harness 302000 Locking Carabiner, Oval - Department Water Sports Water Sports Water Sports Water Sports CatalogP- DateOnWeb:- 300 500 500 Jerry Martin 20150001 100100 Std. Scuba Tank, Yellow 23 1/1/2015 400 200 100200 Std. Souba Tank, Magenta WAREHOUSE WarehouseSta Manager 20150002 23 1/1/2015 400 250 Varehouse- WarehouseCity 100 Atlanta 200 Chicago 300 Bangor SquareFee 125000 20150003 101100 Dive Mask, Small Clear 27 1/1/2015 400 101100 Dive Mask, Small Clear 450 20150004 101200 Dive Mask, Med Clear 27 1/1/2015 400 101200 Dive Mask, Med Clear 250 250 GA Dave Jones 201000 Half-dome Tent Camping Camping 20150007 203000 Half-dome Tent Vestibule - Wic Camping Climbing 45 1/1/2015 400 201000 Half-dome Tent 250 IL Lucille Smith 100000 20150006 202000 Half-dome Tent Vestibule 45 1/1/2015 400 202000 Half-dome Tent Vestibule 200 ME Bart Evans 150000 400 301000 Light Fly Climbing Harness Dale Rogers Grace Jefferson NULL 4/1/2015 250 400 Seattle WA 130000 20150008 301000 Light Fly Climbing Harness 500 San Francisco 200000 76 1/1/2015 400 302000 Locking Carabiner, Oval 1000 CA
1. What are all the months that retail orders were made but we only want the first 3 letters of each month. 2. What are the warehouse cities where the size is greater than 130,000 square feet. In your output ensure the results are all in uppercase and please rename the warehouse cities to "CapitalCity". 3. Return the number of rows in the 2013 catalog that have page numbers in them. 4. What is the total of extended prices from our orders? 5. Return the average of the Extended Prices from our order items if the Price is over 100. 6. What are the max prices and minimum prices from our orders? Rename the output as MaxPrice and MinPrice. 7. We need to get a table of the warehouse IDs and their respective cities and states. Please provide the cities and states in the following format: (City), (State) such as "Atlanta, GA". Have the output labeled Location and be sure the results are in order based on the warehouse ID. 8. Return a concatenated string that looks like this form: "We have 200 of the Std. Scuba Tank, Magenta in Warehouse 100." From our inventory. The goal here is to have output that returns a row for each row in the table that creates sentences in the format of: "We have (quantity on hand) of the (sku description) in Warehouse (warehouse id)" So the first few lines will be like: "We have 250 of the Std. Scuba Tank, Yellow in Warehouse 100" "We have 200 of the Std. Scuba Tank, Magenta in Warehouse 100" "We have O of the Dive Mask, Small Clear in Warehouse 100" 9. Return only the current year by using a SQL query. 10. Return what the date would look like one month from now using a SQL query.
Transcribed Image Text:1. What are all the months that retail orders were made but we only want the first 3 letters of each month. 2. What are the warehouse cities where the size is greater than 130,000 square feet. In your output ensure the results are all in uppercase and please rename the warehouse cities to "CapitalCity". 3. Return the number of rows in the 2013 catalog that have page numbers in them. 4. What is the total of extended prices from our orders? 5. Return the average of the Extended Prices from our order items if the Price is over 100. 6. What are the max prices and minimum prices from our orders? Rename the output as MaxPrice and MinPrice. 7. We need to get a table of the warehouse IDs and their respective cities and states. Please provide the cities and states in the following format: (City), (State) such as "Atlanta, GA". Have the output labeled Location and be sure the results are in order based on the warehouse ID. 8. Return a concatenated string that looks like this form: "We have 200 of the Std. Scuba Tank, Magenta in Warehouse 100." From our inventory. The goal here is to have output that returns a row for each row in the table that creates sentences in the format of: "We have (quantity on hand) of the (sku description) in Warehouse (warehouse id)" So the first few lines will be like: "We have 250 of the Std. Scuba Tank, Yellow in Warehouse 100" "We have 200 of the Std. Scuba Tank, Magenta in Warehouse 100" "We have O of the Dive Mask, Small Clear in Warehouse 100" 9. Return only the current year by using a SQL query. 10. Return what the date would look like one month from now using a SQL query.
Expert Solution
trending now

Trending now

This is a popular solution!

steps

Step by step

Solved in 3 steps

SEE SOLUTIONCheck out a sample Q&A here
Blurred answer
Knowledge Booster
Table
Learn more about
Need a deep-dive on the concept behind this application? Look no further. Learn more about this topic, computer-science and related others by exploring similar questions and additional content below.
Recommended textbooks for you
Database System Concepts
Database System Concepts
Computer Science
ISBN:
9780078022159
Author:
Abraham Silberschatz Professor, Henry F. Korth, S. Sudarshan
Publisher:
McGraw-Hill Education
Starting Out with Python (4th Edition)
Starting Out with Python (4th Edition)
Computer Science
ISBN:
9780134444321
Author:
Tony Gaddis
Publisher:
PEARSON
Digital Fundamentals (11th Edition)
Digital Fundamentals (11th Edition)
Computer Science
ISBN:
9780132737968
Author:
Thomas L. Floyd
Publisher:
PEARSON
C How to Program (8th Edition)
C How to Program (8th Edition)
Computer Science
ISBN:
9780133976892
Author:
Paul J. Deitel, Harvey Deitel
Publisher:
PEARSON
Database Systems: Design, Implementation, & Manag…
Database Systems: Design, Implementation, & Manag…
Computer Science
ISBN:
9781337627900
Author:
Carlos Coronel, Steven Morris
Publisher:
Cengage Learning
Programmable Logic Controllers
Programmable Logic Controllers
Computer Science
ISBN:
9780073373843
Author:
Frank D. Petruzella
Publisher:
McGraw-Hill Education
SEE MORE TEXTBOOKS