adventureworks2012

adventureworks2012, adventureworks2012

hello in this last video in the sequel review series I willaddress some point related to real rules sequel Querreyriding number one point I would like to address is that it’s important to understand thescheme of the database you are clearing primarily this relatesto data types as well as the relationships that havebeen fined between two tables it is important to understand today’sattacks that have been used to define too feels and tables because we must be able toprovide proper literal values when required as well s understand which feels might be jointto which feels data types of courseconcludes things such as currency types any of toomany dates types the sequel Server 2012currently supports numeric values which might include boat in teacher s Wallace real valueswhich are still distinctly different from the current to give a pipe and somedidn’t you were less common types suggest geography and geometry relationships are important tounderstand because dose will form the basis for creatingjoints between tables in our queries most commonly the jointsthat we will define in our queries will follow therelationships that have been defined and to join keys will become the primaryin foreign keys that make up those relationships if adatabase diagram is not yet available for the database you’re working with you should consider creating one you cancreate a database diagram using the built-in segal ServerManagement Studio tool or you can exports to schema to tools such as Physyou or even more specialized CASE toolssecondly we to understand the question what isthe end-user looking for in terms of data do weproperly understand the terminology that theend-user is using such as well we are talking about atotal sales amount are there any discounts that need to beapplied should we be considering returns and so on what will theinformation be used for this may be important because it willhelp us free context and possibly help to userdevelop a more refined query than what might normally be obtainedit’s very important that if you have any questions about theconcepts or the purpose of the query to ask theend-user for clarification this will avoid either lots of goingback and forth between the end user and yourself or alternatively it will afford theend-user from using incorrect information for important purposes number three as youare developing complex queries you should consider following adisciplined approach to developing to query step-by-step first to be my to develop aquery that has for surely no or few filters in other words restricting the WHEREclause to the absolute minimum number of conditions that we can a forced to use secondly we may at joins paying particularattention of course to join key switch again in many circumstances will relates tothe primary and 40s used to define the relationships betweenour peoples third we will go and refine the WHERE clauseto make sure that we filter out any roasted or not need it in afour-step we may perform aggregation and groupingand possibly even further filtering

adventureworks2012we may perform aggregation and groupingand possibly even further filtering using the having clause and finally andonly if needed we will apply a sort or let’s look at anexample a user might require you to find to customers in the United States thathave ordered at least then men’s sports shorts in any size in the previous 12 months if we consider the question that the user has asked usto answer using the AdventureWorks 2012 databasewe might find that we do not have sufficient information when we asked for some clarification wemight obtained for example the specific columns that the end user is expectingto see so to query might change to finding thecustomers in the United States that have ordered at least 10 minutessports shorts in any size and the previous 12 monthsand we need to list their customer ID title first name last name and the totalnumber of shorts ordered. Let’s work through this example step-by-step superiors a plan query it’s what we do connect its twodifferent works 2012 database instead one we are good to go really wit few first as we are expectsits list primary customer information you the fists that first AdventureWorks 2012 the base is veryhighly localized this unfortunately alreadyrequire sis you know first step to provide each orcalls we will be able to select the coast moreI from part of the birthday table whichisn’t seals schema this case and however the customer table we do not find information such us firstname that’s the and final that information isfound in the person so people who shortly first work to meet the person Inc which isn’t course keep and we are going to join the businessand that the worst it’ll to good person the column the just like the this does not sound particularly natural dispersal sessions that will be marc Dorcel looks differentwatched sent the bases now we can conclude funk first look masking columns then executes this clip take a brief look at theofficial results this next satisfactory this is the partwith this that were your it’s you ok with you ask were of shorts their orders in a laterstage then the next stage I’m going to whysome filters basic still too would include visitingare crossed results to only those customers that its in the United States your first todo that the once again store want some shorts thecustomer table things the fall you territory are thewhich were first to the sales territory the sales scheme so we go dress this joint 6 think hooch or sales territory it its sleep Jory territory goals ok

adventureworks2012Jory territory goals ok now sales territory you fortunes was nota good thing the of the country it’s been since thecountry reach cool me country is found in country each you which sport ofpersons it so we’ll be procured com its as well using the country each Google its keep November that being able to rights thesejoints goals understanding let’s keep I’m def use the already look the company’sdiagram for that works we talk they took the standspent different peoples or all as well as howthey are leads to each other now wanted rightsMarie first where addition where i’m looking for. country or reach the to text United States it and the round you re indicates thatthere the equals to the group’s first migrate toit of course a very important secretarycriteria is we’re leaders to having actuallywaterworks the sports shorts sport shorts is a go next that would have been wordsthrough its sales or he said all sorts of consists of to tables sales or better the sales or the two so we will two-week go to States notice that on purpose with the to aclient which is certain to be want differentjordan’s were legal to chill wit so we’re going to short sales were otherusing first repeat gorged sales Google the table ok using the sales order right ok it in addition to also be to find products using that the of profits which will winery to showing one more time to see product people Road stable storm reception scoop thisparticular tape it Google used for 30 past shorty between the sales or detail to Roberts the you know third step refining we rgirls and go to pam has you additional columns than needs to create conditions work myconditions balls making sure that the water is least with the best well works so I’mgoing to create the conditions uses worth it I might do something thatrequires more or dates to be greater than they were so the friction uses the at status and I think 12 months pools track 12 months you that’s if I subtract 12 months from the state’s check the get the function and going back 12 months it with the is greater and that states that use that word waspleased with that last well months know if we execute thisclearly specifically first the fields months explores I will and the with zero results this is an unfortunateside effect

