Additional Information The OrderDate field in table Order stores dates formatted as TEXT. This means that only operation
Posted: Sat Feb 19, 2022 3:39 pm
Additional Information
The OrderDate field in table Order stores dates formatted as
TEXT. This means that only operations one would typically use on a
string can be performed on this field as part of an SQL query.
These strings are formatted to list the Month, Day, Year and time
of the order. Typical entries for values in this field would look
like the following three examples (Note the examples has quotation
marks that will not form part of the value stored in the
database":
"Jul 4 2012 12:00:00:000AM"
'May 15 2013 12:00:00:000AM"
"Nov 28 2014 12:00:00:000AM"
Your Task
Note the additional information above.
Business management is interested in determining who the
customers are that spends the most money in a specific year. They
specifically want to know which of our customers
spend more than 20000 during the year of
2014.
Write the SQL to return the sum of each customer's total amount
spend (call this result TotalSpend), as well as the Customer
LastName and FirstName for orders placed in the year 2014 only.
List only entries where the TotalSpend is more than 20000
Note: This question requires you to write
SQL syntax ONLY. Absolutely no python code should be used. Only the
SQL itself.
For example:
Result
TotalSpend FirstName LastName
---------- ---------- ----------
42806.25
Jose Pavarotti
42598.9
Roland Mendel
40526.99
Horst Kloss
24238.05
Mario Pontes
22796.34 Patricia
McKenna
21725.6
Paula Wilson
20204.95 Philip
Cramer
The OrderDate field in table Order stores dates formatted as
TEXT. This means that only operations one would typically use on a
string can be performed on this field as part of an SQL query.
These strings are formatted to list the Month, Day, Year and time
of the order. Typical entries for values in this field would look
like the following three examples (Note the examples has quotation
marks that will not form part of the value stored in the
database":
"Jul 4 2012 12:00:00:000AM"
'May 15 2013 12:00:00:000AM"
"Nov 28 2014 12:00:00:000AM"
Your Task
Note the additional information above.
Business management is interested in determining who the
customers are that spends the most money in a specific year. They
specifically want to know which of our customers
spend more than 20000 during the year of
2014.
Write the SQL to return the sum of each customer's total amount
spend (call this result TotalSpend), as well as the Customer
LastName and FirstName for orders placed in the year 2014 only.
List only entries where the TotalSpend is more than 20000
Note: This question requires you to write
SQL syntax ONLY. Absolutely no python code should be used. Only the
SQL itself.
For example:
Result
TotalSpend FirstName LastName
---------- ---------- ----------
42806.25
Jose Pavarotti
42598.9
Roland Mendel
40526.99
Horst Kloss
24238.05
Mario Pontes
22796.34 Patricia
McKenna
21725.6
Paula Wilson
20204.95 Philip
Cramer