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
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
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

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