CIT 111: Introduction to Databases

Problem Solving

Case Study

CASE STUDY � Apply the principles of problem solving to help create effective queries.

SQL syntax and function syntax can always be looked up. What you cant look up is how exactly to create unique queries. No amount of Googling is going to help you put together a query to get the unique data that your company needs. What will help is good problem-solving skills.

This is the point in the class where query creation can start to get challenging. You can look up different functions and what they do and what parameters you need and in what order, but knowing how to use these functions inside a complex query, can be challenging.

MIT had a good page about problem solving which is where most of the information in this case study came from: http://web.mit.edu/be.400/www/problem.htm

This page states that "Your ability to solve problems will have a great impact on your success in this course as well as in your "real life" endeavors."

"The questions in this course will be different than the exercises to which you are accustomed. In exercise solving, you recall and apply routines that you have previously used to solve questions, but in problem solving you are usually unsure about how to proceed as well as about the connection between the given data and the goal."

Let's look at the steps we can take to become a better problem solver.

ENGAGE:

• Read the question aloud.
• Mentally prepare yourself to solve the problem and overcome the initial anxiety and panic you may be experiencing. Believe in your ability to solve the problem or in other words "psyche up".
• Learn to gain confidence in your abilities to solve questions by applying your problemsolving techniques frequently.

2DEFINE:

• Reread the problem again. Do not worry about spending a lot of time on rereading. Research has shown that successful problem solvers spend two to three times longer reading an initial problem statement than unsuccessful problem solvers.
• Look for clue words. A simple way to do this is to highlight the key phrases and THE KEY PHRASE directly on the question sheet. Cross out unimportant and irrelevant information. It may be helpful to divide the problem statement into smaller parts such as:
1. The stated objective, goal, decision to make or feature.
2. The situation, condition or context.
3. The constraints: on the inputs, on the solution and on the process we can use.
4. The criteria by which we will judge an acceptable answer.
• Analyze the given information and determine what needs to be solved (i.e. understand the problem).
• Visualize the problem (by constructing a drawing, table or graph).
• Interpret the problem in light of your own knowledge and experience.
• List the known and unknown variables.
• Use your own style to understand the question. The above are some "optional" activities that you may want to perform during this stage. People do not go through this stage the same way; some prefer to transform the problem into pictures while others prefer equations because they think better in those terms. Use your preferences from the above list. Remember that the main goal of this section is to classify the given information into different categories: the goal, the givens, the constraints and the criteria.

EXPLORE:

• In this stage you play around with the problem statement, background subject knowledge and experience. Try to discover the real problem without committing to a solution method.
• Identify and make reasonable assumptions within the specified constraints.
• Create mental images and identify the main goal (put the problem statement in the context of your own experiences and knowledge).
• Focus on discovering the mathematical process and determining the unit measure for expressing the answer.
• Estimate a ballpark answer prior to proceeding to actually solving the problem.
• Create a series of options or hypothesis of how to solve the problem elaborated more in the plan stage.
• Ask questions such as "what is the most important information? What am I trying to do? What does the question mean in the grand scheme of things? What is the simplest way of looking at the problem?"
• Remember to focus on what you NEED to learn rather than what you want to learn, and what issues NEED to be. You must learn to identify issues and define problems carefully. Unsuccessful problem solvers spend most of their time doing while successful problem solvers spend most of their time thinking of what to do. Therefore, be very careful and meticulous about defining your goals.

PLAN:

• Decide on a strategy and map out steps to be taken.
• Determine a method to monitor your progress.

DO IT:

• Follow the steps and be careful with all your order of magnitude calculations and signs.

EVALUATE:

• Interpret and evaluate the solution. Check your work and compare your answer with your estimate.
• Look back and decide what was done.
• Finally, check to see if you've answered the question asked.

If you learn to use this strategy effectively, you will find it a valuable tool to use for solving new and complex problems.

Practice

Let do a problem together as we go through the problem-solving steps.

We want to know how long its been since our customers orders have shipped. The length of time will be rounded to the nearest year.

1. So, you just read through it. Dont panic, realize you have all the skills you need to solve this. You just need to go through the problem-solving steps.
2. Re-read the problem and look for clues or keywords that might help.
   • Youre looking for a length of time between one date and another date.
   • That same length of time will need to be rounded.
   • We know about a function that rounds and one that finds the difference between two dates. Can we use those somehow?
   • We know the customer ship date already from the database as well. We also know what todays date is. Can these be parameters in a function?
3. Lets play around with one of the customer ship dates to see if what we are thinking might work.
   1. Lets get an estimate. We look at all the customer order dates in the table. The first customer order was way back in January 3rd 2016. So, we can see about how many years its been since then to get an estimate.
   2. Now what function would work for this? We learned a lot of date functions, one to format a date, one to add an interval of time to a date and one of get the difference between two dates. Which would work best?
   3. We dont know the interval of time we need yet, thats what we are trying to figure out. We want to get a number of years as a result not an actual date, so we wont be formatting a date (we will round the result number though). So DATEDIFF will probably be the right function to use.
4. We know the parameters for DATEDIFF are two dates and it always results in a difference in the number of days between those two dates. We also know that in order to get a positive number of days, we put the later date first. We also know we can get todays date with the NOW function. Lets try that and we can compare our estimate we got earlier for comparison. Also, remember we need years not days. What math would we need to make a number of days into years? Divide by 365 right? Then once we get the year division result it will probably have a decimal place that will need to be rounded up or down. We can then use a ROUND function for that.
5. Lets try some SQL:

   1. SELECT DATEDIFF(NOW(), shipped_date) FROM cust_order;

   2. The first number we get was 1709. If I divide that by 365 what do I get?

   3. SELECT DATEDIFF(NOW(), shipped_date)/365 FROM cust_order;

   4. Now to round it. We know that one function can be nested inside another function. And the nest function runs first and the outer function will then use the inner function as its parameter. The round function takes it parameter and rounds it.

   5. SELECT ROUND(DATEDIFF(NOW(), shipped_date)/365) FROM cust_order;

6. Does that new number look like my estimate? Yes? Then we are on the right track. No? then lets rethink our solution and try something else and go through problem-solving steps 4 and 5 again. At this point I might want to think about adding any other columns to clarify which order goes with what rounded year and maybe add an alias to describe the functions we ran.

   SELECT cust_order_id, 
     ROUND(DATEDIFF(NOW(), shipped_date)/365) AS 'Years since product shipped'
   FROM cust_order;

Try a couple more, it will be good practice for your homework.

(using bike)

• Show the shipped_date
• Add 2 months to each shipped_date
• Format that date so it takes the format of Month name, number day with comma and then a 4-digit year. For example, January 14, 2019

(using magazine)

• Find any string with 'Ma', then, we want to take from that word on and take if off the magazine name.
• What if we just wanted that 'Ma' string and beyond to show up?

Don't look at the solutions later in this activity until you have gone through the problem-solving steps first. Note that you may have been able to get the right results with different solutions and that is just fine, as long as it worked properly. But also note which was a more efficient solution.

Useful Links:

• Return to: Joining and Summarizing Data
• Go to: Week Index • Course Home • Canvas