Queries

1. Queries

2. Open the Query table. What am I looking for?

3. What am I looking for? • What data element is that? • muname • The data element resides in which table? • mapunit

4. Creating the Query Select Table Type in “FROM” Type in the “WHERE” clause Identify the condition Identify a comparison operator

5. What data type is the data element? • a variable character code

6. Variable Character Code?? • What comparison operator will meet the conditions?

7. Exercise 1 • Problem:Create a query SQL that will load the local database with the map unit name for the ‘harney silt loam, 0 to 1 percent slopes’ mapunit. • Send result to: • paul.finnell@usda.gov • Subject = Exercise 1

8. Using the ‘?’ parameter • FROM mapunitWHERE muname MATCHES ‘harneysilt loam, 0 to 1 percent slopes’

9. Exercise 2 • Problem:Create a query SQL that will select by component name that are greater than 80 percent composition. • Send result to: • paul.finnell@usda.gov • Subject = Exercise 2

10. Exercise 2 - using >, < FROM component WHERE comppct_r >= 80 and compname matches ?

11. Exercise 3 Choice field • Problem:Create a query SQL that will allow the user to select the Component, Major Component flag, and Comp Kind by choice. • Send result to: • paul.finnell@usda.gov • Subject = Exercise 3

12. Exercise 3 FROM component WHERE compname = ? ANDmajcompflag = ? ANDcompkind = ?

13. ‘IN’ parameter • The IN parameter is used to identify multiple choice conditions. For example: FROM component WHERE compname = ? and majcompflag = ? and compkind IN (?)

14. ‘IN’ parameter • The IN parameter is used to identify multiple string conditions. For example: FROM component WHERE compname IN (‘harney’, ‘farnum’, ‘albion’, ‘crete’) AND mapcompflag = 1 and compkind IN (?)

15. ‘IN’ parameter • The IN parameter is used to identify multiple string conditions. For example: FROM component WHERE compname IN (?)

16. Exercise 4 – ‘IN’ operator • Problem: Create a query SQL that will allow the user to select the Component, Major Component flag, BUT, choose Comp Kind from a choice list. • Send result to: • paul.finnell@usda.gov • Subject = Exercise 4

17. Example 5 - Between • Problem: Select a component by name, by kind, with a slope between the user defined lowest RV and highest RV FROM component WHERE compkind IN (?) and (slope_r BETWEEN ? and ?) and compname matches ?

18. Example 5 - Between

19. Exercise • Develop a query that loads all components with the • Lithic bedrock between 50 and 100 cm.

20. Adding Tables • Additional tables can be included to the FROM clause • Include all Tables so the path is continuous from top to bottom • JOIN conditions can include full join condition or use ‘by default’

21. JOINing Tables • Joins can be INNER or OUTER FROM area INNER JOIN legend by default INNER JOIN lmapunit by default INNER JOIN mapunit by default INNER JOIN correlation by default INNER JOIN datamapunit by default LEFT OUTER JOIN component by default

22. JOIN Structure New method: FROM mapunit INNER JOIN correlation by default INNER JOIN datamapunit by default;. Old method: FROM mapunit, correlation, datamapunit WHERE join mapunit to correlation and join correlation to datamapunit;.

23. Types of JOINs • INNER JOIN • only matches values from both tables • the most common type of join • an INNER JOIN is the default join type. If an inner join is omitted from the join clause of a query, the NASIS SQL server will assume it to be an inner join • allows you to join multiple tables in one query, but it requires specific condition for it to work. • must ensure that the join statement has two tables with at least one common overlapping field.

24. Types of JOINS • OUTER JOINS • left outer join • all values from the left (table left of the word join) table and only the matching values in the right (table right of the word join) table • right outer join • all values from the right table and only the matching values from the left table • full outer join • All values from both tables regardless of matching values • Fields will have null values that lack a matching row

25. Example 5 answer FROM area INNER JOIN legend by default INNER JOIN mapunit by defaultWHERE muname MATCHES ‘harney silt loam, 0 to 1 percent slopes’ • Assumption: each area has a legend and each legend retrieved will have a Harney map unit.