adventureworks2012 with zero results this is an unfortunateside effect

adventureworks2012with zero results this is an unfortunateside effect use if that works 2012 the these which things what’s up roasted malts thefortunately it does not get the from goods I’m units so I’m going to change thisfunction grants tracks 12 months from today instead succinct well months from to first 2006 just to make sure that somersaults to compensate for this extruded yourtrading results we did previous 12 months from ok towork 2006 I’m want to put a second goals that requires my order its to be this then cooked through first of 2006 note that stoop modifications picturesthe or sickly you require it goes really we database that the small the birdsproblems problems the torso use all XP to clearit now once again Coast or results the second or my finding of the year wheels falsecleary forty proper courts you know that thecroats be is ments watch shorts so I’m going touse this query which is cool to select from coax the Eagle weird the its likes wash shorts just to get tomake the all folks for its that on it so I can see that my Vince sport shortsthe ok bins sports shorts in a coma and then a particle science movie March orextra-large nada might be different ways handlingthis warm I could find want the peace using thismessage first and then this the mall individually inthe we’re close prepares that you repairs over it or accurate see the all its number which certainly looksvery said we’re chip these sport shorts andthen the simple while score it again or I can use while score all what’s the your specific requirements the very ourcase the best choice might be to query based own products the this look I will do so I’m going to seethat are equal all its name should be like it dance sports shorts home space in that world court you know these based on since that time I think thatsomething useful work right because I have a single who you actual ritual value they don’t you what sequelcircle will troops this single quote as the and the whenwith who well so it’s actually treating my query as you well it’s the like and then whatever comes actual that to youthat’s for sequence these which of course don’t make sense withyour facebook Pro like this who she divorced some orpurpose AP you pick a spot is keeping a singlequote so that sequence ogletree single wats work that’s we or ok well we don’t searching beautifulcity don’t know that single quote notice this is not thesame constructed it double quotes single character youthe whole

adventureworks2012it double quotes single character youthe whole to single characters now when executingthis query I’ve just retrieved the list all thecustomers that have purchased thats or shorts inscience in the past 12 months or this bookincludes its moments CV to first last six and who are located you sales territory United States know that this customer service is whatyou need it will be. customer the Apes instead number fourgoing to perform any required thank location and grouping now I know that this query is cool toorich for duplicates customer things that’s notwhat I’m interested in so I’m certainly going to be two cu like the skirt values or shouldconstantly the titled firstly lasting doing this grouping results to 58 gross that or each UT so if fifty aids the customers that have purchasedminutes or shorts in the 12 months see ok to first 2006 and use its territory is the United States I have not yet metall the requirements to my requirements also involve making surethat I’m listing those customers that have worked atleast 10 of ghosts Porsche source as well as the number shorts that the or so thisimpulse aggregation in this case people so were or you just from sales order dq the it that will be me too shores each super s or now we can see that I have manycustomers who did not meet the requirements okayor at least than shorts so we will now and filter to clearly which will units course theworst more or at least send all these shorts this cannot be ok with it where wallsbecause where goals operates or the groupie I ish is you too so the school’s you see in I think most where we go meets some operation and say that with me at least and total or for shorts so at least thatmeans greater than you to and we can now execute this period which is our finalquery miss the all the customers United States network east than ants shorts in any science use 12 months yourlisting their cost already Michael first the less than the number it shows that the or so wehave no used a five-state forces to no this weary statement for fun my all sorting in for business purposes speakmike wants or this particular weary my ass the and then following firstly in this case who stick this step networkfrom to its like that now that we havecomplete its our step-by-step query rewriting usingthis example query I have two more points I would like tohighlight it is very important to consider what’s refer to as spittle values as in the query example we’ve seen weneeded to produce a listing that includes customers who had orderedat least 10 units a particular products it’s important toclearly understand what a pivotal value here is thephysical value is 10 so we will need to consider if the value 9 10 and 11 should or should not be included so weneed to consider if a customer order nine short should they be included theanswer is no because they have not orders at least 10 shouldthey be included if they have ordered 10 shorts the answer is yes because thinis far of having order at least 10 shorts and of course 11 should be included aswell based on this analysis of this pivotalvalues which values that immediately are aroundare key value of 10 naming 9 and 11 is going to be critical to determiningthe proper comparison operator which in our case was greaterthan or equal to it’s also important to consider ofcourse if customers who have ordered at least 10 should be included or excludesremember that far where clause is going todetermine the Rose that matched your criteria andthose rows that match the criteria are going to be included in our resultsat and a final and fifty-point isconsiderations for performance now performance is generally outside thescope of this academic course and this review however there are fewpointers and techniques that you may want toconsider for civil don’t query more than what you need in aproceeding sequel example I queried rose from the customer table sales order header sales order detailand product I also queried sales territory and country region tables potentially I might have been able toeliminate some of their stables by immediately providing key valuesalright devalue instead of their names this cansignificantly speed up query performance because that wouldreduce the number of joints I have to make in my query second the it’s important to learned about the query plan bootyestimated and actual query plans exactly how to do that is outside thescope of this video but sequel Server Management Studioallows you too few house equal server is going to executeyour particular query using that information you can thenidentify departed the query that dick particularly long two-run youcan also identify whether or not your query is using the indexes you wereexpecting your query to use indexes are very important tools makingsure that your queries perform optimally thank you for watchingthis last video in the sequel review series