SQL or Structured Query Language is used to communicate, store, manipulate and query data within a database. SQL is the standard language for relationship database management systems. SQL statements are used to perform tasks such as update data on a database, retrieve data from a database, add data to a database and perform joins, queries, searches and create views. It serves as a means of communication between a user and data/database. There are various SQL editors and engines; however, my experience primarily deals with pgAdminIII and PostgreSQL. The following assignments and examples below demonstrate various functions available using SQL language and commands.
SQL Syntax and DML
DML or data manipulation language is a language of syntax elements similar to a programming language used for selecting, inserting, deleting and updating data in a database. DML language manipulates and changes the data using statements which modify the data but not the database. DML consists of detailed syntax, data types and expressions that perform a specific overall function. Common DML statements include SELECT, FROM, WHERE, INSERT, UPDATE, DELETE FROM, etc. These statements normally answer more specific questions about the data.
Example 1
What game fish, listed by common name, were observed in 2004?
Example 1
What game fish, listed by common name, were observed in 2004?
Results: 116 records were located for game fish (listed by common name) observed in 2004.
Results: 116 records were located for game fish (listed by common name) observed in 2004.
Example 2
When and where were Alewife larger than 100 mm observed?
When and where were Alewife larger than 100 mm observed?
Results: 2 records include ALE or Alewife observed that were at least 100 mm in maximum length.
Example 3
What are the X,Y coordinates for the sites associated with the George Washington Birthplace (GEWA) NM?
What are the X,Y coordinates for the sites associated with the George Washington Birthplace (GEWA) NM?
Results: 31 records total for GEWA sites.
Creating VIEWs
Views are a way for a user to save their outputs and results from a SQL query that they have created. The View is saved and considered a new table displaying only the results of the user specified query. This table View can be added to a database on a server and used to create feature classes.
The following SQL query creates a View that lists the fish in the GEWA area by common name found in both the gamefish and non gamefish tables as provided within the original data sets.
The following SQL query creates a View that lists the fish in the GEWA area by common name found in both the gamefish and non gamefish tables as provided within the original data sets.
SQL is used to assist employers, companies and organizations in analyzing, organizing and managing data. SQL provides the ability to analyze large amounts of data and condense it to an user specified output. This proves to be more cost efficient and time manageable for larger corporations and companies.