26. Outer joins • Outer joins can be used to load data in which child tables are not populated. • Find all ‘sites’ regardless of whether the site mapunit overlap table is populated. • Once you set an outer join, all subsequent joins within an AND clause are to be OUTER

27. OUTER JOIN FROM site LEFT OUTER JOIN sitemuoverlap by default WHERE usiteid matches '*KS155*‘ FROM nasissite LEFT OUTER JOIN pedon by default LEFT OUTER JOIN phorizon by default LEFT OUTER JOIN phvnirscan by default LEFT OUTER JOIN phvnirscanrawdata by default WHERE nasissitename LIKE "MLRA%"

28. Specific joins FROM areatype INNER JOIN area by default INNER JOIN project BY nonmlra_ssa INNER JOIN projectmapunit by default INNER JOIN mapunit by default INNER JOIN correlation by default INNER JOIN data_mapunit by default WHERE areasymbol MATCHES ? "Soil Survey Area Symbol matches" AND muname MATCHES ? "Mapunit name matches" and correlation.repdmu IN (?)

29. Target tables • the target table focuses the outcome of a particular query. • Use this simple query to explain the process FROM datamapunit INNER JOIN component by default WHERE compname matches ? Run this as a local query • First with Component as the target table • Second with Datamapunit as the target table

30. Target tables FROM area INNER JOIN legend by default INNER JOIN lmapunit by default INNER JOIN mapunit by default INNER JOIN correlation by default INNER JOIN datamapunit by default INNER JOIN component by default INNER JOIN copedon by default INNER JOIN pedon by default INNER JOIN siteobs by default INNER jOIN site by default WHERE areasymbol MATCHES ? "Area Symbol matches" AND mustatus != ‘additional’ and compname matches ? AND repdmu =1 AND legendsuituse = 3

31. Arithmetic Operators • Used in the WHERE clause to create a condition FROM chorizon WHERE sandtotal_r is not null and silttotal_r is not null and claytotal_r is not null and om_r < 36 and (sandtotal_r+silttotal_r+claytotal_r) NOT BETWEEN 99.995 and 100.005

32. Exercise 6 • Problem: Select horizons in which the sum of the fine silt and coarse silt does not equal total silt. • Send result to: • paul.finnell@usda.gov • Subject = Exercise 6

33. Exercise 7 • Problem: Select “Kettles” components by elevation that is >= 10,000 feet. • Hint: 3.2808 ft. = 1 meter • Send result to: • paul.finnell@usda.gov • Subject = Exercise 7

34. OR condition • The OR condition returns a selection if either the first OR the second condition is true contrary to the AND condition where it returns a selection if the first AND the second condition are true.

35. Use of ‘OR’ • Problem:Select “Platte” components with occasional or frequent flooding allowing the user to define a selected range of months. • Concepts: • Comparing a range or selection of values • AND and OR in conditions • Ordered codes in NASIS • Storage of monthly data in NASIS

36. OR FROM component INNER JOIN comonth by default WHERE compname matches ‘platte’ and (flodfreq =‘frequent’ OR flodfreq = ‘occasional’) flodfreq is a ‘Choice’ field so the ‘=‘ is used instead of the matches

37. Exercise 9 • Problem:Create a query that loads all major components and only those minor components that are hydric.

38. Exercise 9 answer FROM datamapunit INNER JOIN component by default WHERE ((majcompflag=’yes’) OR (hydricrating = ‘yes’))

39. Choice ‘operators’ • Choice ?? • Choices identified in the Domains report • Can use ‘matches’ or ‘=‘ • flodfreqcl = “rare” • flodfreqcl matches “2” • flodfreqcl = 2

40. YOU KNOW YOU ARE A TRUE Kansan WHEN: 12. Your idea of creative landscaping is a statue of a deer next to your cedar. 13. You were unaware that there is a legal drinking age. 14. Down South to you means Oklahoma.  15. A brat is something you eat. 16. Your neighbor throws a party to celebrate his new pole shed.

