12.2 What Is SQL?

When data and applications are maintained separately, features of the DBMS make it possible for the application to interact with the data stored in the database. Because it is so common to separate data from applications, there is a standard programming language used by applications to interact with the data in a database. This language is called Structured Query Language (SQL). The S in SQL could also easily stand for standard because SQL is used to interact with data in all sorts of databases, including Oracle, SQL Server, and MySQL.

Before you get too nervous about learning a programming language for this class, it is important to recognize that there are only a limited number of things that an application actually needs to do to interact with a database, so the number of things that we actually do with SQL is pretty small. These activities include the following:

1. Creating database tables
2. Manipulating (adding, updating, and deleting) data in tables
3. Retrieving existing data from tables

In this text, we will cover some of the basics of SQL and focus on SQL features that allow us to retrieve data from an existing table.

SQL Basics

When retrieving data from database tables, we use what we call a SELECT query. For example, we will discuss how to use a SELECT query to retrieve data from the employee table depicted in Figure 12.1.

There are six sections, called clauses, for a SELECT query. While a single query can use all six of these clauses, most queries—even advanced queries—will only use two or three. The six clauses are listed below in the order we use them when writing a query:

1. SELECT: The SELECT clause is always used first when writing a query. The SELECT clause is used to define which of the columns in the table will be displayed in the query results. If we wanted to write a query to display the first and last names of the employees on the employee table, we would start that query, "SELECT FirstName, LastName". The other fields on the table (Salary, Link to Position, and Link to Location) would not appear in the resulting query.
2. FROM: The FROM clause always appears second in a query. The FROM clause lists the tables that will be included in a query result. In our example, to list the first and last names of the employees on the employee table, our SQL statement would be "SELECT FirstName, LastName FROM Employee". Because the SELECT and FROM clauses are the only two required clauses in SQL statements, "SELECT FirstName, LastName FROM Employee" is a valid and complete SQL statement that would return the results shown in Figure 12.2.
Figure 12.2: Query Results
3. WHERE: The WHERE clause is used to limit the rows that are returned in a query result. For example, we may wish to see only those employees whose last name is “Adkins” in our query result. The WHERE clause is optional. When used, it appears directly after FROM. We would add the WHERE clause "WHERE LastName = 'Adkins'" to our SQL statement to show only those employees with the last name “Adkins” in the query result. The resulting SQL statement would be: "SELECT FirstName, LastName FROM Employee WHERE LastName = 'Adkins'" and the DBMS would return the results in Figure 12.3.
Figure 12.3: Query Results with a WHERE Clause.
4. GROUP BY: The GROUP BY clause is used to summarize the data by categories. For example, we may want to know how many employees work in each location. We would use the GROUP BY clause to categorize the query results by location so that we could then calculate the number of employees in each location. The SQL statement required to create this query is beyond the scope of this resource. Because seeing the result of this type of query is helpful in understanding what this type of query does, the results have been included in Figure 12.4.
Figure 12.4: Query Result with a GROUP BY Clause.
5. HAVING: The HAVING clause is also beyond the scope of this class. HAVING is used to restrict the categories in a query result. We might use having to show only those locations where four employees work (see Figure 12.5).
Figure 12.5: Query Result with a HAVING Clause.
6. ORDER BY: The ORDER BY clause is used to sort the rows of the query result. ORDER BY is an optional clause that always appears last in a SQL statement. For example, we may wish to rearrange the three employees returned in the query results in Figure 12.3 to display the employees in alphabetical order by first name. The ORDER BY clause would be "ORDER BY FirstName". The complete syntax for the SQL statement would be "SELECT FirstName, LastName FROM Employee WHERE LastName= 'Adkins' ORDER BY FirstName". The query results for this SQL statement would be as displayed in Figure 12.6.
Figure 12.6: Query Results with an ORDER BY Clause.

data.world

In order to successfully interact with data in a database, the DBMS must provide a place where the user can write and submit SQL statements and then view or receive the results. When an application submits SQL statements and receives query results, this process is unseen by the user. In this lesson, however, you need a place to edit SQL statements and display the results. We will use the data.world website for this purpose.

To create a data.world account, go to the The data.world Login Page. Select the sign up now link at the bottom of the login table (see Figure 12.7).

One benefit of data.world is that it has a rich library of help documents and tutorials. This library includes a tutorial on writing SQL statements. You can access these resources from the main landing page after you log in (see Figure 12.8).

In these lessons, we will refer to the Student Organization that we created in the assessment for lesson 11. A diagram depicting this database can be found in Figure 12.9.

In the remainder of this lesson, we will discuss the SELECT and ORDER BY clauses in more detail. We will discuss the WHERE clause in lesson 13. In Lesson 14, we will cover how to use the FROM clause to include more than one table in a query result.