1 / 25

Structured Query Language (SQL)

Structured Query Language (SQL). Lecture 8 CONS 340. Learning Objectives. Synthesize database concepts from previous weeks E/R Diagrams to Database Demonstrate concepts of database query Learn how to construct a SQL statement Use Relational Query By Example (RQBE )

jana-barlow
Download Presentation

Structured Query Language (SQL)

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. Structured Query Language (SQL) Lecture 8 CONS 340

  2. Learning Objectives • Synthesize database concepts from previous weeks • E/R Diagrams to Database • Demonstrate concepts of database query • Learn how to construct a SQL statement • Use Relational Query By Example (RQBE) • Show examples in ArcMap

  3. Query Language • A computer language used in database management systems to retrieve, add, modify, or delete data

  4. machine data Computer Languages • First generation -- machine language Byte level – 01100101 • Second generation -- assembly language A close approximation of machine language – ASCII 10011011 = e • Third generation -- “high level” languages Procedural Language – Linear programming – Visual Basic, Java – must define objects and actions • Fourth generation -- “4GL” languages • Closer to data, more like spoken languages – SQL

  5. SQL: Structured Query Language • Standard language used with relational databases to build complex logical expressions to access data • Developed by IBM in the 1970s, has become an industry standard • Considered to be a “4GL”

  6. Basic SQL Statements • SELECT column_list FROM table_list {WHERE where_clause} {ORDER BY column_list} {GROUP BY column_list} • Retreive data from one or more tables in the database • INSERT source INTO target(columns) VALUES (value_list) • Insert a tuple (row) into a table in the database • UPDATE target SET col_val_pair_list {WHERE where_clause} • Modify data of one or more tuples in a table • DELETE FROM table {WHERE where_clause} • Delete a tuple from a table

  7. E.F. Codd’s 8 Operators • Traditional • Union • Intersection • Difference • Product • Special • Restrict • Project • Join • Divide 1970, A Relational Model of Data for Large Shared Data Banks, in Communications of the ACM, Vol. 13, No. 6, June 1970, pp. 377-387

  8. SELECT statement • For user’s intents and purposes the SELECT statement is often used • The basic structure is as follows: • SELECT column(s) • FROM table(s) • WHERE clause(s) Project Join Restrict

  9. city Project Students • Returns a relation consisting of all tuples that remain as (sub)tuples in a specified relation after specified attributes have been eliminated. Address Name Age Major Student_no Gender GPA Students [Name, Major] Returns a relation of the Name and Major attributes of the Students relation

  10. Common Attributes a2 b2 a1 b3 a2 b2 c2 a1 b3 c3 Join • Returns a relation consisting of all possible tuples that are a combination of two tuples, one from each of two specified relations, such that the two tuples contributing to any given combination have a common value for the common attribute(s) of the two relations (and that common value appears just once, not twice, in the resulting tuple). a1 b1 a2 b1 a3 b2 b1 c1 b2 c2 b3 c3 Join a1 b1 c1 a2 b1 c1 a3 b2 c2

  11. London London London London Restrict • Returns a relation consisting of all tuples from a specified relation that meet a specified condition. Usually expressed as a WHERE clause. S Rome London Paris London London Rome S WHERE City = London London Madrid

  12. Class Demonstration • Using characteristics from the class (Person and Clothing) show how SQL works, conceptually.

  13. Apply this with a RDBMS in RQBE • Relational Query by Example • A simpler method of creating queries • Generates SQL behind the scenes • Facilitated by specific software interfaces

  14. Query Design View QBE Grid

  15. Project Join Restrict Constructed SQL Statement

  16. Application in ArcMap

  17. Who manages which park? • We need to know how to join tables in ArcMap so we can connect the park contact with the park itself… • How do we join tables?

  18. Joining and SELECT in ArcMap • Rarely will you need to know the full SELECT statement while using ArcMap • So, how would we join two tables without using standard SELECT statement? Remember: • SELECT column(s) • FROM table(s) • WHERE clause(s)

  19. Join • Without using standard SELECT statement we must simulate the Join first • Once completed we have a new table containing linked data with both original tables

  20. The derived table • The new table keeps the original table name, but data is not permanently linked.

  21. Another SELECT example • Let’s use the newly joined table to find parks not controlled by Mr. Jones or not controlled by Ms. Cavallaro?

  22. The Result

  23. The other operators in ArcMap • Insert • Start typing in blank space • Update • Start modifying data • Delete From • Right click or SELECT and press delete key

More Related