41. NULL operator • Problem: Select RaCA site records that do not have a RaCA group ID populated FROM site WHERE rcasiteidIS NOT NULL AND rcasoilgroupid IS NULL AND • Concepts: • IS NULL and IS NOT NULL operators

42. Subqueries FROM area INNER JOIN legend by default INNER JOIN lmapunit by default INNER JOIN mapunit by default INNER JOIN correlation by default INNER JOIN data_mapunit by default INNER JOIN component by default INNER JOIN chorizon by default WHERE areasymbol IMATCHES ? AND mustatus = "correlated" AND repdmu = "yes" AND legendsuituse ="current wherever mapped" AND majcompflag = "yes" AND hzdepb_r IN (SELECT MAX(hzdepb_r) FROM chorizon WHERE JOIN chorizon TO component) AND EXISTS (SELECT * FROM copmgrp WHERE pmgroupname IMATCHES "* till *" AND pmgroupnameNOT IMATCHES "* till over *" AND copmgrp.rvindicator= "yes" AND JOIN component TO copmgrp)

43. Subqueries Subqueries are placed in the WHERE clause and used to retrieve data or set conditions on the retrieval of records • ANY(SELECT * FROM copmgrp WHERE JOIN component to copmgrp and pmgroupname matches '*till*' and rvindicator = 1) • hzdepb_rIN (SELECT MAX(hzdepb_r) FROM chorizon WHERE JOIN component to chorizon) • comppct_r= (SELECT max(comppct_r) FROM component INNER JOIN datamapunit by default)

44. Subqueries EXISTS (SELECT DISTINCT scoopheadspace FROM phdbscoopreading WHERE JOIN phdbscoop TO phdbscoopreading GROUP BY scoopheadspace HAVING COUNT(*) < 4)

45. Aggregation • Group By • Is used to build an aggregation inside the SQL statement • Groups specific column(s) for aggregation FROM site INNER JOIN siteobs by default INNER JOIN pedon by default WHERE rcasiteid IS NOT NULL AND GROUP BY rcasiteid, siteiid HAVING count(upedonid) <> 5

46. Exercise 10 • Write a query that identifies those surface layers that have more than one texture assigned as the RV

47. Answer 10 FROM datamapunit INNER JOIN component by default INNER JOIN chorizon by default INNER JOIN chtexturegrp by default WHERE chtgiid IN (SELECT chtgiidrefFROM chtexture GROUP BY chtgiidref HAVING COUNT(*) > 1) and chtexturegrp.stratextsflag!= "yes” and hzdept_r = 0

48. Alias tables An alias is used to return to a table multiple times to retrieve various records An example would be to return to the area table to retrieve the MLRA overlap and the Non-MLRA survey area

49. FROM area_type at1, area a1, area area2, legend, lmapunit, mapunit, datamapunit, lmuaoverlap, laoverlap, correlation, area_type at2 WHERE mapunit.mapunit_status != "additional" and correlation.representative_dmu =1 and at1.area_type_name= "MLRA" and a1.area_symbol IMATCHES ? "MLRA symbol use a1 for local as target table" and legend.legendsuituse = "current wherever mapped" and at2.area_type_name = "Non-MLRA Soil Survey Area" and join at1 to a1 and join at2 to area2 and join area2 to legend and join legend to lmapunit and join lmapunit to mapunit and join a1 to laoverlap and join laoverlap to lmuaoverlap and join lmapunit to lmuaoverlap and JOIN correlation TO mapunit AND JOIN datamapunit TO correlation

50. Using the FROM inner join FROM area_type at1 join area a1 by default and at1.area_type_name= "MLRA" and a1.area_symbol IMATCHES ? "MLRA symbol use a1 for local as target table" join laoverlap by default join lmuaoverlap by default join lmapunit by default join legend by default and legend.legendsuituse = "current wherever mapped" join area area2 by default join area_type at2 by default and at2.area_type_name = "Non-MLRA Soil Survey Area" join mapunit by default and mapunit.mapunit_status  != "additional" join correlation by default and correlation.representative_dmu =1 join datamapunit by default