The Elements of a SQL Query
SQL queries using Data Manipulation Language (the set of SQL statements that access or modify data, as opposed to the Data Definition Language that modifies the structure of the database itself) consist of four blocks, the first two of which are not optional. At a minimum, a SQL query follows the following form: Here, the select keyword identifies what information you wish to display and the from keyword identifies where that data comes from and how those data sources associate with each other. Optionally, a where statement sets limiting criteria, and group by and order by statements associate values and display them in a specific sequence. For example: This query results in a grid that shows the Social Security number, an employee last name, and the employee’s department name—in that column order—taken from the employees and departments tables. The employees table governs, so it’ll only show department names when there’s a matching department number field in both tables (a left outer join is a method of linking tables wherein the left-sided table shows all results and only matching results from the right-sided table appear). Furthermore, the grid only shows employees whose active flag is set to Y, and the result is sorted in ascending order by the department name. But all of this data exploration begins with the select statement.
The SQL SELECT Statement
SQL uses a SELECT statement to select, or extract, specific data. Consider an example based on the Northwind database that frequently ships with database products as a tutorial. Here’s an excerpt from the database’s employees table: It would return: It returns the FirstName and LastName of any employee who is from Tacoma:
The Power of Queries
A database has the potential to reveal complex trends and activities, but this power is only harnessed through the use of the query. A complex database consists of many tables storing a large amount of data. A query allows you to filter the data into a single table so that you can analyze it more easily. Queries also can perform calculations on your data or automate data management tasks. You can also review updates to your data before committing them to the database.
