1 / 32

Data Query

Data Query. Peter McCartney. RDIFS Training Workshop Sevilleta LTER October 28-30, 2002. Goals. Retrieve data from storage Join tables as needed to produce matrix output (denormalize) Transform data to meet analytic needs Filter, aggregate, select. Structured Query Language.

lelia
Download Presentation

Data Query

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Data Query Peter McCartney RDIFS Training WorkshopSevilleta LTEROctober 28-30, 2002 Center for Environmental Studies Arizona State University

  2. Goals • Retrieve data from storage • Join tables as needed to produce matrix output (denormalize) • Transform data to meet analytic needs • Filter, aggregate, select Center for Environmental Studies Arizona State University

  3. Structured Query Language • Universal syntax for querying relational datbases. • Provides means for specifying • Attributes required • The source tables and how to join them • Criteria for limiting the result (filtering) • Instructions for ordering the result set Center for Environmental Studies Arizona State University

  4. A Simple SQL Query SELECT personnel.first_name, personnel.last_name, personnel.country_code FROM personnel WHERE personnel.country_code="usa“ Center for Environmental Studies Arizona State University

  5. A Slightly More Complex Query SELECT personnel.first_name, personnel.last_name, country.country_name, interests.interest_area FROM country INNER JOIN (personnel INNER JOIN interests ON personnel.person_ID = interests.person_ID) ON country.country_code = personnel.country_code WHERE (((personnel.country_code)="usa")) Center for Environmental Studies Arizona State University

  6. Result Center for Environmental Studies Arizona State University

  7. Views • Views are query statements that are stored in the database. • Views return a matrix and may be treated as if they were another table (you can use a view in a query) • Only the query statement is stored; the result set is reproduced each time you call the view • Views let you hide much of the complicated table joins from your users Center for Environmental Studies Arizona State University

  8. Center for Environmental Studies Arizona State University

  9. Query a View SELECT myFirstView.* FROM myFirstView WHERE (((myFirstView.interest_area)="data management")); Center for Environmental Studies Arizona State University

  10. Querying Remote Databases • The beauty of relational databases is that you can use a variety of client applications to query them. • Your users do NOT need to learn Access in order to use your database. Center for Environmental Studies Arizona State University

  11. Accessing Databases • Native protocol • Every database has its own native protocol. Applications must be written to this. • Open Data Base Connectivity • Windows, Unix, Mac • Java Data Base Connectivity • Cross platform • Designed for Java Language Center for Environmental Studies Arizona State University

  12. A Typical Client Server Connection Connectivity layer translates applications requests into database‘s native syntax. Database (Access) ConnectionLayers (ODBC) Application(Excel) NativeSyntax StandardSQL Center for Environmental Studies Arizona State University

  13. Define an ODBC Source Center for Environmental Studies Arizona State University

  14. Query • Once an odbc source is defined, you may access it from any application that supports ODBC • Excel, SPSS, ArcView, MapInfo, Active Server Pages • Range of capabilities and user friendly features will vary Center for Environmental Studies Arizona State University

  15. Center for Environmental Studies Arizona State University

  16. Center for Environmental Studies Arizona State University

  17. Center for Environmental Studies Arizona State University

  18. Center for Environmental Studies Arizona State University

  19. Center for Environmental Studies Arizona State University

  20. Query from Arc View Center for Environmental Studies Arizona State University

  21. Center for Environmental Studies Arizona State University

  22. Center for Environmental Studies Arizona State University

  23. Center for Environmental Studies Arizona State University

  24. Programming the Web • Client side • ECMCA languages – Javascript • Code executed by client browser. All data must be transferred to browser • Server side • Template “page” scripting • CGI programs Center for Environmental Studies Arizona State University

  25. Web Query • HTTPD/CGI programs • External program receives form input, generates entire page to be returned. • Server side Java • Server scripting • Permits embedding script within html pages • Html code is returned as is, script code is executed by script engine prior to page being returned. • Active Server Pages (MS IIS) • Java Server Pages ( Apache tomcat, BEA WebLogic, IBM WebSphere, Allaire Jrun Center for Environmental Studies Arizona State University

  26. Three-tiered Query using ASP • Declare a connection object and at least one recordset object. • Open the connection to your ODBC source • Parse the form request parameters • Build an sql query • Execute the query putting the result in your recordset • Access the data in your recordset and write html output to display the data in the page Center for Environmental Studies Arizona State University

  27. Center for Environmental Studies Arizona State University

  28. Center for Environmental Studies Arizona State University

  29. Center for Environmental Studies Arizona State University

  30. Center for Environmental Studies Arizona State University

  31. Center for Environmental Studies Arizona State University

  32. Lab Assignment • Add some data to your database • Create some views and store them in your database • (or use the samples at: \\alamillo\inetpub\wwwroot\dbweb) • Create an ODBC data source • Query your data from Access • Create an asp page to display some data following templates: • http://alamillo/dbweb Center for Environmental Studies Arizona State University

More Related